/**********************************************************************************************
-- Title : T-SQLÀ» ÀÌ¿ëÇÑ µ¥ÀÌÅͺ£À̽º ¹Ì·¯¸µ ±¸Çö(À©µµ¿ì°èÁ¤)
-- Reference : feelanet.com
-- Key word : endpoint listener role database_mirroring ¹Ì·¯¸µ
**********************************************************************************************/

/*
À©µµ¿ì °èÁ¤À» »ç¿ëÇÑ mirroring ±¸¼º
ÇÑ ¼­¹ö¿¡¼­ ÀνºÅϽº 3°³·Î ±¸¼º
Æ÷Æ®¸¸ Ʋ¸®°Ô ÇØÁÖ¸é µÊ
°èÁ¤ÀÌ µ¿ÀÏÇØ¾ß ÇÔ(administrator, ¼­ºñ½º ½ÃÀÛ °èÁ¤)*/

/*
-- ÁÖ¼­¹ö¿¡¼­(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

create endpoint Endpoint_Mirroring
    state = started
    as tcp (listener_port=5022)
    for database_mirroring (role = all);
go

-- È®ÀÎ
select e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc, t.port, e.is_encryption_enabled
     , e.encryption_algorithm_desc, e.connection_auth_desc 
from sys.database_mirroring_endpoints e 
       inner join sys.tcp_endpoints t
         on e.endpoint_id = t.endpoint_id;
go
 

/*
-- ¹Ì·¯¼­¹ö¿¡¼­(mapbakdba\s1:5023)
*/
create endpoint endpoint_mirroring
    state=started 
    as tcp (listener_port=5023) 
    for database_mirroring (role=all);
go

-- È®ÀÎ
select e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc, t.port, e.is_encryption_enabled
     , e.encryption_algorithm_desc, e.connection_auth_desc 
from sys.database_mirroring_endpoints e 
       inner join sys.tcp_endpoints t
         on e.endpoint_id = t.endpoint_id;
go
 

/*
-- °¨½Ã¼­¹ö¿¡¼­(mapbakdba\s2:5024)
*/
create endpoint endpoint_mirroring
    state=started 
    as tcp (listener_port=5024) 
    for database_mirroring (role=witness)
go

-- È®ÀÎ
select e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc, t.port, e.is_encryption_enabled
     , e.encryption_algorithm_desc, e.connection_auth_desc 
from sys.database_mirroring_endpoints e 
       inner join sys.tcp_endpoints t
         on e.endpoint_id = t.endpoint_id;
go
 

/*
-- ÁÖ¼­¹ö¿¡¼­(mapbakdba:5022)
*/
-- 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
 

/*
-- ¹Ì·¯¼­¹ö¿¡¼­(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 'e:\databases\mirrortest.mdf'
   , move 'mirrortest_log' to 'e:\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 off;
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