SQLSERVER2005 database synchronization configuration graphics and text detailed _mssql2005

<1> Software Preparation conditions
Machine A-side: SqlServer2005 Management Studio + winserver 2003 Enterprise (as publisher)
Machine B-side: Sqlserver2005 Management Studio Express + WinXP (as subscriber)

One: "Preparation conditions"

<1> Software Preparation conditions

Machine A-side: SqlServer2005 Management Studio + winserver 2003 Enterprise (as publisher)

Machine B-side: Sqlserver2005 Management Studio Express + WinXP (as subscriber)

(You can use something else, but the Subscriber version must not be higher than the publisher version)

<2> Database Replication Readiness Criteria

1. All synchronized data tables to use primary key, if there is no primary key does not matter, SQL Server will be prompted to automatically generate primary keys for the table, as shown

2. The Publisher, Distributor, and Subscriber must use the computer name to register the SQL Server servers.

3. SQL Server must start the agent service, and the agent service has to run with the account number of the local computer. You may not register with an IP address or alias, such as local, "." and localhost and so on.

If not the same network segment or remote server, or can only use IP, can not use the computer name, for each other to register the server alias. Add the corresponding relationship to the local System network configuration file. The exact location of the file in C:\Windows\system32\drivers\etc\hosts

Configuration mode: Use Notepad to open the Hosts file, at the bottom of the file add IP address and host name of the corresponding relationship. As shown in figure:

4.SqlServer The agent service must be started and the agent service must be running on the local computer account.

5. The Publisher and Subscriber have to set up MSDTC to allow the Network access Control Panel---> Administration Tools---> Component Services---> Computer---> My computer (Win7 keep looking for-->distributed Transaction Coordinator---> Local DTC), right-click Properties, locate the MSDTC tab, click Security Settings, and follow the settings below

)

OK, restart the MSDTC service.

Second: Detailed procedures for the preparation of work

1. At the publisher, create a new shared directory, as a repository for the snapshot files for the publication, and add everyone's full control rights to the shared directory.

2. Determine the database structure used for publishing at the publisher to be used for subscriptions to the databases and Subscribers, or the subscriber to create a new empty database (without creating a table).

3. Set the Publisher and Subscriber database owners to SA.

4, start the SQL Agent (SQLServerAgent) service.

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

6. Set SQL Server Authentication to mixed authentication SQL Server and Windows (publish, subscriber all set)

7. Publisher and Subscriber register with each other

The steps are as follows: View---Click the registered server---à right-click the database engine---à new server registration----à fill in the name of the remote server you want to register----à authentication select SQL Server authentication----à user name (sa) password, for IP only, You cannot register a server alias with a computer name, see above.

Prepare for work, and then we'll publish and subscribe to the operation

Second, "Publish and subscribe"

One: Publish

(1) First we open SqlServer2005 Management Studio (Enterprise Edition) at the publisher (winserver2003). In Object Explorer, locate the replication node, right---> New Publication (Note: If your SQL Server is Express version, it is not released this feature, only subscribe to the function!)

(2) Next select the database to publish, Next, select "Publication type", where I choose "Merge publication"

(3) Next, select the Subscriber type, select "SqlServer2005", Next, select the Publishing project, we select tables, views, stored procedures, and so on.

(4) If your table does not have a primary key, SQL Server prompts you to automatically add a primary key for a table that does not have a primary key.

(5) Next set up the job plan, in order to easily see the test effect, we set "every minute" to perform a job.

(6) Set the Snapshot Agent security, when connecting to the publisher, use the simulation process.

(7) To publish a name, complete the release

Two: Subscribe

Next, we configure the Subscriber. The Subscriber is a WinXP system, SQL Server version is SQL Server, "s. Manager Studio Express, and only subscriptions are supported."

(1) With the publication, new subscription

(2) Select the Publisher, find our newly created release "Test3copy", Next

(3) Select the location of the running agent, according to the requirements of the choice is "push" or "request"!

(4) The next similar to the release, here is not to repeat, the success of the subscription, in the subscription database and publish the database table will be more than one field, SQL Server automatically generated! flag subscriptions are successful.

Third, "View sync status"

View synchronization status, which can only be viewed at the publisher. Specific steps: (the publication in the following figure is not the same as the one mentioned above)

(1) Open "copy"--> right-click publish name--> "Enable Replication Monitor"

(2) Expand the "My publisher"--> Publisher machine name-—— > publication name. On the right you will see the status of the subscriber "synchronizing." Right, select Properties to open the detailed description.

(3) When you open a property, a dialog box for synchronization history pops up. Here are the records for inserts, updates, deletes, and so on.

Of course, there are many details in this article may not be so clear, inevitably there will be some problems, I will later in the next blog to write about the problems encountered and solutions. Do you have any questions, welcome to talk to each other