分类 [ 数据库 ] 下的全部文章

客户有需求:一个C/S应用程序需要一台数据库服务器和几十台客户机,如果公司网络中断,并不影响应用,所以每台客户机本地单独有一个数据库,而服务器上的数据库中保存全部客户机的数据,并且能允许客户机和服务器两端的数据库单独修改,网络良好的情况下客户机本地数据库与保存在服务器上的数据一致,例如服务器保存A、B、C三份数据(分别是客户机A、B、C的数据),而A客户机本地数据库中只保存A份数据。

 

1、概览

一台数据库服务器与多台客户机数据库实现筛选表行双向数据库同步,即每台客户机只同步自己的数据记录,而服务器保全了所有客户机的数据记录,并且允许在两端做修改。要求有内部的DNS服务器做机器名的解析,如果没有就要通过本地的hosts文件做机器名的解析。数据库服务器上安装windows server 2008 R2 + SQL Server 2008 R2,客户机上安装windows 8 + SQL Server 2008 Express。

我们在每个同步的表里添加字符型的HostID字段,存放客户机名或者特有的机器识别信息,每台客户机上的应用程序在插入表记录时给HostID字段一个具体的值,在定义发布时指定筛选条件[HostID] = HOST_NAME()。假设服务器机器名为SQLServer。

本方案采用微软的合并复制同步机制。

 

 

 

2、客户机和服务器上都操作

 

1、开启数据库的远程tcp连接

进入SSMS管理工具,右击最上面的实例名,然后点击“属性”,“连接”,勾选“允许远程连接到此服务器”。再次右击实例名,然后点击“方面”,确保服务器配置下的参数RemoteAccessEnabled=True。

打开配置管理器,点击“SQL Server网络配置”,双击TCP/IP,然后启用,在“IP地址”页上,翻到最后,IPALL里面TCP动态端口为空,TCP端口为1433,然后重启SQL Server服务。

 

2、确保数据库实例名与机器名相同

使用SSMS登录到数据库,然后:

查询现有实例名:select srvname,srvnetname from master..sysservers    ------比如查询到的实例名是E305-PC,如果与机器名不同,那么先删除然后添加:

删除实例名:sp_dropserver 'E305-PC'

添加实例名:sp_addserver 'NO25','local'      -----假如机器名是NO25

重启数据库服务。

 

3、创建业务数据库

 

4、开启防火墙

开启防火墙:打开防火墙——高级设置——入站规则——新建规则——选择程序——位置:C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlserver.exe。 SQL Server的端口号是1433。

 

 

3、服务器上操作

 

1、创建本地windows用户sql2008,加入Administrators组。

2、创建快照保存目录:在非系统盘上创建目录SQLServerReplicate,并且共享为\\SQLServer\SQLServerReplicate,everyone具有读权限,sql2008具有读写权限。

3、把windows用户sql2008设为数据库的登录名,并且是业务数据库的db_owner,同时具备public、serveradmin和sysadmin的服务器角色。

4、在业务数据库中创建表,对于自增长的字段,设置其“不用于复制”为“否”。

5、创建发布

采用SSMS管理工具登陆发布数据服务器,展开“复制”,鼠标右击“本地发布”-->新建发布,然后选择发布的数据库,再选择“合并发布”,再选择SQL Server 2008兼容的发布,然后再选择需要发布的表,接下来会提示会在表里添加专门用于同步的列uniqueidentifier,在接下来的“筛选表行”界面上点击“添加”按钮下的“添加筛选器”,在这里为需要筛选数据的每个表定义筛选条件(即查询的结果就是需要同步的数据,例如:SELECT <published_columns> FROM [dbo].[QC] WHERE [HostID] = HOST_NAME(),黄色部分不能改动),下一步,在“快照代理”界面上定义执行计划为“在每周 星期一 的 4:00:00 执行。…..”,下一步,在“代理安全性”界面上点击“安全设置”,然后输入操作系统用户SQLServer \sql2008和密码(假如服务器的机器名是SQLServer),“通过模拟进程账户”连接到发布服务器(即发布数据库),这里的“模拟进程账户”就是上面设置的SQLServer \sql2008。最后不断地“下一步”直到输入发布名的界面,输入一个名字并点击完成。

6、把SQLServer\sql2008用户加入发布访问列表中:右击刚刚创建的发布à属性à发布访问列表,然后加入sql2008这个windows账户。

7、验证发布:右击发布名字à查看快照代理状态,或者进入目录\\SQLServer\ SQLServerReplicate检查有没快照数据。

 

8、创建推送订阅

继续在服务器上的数据库中操作。
展开“复制”、“本地发布”,右击其中的一个发布名称,“新建订阅”,下一步,确保选中的发布名字,点选“在发布服务器SQLServer\SQL2008SRV上运行所有代理(推送订阅)”,下一步,点击“添加SQL Server订阅服务器”按钮,然后输入登陆到客户数据库的信息,指定一个订阅数据库,下一步,进入“合并代理安全性”界面上点击“…”按钮定义:运行代理进程的windows账户SQLServer\sql2008和密码,“通过模拟进程账户”登陆到发布服务器,通过sa账户登陆到订阅数据库(客户机的数据库);确认后点击下一步,“代理计划”选择“连续运行”,下一步,在订阅类型界面上选择“服务器”,下一步,在“HOST_NAME值”界面上填写HOST_NAME的值,默认就是客户机器名(也可以定义其他的值,但是在本客户机上往数据库插入记录时,字段HostID的值与这里定义的HOST_NAME值相同),这个值就是用于筛选表行的,不要填写错误,下一步,下一步,直到完成。

