SQL Server dual server Setup and Data automatic synchronization tutorial _mssql

Account: The use of software and hardware environment for the win XP SP2, SQL Server SP2 personal version, ordinary dual-core desktop, 1000M LAN, a machine for the use of the server, the above has a database and massive data, B machine for the new server, two-machine landing mode are SQL Server authentication mode, other settings are default settings, no special. There is also an FTP server on the machine for Automatic Updates.

1. Perform database backup on a machine.

2, with a computer backup down the database backup files on B machine to perform database recovery, to ensure that a, B machines have exactly the same database. (after finishing found that this step seems to do not have to do, anyway, when I found that I have finished, there will be no restart verification).

3, on a, B machine built two user names, passwords exactly the same users (Control Panel > Computer Management > Users > New), and add them to the Administartor group. (if both machines are used Administartor user login, then, the two machine login password changed to the same line, do not have to build new users)

4, both machines on the operation: Start > Program > Management Tools > Services > Right-click sqlserveragent> properties > Login > Set to the last step of the username and password.

5, on a machine into Enterprise Manager, the B-machine server into the server group, B machine into Enterprise Manager, the a machine server to join the server group. To save the trouble, I used the SA for two machines to log on to each other SQL Server.

6, a machine into Enterprise Manager, right-click the database you want to publish > new > Publish, one step down, no need for special settings, after the walk, the database will be more than a "release" entry, right click > properties, can change the publication properties of the settings. Note A few points: 1 is the user of the proxy login to be set up in step 3rd, cannot use "System user", 2 is the database access user to specify (I am using the SA, Save Trouble), 3 is to put a snapshot of the directory to share, do not share, B machine is not get the snapshot (I was no use of SQL default directory, Manually designated as the root directory of the FTP server, for the reasons detailed below), 4 is to pay attention to the work of the release agent does not set too fast, will have to generate snapshot files, and then notify Subscribers, subscribers to take the data or need some time, as to how fast you need to see the frequency of data changes and the speed of synchronization between the two machines to set it, For me, the data loss of the day was totally tolerable, so I set it up every 5 hours.

7, B machine into Enterprise Manager, right click on the database to sync > new > Subscribe, step by step, there is no need for special settings.

8, after completion, it should be able to be synchronized, but I found no effect, look at the release manager on a machine log found that the original is a B machine can not get the snapshot files on a machine, a look, the original SQL Server subscription port is incredibly 445, and virus sweep wave, so was blocked by the firewall, embarrassed, And I don't have permission to modify firewall blocking rules. So in the Publisher properties, the snapshot directory is designated as the root directory of FTP, open allow FTP, fill in the FTP Server access username and password, rerun the publishing agent. b Machine to delete the original subscription, re-establish the subscription, set up using FTP, so the problem is solved.

PS: Online resources say synchronized tables need to have primary keys, but there are some tables in my system that don't have a primary key (because there are several identical values in each column in the table, so you can't add a primary key, and you're too lazy to change the table structure to add a sequence, because adding a sequence, I have to change the program, afraid of--! , and finally the same success, so, the table has no primary key, certainly does not affect synchronization, at least not affect the snapshot synchronization mode.