sqlserver同步-发布订阅

参考文档

https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2012/ms151176(v=sql.110)

复制概念

Microsoft SQL Server 提供以下类型的复制以用于分布式应用程序：

事务复制。 有关详细信息，请参阅事务复制。

合并复制。 有关详细信息，请参阅合并复制。

快照复制。 有关详细信息，请参阅快照复制。
本例使用事物复制

事务复制

事务复制通常从发布数据库对象和数据的快照开始。 创建了初始快照后，接着在发布服务器上所做的数据更改和架构修改通常在修改发生时（几乎实时）便传递给订阅服务器。 数据更改将按照其在发布服务器上发生的顺序和事务边界应用于订阅服务器，因此，在发布内部可以保证事务的一致性。

事务复制通常用于服务器到服务器环境中，在以下各种情况下适合采用事务复制：

希望发生增量更改时将其传播到订阅服务器。

从发布服务器上发生更改，至更改到达订阅服务器，应用程序需要这两者之间的滞后时间较短。

应用程序需要访问中间数据状态。 例如，如果某一行更改了五次，事务复制将允许应用程序响应每次更改（例如，激发触发器），而不只是响应该行最终的数据更改。

发布服务器有大量的插入、更新和删除活动。

发布服务器或订阅服务器不是 SQL Server 数据库（例如，Oracle）。

默认情况下，事务发布的订阅服务器应视为只读，因为更改将不会传播回发布服务器。 但是，事务复制确实提供了允许在订阅服务器上进行更新的选项。

事务复制的工作机制

事务复制是由 SQL Server 快照代理、日志读取器代理和分发代理实现的。 快照代理准备快照文件（其中包含了已发布表和数据库对象的架构和数据），然后将这些文件存储在快照文件夹中，并在分发服务器中的分发数据库中记录同步作业。

日志读取器代理监视为事务复制配置的每个数据库的事务日志，并将标记为要复制的事务从事务日志复制到分发数据库中，分发数据库的作用相当于一个可靠的存储-转发队列。 分发代理将快照文件夹中的初始快照文件和分发数据库表中的事务复制到订阅服务器中。

在发布服务器中所做的增量更改根据分发代理的计划流向订阅服务器，分发代理可以连续运行以尽量减少滞后时间，也可以按预定的时间间隔运行。 由于数据更改必须在发布服务器中进行（使用事务复制时，无需指定立即更新或排队更新选项），从而避免了更新冲突。 最后，所有订阅服务器都将获得与发布服务器相同的值。 如果事务复制使用了立即更新或排队更新选项，更新可以在订阅服务器中进行，对于排队更新，可能会发生冲突。

下图显示了事务复制的主要组件。

初始数据集

新的事务复制订阅服务器中必须包含一些表，这些表需要与发布服务器中的表具有相同的架构和数据，这样才能从发布服务器中接收增量更改。 初始数据集通常是由快照代理创建并由分发代理分发和应用的快照。 初始数据集还可以通过备份或其他方式提供，如使用 SQL Server Integration Services 提供。

在向订阅服务器分发并应用快照时，只有那些等待初始快照的订阅服务器才会受到影响。 该发布的其他订阅服务器（已经初始化的订阅服务器）不会受到影响。

并发快照处理

在快照生成期间，快照复制会在作为复制的一部分发布的所有表上放置共享锁。 这样可以防止更新正在发布的表。 并发快照处理（事务复制的默认方式）在整个快照生成过程中并不保留共享锁，因而允许用户在复制创建初始快照文件时继续工作，而不会被打断。

快照代理

快照代理在事务复制中实现初始快照所使用的过程与快照复制所使用的过程相同（上述有关并发快照处理的情况除外）。

生成快照文件后，可以使用 Microsoft Windows 资源管理器在快照文件夹中查看这些快照文件。

数据修改与日志读取器代理

日志读取器代理在分发服务器中运行；它通常连续运行，但也可以按照您制定的计划运行。 执行日志读取器代理时，它首先读取发布事务日志（该日志与执行一般 SQL Server 数据库引擎操作期间用于事务跟踪和恢复的数据库日志相同），并标识任何 INSERT、UPDATE 以及 DELETE 语句，或者对已标记为要复制的事务进行的其他数据修改。 然后，该代理将这些事务批量复制到分发服务器中的分发数据库中。 日志读取器代理使用内部存储过程 sp_replcmds 从日志中获取标记为要复制的下一个命令集。 这样，分发数据库就成为一个存储-转发队列，从该队列中将更改发送到订阅服务器中。 只有已提交的事务才能发送到分发数据库中。

当整批事务都成功写入分发数据库之后，将提交这批事务。 在每一批命令都提交到分发服务器后，日志读取器代理将调用 sp_repldone 以标记最终完成复制的位置。 最后，代理在事务日志中标记可以清除的行。 仍在等待复制的行不会被清除。

事务命令在传播到所有订阅服务器或达到最大分发保持期之前，一直存储在分发数据库中。 订阅服务器按事务在发布服务器中应用的相同顺序接收事务。

分发代理

对于推送订阅，分发代理在分发服务器上运行；对于请求订阅，分发代理在订阅服务器上运行。 该代理将事务从分发数据库移动到订阅服务器中。 如果订阅被标记为需要验证，则分发代理还要检查发布服务器和订阅服务器中的数据是否匹配。

准备工作

准备三台服务器饭别作为发布服务器，分发服务器，和订阅服务器，发布和分发也可以做在一起，但对生产系统来说，需要改动内容过多，最好分开处理，减少对发布服务器修改，以防发生不可预知的问题。

在虚拟化环境下测试，如果要加入域控制器，需要机器sid不同，本例没有使用域控制器，但以防万一，还是保证下sid不同。

查看服务器的sid

Windows电脑使用系统SID作为设备的唯一标识码。

C:\Users\Administrator>wmic
wmic:root\cli>csproduct list full


Description=计算机系统产品
IdentifyingNumber=1632-8161-9799-0883-4547-2216-90
Name=Virtual Machine
SKUNumber=
UUID=BF9F20D4-7FF4-4BBF-B99E-CB3AB7BA3EA8
Vendor=Microsoft Corporation
Version=7.0



wmic:root\cli>useraccount get name,sid
Name           SID
Administrator  S-1-5-21-1159014266-810630839-3809922025-500
Guest          S-1-5-21-1159014266-810630839-3809922025-501

另外两台sid

wmic:root\cli>useraccount get name,sid
Name           SID
Administrator  S-1-5-21-4169382184-4183743644-2975725785-500
Guest          S-1-5-21-4169382184-4183743644-2975725785-501

wmic:root\cli>useraccount get name,sid
Name           SID
Administrator  S-1-5-21-1730229181-1642809482-2327181613-500
Guest          S-1-5-21-1730229181-1642809482-2327181613-501

修改sid或计算机名的问题

修改sid或计算机名的问题后sqlserver不可用windows集成认证的问题

修改计算机名

1.打开新建查询，执行以下SQL 语句：

use master 
go 
select @@servername
select serverproperty('servername') 

2.执行之后会显示出原计算机名（执行结果上方）和更改后的计算机名（执行结果下方），如果两个名称一样则完成修改，如果不一样则执行以下语句：

sp_dropserver 'old_server_name' 
go
sp_addserver 'new_computer_name','local'

其中的old_server_name为原计算机名，new_computer_name为新的计算机名，在更改成功之后重启计算机，或数据库服务。
如果通过更改机器名任然无法登陆，进行下一步。用更改过的机器名新建一个登陆名。
3、新建一个登陆名
并设置相应的权限，要不任然打不开用户数据库。

重新启动服务器：

1.打开配置管理器：
2.右键重新启动SQL server；
或重启服务器。

驱动器路径改变问题

驱动器路径由于修改ssid发生改变，无法通过diskmgmg.msc修改盘符。
可以先停用本磁盘的页面文件，然后再修改路径即可。改完后需要重启数据库或主机方可使挂起的数据库恢复正常。

准备订阅数据库服务器

订阅数据库需要安装和发布数据库一致的数据库，以方便以后更换ip直接切换使用，配置相同的系统用户账号和密码，这里用管理员账号。首先需要查看下发布数据库已安装的sqlserver版本和组件。

查看已经安装的组件

在发布数据库服务器，运行安装光盘上的setup，选择修复，到这一步。

实例组件

共享功能组件

