보미아빠 2024. 2. 2. 11:10
--------------------------------------------
-- full & transaction log backup and norecovery mode
--------------------------------------------

-- principal server
backup database mirrortest to disk = 'd:\mssql\share\mirrortest.full'
backup log mirrortest to disk = 'd:\mssql\share\mirrortest.log'

-- mirror server
restore database mirrortest from disk = '\\ip\share\mirrortest.full' with norecovery 
restore log mirrortest from disk = '\\ip\share\mirrortest.log' with norecovery

--------------------------------------------
-- mirror mirroring endpoint add
--------------------------------------------

CREATE ENDPOINT [Mirroring] STATE=STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = DISABLED)

--------------------------------------------
-- principal mirroring endpoint add
--------------------------------------------
CREATE ENDPOINT [Mirroring] STATE=STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = DISABLED)

--------------------------------------------
-- mirroring patner setting (mirror server) partner ip
--------------------------------------------
alter database mirrortest set partner = 'tcp://ip:5022'

--------------------------------------------
-- mirroring patner setting (principal server) partner ip
--------------------------------------------
alter database mirrortest set partner = 'tcp://ip:5022'

--------------------------------------------
-- mirroring start (principal server)
--------------------------------------------
alter database mirrortest set safety full


--------------------------------------------
-- ** mirroring off
--------------------------------------------
alter database mirrortest set partner off

--------------------------------------------
-- ** mirroring failover
--------------------------------------------
alter database mirrortest set partner failover 

--------------------------------------------
-- ** mirroring suspend 
--------------------------------------------
alter database mirrortest set partner suspend 

--------------------------------------------
-- ** mirroring suspend 
--------------------------------------------
alter database mirrortest set partner resume 

--------------------------------------------
-- ** mirroring endpoint
--------------------------------------------
SELECT name, port FROM sys.tcp_endpoints;  

--------------------------------------------
-- ** mirroring status
--------------------------------------------

select 
	db.name, 
	db.state_desc, 
	dm.mirroring_role_desc, --**
	dm.mirroring_state_desc, --**
	dm.mirroring_safety_level_desc, --**
	dm.mirroring_partner_name, 
	dm.mirroring_partner_instance 
from sys.databases db
	inner join sys.database_mirroring dm
	on db.database_id = dm.database_id
where dm.mirroring_role_desc is not null
order by db.name


--------------------------------------------
-- ** go home~
--------------------------------------------
ALTER DATABASE [databaseName] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS