在创建事务复制时,如果发布数据库很大,使用快照初始化时,将等待很久,如果出现问题可能又得重新初始化。使用备份初始化会省很多时间,但是数据库在创建发布订阅期间不能访问。使用备份代替快照迁移数据,备份可以压缩或使用 FTP 传输,若在同一个机房,拷贝复制更快。
现在测试:
创建测试库(发布服务器)
-- 创建测试库(发布服务器)
USE [master]
GO
CREATE DATABASE [PubDB]
ON PRIMARY
(
NAME = N'PubDB',
FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\PubDB.mdf' ,
SIZE = 5120KB ,
FILEGROWTH = 1024KB )
LOG ON
(
NAME = N'PubDB_log',
FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\PubDB_log.ldf' ,
SIZE = 1024KB ,
FILEGROWTH = 10%)
GO
-- 创建测试表(发布服务器)
USE [PubDB]
GO
CREATE TABLE [dbo].[PubTab](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](20) NOT NULL,
[Value] [decimal](18, 4) NULL,
CONSTRAINT [PK_PubTab] PRIMARY KEY CLUSTERED ([id] ASC),
) ON [PRIMARY]
GO
INSERT INTO [dbo].[PubTab]([name],[Value])
VALUES('aa',99),('bb',100)
GO
添加复制用的登录账户和数据库用户。订阅不是同一个实例的添加登录账户(发布服务器/订阅服务器)
-- 添加复制用的登录账户和数据库用户。订阅不是同一个实例的添加登录账户(发布服务器/订阅服务器)
USE [master]
GO
CREATE LOGIN [ReplUser] WITH PASSWORD=N'ReplUser', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [ReplUser]
GO
USE [PubDB]
GO
CREATE USER [ReplUser] FOR LOGIN [ReplUser]
GO
创建发布(发布服务器)
创建完备份后,设置允许从备份文件初始化(发布服务器发布数据库)
-- 允许从备份文件初始化(发布服务器发布数据库)
USE [PubDB]
GO
EXEC sp_changepublication
@publication = N'PublName',
@property = N'allow_initialize_from_backup',
@value = true
GO
备份数据库(发布服务器)
USE [master]
GO
BACKUP DATABASE [PubDB]
TO DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\PubDB.bak'
WITH NOFORMAT, NOINIT, NAME = N'PubDB-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
还原最新的备份数据库,将作为订阅库(订阅服务器)
USE [master]
GO
RESTORE DATABASE [SubDB]
FROM DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\PubDB.bak'
WITH FILE = 1,
MOVE N'PubDB' TO N'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\SubDB.mdf',
MOVE N'PubDB_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\SubDB_log.ldf',
NOUNLOAD, STATS = 5
GO
添加订阅,指定备份文件(发布服务器发布数据库)
USE [PubDB]
GO
EXEC sp_addsubscription
@publication = N'PublName',
@subscriber = N'HZC',
@destination_db = N'SubDB',
@subscription_type = N'Push',
@sync_type = N'initialize with backup',
@backupdevicetype='disk',
@backupdevicename='D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\PubDB.bak'
创建订阅后,更改订阅服务器连接账号
至此,发布订阅据配置完成了,现在看看表中的数据。
SELECT * FROM [PubDB].[dbo].[PubTab]
SELECT * FROM [SubDB].[dbo].[PubTab]
增加一行记录再查看,复制正常!
INSERT INTO [PubDB].[dbo].[PubTab]([name],[Value]) VALUES('cc',0)
GO
SELECT * FROM [PubDB].[dbo].[PubTab]
SELECT * FROM [SubDB].[dbo].[PubTab]
但是,当添加新表发布时,不能使用快照发布来同步了!~
创建测试表(发布服务器发布数据库),新表 identity 字段需要增加 NOT FOR REPLICATION
-- 创建测试表(发布服务器发布数据库)
USE [PubDB]
GO
CREATE TABLE [dbo].[PubTab02](
[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Name] [varchar](20) NOT NULL,
[Value] [decimal](18, 4) NULL,
CONSTRAINT [PK_PubTab02] PRIMARY KEY CLUSTERED ([id] ASC),
) ON [PRIMARY]
GO
正常的添加发布(发布服务器发布数据库)
这时不能启用快照同步了,快照没有用。
右键发布的表查看创建表的结构(发布服务器发布数据库),接着到订阅服务器数据库执行。
USE [SubDB]
GO
CREATE TABLE [dbo].[PubTab02](
[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Name] [varchar](20) NOT NULL,
[Value] [decimal](18, 4) NULL,
CONSTRAINT [PK_PubTab02] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
好了,添加算是完成了,现在测试添加数据(发布服务器发布数据库)
USE [PubDB]
GO
INSERT INTO [dbo].[PubTab02]([name],[Value]) VALUES('aa',99)
GO
SELECT * FROM [PubDB].[dbo].[PubTab02]
SELECT * FROM [SubDB].[dbo].[PubTab02]
若是添加 / 删除 字段,按正常添加 / 删除即可,直接在发布服务器数据库执行,如。
USE [PubDB]
GO
ALTER TABLE [dbo].[PubTab02] ADD InsertDate DATETIME NULL
GO
INSERT INTO [dbo].[PubTab02]([name],[Value],[InsertDate]) VALUES('aa',99,GETDATE())
GO
SELECT * FROM [PubDB].[dbo].[PubTab02]
SELECT * FROM [SubDB].[dbo].[PubTab02]
参考: