Microsoft SQL Server Transactional Replication Settings

Enable Transactional Replication wizard or the Manage Transactional Log Settings dialog

The information below explains how to configure the transactional replication settings for SQL Server in the Enable Transactional Replication wizard or the Manage Transactional Log Settings dialog available from the Transactional Setup > Manage option on the connection in the Metadata Explorer.

When replicating from SQL Server using mirroring or synchronization, three transactional modes are available: Log Reader, Log Server Agent and Triggers.

Log Reader and Log Server Agent Settings

If you plan to define a mirroring or synchronization replication, you need to define two connections to the database: a non system administrator connection and a system administrator connection. The system administrator connection is used to set up the distributor and access the transaction log. However, a system administrator connection does not provide sufficient user information in the log to identify transactions for replication, so you also need the non system administrator connection.

Notes:

When using SQL Server as a source in mirroring (or as a source or target in synchronization replications), SQL Server requires that tables have a primary key defined. If tables do not have a primary key, you can set up trigger-based replication.
When using SQL Server for synchronization, the database should be set up with a recovery model other than Simple -- either Full or Bulk-Logged recovery models are acceptable. This is because the Simple recovery model, the transaction log that is used to retrieve user ID information can be truncated, causing the synchronization algorithm to fail.

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 two buttons are active

The Ellipsis button to the right of the Distributor field
Click here to open the Change Distributor dialog and view the distributor values. You cannot edit values in the dialog unless you first exit the dialog, remove the installed distributor, then re-open the dialog.
The Remove button is active so that you can remove the distributor in cases where you prefer to use a distributor defined for another accessible installation of SQL Server.

If no distributor exists, the server name is displayed, and two buttons are active:

The Ellipsis button to the right of the Distributor field
Click here to open the Change Distributor dialog and set or create a distributor on a different SQL Server installation from the one containing the table(s) to be replicated (remote distributor).
The Install button is active so that you can install a distributor. If you choose this option to install a distributor in the current SQL Server installation, Syniti DR creates a distributor called DBRS_Distribution.

Be sure to install a distributor before closing the dialog.

Change Distributor Dialog

This dialog can be accessed by clicking next to the Distributor field.

If you have a distributor installed in the SQL Server to which you are connecting for replication, the 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. To change the distributor, you need to close the dialog, remove the local distributor using the Microsoft SQL Server console and reopen the dialog.
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, one will be created using the specified name. Provide a user name and password with appropriate privileges for creating the distributor in the remote installation of SQL Server.
When setting up a remote distributor, SQL Server requires an internal distributor_admin password that is shared between the local publisher and the remote distributor. This password is used only when setting up the distributor and can be auto-generated by the application. If you have a specific password policy or wish to set this password explicitly, use the Password for distributor_admin field to set a password.

NOTES: The following are limitations on the use of remote distributors:

If problems arise when Syniti DR attempts to create the remote distributor, you may need to enter the full pathname to the location where the distributor should be created. This is usually 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.
If problems arise because Syniti DR is not able to locate the server name specified for a remote distributor, you can change the value of the Server field to use an established alias for the server.

While you can set up a distributor on a remote installation of SQL Server, it is not currently possible to set a remote publisher for that distributor using the dialogs available in Syniti DR. For example, if SQL Server installation A is used as a remote distributor for SQL Server installation B, it is not possible to set SQL Server installation C as a publisher for the remote distributor in server A. It is possible to configure this situation manually from the SQL Server management console.
When a distributor is defined as a remote distributor for a SQL Server installation, if an attempt is made to use that distributor in its local installation, the operation will fail.

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.

Use SQL Server Authentication

Select this option to use a SQL Server login ID and password. Provide a system administrator login and password.

Verify (available only in the Manage Transactional Log Settings dlialog)

If you have made changes in the dialog, click Verify to make sure that the distributor is available and set up correctly.