Database synchronous backup with SQL Server 2008 database replication

SQL Server 2008 database replication is a publish/subscribe mechanism for synchronizing data between multiple servers, which we use for synchronous backups of databases. Synchronous backup here refers to the backup server and the primary server for real-time data synchronization, normally only use the primary database server, the backup server only when the primary server failure to put into use. It is a database backup solution that is superior to file backup.

When choosing a database synchronous backup solution, we evaluated two ways: Database mirroring for SQL Server 2008 and SQL Server 2008 database replication. The advantage of database mirroring is that the system can automatically detect a primary server failure and automatically switch to the mirror server. However, the disadvantage is that the configuration is complex and the data in the mirrored database is not visible (in SQL Server Management Studio, you can only see that the mirror database is mirrored, you cannot do any database operations, and the simplest queries do not.) If you want to see the truth, look at whether the data in the mirrored database is correct. Only the mirrored database is switched to the primary database is visible). If you want to use database mirroring, it is highly recommended that the SQL Server 2005 image Build-up manual written by Killkill, which we completed in the database mirroring deployment test, follows this article.

Finally, we selected SQL Server 2008 database replication.

Here's an example to learn how to deploy SQL Server 2008 database replication.

test Environment: Windows Server R2 + SQL server R2 (English version), two servers, one primary database server CNBlogsDB1, one backup database server CNBlogsDB2.

Replication principle: We are using snapshot-based transactional replication. The primary database server generates a snapshot, the backup library server reads and loads the snapshot, and then repeatedly replicates the transaction log from the primary database server. See:

Pictures from SQL Server Books Online

Installation and configuration steps:

First, install SQL Server R2 on two servers, the main installed components: Database Engine (including SQL Server Replication), Management Tools.

Second, the configuration of the primary database server (publisher):

1. Create a new sample database Cnblogsdemo in the primary database server CNBlogsDB1 (Note Recovery mode uses the default value of full, which is the only mode for transactional replication), and then creates a test table, such as Cnblogstest.

2. Set the folder where the snapshot is held:

Before you create a publication, set up a folder for the snapshot, which will generate a snapshot file in the folder after it is created, and the subscriber needs to load the snapshot file at initialization time.

Select the Replication "Local Publications" attribute and select Publishers in the window that appears, such as:

Click the button at the red box and the Settings window appears:

Set the snapshot file storage path in the default Snapshot folder.

3. Create a publication on the primary database server:

In replication, select New Publication in the Local Publications and a wizard appears. Select the database cnblogsdemo you want to publish, and then select the publication type transational publication, such as:

Click Next and an error occurs:

Originally all the tables to be copied need to have a primary key, just built cnblogstest table, there is no primary key. Build the primary key and restart the wizard.

Then select the objects you want to copy:

Point Next,next, enter the Snapshot Agent window, select Create a snapshot immediately and keep the snapshot available to initialize subscriptions, See:

Next, enter Agent Security:

Select Security Settings to make the appropriate account settings:

One is to set up a Windows account that runs the Snapshot Agent, and here we choose the same account as SQL Server agent.

One is to set up a SQL account to connect to the publisher, and here we use the SA account of the primary database server.

Continue: Ok,next,next, a name for this release:

Click Finish to begin the formal creation of the publication, the following window will appear when the creation is successful:

When you view the snapshot folder, you see the UNC folder, and the snapshot files are in this folder.

Here's a question of how to let Subscribers access this snapshot folder over the network.

We spent some time on this issue, would like to share folders, but do not want to open anonymous sharing, tossing for half a day, did not fix the Subscriber access to shared folder user authentication issues. So the FTP approach, so, the following describes how to let the Subscriber access to the snapshot file via FTP.

4. Setting up FTP access for snapshots

First, open the FTP service on the primary database server, establish an FTP site to the snapshot folder, and set up an FTP account that can be connected remotely. Then set up the FTP client configuration at this Publisher. The configuration method is as follows:

In Replication "Local publications, select the Publish [Cnblogsdemo]:cnblogsdemo_publication, select Properties" FTP Snapshot that you just created, such as:

Select Allow Subscribers to download snapshot files using FTP, and set the FTP client connection parameters, the Subscriber is connected to the FTP server through the settings here (Note: Path from the FTP root folder is set to the same setting as:/FTP).

Click OK to create an FTP folder in the snapshot folder and generate the snapshot files in that folder.

In this way, the publisher is configured, and the Subscriber is configured below.

Third, the configuration of the Backup database server (subscriber):

Go to subscriber CNBlogsDB2 and create a database Cnblogsdemo with the same name as the publisher, using the full recovery model.

In replication, select New subscriptions in the Local subscriptions to enter the wizard.

Next, go to the Select Publisher window, select Find SQL Server Publisher, and the Server Connection window appears:

Note here that the server name must be filled in the name of the publisher computer, if the computer name is not connected to the Hosts file to add an IP address resolution.

After you successfully connect to the Publisher, you can see the publication that you just created on the primary database server:

Next, go to the selection window for the Distribution Agent work location:

Here we choose pull subscriptions, which pulls the data over so that the primary database server is less burdensome.

Next, select the database on the Subscriber, we have already built the database with the same name, so the system will find it by itself.

Next, go to the Distribution Agent Security Settings window:

Click the button in the Red box to enter the Settings window:

Setup for example, Connect to the Distributor is set at the publisher's SA account.

OK, Next, Next, Next:

Next, Finish, Success:

The subscription to the backup database is built!

Now take a look at the database Cnblogsdemo for replication on the Subscriber CNBlogsDB2:

See! The table cnblogstest that we established at the publisher was copied over.

Now we're going to add a record to the publisher CNBlogsDB1:

Then go to the Subscriber server CNBlogsDB2 look at:

The data is now in sync! Get!

Problems encountered:

It was a long time between two problems during the testing process.

1) The Log Reader agent for the Publisher cannot start, error message:

The process could 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 is Impe Rsonated, or do not have permission. (Source:mssqlserver, Error number:15517)  
Get help:http://help/15517  
· The process could not execute ' sp_replcmds ' on ' YCSERVER006 '. (Source:mssql_repl, Error number:mssql_repl22037)  
Get help:http://help/mssql_repl22037

When we started the test, attaching an existing database for replication encountered this problem, attached is the SQL Server 2005 database file, the owner is empty, changed to the SA problem is resolved, such as:

2) The second problem is that the Subscriber has previously described the issue of accessing the snapshot folder on the publisher, which was later resolved by FTP.

For SQL Server 2008 database replication, I've been learning about this, and I'm looking forward to sharing this experience with friends in the garden, and you'll learn a lot during the sharing process.

Related articles:

Two ways to synchronize SQL Server 2008 databases (publish, subscribe)