Shulou(Shulou.com)05/31 Report--

This article introduces you how to set automatic synchronization in MSSQL, the content is very detailed, interested friends can refer to, hope to be helpful to you.

1. Preparatory work:

1. Establish a sqlagt user on 117Secret75, which belongs to the administrators group, and is used to start the Sqlserveragent service (boot self-startup). The local system account local will not have the network function and will cause errors.

2. The SQL registration in the enterprise manager on 117xame75 must be a native name, not local or ip

3. Set up 75 SQL registration on 117,

4. If the machine name has been changed after installing MSSQL on 117x75, you need to modify the MSSQL registered host name:

4.1 check for sql registration issues

Use master

Select srvid,srvname,datasource from sysservers

If the value of the srvid field = 0century servname is not the same as the value of datasource, you need to run the next step

4.2 modify the registration name

USE master

GO

DECLARE @ serverproperty_servername varchar

@ servername varchar

SELECT @ serverproperty_servername = CONVERT (varchar, SERVERPROPERTY ('ServerName'))

SELECT @ servername = CONVERT (varchar @ @ SERVERNAME)

Select @ serverproperty_servername,@servername

EXEC sp_dropserver @ server=@servername

EXEC sp_addserver @ server=@serverproperty_servername, @ local='local'

4.3.Running 3.1Jet servname will have the same value as datasource

5. Whether the extended stored procedure required by the system exists (if not, 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 = 'xplog70.dll'

Go

6. Synchronization is through the private network, which cannot close port 1433, close file sharing, or delete default shares such as D$ E$

Second, synchronous configuration

117 role: publisher, Distributor 117 host name is "117"

75 role: subscriber 75 server name "75"

1. Enterprise Manager-- > Select 117 nodes

Tools-- > replication-- > configure publications, subscribers, and distributions

The next step-- > make "117" its own distributor-- > Snapshot folder (\\ 117\ d$\ mssql\ ReplData)-- > Yes-> No, use the following default settings-- > finish

The distributor that successfully enabled "117" to "117" will be prompted when it is finished.

After completion, the distribution library will be set up at 117, and you will see the replication monitor entry

2. Tools-- > copy-- > create and manage publications such as synchronizing KBOX

Select the database to synchronize-- > Click create publication-- > next-- > Select the database to synchronize-- > next-- > transaction publication-- > Server running SQLServer2000-- >

Select the table to be synchronized (there must be a primary key in the table structure to be synchronized)-- > next-- > next-- > publish name (random)-- > next-- > finish.

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

4. In the create and manage release interface

Expand the database that has already created the publication KBOX-- > Select the publication under this library-- > then select force a new subscription-- > Select 75music-- > subscribe database name (default)-- > continuous minimum lag-- >

Yes, initialize the schema and data, and check start the snapshot agent to start initialization processing immediately (for example, the database and table structure to be synchronized on 75, select No)-- > next-- > finish.

Upon completion, you will be prompted to successfully create the subscription: 75

5. Click on the replication monitor, and you will be prompted for the first time whether you want to automatically refresh the replication monitor. Select Yes, and check the box to no longer prompt.

6. Expand replication Monitor, publisher-- > 117Mai-- > KBOX:KBOX

Click the refresh button to see that the 75:KBOX status is running

To the KBOX library on 75, you can see that the table structure and contents have been updated synchronously.

The test inserts a piece of data into any table of the 117KBOX library, and you will see the same data synchronized on 75

Note: new tables and table structure changes in KBOX library need to be manually deleted and re-forced subscriptions

On how to set up automatic synchronization in MSSQL to share here, I hope the above content can be of some help to you, you can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.