Application of SQL SERVER 2005 Synchronous replication Technology

This article takes snapshot replication as an example to describe the application of SQL SERVER 2005 Synchronous replication technology.

The following implementation replication steps (take snapshot replication as an example)

Running platform SQL SERVER 2005

First, the preparatory work:

1. Establish a WINDOWS user, set as administrator rights, and set a password as a valid access user to the publication snapshot file.

2. The communication between the Publisher and Subscriber is normal under SQL Server (that is, you can exchange visits). Open port 1433 and set a special case in the firewall

3. Create a shared directory at the publisher as the repository for the publication snapshot files. For example: in the D-Packing directory to build a folder named Sqlcopy

4. Set up SQL Agent (both Publisher and Subscriber settings) This article is published in www.xker.com (Small technology Network)

Open Services (Control Panel---management tools---services)

---Right-click the SQL Server AGENT---Properties---login---Select this account

---Enter or select the Windows user created in the first step

Enter the user password---"Password"

5. Set up SQL SERVER authentication, resolving permissions issues when connecting (publish, subscriber set)

The procedure is: Object Explorer----Right-click the SQL Instance-----Properties----Security----Server Authentication------Select SQL Server and Windows, and then click OK

6. Open the Network protocol TCP/IP and Pipe naming protocol for SQL Server 2005 and restart network services.

7. In SQL Server, create the corresponding system user login name in step 1 as the owner of the publication database (set to Dbo_owner and public).

8. Log into SQL Server with System Superuser SA to establish database and tables.

9. Publisher and Subscriber register with each other

The steps are as follows: View----Click to register the server----right key database engine----New Server registration-----Fill in the remote server name to register------authentication Select SQL Server Authentication-----user name (SA) Password------Create a group (also can not be built)-----completed.

10. Register a server alias for IP only, not computer name

Second, the beginning:

Publisher configuration (Configuring publications and subscriptions at the publisher)

1. Select a Replication node

2. Right-click Local publishing----next---------the System pop-up dialog box to see the prompts----until "Specify Snapshot folder"

----Enter the directory created in the preparation work in the snapshot folder (point to the shared folder that you built in step 3)------ Select the publication database-------Select the publication type-------Select the Subscriber type-------Select the object to publish------set the Snapshot Agent-------fill out the publication name. This article is published in www.xker.com (Small technology Network)

3. Right-click Local subscription--------Select the Publisher-------Select a subscription (if it is a server-side subscription select push subscription instead

Select a pull subscription)-------Fill in the Subscriber--------Select the agent schedule (typically run continuously)---------the rest of the default items selected.

At this point, SQL SERVER 2005 synchronous replication is complete. With replication technology, a user can publish data from one client to multiple servers so that different server users can share the data within the bounds of permission. Replication technology to ensure that the data distributed in different locations automatically synchronized updates, so as to ensure the consistency of data, there is no need to programmatically implement client and server-side data synchronization! Greatly improve the work efficiency!