为了以后方便创建更多的订阅,设计的如下的存储过程:
CREATE PROCEDURE pro_CreateSubscriber
@Subscriber nvarchar(20) --订阅客户机的机器名,如N'NO33'
AS
BEGIN
exec sp_addmergesubscription @publication = N'pub1', @subscriber = @Subscriber, @subscriber_db = N'ProduceManager', @subscription_type = N'Push', @sync_type = N'Automatic', @subscriber_type = N'Global', @subscription_priority = 75, @description = null, @use_interactive_resolver = N'False', @hostname = @Subscriber
exec sp_addmergepushsubscription_agent @publication = N'pub1', @subscriber = @Subscriber, @subscriber_db = N'ProduceManager', @job_login = N'NO41\sql2008', @job_password = N'123456', @subscriber_security_mode = 0, @subscriber_login = N'sa', @subscriber_password = N'123456', @publisher_security_mode = 1, @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 1, @frequency_subday = 2, @frequency_subday_interval = 30, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20150913, @active_end_date = 99991231, @enabled_for_syncmgr = N'False'
END
GO

此后在服务器上的数据库中创建订阅见简单了,只需执行这个存储过程即可,即:exec pro_CreateSubscriber N'MyCOmputer',就是给客户机MyComputer创建订阅。

 

9、验证订阅:右击刚刚创建的订阅名à查看同步状态,或者直接在客户机数据库中查看库表。

 

附录:脚本

本实例脚本假定:服务器机器名SQLServer,服务器数据库实例名SQL2008SRV,客户机器名WYQ,客户机数据库实例名EXPRESS,业务数据库ProduceManager,同步两个表xinxi_tb和kecheng_tb。sql2008和客户机数据库sa的密码都是Passwd123。

创建发布的脚本

use [ProduceManager]

exec sp_replicationdboption @dbname = N'ProduceManager', @optname = N'merge publish', @value = N'true'

GO

 

-- 添加合并发布

use [ProduceManager]

exec sp_addmergepublication @publication = N'pub1', @description = N'来自发布服务器“SQLServer\SQL2008SRV”的数据库“ProduceManager”的合并发布。', @sync_mode = N'native', @retention = 14, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_subdirectory = N'ftp', @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @dynamic_filters = N'true', @conflict_retention = 14, @keep_partition_changes = N'false', @allow_synctoalternate = N'false', @max_concurrent_merge = 0, @max_concurrent_dynamic_snapshots = 0, @use_partition_groups = null, @publication_compatibility_level = N'100RTM', @replicate_ddl = 1, @allow_subscriber_initiated_snapshot = N'false', @allow_web_synchronization = N'false', @allow_partition_realignment = N'true', @retention_period_unit = N'days', @conflict_logging = N'both', @automatic_reinitialization_policy = 0

GO 

 

exec sp_addpublication_snapshot @publication = N'pub1', @frequency_type = 8, @frequency_interval = 2, @frequency_relative_interval = 1, @frequency_recurrence_factor = 1, @frequency_subday = 1, @frequency_subday_interval = 5, @active_start_time_of_day = 40000, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = N'SQLServer\sql2008', @job_password = N'Passwd123', @publisher_security_mode = 1

 GO

 

-- 配置表kecheng_tb

use [ProduceManager]

exec sp_addmergearticle @publication = N'pub1', @article = N'kecheng_tb', @source_owner = N'dbo', @source_object = N'kecheng_tb', @type = N'table', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000010C034FD1, @identityrangemanagementoption = N'manual', @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'[HostID] = HOST_NAME()', @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0

GO 

 

-- 配置表xinxi_tb

use [ProduceManager]

exec sp_addmergearticle @publication = N'pub1', @article = N'xinxi_tb', @source_owner = N'dbo', @source_object = N'xinxi_tb', @type = N'table', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000010C034FD1, @identityrangemanagementoption = N'auto', @pub_identity_range = 10000, @identity_range = 1000, @threshold = 80, @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'[HostID] = HOST_NAME()', @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0

GO

  

 

创建定于的脚本

-----------------开始: 要在发布服务器“SQLServer\SQL2008SRV”上运行的脚本-----------------

use [ProduceManager]

exec sp_addmergesubscription @publication = N'pub1', @subscriber = N'WYQ\EXPRESS', @subscriber_db = N'ProduceManager', @subscription_type = N'Push', @sync_type = N'Automatic', @subscriber_type = N'Global', @subscription_priority = 75, @description = null, @use_interactive_resolver = N'False', @hostname = N'WYQ'

 

exec sp_addmergepushsubscription_agent @publication = N'pub1', @subscriber = N'WYQ\EXPRESS', @subscriber_db = N'ProduceManager', @job_login = N'SQLServer\sql2008', @job_password = N'Passwd123', @subscriber_security_mode = 0, @subscriber_login = N'sa', @subscriber_password = N'Passwd123', @publisher_security_mode = 1, @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 = 20150815, @active_end_date = 99991231, @enabled_for_syncmgr = N'False'

GO

-----------------结束: 要在发布服务器“SQLServer\SQL2008SRV”上运行的脚本-----------------