Data synchronization between SQL server servers 2

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,
The configured replication starts to run again after, and 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. The snapshot may need to be retried, and the copy 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
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