Sql2005 Data synchronization Method _mssql2005

Mainly to pay attention to the issue of permissions, general do publish/subscribe, suggest you do the following preparation work:

1. Publisher, Subscriber creates a Windows user with the same name and sets the same password as a valid access user for the publication snapshot folder
My computer
--Control Panel
--management tools
--Computer Management
--Users and groups
--Right key user
--New user
--Establish a user who is logged on to Windows in the group of Administrators
2. At the publisher, create a new shared directory as the repository for the published snapshot files:
My Computer--d:\. Create a new directory named: PUB
--Right key to this new directory
--Properties--shared
--Select "Share this Folder"
--Set specific user rights by Newlay by "permissions" to ensure that the user created in the first step has all permissions on the folder
-Determine

3. Set up the startup user for the SQL Agent (SQLServerAgent) service (Publish/subscriber does this setting)
Start--Program--management tools--Service
--Right Key SQLServerAgent
--Attribute--Login--Select "This account"
--Enter or select the Windows logon user name created in the first step
--Enter the user's password in "password"
4. Set SQL Server Authentication mode to resolve permissions issues when connecting (publish/subscriber does this setting)
Enterprise Manager
--Right key SQL instance--Properties
--Security--authentication
--Select SQL Server and Windows
-Determine
5. Registering with each other at the Publisher and Subscriber
Enterprise Manager
--Right-click SQL Server Group
--New SQL Server registration ...
--Next--Available servers, enter the name of the remote server you want to register--add
--Next--Connect using, select Second SQL Server Authentication
--Next--Enter user name and password
--Next--Select a SQL Server group, or you can create a new group
--The next step--complete
6. Register a server alias for a computer name that can only be used for IP
(at the end of the connection configuration, for example, at the Subscriber, the server name is entered in the IP of the publisher)
Start--Program--microsoft SQL server--Client Network Utility
--Alias--add
--Network Library Select "TCP/IP"-Server alias input SQL Server name
--Connection parameter--Enter SQL Server IP address in server name
--If you modify the SQL port, deselect "Dynamic decision port" and enter the corresponding port number
##############################################################
Add
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!