Automatic MSSQL synchronization setting

Automatic MSSQL Synchronization
The following uses 117 and 75 as examples to describe: 117 release & distributor, 75 subscription Server

I. preparations:
1. Create a sqlagt user on 117 & 75, which belongs to the Administrators group and is used to start the SQLServerAgent service (which is automatically started upon startup). If the local system account does not have the network function, an error may occur.

2. SQL registration in Enterprise Manager 117/75 must be the name of the Local Machine and cannot be local or IP

3. Create 75 SQL registration on 117

4. If MSSQL is installed and the machine name is changed, you need to modify the MSSQL registration Host Name:
4.1 check for SQL registration problems
use master
select srvid, srvname, datasource from sysservers
If the srvid field is 0, if the value of servname is different from that of datasource, you need to run the next step
4.2 modify the registration name
use master
go
declare @ serverproperty_servername varchar (100 ),
@ servername varchar (100)
select @ serverproperty_servername = convert (varchar (100), serverproperty ('servername '))
select @ servername = convert (varchar (100), @ servername)
select @ serverproperty_servername, @ servername
exec sp_dropserver @ Server = @ servername
exec sp_addserver @ Server = @ serverproperty_servername, @ local = 'local'
4.3 run 3.1 again, the value of servname and datasource will be the same

5. Whether the extended storage process required by the system exists (if it does not exist, it needs to be restored ):
Sp_addextendedproc 'xp _ regenumvalues ', @ dllname = 'xpstar. dll'
Go
Sp_addextendedproc 'xp _ regdeletevalue ', @ dllname = 'xpstar. dll'
Go
Sp_addextendedproc 'xp _ regdeletekey ', @ dllname = 'xpstar. dll'
Go
Sp_addextendedproc xp_cmdshell, @ dllname = 'loglog70. dll'
Go

6. synchronization is performed through the Intranet, and port 1433 cannot be disabled on the Intranet, file sharing cannot be disabled, and default sharing cannot be deleted, such as d $ e $

Ii. Synchronization Configuration
117 role: the Host Name of the publishing and distribution server 117 is "117"
75 role: subscription server 75 server name "75"

1. Enterprise Manager --> select node 117
Tool --> copy --> Configuration Publishing, subscription server, and distribution
Next --> make "117" its own distributor --> snapshot folder (\ 117 \ D $ \ MSSQL \ repldata) --> yes --> NO, use the following default settings --> complete
A message indicating that 117 is enabled as 117 is displayed.
After completion, the distribution library is created in 117, and the replication monitor item is displayed.

2. Tools --> copy --> create and manage Publishing, for example, synchronize kbox
Select the database to be synchronized --> Click Create release --> next --> select the database to be synchronized --> next --> transaction release --> server running sqlserver2000 -->
Select the table to be synchronized (the table structure must have a primary key to be selected for synchronization) --> next --> release name (random) --> next --> complete

3. Create an empty database kbox with the same name on 75 to be synchronized

4. On the create and manage release page
Expand the database kbox that has been created to publish --> select publish under this database --> then select force New subscription --> select 75 --> subscribe Database Name (default) --> consecutive shortest latencies -->
Yes. initialize the Architecture and Data. Check the "Start snapshot agent" option to start initialization immediately (for example, if the database and table structure to be synchronized are available on instance 75, select "no") --> next --> finish
After the subscription is completed, a message is displayed, indicating that the subscription is successfully created: 75.

5. Click copy monitor. The first time you will be prompted whether you want to automatically refresh the copy monitor, select Yes, and no longer prompt will be displayed.

6. Expand the replication Monitor and the Publishing Server --> 117 --> kbox: kbox
Click the refresh button to see 75: The kbox status is running.
To the kbox database on 75, we can see that the table structure and content have been updated synchronously.
Test to insert a data entry to any table in the 117kbox database. The synchronized data is displayed on the 75 page.

Note: To add tables and change the table structure in the kbox database, manually delete the table and re-force the subscription.