首页 > 开发 > 其他 > 正文

Replication的犄角旮旯(五)--关于复制identity列

2019-11-05 06:22:43
字体:
来源:转载
供稿:网友
Replication的犄角旮旯(五)--关于复制identity列

《Replication的犄角旮旯》系列导读

Replication的犄角旮旯(一)--变更订阅端表名的应用场景

Replication的犄角旮旯(二)--寻找订阅端丢失的记录

Replication的犄角旮旯(三)--聊聊@bitmap

Replication的犄角旮旯(四)--关于事务复制的监控

Replication的犄角旮旯(五)--关于复制identity列

Replication的犄角旮旯(六)-- 一个DDL引发的血案(上)(如何近似估算DDL操作进度)

Replication的犄角旮旯(七)-- 一个DDL引发的血案(下)(聊聊logreader的延迟)

Replication的犄角旮旯(八)-- 订阅与发布异构的问题

Replication的犄角旮旯(九)-- sp_setsubscriptionxactseqno,赋予订阅活力的工具

---------------------------------------华丽丽的分割线--------------------------------------------

今天被群友问到复制环境中identity属性的问题。在此通过几个测试说明一下identity列是如何在复制环境中实现的;

以下测试均是基于SQLSERVER 2012 SP1下的事务复制环境;

先抛出几个测试目的;

1、identity列和not for replication的关系,发布端及订阅端何时添加not for replication属性

2、快照初始化、备份初始化、不初始化订阅对not for replication参数的依赖

3、如何添加not for replication,在哪添加not for relication,以及替代not for replication的方法

先解释一下not for replication

NOT FOR REPLICATION

在 CREATE TABLE 语句中,可为 IDENTITY 属性、FOREIGN KEY 约束和 CHECK 约束指定 NOT FOR REPLICATION 子句。 如果为 IDENTITY 属性指定了该子句,则复制代理执行插入时,标识列中的值将不会增加。 如果为约束指定了此子句,则当复制代理执行插入、更新或删除操作时,将不会强制执行此约束。

http://msdn.microsoft.com/zh-cn/library/ms174979.aspx

简单说,对于identity、外键约束、check约束,可以通过指定not forreplication避免订阅端写入数据失败;

测试开始:

  先在同一个实例下创建两个库test_byxl_1、test_byxl_2,分别作为本次测试的发布库和订阅库;

  

  test_byxl_1下创建tb_ident_1表,结构如下

