How to implement data synchronization in SQL Server 2005

Now if there's one such application, a game service provider in the promotion of a large game, now set up a number of database servers, in order to facilitate statistical data, the final data can be automatically transferred to the designated storage of another server, this time will face a problem like this, How do you guarantee the synchronization of data between these multiple databases?

We can use replication to copy and distribute a set of data or database objects from one database to another so that different server users can share the data within the permissions of the permission. With replication, data can be distributed to different locations on local and wide area networks, ensuring that data is automatically synchronized and updated at different locations to ensure data consistency.

In SQL Server 2005, replication primarily requires publishers, distributors, and Subscribers, and the server on which the source data resides is the publisher, responsible for publishing data. The publisher replicates changes to the data or objects that are replicated to the distributor, which is used by the Distributor to receive all changes to the data, contains a distribution database, saves the changes, and distributes the changes to subscribers. During the replication process, the publisher is a database instance that provides data to other locations through replication, and the Distributor is also a database instance, which acts as a storage area. A Subscriber is a database instance that receives replicated data. A subscriber can receive data from multiple publishers.

There are three main forms of replication: Snapshot replication, transactional replication, and merge replication. Snapshot replication distributes data at a specific point in time, without monitoring updates to the data. If synchronization occurs, the full snapshot is generated and sent to the Subscriber, and transactional replication typically begins with a snapshot of the publication database objects and data. After the initial snapshot is taken, data changes and schema modifications that are subsequently made at the publisher are usually passed to the subscriber when the modification occurs. The order in which data changes are applied at the Subscriber and the transaction boundary of the change is the same as at the publisher. Merge replication is often the beginning of a report snapshot of the publication database objects and data. Use triggers to track subsequent data changes and schema modifications made at the Publisher and Subscriber. Subscribers synchronize with the publisher when they connect to the network and exchange all rows that have changed between the Publisher and Subscriber since the last synchronization.

Now it's time to build data replication operations through SQL Server 2005来.

1, first on the two servers to establish two identical database, table structure and storage data;

2. Open SQL Server 2005 on server one, then select "Copy"--> "Local Publishing", then right-click to select New Publication;

3. In the New Publication Wizard, the first request to select the Distributor, in the operation we temporarily select the computer as a distributor, so select the default value.

4, select the path of the snapshot, generally choose the default path, according to the wizard to select the database to publish;

5, select the content of the publication, you can choose to publish the table, you can also publish other database objects, such as functions. After you select a table, you can also select the corresponding column;

6, after the release of the content set up, and then run the SQL Agent account. Then follow the wizard to create the duplicate name. This makes it a good place to publish and distribute applications.

Next, configure the subscription's application.

1. First open SQL Server 2005 on server Two, then select "Copy"--> "local Subscription", then right-click to select New Subscription;

2, select the Publisher, in the publisher's next list to find the corresponding already established SQL Server Publisher, and then you can see just the new publication replication name;

3, select the way to subscribe. Here you can select the pull subscription, and then select the local database to be synchronized;

4. After setting up the local database, the security of running agent is set up, and the SQL proxy account is set up. The subscription settings are complete.

Below we can do a demonstration of the operation, a new record on the server, and then go to the server two to look at the corresponding table data, you will find that, you will see the updated data.