Data synchronization between SQL Server servers

Before setting and using database replication in SQL Server 2000, check whether the following SQL Server servers meet the requirements:

1. Does the MSSQLServer and SQLServerAgent services start and run as domain users (. \ administrator users can also)

If you use the local account to log on, the network function is unavailable and the following error occurs:

The process failed to connect to the distributor '@ server name'

(If your server has used the full-text retrieval service of SQL Server, do not modify the local startup of MSSQLServer and SQLServerAgent services.
The full-text search service is unavailable. Use another machine as the distribution server for SQL Server 2000 replication .)

To modify the login user who starts the service, you must restart the MSSQLServer and SQLServerAgent services to take effect.

2. Check whether the names of the related SQL Server servers have been changed. (The srvname and datasource must be the same on the local machine with srvid = 0)

Run the following command in the query Analyzer:
Use master
Select srvid, srvname, datasource from sysservers

If there is no srvid = 0 or srvid = 0 (that is, the current machine) but the srvname is different from the datasource, You need to modify it as follows:

Use master
Go
-- Set two variables
Declare @ serverproperty_servername varchar (100 ),
@ Servername varchar (100)
-- Obtain information about the Windows NT server and the instance associated with the specified SQL server instance
Select @ serverproperty_servername = convert (varchar (100), serverproperty ('servername '))
-- Returns the name of the local server running Microsoft SQL Server.
Select @ servername = convert (varchar (100), @ servername)
-- Display the obtained Parameters
Select @ serverproperty_servername, @ servername
-- If @ serverproperty_servername is different from @ servername (because you changed the computer name), run the following
-- Delete the wrong server name
Exec sp_dropserver @ Server = @ servername
-- Add the correct server name
Exec sp_addserver @ Server = @ serverproperty_servername, @ local = 'local'

To modify this parameter, you must restart the MSSQLServer and SQLServerAgent services to take effect.

In this way, errors 18482 and 18483 will not occur during replication creation.

3. Check whether the registration names of the SQL Server related to the SQL Server Enterprise Manager are the same as those described in the second section above.

You cannot use the registration name of an IP address.

(We can delete the registration of IP addresses and create a new server name registered as an SQL Server administrator)

In this way, errors 14010, 20084, 18456, 18482, and 18483 will not occur during replication creation.

4. Check whether the network of Several SQL Server servers can be accessed normally.

If you can ping the Host IP address but cannot ping the host name, you must

Winnt \ system32 \ drivers \ etc \ hosts (Win2000)
Windows \ system32 \ drivers \ etc \ hosts (win2003)

The correspondence between the IP address of the database server and the host name.

For example:

127.0.0.1 localhost
192.168.0.35 oracledb
192.168.0.65 fengyu02 fengyu02
202.84.10.193 bj_db

Or create an alias in the network utility of the SQL Server Client, for example:

