sql语句大全之最近帮客户实施的基于SQL Server Al

当前位置:

首页 > 数据库 > sql数据库 >

sql语句大全之最近帮客户实施的基于SQL Server Al

最近一个来自重庆的客户找到走起君，客户的业务是做移动互联网支付，是微信支付收单渠道合作伙伴，数据库里存储的是支付流水和交易流水。

由于客户那边没有DBA，所以找到走起君商量一个数据库服务器搬迁项目。

项目背景

客户需要把在10楼的服务器全部搬到15楼，而且需要在有限的停机时间之内，客户使用的数据库是SQL Server2008R2，Windows2008R2

客户的两个重要要求

1、总停机时间少于10分钟

2、数据不能有任何丢失

出方案

针对这两个要求，SQL Server有哪些可以选择的方案呢？

方案一复制

使用复制，当前客户环境已经有一套数据库复制在跑，10楼的发布库不动，在15楼增加一个订阅库，数据复制到15楼，但是复制有一个致命点：不保证数据一致性，因为复制是异步的

复制只能满足要求一，不能满足要求二，只能抛弃这个方案

方案二日志备份

在15楼增加一台数据库服务器，10楼的发布库做完整备份还原到15楼的数据库，然后在搬迁的时候追加一个日志备份，并还原到15楼的数据库服务器

日志备份保存的数据是完整备份到日志备份这个时间段的数据，由于每天写入的变更数据量比较大，导致ldf文件也比较大，达到40G+，在测试过程中

发现，kill掉数据库所有连接-》设置数据库为只读模式-》备份-》移动日志备份文件-》还原日志备份文件-》设置数据库为读写模式，整个过程花费时间超过15分钟

只能满足要求二，不能满足要求一，并且一旦迁移过程出错，回滚时间+迁移时间>要求的停机时间

回滚：一旦15楼的数据库有数据写入，要回滚需要完整备份数据库或分离数据库然后还原到10楼或附加到10楼的数据库，回滚时间无法满足小于10分钟的要求

方案三 AlwaysOn

跟客户商量沟通之后，最终选定SQL Server的AlwaysOn

从示意图可以看出，目前的架构需要做如何升级

增加一个成都机房

所有数据库升级到SQL Server2014 SP2

所有操作系统升级到Windows2012R2

回滚：一旦15楼的数据库有数据写入，要回滚可以先kill掉数据库所有连接，禁用数据库帐号不让连接数据库，等成都从库同步完数据之后，重新手动故障转移回去成都机房

整个回滚过程10分钟之内可以搞定

然后哔哩吧啦哔哩吧啦过了一个月，客户说软件和硬件环境都已经准备好了，当中数据库升级过程走起君也有参与在内

升级完毕之后的环境

操作系统：Windows2012R2

数据库：SQL Server2014 SP2

两边机房带宽：各10M 没有拉专线

VPN：使用华为防火墙内置的VPN功能

数据库大小：100G+

AlwaysOn节点数：5个重庆机房3个成都机房2个

升级之后的示意图

到目前为止，大家可能已经猜到走起君做了这个架构之后要怎麽做了

由于是点对点VPN，所以切换过程涉及拆除VPN和重建VPN的过程

切换过程

（1）主库切换到成都机房

（2）拆除10楼到成都机房的VPN

（3）10楼所有服务器关机搬到15楼

（4）15楼所有服务器开机

（5）重建15楼到成都的VPN，建好VPN之后，成都机房的主库和域控会自动与重庆机房的域控和从库通信，主库会把差异数据发回重庆，无须人工介入

（6）成都机房主库切换回去重庆机房15楼

这里有一个比较严重的问题

客户没有使用专线，两边机房只有10M带宽！

重要的问题说三遍！

这样一个低成本的架构，没有专线，带宽不高，只用硬件防火墙的VPN搭建起来的内网，SQL Server可以做得到吗？？？

答案是：没问题，SQL Server完全做得到！！！

这里软件环境需要满足下面要求

1、操作系统必须是Windows2012R2或以上版本

2、数据库必须是SQL Server2012或以上版本

再次用文字描述一下切换过程
第一步：在重庆机房节点kill掉所有数据库连接并设置程序用数据库帐号设置为禁用，禁止连接数据库
第二步：打开AlwaysOn的AG的属性界面，将成都异地节点改为同步提交模式
第三步：使用脚本查看当前数据库中各个表的记录数，脚本地址：http://www.cnblogs.com/lyhabc/p/3828496.html
第四步：打开AlwaysOn的显示面板，查看成都机房节点数据同步情况，如果已经追上主库的日志那么实施故障转移
第五步：手动进行故障转移
第六步：在成都机房节点查看AlwaysOn的转移情况
第七步：在成都机房节点使用脚本验证当前数据库中各个表的记录数是否与手动故障转移之前的记录数相同，脚本地址：http://www.cnblogs.com/lyhabc/p/3828496.html
第八步：在成都机房节点打开AlwaysOn的AG的属性界面，将所有的辅助副本都改为异步提交模式
第九步：拆除10楼到成都的VPN
第十步：重庆机房所有数据库服务器关闭SQL服务然后关机
第十一步：所有服务器搬到15楼并开机
第十二步：重建15楼到成都的VPN
第十三步：在成都机房节点kill掉所有数据库连接并设置程序用数据库帐号设置为禁用，禁止连接数据库
第十四步：在成都机房节点打开AlwaysOn的AG的属性界面，将原来重庆机房的主副本节点改为同步提交模式
第十五步：使用脚本查看当前数据库中各个表的记录数，脚本地址：http://www.cnblogs.com/lyhabc/p/3828496.html
第十六步：打开AlwaysOn的显示面板，查看重庆机房节点数据同步情况，如果已经追上主库的日志那么实施故障转移
第十七步：手动进行故障转移
第十八步：在重庆机房节点查看AlwaysOn的转移情况
第十九步：在重庆机房节点使用脚本验证当前数据库中各个表的记录数是否与手动故障转移之前的记录数相同，脚本地址：http://www.cnblogs.com/lyhabc/p/3828496.html
第二十步：在重庆机房节点打开AlwaysOn的AG的属性界面，将成都节点副本改为异步提交模式

整个过程非常顺利，没有数据丢失，停机时间控制在10分钟之内

原理

相信不少人都用过SQL Server的AlwaysOn集群，AlwaysOn集群真的是非常方便，随意切换

数据做了加密和压缩，数据库块级别的传输
数据自动补偿
切换和回切不需要重建集群
操作傻瓜化
数据0丢失

重庆机房关机时间段数据自动补偿，避免数据丢失

两个停机时间点，每个时间点大约5分钟

时间点1

时间点2

最后一个，之所以要使用Windows2012R2操作系统，是因为Windows2012R2引入了动态仲裁机制，也就是说当前WSFC集群只有一个节点的情况下

整个WSFC集群也会不会挂掉

利用这个机制，当重庆机房所有服务器关机的情况下，成都机房的数据库节点依然能working，这个相比Windows2008R2是一个相当大的进步

这里有一个注意点

在Windows2008R2时代，因为没有动态仲裁机制，所以需要将异地节点的投票权去掉，这里有几个原因

1、当异地节点挂掉之后，整个WSFC集群节点凑不够基数，导致整个WSFC集群失去仲裁挂掉

2、主库无故切换到异地节点（设置为手动故障转移防止这种情况发生）

3、SQL2012异地节点无故变为正在解析状态（重启异地节点数据库服务器的SQL Server服务解决这个问题，现在SQL2014 SP2没出现过这个问题）

而到了Windows2012R2时代，有些老司机依然会继续使用这种做法，把异地节点的投票权去掉，这样做的话，当前整个WSFC集群没有一个节点拥有投票的情况下整个WSFC集群就会挂掉，成都机房的AG就会显示“正在解析”，这是因为当前整个WSFC集群里面没有一个节点拥有投票权，即使成都这个节点在开机状态，所以提醒一下大家，如果操作系统是Windows2012R2，不需要把异地节点投票权去掉，因为到目前为止，在上面的三种情况下，第二和第三种情况通过方法可以解决，第一种情况因为Windows2012R2引入了动态仲裁机制也不会发生