/**********************************************************************************************
-- Title : 인증서를 이용한 데이터베이스 미러링 구현
-- Reference : feelanet.com
-- Key word : 인증서 certificate mirroring 미러링
**********************************************************************************************/

/*
미러링 구성 시 도메인 계정을 사용하지 않는 경우 인증서를 사용한다.
*/

/*
-- 주서버에서(mapbakdba:5022)
*/
create database MirrorTest;
go
 
use MirrorTest;
go

create table t1(ids int primary key identity, strA varchar(10));
go

insert into t1 values('a');
insert into t1 values('b');
insert into t1 values('c');
go

-- backup
backup database MirrorTest to disk = 'd:\databases\MirrorTest.bak' with init;
go
backup log MirrorTest to disk = 'd:\databases\MirrorTest.log' with init;
go

--주서버에서 인증서로 미러링 endpoint 생성

--주서버에서 master key 생성
use master;
go
create master key encryption by password = 'principal12#$';
go

--인증서 생성
create certificate principal_cert 
   with subject = 'principal certificate'
  , start_date = '03/13/2008'
  , expiry_date = '03/13/2009';
go

--endpoint 생성
create endpoint endpoint_mirroring
   state = started
   as tcp (
      listener_port=5022
      , listener_ip = all
   ) 
   for database_mirroring ( 
      authentication = certificate principal_cert
      , role = all
   );
go

--인증서 백업 후 미러 서버와 witness 서버로 복사
backup certificate principal_cert to file = 'd:\principal_cert.cer';
go
 

/*
-- 미러서버에서(mapbakdba\s1:5023)
*/
--미러서버에서 인증서로 미러링 endpoint 생성

--미러서버에서 master key 생성
use master;
go
create master key encryption by password = 'mirror12#$';
go

--인증서 생성
create certificate mirror_cert 
   with subject = 'principal certificate'
  , start_date = '03/13/2008'
  , expiry_date = '03/13/2009';
go

--endpoint 생성
create endpoint endpoint_mirroring
   state = started
   as tcp (
      listener_port=5023
      , listener_ip = all
   ) 
   for database_mirroring ( 
      authentication = certificate mirror_cert
      , role = all
   );
go

--인증서 백업 후 주 서버와 witness 서버로 복사
backup certificate mirror_cert to file = 'd:\mirror_cert.cer';
go
 

/*
-- 감시서버에서(mapbakdba\s2:5024)
*/
--witness서버에서 인증서로 미러링 endpoint 생성
--witness서버에서 master key 생성
use master;
go
create master key encryption by password = 'witness12#$';
go

--인증서 생성
create certificate witness_cert 
   with subject = 'witness certificate'
  , start_date = '03/13/2008'
  , expiry_date = '03/13/2009';
go

--endpoint 생성
create endpoint endpoint_mirroring
   state = started
   as tcp (
      listener_port=5024
      , listener_ip = all
   ) 
   for database_mirroring ( 
      authentication = certificate witness_cert
      , role = witness
   );
go

--인증서 백업 후 주 서버와 미러 서버로 복사
backup certificate witness_cert to file = 'd:\witness_cert.cer';
go
 

/*
-- 주서버에서(mapbakdba:5022)
*/
--미러서버에 대한 계정 생성
use master;
go
create login mirror_login with password = 'mirrorlogin12#$';
go

--미러서버에대한 사용자 생성
create user mirror_user for login mirror_login;
go

--인증서와 사용자 연결
create certificate mirror_cert
   authorization mirror_user
   from file = 'd:\mirror_cert.cer'
go

--해당 원격 미러링 끝점에 대한 로그인에 connect 권한을 부여
grant connect on endpoint::endpoint_mirroring to [mirror_login];
go
--witness서버에 대한 계정 생성
use master;
go
create login witness_login with password = 'witnesslogin12#$';
go

--witness서버에대한 사용자 생성
create user witness_user for login witness_login;
go
--인증서와 사용자 연결
create certificate witness_cert
   authorization witness_user
   from file = 'd:\witness_cert.cer'
go

--해당 원격 미러링 끝점에 대한 로그인에 connect 권한을 부여
grant connect on endpoint::endpoint_mirroring to [witness_login];
go
 

/*
-- 미러서버에서(mapbakdba\s1:5023)
*/
--주서버에 대한 계정 생성
use master;
go
drop login principal_login with password = 'principallogin12#$';
go

--주서버에대한 사용자 생성
drop user principal_user for login principal_login;
go

--인증서와 사용자 연결
drop certificate principal_cert
   authorization principal_user
   from file = 'd:\principal_cert.cer'
go

--해당 원격 미러링 끝점에 대한 로그인에 connect 권한을 부여합니다.
grant connect on endpoint::endpoint_mirroring to [principal_login];
go

--witness서버에 대한 계정 생성
use master;
go
drop login witness_login with password = 'witnesslogin12#$';
go

--witness서버에대한 사용자 생성
drop user witness_user for login witness_login;
go

--인증서와 사용자 연결
drop certificate witness_cert
   authorization witness_user
   from file = 'd:\witness_cert.cer'
go

--해당 원격 미러링 끝점에 대한 로그인에 connect 권한을 부여
grant connect on endpoint::endpoint_mirroring to [witness_login];
go
 

/*
-- 감시서버에서(mapbakdba\s2:5024)
*/
--주서버에 대한 계정 생성
use master;
go
drop login principal_login with password = 'principallogin12#$';
go

--주서버에대한 사용자 생성
drop user principal_user for login principal_login;
go

--인증서와 사용자 연결
drop certificate principal_cert
   authorization principal_user
   from file = 'd:\principal_cert.cer'
go

--해당 원격 미러링 끝점에 대한 로그인에 connect 권한을 부여합니다.
grant connect on endpoint::endpoint_mirroring to [principal_login];
go

--미러서버에 대한 계정 생성
use master;
go
drop login mirror_login with password = 'mirrorlogin12#$';
go

--미러서버에대한 사용자 생성
drop user mirror_user for login mirror_login;
go

--인증서와 사용자 연결
drop certificate mirror_cert
   authorization mirror_user
   from file = 'd:\mirror_cert.cer'
go

--해당 원격 미러링 끝점에 대한 로그인에 connect 권한을 부여
grant connect on endpoint::endpoint_mirroring to [mirror_login];
go
 

/*
-- 미러서버에서(mapbakdba\s1:5023)
*/
-- restore
restore filelistonly from disk = 'd:\databases\mirrortest.bak';
go

restore database mirrortest from disk = 'd:\databases\mirrortest.bak' 
with move 'mirrortest' to 'd:\databases\mirrortest.mdf'
   , move 'mirrortest_log' to 'd:\databases\mirrortest_log.ldf'
   , norecovery;
go

restore log mirrortest from disk = 'd:\databases\mirrortest.log'
with norecovery;
go

--mirroring start at mirror
alter database mirrortest 
    set partner = 'tcp://mapbakdba:5022'
go
 

/*
-- 주서버에서(mapbakdba:5022)
*/
--mirroring start at principal
alter database mirrortest
    set partner = 'tcp://mapbakdba:5023'
go

--witness start at principal
alter database mirrortest 
    set witness = 'tcp://mapbakdba:5024'
go