Chat now with support
Chat with Support

SharePlex 9.2.3 - Installation and Setup for SQL Server Source

About this Guide Conventions used in this guide SharePlex pre-installation checklist Download the SharePlex installer Install SharePlex on Linux and UNIX Install SharePlex on Windows Assign SharePlex users to security groups Set up replication between SQL Server databases Set up replication from SQL Server to a different target type Generic SharePlex demonstration-all platforms Solve Installation Problems Database Setup Utilities General SharePlex Utilities Uninstall SharePlex Advanced installer options Install SharePlex as root Run the installer in unattended mode SharePlex installed items

Database Setup for SQL Server

Overview

Run the Database Setup utility for SQL Server (db_setup) to set up a SharePlex account and other components in a SQL Server source or target database to prepare for replication.

This utility does the following:

  • Establish a database account and login credentials for SharePlex. You can create this account ahead of time or have the setup program create it. The account requires full DBA privileges.
  • Capture the database name and the connection string or DSN that SharePlex will use to connect to the database.
  • Create some tables for internal use.
  • (SQL Server source only) Configure the SQL Server replication components and add a publication named SP_REPL_PUB.

Limitations of use

  • The Database Setup utility ignores the data type named "Test."

  • The utility is command-line based. There is no GUI wizard for this utility on Windows.

Requirements for use

Source database only:

  • Make certain that the SQL Server source databases that you will be replicating are owned by a SQL Server user, not a domain user.

  • SharePlex Capture makes use of the underlying components of the native SQL Server replication components. SQL Server Replication must be installed before you install and set up SharePlex, and then the Database Setup utility must be run to configure a local Distribution Agent. This utility is typically run as part of the SharePlex installation procedure.

Source and target databases:

  • Install an ODBC driver for SQL Server.

  • A System DSN (data source name) must exist for the SQL Server database. SharePlex Post uses the DSN to connect to the database through ODBC. This must be a System DSN, not a User DSN.

    If you are using SharePlex for multiple databases that are in different SQL Server instances on the same system, each of those databases must have a unique name. Because SharePlex identifies a database by its name, if two or more databases have the same name, SharePlex will only connect to one of them.

    If databases in different SQL Server instances have the same name but you are only using SharePlex for one of them, the names can remain identical without causing connection conflicts.

  • Run the Database Setup utility on all SQL Server instances in the SharePlex replication configuration.
  • Within a cluster, run the Database Setup utility on the node to which the shared disk that contains the variable-data directory is mounted.

Required privileges

Review the following requirements to ensure that the setup succeeds.

  • The setup utility must be run as a SQL Server System Administrator in order to grant SharePlex the required privileges to operate on the database and to create the SharePlex database account and objects.
  • (Cloud installations) Common restrictions on privileges in cloud-hosted database services make it difficult for the setup utility to succeed in every possible type of scenario. To ensure that the database setup succeeds, only use the setup utility for the following purposes: To do a first-time database setup with a new SharePlex user, or, to modify an existing SharePlex user that either owns the database or has access to it.

Run Database Setup for SQL Server

  1. Log on to the Windows system as a SQL Server System Administrator. The elevated privilege is necessary to grant SharePlex the privileges it needs to operate on the database.

    Important: This user must be a system user so that a system DSN can be created. SharePlex runs as a service, as system user, and does not detect User DSNs.

  2. If SharePlex is running, shut it down:

    1. Run SpUtils from the shortcut on the Windows desktop.
    2. Select the SharePlex Services tab.
    3. Select the correct port, and then stop the SharePlex service.
    4. Close the utility.
  3. Run the Windows command prompt.
  4. Run the db_setup program from the bin subdirectory of the SharePlex product directory.

    Important! If you installed the SharePlex instance on any port other than the default of 2100, use the -p option to specify the port number. For example, in the following command the port number is 9400.

    C:\users\splex\bin> db_setup -p9400

  5. See one of the following, depending on whether you are setting up a new database for SharePlex or modifying an existing database setup to change the SharePlex user and password.

Table 9: Run database setup for a new source or target

Prompt Response

What would you like to do?

[1] Set up new Source or Target

[2] Modify existing database setup

Action:

Enter 1 to set up a new source or target database for SharePlex.

Source or Target

[1] Source

[2] Target

Are you setting up a Source or Target?:

Enter 1 if the database is a source for SharePlex.

Enter 2 if the database is a target for SharePlex.

Connection Types:

[1] SQL Server

Select connection type:

Enter 1 to specify SQL Server.

Database name:

Enter the actual name of the database, not the DSN or a connection string.

Note:

If you are using SharePlex for multiple databases that are in different SQL Server instances on the same system, each of those databases must have a unique name. Because SharePlex identifies a database by its name, if two or more databases have the same name, SharePlex will only connect to one of them.

If databases in different SQL Server instances have the same name but you are only using SharePlex for one of them, the names can remain identical without causing connection conflicts.

Available System Data Sources (System DSNs)

[1] existing_DSN_name

[A] I want to manually enter a connection string

[B] I want to manually enter a DSN

Select DSN:

Do one of the following:

  • To select a displayed DSN, enter the corresponding number.
  • To type a connection string, enter A. You are then prompted for the connection string.
  • To type a DSN string, enter B. You are then prompted for the DSN string.

SQL Server Administrator name:

Enter the name of the SQL Server Administrator, which the setup program uses to perform the setup work on the database.

Password for the Administrator account:

Enter the password of the SQL Server Administrator.

Would you like to create a new SharePlex database user? [y]:

Press Enter to create a new SharePlex database account, or enter n to use an existing account as the SharePlex database account.

SharePlex database user:

Specify the name of the new or exsiting SharePlex account.

 

Password:

Enter the password of the SharePlex account.

Table 10: Change the SharePlex user and password in an existing database setup

Prompt Response

What would you like to do?

[1] Set up new Source or Target

[2] Modify existing database setup

Action:

Enter 2 to modify an existing SharePlex account in the database.

SharePlex connections:

[1] r.database

[2] r.database

Select Connection: 1

Enter the number that corresponds to the existing SharePlex database setup that you want to modify.

Available System Data Sources (System DSNs)

[1] existing_DSN_name

[A] I want to manually enter a connection string

[B] I want to manually enter a DSN

Select DSN:

Do one of the following:

  • To select a displayed DSN, enter the corresponding number.
  • To type a connection string, enter A.
  • To type a DSN string, enter B.

SQL Server Administrator name:

Enter the name of the SQL Server Administrator, which the setup program uses to perform the setup work on the database.

Password for the Administrator account:

Enter the password of the SQL Server Administrator.

Would you like to create a new SharePlex database user? [y]:

Enter n to specify an existing SharePlex database account.

SharePlex database user:

Specify the name of the SharePlex account that you want to modify.

 

Password:

Enter the password of the SharePlex account.

Related Documents