Replication的犄角旮旯(一)--变更订阅端表名的应用场景
《Replication的犄角旮旯》系列导读
Replication的犄角旮旯(一)--变更订阅端表名的应用场景
Replication的犄角旮旯(二)--寻找订阅端丢失的记录
Replication的犄角旮旯(三)--聊聊@bitmap
Replication的犄角旮旯(四)--关于事务复制的监控
Replication的犄角旮旯(五)--关于复制identity列
Replication的犄角旮旯(六)-- 一个DDL引发的血案(上)(如何近似估算DDL操作进度)
Replication的犄角旮旯(七)-- 一个DDL引发的血案(下)(聊聊logreader的延迟)
Replication的犄角旮旯(八)-- 订阅与发布异构的问题
Replication的犄角旮旯(九)-- sp_setsubscriptionxactseqno,赋予订阅活力的工具
---------------------------------------华丽丽的分割线--------------------------------------------
接触Replication只有1年多的时间;曾追随JD首席DBR(DB for Replication)陈璟同鞋学习复制,受益匪浅;
关于SQLServer Replication的文章看过不少,大多以原理介绍、如何搭建复制居多。本文旨在从生产环境出发,挖掘Replication中各种犄角旮旯的功能,使其成为运维环节中便于使用的工具;
如无特殊说明,本系列均是基于transaction replication场景;
变更订阅端表名的应用场景
本文以之前我在SQL PASS活动上分享的“翻滚吧 Replication”为背景,相关PPT及demo如下:
http://pan.baidu.com/s/1bnzvsuz
场景描述:一般通过快照或备份初始化,订阅端表名与发布端一致;而我们要研究的是订阅端表名与发布端不一致时的应用场景(发布端 table、订阅端table_new)
用途:适用于在不影响当前复制链路的情况下,实现对同一订阅存在多个副本,以至于延伸到可以满足数据移动、表结构变更等用途;
案例:对于一个较大的且数据表,如果业务方提出要升级表结构(如int类型改为bigint),如何尽量减少停机操作时间?如果这个表参与复制呢?如果被修改的column是主键呢?
操作:
1、按照一般方法创建好一个publication,并添加需要发布的article;
2、编辑项目属性,参照下图,编辑“目标对象名称”、“名称已被使用时的操作”及“语句传递”
注:
a)对于修改表结构(int类型改为bigint类型)的需求,可以先在订阅端创建新结构的新表(如table_new),在通过指定“名称已被使用时的操作”为“现有对象保持不变”,让订阅在应用快照时只写入数据而忽略表结构上不一致;
b)事务复制是通过调用订阅端对应的ins、del、upd存储过程实现复制命令在订阅端的执行,为了不影响原有复制链路,需要自定义新的订阅端存储过程名
PRrevri6Ev//kX80Orw1UGrQ5Xr3/69PVPn77+8Sevfvjk5fdvXn7vvRfffaf5z3/avP3Hz79++d7jj+VhB+ERLPfZpnak7p3Fxd79Hq26TvAS45+3TYzxabw+tN56660+RpYMy7Q++elnbdu++uGTVxmr/uN/eP7v//CzP/yD55+/ePdvn8jDVuEhJ+tB0LLepoWO5v27Bkfu7N0VPHY1GIzxhp4CrS+/+KUMe+utt4qZVkqpjGEDofWTz34/6/9b+/p1++pV933gq+bXL59//uLZL168870ytNRd9wZ+PeiHZSXL4UoeRaQ8sndD73ZgGOOVvQm0elalmVkBWjcf3EhoffzjZ23bPv3ZN6swPv7xs5e/efXRD56+//1P3nv88bvfe/Ls5807f/ORPGwJLRVjwalfBZLalBXp2wJhsYrVSLyWrB4pkceo9i4L1RFaI4+fDYzxabwVtNTvCYdD60fPumnr9ev21W9fv/zN6xf/71Xz65fPv3zx2S9ePPt58+nPnv/FX93Iw5bYkBv3RkHLmqOdWXuJv6vVkd9j8Xj98fvHbp3byDmRhcEjwhifzMfOtJ7846dt2z792fNPfvLZx//02ZMfPWt+/fLm7z957/HH7/7tk3f+5qOnP33+5999Xx62nPKcidWSbE196/SrtjaLrSH5fRVHXjxSP0ye2+DZcwqXOHsY4936MNe0VGh99INPv1na/s2lrJfPv3752ecvnv3v5tP/1Tz96fNP/umzP/1LE1rplOdPrGp1qzUpv+ISf9cINvxa4wDjH/W4Np2OFj2HGOMdOoXWtVgQ+FWCpbmgNXL1oAqtm394+vq3rzs4ffyjZ09++Olnn794/39+8u4HT975Hx/9xV/ffPzjz+7/1/eyYw5OtWqkWp7V3RBazlRuDUltIVLdOcBg75ES6yTHjwhjfCZPgdaqv9NSofX+9z/Jlwt2OVb3heGPP3vyo2f3/0vhFMhJM9tlKRgmI623033ESfxYo8UYb+tj3xHjvccfv/zNq27d4JN//PSjHzz99OdNt27wL/765s+/+/6THz774z8zD16d3x2GOTFq5FC8rfmH33wAxbOHMcbSv7u9ffTho5sPbiLuoDUovofW45vHQ2uVM633PvhYLhf85Cf9t4XPPvrBp3/0Z+9sfpYxxhjP4rffvn9/SXf4efvtByNqlaH17veeNL9+2a0b/OgHn978/SdPf/K8Wzf4539186d/+f7NPzz9o/8EtDDGGC/uMrQwxhjjndiDFsYYY7xDAy2MMcaHsb56EGOMMd6hybQwxhgfxmRaGGOMD2MyLYwxxocx0MIYY3wYAy2MMcaHMde0MMYYH8bfQKvDFdDCGGO8ZwMtjDHGhzHQwhhjfBgDLYwxxocx0MIYY3wYAy2MMcaH8RrQumga104wYFz7eHYHH6eNMa7Bt7e3X3/1+cRZZSloqXCSLFF5ZlHNQpGsEsdbsByP880HN4s+uhtjfBR/9cUv37x5Mxu0usl63kyrSCyrUC0vgi2DlsO/rqTflYXBrRndQWvzYWCM9+A5obXE14MpBizeBKEVAZ7Vps+2eHd4hIEWxrj3kaDl7Cp+PdjnQ2oLshGnllrRb1xtBAcNtDDGvY8HLWfDqisL1W/8ZJVIy0Fo4dFOoeX8p8Q54WoV6aurq3/9l19NH/Bc7WCMpQ9zTWtGaDnxGYGCmZbaLNCayzLTivyHQy3f6o8CxjCeyyfJtCxlTflhKbEyXDlAAlpLe0ZobWWghfFcPgC0LH7EMy3ZiAxTe4lDy4/BU1yElvp/lOIfoqvSs+Tq6ip7+6//8qvstQuQkWlT2V4ZoHaHMQ5619CS043KnvhsVfwfehGQMlIlYrBrHLEPrfj/XdTCNAeS2+lrGuBsNFpelZWotTDGEe/9mlZqiwrF7/2KkWqz8q0znngtPNRLQKtJPgwWbGSGNBe01MYxxhHvOtPqbREo27ACnJKmxmlIwBxM4umeEVoy2IFNlmY1ZFoY78DHgBau2T20LD4V/9Og7k1r9VeqZF5lpV+XuxexrLdZRXkljGQL40EGWnjvTqG123SWnAnjdXyka1q4Tg+6I8YmX9hygQrj1UymhfdubuOEMe4NtPDeDbQwxr35ehDv3UALY9ybTAvv3UALY9wbaOG9++aDm83HgDHeiYEW3rtbhBBKxDUtjDHGFZlMC2OM8WEMtDDGGB/GQAtjjPFhDLQwxhgfxkALY4zxYczQQYwxxofxgpmWvGm3egNv+aikzU8KxhjjfXpZaEV2DYKW86jAzU8lxhjjpb2jTKvbKKLOet4S3MIY49N7vUxLQiWFUCMg57R2sZ/dvvkJxRhjvJwXh5aTZmWplZ9pjYAWDMMY45N5DWilb+WGFaa21m8XszGMMcbn844yrbTQai0egzHG+Hze8ppWVj7impbfI8YY45N5R6sH1bdqOdDCGOM6vTi00reN9pVgfPVE9n2jujfYFMYY4yN6DWjJHGsctJy98AljjGvwUtBSF1n0u9QMzPkKUVYvhmGMMT6fucs7xhjjwxhoYYwxPoyBFsYY48MYaGGMMT6MgRbGGOPDGGhhjDE+jIEWxhjjwxhoYYwxPoyBFsYY48MYaGGMMT6MgRbGGOPDeH5o3XxwgzHGGEv/67/8ao/Qar58/uLL57zyyiuvvJ74dZAfPnj44d99uF9oYbwrj/g3hjEuun3zpg3o8c3jB/cf1AutKU8ksR4gySNOMMZ4qNs3b5qvPi+GdaQ5G7TUR2qpD82yHi8ZAY/VGs/oOrfJtDBewqeCVpBAva1dDrQsOz36byONY4wx7nw2aKkBU6A1Ih9ywOlgFXSdzGRaGC9hoBXixyDSZIXOW2sbY4yxagmtt66vv/znX2RhZ4ZWsVzCyaKaU122FuEfPq7JtDBewhm03rq+7ibPjFtHgpbEwFzQ8rMi2Wm2oQYXB4Mxxrh3Cq2eWJJbR4KWtA8tP+lRUSTDZKRa0tzFlQ82fGiTaWG8hHtoZcTKuHVmaEXKVWjJyCLG1F0QC2OMg95sIUZHmmNBy8+QLAJZJGsEulb7q+MVTKaF8RI+G7QsqRWd+Ah41NYGBasA2/YDgTHGe/aplryHDvhywRhX4mbyhIX35s2g9U33QAtjvJiB1vn86MNHwYeSAC2M8cEMtE7mhw8e3n/7ftyngtbmZx9jvJDbqyugdVZ3Xw9mr19/9fkb7fXxzWOghTHeu4HWWa0SqzGI9fVXnz/68NF5oBV5jBhCo9V9vNE2ur7+5p/57e3WQ0FzSiVWLZlW/DRdxhJOVgw2VQzrA0aPDS2tF0BrQwGt86rqTMv5kVZ6jlJCWDGqZDvBFqxdsiLQ2q3ItLYU0Dqpas+0HDY4b2VwkXZqI0UyBZsdxFG0mrJM6+rqKvtjvXnzJtvVl0hl1Z3IPtiPGaol2lxQQOu8qj3TUk+KmlpFzuZFS+BkSRBIVmE6vGJdtJXUTMua+n0kpHt3BY9dDeaOgNZJRaY1ACTFsNalSHzbb0T2rtZCm0u9pjUdWrvSbgcGtE4sMi1FkhPWXhnstxMhX3sXk/7wnKNA2yqYaaXf+42Alvxq0SlJO4p8XXm5m94FRz7x286sujNOT0DrpCLTikLrYpOpFbmO3Mhas/pywtTxyFe0H0Uyrf7tuEzLqq6+lfO+Wj3YplUYPKJ470ALZao907LUnR2VRhISwZI41fwhXZLB+62hDRXJtCZCqzXSnWA+twS0rL6cg5K9Z9WDDd4R0DqpyLSGrR50IKFWsZjU3mWY3GUNyYGoczhoEy2UaY0DzGrQmivTkpFAC3WqPdNST4pPIGtDRVS6ncYX+eQ35Y8H7UFppuVM5cG17MULSP0uh0+R6mqwg43iJbRigjild1NA66Qi0zKVnib/bWtjQ428COTIWv6Qir2gPeiFgNawaXdr7XdlYERA67zaLNO6XC57gJZ6Uhwq+EjLUOS/tf4k6i4LWn5TaEM1o+6I4aQga2pGym5zREDrpNoy09oDtBBaTtx7cEsBrfOq6mtaCC2ncZkWmkdA66Qi00JoKZFpbSmgdV5VfE0LY3x28xDI87niTAtjfHqTaZ1OZFoY4/MaaJ1LVV7TSmT9Ispa1O7EqJLt+C2oK+OtjoIr3a218mhpcU0LoSVUX6aVKMgDf66P0E5tpFjiUM1nbYTEaGk19m2cLndvXXHUH/AitLqqvCNGoiK0hs74XZhERYQ0cjxOp3EC9QMIxqO5ZGVau32iI0KHUH2/00oUAUlroEglmUOd4LaDuuKwiweljhktJDXTaoEWQhNEplX+etBimBXst+NTJAWPfGuNxzkWELWhZKYVvxUs3xwiZKn2TEs9KSpsnFoWY9TICPP8BiMpVCQGLarswxZ/Dkj86R4I1SYyrUL2o8IjK4yXFKmWDcAJk3/LNNjiE9BaUy+AFkILiExLkQUJhyVqFdl+CiR1b7Gj9i7brCwqEoMWFZkWQrOLTGsAtJw8yUFUup0lQ1ZH8u2gTCsegxaVf03rcnfV+4VrWgjFtFmm1ZGm3RpawazFedsOoYvEVQYzte44aJFpbauGHxcjNLe2zLTuv31/D9BSz4sDCR9pGYr8t06nI6hJprU3cUcMhJbQZplW++1/RTeEFkLLiQ8bQrNry0yrBVro1CLTQmgJVXnvwcvWd57GGK9gbvF+OtV4l/eGp5NgXId5AuT5rBKrgmtaW/9bwhivYTKt06nWa1r9Z/pbFZfVWYva44ov53OWEU7U0EWJrbYe0ln6yOrEVBOvaY37ZbFaK9hUJCyLmevnzzP/jPr6GmidWHVnWt/KWfuezdHWzB75XZS/zF0ttFa3O30V0TJuMJG6QCtVk0Ar+1mxlJyv54KW2rXarDVI2ZoDreKRXsSvquPnJCqgdV7VeE2rbXNoqf9g0tN0uTtfW2FSFwMkTndWwKUEDxkgh6eOxxpkpIofUzw/55Z1Rwzrpk3thBnfoogFFYeR/pNTsjB1DFbLxb2z5VtA69Sqe/XgwFsiWeWWZMDlbqcXMYZiF1awHGTfuASJrKiWBNmTtlA5qFI1JWg5M358+nbSo0Hp3VCgxjHjY1K2pp6QYQJa51XtmZY6z2bTt5y7rVleympZbqTdyX6z3i35jVu70rrq3rTTbFtWt05FhSpmWqmC37AVv0V03jo5TXqrw9FjiwzPOhUz3ykYaJ1aXNPqCn4/Hctz5MzpVolV8aIxKRtD6+LNIkRGEfVY/EacvZHXYu+1qRl1Tcu6Ve4g/MjWnE7V/KYIG6fEOur1bv4LtM4rVg9+I2eSTafgyEydSe3CaicttJq6aLxpXfaoG9Yg/UPLTog/pMo1ItMawbbL3dTEWT0oq49IoeKZljXCSI9T8Qa0Tq3aMy3nX2B3gvrtdKPflW1YUlu4uNCyWo4MWNZ1mrXCsmadI7Vaq1zNtK8H/atcwQtC8ns/H2xBqsVHG1zW4bc2WEDrvOKalrl2QOXKaGip8Q60rL1tiTfWqILQcipajJTxakyFcjKt1mZAG05BihhzGvS/9ys25SAwy+oGrWZk9SCKqPbVg204e3Co4MzOca448ZGAtE2LGWq/8TEUm7XGXKesTCud630wOIX