SQL Server initialize replication with backup files – listen to the wind blowing the rain

First, background

When MySQL builds a database of historical data (Master/slave), it can be restored by backing up the historical data on the Master server and using the backup file in Slave, which is the advantage of being able to build the environment more quickly. Because the backup file can be compressed, sub-package, and can use FTP and other tools to ensure the safe and fast transmission process; For more information: MySQL Master Slave under Windows

When SQL Server encounters the same need to build a copy of the historical database, the usual practice is to publish the snapshot locally and then transfer the data from the subscription, how should SQL Server implement the backup history data to build replication (publish/subscribe)? Is the basic logical structure diagram of the backup file initialization subscription:

(FIGURE0: Backup file initialization subscription logical structure diagram)

Second, Building Process

(i) environmental Information

System environment: Windows Server + SQL Server 2008

Publisher: 192.168.1.105, server name: QuZhoushiwei105

Distribution server: Same machine as publisher

Subscriber: 192.168.1.106, server name: QuZhoushiwei106

Publication database: Barfoo.testpublish

Subscription database: Barfoo.testsubscribe

Database account Number: Replicationuser/replicationpassword

Description: The Publisher and Subscriber are in the same intranet machine, if your environment is cross-network segment (across the room) please refer to: SQL server replication (Publish subscription) Combat II: Cross-network segment (cross-room) deployment

(b) construction Steps

1) at the Publisher on the QuZhoushiwei105 server name to the publisher, if you log in as localhost or IP server, when the creation of the release will appear Figure1 error message;

(Figure1: Error message)

After logging on to the server, use the following SQL script to create a test database: Barfoo.testpublish, create a test table: UserInfo, and insert a piece of data to simulate historical data;

– Create a test database
Use MASTER
GO
CREATE DATABASE [Barfoo.testpublish]
GO

– Create a test table
Use [Barfoo.testpublish]
GO
CREATE TABLE [dbo]. [UserInfo] (
[ID] [int] IDENTITY (REPLICATION) not ' for ' not NULL,
[Names] [nvarchar] () NULL,
[Address] [nvarchar] () NULL,
CONSTRAINT [Pk_userinfo] PRIMARY KEY CLUSTERED
(
[Id] ASC
) on [PRIMARY]
) on [PRIMARY]
GO

– Insert test data
INSERT [dbo]. [UserInfo] ([names],[address]) VALUES (n ' Gaizai ', n ' Guangzhou ')

(Figure2:userinfo table record)

2) Execute the following SQL script to create the account number and password (Replicationuser/replicationpassword) at the publication database and subscriber;

– Publisher Create account password
Use [master]
GO
CREATE LOGIN [Replicationuser] with password=n ' Replicationpassword ', Default_database=[master], Check_expiration=off , Check_policy=off
GO
EXEC Master. sp_addsrvrolemember @loginame = n ' replicationuser ', @rolename = n ' sysadmin '
GO
Use [Barfoo.testpublish]
GO
CREATE USER [Replicationuser] for LOGIN [Replicationuser]
GO
Use [Barfoo.testpublish]
GO
ALTER USER [Replicationuser] with default_schema=[dbo]
GO

– Subscriber Create account password
Use [master]
GO
CREATE LOGIN [Replicationuser] with password=n ' Replicationpassword ', Default_database=[master], Check_expiration=off , Check_policy=off
GO
EXEC Master. sp_addsrvrolemember @loginame = n ' replicationuser ', @rolename = n ' sysadmin '
GO

3) Create a publication at the publisher, as shown in the following steps:

(Figure3: New publication)

(Figure4: Select data barfoo.testpublish)

(FIGURE5: Select Transactional publication)

(Figure6: Select a field to publish)

(Figure7: Not checked)

(Figure8: Setting up the Snapshot Agent)

(FIGURE9: Set agent Security)

(FIGURE10: Set the Log Reader Agent)

(FIGURE11: Set account password)

(Figure12: Creating a publication)

(Figure13: Publication name)

(FIGURE14: Created successfully)

(FIGURE15: Created publication)

4) Set the subscription options in the publication properties to TRUE if the default value allowed to initialize from the backup file is False, or you can use the following SQL script to modify it;

(Figure16:allow initialization from backup files)

– Modification of the publication allows initialization from backup
Use [Barfoo.testpublish]
GO
DECLARE @publication as sysname
SET @publication = N ' testpub '
EXEC sp_changepublication
@publication = @publication,
@property = N ' Allow_initialize_from_backup ',
@value = True
GO

5) Use the following SQL script to back up the database barfoo.testpublish, keep the backup file, need to be used when creating the subscription later;

