Synchronize two sqlserver databases (1)

Synchronize two sqlserver databases (1)Source: Release Date:

How to synchronize the content of two sqlserver databases? The program code can have version management cvs for synchronization management, but Database Synchronization is very troublesome. You can only change one of them and then change the other, if you forget to change the other database, the structure or content of the two databases is often inconsistent. do you have any good methods?

I. Distribution and Replication

Use forced subscription to synchronize databases. You can use the Database Synchronization Mechanism to process large amounts of and batch data:
//
Note:
For ease of operation, all operations are performed on the publisher (distributor) and the push mode is used.
Use the forced subscription method on the customer's machine.

Ii. pass the test

1: Environment

Server environment:
Machine name: zehuadb
Operating System: Windows 2000 Server
Database Version: SQL 2000 Server Personal Edition

Client
Machine name: zlp
Operating System: Windows 2000 Server
Database Version: SQL 2000 Server Personal Edition

2: create a user account

Create a domain user account on the server
My computer management-> local users and groups-> Users-> Create
Username: zlp
Userpwd: zlp

3: restart the server MSSQLServer

My computer-> Control Panel-> management tools-> services-> MSSQLServer services
(Changed to: domain user account, we created zlp user. zlp, password: zlp)

4. Install the distributor.

A: configure the distributor.
Tool-> copy-> Configuration Publishing, subscription server and distribution-> next (all use the default configuration)

B: configure the Publishing Server
Tools-> copy-> create and manage publishing-> select the database to be released (sz) -> next-> snapshot publishing-> next-> select the content to be released-> next-> complete

C: Force configure the subscription server (push mode, pull mode is the same)
Tool-> copy-> Configuration Publishing, subscription server and distribution-> subscription server-> New-> SQL Server database-> enter the name of the client server (zlp) -> use SQL Server Authentication (SA, empty password)-> OK-> application-> OK

D: Initialize the subscription.
Copy Monitor-> publisher (zehuadb) -> double-click subscription-> force new-> next-> select an enabled subscription server-> zlp-> next-> complete

5. test whether the configuration is successful.

Copy Monitor-> Publish publish? Zehuadb)-> double-click SZ: SZ-> node status-> click Run Agent now

View:

Copy Monitor-> publisher (zehuadb)-> SZ: SZ-> select zlp: SZ (type forced)-> right-click-> Start Synchronization

If no error mark (Red Cross) exists, congratulations!

 

Synchronize two sqlserver databases (2)Source: Release Date:

 

6. Test Data

Run the following command on the server:

Select a table and execute the following SQL statement:
Insert into wq_newsgroup_s select 'test successfully', 5

Copy Monitor-> Publish server (zehuadb)-> SZ: SZ-> snapshot-> Start agent-> zlp: SZ (Force)-> Start synchronization Processing

Check whether a new record is inserted in the synchronized wq_newsgroup_s.

The test is completed and passed.
7. Modify the synchronization time of the database. Generally, you can perform database synchronization at night.
(Procedure omitted)

/*
Note:
The server end cannot publish or distribute data in local mode. You need to delete the registration and create a new local computer name.

Uninstall method: tool-> copy-> prohibit publishing-> static release on "zehuadb", Uninstall all Database Synchronization configuration servers

Note: The SQLServerAgent service on the Publishing Server and distribution server must be started.
Push mode: "D: Microsoft SQL servermssqlrepldataunc" directory file can be shared
Pull Mode: Shared ~!

*/

A small number of databases can be synchronized using triggers to synchronize a single table.

Iii. Possible problems during configuration

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.

Synchronize two sqlserver databases (3)Source: Release Date:

 

3. Check whether the registration names of the SQL Server related to the SQL Server Enterprise Manager are the same as the srvname described in the second point 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

