MSSQL数据库复制之多乐趣-猿码集

1. MSSQL数据库复制简介

MSSQL数据库复制是 SQL Server 提供的一种数据复制功能，可以将数据从一个数据库实例复制到另一个数据库实例，以满足多种业务需求。复制操作可以包括整个数据库、单个表或者一组表的数据。

MSSQL复制功能可以分为三种类型：

事务复制：在主发布服务器上，在每个事务完成后将该事务发送到一个或多个订阅服务器上，所以这些服务器与发布服务器保持同步。

快照复制：在主发布服务器上执行数据快照，然后将该快照发送到订阅服务器上。

合并复制：在主发布服务器和每个订阅服务器上执行数据修改操作。

2. MSSQL数据库复制的多乐趣

2.1 数据备份与恢复

复制可以作为备份和恢复数据库的工具来使用。如果主要的发布服务器发生故障，复制数据库可以帮助恢复数据。

以下是复制主服务器上的备份命令：


BACKUP DATABASE database_name
  TO DISK = 'backup_file_path'
  WITH INIT;

以下是恢复数据库的命令：


RESTORE DATABASE database_name
  FROM DISK = 'backup_file_path';

2.2 数据分发

可以使用复制，将数据分发到不同的位置。例如，可以将销售数据分发到一个地理位置不同的数据中心中进行处理。这样可以显著提高整体的数据处理速度。

2.3 数据报表和分析

通过将数据从主服务器复制到多个订阅服务器，每个订阅服务器可以使用其上的本地数据来生成报表和分析结果，而不会对主服务器造成额外的负担。这样可以提高报表和分析的效率，同时不会影响主服务器的性能。

2.4 数据托管

可以使用复制，在服务器直接复制数据，以便不同的应用程序能够使用相同的数据。数据托管可以将复制作为一项服务，为不同的应用程序提供共享数据。这样可以显著提高数据的可靠性和稳定性。

3. MSSQL数据库复制的配置步骤

3.1 确定主服务器和订阅服务器

首先需要确定主服务器和订阅服务器的位置。主服务器是在存储复制数据的服务器，订阅服务器是接收复制数据的服务器。

3.2 启用主服务器上的发布功能

在主服务器上启用发布功能，对应用程序和订阅服务器可用的数据进行发布。

以下是在主服务器上启用发布功能的脚本：


--开启xp_cmdshell
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
--创建共享文件夹，以便其他服务器可以将快照复制到此处
EXEC sp_replicationdboption 
    @dbname = N'database_name', 
    @optname = N'replicate_snapshot_folder', 
    @value = N'\\shared_folder_path'
--确定发布服务器
DECLARE @publisher AS sysname;
SET @publisher = @@SERVERNAME;
USE [distribution];
EXEC sp_add_publisher 
    @publisher = @publisher, 
    @distribution_db = N'distribution', 
    @security_mode = 1;
EXEC sp_add_distributiondb 
    @database_name = N'distribution', 
    @data_folder = N'\\data_folder_path', 
    @log_folder = N'\\log_folder_path',
    @min_distretention = 0, 
    @max_distretention = 72, 
    @history_retention = 48, 
    @security_mode = 1;
EXEC sp_add_publication 
    @publication = N'Publication_Name', 
    @description = N'Transactional Publication of database_name database from Publisher ''Publisher_ServerName''.', 
    @sync_method = N'concurrent', 
    @retention = 0, 
    @allow_push = N'true', 
    @allow_pull = N'true', 
    @allow_anonymous = N'false', 
    @enabled_for_internet = N'false', 
    @snapshot_in_defaultfolder = N'true', 
    @compress_snapshot = N'false', 
    @ftp_port = 21, 
    @allow_subscription_copy = N'false', 
    @add_to_active_directory = N'false', 
    @publication_type = 0;
EXEC sp_add_publication_snapshot 
    @publication = N'Publication_Name', 
    @frequency_type = 1, 
    @frequency_interval = 0, 
    @frequency_relative_interval = 0, 
    @frequency_recurrence_factor = 0, 
    @frequency_subday = 0, 
    @frequency_subday_interval = 0, 
    @active_start_time_of_day = 0, 
    @active_end_time_of_day = 235959, 
    @active_start_date = 0, 
    @active_end_date = 0, 
    @job_login = null, 
    @job_password = null, 
    @publisher_security_mode = 1;
EXEC sp_add_article 
    @publication = N'Publication_Name', 
    @article = N'all', 
    @source_owner = N'dbo', 
    @source_object = N'all', 
    @type = N'logbased', 
    @description = null, 
    @creation_script = null, 
    @pre_creation_cmd = N'drop', 
    @schema_option = 0x000000000803509F, 
    @identityrangemanagementoption = N'none', 
    @destination_table = null, 
    @destination_owner = null, 
    @vertical_partition = N'false', 
    @ins_cmd = N'CALL [sp_MSins_all_sqlclr_art]', 
    @del_cmd = N'CALL [sp_MSdel_all_sqlclr_art]', 
    @upd_cmd = N'SCALL [sp_MSupd_all_sqlclr_art]';

3.3 在发行数据库的表中启用追加订阅

要将副本数据发送到订阅服务器，需要在发行表中启用追加订阅选项。


EXEC sp_addpublication_snapshot 
    @publication = N'Publication_Name', 
    @frequency_type = 1, 
    @frequency_interval = 0, 
    @frequency_relative_interval = 0, 
    @frequency_recurrence_factor = 0,
    @frequency_subday = 0, 
    @frequency_subday_interval = 0, 
    @active_start_time_of_day = 0, 
    @active_end_time_of_day = 235959, 
    @active_start_date = 0, 
    @active_end_date = 0, 
    @job_login = null, 
    @job_password = null, 
    @publisher_security_mode = 1;
GO
EXEC sp_articleview @publication = N'Publication_Name', @article = N'all', @view_name = N'sql_expression', @where_clause = N''

3.4 在订阅服务器上创建订阅

在订阅服务器上创建订阅以接收数据。


EXEC sp_addsubscription 
    @publication = N'Publication_Name', 
    @subscriber = N'Subscriber_Name', 
    @destination_db = N'destination_database_name', 
    @sync_type = N'Automatic', 
    @subscription_type = N'Push', 
    @update_mode = N'read only';

4. MSSQL数据库复制的维护

4.1 复制监视工具

在实施复制之后，需要使用复制监视工具监视复制操作。

可以使用 SQL Server Management Studio 的复制监视工具来监视复制操作。

以下是启用复制监视的步骤：

在 SQL Server Management Studio 中右键单击发布数据库。

选择“任务”，然后选择“監視”。

在“監視管理程序”對話框中，按“是否註冊此發行物”選項卡。

选择“启用复制监视”选项。

4.2 复制错误

在维护复制时，需要查看是否有任何错误。

以下是查询复制错误的命令：


SELECT * 
FROM distribution.dbo.MSrepl_errors

4.3 磁盘空间

在维护复制时，应注意磁盘空间。

以下是查询磁盘空间使用情况的命令：


EXECUTE xp_fixeddrives;

5. 总结

通过使用MSSQL数据库复制功能，可以满足不同业务需求，在一个或多个数据库实例之间复制数据，从而提高应用程序的性能和可靠性。

MSSQL数据库复制之多乐趣