– Backing up data
BACKUP DATABASE [Barfoo.testpublish]
To DISK = N ' G:dbbackupbarfoo.testpublish_20130822.bak '
With Noformat, noinit, NAME = N ' barfoo.testpublish-full database backup ',
SKIP, Norewind, nounload, STATS = 10
GO

6) on subscriber: 192.168.1.106, use the following SQL script to restore the backup file just now;

– Restore Database
RESTORE DATABASE [Barfoo.testsubscribe]
From DISK = N ' G:dbbackupbarfoo.testpublish_20130822.bak '
With FILE = 1,
MOVE n ' barfoo.testpublish ' to n ' g:databasebarfoo.testsubscribe.mdf ',
MOVE n ' barfoo.testpublish_log ' to n ' g:databasebarfoo.testsubscribe_log.ldf ',
Nounload, REPLACE, STATS = 10
GO

7) Modify the Account Replicationuser,sql script at the Subscriber line as follows:

– Modify Account Replicationuser
Use [Barfoo.testsubscribe]
GO
ALTER USER [Replicationuser] with default_schema=[dbo]
GO

8) at the publisher, execute the sp_addsubscription stored procedure to add the subscription, and the SQL script is as follows:

– Create a subscription at the publisher
EXEC sp_addsubscription
@publication = N ' testpub ',
@subscriber = ' QuZhoushiwei106 ',
@destination_db = N ' Barfoo.testsubscribe ',
@subscription_type = N ' Push ',
@sync_type = N ' Initialize with backup ',
@backupdevicetype = ' Disk ',
@backupdevicename = ' G:dbbackupbarfoo.testpublish_20130822.bak '

If the above SQL script executes successfully, the database returns the following prompt message:

(Figure17: Create subscription return information)

Note: If the backup file is too long after the execution of the script above, you may receive the following error message, if you encounter this problem, you can refer to the following "question" content:

Msg 21397, Level 16, State 1, procedure sp_msaddautonosyncsubscription, line No. 271

Some transactions are required to synchronize a non-synchronous subscription created from a specified backup, but these transactions are not available at the Distributor. Try this again using the updated log and the differential or full database backup.

9) Check the security of the newly added subscription attribute, the Subscriber connection, confirm the correct account and password, the default is to use the proxy account;

(Figure18: Subscriber properties)

(Figure19: Setting security)

(FIGURE20: Set login password)

You can also set the account password for a subscriber connection by using the following SQL script:

– Set Subscriber account password
EXEC sp_addpushsubscription_agent
@publication = N ' testpub ',
@subscriber = N ' QUZHOUSHIWEI106 ',
@subscriber_db = N ' Barfoo.testsubscribe ',
@subscriber_security_mode = 0,
@subscriber_login = N ' Replicationuser ',
@subscriber_password = N ' Replicationpassword '

10) Check the subscription status of the Publisher and Subscribers;

(FIGURE21: Start Replication Monitor)

(Figure22: Subscription status)

(FIGURE23: subscriber's Local subscription)

11) View data from the UserInfo table of the Subscriber QuZhoushiwei106 database barfoo.testsubscribe;

(Figure24:userinfo table data)

12) Use the following SQL script to insert new data in the UserInfo table at the publisher, test the replication, and view the Publisher and subscriber data separately;

– Test Subscription data
INSERT [dbo]. [UserInfo] ([names],[address]) VALUES (n ' Viajar ', n ' Beijing ')

(Figure25: UserInfo table data at the publisher)

(Figure26: UserInfo table data at Subscriber)

Third, Precautions

1. Under SQL Server to implement the Publisher and Subscriber communication is normal (that is, can exchange visits), open 1433 port, in the firewall set the inbound rules;

2. The publisher and the subscriber's SQL Server Agent account must be set the same, or not exchange visits;

3. Adding a new table later requires you to manually create the table structure at the Subscriber, primarily by ticking the new table in the project that publishes the properties, and then creating the table structure script from the table's right-click menu.

Four, Questions

(a) How to ensure that the Publisher continues to enter the data, how to ensure that after the copy of the historical data backup can also know which LSN to start reading?

Answer: If the backup file is too long apart, the following error message may appear when you execute the above script:

Msg 21397, Level 16, State 1, procedure sp_msaddautonosyncsubscription, line No. 271

Some transactions are required to synchronize a non-synchronous subscription created from a specified backup, but these transactions are not available at the Distributor. Try this again using the updated log and the differential or full database backup.

If you encounter this problem, there are 3 workarounds:

A. Follow the instructions above to make a differential backup of the Barfoo.testpublish database, and then make a differential restore in the Barfoo.testsubscribe database, you need to be aware of the use of sp_ AddSubscription should specify the file of the differential backup;

