数据库镜像实践

数据库镜像

参考转载自：

http://www.mssqlmct.cn/dba/?post=67

https://blog.csdn.net/dba_huangzj/article/details/27652857

1.创建数据库主密钥（如果主密钥不存在）。
2.在Master数据库中创建证书并用主密钥加密。
3.使用证书授权创建端点（endpoint）。
4.备份证书成为证书文件。
5.在服务器上创建登录账号，用于提供其他实例访问。
6.在master库中创建用户，并映射到上一步的登录账号中。
7.把证书授权给这些用户。
8.在端点上授权。
9.设置主体服务器的镜像伙伴。
10.设置镜像服务器的主体伙伴。
11.配置见证服务器。

---------------------

1. 　　查看终端点 select * from sys.endpoints 
2.    删除某终端点(终端点不带引号)   drop endpoint <endpoint_name> 
3.    删除证书  在master | Security | Certificates (drop master key...) 
4.    删除用户  在master | User 
5.    然后可以删除登录名  drop login <login_name> 
6.    修改master key  :   alter master key drop encryption by service master key 
7.    删除master key  : drop master key 
8.    删除镜像的命令: alter databse <dbname> set partner off

主库操作，所有步骤从库同步，注意修改名称

--USE master;
--DROP MASTER KEY

-----------创建 master 数据库主密钥
Use master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';

GO
------------
USE master;
-------------然后对服务器实例创建一个用于其数据库镜像出站连接的加密证书。
--drop CERTIFICATE SQLSVR1_cert
CREATE CERTIFICATE SQLSVR1_cert

   WITH SUBJECT = 'SQLSVR1 certificate for database mirroring', 

   EXPIRY_DATE = '12/31/2024';

GO
----------- 备份主体服务器 SQLSVR1 的加密证书。请确保此证书保存在安全可靠的存储介质上。

BACKUP CERTIFICATE SQLSVR1_cert TO FILE = 'C:\db\share_file\SQLSVR1.cer';

GO

------------- 使用主体服务器实例的证书 SQLSVR1_cert 为主体服务器 SQLSVR1 创建端点。
CREATE ENDPOINT [默认的镜像端点]
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5022
      , LISTENER_IP = ALL
   ) 

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

GO


--drop ENDPOINT 镜像
--select * from sys.endpoints 


--创建登录名
--在主体服务器实例的 master 数据库中为镜像服务器创建一个登录名，然后为新创建的登录名创建一个用户。再将用户与镜像服务器的证书相关联，并授予其对数据库镜像端点的 CONNECT 权限。

USE master;

CREATE LOGIN SQLSVR2_login 
   WITH PASSWORD = 'Sample@#';
GO

CREATE USER SQLSVR2_user FOR LOGIN SQLSVR2_login;
GO

CREATE CERTIFICATE SQLSVR2_cert
   AUTHORIZATION SQLSVR2_user
   FROM FILE = 'C:\db\share_file\SQLSVR2.cer'
GO

GRANT connect on endpoint::[默认的镜像端点] TO [SQLSVR2_login];
GO

--设定镜像伙伴（这一步需要从库先操作）
ALTER DATABASE AdventureWorks2012 
    SET PARTNER = 'TCP://192.168.214.12:5022';
GO

所有步骤从库同步，注意修改名称

Use master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';

GO
------------
USE master;
--drop CERTIFICATE SQLSVR2_cert
CREATE CERTIFICATE SQLSVR2_cert

   WITH SUBJECT = 'SQLSVR2 certificate for database mirroring', 

   EXPIRY_DATE = '12/31/2024';

GO
-----------

BACKUP CERTIFICATE SQLSVR2_cert TO FILE = 'C:\db\share_file\SQLSVR2.cer';

GO
---------

CREATE ENDPOINT [默认的镜像端点]

   STATE = STARTED

   AS TCP (

      LISTENER_PORT=5022

      , LISTENER_IP = ALL

   ) 

   FOR DATABASE_MIRRORING ( 

      AUTHENTICATION = CERTIFICATE SQLSVR2_cert

      , ENCRYPTION = REQUIRED ALGORITHM AES

      , ROLE = ALL

   );

