SETTING UP DATABASE MIRRORING (No Witness Server)

SETTING UP DATABASE MIRRORING (No Witness Server)

--* Recovery model must be Full.
--* Set "Auto_Close" database property to False.
--* Configure MSSQLSERVER service accounts on Principal and Mirroring server. (Don't use NETWORKSERVICE...)
--* Firewall or Port(5022, 5023) is down to allow connection.
--* No need for SQL SERVER AGENT.

--1. Full backup the database
backup database testdb1
to disk = 'c:\data\testdb1.bak'
with init;

backup log testdb1
to disk = 'c:\data\testdb1_log.bak'
with init;


--2. restore backup on Mirroring server
restore database testdb1
from disk = 'c:\data\testdb1.bak'
with norecovery
,move 'testdb1_data' to 'c:\data\testdb1_data.mdf'
,move 'testdb1_log' to 'c:\data\testdb1_log.ldf'
go

restore log testdb1
from disk = 'c:\data\testdb1_log.bak'
with norecovery
go


--3. Start Mirroring on Principal Database
Database Properties \ Mirroring Page \ Configure Security ...
Choose Principal Server and Mirror Server, Endpoint names, Ports, Service Accounts. (Without Witness Server)

** Service Accounts for both servers should have enough permission.
** If Endpoint is not started by default, manually start it by SQL.


--4. When succeeded, you will see...
On principal database:  testdb1 (Principal, Synchronized)
On morror database:     testdb1 (Mirror, Synchronized / Restoring...)

FAILOVER

--Important SQL:
ALTER DATABASE <database_name> SET PARTNER FAILOVER   ----切换主备
ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS    ---强制启用镜像数据库


*. To manually fail over database mirroring   数据库镜像会话期间的角色切换
--------------------------------------------------------------------------
Principal & Mirroring - Role switching.  (When both are online.)  联机时的主备切换

A. <SSMS>
   Right-click the PRINCIPAL database: Tasks -> Mirror,
   Database Properties dialog box: Mirroring page -> [Failover]

B. <T-SQL>  (on Principal server)
ALTER DATABASE <database_name> SET PARTNER FAILOVER

----Takes a few seconds. Very quick.



*. Force Service in a Database Mirroring Session  在数据库镜像会话中强制服务
----------------------------------------------------------------------------
Take mirror database online after principal server is gone. (Potential data loss may happen.)
Using following SQL on mirroring server.
  ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
  
--To test it,
1) Stop the MSSQLSERVER service on Principal server. 
2) The Mirroring database statues: (Mirror, Disconnected / In Recovery). 
   Run T-SQL on Mirroring server:   
     ALTER DATABASE [testdb1] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
3) The Mirroring database statues: (In Recovery).
   Run T-SQL:   
     RESTORE DATABASE [testdb1] WITH RECOVERY
4) Use SSMS to remove the Mirroring. (in Database properties)   --Done.

PAUSE / RESUME Mirroring

-- Run either on Principal server or Mirror server

ALTER DATABASE <database_name> SET PARTNER SUSPEND    --暂停镜像服务
ALTER DATABASE <database_name> SET PARTNER RESUME     --恢复镜像服务




--== REFERENCE ==--

--(English, US)
http://msdn.microsoft.com/en-CA/library/ms189850(v=sql.110).aspx#ManualFailover

--(简体中文)
http://msdn.microsoft.com/zh-cn/library/ms189850(v=sql.110).aspx#ForcedService
http://jimshu.blog.51cto.com/3171847/586956