SE の雑記

USE master
GO
CREATE MASTER KEY ENCRYPTION BY Password='PrimaryP@ss'
GO
BACKUP MASTER KEY TO FILE='C:tempDEK_Primary_MasterKey'
ENCRYPTION BY Password = 'Primary_DEKP@ss'
GO

USE master
GO
CREATE CERTIFICATE DEKCert
WITH Subject ='DEK Certificate'
GO
BACKUP CERTIFICATE DEKCert
TO FILE= 'C:tempDEK_Cert.cer'
WITH PRIVATE KEY
(
	FILE = 'C:tempDEK_PrivateKey.pvk',
	ENCRYPTION BY PASSWORD= 'CERTP@ss'
)

USE SyncDB01
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE DEKCert
GO
ALTER DATABASE SyncDB01 SET ENCRYPTION ON
GO

USE master
GO
CREATE MASTER KEY ENCRYPTION BY Password='SecondaryP@ss'
GO
BACKUP MASTER KEY TO FILE='C:tempDEK_Secondary_MasterKey'
ENCRYPTION BY Password = 'Secondary_DEKP@ss'
GO

USE master
GO
CREATE CERTIFICATE DEKCert
FROM FILE= 'C:tempDEK_Cert.cer'
WITH PRIVATE KEY
(
	FILE = 'C:tempDEK_PrivateKey.pvk',
	DECRYPTION BY PASSWORD= 'CERTP@ss'
)

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:Connect ALWAYSON-SQL-01
IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
	ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END
GO
use [master]
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [ALWAYSONSQL2014-Service-User]
GO
:Connect ALWAYSON-SQL-02
IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
	ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END
GO
use [master]
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [ALWAYSONSQL2014-Service-User]
GO
:Connect ALWAYSON-SQL-01
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
:Connect ALWAYSON-SQL-02
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
:Connect ALWAYSON-SQL-01
USE [master]
GO
CREATE AVAILABILITY GROUP [AlwaysOn-AG]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR DATABASE [SyncDB01]
REPLICA ON N'ALWAYSON-SQL-01' WITH (ENDPOINT_URL = N'TCP://AlwaysOn-SQL-01.AlwaysOn.local:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)),
	N'ALWAYSON-SQL-02' WITH (ENDPOINT_URL = N'TCP://AlwaysOn-SQL-02.AlwaysOn.local:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));
GO
:Connect ALWAYSON-SQL-01
USE [master]
GO
ALTER AVAILABILITY GROUP [AlwaysOn-AG]
ADD LISTENER N'AlwaysOn-LN' (
WITH DHCP
 ON (N'10.0.0.0', N'255.0.0.0'
)
, PORT=1433);
GO
:Connect ALWAYSON-SQL-02
ALTER AVAILABILITY GROUP [AlwaysOn-AG] JOIN;
GO
:Connect ALWAYSON-SQL-01
BACKUP DATABASE [SyncDB01] TO  DISK = N'\ALWAYSON-AD-01QuorumSyncDB01.bak' WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5
GO
:Connect ALWAYSON-SQL-02
RESTORE DATABASE [SyncDB01] FROM  DISK = N'\ALWAYSON-AD-01QuorumSyncDB01.bak' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5
GO
:Connect ALWAYSON-SQL-01
BACKUP LOG [SyncDB01] TO  DISK = N'\ALWAYSON-AD-01QuorumSyncDB01_20141222054852.trn' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5
GO
:Connect ALWAYSON-SQL-02
RESTORE LOG [SyncDB01] FROM  DISK = N'\ALWAYSON-AD-01QuorumSyncDB01_20141222054852.trn' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5
GO
:Connect ALWAYSON-SQL-02
-- Wait for the replica to start communicating
begin try
declare @conn bit
declare @count int
declare @replica_id uniqueidentifier
declare @group_id uniqueidentifier
set @conn = 0
set @count = 30 -- wait for 5 minutes
if (serverproperty('IsHadrEnabled') = 1)
	and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)
	and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)
begin
    select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N'AlwaysOn-AG'
	select @replica_id = replicas.replica_id from master.sys.availability_replicas as replicas where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id
	while @conn <> 1 and @count > 0
	begin
		set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)
		if @conn = 1
		begin
			-- exit loop when the replica is connected, or if the query cannot find the replica status
			break
		end
		waitfor delay '00:00:10'
		set @count = @count - 1
	end
end
end try
begin catch
	-- If the wait loop fails, do not stop execution of the alter database statement
end catch
ALTER DATABASE [SyncDB01] SET HADR AVAILABILITY GROUP = [AlwaysOn-AG];
GO
GO