搭建混合场景下SQL Server数据库镜像

  • Article

准备环境

On-premise: 主体数据库服务器-本地虚拟机PEP-SQLBI01，装有SQL Server 2014 Enterprise默认SQL实例。

Azure (Global Azure): 镜像数据库服务器-在azure上创建一台虚拟机SQLONE1并且装有SQL Server 2014 Enterprise。并且开启1433和5022端口。

使用证书配置数据库镜像

1.在主体服务器上，在 PowerShell 窗口中运行 SQLCMD.EXE，然后创建并备份服务器证书。

SQLCMD -S PEP-SQLBI01

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password1';

GO

 

CREATE CERTIFICATE SQLOnPrem_cert WITH SUBJECT = SQLOnPrem certificate';

GO

 

CREATE ENDPOINT Endpoint_Mirroring

   STATE = STARTED

   AS TCP (LISTENER_PORT=5022, LISTENER_IP = ALL)

   FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE SQLOnPrem_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL);

GO

 

BACKUP CERTIFICATE SQLOnPrem _cert TO FILE = SQLOnPrem _cert.cer';

GO

 

执行结果

之前我已经创建过master key，所以这里显示已经创建了。执行命令后在SQL Server Management Studio中可以看到endpoint和certificate 已经创建出来。

2.在 镜像服务器SQLONE1 上，在 PowerShell 窗口中运行 SQLCMD.EXE，然后创建并备份服务器证书。

SQLCMD -S SQLONE1

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password1';

GO

 

CREATE CERTIFICATE SQLInCloud_cert WITH SUBJECT = 'SQLInCloud certificate for database mirroring';

GO

 

CREATE ENDPOINT Endpoint_Mirroring

   STATE = STARTED

   AS TCP (LISTENER_PORT=5022, LISTENER_IP = ALL)

   FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE SQLInCloud_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL);

GO

 

BACKUP CERTIFICATE SQLInCloud_cert TO FILE = 'SQLInCloud_cert.cer';

GO

执行结果：

3.证书将保存到默认的数据目录：C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA。在两个服务器上将证书复制到同一目录，以使每个服务器都拥有来自其他两个服务器的证书。由于两个 SQL Server 虚拟机位于两个不同的虚拟网络中，并且共享文件比较困难，因此在虚拟机之间复制文件的最简单方式为将本地计算机上的某个磁盘驱动器连接到远程桌面会话，然后将证书复制到所连接的这个磁盘驱动器和从其复制证书。

4.在 PEP-SQLBI01上，使用 SQLInCloud 的证书向其授予登录权限。

CREATE LOGIN DBMirroringLogin WITH PASSWORD = 'Sample_Login_Password!@#';

GO

 

CREATE USER DBMirroringLogin FOR LOGIN DBMirroringLogin;

GO

 

CREATE CERTIFICATE SQLInCloud_cert AUTHORIZATION DBMirroringLogin FROM FILE = 'SQLInCloud_cert.cer';

GO

 

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [DBMirroringLogin];

GO

 

执行结果：

5.在 SQLONE1 上，使用 SQLOnPrem 的证书向其授予登录权限。

CREATE LOGIN DBMirroringLogin WITH PASSWORD = 'Sample_Login_Password!@#';

GO

 

CREATE USER DBMirroringLogin FOR LOGIN DBMirroringLogin;

GO

 

CREATE CERTIFICATE SQLOnPrem_cert AUTHORIZATION DBMirroringLogin FROM FILE = 'SQLOnPrem_cert.cer'

GO

 

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [DBMirroringLogin];

GO

 

6.在 PEP-SQLBI01 上，创建一个数据库，然后进行一次完整备份和一次日志备份。

7.将备份文件复制到SQLONE1：C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\BACKUP。

8.在SQLONE1 上，用 WITH NORECOVERY 选项还原数据库备份，然后使它成为镜像伙伴。

RESTORE DATABASE MyDB1 FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\BACKUP\MyDB1.bak' WITH NORECOVERY;

GO

 

RESTORE LOG MyDB1 FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\BACKUP\MyDB1.log' WITH NORECOVERY;

GO

 

ALTER DATABASE MyDB1 SET PARTNER = 'TCP://<NAT_PUBLIC_IP_ADDRESS>:5022';

GO

执行结果：

注意，必须为内部 NAT 设备而非 On-Premise 的主机名提供公共 IP 地址。AzureVM 无法直接连接到 PEP-SQLBI01，因此必须将流量从 NAT 设备转发到 PEP-SQLBI01。已在内部 NAT 设备上为端口 5022 配置了端口转发，因此 ALTER DATABASE 命令应成功。

9.在 PEP-SQLBI01 上，使之成为镜像伙伴。

ALTER DATABASE MyDB1 SET PARTNER = 'TCP://<uniqueservicename>.cloudapp.net:5022';

GO

执行结果：

请注意，将使用唯一云服务名称 .cloudapp.net 而不使用 AzureVM 的主机名。在创建 SQLONE1 时，已打开了默认数据库镜像端点，端口 5022。

祝贺你！你已成功地使用证书在混合 IT 环境中设置了数据库镜像。

10.在主题服务器插入3行数据。

故障切换：

镜像服务器变成主题服务器：

参考文章

教程：混合 IT 环境中用于实现灾难恢复的数据库镜像  

https://msdn.microsoft.com/zh-cn/library/azure/jj870964.aspx

Microsoft China Partner Team