MS SQL Server Settings

Overview

This page provides essential information for setting up replications using Microsoft SQL Server as a source database for Refresh, Mirroring, and synchronization. The setup process for a refresh replication can usually be completed using Syniti Replicate without additional documentation, as it does not involve access to the SQL Server logs. This page also describes the setup process using either the Log Reader or Log Server Agent options for one-way mirroring and synchronization when replicating data from a SQL Server database.

For mirroring replications using Microsoft SQL Server as a source, Syniti Replicate offers the following approaches:

• Log Reader: Queries the SQL Server Log directly.

• Log Server Agent: Uses a Windows service and a Log Server component to query the SQL Server Log for increased performance when dealing with large amounts of data.

• Triggers: Uses Syniti Replicate triggers installed in the SQL Server database to log changes.

  Note

  If you are using a SQL Server Contained Database as a source, you must use trigger-based replication. See the Setup Guide for Trigger-based replications.

Versions Supported

Microsoft SQL Server is supported as source and target systems with the applicable modes as per the following table:

where,

• X: Supported

• L: Log-based replications

• T: Triggers-based replication

For more information, refer to Microsoft SQL Server Database Matrix.

Prerequisites

• Ensure that you have the Microsoft SQL Server .NET Data Provider (included with .NET Framework).

• Ensure that you have the System.Data (file name: System.Data.dll) assembly in your path name.

User Permissions

When setting up replications that use SQL Server as either a source or target database, you need to ensure that the User ID used for establishing connections to the database has sufficient privileges to complete all the operations required for Syniti Replicate to perform a replication.

This section is organized by the type of replication you want to perform. It describes all the user authorizations that will be required during the setup and execution of replications.

Refresh with SQL Server as Either Source or Target Database

1. Authorize to Connect the Database

This permission must be granted already when the user is created. However, if the user is not authorized to establish a connection, you can use the command syntax.

Command Syntax

grant connect to <uid>;

Sample Command

grant connect to sdruser;

where sdruser is the User ID.

2. Authorize to Select Catalog

To display a list of tables and show fields in the table in the Management Center (for selecting a source or target table and for setting which fields to replicate), Syniti Replicate runs a SELECT command on the catalog. If the user ID has insufficient privileges, an error is generated on the SQL Server system.

Use the command syntax to allow read access to any table in the database, including catalog information. It is actually broader than the necessary permission, since this step requires access only to the system tables.

Command Syntax

use <database>;
exec sp_addrolemember 'db_datareader', '<uid>'

Sample Command

use test;
exec sp_addrolemember 'db_datareader', 'sdruser'

where sdruser is the User ID.

3. Authorize to Select Tables

Syniti Replicate runs a SELECT statement to identify records to replicate. Therefore, the user ID used to make a connection must have adequate authority to run a SELECT statement for tables involved in replication.

If you have used the above two syntax, you do not need to grant select authority to specific tables involved in replications.

4. Authorize to Create Tables

(Optional for transactional replications)

To create a target table in the Management Center (as part of the Create Table Wizard), Syniti Replicate requires permissions to modify the database schema.

Command Syntax

use <database>;
grant alter on schema::<schema> to <uid>;
grant create table to <uid>;

Sample Command

use test;
grant alter on schema::dbo to sdruser;
grant create table to sdruser;

where sdruser is the User ID.

5. Authorize to Update Tables

(Optional for transactional replications)

To create a target table in the Management Center (as part of the Create Table Wizard), Syniti Replicate requires write permissions to the database. This command allows write access to any table in the database and is broader than the necessary permission. It is possible to grant more granular access to every single table in the database, if necessary, by changing permissions for a user.

Command Syntax

use <database>;
exec sp_addrolemember 'db_datareader', '<uid>'

Sample Command

use test;

exec sp_addrolemember 'db_datareader', 'sdruser'

where sdruser is the User ID.

6. Authorize to Drop or Alter Tables

(Optional for transactional replications)

The use of these commands in Syniti Replicate is entirely optional (not necessary for running a refresh replication). They are used if you choose to remove a table from SQL Server or change the table via the Management Center SQL Query tab. This command is broader than needed. Alter and drop can also be granted to specific tables.

Command Syntax

use <database>;
grant alter on schema::<schema> to <uid>;
grant create table to <uid>;

Sample Command

use test;

grant alter on schema::dbo to sdruser;

grant create table to sdruser;

where sdruser is the User ID.

Transactional Replications or Refresh with SQL Server as Either Source or Target Database

This section includes information for Mirroring, where SQL Server is the Source data source, and for synchronization, where SQL Server can be either the Source or Target data source.

Important

Complete the authorizations mentioned in the above table before you perform the below authorization.

Authorize to Set Up Transactional Replications and Access Log

Note

This authorization is not required when SQL Server is a target in mirroring replications or when using triggers instead of the transaction log to identify data change.

When setting up transactional replications (where SQL Server is serving as the source of data, either for mirroring or for synchronization), you need additional privileges that are not required for replication operations. You need a user with sysadmin privileges to set up and verify the distributor. For synchronization, a separate user with sysadmin credentials is needed to handle conflicts based on the user.

exec sp_addsrvrolemember '<uid>', 'sysadmin';

For example,

exec sp_addsrvrolemember 'sdruser', 'sysadmin';

Authorize to Execute Replications Without Using a SYSADMIN User ID(Optional)

If you are using a User ID with system administrator privileges to run replications, you do not need to set the below privileges. However, when you do not want to have replications running under a system administrator User ID, you can set the following privileges for an alternative User ID instead.

