SQL Server Database Synchronization Solution

Last Update:2018-12-05 Source: Internet

Author: User

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

SQL Server Database Synchronization is a commonly used part of the project. If data in a project is stored on different database servers, and these databases need to be called by multiple domains, configuring SQL Server Database Synchronization is a good solution. The configuration of SQL Server Database Synchronization is complicated. The following describes the detailed configuration steps:

I. Prerequisites for Data Replication

1. The database fault recovery model must be a full restoration model.

2. All synchronized data tables must use the primary key.

3. The publisher, distributor, and subscription server must use the computer name to register the SQLSERVER server.

4. SQLSERVER must start the proxy service, and the proxy service must run with the account of the local computer.

II. Implementation steps for solving the prerequisites

1. Adjust the database fault recovery model to the full recovery model. The procedure is as follows:

Open SQLSERVER Enterprise Manager and select the corresponding database. Right-click à.

2. All synchronized data tables must use the primary key. (Mainly refers to transaction replication) if there is no primary key for the data table, add a field name called id, type: int type, marked as auto-increment 1 field.

3. The publisher, distributor, and subscription server must use the computer name to register the SQLSERVER server.

The servers registered in the enterprise manager must be registered with the server name if they are used as the Publishing Server, distribution server, and subscription server. Do not use IP addresses or aliases for registration, such as LOCAL, ".", or LOCALHOST.

If the network segment or remote server is not the same, add the corresponding relationship to the local system network configuration file. The file is located at % systemroot % \ system32 \ drivers \ etc \ hosts.

Configuration method: use NotePad to open the hosts file and add the ing between the IP address and the host name at the bottom of the file. :

SQLSERVER must start the proxy service, and the proxy service must run with the account of the local computer.

How to start the SQLSERVER agent: Right-click my computer and choose "manage"> "service"> "SQLSERVERAGENT" to enable it automatically. :

Configuration method for running with a local computer account: Open SQLSERVER Enterprise Manager and select SQLSERVER proxy. Right-click Properties. Change the system account to a local account, which requires the permission of the system administrator. This account is generally set to administrator.

After the preceding prerequisites are met, you can configure the database replication service.

3. Understand the concept and principle of replication Configuration

1. Data Replication role

The replication service has three roles: the Publishing Server, the distribution server, and the subscription server. They do different jobs respectively. Just like the concept of purchasing books and newspapers on a daily basis.

Publishing Server: A publishing server that is mainly responsible for data publishing and publishing. This role is like our press or newspaper.

Distributor: it is mainly responsible for distributing the content of the publisher to subscribers. It serves as a bridge between the publishing server and the subscription server. This role is like our postman who delivers books and newspapers to us.

Subscription server: mainly responsible for receiving published content. This role is like subscribing to books and newspapers by ourselves. It is the role of a subscriber.

2. Data subscription Mode

Data subscription modes include push subscription and pull subscription. Push-type subscription mainly refers to the distribution server pushing data to the subscription server. The pull subscription server actively retrieves data from the distribution server.

This is like subscribing to magazines and newspapers by ourselves. If people deliver goods to their homes, this is push-type subscription, which consumes the resources of the distribution server, that is, the resources of the delivery personnel. If it is pull-type subscription, We need to purchase it at the bookstore by ourselves, which consumes our own materials. Resources of the subscription server are consumed.

3. Data publishing type

Three data release types can be released (SQL2000 ):

A. Copying snapshots

When one or more of the following conditions are met, using snapshot replication is the most appropriate:

· Data is rarely changed.

· Data copies that are outdated with the Publishing Server are allowed for a period of time.

· Copy a small amount of data.

· A large number of changes in the short term

B. Transaction Replication

Transactional replication is usually used in server-to-server environments. It is applicable to the following situations:

· If you want to transmit an incremental change to the subscription server.

· When a change occurs on the publishing server and the change arrives at the subscription server, the application requires a short lag time between the two.

· The application needs to access the intermediate data status. For example, if a row is changed five times, transactional replication allows the application to respond to each change (for example, to trigger a trigger), not just to respond to the final data change of the row.

· The publishing server has a large number of insert, update, and delete activities.

C. Merge and copy

Merge replication is usually used in the environment from the server to the client. Merge replication applies to the following situations:

· Multiple subscription servers may update the same data at different times and spread the changes to the Publishing Server and other subscription servers.

· The subscription server needs to receive data, change the data offline, and synchronize the changes with the publisher and other subscription servers in the future.

· Each subscription server requires different data partitions.

· Conflicts may occur. When a conflict occurs, you must be able to detect and resolve the conflict.

· The application needs the final data change result instead of accessing the intermediate data status. For example, if the row on the subscription server is changed five times before the subscription server synchronizes with the publishing server, the row is changed only once on the Publishing Server to reflect the final data change (that is, the value of the fifth change ).