GO

--drop ENDPOINT 镜像
--select * from sys.endpoints 






USE master;

CREATE LOGIN SQLSVR1_login 

   WITH PASSWORD = 'Sample@#';

GO

CREATE USER SQLSVR1_user FOR LOGIN SQLSVR1_login;

GO

CREATE CERTIFICATE SQLSVR1_cert

   AUTHORIZATION SQLSVR1_user

   FROM FILE = 'C:\db\share_file\SQLSVR1.cer'

GO

GRANT connect on endpoint::[默认的镜像端点] TO [SQLSVR1_login];

GO


----
ALTER DATABASE AdventureWorks2012 

    SET PARTNER = 'TCP://192.168.214.11:5022';

GO

加入见证服务器

--USE master;
--DROP MASTER KEY

-----------创建 master 数据库主密钥
Use master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';

GO
------------
USE master;
-------------然后对服务器实例创建一个用于其数据库镜像出站连接的加密证书。
--drop CERTIFICATE SQLSVR3_cert
CREATE CERTIFICATE SQLSVR3_cert

   WITH SUBJECT = 'SQLSVR3 certificate for database mirroring', 

   EXPIRY_DATE = '12/31/2024';

GO
----------- 备份主体服务器 SQLSVR3 的加密证书。请确保此证书保存在安全可靠的存储介质上。

BACKUP CERTIFICATE SQLSVR3_cert TO FILE = 'C:\db\share_file\SQLSVR3.cer';

GO

------------- 使用主体服务器实例的证书 SQLSVR3_cert 为主体服务器 SQLSVR3 创建端点。
CREATE ENDPOINT [默认的镜像端点]
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5022
      , LISTENER_IP = ALL
   ) 

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

GO


--drop ENDPOINT 镜像
--select * from sys.endpoints 


--创建登录名
--在主体服务器实例的 master 数据库中为镜像服务器创建一个登录名，然后为新创建的登录名创建一个用户。再将用户与镜像服务器的证书相关联，并授予其对数据库镜像端点的 CONNECT 权限。


USE master;

CREATE LOGIN SQLSVR2_login 
   WITH PASSWORD = 'Sample@#';
GO

CREATE USER SQLSVR2_user FOR LOGIN SQLSVR2_login;
GO

CREATE CERTIFICATE SQLSVR2_cert
   AUTHORIZATION SQLSVR2_user
   FROM FILE = 'C:\db\share_file\SQLSVR2.cer'
GO

GRANT connect on endpoint::[默认的镜像端点] TO [SQLSVR2_login];
GO


-----------



USE master;

CREATE LOGIN SQLSVR1_login 
   WITH PASSWORD = 'Sample@#';
GO

CREATE USER SQLSVR1_user FOR LOGIN SQLSVR1_login;
GO

CREATE CERTIFICATE SQLSVR1_cert
   AUTHORIZATION SQLSVR1_user
   FROM FILE = 'C:\db\share_file\SQLSVR1.cer'
GO

GRANT connect on endpoint::[默认的镜像端点] TO [SQLSVR1_login];
GO





--然后在主、从库上都运行

USE master;

CREATE LOGIN SQLSVR3_login
WITH PASSWORD = 'Sample@#';
GO

CREATE USER SQLSVR3_user FOR LOGIN SQLSVR3_login;
GO

CREATE CERTIFICATE SQLSVR3_cert
AUTHORIZATION SQLSVR3_user
FROM FILE = 'C:\db\share_file\SQLSVR3.cer'
GO

GRANT connect on endpoint::[默认的镜像端点] TO [SQLSVR3_login];
GO

--然后在主库上运行

ALTER DATABASE AdventureWorks2012 SET WITNESS = 'TCP://192.168.214.13:5022'

-- 验证结果

然后关掉主库的引擎服务，查看从库是否实现故障转移~

数据库镜像实践

成功~

秒客网

数据库镜像实践

相关文章