Master-slave replication of the SQL2000 database

Today because of job needs, wrote this article, SQL2000 estimated that many people do not have, I think writing down is a way of thinking.

Real Environment: A zone: A server, 3 clients, B Zone: 1 servers, more than 10 clients. Zone A and B network interoperability. A zone client, the server all Access Zone B primary server, Zone B client access to Zone B server.

Fault: Zone A to Zone B network drops, causing the client in zone A to respond slowly to the server in zone B. (line distance is too long, network temporarily can not solve the problem of packet loss)

Requirements: A-zone server on the database synchronization of the data on the server B, the A-zone client access to a zone server, the zone a client for smoother use.

Results: After the experiment, it was found that the master server data was overwritten when the zone a client, and the B-zone client simultaneously inserted the same record as the primary key.

Conjecture: Global enterprises, should have branch offices all over the world, how to achieve the end of data writing? Welcome message to tell me. can also be discussed together.

===================================================================================================

Lab Precautions:

1: After the two computer systems are installed, be sure to first modify the machine name and so on, restart the computer.

2: Install the database separately,

3: Check the computer name and the database under the machine name is consistent

Under Computer: CMD input hostname

SQL query interface: SELECT @ @servername

If it is consistent, continue.

===================================================================================================

Experimental process:

1: Prepare two virtual machines, install the system, database, modify the computer name and so on.

2: Open Enterprise Manager on the primary server, add the administrative group, and then add the primary standby server to the SQL group.

(some say that using an IP address in Enterprise Manager will fail, I have no test.) Supposedly, IP should be no problem. ）

Select the database you want to copy, select Tools, copy, configure publish subscribers and publish,

3: In the Open interface all the way to the next step until the completion, the work can be appropriately modified.

4: Select the publication in the pop-up screen, select New Publication, select the desired database,

5:

Note here:

The first item: a certain period of time from the main server to update themselves, but the standby server can not make any changes, such as operations, can be queried.

Second item: The standby server is updated from the primary server, and the operations on the standby server are not synchronized to the primary server.

(Note Insert, when the primary server inserts a record, the standby server inserts an identical record, the standby server this record insertion failure (prompt insertion failure))

The third primary and standby server is updated with each other.

(Note that a record with a primary key is inserted at the same time, when the primary server inserts a record, the standby server inserts an identical record, this record is overwritten (no prompt for insertion failure), that is: cannot insert a primary key

Records).

6: All the way next, to, select the copied object, next. Until finished.

7: Complete the structure

8: Select the standby server in the SQL group. Select, copy under Subscriptions, right-click in the blank section on the right, select, create new pull subscription,

9: Select Next, according to the appropriate environment selection, we choose to "view the publications of registered Servers," in the next step.

At the select Point Open just the publisher we did choose the published task, in the next step,

10: This may happen because we did not check the Subscriber when we configured the publication, the Subscriber,

11: Cancel below, we go back to the publisher, select Copy, publish content, select "Configure Publish and subscribe". "Check the subscriber, OK after returning to our subscriber."

12: The back can be very simple, according to the need to choose, I will no longer. At this point our SQL2000 master-slave replication is complete.

13: Verify that you can insert data into the Query Analyzer to verify.

===================================================================================================

Master-slave replication of the SQL2000 database