Configure SQL Server 2017 Always On on Windows Server 2019 without domain Step by Step

Jiayi Yang AlwaysOn AG • SQL Server • Windows Server 2019年3月7日

--#SQL1#

--プライマリ レプリカをホストするインスタンスごとに、データベース マスター キーが必要です。 マスター キーがまだ存在していない場合は、次のコマンドを実行します。
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
GO

--プライマリ レプリカとなるインスタンスで、セカンダリ レプリカの着信接続用と、プライマリ レプリカのエンドポイントをセキュリティで保護するために使用される証明書を作成します。
CREATE CERTIFICATE SQL1_Cert
WITH SUBJECT = 'SQL1 Certificate';
GO

--証明書確認
select *
from sys.certificates
go

--証明書をバックアップします。 また、必要であれば、秘密キーを使ってさらにセキュリティで保護することもできます。 この例では、秘密キーを使用しません。
BACKUP CERTIFICATE SQL1_Cert 
TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\SQL1_Cert.cer';
GO

--可用性グループによって使用されるエンドポイントを作成します。 可用性グループの場合は、エンドポイントに DATABASE_MIRRORING の型がある必要があります。 エンドポイントでは、手順 4 で認証のインスタンスのために作成した証明書を使用します。
CREATE ENDPOINT DIAG_EP
STATE = STARTED
AS TCP (LISTENER_PORT = 5022,LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE SQL1_Cert,ROLE = ALL)
GO

--#SQL2#

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
GO

CREATE CERTIFICATE SQL2_Cert
WITH SUBJECT = 'SQL2 Certificate';
GO

BACKUP CERTIFICATE SQL2_Cert 
TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\SQL2_Cert.cer';
GO

CREATE ENDPOINT DIAG_EP
STATE = STARTED
AS TCP (LISTENER_PORT = 5022,LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE SQL2_Cert,ROLE = ALL)
GO

--#SQL1#
--SQLログインを作成する
CREATE LOGIN AG_Login WITH PASSWORD = '<password>'
GO

--SQLログインユーザーを作成する
CREATE USER AG_User FOR LOGIN AG_Login
GO

--#2の証明書を使って、作成したSQLログインユーザーに認証を付けて、証明書を作成する
CREATE CERTIFICATE SQL2_Cert
AUTHORIZATION AG_User
FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\SQL2_Cert.cer'
GO

--エンドポイントに接続権限を設定する
GRANT CONNECT ON ENDPOINT::DIAG_EP TO AG_Login
GO

--#SQL2#
--SQLログインを作成する
CREATE LOGIN AG_Login WITH PASSWORD = '<password>'
GO

--SQLログインユーザーを作成する
CREATE USER AG_User FOR LOGIN AG_Login
GO

--#2の証明書を使って、作成したSQLログインユーザーに認証を付けて、証明書を作成する
CREATE CERTIFICATE SQL1_Cert
AUTHORIZATION AG_User
FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\SQL1_Cert.cer'
GO

--エンドポイントに接続権限を設定する
GRANT CONNECT ON ENDPOINT::DIAG_EP TO AG_Login
GO

--#SQL1#
-- データベースの作成
CREATE DATABASE agTestDB
go

-- テーブルの作成
USE agTestDB
CREATE TABLE agTest1
( colA int PRIMARY KEY
 ,colB int
 ,colC varchar(20))

-- データを 2件追加
INSERT INTO agTest1 VALUES(1, 111, 'AAA')
INSERT INTO agTest1 VALUES(2, 222, 'BBB')

-- データの確認
SELECT * FROM agTest1

--完全バックアップ
BACKUP DATABASE [agTestDB] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\agTestDB.bak' WITH NOFORMAT, INIT,  NAME = N'agTestDB-完全 データベース バックアップ', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10, CHECKSUM
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'agTestDB' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'agTestDB' )
if @backupSetId is null begin raiserror(N'確認に失敗しました。データベース ''agTestDB'' のバックアップ情報が見つかりません。', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\agTestDB.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

100

101

102

103

104

105

106

107

108

109

110

111

112

--#SQL1#

--プライマリレプリカをホストするインスタンスごとに、データベースマスターキーが必要です。マスターキーがまだ存在していない場合は、次のコマンドを実行します。

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

--プライマリレプリカとなるインスタンスで、セカンダリレプリカの着信接続用と、プライマリレプリカのエンドポイントをセキュリティで保護するために使用される証明書を作成します。

CREATE CERTIFICATE SQL1_Cert

WITH SUBJECT = 'SQL1 Certificate';

--証明書確認

select *

from sys.certificates

--証明書をバックアップします。また、必要であれば、秘密キーを使ってさらにセキュリティで保護することもできます。この例では、秘密キーを使用しません。

BACKUP CERTIFICATE SQL1_Cert

TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\SQL1_Cert.cer';

--可用性グループによって使用されるエンドポイントを作成します。可用性グループの場合は、エンドポイントに DATABASE_MIRRORING の型がある必要があります。エンドポイントでは、手順 4 で認証のインスタンスのために作成した証明書を使用します。

CREATE ENDPOINT DIAG_EP

STATE = STARTED

AS TCP (LISTENER_PORT = 5022,LISTENER_IP = ALL)

FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE SQL1_Cert,ROLE = ALL)

--#SQL2#

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

CREATE CERTIFICATE SQL2_Cert

WITH SUBJECT = 'SQL2 Certificate';

BACKUP CERTIFICATE SQL2_Cert

TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\SQL2_Cert.cer';

CREATE ENDPOINT DIAG_EP

STATE = STARTED

AS TCP (LISTENER_PORT = 5022,LISTENER_IP = ALL)

FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE SQL2_Cert,ROLE = ALL)

--#SQL1#

--SQLログインを作成する

CREATE LOGIN AG_Login WITH PASSWORD = '<password>'

--SQLログインユーザーを作成する

CREATE USER AG_User FOR LOGIN AG_Login

--#2の証明書を使って、作成したSQLログインユーザーに認証を付けて、証明書を作成する

CREATE CERTIFICATE SQL2_Cert

AUTHORIZATION AG_User

FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\SQL2_Cert.cer'

--エンドポイントに接続権限を設定する

GRANT CONNECT ON ENDPOINT::DIAG_EP TO AG_Login

--#SQL2#

--SQLログインを作成する

CREATE LOGIN AG_Login WITH PASSWORD = '<password>'

--SQLログインユーザーを作成する

CREATE USER AG_User FOR LOGIN AG_Login

--#2の証明書を使って、作成したSQLログインユーザーに認証を付けて、証明書を作成する

CREATE CERTIFICATE SQL1_Cert

AUTHORIZATION AG_User

FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\SQL1_Cert.cer'

--エンドポイントに接続権限を設定する

GRANT CONNECT ON ENDPOINT::DIAG_EP TO AG_Login

--#SQL1#

-- データベースの作成

CREATE DATABASE agTestDB

-- テーブルの作成

USE agTestDB

CREATE TABLE agTest1

( colA int PRIMARY KEY

,colB int

,colC varchar(20))

-- データを 2件追加

INSERT INTO agTest1 VALUES(1, 111, 'AAA')

INSERT INTO agTest1 VALUES(2, 222, 'BBB')

-- データの確認

SELECT * FROM agTest1

--完全バックアップ

BACKUP DATABASE [agTestDB] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\agTestDB.bak' WITH NOFORMAT, INIT, NAME = N'agTestDB-完全データベースバックアップ', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM

declare @backupSetId as int

select @backupSetId = position from msdb..backupset where database_name=N'agTestDB' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'agTestDB' )

if @backupSetId is null begin raiserror(N'確認に失敗しました。データベース ''agTestDB'' のバックアップ情報が見つかりません。', 16, 1) end

RESTORE VERIFYONLY FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\agTestDB.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

Tagged with: AlwaysOn AG • sql server 2017 • SQL Server 2019 • Windows Server 2019