Implement database synchronization backups through SQL Server 2008 database replication _mssql2008

SQL Server 2008 database replication is a publish/subscribe mechanism for data synchronization between multiple servers, which we use for synchronous backups of the database. 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 in the primary server failure to put into use. It is a database backup solution that is superior to file backup.

When choosing a database synchronization 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 automatically discovers the primary server failure and automatically switches to the mirror server. 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 mirrored database is mirrored and cannot perform any database operations, nor is the simplest query.) If you want to see the truth, look at whether the data in the mirror database is correct. It is only visible if you switch the mirror database to the primary database. If you want to use database mirroring, strongly recommend the SQL Server 2005 Mirror build manual written by Killkill, we completed the database mirroring deployment test according to this article.

In the end, we chose SQL Server 2008 database replication.

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

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

principle of replication: 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 keeps copying the transaction log from the primary database server. See figure below:

Pictures from SQL Server Books Online

Installation and configuration steps:

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

Second, the primary database server (publisher) configuration:

1. Create a new sample database on the primary database server CNBlogsDB1 Cnblogsdemo (note that recovery mode will use the default value full, only this mode can be replicated), and then create a test table, such as: Cnblogstest.

2. Set the folder where snapshots are stored:

Before creating a publication, set up the folder where the snapshot is held, and the snapshot file is generated when the publication is created, and the subscriber needs to load the snapshot file when it is initialized.

Select the replication "Local Publications" property, select Publishers in the window that appears, as shown in the following figure:

Click the button at the red box to appear in the Settings window:

Set the snapshot file store path in 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, as shown in the following figure:

Click Next to receive an error:

All the tables you want to copy need to have a primary key, and when you just built the Cnblogstest table, you didn't build the primary key. Build the primary key and restart the wizard.

Then select the objects you want to copy:

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

Next, enter Agent security:

Select the security settings and make the appropriate account settings:

One is to set up the Windows account running the Snapshot Agent, where we choose the same account as the SQL Server agent.

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

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

Click Finish to begin the formal creation of the publication, the creation of a successful window will appear as follows:

When you view the snapshot folder, you will see the UNC folder, where the snapshot files are.

Here's a question about how to get subscribers to access this snapshot folder over the network.

We spent some time on this issue, we wanted to share the folder, but did not want to open the anonymous share, toss a half-day, did not deal with the subscriber access to the shared folder user authentication problem. So the FTP approach, so, here is how to let the Subscriber access to the snapshot files via FTP.

4. Set up FTP access for snapshots

Start the FTP service on the primary database server, set up an FTP site that points to the snapshot folder, and set up a remote 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 publication you just created [Cnblogsdemo]:cnblogsdemo_publication, select Properties FTP Snapshot, as shown in the following figure:

Select Allow Subscribers to download snapshot files using FTP and set the FTP client connection parameters, where the Subscriber connects to the FTP server (note: Path from the FTP root The settings for folder are set to:/ftp, as shown above.

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

The publisher is configured so that the Subscriber is configured below.

Third, backup the database server (subscriber) configuration:

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

In replication, select New subscriptions in the local subscriptions and go to 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 publisher's computer name, 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 Distribution Agent work Location Selection window:

We choose pull subscriptions here to pull the data over, so the burden on the primary database server will be lighter.

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

Next, enter the Distribution Agent Security Settings window:

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

Set as shown above, the Connect to the Distributor is set up 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 for replication on the subscriber CNBlogsDB2 Cnblogsdemo:

See! The table Cnblogstest we created at the publisher has been copied over.

Now we go to the publisher CNBlogsDB1 to add a record:

Then go to the subscriber CNBlogsDB2 look:

The data is immediately synchronized! Get!

The problems encountered:

It was a long time to be plagued by two problems during the test.

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 Rsonated, or you don't 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

At the beginning of the test, attach an existing database for replication encountered this problem, the additional is the SQL Server 2005 database files, owner is empty, the SA problem is resolved, the following figure:

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

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