SQL Server 2005 Synchronous replication Technology _mssql2005

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 (Publisher and Subscriber are set)

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.

3. Right-click Local subscription--------Select the Publisher-------Select a subscription (if it is the server-side subscription Select the push subscription instead of a pull subscription)-------Fill in the Subscriber--------Select the agent schedule (generally choose to run continuously)---------the rest Select the default 。

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!

After installing Microsoft SQL Server 2005, it is found that when connecting to a server from Microsoft SQL Server Management Studio, the connection always prompts for failure if the server name is not filled in with the machine name but the IP address. The prompt message is:

Unable to connect to 10.114.*.*
Additional Information:
A connection was made to the server successfully, but an error occurred during the logon process. (provider: Named pipe provider, there is no process on the other end of the error:0-pipe.) ) (Microsoft SQL Server, error: 233)

Workaround: Open the Start-All Programs-microsoft SQL Server 2005-Configuration tool-sql Server Configuration Manager, and in the form that pops up, find "SQL Server 2005 Network configuration," under "MSSQLSERVER protocol" Named Pipes and TCP/IP are enabled, and then restart SQL Server.


Here's a simple description
1, on the primary DB Server (MASTERDB), set up SQL Server authentication mode, login: aa, Password: AA
2. The database structure in the primary server is imported into the backup machine.
3, on the main server into the SQL Server Management Studio, select the Write node, right click on the local release set, set the distribution.
4. Enter SQL Server Management Studio on the primary server, select the Write node, right-click the native release set-new release set-
Next--Select the database to sync--the next release set type Select a transactional release set (several other types have their descriptions, the root
According to different needs to choose a different type)--Next in the published object to select the table to synchronize--next--Next select immediate
Set up a snapshot set ...--Next click on the security Settings option to execute with the SQL Server Agent service account, connect to the Publisher selection
Use the following SQL Server login method, enter login name, password and Confirm password--OK--complete Set release set name--
Complete.
5, on the backup server into the database management platform, select the Copy node, right click on the local subscription--new subscription--Next set
Place Publisher (primary server)--next--on the sender side--next--next--Set attributes--determine--
Complete. Ok!
Next you can test, add a record to the primary server or modify a record to view the related notes on the backup server
The record has been modified in fact and has been synchronized.
Because I use the traditional system, so are traditional characters, but the simplified is some of the terminology easier to understand, a look at the understanding.