查看后记下，不要往下执行。

sa密码和发布数据库密码一致

配置分发

本例使用单独的服务器作为分发数据库服务器，可以和订阅发布数据库安装一致；以最大限度减少对发布服务器的更改。
在作为分发数据库的主机上。

以主机名登陆数据库

如果以ip地址登陆数据库，后面会产生无法连接服务器错误。

本身作为分发库

注意：若要使用该向导配置 SQL Server 代理服务，SQL Server 服务帐户必须拥有该服务器的管理员权限。如果该服务不具备这些权限，则您必须手动更改这一配置。

使管理员能作为服务登陆

由于本例没有域控制器和入域，采用本地管理员账户作为服务用户。把管理员作为服务用户登录。

先停下，把代理改为自动启动

配置代理为自动启动。

并把服务改为自动启动。

不支持非网络路径作为快照文件夹

创建共享文件夹

c:\Users\Administrator>md c:\MSSQL11.MSSQLSERVER\MSSQL\ReplData

c:\Users\Administrator>md C:\MSSQL11.MSSQLSERVER\MSSQL\Data

共享给所有人。

添加发布服务器作为发布服务器。

使用本地管理员密码

这个错误是事先准备工作中没有把sqlserver代理配置为自动。

配置发布

在发布服务器数据库上新建发布

选择另外的服务器作为分发

使用管理员密码

选择事物发布

项目只能选择表，且表中没有主键的不能发布。

这个地方可以使用本地管理员账户。

订阅

以计算机名登录订阅数据库服务器的数据库。

创建一个空的需要接受订阅的数据库，和发布库名称一致。

新建一个订阅

连接到发布服务器

代理账户安全设置

因为选定在代理账户下运行，需要提升代理账户的权限，可改为本地管理员账户，权限不足会报下面错误

订阅快照发布

快照发布

停止一个订阅

直接在订阅服务器删除订阅，然后在发布服务器删除发布。

其他

经观察，在事物发布启动后，快照数据持续向分发服务器传递，占用带宽可达300多兆，持续一个半小时，200多G的数据量。
订阅代理虽已启动，但不会收取订阅数据，知道初始快照传完。

错误

分发服务器错误

处理：
修改订阅服务器，sqlserver代理的登陆用户为本地管理员，重启服务和数据库。

错误：
Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. 原因: 找不到与提供的名称匹配的登录名。 [客户端
处理：
这个应该也是代理权限不足，系统使用了匿名用户。

其他

分发器错误

修改sql代理为管理员账户登录

然后错误信息

启用guest用户

在分发服务器上配置发布服务器
配置分发时，选择的发布服务器不是本机，而是实际的发布服务器

在发布服务器上配置分发服务



在发布服务器上建立发布
其他通前

错误消息:

"代理’xxxx’ 在出错后正在重试。已重试了25 次。有关详细信息，请参阅Jobs 文件夹中的代理作业历史记录。”

发生这个错误一般都是在一台机器上面有比较多的发布链，错误原因在于数据库对Replication使用内存的限制，
我们需要更改这个限制来解决这个问题（最好是不要再一台机器上创建太多的发布链），方法如下：

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\SubSystems
点开Windows项，找到下面这些内容
%SystemRoot%\system32\csrss.exe ObjectDirectory=\Windows SharedSection=1024,20480,768
将最后的数字（不一定是这个数字，可能是等）改成1024 保存，重启即可。

Windows2008 server 如果不是amdin用户可能会不让打开注册表，如果是管理组权限的话，可以到
C:\windows\system32 下面找到regedit32.exe 文件，右键，然后将自己的账号添加到运行权限里面。

除了表以外，添加其他发布报错，不知何原因

日志

创建发布时，需要排除没有主键的表，包含用户视图、用户函数和存储过程，加入其他会报错。

错误

SQL server事物复制报错：要复制的 LOB 数据的长度(xxxxx)超出了配置的最大值 65536…
处理：
如果使用了SQL SERVER的复制功能，建议把max text repl size(最大文本复制尺寸)直接改为“-1”，可以可以确保复制不会影响到生产业务。max text repl size修改后直接生效，无需重启数据库。

改为-1

快照发布会锁定表

造成主库无法使用，连续生产环境不可行