1 create table test_byxl_1.dbo.tb_ident_1 (id int PRimary key identity ,name varchar(10))
View Code

  添加这个表的发布,只创建publication、添加article即可;

  QQiww9/BlSew4eFbX9d69X/Xu/aprx9aOjWtbln/U8P7bta++VD1+3IX/77/LqurnfPRXeScIBAKB4EbGG5l1rT3fXmqxN+paezLeyEzYEGnDhvdWnGnb/ld7o7fiTNqw4aJD90k93heIsqB89OS5gbvfI+RBLDDv6BVJfL63WNf1jo1rW4n8+YLyd+4/flr67//n6f/xv5Wcr525IFvWyZJnNZYnNwg3aZqm/WLylmDzT/7wC0GOpmnaw0uu5B29krfl/R/d//4n1KCf/OEXwU3Rx6HJ92vWnWx5/0fUbMP2OnxKCAQCwcaLby693tJz/GKLvXG9pefFN5cmbIi0YcN7zpe0fO5v3eZv3eZv/TwYpKaFFELl1rBYH2z1+frBhqFCz/mStGHDRYduyQ3aAkdNuu+XE0aMmkQC5SFe7r8zgLAMYoFbj12VxJY9J8mN4P+p/8+73/+9t3+g47vemy3fXatru3C5sbSs9ljJ5VffXWVqNedJStQ+JpWFU+5/do6p/4+f1Z78KzNo4ZT7fzElL/hwxR9+8aM/FHLnNufJUOd5H/zo/g9WUJUr/vCL0LhRR+GU+zXrTvI++JE2ONuwXQtNybb4+FnqeCIQCC/ES0FFa7Y3rrf0vPTmUpUhPlq1efxEnyg+WrVZZYi0YcO7zp5szl0zGFvWNG8Jlte+P9focO37c7k1bNzMXdN19mTasOGiQ7f4euh9gaMmaSNGTXpw9OuGDaA8xMvdvf0IyyAWuK2oRhKbvjym6/qXB8/uOFCa9/Wpv+44erPtu4/X7Zq/Im/Wn9ZPe2/VweMXX3xzKaft1rn33j93JSmYl/Cee4fkZD6nPbmRaXvkhfsfeGFr8OHKPz4gWQtMywyNpWnakxu3FW1M0557p6hm5R8fCG6KLI68cH+oz7By5JH5nEZ2P8rROZu0+59Li2VKCATCefHSrGW1LT3HLrbYG7UtPS/NWqYyxPiJPskbh8ZP9KkMkTZs+Hclx5s2rWrauKpp46rGUIHE+Ik+453l4yf6st95z1TTuHFVI5XftGkV6eq7kuNpw4aLDt282tD7AokFjnDGKhTKTijf7upFWAaxwC+O10pi3bZCXdf//o9/9P/t7u3u/pb2rusNt6quNQUu1p04e7XgxKU9h86n/2Exp+3n8+4dMS+LLgTjaMaI9HdJeWm6wO4eyPg8mJ81/YF7px8d7HZpethDUjM2/d4R896dnv7IWI4ymvNlcTRjhPbIUrYceSxN18L2OtLRo0tAIBAui5ffWl7b0nP0You9UdPS+/Jby1WGsLRAUcPalh5jiLRhwztPHmlYt6zev6zev6x+3fJQYVm9f9ma2e+Mn+j7ZwhTec3sdxr8y+r9y8i/9f5l9f7lpNx58kjasOGiQ/deTd/IkSPJdV9LGTWJxIOjX0cZ5Vud3QjLIBa4/dtaSXy6ed8//vHPLw+e3frN6U07i7LzDlZfa16UtW3Okk1/mLf6xVlLt+8t/d3v53PafjHv3hHzsqhC1owH7p1xdPu3RzNGpL9Hcpala+M2Mm2PZox4IOOL4MNgq2U8Xwx1+8iMefeOmJf1xbx7tWDPWTMeeGSZbL94sfGRsJ6PZozQHlk2WMnvkBo0rIfQ9Ez19844Gqp5IGNGOlVpGp07QzKlSPcLgUA4N15+a3ltS+/Ri632BrFAY4ialp5jF1u5YVig6V4wtRbIb0gPQSywbs2SutVL6tYsubFmSd2awcKN1UtWvz17/ETf9+GMn+hb/fbsYNrqwVZ1oVbEAkWH7q1rvSNHjiTXfS1l1KT7fjnB8IDYymtP6GULbOgn/mXf/nq9NdcXadsNx/XzC2zu01nllvbvELzY8bw2JS/0kFjglyfrJLFsw56/3f2+u/dOW0d3fVPH5dqWssr60+dqDp+q+uZI+Zf7zuZ+deqJjPc4bb+Yf++I+X+hCn+Z8cC9M4q+PFn04oj090nO8nRt3GdM26IXRzzw4hfBh8FWy9PvnVFEmgQLwW6LXhzxwIvLQ2OdHGz16HLZfgmi6MURWqhh0YsjNE2jpmqUTbsZrP/sUc1oW/f+OE0LTumzRzVjd4z+P3tU04L7/sX8e4MJ9OiW00MgEF6IqW9/UlLZtO7rADfe+nCN5E17b324RtSwpLJp6tufqAwxfqKvra2tra1t/ESfvFIyRNqw4VfWrziYNkISi8Y+YXRLel409gl5kyvrV6QNGy46dDOu9pL3BQYt8MHRr6f49teH7jSfyIx6JSnPsMDoVqQWFOt68QajPn17q37zSHowZ97Wm7pevIHKn5fbRH8dW9mCYP2GE3prrk86VsjYIpznhpP6pQWR95m+vZXs14Jiar5N+9ONnMyyUO2lBaMmLSjW67fPM46JUQ4dB/EcYi43tXSGxxfGUlJ6HlV/avHPBpdu/mP+KWPT2fk/D8+0yLc78iabpzq4C5Nzo+/5i3SqObHAnaduSOKjNTt6eu98sbdk81cn/NsOr9r0TfH5mveXbZ65cO3UOZ+k//FPG7cff/T5tzltt8//8Yj5f6EKf5n5wKOf3Nh56vhLI57/gOR88rzojvBL24P9BFtxM0n/1BAfjONkPfqJaW6fPcqMEorjL40w8uky+5DaTe35D8i+GPOhHzIz//HM4ztPffbo4OjHXxpByoIh+NNDIBBeiKlvf1Ja1bQ+/xw36PfPsYyf6BM1LK1qmvr2J2FDfHOOG+Mn+hoaGhoaGkyKaVSKGpZW3TSGIBZYODr10JjUwjHBfwvHpBaOGVk4emThmNTFT48z+jQYP9G3+OlxhaNHFo5OPTQ69dDo1MIxIwvHjCwcnUqCWKDo0L12pZe8LzBl1CRtwahJKaM2nGjaP4GsBvn21+tlc6NcSdpwIvq2k1JGTbrv5b1EpFKCK4tGeUKKb399U2v43OblNA46630fnTcUdsK2lvrt86Kcg6y8gWhuZG19++sb9xLhW1Cs129715yTWaYbxz9Y/25uk7HvVPmj87reWl9cdqKJ148d5fqbt6jY9qymPbuFlEvn/nxSDqnfMknT/mPuyVDayUU/C0szyqGQ5dsYpXN/rmk/n/Rs2AQGp52Trmnp26LtfNuzWmj3b94iFri7uF4S81ds7bjd09hy+2pd24XLjSVltceKq/cfq9h1MLB1T/GmHSfW5h35f5+ZwWm7Y8GPifQ89fyPUxasKa6f+1Tqyzvqdxcffznl+bkkZ8Xz2lM5TNvjL6eQzPrdxfVzn9IeW0FtXfH8j984zhkrZcEaqmbNG6lhrVTj+MspxnB0mX1I7+bzc8m+0HMwHprqg5HzmGbso7G/giH400MgEF6IV2Z/cqb65l/3lXFj/ERfjZjxE32ihmeqb74y+xN6iI37yrhhOYSo4ZnqZmOItGHDr27MOjZ+FBO/OjZ+1J8njKdHMZX/PGE8r+GoY+NHXd2YlTZsuPDQVVN3hIleUKtB83KbWrfOjG4lacMJvWxxTCtSmblNOhG4B1ddolfL0re31m+ft6BYP7mKXhULX7n07a8nK4I

发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表