Iv. Data Replication steps

A.Configure the Publishing Server

Open SQLSERVER Enterprise Manager. Select the registered SQL Server. Wizard.

After the selection wizard is displayed, select "Configure release and distribution wizard" and the following wizard appears:

Click "Next"

Select this server as the distributor and distributor, and click Next.

Select the snapshot folder. Note that the network path is required here.

Click Next,

Use the default settings. Click "Next" and click "complete". The "Configuring" dialog box appears, as shown in the following figure:

After the configuration is complete, the following dialog box is displayed:

In this case, our server has been configured successfully!

Click OK. The following window is displayed. For example:

In this case, SQLSERVER Enterprise Manager has a replication monitor. For example:

After the publishing server is configured successfully, we can start to configure the Publishing Server.

B. Configure the distributor

Distribution can be configured on the distribution server or other SQLSERVER

Server. The distribution server and the distribution server are configured on the same server. The following describes how to configure a database to be published:

Open SQLSERVER Enterprise Manager. Select the registered SQL Server. Wizard.

After the selection wizard is displayed, click Copy and select create release wizard. Select the database to be released. For example:

After you click "create release", the wizard appears. Click "Next" and the following page appears:

This interface requires that you select the database to be released. The system automatically selects the database selected for the first time. You only need to click "Next" and the following interface appears:

In this case, you need to select the release type. Based on our description of the release type and network bandwidth, You need to select the appropriate release type. snapshot release and transaction release are commonly used. The following uses the two releases as an example for configuration. First, select snapshot release and click "Next". The following page is displayed:

The wizard prompts us to select the subscription server type. Because we are using

For SQL server 2000, select "run SQL SERVER 2000 SERVER" (default system option) and click "Next". The following page is displayed:

Select a database object for release. You can select all the tables, stored procedures, views, and user-defined functions to be released. Click "release all" in the table and click "Next". The following page is displayed:

Because we use snapshot replication, there will be a problem that the "IDENTITY attribute is not transmitted to the subscription server (that is, the attribute of int type is not converted to auto-increment 1, but the data will be transmitted.) Click next and the following page is displayed:

After the preceding interface is displayed, click "Next" again. The following dialog box is displayed:

You can select to filter the defined data, such as vertical (data column) or horizontal (Data row. Select "no" (default) and click "Next", for example:

The configured page appears. Click Finish. For example:

After the Publishing Server is created, the following page is displayed:

As a result, the configuration of the Publishing Server is complete.

In this case, we can use the replication monitor to view the operation of the replication agents on the existing servers. For example:

After the release server is configured, the released database has an additional icon. For example:

Note: If you need to perform transaction replication on a database, you only need to select "transaction publishing" in the distribution type. For example:

Other operations are the same as snapshot replication. Note that "transaction replication" requires that the data table must have a primary key; otherwise, the table cannot be published.

After the publishing server configuration is complete, we may have configured the subscription server.

C.Configure the subscription Server

There are two ways to subscribe to the server. One is push-type subscription and the other is pull-type subscription.

Select the subscription method. Consider the following factors:

① For network considerations, for example, the Internet remote server needs to subscribe to local data. Because the local server does not have a public IP address, it needs to adopt a local push-type subscription to the remote server, that is, a forced subscription.

② Considerations for server performance, for example, the subscription server and the distribution server are both Internet IP addresses or Intranet IP addresses. However, it is required that the replication process will not put too much pressure on the distribution server. In this case, we can adopt the pull subscription method. The pull subscription consumes resources of the subscription server, but does not significantly affect the performance of the distribution server.

Push subscriptionThe specific configuration is as follows:

Open SQLSERVER Enterprise Manager. Select the registered SQL Server tool Wizard to copy and create a forced subscription wizard. For example:

Select the database to be forcibly subscribed, and click Force New subscription. A welcome wizard is displayed. For example:

Click "Next" and select the subscription server, for example:

Click "Next" to display the name of the database subscription, for example:

In this case, create a suitable database name "Browse or create" to select the current

Some databases or create a new database. For example:

To create a new database, click "CREATE". The "create database" dialog box is displayed. For example:

After creating a database, click "Next", for example:

You can use the shortest latency to select when the distribution agent updates the subscription content.

You can choose to use scheduling. In this case, we select a continuous pool (set by default) and click "Next". The following page is displayed:

Click "Next" to initialize the Architecture and Data. The system checks the working status of the SQLSERVER proxy service. For example:

If the status is "running", click "Next". If the status is unknown, check whether the SQLSERVER proxy service is started. If not, start the SQLSERVER proxy service. Click "Next,

The configuration completion page appears, for example:

Click "finish" and start configuring subscription. For example:

Now, the push subscription configuration is complete. (The pull subscription configuration is basically the same as this. You need to configure the request subscription on the subscription server by using the copy wizard)