– Differential BACKUP Database
BACKUP DATABASE [Barfoo.testpublish]
To DISK = N ' G:dbbackupbarfoo.testpublish_diff_20130822.bak '
With Noformat, noinit, differential, NAME = N ' barfoo.testpublish-differential database backup ',
SKIP, Norewind, nounload, STATS = 10
GO

– Full Restore Database
RESTORE DATABASE [Barfoo.testsubscribe]
From DISK = N ' G:dbbackupbarfoo.testpublish_20130822.bak '
With FILE = 1,
MOVE n ' barfoo.testpublish ' to n ' g:databasebarfoo.testsubscribe.mdf ',
MOVE n ' barfoo.testpublish_log ' to n ' g:databasebarfoo.testsubscribe_log.ldf ',
Nounload, REPLACE, NORECOVERY, STATS = 10
GO

– Differential RESTORE Database
RESTORE DATABASE [Barfoo.testsubscribe]
From DISK = N ' G:dbbackupbarfoo.testpublish_diff_20130822.bak '
With FILE = 1,
MOVE n ' barfoo.testpublish ' to n ' g:databasebarfoo.testsubscribe.mdf ',
MOVE n ' barfoo.testpublish_log ' to n ' g:databasebarfoo.testsubscribe_log.ldf ',
Nounload, STATS = 10
GO

– Create a subscription at the publisher
EXEC sp_addsubscription
@publication = N ' testpub ',
@subscriber = ' QuZhoushiwei106 ',
@destination_db = N ' Barfoo.testsubscribe ',
@subscription_type = N ' Push ',
@sync_type = N ' Initialize with backup ',
@backupdevicetype = ' Disk ',
@backupdevicename = ' G:dbbackupbarfoo.testpublish_diff_20130822.bak '

B. If your database barfoo.testpublish can accept a short period of time without writing data, you can set the database to read-only state before making a full backup, with the database "Properties"-"options"-"state"-"database read-only" set to true;

(Figure27: Database read-only)

C. The last way to use the most often, is to create a good release immediately after the Distributor cleanup this job, can refer to:Implementing NoSync initializations (and Variations) on SQL Server 2005/8(Careyson), turn this job off, the data will be synced to the subscriber after the subscription is created, but the subscription will not be executed dbo.sp_msdistribution_cleanup. Can be tested using Checkpoint 1;

(Figure28: Distribution cleanup)

(b) If it is transactional replication, after the creation of the corresponding three jobs will be generated, the following 3 is the job in the replication jobs, what are their roles?

A. quzhoushiwei105-barfoo.testpublish-12

B. quzhoushiwei105-barfoo.testpublish-testpub-12

C. quzhoushiwei105-barfoo.testpublish-testpub-quzhoushiwei106-22

Quzhoushiwei105-barfoo.testpublish-12, this is the job of the Repl-logreader category, a database will only have a Log reader job, the name of the format is: servername-dbname-num;

Quzhoushiwei105-barfoo.testpublish-testpub-12, this is the Repl-snapshot category, the job, a release (also called a synchronization chain) corresponding to a snapshot job, the name of the format is: SERVERNAME-DBNAME-PUBL Ishname-num;

Quzhoushiwei105-barfoo.testpublish-testpub-quzhoushiwei106-22, this is a repl-distribution category job, and a subscription corresponds to a distribution job, named in the format: ServerName- Dbname-publishname-servername-num;

You can query for information about subscription job and subscription properties by using the following SQL script:

– Return to subscription information
SELECT * from Msdb.dbo.sysjobs WHERE category_id=10
– Returns the subscription attribute information
SELECT [Description]
, [name]
, [Allow_initialize_from_backup]
, [MIN_AUTONOSYNC_LSN]
from [Barfoo.testpublish]. [dbo]. [Syspublications]
GO

(Figure29: Subscription configuration information)

Five, Reference Documents

Initializing a transactional subscription (without using a snapshot)

How to initialize a transactional subscription from a backup (Replication Transact-SQL programming)

Initializing a subscription with a snapshot

Replication without Creating a Snapshot (Careyson)

implementing NoSync initializations (and Variations) on SQL Server 2005/8 (Careyson)

SQL Server transactional replication subscriptions are initialized through backup files

SQL Server replication Initializes a subscription with a backup file

ALTER AUTHORIZATION (Transact-SQL)

server2008+sql2008 Log Read agent is not running The process cannot be in the win-xxx "on Execution" Sp_replcmds "

SQL Server Replication Common Errors

sp_addsubscription (Transact-SQL)

sp_addpullsubscription_agent (Transact-SQL)

SQL Server database transaction log sequence number (LSN) Introduction (translated)

Log Sequence Numbers (original)

This article link: http://www.cnblogs.com/gaizai/p/3309567.html, reprint please specify.

SQL Server initialize replication with backup files – listen to the wind blowing the rain