1. MSSQL数据库复制介绍
MSSQL数据库复制是在MSSQL Server环境下使用内置管理工具，针对具有相同结构的MSSQL数据库服务器将 数据数据从一个服务器复制到其他服务器的过程，它可以实现多台服务器之间同步数据。
该技术可应用在需要每天更新数据的业务领域，避免了手动操作带来的繁琐问题，减小人为失误的可能性，极大地提高数据库的可用性和质量。
2. MSSQL数据库复制类型
2.1 简单去向复制
简单复制是指在一台服务器上单向复制少量表数据到另一个服务器上，不涉及到数据修改，不同于发布和订阅服务器工作方式的复制，不需要必须使用特定的技术来保证数据一致性，比较灵活方便。数据复制方向可设置为单向，双向。
简单复制适用于比较简单的数据表复制，数据量不大，且数据同步周期较长的业务领域，如静态网站数据、文件传输等。
2.2 事务性复制
事务性复制是一个多台服务器之间的复制程序，它能跨不同的地域，并且能在多台服务器之间平衡数据，并且实现多台服务器数据的交互。在其中，每一个数据库都能同时扮演着发布服务器和订阅服务器的角色，数据的复制改变也是针对事务的，数据更改将会实时地在各个服务器上反映。
事务性复制适用于数据同步效率高、数据实时同步、数据库数据量不大、网络连接速度较快的业务领域，如金融银行、交易系统、电子商务等。
3. MSSQL数据库复制功能介绍
3.1 对象复制
对象复制，最简单的复制方式，是将源数据库的一个或多个对象的复制到目标数据库，目标数据库的结构与源数据库的结构一样，以实现双方这些对象的数据同步。对象的定义不仅包括表、视图、触发器、存储过程、函数等。
--创建测试表
CREATE TABLE [dbo].[test_table](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](20) NOT NULL,
CONSTRAINT [PK_test_table] PRIMARY KEY CLUSTERED
(
[id] ASC
)
)
--在源数据库执行以下语句
EXEC sp_addtabletocontents
@table_name ='test_table',
@table_owner ='dbo';
--在目标数据库执行以下语句
EXEC sp_addarticle
@publication ='my_publication',
@article ='test_table',
@source_object ='test_table',
@destination_table ='test_table';
3.2 自定义复制
自定义复制是一种比较灵活的复制方式，它允许你自定义要复制的对象、所需的筛选器、传递规则和目标对象等属性，并且能够让你控制复制的过程，确保复制的高效性和可靠性。
--创建一个自定义复制对象
EXEC sp_addpublication @publication='MyAdMagazine',
@description='MyAdMagazine Subscriptions',
@sync_method='native',
@retention=14,
@allow_push= 'true',
@allow_pull= 'true',
@allow_subscription_copy= 'true',
@encryption_level= '0',
@frequency_type= 1,
@frequency_interval= 1,
@frequency_relative_interval= 1,
@frequency_recurrence_factor=16,
@frequency_subday_type= 2,
@frequency_subday_interval= 30,
@active_start_time_of_day= 133000,
@active_end_time_of_day= 190000,
@active_start_date= 20090218,
@active_end_date= 20091231;
3.3 传输复制
传输复制传输复制是通过一个模拟的物理数据库模型，复制数据在源服务器和目标服务器之间。这个模拟的物理数据库是由数据文件和日志文件组成，来收集变更、容错、信息流和跟踪。传输复制可以从一个服务器到另一个服务器传送数据和日志记录，支持跨多个版本的 SQL Server 数据库。此外，传输复制支持 Internet，并允许跨 Internet 连接的发布和订阅。
传输复制适用于需要高效传输数据的业务领域，如大型跨区域电子商务平台、分公司数据汇总平台。
4. MSSQL数据库复制操作示例
以简单复制为例，说明复制操作流程。
4.1 准备复制环境
创建数据库和表
--创建源数据库
USE master
if not exists(select * from sys.databases where name='pubs')
create database pubs
go
use pubs
--创建id自增长表
CREATE TABLE [dbo].[test_table](
[id] [int] IDENTITY(1,1) NOT NULL,
[username] [varchar](50) NOT NULL,
CONSTRAINT [PK_testtable] PRIMARY KEY CLUSTERED
(
[id] ASC
)
) ON [PRIMARY]
GO
创建目标数据库和表
USE master
if not exists(select * from sys.databases where name='pubs_replica')
create database pubs_replica
go
use pubs_replica
--在目标服务器上创建相同的表
CREATE TABLE [dbo].[test_table](
[id] [int] IDENTITY(1,1) NOT NULL,
[username] [varchar](50) NOT NULL,
CONSTRAINT [PK_test_table] PRIMARY KEY CLUSTERED
(
[id] ASC
)
) ON [PRIMARY]
GO
4.2 打开源服务器
开启发布功能
use master
execute sp_replicationdboption @dbname = N'pubs',
@optname = N'publish',
@value = N'true'
go
创建发布
use master
EXEC sp_addpublication @publication = N'MyPublication',
@description = N'发布 pubs 数据库.';
go
4.3 打开目标服务器
开启订阅功能
use master
execute sp_replicationdboption @dbname = N'pubs_replica',
@optname = N'subscribe',
@value = N'true'
go
创建订阅
use master
EXEC sp_addsubscription @publication = N'MyPublication',
@subscriber = $(MS_serverName),
@destination_db = N'pubs_replica',
@subscription_type = N'push',
@sync_type = N'automatic';
go
启动代理功能
use msdb
EXEC sp_add_proxy @proxy_name = 'Replication Subsystem Proxy Account',
@enabled = 1;
EXEC sp_grant_login_to_proxy @proxy_name = 'Replication Subsystem Proxy Account',
@login_name = N'domain\account';
EXEC sp_grant_proxy_to_subsystem @proxy_name = 'Replication Subsystem Proxy Account',
@subsystem_id = 4;
go
4.4 进行数据库复制
执行以下命令，将数据从源数据库发送到目标数据库
use pubs
EXEC sp_addsubscription @publication = N'MyPublication',
@subscriber = $(MS_serverName),
@destination_db = N'pubs_replica',
@subscription_type = N'push',
@sync_type = N'automatic';
go
use pubs_replica
SELECT * FROM test_table
go
5. 总结
MSSQL数据库复制可应用于多台服务器之间的数据同步、故障切换、高可用性和负载均衡解决方案等多种情况，本文介绍了简单复制、事务性复制和传输复制三种复制方式，以及几个复制时可能会用到的功能和一些操作示例。掌握这些技巧可以最大化发挥数据同步的效益。