【项目笔记】SQL Server 2012 数据库镜像

一、背景：

        项目中需要双数据库运行，如果主数据库出问题时，软件就自动连接到备数据库，并且此间数据不能有差别。做到无痕切换，所以这里考虑要对数据库做镜像，因为要实现自动切换，所以必须要配置见证服务器。

二、配置镜像注意点：

        1.数据库的模式要是完整模式。
        2.要对数据库完整备份和事务日志备份，分别还原到镜像库上，使用 NORECOVERY 模式。
        3.实现数据库的故障自动切换。
        4.要进行sql server的镜像设置一定要使用sql server 的配置管理器开启TCP/IP协议，如果没有启用TCP/IP协议则只能在同一个网段内的机器配置镜像，前面的配置步骤里面所用到的IP地址要换成对应的实例名。同一个网段配置并使用镜像的时实性、传输速率更高，适用于大数据量的同步，跨网段或者跨公网的sql server 镜像一般适用于数据量小，时实性要求不高的数据同步，而且数据库在公网上同步也不安全。

三、配置镜像操作步骤：

1.主机名与对应IP：

主机：192.168.3.21  更改为 ServerA
备机：192.168.3.201 更改为 ServerB
见证：192.168.3.55  更改为 ServerC

2.更改hosts，添加域名解析：

        进入C:\Windows\System32\drivers\etc，找到hosts文件，编辑添加主机和备机的IP主机名，如：
        192.168.3.21  ServerA
        192.168.3.201 ServerB
        192.168.3.55   ServerC

3.主机数据/日志还原到备机

        主、备、见证数据库的名字，账号密码建议统一一样，服务不会自动维护用户密码，所以需要提前设置好，防止切换以后应用连接不上。

4.创建密钥：

1】、创建主密钥（主库、镜像库、认证服务器上都执行）：

use master
go
create master key encryption by password='abc@123456'
Go

2】、分别在主体服务器、镜像服务器、见证服务器上创建证书：
主库上执行：

use master
go
create certificate ServerA_cert with subject='ServerA certificate',expiry_date='2099-1-1'
go

镜像库上执行：

use master
go
create certificate ServerB_cert with subject='ServerB certificate',expiry_date='2099-1-1'
go

见证服务器上执行

use master
go
create certificate ServerC_cert with subject='ServerC certificate',expiry_date='2099-1-1'
go

5、分别在主体服务器、镜像服务器、见证服务器上创建端点：

主库上执行：

use master
go
create endpoint Endpoint_Mirroring
state=started
as tcp ( listener_port = 5022,listener_ip = all )
for database_mirroring ( authentication = certificate ServerA_cert, encryption = required algorithm aes, role = all )
go

镜像库上执行：

use master
go
create endpoint Endpoint_Mirroring
state=started
as tcp ( listener_port = 5022,listener_ip = all )
for database_mirroring ( authentication = certificate ServerB_cert, encryption = required algorithm aes, role = all )
go

见证服务器上执行

use master
go
create endpoint Endpoint_Mirroring
state=started
as tcp ( listener_port = 5022,listener_ip = all )
for database_mirroring ( authentication = certificate ServerC_cert, encryption = required algorithm aes, role = all )
go

6、备份证书（主体服务器、镜像服务器、见证服务器都备份，并互相拷贝过去，保证每个服务器上都有3个证书）

主库上执行：

use master
go
backup certificate mirror01_cert to file = 'D:\cert\ServerA_cert.cer'
go

镜像库上执行：

use master
go
backup certificate mirror02_cert to file = 'D:\cert\ServerB_cert.cer'
go

见证服务器上执行

use master
go
backup certificate mirror_witness_cert to file = 'D:\cert\ServerC_cert.cer'
go

7、创建登录名（这个要和证书关联，所以主体服务器、镜像服务器、见证服务器都要创建除自己以外的另外2个用户）

主库上执行：

--创建镜像库上的证书关联用户
use master
go
create ServerB_login with password='abc&123456'
go

--创建见证库上的证书关联用户
use master
go
create login ServerC_login with password='abc&123456'
go

镜像库上执行：

--创建主库上的证书关联用户
use master
go
create login ServerA_login with password='abc&123456'
go

--创建见证库上的证书关联用户
use master
go
create login ServerC_login with password='abc&123456'
go

见证服务器执行

--创建主库上的证书关联用户
use master
go
create login ServerA_login with password='abc&123456'
go

--创建镜像库上的证书关联用户
use master
go
create login ServerB_login with password='abc&123456'
go

8、创建使用该登录名的用户（主体服务器、镜像服务器、见证服务器都要创建）

主库上执行

--创建镜像库上的证书关联用户
use master
go
create user ServerB_user for login ServerB_login
go

--创建见证库上的证书关联用户
use master
go
create user  ServerC_user for login ServerC_login
go

镜像库上执行

--创建主库上的证书关联用户
use master
go
create user ServerA_user for login ServerA_login
go

--创建见证库上的证书关联用户
use master
go
create user ServerC_user for login ServerC_login
go

见证服务器执行

--创建主库上的证书关联用户
use master
go
create user ServerA_user for login ServerA_login
go

--创建镜像库上的证书关联用户
use master
go
create user ServerB_user for login ServerB_login
go

9、证书与用户关联

        把证书复制到各自的备份文件目录下。然后执行：

主库上执行

--使镜像库上的证书与用户关联
use master
go
create certificate ServerB_cert
authorization ServerB_user
from file='D:\cert\ServerB_cert.cer'
go

--使见证库上的证书与用户关联
use master
go
create certificate ServerC_cert
authorization ServerC_user
from file='D:\cert\ServerC_cert.cer'
go

镜像库上执行

--使主库上的证书与用户关联
use master
go
create certificate ServerA_cert
authorization ServerA_user
from file='D:\cert\ServerA_cert.cer'
go

--使见证库上的证书与用户关联
use master
go
create certificate ServerC_cert
authorization ServerC_user
from file='D:\cert\ServerC_cert.cer'
go

见证服务器执行

--使主库上的证书与用户关联
use master
go
create certificate ServerA_cert
authorization ServerA_user
from file='D:\cert\ServerA_cert.cer'
go

--使镜像库上的证书与用户关联
use master
go
create certificate ServerB_cert
authorization ServerB_user
from file='D:\cert\ServerB_cert.cer'
go

8、授予对远程数据库端点的登录名的CONNECT权限(每个上面2个用户都要有端点的权限)

命令：

主库上执行

use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [ServerB_login];
go

use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [ServerC_login];
go

镜像库上执行

use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [ServerA_login];
go

use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [ServerB_login];
go

见证服务器执行

use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [ServerA_login];
go

use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [ServerB_login];
go

10、备份并还原数据库：

        1.数据库做完整备份和事务日志备份，然后在镜像库上做还原：

        先还原完整备份，再还原事务日志，同样的，把主数据库的备份文件拷贝镜像数据库的备份文件夹内，还原镜像数据库，做到主备镜像数据库一致。注意：主数据库在还原数据库的选项中选择RESTORE WITH RECOVER还原，镜像数据库用RESTORE WITH NORECOVER还原，这个很重要。做完这个操作后,数据库的状态:正在恢复。

        2.镜像库上执行 （数据库名 MySQLData）：

use master
go
ALTER DATABASE MySQLData SET PARTNER = 'TCP://SERVERA:5022';
go

        3.主库上执行

--连接镜像库
use master
go
ALTER DATABASE  MySQLData SET PARTNER = 'TCP://SERVERB:5022';
go
--连接见证服务器
use master
go
ALTER DATABASE  MySQLData SET WITNESS = 'TCP://SERVERC:5022';
go

 到此处镜像完成！