Analysis of SQL server 2008 database synchronization backup process

Finally, we chose SQL Server 2008 database replication.

The following example shows how to deploy SQL Server 2008 database replication.

Test environment:Windows Server 2008 R2 + SQL Server 2008 R2 (English version), two servers, one master database Server CNBlogsDB1, and one backup database Server CNBlogsDB2.

Replication principle:We use snapshot-based transaction replication. The master database server generates a snapshot, the backup database server reads and loads the snapshot, and then continuously copies the transaction log from the master database server. See the figure below:

Picture from SQL Server books online

Installation and configuration steps:

1. Install SQL Server 2008 R2 on the two servers. The main installation components are Database Engine (including SQL Server Replication) and Management Tools.

II. Configuration of the primary database server (Publishing server:

1. On the master database server CNBlogsDB1, create a new example database CNBlogsDemo (note that the Recovery mode must use the default value Full, and only this mode can be used for Transaction replication), and then create a test table, such as CNBlogsTest.

2. Set the folder for storing snapshots:

Before creating a snapshot, set the folder for storing the snapshot. After the snapshot is created and published, the snapshot file is generated in the folder. The subscription server needs to load the snapshot file during initialization.

Select the Replication Local Publications attribute and select Publishers in the displayed window, as shown in the following figure:

Click the button in the red box to display the setting window:

Set the path for storing Snapshot files in Default Snapshot Folder.

3. Create and release on the master database server:

In Replication Local Publications, select New Publication. A wizard is displayed. Select the database CNBlogsDemo to be released, and then select the release type Transational publication, as shown in the figure below:

Click Next and an error occurs:

All the tables to be copied must have a primary key. When creating the CNBlogsTest table, no primary key is created. Create a primary key and restart the Wizard.

Then select the object to be copied:

Click Next and Next to enter the Snapshot Agent window. Select Create a snapshot immediately and keep the snapshot available to initialize subscriptions, as shown in the figure below:

Next, go to Agent Security:

Select Security Settings and set the corresponding account:

One is to set up a Windows account for running the Snapshot Agent. Here we select the same account as the SQL Server Agent.

One is to set up an SQL account for connecting to the publishing server. Here we will use the sa account of the primary database server.

Continue: OK, Next, Next, name the release:

 

Click Finish. The following window is displayed after the release is created:

When you view the snapshot folder, the unc folder is displayed, and the snapshot file is in this folder.

Here we need to consider how to allow the subscription server to access this snapshot folder through the network.

We spent some time on this issue. We wanted to share folders, but we didn't want to open anonymous sharing, the issue of accessing shared folder User Authentication by the subscription server is not fixed. Therefore, the FTP method is used. The following describes how to allow the subscription server to access snapshot files through FTP.

4. Set FTP access for snapshots

First, activate the FTP service on the master database server, create an FTP site pointing to the snapshot folder, and set up an FTP account that can be remotely connected. Then configure the FTP client on the publishing server. The configuration method is as follows:

In Replication Local Publications, select the released [CNBlogsDemo]: CNBlogsDemo_Publication, and select Properties FTP Snapshot, as shown in the figure below:

 

Select Allow Subscribers to download snapshot files using FTP and set the FTP client connection parameters. The subscription server connects to the FTP server through the settings here (note: path from the FTP root folder must be set to/ftp, as shown in the preceding figure ).

Click OK. An ftp folder is created in the snapshot folder and a snapshot file is generated in the folder.

In this way, the publishing server is configured, and the subscription server is configured below.

III. Backup database server (subscription server) configuration:

Go to the subscription server CNBlogsDB2, create a database CNBlogsDemo with the same name as the publisher, and use the full recovery mode.

In Replication Local Subscriptions, select New Subscriptions to enter the Wizard.

Next, enter the publishing Server selection window, select Find SQL Server Publisher, and the Server connection window appears:

Note that the Server Name must be the computer Name of the publishing Server. If the computer Name cannot be connected, add an IP address to the hosts file for resolution.

After successfully connecting to the publishing server, you can see the release just created on the primary database server:

Next, go to the selection window of "distribution agent job location:

 

 

Here we select pull subscriptions to pull the data, so that the burden on the primary database server will be lighter.

Next, select the database on the subscription server. We have already created a database with the same name, so the system will find it.

Next, go to the distribution agent Security Settings window:

Click the button in the red box to enter the settings window:

As shown in the figure above, Connect to the Distributor sets the sa account of the publishing server.

OK, Next:

Next, Finish, Success:

The subscription of the backup database is ready!

Now let's take a look at the database CNBlogsDemo for replication on the subscription server CNBlogsDB2:

Look! We copied the CNBlogsTest table created on the publishing server.

Now let's add a record to the publishing server CNBlogsDB1:

Go to the subscription server CNBlogsDB2 and take a look:

Data is synchronized now! Done!

    

Problems:

During the test, two problems have been involved for a long time.

1) Log Reader Agent of the publishing server cannot be started. Error message:

· The process cocould not execute 'sp _ replcmds 'on 'ycserver006'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http: // help/MSSQL_REPL20011
· Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Source: MSSQLServer, Error number: 15517)
Get help: http: // help/15517
· The process cocould not execute 'sp _ replcmds 'on 'ycserver006'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http: // help/MSSQL_REPL22037

During the test, an existing database was attached for replication. The SQL Server 2005 database file is appended and the Owner is empty. The sa problem is solved, as shown in the figure below:

2) The second problem was the issue that the subscription server previously described accessed the snapshot folder on the publishing server, which was solved through FTP.