SharePlex 11.4 - Installation and Setup guide

• If any user has used Snowflake setup utility of a version prior to 11.4, they should first perform the following cleanup steps:

  1. Drop the role 'SHAREPLEX_ROLE' from the Snowflake account using the owner role. For example, if 'SHAREPLEX_ROLE' was created with a user having the 'ACCOUNTADMIN' role, execute the following query using the same privileges:

     drop role "SHAREPLEX_ROLE";

  2. Drop the table 'SHAREPLEX_OPEN_TRANS' from the target DB.SCHEMA. Refer to the query below:

     drop table SHAREPLEX_OPEN_TRANS;

     Re-run the Snowflake setup utility now, following the guidelines below. 'SHAREPLEX_ROLE' will be re-created correctly by the setup using the 'USERADMIN' role."

• Configure DSN in the ODBC directory as per your requirement (for password based authentication or for RSA based authentication).

• Enter the warehouse name that SharePlex is using in the odbc.ini file. For more information, see Guidelines for use section.

• SharePlex setup requires one Admin user to operate, below is the query is reference. Create an Admin user as shown in the following query::
  create user <user_name> identified by '<user_password>';

• Grant the SYSADMIN and USERADMIN roles to the Admin user using the following queries:
  grant role SYSADMIN to user <user_name>;
  grant role USERADMIN to user <user_name>;

• Snowflake_setup internally uses the USERADMIN role to create a new SharePlex role to be used in replication and user-related tasks. The SYSADMIN role is used to grant access to databases and warehouses.

• If the SYSADMIN role does not have access to the intended database and warehouse, provide access using the following queries:

  grant OWNERSHIP on database <database name> to SYSADMIN with grant option;

  grant USAGE on warehouse <warehouse_name> to role SYSADMIN with grant option;

  grant OPERATE on warehouse <warehouse_name> to role SYSADMIN with grant option;

• The required privileges will be granted to the SharePlex role (SHAREPLEX_ROLE), which will then be assigned to the SharePlex user provided as input.

Overview

Use the Database Setup utility for Snowflake (snowflake _setup) to establish SharePlex as a Snowflake user and create the required SharePlex database objects.

This setup utility:

• Create a new role SHAREPLEX_ROLE if it does not exist.

• Creates a new user or uses an existing user

• Creates SharePlex internal tables and objects

It is recommended that you review all of the content in this topic before running the setup utility.

Guidelines for use

• Run the Snowflake_Setup utility on the target Snowflake instance in the SharePlex replication configuration.

• Within a server cluster, run the Snowflake Setup utility on the node to which the shared disk that contains the SharePlex variable-data directory is mounted.

• For consolidated replication, run the Snowflake Setup utility for each variable-data directory.

• Provide a DSN (Data Source Name) as mentioned below:

  • If you have a DSN defined, and you want to use it for the SharePlex connection, copy or link the ODBC files in which that DSN is defined (odbc.ini and odbcinst.ini) to the odbc subdirectory of the SharePlex variable-data directory. This prevents connection errors when the SharePlex processes connect to the database.

  • If you do not have a DSN defined but want to use one, you can create it in the template files provided in the odbc subdirectory.

For an example of the sample odbc.ini and odbcinst.ini files, see the example below.

Sample of an ODBC file in a password based authentication:

Sample of an ODBC file for in an RSA based authentication:

Privileges granted to SharePlex

The database setup utility creates the SHAREPLEX_ROLE and assigns it to SharePlex users. SharePlex assigns the following privileges to the SharePlex_role:

Run database setup for Snowflake

Important! The Snowflake instance must be open before this procedure is performed.

Perform the following steps to run database setup for Snowflake:

1. (Linux only) If you are using multiple variable-data directories, export the environment variable that points to the variable-data directory for the SharePlex instance for which you are running Database Setup.

   ksh shell:

   export SP_SYS_VARDIR=/full_path_of_variable-data_directory

   csh shell:

   setenv SP_SYS_VARDIR=/full_path_of_variable-data_directory

2. Shut down any SharePlex processes that are running, including sp_cop.

3. Run the Snowflake database Setup command (./snowflake_setup ) using password or RSA based admin user.
   

4. Refer to the following table for the prompts and responses to configure SharePlex correctly for the desired connection type.

Table 11: Setup prompts and response for password based authentication

A successful setup terminates with a message similar to the following:

Completed SharePlex for Snowflake database configuration

SharePlex user name: amore2

Database name: SHAREPLEX_TEST

- - The datasource identifier in the SharePlex configuration is 'r.SHAREPLEX_TEST' - -

5. The current version of SharePlex setup does not grant privileges to existing objects in the database. After successfully completing the Snowflake setup, run the following queries for smooth replication:

   grant SELECT,INSERT,UPDATE,DELETE,TRUNCATE ON ALL TABLES IN DATABASE <DataBase name> to role SHAREPLEX_ROLE;

   grant ALL PRIVILEGES ON ALL SCHEMAS IN DATABASE <DataBase name> to role SHAREPLEX_ROLE;

.

Table 12: Setup prompts and response for RSA based authentication

A successful setup terminates with a message similar to the following:

Completed SharePlex for Snowflake database configuration

SharePlex user name: amore2

Database name: PROVIDENCE

- - The datasource identifier in the SharePlex configuration is 'r.SHAREPLEX_TEST' - -