SQL Server Replication Setup
January 16, 2000
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
‘User Manager for Domains’ create an NT Login (ex. Sqluser) on
both servers with the same password.
the ‘User Cannot Change Password’ and ‘Password Never
Expires’ user properties.
this account in the Administrators Group.
‘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
Advanced Rights are:
‘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.
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
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
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
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.
SET UP THE REPLICATION TOPOLOGY EXAMPLE
Publisher (ServerP), verify that at least 16 MB of
memory is assigned to SQL Server.
SQL Enterprise Manager.
the Server Manager window, select ServerP.
the Server menu, select SQL Server and choose Configure.
the Server Configuration/Options dialog box that appears, select the
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
2. Install a
distribution database on ServerP.
the Server menu and choose Replication Configuration.
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
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
Yes. The Replication-Publishing dialog box appears.
3. Set the
publishing options for ServerP.
Enable Publishing to These Servers, select the Enable checkboxes for
Publishing Databases, select the checkbox for Pubs. This does not publish
the database but does authorize the database to publish.
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:
the Server Manager window, select the subscription server (for example,
the Server menu, choose Replication Configuration; and from the drop-down
menu that appears, choose Subscribing. The Replication-Subscribing dialog
Enable Subscribing from These Servers, select the Enable checkbox for
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.
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.
Replication Topology dialog box appears.
REPLICATE TABLES FROM PUBLISHER TO SUBSCRIBER
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
the Server Manager window, select ServerP, and
then from the toolbar, choose the Manage Publications button. The Manage
Publications dialog box appears.
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.'
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.
the top right corner of the dialog box, choose Add. The Manage Publications
dialog box returns.
the Pubs database on ServerS to the NewPubsTable
the Server Manager window, choose ServerS, and
then from the toolbar, choose the Manage Subscriptions button. The Manage
Subscriptions dialog box appears.
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).
the NewPubsTable publication, and then choose Subscribe.
Subscription Options dialog box appears.
the list in the Destination Database box, select the Pubs database.
OK. The Manage Subscription dialog box returns.
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.