5. Whether the extended stored procedure required by the system exists (if it does not exist, it must 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_mongoshell, @ dllname = 'loglog70. dll '

Next, you can use the SQL Server Enterprise Manager to [copy]-> right-click to select
-> The [configure Publishing, subscription server, and Distribution] graphic interface is used to configure database replication.

The steps for configuring replication are as follows:

1. Create a publishing and distribution server

[Welcome to the configuration release and distribution wizard]-> [select a distribution server]

-> [Make "@ servername" its own distribution server, and SQL server will create distribution databases and logs]

-> [Create a snapshot folder]-> [custom configuration]-> [No, use the following default configuration]-> [complete]

After the preceding steps are completed, a distribion library and

A distributor_admin administrator-level user (we can change the password as needed)

Four new jobs are added to the server:
[Proxy Program History clearing: distribution]
[Clear Distribution: distribution]
[Copy proxy check]
[Re-initialize a subscription with failed data verification]

The SQL Server Enterprise Manager has a replication Monitor, which allows you to publish, distribute, and subscribe to the current machine.

In SQL Server Enterprise Manager, right-click and choose
-> [Configure Publishing, subscription server, and Distribution], you can see the following:

In the [attributes of the publisher and distributor] window

-> [Publisher]-> [add]-> [OK]
-> [Publish database]-> [transaction]/[merge]-> [OK]
-> [Subscription server]-> [add]-> [OK]

Add other SQL Server servers on the network as Publish or subscribe servers.

Options for adding a Publishing Server:

The newly created jin001 Publishing Server is connected to the Administrator-level database user test,

The password option box for the management link to the Publishing Server. The default value is selected,
Enter the password of the distributor_admin user when creating a Fengyu/Fengyu connection on the newly created jin001 Publishing Server and the distribution server.

You can enter an optional Password box for the management link to the Publishing Server,
That is to say, you do not need a password to establish a link for publishing to the distributor (this is of course a lack of security and can be used in a testing environment)

Options for adding a subscription Server:

2. Select the distribution server for another Publishing Server (for example, jin001) on the new network.

[Welcome to the configuration release and distribution wizard]-> [select a distribution server]

-> Use the following servers (the selected servers must be configured as distribution servers)-> [Selected servers] (for example, Fengyu/Fengyu)

-> [Next]-> [enter the password of the distributor_admin user of the distributor (for example, Fengyu/Fengyu) Twice]

-> [Next]-> [custom configuration]-> [No, use the following default configuration]

-> [Next]-> [complete]-> [OK]

Create a database copy and release process:

[Copy]-> [publish content]-> right-click and choose-> [Create release]

-> [Next]-> [select Release Database]-> [select a database to be released]

-> [Next]-> [select release type]-> [Transaction release]/[merge release]

-> [Next]-> [specify the subscription server type]-> [run SQL Server 2000 Server]

-> [Next]-> [specify a project]-> [only tables with primary keys can be published in transaction Publishing]-> [select a table with primary keys to be released]

-> [The Unique Identifier Field [rowguid] of the unique index and rowguidcol attribute will be added to the table during merge and release. The default value is newid ()].

(Adding a new column will cause the insert statement without the column list to fail, increase the table size, and increase the time required to generate the first snapshot)

-> [Select a table to be released]

-> [Next]-> [select the release Name and description]->

-> [Next]-> [custom release attributes]-> [No, create a release according to the specified method]

-> [Next]-> [complete]-> [close]

There are many useful options in publishing properties: Set subscription expiration (for example, 24 hours)

Set the project attributes of the publishing table:

In the general window, you can specify the name of the target table to be published, which may be different from the original table name.

Is the topic of the command and snapshot window

(SQL Server database replication technology uses insert, update, and delete operations to re-Execute transaction operations on the publishing server on the subscription server.

To read documents, you need to set the release database to the full recovery mode so that transactions will not be lost.

However, in my own test, I found that publishing a database is in simple recovery mode. Some large transactions are generated every 10 seconds and the database logs are reduced after 10 minutes,
During this period, jobs on the publishing and subscription servers are paused. After the pause is resumed, no transaction changes are lost)

You can filter data in a published table. For example, you can only select some columns in the table:

For example, if you only select some qualified records in the table, you can manually write the filtered SQL statement:

Publish the subscription options of the table and create a forced subscription:

After the publishing is successfully established, a new job is added to the Publishing Server: [Clearing invalid subscriptions]

Two new jobs are added to the distributor:
[JIN001-dack-dack-5] type [repl snapshot]
[JIN001-dack-3] type [repl Log Reader]

The blue name above uses different numbers based on the server name, name, and number of releases.

A repl snapshot job is a prerequisite for SQL Server replication. It first generates the published table structure, data, indexes, and constraints to the files in the OS directory of the Publishing Server.
(It is generated only when a subscription is available. It is generated when the subscription request is initialized or scheduled according to a certain schedule)

The repl Log Reader is always running during transaction replication. (You can run the task according to the scheduling schedule when merging and copying data)

Create a database copy and subscription process:

[Copy]-> [subscribe]-> right-click and choose-> [Create a request subscription]

-> [Next]-> [find a release]-> [view a release made by a registered server]

-> [Next]-> [select release]-> [select the Database Release name on the created Publishing Server]

-> [Next]-> [Specifying synchronization agent logon]-> [when a contemporary agent is connected to the proxy server: using SQL Server Authentication]
(Enter the distributor_admin user name and password on the Publishing Server)

-> [Next]-> [select the target database]-> [select the database name in which the subscription is created]/[Create a database name]

-> [Next]-> [Allow Anonymous subscription]-> [Yes, generate anonymous subscription]

-> [Next]-> [initialize subscription]-> [Yes, initialize Architecture and Data]

-> [Next]-> [snapshot transfer]-> [use the snapshot file in the default snapshot folder of the release]
(The subscription server must be able to access the publisher's repldata folder. If there is a problem, you can manually set network sharing and sharing permissions)

-> [Next]-> [snapshot transfer]-> [use the snapshot file in the default snapshot folder of the release]

-> [Next]-> [set distribution Agent Scheduling]-> [use the following scheduling]-> [change]-> [for example, scheduling every five minutes]

-> [Next]-> [enable required services]-> [This subscription requires the SQLServerAgent service to be run on the Publishing Server]

-> [Next]-> [complete]-> [OK]

After the subscription is successfully created, the subscription Server adds a [repl-distribution] job (when merging and copying, the job category is [repl-merge]).
It will run the Database Synchronization and replication jobs according to the time schedule table we have given.

View the running status of its history, as shown in the following figure:

On the distributor's [Copy monitor]-> [Publisher]-> [publisher name]-> [Log Reader]-> right-click and choose-> [proxy history], example:

Iii. Experiment logs that may encounter exceptions after SQL Server replication is configured:

1. When the Publishing Server is disconnected, the SQL Server service is shut down, restarted, and shut down, it does not have much impact on the configured replication.

During the interruption, the distribution and subscription receive the transaction information that has not been copied.

2. If the distribution server is disconnected, the SQL Server service is disabled, restarted, or shut down, the replication settings may be affected.

Transactions on the Publishing Server are accumulated during the interruption.
(If you set the option to delete expired subscriptions for a long time, the transaction logs of busy database publishing may expand rapidly ),

The subscription server will try again and again because it cannot access the Publishing Server.
We can set the interval between the number of retries and the number of retries (the maximum number of retries is 9999. If the number of retries is one per minute, it can be about 6.9 days without error)

The SQL Server Service of the distribution server is started. After the network is connected, the accumulated jobs on the distribution server will be applied to the subscription machine in chronological order:

It takes a relatively long time (in fact, the insert, update, and delete statements of all transactions are generated and executed on the subscription server)
We tested 58 transactions on a common PC and executed 100228 commands at 7 minutes 28 seconds.

3. The subscription server is disconnected, the SQL Server service is disabled, restarted, and shut down, which has a great impact on the configured replication. You may need to try again.

our lab environment (subscription server) was shut down unexpectedly at, and then restarted at the next day,
after, the replication has started to run normally, the stacked jobs on the publishing server will be applied to the subscription machine in chronological order
however, the snapshot error message appears in the replication manager. Snapshots may need to be retried, replication may need to be restarted.
)
4. You can use the delete button to delete a created release or subscription.
we recommend that you delete the subscription before publishing, finally, disable the order of publishing.
to completely delete the replication settings on SQL Server, perform the following operations:

[Copy]-> right-click and choose [disable Publishing]-> [Welcome To The Disable publishing and distribution wizard]

-> [Next]-> [disable Publishing]-> [disable publishing on "@ servername"]

-> [Next]-> [complete the release and distribution disabling wizard]-> [complete]

We can also use the T-SQL command to copy the publication and subscription creation and deletion, select the published and subscription has been set, right-click the label
You can [generate an SQL script]. (I will not discuss it in detail here. The recommended website will have more details)

When you try to delete or change a table, the following error occurs:
Server: MSG 3724, level 16, state 2, line 1
Cannot drop the table 'object _ name' because it is being used for replication.

A typical case is that the table was used for replication, but was deleted later.

Solution:
Select * From sysobjects where replinfo> '0'

Sp_configure 'Allow updates', 1
Go
Reconfigure with override
Go
Begin transaction
Update sysobjects set replinfo = '0' where replinfo> '0'
Commit transaction
Go
Rollback transaction
Go
Sp_configure 'Allow updates', 0
Go
Author's blog:Http://blog.csdn.net/aloesky/RelatedArticle