Alwayson--使用证书创建高可用性组
--场景:
--有服务器SQLNode11,SQLNODE21,SQLNODE31三台在同一故障转移群集SQLNode01中
--的数据库服务器,安装SQL SERVER 2012 并配置启动alwayson高可用性组,服务使
--用localsystem运行,为SQLNode11上数据库DB1和DB2创建高可用性组,并将
--SQLNode21和SQLNode31加入到可用性组中。
--实现步骤概述：在各个节点上创建证书和端点并备份证书，使用备份依次在其他
--两个节点上创建登陆用户，在各个节点创建证书并为将连接权限赋予登陆用户，
--激活alwayson回话服务，在主节点SQLNode11上备份数据库并使用WITH NORECOVERY
--在从节点SQLNode21和SQLNode31上还原数据库，在主节点SQLNode11创建高可用性
--组，在从节点SQLNode21和SQLNode31上加入到可用性组，并在从节点SQLNode21
--和SQLNode31上将数据库添加到可用组中。
--1. 在节点SQLNode11创建和备份证书和创建镜像端点
USE master;
USE master;
GO
--=========================================================================================================
--创建Master key
IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys k WHERE k.Name='##MS_DatabaseMasterKey##')
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Master@key'
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.databases db WHERE db.[is_master_key_encrypted_by_server]=1)
BEGIN
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
END
GO
--=========================================================================================================
--创建和备份证书
IF NOT EXISTS (SELECT 1 FROM sys.certificates C WHERE C.Name='ServerCert_SQLNode11' )
BEGIN
CREATE CERTIFICATE ServerCert_SQLNode11 WITH SUBJECT ='ServerCert_SQLNode11'
END
GO
BACKUP CERTIFICATE ServerCert_SQLNode11 TO FILE='D:\ServerCert_SQLNode11.cer'
GO
--=========================================================================================================
--创建镜像专用的端点，并使用证书加密
--同一个实例上只能存在一个镜像端点
IF NOT EXISTS(SELECT * FROM sys.endpoints e WHERE e.[Type]=4)
BEGIN
CREATE ENDPOINT DBMirrorEndPoint
STATE=STARTED AS
TCP(LISTENER_PORT=5022)
FOR DATABASE_MIRRORING(AUTHENTICATION=CERTIFICATE ServerCert_SQLNode11, ENCRYPTION=REQUIRED,ROLE=ALL)
END
GO
--2. 在节点SQLNode21创建和备份证书和创建镜像端点
USE master;
GO
--=========================================================================================================
--创建Master key
IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys k WHERE k.Name='##MS_DatabaseMasterKey##')
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Master@key'
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.databases db WHERE db.[is_master_key_encrypted_by_server]=1)
BEGIN
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
END
GO
--=========================================================================================================
--创建和备份证书
IF NOT EXISTS (SELECT 1 FROM sys.certificates C WHERE C.Name='ServerCert_SQLNode21' )
BEGIN
CREATE CERTIFICATE ServerCert_SQLNode21 WITH SUBJECT ='ServerCert_SQLNode21'
END
GO
BACKUP CERTIFICATE ServerCert_SQLNode21 TO FILE='D:\ServerCert_SQLNode21.cer'
GO
--=========================================================================================================
--创建镜像专用的端点，并使用证书加密
--同一个实例上只能存在一个镜像端点
IF NOT EXISTS(SELECT * FROM sys.endpoints e WHERE e.[Type]=4)
BEGIN
CREATE ENDPOINT DBMirrorEndPoint
STATE=STARTED AS
TCP(LISTENER_PORT=5022)
FOR DATABASE_MIRRORING(AUTHENTICATION=CERTIFICATE ServerCert_SQLNode21, ENCRYPTION=REQUIRED,ROLE=ALL)
END
GO
--3. 在节点SQLNode31创建和备份证书和创建镜像端点
USE master;
GO
--=========================================================================================================
--创建Master key
IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys k WHERE k.Name='##MS_DatabaseMasterKey##')
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Master@key'
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.databases db WHERE db.[is_master_key_encrypted_by_server]=1)
BEGIN
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
END
GO
--=========================================================================================================
--创建和备份证书
IF NOT EXISTS (SELECT 1 FROM sys.certificates C WHERE C.Name='ServerCert_SQLNode31' )
BEGIN
CREATE CERTIFICATE ServerCert_SQLNode31 WITH SUBJECT ='ServerCert_SQLNode31'
END
GO
BACKUP CERTIFICATE ServerCert_SQLNode31 TO FILE='D:\ServerCert_SQLNode31.cer'
GO
--=========================================================================================================
--创建镜像专用的端点，并使用证书加密
--同一个实例上只能存在一个镜像端点
IF NOT EXISTS(SELECT * FROM sys.endpoints e WHERE e.[Type]=4)
BEGIN
CREATE ENDPOINT DBMirrorEndPoint
STATE=STARTED AS
TCP(LISTENER_PORT=5022)
FOR DATABASE_MIRRORING(AUTHENTICATION=CERTIFICATE ServerCert_SQLNode31, ENCRYPTION=REQUIRED,ROLE=ALL)
END
GO
--4. 在SQLNode11上创建登陆和用户，并赋予连接端点权限
USE master;
GO
--=========================================================================================================
--创建登陆和用户，并赋予连接端点权限
IF NOT EXISTS(SELECT 1 FROM sys.syslogins l WHERE l.[Name] ='ServerLogin_SQLNode21')
BEGIN
CREATE LOGIN ServerLogin_SQLNode21 WITH PASSWORD ='Auto@sql'
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.sysusers u WHERE u.[Name]= 'ServerUser_SQLNode21')
BEGIN
CREATE USER ServerUser_SQLNode21 FOR LOGIN ServerLogin_SQLNode21
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.certificates c WHERE c.[Name]= 'ServerCert_SQLNode21')
BEGIN
CREATE CERTIFICATE ServerCert_SQLNode21 AUTHORIZATION ServerUser_SQLNode21 FROM FILE='D:\ServerCert_SQLNode21.cer'
END
GO
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO ServerLogin_SQLNode21
GO
USE master;
GO
--=========================================================================================================
--创建登陆和用户，并赋予连接端点权限
IF NOT EXISTS(SELECT 1 FROM sys.syslogins l WHERE l.[Name] ='ServerLogin_SQLNode31')
BEGIN
CREATE LOGIN ServerLogin_SQLNode31 WITH PASSWORD ='Auto@sql'
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.sysusers u WHERE u.[Name]= 'ServerUser_SQLNode31')
BEGIN
CREATE USER ServerUser_SQLNode31 FOR LOGIN ServerLogin_SQLNode31
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.certificates c WHERE c.[Name]= 'ServerCert_SQLNode31')
BEGIN
CREATE CERTIFICATE ServerCert_SQLNode31 AUTHORIZATION ServerUser_SQLNode31 FROM FILE='D:\ServerCert_SQLNode31.cer'
END
GO
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO ServerLogin_SQLNode31
GO
--5. 在SQLNode21上创建登陆和用户，并赋予连接端点权限
USE master;
GO
--=========================================================================================================
--创建登陆和用户，并赋予连接端点权限
IF NOT EXISTS(SELECT 1 FROM sys.syslogins l WHERE l.[Name] ='ServerLogin_SQLNode11')
BEGIN
CREATE LOGIN ServerLogin_SQLNode11 WITH PASSWORD ='Auto@sql'
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.sysusers u WHERE u.[Name]= 'ServerUser_SQLNode11')
BEGIN
CREATE USER ServerUser_SQLNode11 FOR LOGIN ServerLogin_SQLNode11
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.certificates c WHERE c.[Name]= 'ServerCert_SQLNode11')
BEGIN
CREATE CERTIFICATE ServerCert_SQLNode11 AUTHORIZATION ServerUser_SQLNode11 FROM FILE='D:\ServerCert_SQLNode11.cer'
END
GO
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO ServerLogin_SQLNode11
GO
USE master;
GO
--=========================================================================================================
--创建登陆和用户，并赋予连接端点权限
IF NOT EXISTS(SELECT 1 FROM sys.syslogins l WHERE l.[Name] ='ServerLogin_SQLNode31')
BEGIN
CREATE LOGIN ServerLogin_SQLNode31 WITH PASSWORD ='Auto@sql'
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.sysusers u WHERE u.[Name]= 'ServerUser_SQLNode31')
BEGIN
CREATE USER ServerUser_SQLNode31 FOR LOGIN ServerLogin_SQLNode31
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.certificates c WHERE c.[Name]= 'ServerCert_SQLNode31')
BEGIN
CREATE CERTIFICATE ServerCert_SQLNode31 AUTHORIZATION ServerUser_SQLNode31 FROM FILE='D:\ServerCert_SQLNode31.cer'
END
GO
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO ServerLogin_SQLNode31
GO
--6. 在SQLNode31上创建登陆和用户，并赋予连接端点权限
USE master;
GO
--=========================================================================================================
--创建登陆和用户，并赋予连接端点权限
IF NOT EXISTS(SELECT 1 FROM sys.syslogins l WHERE l.[Name] ='ServerLogin_SQLNode11')
BEGIN
CREATE LOGIN ServerLogin_SQLNode11 WITH PASSWORD ='Auto@sql'
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.sysusers u WHERE u.[Name]= 'ServerUser_SQLNode11')
BEGIN
CREATE USER ServerUser_SQLNode11 FOR LOGIN ServerLogin_SQLNode11
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.certificates c WHERE c.[Name]= 'ServerCert_SQLNode11')
BEGIN
CREATE CERTIFICATE ServerCert_SQLNode11 AUTHORIZATION ServerUser_SQLNode11 FROM FILE='D:\ServerCert_SQLNode11.cer'
END
GO
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO ServerLogin_SQLNode11
GO
USE master;
GO
--=========================================================================================================
--创建登陆和用户，并赋予连接端点权限
IF NOT EXISTS(SELECT 1 FROM sys.syslogins l WHERE l.[Name] ='ServerLogin_SQLNode21')
BEGIN
CREATE LOGIN ServerLogin_SQLNode21 WITH PASSWORD ='Auto@sql'
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.sysusers u WHERE u.[Name]= 'ServerUser_SQLNode21')
BEGIN
CREATE USER ServerUser_SQLNode21 FOR LOGIN ServerLogin_SQLNode21
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.certificates c WHERE c.[Name]= 'ServerCert_SQLNode21')
BEGIN
CREATE CERTIFICATE ServerCert_SQLNode21 AUTHORIZATION ServerUser_SQLNode21 FROM FILE='D:\ServerCert_SQLNode21.cer'
END
GO
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO ServerLogin_SQLNode21
GO
--7. 在SQLNode11上激活alwayson回话
USE master;
GO
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END
GO
--8. 在书在SQLNode21上激活alwayson回话
USE master;
GO
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END
GO
--9. 在SQLNode31上激活alwayson回话
USE master;
GO
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END
GO
--10. 在SQLNode11上备份数据库
USE master;
GO
BACKUP DATABASE [DB1] TO DISK = N'D:\DB1_F1.bak' WITH NOFORMAT, NOINIT, NAME = N'DB1-完整数据库备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [DB2] TO DISK = N'D:\DB2_F1.bak' WITH NOFORMAT, NOINIT, NAME = N'DB2-完整数据库备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
--11. 在SQLNode11上创建可用性组
USE [master]
GO
CREATE AVAILABILITY GROUP [SQLNode01_AG001]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR DATABASE [DB1], [DB2]
REPLICA ON N'SQLNODE11' WITH
(
ENDPOINT_URL = N'TCP://SQLNode11.DCDemo.com:5022',
FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
),
N'SQLNODE21' WITH
(
ENDPOINT_URL = N'TCP://SqlNode21.DCDemo.com:5022',
FAILOVER_MODE = AUTOMATIC,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)
),
N'SQLNODE31' WITH
(
ENDPOINT_URL = N'TCP://SqlNode31.DCDemo.com:5022',
FAILOVER_MODE = MANUAL,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY)
);
GO
--12. 在SQLNode21和SQLNode31上加入可用性组
USE master;
GO
ALTER AVAILABILITY GROUP [SQLNode01_AG001] JOIN;
GO
--13. 将数据库备份拷贝到SQLNode21和SQLNode31上并还原数据库
USE master;
GO
RESTORE DATABASE [DB1] FROM DISK = N'D:\DB1_F1.bak' WITH NORECOVERY, NOUNLOAD, STATS = 5
GO
RESTORE DATABASE [DB2] FROM DISK = N'D:\DB2_F1.bak' WITH NORECOVERY, NOUNLOAD, STATS = 5
GO
--14. 在SQLNode11上备份日志
USE master;
GO
BACKUP LOG [DB1] TO DISK = N'D:\DB1_L1.trn' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5
GO
BACKUP LOG [DB2] TO DISK = N'D:\DB2_L1.trn' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5
GO
--15. 在SQLNode21和SQLNOde31上还原日志备份
USE master;
GO
RESTORE LOG [DB1] FROM DISK = N'D:\DB1_L1.trn' WITH NORECOVERY, NOUNLOAD, STATS = 5
GO
GO
RESTORE LOG [DB2] FROM DISK = N'D:\DB2_L1.trn' WITH NORECOVERY, NOUNLOAD, STATS = 5
GO
--16. 在SQLNode21和SQLNOde31上将DB添加到可用性组
USE master;
GO
ALTER DATABASE [DB1] SET HADR AVAILABILITY GROUP = [SQLNode01_AG001];
GO
ALTER DATABASE [DB2] SET HADR AVAILABILITY GROUP = [SQLNode01_AG001];
GO
