Synchronize data between two SQL Server databases-list the steps for configuring replication in sequence

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)

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.

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 reconfigure with override go

Question:
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 functions ,,,

/* -- Description: The following Code Demonstrate 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 for demonstration (master database) 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: \ test_data.bak 'with format Go -- Restore the database to a backup database (demonstrate synchronization between the master database and the backup database) Restore database db_test_bak from disk = 'C: \ test_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 jobs for synchronization between master server data training and slave server database Declare @ jogid uniqueidentifier Exec MSDB .. sp_add_job @ Job_id = @ jogid output, @ Job_name = n' Data Synchronization Process' -- Create synchronization process 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: \ test_log.bak ''with format -- Restore the log backup of the master database in the backup database (the latest changes in the application master database) -- 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 stored in the shared directory that can be accessed by both the primary server and the backup server. Restore log db_test_bak from disk = ''c: \ test_log.bak ''with standby = ''c: \ test_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. -- 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 -- Query the slave 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 -- 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: \ test_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: \ test_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 the user
-- Create a user
-- Create a Windows login user affiliated to 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
-- Property -- share
-- Select "share this folder"
-- Use the "permission" button to set specific user permissions to ensure 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 -- management tool -- service
-- Right-click MSSQLServer
-- Property -- login -- select "this account"
-- Enter or select the Windows logon user name created in step 1.
-- Enter the user's password in "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)