SQL Server dual-Server setup and automatic data synchronization tutorial

Description: The hardware and software environments used are Windows XP SP2, SQL Server 2000 SP2 Personal Edition, General dual-core desktop, M LAN, and host A is an existing Server with databases and massive data, machine B is the new server. Both login modes are SQL server Authentication mode. Other settings are default settings, and no special settings are available. Another FTP server on machine A is used to automatically update programs.

1. Back up the database on machine.

2. Use the database backup files backed up by machine A to restore the database on machine B to ensure that machines A and B have identical databases. (It seems that this step does not need to be done after it is completed. If it is found that it has been completed, it will not be re-verified ).

3. Create two users with identical usernames and passwords (Control Panel> Computer Management> User> New) on host A and host B and add them to the administartor group. (If the two machines used to log on with the administartor user, you can change the login password of the two machines to the same without creating a new user)

4. perform this operation on both machines: Start> program> Management Tools> services> right-click SQLSERVERAGENT> Properties> login> set to the user name and password obtained in the previous step.

5. Enter the Enterprise Manager on machine A, add server B to the server group, enter server B in the Enterprise Manager, and add server A to the server group. In order to save trouble, I used Sa to log on to SQL Server from both hosts.

6. Machine A enters the Enterprise Manager, right-click the database to be released, choose new> Publish, and proceed step by step. No special settings are required, there will be an additional "publish" entry under the database, right-click> attribute, you can change the publishing attribute settings. Pay attention to the following points: 1. the proxy login user must use the user set in step 2, and the "system user" cannot be used. 2. The database access user must be specified (I am using the Sa, save trouble). 3. Set the directory where snapshots are stored on machine A to be shared. If snapshot is not shared, machine B will not be able to get snapshots (I am not using the default SQL directory, manually specified as the root directory of the FTP server for detailed reasons), 4 is to pay attention to the frequency of publishing proxy should not be set too fast, the snapshot file must be generated, and then notify the subscriber, it may take some time for a subscriber to get data again. As for how fast it will take, let's set it based on your data change frequency and the speed of two-machine synchronization. For me, the loss of data in one day is completely tolerable. Therefore, I set the release every five hours.

7. Machine B enters the Enterprise Manager, right-click the database to be synchronized, choose create> subscribe, and proceed step by step without any special settings.

8. After the synchronization is completed, it is reasonable to say that the synchronization can be completed, but I found no effect. After checking the log of the release manager on machine A, I found that the snapshot file on machine B cannot be obtained, at first glance, the original SQL Server subscription port is actually 445, which is the same as the virus sweeping wave, so it was blocked by the firewall and blocked, and I do not have the permission to modify the firewall interception rules. Therefore, in the Publishing Server properties, specify the snapshot storage directory as the FTP root directory, enable FTP, enter the FTP server access username and password, and re-run the publishing agent. Delete the original subscription on machine B, re-establish the subscription, and set the FTP MODE, so the problem is solved.

PS: According to the online reference materials, the synchronized table requires a primary key, but some tables in my system do not have a primary key (because each column in the table has several identical values, therefore, you cannot add a primary key, and you are too lazy to modify the table structure to add a sequence. Because you need to add a sequence, you have to change the program, for fear --!), Finally, the synchronization is also successful. SO, whether the table has a primary key does not affect synchronization, at least does not affect the snapshot synchronization mode.