DBRS_distributor database: 'db_datareader' and 'replmonitor' roles
msdb database: 'db_datareader', 'SQLAgentReaderRole', 'SQLAgentUserRole' roles
publisher database: 'db_owner' OR 'db_datareader' role and PAL member

To add a user to the Publications Access List (PAL) from the SQL Server Management Studio, right-click Replication and select Local Publications> Publication Name > Properties) and then add the user. All remaining privileges can be set through the SQL Server Management Studio (Security > User name > Properties > User Mapping).

Add Source Connection Wizard

Select Provider Screen

1. Select Microsoft SQL Server as the database from the list.

2. The Microsoft .NET Driver (SQL Client) value is provided automatically. You do not need to change this value.

3. The path name to the .NET Assembly for the SQL Server Data Provider is already available in the .NET environment, such that you do not need to provide a value for this field.

Set Connection String Screen

User ID

When replicating from SQL Server using mirroring or synchronization, use a User ID that does not have system administrator privileges. This User ID should also be used exclusively for Syniti Replicate, such that no other applications should use the User ID that you specify for Syniti Replicate. You will need to provide a second User ID with system administrator (or the alternative specific privileges described) in the Setup Info dialog box. The second User ID is used to create the distributor and to access the transaction log.

For Synchronization Replications

The User ID that you provide must be unique to Syniti Replicate. It should not be used for any transactions occurring in either database involved in the synchronization. Syniti Replicate does not replicate transactions by the user you specify in this connection. This user ID is used by Syniti Replicate during synchronization to read the database logs and perform the synchronization operations. Therefore, any transactions found in the logs with this user ID are not replicated as part of the synchronization data.

Connection Properties

Required Information

Optional Information

Limitations

• With SQL Server 2000, Triggers transactional replications does not support data types such as text, ntext, and image.

• For Refresh or Mirroring replications using Bulk Insert with SQL Server as a target, it supports a maximum number of 512 fields. For more fields you must use single insert method.

• Databases, schemas, and tables names containing special characters like slash (/) or backslash (\) are not supported.

• SQL_Variant datatype is not supported.

For more information on the SQL Server database’s limitations, refer to the Microsoft documentation.

Data Type Mapping

When Microsoft SQL Server is used as a target system, the mapping of Syniti Replicate data types to the corresponding data type in Microsoft SQL Server is listed in the following table:

Enable Transactional Replication Wizard

For transactional replications (mirroring and synchronization), use the Enable Transactional Replication wizard after setting up a source connection. The following fields require specific information for SQL Server:

Log Type Screen

Select whether you plan to perform replications using the Log Reader (default) or Log Server Agent.

Log Settings Screen

Publisher

The name of the database server that you are using for the connection.

Distributor

Initially, this field displays the name of the database server you are using for the connection. When you click Verify to see if a Distributor is installed for this database server, the value changes as follows:

• If a distributor exists for the server, the server name is displayed and the following two buttons becomes active:

  • The Ellipsis button next to the Distributor field. Click this button to open the Change Distributor dialog box and view the distributor values. You cannot edit values in the dialog box unless you first exit it. Remove the installed distributor, then re-open the dialog box.

  • The Remove button is active such that you can remove the distributor when you prefer to use a distributor defined for another accessible installation of SQL Server.

• If no distributor exists, the server name is displayed, and the following two buttons becomes active:

  • The Ellipsis button next to the Distributor field. Click this button to open the Change Distributor dialog box and set or create a distributor on a different SQL Server installation from the one containing the tables to be replicated (remote distributor).

  • The Install button is active such that you can install a distributor. If you choose this option to install a distributor in the current SQL Server installation, Syniti Replicate creates a distributor called DBRS_Distribution.

Ensure that you install a distributor before closing the dialog box.

Change Distributor Dialog Box

Select the Ellipsis button next to the Distributor field to access this dialog box.

If you have a distributor installed in the SQL Server to which you are connecting for replication, Use Local Distributor option is selected and values in the Server and Database fields reflect the SQL Server installation and distributor database. These values cannot be changed from the dialog box. To change the distributor, close the dialog box and remove the local distributor, and then reopen the dialog box.

If you do not have a distributor installed and want to set or create a distributor on a remote installation of SQL Server, select the Use Remote Distributor option and enter the SQL Server name (or an established alias for the SQL Server system), then the distributor database name. If the distributor does not exist, a distributor will be created using the specified name. Enter the user name and password with appropriate privileges for creating the distributor in the remote installation of SQL Server.

If problems arise when Syniti Replicate attempts to create the remote distributor, you may need to enter the full path name to the location where the distributor should be created. Usually it will be in the SQL Server folder that contains the Data and Log folders, for example, C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL. The path has to be the complete path up to the SQL Server folder that contains the Data and Log folders.

Use Windows Authentication

Select this option if you (or your system administrator) have set up your environment to use your Windows login ID to access SQL Server. You may also need to open the Connection Properties dialog box and set the optional field Integrated Security to True.

Use SQL Server Authentication

Select this option to use a SQL Server User ID and password. Provide a system administrator User ID and password. Syniti recommends to use this authentication type in Syniti Replicate.

Further Resources

• For a complete up-to-date list of supported sources and targets, and information on where to obtain a .NET provider, check the Supported .NET Providers article in the Help Center.

• Microsoft SQL Server - Always-On Environment

• Microsoft SQL Server Using Trigger-Based Replication