SQL Server 2005 implementing database synchronization Backup process-results-analysis _mssql

Database replication:

In simple terms, database replication is made up of two servers, primary servers and backup servers, and the backup server is modified automatically after the primary server is modified.

There are two modes of replication: Push mode and request mode, push mode is the main server changes, automatically sent to the backup server, the request mode is, the backup server automatically to request, the individual began to pay more attention to test the difference between two modes, but the test found that in fact, two patterns are not very large, in the current server configuration, There is no need to consider so much, although there will be some differences, but can be ignored within the scope of the proposed direct selection of the push mode, because the configuration is relatively simple

There are three types of database publishing: Snapshot Publishing, transactional publishing, updatable and transactional publications, merge publications

General use of transactional release, the specific benefits, do not say, online a lot of

Replication process:

First: Open database--Copy---Local publishing---New publication

The following only provide attention to the place, the other click the next step can be

It has been said, recommend things

Usually only copy the table, but if you need other, please consider yourself

Snapshots, if you need a snapshot once a day, you can click on the button below, if only snapshots once, there is no need

Use the default configuration to

This completes the configuration of a publisher without an accident, so now configure the Subscriber

You can operate directly on your own machine.

First: Click on the database-copy---Local publishing---Create a new subscription

Find out the name you just posted.

Push mode and request mode, if you don't want to get in trouble, suggest using push mode directly

This is to set up your subscriber, if not the same server, then add Subscribers, connect to your subscriber, that is, backup server

This is also the default on the line, until the completion of the

Focus on the errors that occur during replication

①: Because the primary server and the standby server must be in a local area network, and cannot be connected by IP, the host name must be used to connect, then the use of the host name can not be connected, possibly because the machine name has been modified to make the server. SQL and access and machine name inconsistent

Use the new search, see if it is consistent, if not consistent, use the following code to modify it

SELECT @ @servername and select serverproperty (' servername ')

If SERVERPROPERTY (' servername ') <> @ @servername
Begin
declare @server sysname
Set @server = @ @servername
exec sp_dropserver @server = @server
Set @server = Cast (serverproperty (' servername ') as sysname)
exec sp_addserver @server = @server, @local = ' local '
End

②: If you are using push mode, then the biggest trouble will be read not to the snapshot, the general error is:

The process was unable to read the file "D:\DBSYNC\unc\FLOORWEB_FLOORLINK2_TESTFLOOR\20081128174839\ufoCorpAccount_2.pre" due to operating system error 3.

This is because the Subscriber does not read the snapshot folder location. There are many possible reasons, the most direct and simple solution is:

Copy your snapshot file, copy it to the Subscriber F:\XX, and modify a subscription property-snapshot Location-"Standby folder", snapshot Folder-"F:\XX"

Cause of the error: At the first replication, the Subscriber needs to read the snapshot to the primary server, synchronize, and then the primary server cannot read the snapshot because of permissions issues, resulting in an error that replicates the primary server's snapshot to the Subscriber, which can be resolved the first time it is synchronized directly against this snapshot.

Some results of the test: for reference only

1.1: If you modify the data at the Subscriber (such as update or delete), this time the primary server operation, the error, will stop the service directly

1.2: When the subscription is reinitialized, it is best to use the latest snapshot, do not use the current snapshot, will be dead, because he will be from the beginning to the end of the snapshot after the operation of all the execution, for example, you inserted 1000w, deleted 1000w, you use the current snapshot, you can directly sync, if you use the previous, The two steps will be executed in full.

1.3: In the millions of data insertion process, memory usage about 150-200MB, and usually occupy about 120mb of memory than, can directly ignore

1.4: Speed, 500w of data insertion, at the last 400-500w time of about 7 minutes, insert 100w of the database volume, synchronization takes approximately 8-10 minutes