SQL Server Replication Setup

Set up the SecurityConnectivity 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.


SET UP THE REPLICATION TOPOLOGY EXAMPLE


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.


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
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.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles