How SQL Server initializes replication through backup files

I. BACKGROUND

MySQL in the history of the database to build replication (Master/slave), you can back up the historical data in the Master server, using this backup file to restore in Slave; The advantage is that you can build the environment more quickly, Because the backup files can be compressed, subcontracting, and can use the FTP and other tools to ensure the security and speed of the transmission process; For more information: Windows builds MySQL Master Slave

When SQL Server encounters the same need to replicate the history database, the usual practice is to publish snapshots locally and then transmit data by subscription, how should SQL Server implement backup history data to build replication (publish/subscribe)? The following figure is the basic logical structure diagram of the backup file initialization subscription:

(FIGURE0: Backup file initialization subscription logical structure diagram)

Second, build the process

(i) Environmental information

System environment: Windows Server 2008 + SQL Server 2008

Publisher: 192.168.1.105, server name: QuZhoushiwei105

Distributor: Same machine as publisher

Subscriber: 192.168.1.106, server name: QuZhoushiwei106

Publish database: Barfoo.testpublish

Subscription database: Barfoo.testsubscribe

Database account Number: Replicationuser/replicationpassword

Description: Publisher and Subscriber are in the same intranet machine, if your environment is across the network segment (across the computer room) Please refer to: SQL Server replication (publish subscription) combat two: cross-net segment (across the engine room) deployment

(ii) Steps to build

1 at the publisher to the QUZHOUSHIWEI105 server name login to the publisher, if you localhost or IP form landing server, in the creation of the release will appear the following figure Figure1 error message;

(Figure1: Error message)

After landing 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 (1,1) not to REPLICATION not NULL,
    [names] [nvarchar] (=) NULL,
    [address] [ NVARCHAR] (m) NULL,
 CONSTRAINT [pk_userinfo] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
) on [PRIMARY]
On [PRIMARY] Go
    
--Inserting 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 the Subscriber respectively;

--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 to
CREATE USER [replicationuser] for LOGIN [Replicationuser] Go with
[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