SQL Server Replication Setup

Sunday Jan 16th 2000 by Kevin Goodwyn

Set up the Security\Connectivity for Replication.

Note: Perform the following steps on the Publisher (ServerP) and Subscriber (ServerS) Servers unless stated otherwise.

1. Log on to Windows NT as Administrator. This account does not have to have the same password for both Servers.

2. Setup NT Login.

A. Using ‘User Manager for Domains’ create an NT Login (ex. Sqluser) on both servers with the same password.

B. Check the ‘User Cannot Change Password’ and ‘Password Never Expires’ user properties.

C. Put this account in the Administrators Group.

3. Setup Advanced Rights.

A. Using ‘User Manager for Domains’, Policies, and User Rights give the NT Login on both servers the four required Advance Rights.

B. In the User Rights Policy Dialog Box check the ‘Show Advanced User Rights’ checkbox. Then select the right from the ‘Right’ drop-down box and click the Add button to grant the right to the user account.

C. The Advanced Rights are:

I. ‘Act as part of the operating system’
II. ‘Increase quotas’
III. ‘Log on as a service’
IV. ‘Replace a process level token’

4. Create a trusted connection in SQL Server with the NT Administrators Group.

A. This may be done using the SQL Server Security Manager GUI or using the xp_grantlogin SQL Extended Stored Procedure.

B. The SQL Statement is xp_grantlogin ‘Administrators’, ‘admn’. This statement grants SQL Server Access to the NT Administrators Group and gives the group System Administrator privilege.

C. It is recommended to use the SQL Statement.

Note: All logins in the NT Administrators Group will be trusted.

5. Logon to Services with NT Login.

A. Go to Control Panel and the Services Applet. Stop the MSSQLServer Service. Click on ‘This Account’ and enter the NT Login and Password created in Step 2. It is a ‘good practice’ to retype the Password. Start the Service.

B. Repeat Step A for the SQLExecutive Service.

6. Set the Default Network Library.

A. Go to the SQL Server Client Configuration Utility and select the Net Library Tab. Set the Default Network Library to Named Pipes or Multi-Protocol. (This must be done because of the trusted connection required for replication. Both Named Pipes and Multi-Protocol supports trusted connections. TCP/IP does not.)

B. Remove any client configuration entries defined under the Advance Tab.

Note: Step 6 has to be done on the Publisher/Distributor Server. It is not required on the Subscriber Server.

7. Check the ‘Truncate log on checkpoint’ option in the Distribution Database.

8. Open SQL Server Enterprise Manager and register as ‘SA’.

9. Compare the SQL Settings (Character Set and Sort Order) on both Servers by executing the sp_helpsort SQL Statement.

Note: Once the Security and Connectivity Issues have been setup properly the task of setting up replication is fairly straightforward. Just follow the Microsoft Articles in the SQL Server Books Online and the SQL Server Administrator’s Companion Manual titled ‘Replicating a Table’, ‘Replicating a Partitioned Table’, etc.


1. On Publisher (ServerP), verify that at least 16 MB of memory is assigned to SQL Server.

A. Start SQL Enterprise Manager.

B. From the Server Manager window, select ServerP.

C. From the Server menu, select SQL Server and choose Configure.

D. From the Server Configuration/Options dialog box that appears, select the Configuration tab.

E. Verify that the memory setting is 8192 or greater. If it is not, change it, and then restart the server. This computer must have at least 32 MB of memory installed.

2. Install a distribution database on ServerP.

A. From the Server menu and choose Replication Configuration.

B. From the drop-down menu that appears choose Install Publishing. (If Replication is already installed, Uninstall Publishing will be displayed. To remove replication refer to Remove Replication under the Replication Tips Section). The Install Replication Publishing dialog box appears. By default, the Local option is selected, and the Database Name box has an entry of distribution.

C. Add a device for the distribution database by choosing from the Data Device box and completing the New Device dialog box that appears. Name the device DistPubsData and give it a size of 30 MB.

D. Add a device for the distribution database transaction log by choosing from the Log Device box and completing the New Device dialog box that appears. Name the device DistPubsLog and give it a size of 15 MB.

E. Choose OK. A dialog box asks whether you will configure publication options for this server at this time. (There is about a minute wait before the dialog box appears.)

F. Choose Yes. The Replication-Publishing dialog box appears.

3. Set the publishing options for ServerP.

A. Under Enable Publishing to These Servers, select the Enable checkboxes for ServerS.

B. Under Publishing Databases, select the checkbox for Pubs. This does not publish the database but does authorize the database to publish.

C. Choose the OK button.

4. Create a database on the subscription server, to be used as the destination databases. If the destination database does not exist on ServerS, create a database named Pubs. For information on using SQL Enterprise Manager to create a database, see Microsoft SQL Server Administrator’s Companion, Chapter 6, Managing Databases.

5. Set the subscription options for the subscription. For ServerS, perform these steps:

A. From the Server Manager window, select the subscription server (for example, ServerS).

B. From the Server menu, choose Replication Configuration; and from the drop-down menu that appears, choose Subscribing. The Replication-Subscribing dialog box appears.

C. Under Enable Subscribing from These Servers, select the Enable checkbox for ServerP.

D. Under Subscribing Databases, select the Enable checkbox for the database that will be authorized to be a destination database (for example, Pubs). This does not subscribe the database to any publications, but it does authorize the database to subscribe.

E. Choose OK.

You have finished setting up your example replication topology!

6. To view a graphic depiction of the replication topology that you have set up, from the Server Manager window, choose ServerP, and then from the toolbar, choose the Replication Topology button.

The Replication Topology dialog box appears.


Note: Now that the basic replication relationships between the Publisher and Subscriber Servers have been established, the replication of a database, table(s), or partitioned tables from one server to another can be accomplished. In this example, the server ServerP will publish the Authors and Employee tables from the Pubs database, and the server ServerS will subscribe to it.

1. Publish the Authors table by creating the publication NewPubsTable.

A. From the Server Manager window, select ServerP, and then from the toolbar, choose the Manage Publications button. The Manage Publications dialog box appears.

B. Select the Pubs database, and then choose the New button. The Edit Publications dialog box appears.

C. In the Publication Title box, type 'NewPubsTable.'

D. In the Description box, type 'Publishing the Authors and Employee tables from the Pubs database.'

E. From the Database Tables list, select the Authors and Employee tables, and then choose the adjacent Add button. The tables are added to the Articles in Publication column.

F. From the top right corner of the dialog box, choose Add. The Manage Publications dialog box returns.

G. Choose Close.

2. Subscribe the Pubs database on ServerS to the NewPubsTable publication from ServerP.

A. From the Server Manager window, choose ServerS, and then from the toolbar, choose the Manage Subscriptions button. The Manage Subscriptions dialog box appears.

B. Open the published database tree for ServerP (choose the "+" box next to the server name), and then open the publications list for the Pubs database (choose the "+" box next to the database).

C. Select the NewPubsTable publication, and then choose Subscribe.

D. The Subscription Options dialog box appears.

E. From the list in the Destination Database box, select the Pubs database.

F. Choose OK. The Manage Subscription dialog box returns.

G. Choose Close.

Note: This replication example is now set up. If the default synchronization setting for the publication NewPubsTable was not changed, synchronization will occur within five minutes. Synchronization will copy the schema and data from the published tables to the destination database. After that, replication of changes to table data will occur as changes are made.

Mobile Site | Full Site