SQL Server 2016 가용성 그룹 만들기 T-SQL 편

간단하다. 몇몇 옵션들을 어떻게 추가하는지에 따라 틀릴 뿐 간단하다.

   

   

-- For additional Transact-SQL code examples of creating an availability group

   

CREATE DATABASE AGDefaultTSQL01 on

(Name=AGDefaultTSQL01, filename='S:\MSSQL\Data\MDF=AGDefaultTSQL01.mdf')

LOG on

(Name=AGDefaultTSQL01_log, filename='S:\MSSQL\Data\LDF\AGDefaultTSQL01_log.ldf');

   

CREATE DATABASE AGDefaultTSQL02 on

(Name=AGDefaultTSQL02, filename='S:\MSSQL\Data\MDF=AGDefaultTSQL02.mdf')

LOG on

(Name=AGDefaultTSQL02_log, filename='S:\MSSQL\Data\LDF\AGDefaultTSQL02_log.ldf');

   

   

   

-- Backup sample databases:

BACKUP DATABASE AGDefaultTSQL01

TO DISK = N'\\AG2016-S1\AGBackupShareFolder\AGDefaultTSQL01.bak'

WITH FORMAT

GO

 

BACKUP DATABASE AGDefaultTSQL02

TO DISK = N'\\AG2016-S1\AGBackupShareFolder\AGDefaultTSQL02.bak'

WITH FORMAT

GO

   

   

-- Create endpoint on server instance that hosts the primary replica:

CREATE ENDPOINT Hadr_endpoint

STATE=STARTED

AS TCP (LISTENER_PORT=5022)

FOR DATABASE_MIRRORING (ROLE=ALL)

GO

   

-- Create endpoint on server instance that hosts the secondary replica:

CREATE ENDPOINT Hadr_endpoint

STATE=STARTED

AS TCP (LISTENER_PORT=5022)

FOR DATABASE_MIRRORING (ROLE=ALL)

GO

   

   

-- If necessary, create a login for the service account, domain_name\user_name

-- of the server instance that will host the other replica:

USE master;

GO

CREATE LOGIN [domain_name\user_name] FROM WINDOWS;

GO

-- And Grant this login connect permissions on the endpoint:

GRANT CONNECT ON ENDPOINT::Hadr_endpoint

TO [domain_name\user_name];

GO

   

   

   

-- Create the availability group, MyAG:

CREATE AVAILABILITY GROUP [AG-GroupNameTSQL]

WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY,

DB_FAILOVER = ON,

DTC_SUPPORT = NONE)

FOR DATABASE [AGDefaultTSQL01], [AGDefaultTSQL02]

REPLICA ON

N'AG2016-S1\AG1' WITH (

ENDPOINT_URL = N'TCP://AG2016-S1.overtop.local:5022',

FAILOVER_MODE = AUTOMATIC,

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

BACKUP_PRIORITY = 50,

SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)

),

N'AG2016-S4\AG4' WITH (

ENDPOINT_URL = N'TCP://AG2016-S4.overtop.local:5022',

FAILOVER_MODE = MANUAL,

AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,

BACKUP_PRIORITY = 50,

SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)

);

   

   

-- On the server instance that hosts the secondary replica,

-- join the secondary replica to the availability group:

ALTER AVAILABILITY GROUP [AG-GroupNameTSQL] JOIN;

GO

   

-- On the server instance that hosts the secondary replica,

-- Restore database backups using the WITH NORECOVERY option:

RESTORE DATABASE [AGDefaultTSQL01]

FROM DISK = N'\\AG2016-S1\AGBackupShareFolder\AGDefaultTSQL01.bak'

WITH NORECOVERY

GO

   

RESTORE DATABASE [AGDefaultTSQL02]

FROM DISK = N'\\AG2016-S1\AGBackupShareFolder\AGDefaultTSQL02.bak'

WITH NORECOVERY

GO

   

-- Back up the transaction log on each primary database:

BACKUP LOG [AGDefaultTSQL01]

TO DISK = N'\\AG2016-S1\AGBackupShareFolder\AGDefaultTSQL01.trn'

WITH NOFORMAT

GO

 

BACKUP LOG [AGDefaultTSQL02]

TO DISK = N'\\AG2016-S1\AGBackupShareFolder\AGDefaultTSQL02.trn'

WITH NOFORMAT

GO

 

-- Restore the transaction log on each secondary database,

-- using the WITH NORECOVERY option:

RESTORE LOG [AGDefaultTSQL01]

FROM DISK = N'\\AG2016-S1\AGBackupShareFolder\AGDefaultTSQL01.trn'

WITH FILE=1, NORECOVERY

GO

RESTORE LOG [AGDefaultTSQL02]

FROM DISK = N'\\AG2016-S1\AGBackupShareFolder\AGDefaultTSQL02.trn'

WITH FILE=1, NORECOVERY

GO

   

-- On the server instance that hosts the secondary replica,

-- join each secondary database to the availability group:

ALTER DATABASE [AGDefaultTSQL01] SET HADR AVAILABILITY GROUP = [AG-GroupNameTSQL];

GO

ALTER DATABASE [AGDefaultTSQL02] SET HADR AVAILABILITY GROUP = [AG-GroupNameTSQL];

GO

   

   

   

   

[참고문서]

Create an Availability Group (Transact-SQL)

https://msdn.microsoft.com/en-us/library/ff878307.aspx