Winntsystem32driversetchosts (Win2000)
Windowssystem32driversetchosts (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 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'
 
 
Next, you can use the [copy]-> right-click and choose-> [configure Publishing, subscription server, and Distribution] GUI in the SQL Server Enterprise Manager 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. 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 will be created in the current "@ servername" SQL Server database (we can change the password as needed ).

Four new jobs are added to the server:

[Clear Agent history: 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, we [copy] Again-> right-click and choose-> [configure Publishing, subscription server, and Distribution]

You can go to the [attributes of the Publishing Server and the distribution server] WINDOW> [Publishing Server]> [add]> [OK]> [Publishing 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, you do not need a password to establish a link to the distribution server (this is of course a lack of security, can be used in the test environment ).
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.

3. Experiment logs that may encounter exceptions after SQL Server is copied and 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 experimental environment (subscription server) was shut down unexpectedly at. After the restart at the next day, the replication that has been set up started again after, the stacked jobs on the publishing server will work on the subscription machine in chronological order, but the snapshot error message appears in the replication manager. The snapshot may need to be retried, And the replication may need to be restarted. (The machines in our experiment environment have not been tested for snapshots, And the replication operation is still successful)

4. You can use the delete button to delete a created release and subscription.

We 'd better always delete and review, then delete, and 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 can [generate 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
Reconfigure with override
Go
1. Questions:

If the synchronization agent is stopped after the merge replication configuration is complete. I want to restart the synchronization proxy for merge replication in the program. What commands or stored procedures are used?
 
Solution:

Sp_start_job
Indicates that the SQL Server Agent executes the job immediately.

Example

In the following example, start a job named "Maid backup.

Use MSDB
Exec sp_start_job @ job_name = 'nightly backup'

2. Log Restoration

/* -- Description:
The following code demonstrates how to use the log restoration function to timely report data changes in the primary database to the standby database.
The data of the standby database can be used for query at any time, but cannot be updated (the standby database is read-only ).
--*/

First, create a database (master database) for demonstration ).

Create Database db_test
On
(Name = db_test_data,
Filename = 'C: db_test.mdf ')
Log On
(Name = db_test_log,
Filename = 'C: db_test.ldf ')
Go

Back up the database.

Backup database db_test to disk = 'C: est_data.bak 'with format
Go

Restore the database to a backup database (this shows the synchronization between the primary database and the backup database ).

Restore database db_test_bak from disk = 'C: est_data.bak'
With replace, standby = 'C: db_test_bak.ldf'
, Move 'db _ test_data 'to 'C: db_test_data.mdf'
, Move 'db _ test_log 'to 'C: db_test_log.ldf'
Go

Start the SQL Agent service.

Exec master.. xp_cmdshell 'net start sqlserveragent', no_output
Go

Create a job for synchronization between the master server data training and the slave server database.

Declare @ jogid uniqueidentifier
Exec MSDB .. sp_add_job
@ Job_id = @ jogid output,
@ Job_name = n' Data Synchronization Process'

To create a synchronization task, follow these steps:

Exec MSDB .. sp_add_jobstep
@ Job_id = @ jogid,
@ Step_name = n' data synchronization ',
@ Subsystem = 'tsql ',
@ Command = N'

Log backup in the primary database

Backup log db_test to disk = ''c: est_log.bak ''with format

Restore the log backup of the primary database in the standby database (the latest changes in the primary database are applied.

In practice, the restoration jobs of the backup and backup databases of the primary database should be created on the primary server and the backup server respectively, and the backup files should be placed in the shared directory that can be accessed by the primary server and the backup.

Restore log db_test_bak from disk = ''C: est_log.bak'' with standby = ''C: est_log.ldf ''',
@ Retry_attempts = 5,
@ Retry_interval = 5

Create Scheduling (executed every minute ).

Exec MSDB .. sp_add_jobschedule
@ Job_id = @ jogid,
@ Name = n' schedule ',
@ Freq_type = 4,
@ Freq_interval = 1,
@ Freq_subday_type = 0x4,
@ Freq_subday_interval = 1,
@ Freq_recurrence_factor = 1
Add the target server.

Exec MSDB. DBO. sp_add_jobserver
@ Job_id = @ jogid,
@ SERVER_NAME = n' (local )'
Go

Through the above processing, the synchronization relationship between the master database and the slave database has been set up.

Run the following command to test whether synchronization can be implemented:

Create a test table in the primary database

Create Table db_test.dbo.tb_test (id int)
Go

Wait 1 minute 30 seconds (because the synchronization interval is set to 1 minute, so the latency is required to see the effect)

Waitfor delay '00: 01: 30'
Go

Check the standby database to see if the synchronization is successful.

Select * From db_test_bak.dbo.tb_test

/* -- Result:
ID
-----------

(The number of affected rows is 0)
--*/

Test successful

Go

Finally, delete all tests.

Drop database db_test, db_test_bak
Exec MSDB .. sp_delete_job @ job_name = n' Data Synchronization Process'
Go
/* ===================================================== ===================================== */

/* -- Server File Server handling instructions
This method is used to synchronize databases. When the primary database is unavailable (for example, the primary database is damaged or shut down for repair)
You can use either of the following methods to make the standby database available.
--*/

1. If the primary database is damaged and the latest logs cannot be backed up, you can directly use the following statement to make the standby database readable and writable (loss of all data after the last log restoration ).
-- Restore log db_test_bak with recovery

2. If the primary database can back up the latest logs, you can use the following statement.
-- First back up the latest transaction logs of the primary database
-- Backup log db_test to disk = ''c: est_log.bak ''with format
-- Restore the latest transaction logs in the slave database and make the slave database readable and writable (upgraded to the master database)
-- Restore log db_test_bak from disk = 'C: est_log.bak'

Simply put:

1. You must use the specified Windows user to log on to your SQL Service, instead of using the "local SYSTEM account"
2. users logging on to the SQL service must have all permissions on the shared directory.
3. If your computer is not added to the domain, you must also ensure that the login users set for the SQL service on the source and target servers are the same (the user names and passwords are the same)

Network Backup is mainly about permission settings. refer to the following Backup File Sharing directory permission settings to solve directory sharing permissions.

The following assumes the permission settings for the shared directory on which the database on server a is backed up to server B (the two servers should be in the LAN and allow shared directory access )::

1. Machine A and machine B create a Windows user with the same name. Set the user group to "Administrators" and set the same password as the valid user accessing the backup folder. The operation is as follows:

My computer -- control panel -- Management Tools -- Computer Management -- users and groups -- Right-click a user -- create a user who logs on to Windows from the Administrator Group

2. Create a new shared directory on the B server as the directory for storing backup files:

My computer -- D: Creates a directory named: bak -- Right-click the newly created directory -- properties -- share -- select "share this folder" -- use the "permission" button to set specific user permissions, make sure that the user created in step 1 has all permissions on the folder-OK

3. Set the start user of the MSSQLServer and SQLServerAgent services
　　
Start -- program -- Administrative Tools -- service -- Right-click MSSQLServer -- properties -- log on -- select "this account" -- enter or select the Windows login user name created in step 1 -- enter this name in "password "user Password -- OK -- set SQLServerAgent in the same way

4. Map the Bak directory of machine B on machine

5. Run the following statement in the analyzer to check whether the statement is successful:

Exec master.. xp_mongoshell 'Drive letter mapped to dir'

6. Prepare a backup plan on server

Note: to create a new user, the startup account of the MSSQLServer service must have the same name and password as the valid access to the shared directory, in this way, you can pass the verification (so you can use another valid user instead, only the user name and password must be the same and have sufficient permissions)