Publish and subscribe to SQL Server

Publish and subscribe in SQL Server

In the migration of the database, there will be many methods, with stored procedures, jobs, or open source tools to Lettle, then today these days have become exposed to a new approach, is SQL Server's own publishing and subscription.

Let's start by explaining the process of data replication. If a is a database on (192.168.210.170), B is a database on (172.23.100.109). B is treated as a data source, and a gets the data from B.

Pre-release: first two servers to be able to communicate with each other, that is, can ping command to pass.

Usually when we link the database, often use the IP login, but the release of the time can not be so, must use the server name, or will error, such as.

　　　　　　　　

There are two ways to solve this problem:

The first method (remote): ① Query the name of the database server, the query statement "SELECT @ @SERVERNAME".

②AB two servers on Remote Desktop login.

③ uses Management Studio on the server to connect to the native database (note: You cannot use the IP to link, the first step to check out the name of the connection).

④ follow the steps below to publish your subscription.

The second method (local): ① Query the name of the database server, the query statement "SELECT @ @SERVERNAME".

② use my local computer C, find C:\Windows\System32\drivers\etc This path, will be in the Hosts file the IP address of a and the first step to query out the name of the server to add, B is also. This is also the mapping.

③ Open the local Management Studio to connect to the AB two database (this should also be used to query the server name).

④ follow the steps below to publish your subscription.

Here the experiment is the second way, and then it's officially started.

1. Release

　　

① "Replication"-"Local Publishing"-"New publication" is found under Database B server.

　　

② Selecting a database to publish

　　

③ Select a publication type, here you select Snapshot Publication.

　　

④ chooses what to publish, which data to publish.

　　

⑤ data filtering, you can filter the line in this step, of course, you can not set conditions, select all the data.

　　　　　　

⑥ setting up the Snapshot Agent and changing the synchronization frequency

　　

⑦ security settings, that is, configure login type, login account password.

　　

　　

⑧ a name for the publication name, I got a LS (John Doe)

　　

Finally, at this point, the release is complete, and the next step is to look at the subscription.

2. Subscription

The subscription synchronizes the snapshot that was just released, synchronizing the data source data to the target database.

① on Server A, "Replication"-"Local Subscription"-"New subscription"

　　

② Choosing a Subscription publication

　　

　　

③ Select the location of the Distribution Agent and select a simple push subscription.

　　

④ Setting the target database for subscribers

　　

⑤ sets the proxy security, which is the subscriber's account password.

　　

⑥ set the synchronization schedule, where you choose to run continuously.

　　

⑦ Click Finish, the subscription configuration is complete.

　　

⑧ view monitoring. On local publishing-start Replication Monitor, you can view the running status of the snapshot and other details at the point of opening.

　　

At this point, both the publication and the subscription are complete. In Server A in the one database, you will find a more AAA data table, which indicates that the B server data to a. When Server B modifies the record (source data) in the AAA table, the record (target data) in a is also changed.

Summary: Engaged in a few days this data release and subscription, but feel this is not very easy to use, flexibility is not good, feel like kettle This open source software with convenience.

Publish and subscribe to SQL Server