SQL_Server2008 R2 跨局域网非默认端口通过发布订阅同步数据

• 准备环境

1. 机器A；（对外访问地址：59.213.217.8，私有地址：172.172.173.8。主机名：TEST）Windows server2012

2. 机器B；（对外访问地址：59.213.217.2，私有地址：172.22.12.39。主机名：ZZFBGS-SCJDGLJ-）Windows server2008

3. 机器A配置连接机器B的别名：别名为机器B的主机名（ZZFBGS-SCJDGLJ-）需重启SQL Server服务；地址和端口为机器B数据库的对外访问地址和端口

4. 机器B配置连接机器A的别名：别名为机器A的主机名（TEST）需重启SQL Server服务；地址和端口为机器A数据库的对外访问地址和端口

5. 机器A中hosts文件添加机器B的映射

6. 机器B中hosts文件添加机器A的映射

7. 机器A通过别名远程连接机器B

8. 机器B通过别名远程连接机器A

9. 机器A上创建测试数据库database_A，将该库同步到机器B上

10. 机器A上创建本地发布

11. 机器B上创建本地订阅

1. 在机器A上验证是否订阅成功
2. 查看机器B上数据是否同步
3. 机器A中插入一列数据，查看机器B上是否同步

1. 机器A中删除一条数据，查看机器B中是否同步
2. 至此SQL_Server2008 R2数据库同步已完成。

 SqlServer发布订阅错误收集

1.1. Message:脚本对于表“dbo.table”失败。

1.1.1. 错误消息

Source: Microsoft.SqlServer.Smo

