Replication synchronization data for SQL Server R2 database

For the database replication function has not been used, just this time need to do data synchronization between the database, the database replication synchronized collation. Database synchronization can be the synchronization of data between different databases in the same server, or between different servers in a LAN. Because this is done in the LAN two servers on the data synchronization between the database, for the same LAN is not two or more servers in the database synchronization can be achieved, temporarily did not do the test, there is no in-depth understanding, so do not know whether it can be achieved. There is no place to look at a lot of advice.

The following specific steps, first of all, we talk about the release server on the preparation and conditions, the SQL Server Agent is set to self-start, to the transactional publication, to ensure that the database table to have a primary key, a new folder, set to share.

First we set up a configuration distribution, if you have previously configured distribution settings, is the right side effect, you can create a local publishing directly.

　　

Then, the next step, take a look at the snapshot folder

　　　

Next, if you start without setting up SQL Server Agent for self-booting, you will be prompted for this

　　

Next, this place fills in the first created shared folder address, such as F:\ShareFolder

　　

Next, fill in the default

　　　　

Next, tick Configure distribution

　　　　

Point to complete, you can see:

Now that the configuration distribution is good, then you create a local release,

　　

Next select the database to synchronize

　　　　

This place chooses a transactional publication, each of which can be seen in the description, and later on

　　

Next, if your database has views and so on, it will be displayed, tick the object you want to synchronize

　　

Next, add the filter item, add it if necessary, do not need to directly next, add the interface right figure

　　　

Do not add here, next, tick the first item

　　

Next, click Security Settings, as follows:

　　　　

The following are determined:

　　

Next, tick create publication, Next, fill in a release name

　　

Click Finish

　　

The local publication is created, and then replication-local publishing can see the local publish test that was just created

　　

Create a local release to create a subscription, in order to facilitate the user to do the subscription, if the other server is the same as the subscription process is just a different choice of publisher, the following

　　

Also next, if this place is in a native subscription, select database and publish test directly, and if you are subscribing to a publication on another server, you can expand the Publisher and select "Find SQL Server Publishers"

　　

This time will pop up the database login interface, you need to note that the server login to use the computer name, cannot use IP address or alias, also note that this place to link the publisher, because ensure that the database on the Publisher to allow remote links. After the link on the interface will be shown on this server's release, choose a good release, Next, tick the first item.

　　　

Next, tick on the Subscriber, select the subscription database, just choose the new database can be. The Create Database dialog box appears, creating a database,

　　

Next, set up security

　　

OK, next, synchronize plan select continuous run, Initialize time selection immediately

　　

Next, select Create a subscription now, and the next step is complete. At this point, expand local and local subscriptions under replication, and you can see that both the publication and the subscription exist. and the subscription database Testdatady just created also exists in the database.

All the work has been done, the following is the test. Because we use TestData as the database for publication, Testdatady is the database of the subscription, so we add or modify the database in the table of the TestData database to see if the data in the Testdatady database also changes with the response. The process of testing can be done manually.

Next we say something else, the first is the shared folder we created at the beginning, he called the snapshot folder, as the name implies is used to store the snapshot. We can find this folder and open it and look at it, the first is an empty folder, when I create a publication and a subscription, here you can see that there are four files named after the published database. These are the initial schemas and data for the tables in the database testdata.

　　

Looking back at the subscription database created when the subscription was created, we just created the database, and the tables, fields, and so on in the database were not created, and when we created the subscription, the tables and fields were created automatically, through the initial schema and data in the snapshot folder.

Another is the distribution database distribution, which he can find, which he exists in the system database.

　　

You can see that this data is a table and a field, the main purpose of this database is to store history, errors and new transactions.

Then there is the release type, which has a snapshot publication, a transactional publication, a transactional publication with updatable subscriptions, and a merge publication.

　　Snapshot publication: The publisher sends a snapshot of published data to the Subscriber at a predetermined interval. His working mechanism is: the publisher, the database to be published a snapshot of the whole; The subscriber's Snapshot Agent reads the snapshot from the publisher and places it in the local snapshot folder; The subscriber's publishing agent publishes the snapshot from the snapshot folder to the Subscriber. History and snapshots are recorded at the Distributor.

Thing publishing: After the Subscriber receives the initial snapshot of published data, the publisher streams the transaction to the Subscriber. His rationale: The initial data and architecture (done with snapshot replication), from which snapshot replication is the basis for all replication. Snapshot replication establishes a subscription database at the Subscriber, the Publisher's data is modified, the transaction log is written, and the log Read agent of the distributor reads the transaction log of the changed data, saves the transaction logs at the publisher's publication database, and the Distributor's Distribution Agent Distributes the transaction logs from the distribution database to the individual subscribers, and then records the history and errors in the distribution database.

Transactional publications with updatable subscriptions: After the SQL Server Subscriber receives the initial snapshot of published data, the publisher streams the transaction to the Subscriber. Transactions from Subscribers are applied to the publisher.

Merge publication: After the Subscriber receives the initial snapshot of published data, the Publisher and Subscribers can independently update the published data. Changes are periodically merged. Microsoft SQL Server Compact Edition can only subscribe to merge publications.

The differences between the several releases are:

1, snapshot Publishing and transactional publishing are unidirectional, that is, a->b; merge release is bidirectional, A->b,b->a.

2. Snapshot replication replicates all data, while transactional replication and merge replication are changes to replicated data.

This is the entire process of replication, but this is simply a data synchronization, the actual project can be selected according to the requirements of the release type and configuration. There is no place to look at more advice.

Replication synchronization data for SQL Server R2 database