----数据库复制(同步)
-----创建发布服务器 的整个流程逻辑思路
1、在本地磁盘上创建一个设置快照的目录(文件夹)
2、设置数据库恢复模式为 完整模式 即 RECOVERY MODE =FULL
3、启用代理服务
4、点击 "复制" 功能按钮 ---右键本地发布---新建分布 进入到发布向导界面
5、默认选择第一项(“ZHAOWENZHONG”将充当自己的分发服务器;SQL SERVER将创建分发数据库和日志);当然也可以选择其他服务器作为 分发服务器;
6、选择接 代理服务配置为 自动启动模式;
7、填写快照文件夹目录绝对路径;
8、选择需要执行 “复制”的数据库;
9、选择发布快照类型:(快照发布、事务发布、具有可更新订阅的事务发布、合并发布)
快照发布:发布服务器按预定的时间间隔向订阅服务器发送已发布数据的快照;
事务发布:在订阅服务器收到已发布数据的初始快照后,发布服务器将事务流式传输到订阅服务器;
具有可更新订阅的事务发布:在SQL SERVER 订阅服务器收到已发布数据的初始快照后,发布服务器将事务式传输到订阅服务器。来自订阅服务器的
事务被应用于发布服务器。
合并发布:在订阅服务器收到已发布数据的初始快照后,发布服务器和订阅服务器可以独立更新已发布数据。更改会定期合并。
10、选择要发布的对象(我们选择全部对象)。
11、选择需要同步的数据(列),如果要进行筛选 则点 右侧的 “添加” 在弹出的窗体中进行筛选。否则直接进行下一步操作(即选择全部)
12、设置代理服务的运作方式(立即运行还是设置相应的时间间隔 来运行快照)。如果设置为时间间隔来运行快照,则需要设置 “作业计划”。
13、设置 代理快照 登陆名(windows 方式 或 混合模式(指定用户名和密码))
14、发布 并填写发布的名称。
--------SCRIPT
---本次测试是选择的 快照发布 类型
/****** 正在编写复制配置的脚本。脚本日期: 2010/4/23 15:27:43 ******/
/****** 请注意: 出于安全原因,所有密码参数均使用 NULL 或空字符串代替。******/
/****** 开始: 要在发布服务器上运行的脚本 ******/
/****** 正在将服务器作为分发服务器安装。脚本日期: 2010/4/23 15:27:43 ******/
use master
exec sp_adddistributor @distributor = N'WENZHONGZHAO', @password = N''
GO
-- 添加代理配置文件
-- 更新代理配置文件默认值
exec sp_MSupdate_agenttype_default @profile_id = 1
GO
exec sp_MSupdate_agenttype_default @profile_id = 2
GO
exec sp_MSupdate_agenttype_default @profile_id = 4
GO
exec sp_MSupdate_agenttype_default @profile_id = 6
GO
exec sp_MSupdate_agenttype_default @profile_id = 11
GO
-- 添加分发数据库
use master
exec sp_adddistributiondb @database = N'distribution', @data_folder = N'D:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/Data', @data_file = N'distribution.MDF', @data_file_size = 5, @log_folder = N'D:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/Data', @log_file = N'distribution.LDF', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
GO
-- 添加分发发布服务器
exec sp_adddistpublisher @publisher = N'WENZHONGZHAO', @distribution_db = N'distribution', @security_mode = 1, @working_directory = N'G:/SQLSERVER2008/ReplData', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO
/****** 结束: 要在发布服务器上运行的脚本 ******/
-- 启用复制数据库
use master
exec sp_replicationdboption @dbname = N'TestCopyDataBase', @optname = N'publish', @value = N'true'
GO
exec [TestCopyDataBase].sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1
GO
exec [TestCopyDataBase].sys.sp_addqreader_agent @job_login = null, @job_password = null, @frompublisher = 1
GO
-- 添加快照发布
use [TestCopyDataBase]
exec sp_addpublication @publication = N'zwz_publication', @description = N'来自发布服务器“WENZHONGZHAO”的数据库“TestCopyDataBase”的快照发布。', @sync_method = N'native', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'true', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_address = N'192.168.0.101', @ftp_port = 21, @ftp_subdirectory = N'/ftp', @ftp_login = N'admin', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'snapshot', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1
GO
exec sp_addpublication_snapshot @publication = N'zwz_publication', @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 = 0, @publisher_login = N'sa', @publisher_password = N''
exec sp_grant_publication_access @publication = N'zwz_publication', @login = N'sa'
GO
exec sp_grant_publication_access @publication = N'zwz_publication', @login = N'NT AUTHORITY/SYSTEM'
GO
exec sp_grant_publication_access @publication = N'zwz_publication', @login = N'wenzhongzhao/Administrator'
GO
exec sp_grant_publication_access @publication = N'zwz_publication', @login = N'NT SERVICE/SQLSERVERAGENT'
GO
exec sp_grant_publication_access @publication = N'zwz_publication', @login = N'NT SERVICE/MSSQLSERVER'
GO
exec sp_grant_publication_access @publication = N'zwz_publication', @login = N'distributor_admin'
GO
-- 添加快照项目
use [TestCopyDataBase]
exec sp_addarticle @publication = N'zwz_publication', @article = N'A', @source_owner = N'dbo', @source_object = N'A', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509D, @identityrangemanagementoption = N'manual', @destination_table = N'A', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'SQL', @del_cmd = N'SQL', @upd_cmd = N'SQL'
GO
-----SCRIPT
-----设置FTP方式 访问 数据库复制快照
15、在主数据库上设置FTP服务,然后建立一个指向 数据库复制快照 文件夹的FTP站点。
并设置好可以远程连接FTP站点的账号,最后在 发布服务器上设置一下FTP客户端即:
a、选择刚刚建立的 本地发布名称 例如: zwz_publication
b、右键 ----属性----选中FTP快照---选中右侧 “运行订阅服务器使用FTP下载快照文件”,然后设置FTP参数
需要注意的是 "从FTP 根文件夹开始的路径" 参数 设置为 “/ftp’即可。点击 确认 按钮后会在 快照文件夹
中 创建一个FTP 文件夹,并在该文件夹中生成快照文件。
------订阅服务器
注意: 进入登陆 订阅服务器时,在服务器名称 中一定要输入 订阅服务器名称 而不能输入 订阅服务器的IP 否则会
提示错误。
1、进入订阅服务器 >>>>>复制>>>>>本地订阅>>>>>右键>>>>新建订阅
2、选择发布服务器>>>>> 在数据库和发布 中选择 发布服务器上的 发布名称例如:zwz_publiction
3、选择分发代理的订阅方式(推送订阅、请求订阅)
推送订阅:是在发布服务器 XXX 上运行所有代理的方式。
请求订阅:是在订阅服务器 YYY 上运行每个代理的方式。
4、我这里选择的是 推送订阅 方式。
5、紧接着 选择 订阅服务器和 订阅服务器上的 订阅数据库。可以同时设置多个订阅服务器,只需 点击 右下角的 添加
订阅服务器。同样需要注意的时,在订阅服务器的 服务器名称 中一定要输入 订阅服务器名称。
6、指定每个分发代理的进程账号和连接 (即配置安全选项 :设置和分发服务器 上设置的 代理方式、 用户、密码 一样)
7、指定 每个代理的同步计划 这里 选择 ”连续运行“
8、开始创建订阅
-----------------开始: 要在发布服务器“WENZHONGZHAO”上运行的脚本-----------------
use [TestCopyDataBase]
exec sp_addsubscription @publication = N'zwz_publication', @subscriber = N'ZFM-PC', @destination_db = N'TestCopyDataBase', @sync_type = N'Automatic', @subscription_type = N'pull', @update_mode = N'read only'
GO
-----------------结束: 要在发布服务器“WENZHONGZHAO”上运行的脚本-----------------
-----------------开始: 要在订阅服务器“ZFM-PC”上运行的脚本-----------------
use [TestCopyDataBase]
exec sp_addpullsubscription @publisher = N'WENZHONGZHAO', @publication = N'zwz_publication', @publisher_db = N'TestCopyDataBase', @independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'read only', @immediate_sync = 1
exec sp_addpullsubscription_agent @publisher = N'WENZHONGZHAO', @publisher_db = N'TestCopyDataBase', @publication = N'zwz_publication', @distributor = N'WENZHONGZHAO', @distributor_security_mode = 0, @distributor_login = N'sa', @distributor_password = null, @enabled_for_syncmgr = N'False', @frequency_type = 64, @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 = 20110623, @active_end_date = 99991231, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'True', @job_login = null, @job_password = null, @publication_type = 0
GO
-----------------结束: 要在订阅服务器“ZFM-PC”上运行的脚本-----------------
复制订阅 事务模式
发布库
1、 表名不能修改
2、 表上可以增加列(只能在表的末尾追加新列,不能在中间插入新列。在末尾追加的新列默认被发布到订阅库上)
3、 可以删除列
4、 不能修改列列类型
订阅库
1、可以删除参与订阅的列,但会导致订阅失败。可以通过修改 自动生成的 存储过程或SQL语句实现订阅。
2、可以增加列
3、参与订阅的列不能修改列名。
4、实现不了订阅索引和触发器
如果修改过发布数据库服务器的名称会报错,执行执行下面SQL即可 修复错误
select @@servername
select serverproperty('servername')
如果两个结果不一致,说明机器改过名字,在配置复制的时候就会报错误。
--执行下面的语句,完成后重新启动SQL服务 (修复)
if serverproperty('servername') <> @@servername
begin
declare @server sysname
set @server = @@servername
exec sp_dropserver @server = @server
set @server = cast(serverproperty('servername') as sysname)
exec sp_addserver @server = @server , @local = 'LOCAL'
end
通过SQL Server 2008数据库复制实现数据库同步备份
最新推荐文章于 2022-07-28 22:05:19 发布