Target Site: System.Collections.Generic.IEnumerable`1[System.String] ScriptWithList(Microsoft.SqlServer.Management.Smo.DependencyCollection, Microsoft.SqlServer.Management.Smo.SqlSmoObject[])

Message:  脚本  对于  表 “ dbo.HY_CORP_BAD_CREDIT_INFO ”失败。 

Stack:     在  Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects)

    在  Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(SqlSmoObject[] objects)

    在  Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleSchScript(Scripter scripter, BaseArticleWrapper articleWrapper, Table smoTable)

    在  Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleScripts(ArticleScriptingBundle articleScriptingBundle)

    在  Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateArticleScripts(ArticleScriptingBundle articleScriptingBundle)

    在  Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateObjectScripts(ArticleScriptingBundle articleScriptingBundle)

    在  Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting()

    在  Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting()

    在  Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()

    在  Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()

    在  Microsoft.SqlServer.Replication.AgentCore.Run() ( 源 : Microsoft.SqlServer.Smo ，错误号 : 0)

获取帮助 : http://help/0

Source: Microsoft.SqlServer.Smo

Target Site: Void CheckSupportedType(Microsoft.SqlServer.Management.Smo.ScriptingOptions)

Message:  对象  HY_CORP_BAD_CREDIT_INFO  中的列  fHappenDate  包含类型  Date ，此类型不受目标服务器版本  SQL Server 2005  支持。

Stack:     在  Microsoft.SqlServer.Management.Smo.Column.CheckSupportedType(ScriptingOptions options)

    在  Microsoft.SqlServer.Management.Smo.Column.VersionValidate(ScriptingOptions so)

    在  Microsoft.SqlServer.Management.Smo.Column.ScriptDdlCreateImpl(StringBuilder sb, ScriptingOptions so)

    在  Microsoft.SqlServer.Management.Smo.Column.ScriptDdl(StringCollection queries, ScriptingOptions so)

    在  Microsoft.SqlServer.Management.Smo.Table.ScriptTableInternal(ScriptingOptions so, StringBuilder sb, ColumnCollection columns, IndexCollection indexes)

    在  Microsoft.SqlServer.Management.Smo.Table.GetTableCreationScript(ScriptingOptions so, StringBuilder sb)

    在  Microsoft.SqlServer.Management.Smo.Table.ScriptCreate(StringCollection queries, ScriptingOptions so)

    在  Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptCreateInternal(StringCollection query, ScriptingOptions so)

    在  Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithListWorker(DependencyCollection depList, SqlSmoObject[] objects)

    在  Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects) ( 源 : Microsoft.SqlServer.Smo ，错误号 : 0)

获取帮助 :  http://help/0

1.1.2. 处理方法

将数据库兼容级别设置到100，在发布数据库上执行如下代码，修改数据库的兼容级别到100。

ALTER DATABASE [DataBase] SET COMPATIBILITY_LEVEL = 100

或者界面操作：

 

1.2. 由于出现操作系统错误 3，进程无法读取文件D:\\XXXX\\X.pre (源: MSSQL_REPL，错误号: MSSQL_REPL20024)

1.2.1. 错误消息

尝试的命令 :

E:\DB_backup_xmq\unc\WINDOWS-Q6MJ0R8_REMAIUS_REMAIUS\20161224144353\hy_wait_work_r45f6f27c_249.pre

( 事务序列号 : 0x0000FA7E0001C1D4005000000000 ，命令  ID: 252)

 

错误消息 :

由于出现操作系统错误  3 ，进程无法读取文件“ E:\DB_backup_xmq\unc\WINDOWS-Q6MJ0R8_REMAIUS_REMAIUS\20161224144353\hy_wait_work_r45f6f27c_249.pre ”。  ( 源 : MSSQL_REPL ，错误号 : MSSQL_REPL20051)

获取帮助 : http://help/MSSQL_REPL20051

系统找不到指定的路径。

 ( 源 : MSSQL_REPL ，错误号 : MSSQL_REPL3)

获取帮助 :  http://help/MSSQL_REPL3

1.2.2. 解决方法

qlServer2008 的订阅发布，起初使用推送订阅很顺利，后来改成请求订阅出现了 这个 问题 。

 

 

1.3. 应用复制的命令时在订阅服务器上找不到该行

1.3.1. 错误消息

尝试的命令 :

if @@trancount > 0 rollback tran

事务序列号 :  

--0x0000FDAB0003A908008F00000000  命令  ID: 7

错误消息 :

应用复制的命令时在订阅服务器上找不到该行。  ( 源 : MSSQLServer ，错误号 : 20598)

获取帮助 : http://help/20598

应用复制的命令时在订阅服务器上找不到该行。  ( 源 : MSSQLServer ，错误号 : 20598)

获取帮助 :  http://help/20598

1.3.2. 解决方法

1. 发布服务器的

exec sp_helpsubscriptionerrors 'WINDOWS-Q6MJ0R8'

, 'remaius'

, 'remaius'

, 'WIN-V0R4COILDAS'

, 'remaius';

2. 解决

a. 忽略，在订阅上执行

exec sp_setsubscriptionxactseqno 'WINDOWS-Q6MJ0R8'

,  'remaius'

,  'remaius'

, 0x0000FDAB0003A908008F00000000;

b. 如第一个找 xact_seqno 的 SQL ，拿到 xact_seqno ， command_id

然后在发布上，

sp_browsereplcmds '0x0000FBF300119D32000900000000','0x0000FBF300119D32000900000000';

sp_browsereplcmds '0x0000FDAB0003A908008F00000000','0x0000FDAB0003A908008F00000000';

 

找到  {CALL [dbo].[sp_MSupd_dboTable1](,'dd',4,0x04)} 【默认的命名规则是 sp_MS+operation+schemaName+tableName 】

然后在订阅服务器，补充主键 =4 的数据 , 然后复制就恢复了。

【注意】： id 自增的时候需要打来自动增长的标志：

--set identity_insert Table_1  ON-- 打开

--insert into Table_1(id) values(134);

--set identity_insert Table_1  OFF-- 关闭

1.4. 数据库 'dbtest' 的事务日志已满

1.4.1. 错误消息

尝试的命令 :

if COLUMNPROPERTY( OBJECT_ID('MSreplication_subscriptions'),'distribution_agent','AllowsNull') <> 1 BEGIN alter table MSreplication_subscriptions alter column distribution_agent sysname null end

 

错误消息 :

数据库  'dbtest  的事务日志已满。若要查明无法重用日志中的空间的原因，请参阅  sys.databases  中的  log_reuse_wait_desc  列。  ( 源 : MSSQLServer ，错误号 : 9002)

获取帮助 :  http://help/9002

1.4.2. 解决方法

-- 清空日志

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE remaius SET RECOVERY SIMPLE;

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (remaius_new_log, 1);

-- Reset the database recovery model.

ALTER DATABASE remaius SET RECOVERY FULL;

2. 进程无法向表“"dbo"."某表名"”进行大容量复制。

2.0.1. 错误消息

 

进程无法向表“"dbo"."某表名"”进行大容量复制。 (源: MSSQL_REPL，错误号: MSSQL_REPL20037)

 

获取帮助:  http://help/MSSQL_REPL20037

 

成批发送失败

 

从 bcp 客户端收到的列值对列 id 37 无效。 (源: MSSQLServer，错误号: 4896)

 

获取帮助:  http://help/4896

 

若要获取详细说明初始化订阅表时所遇到的错误的错误文件，请执行在下面显示的 bcp 命令。有关该 bcp 实用工具及其支持的选项的详细信息，请参阅 BOL。 (源: MSSQLServer，错误号: 20253)

 

获取帮助:  http://help/20253

 

bcp "某发布名称"."dbo"."某表名" in "H:SQLMSSQL10_50.UZAI2008MSSQLReplDatauncSQLCLUSTER$SQL2008_某库名_某发布名20120711171029某表名_2.bcp" -e "errorfile" -t"n<x$3>n" -r"n<,@g>n" -m10000 -S订阅名订阅实例名 -T -w (源: MSSQLServer，错误号: 20253)

 

获取帮助:  http://help/20253

 

2.0.2. 解决方法

因为这张表有一列很长且有特殊html标签类/<&%|^等特殊字符,去掉该列就很顺利的发布成功了。或者将所有的非unicode(varchar/char/text) 类型字段调整为unicode(nvarchar/nchar/ntext)类型即可"

 

1. 解决使用SQL Server发布数据库快照遇到错误：对路径”xxxxx“访问被拒绝的方法