PREREQUISITES
- First I changed the recovery model of the BESMgmt database to use the Full Recovery Model.
- To manage the BESMgmt Tlog files generated from this I created a Maintenance Plan to backup the Tlogs every 6 hours to the E:Backups folder. These will be automatically deleted after two weeks (NOTE: The size of the TLogs can be reduced by increasing the backup frequency).
- I reinstalled SQL Server 2008+SP1 on Paris ensuring that …
- The SQL service account used was SVC_SQL
- The SQL agent account used was SVC_AGT_SQL
- The collation was set to LATIN1_GENERAL_CI_AS
- The default location for datafiles was set to S:DATA
- The default location for logfiles was set to T:LOGS
- TempDB was located on the U-Drive
– Minimised potential connectivity delays by …
o Setting up Paris as a linked-server on London
o Ensuring the SQL Browser Services was active and working on both servers
SETUP
To initiate mirroring I …
– Backed up the BESMgmt database and restored it on Paris with no recovery
– Backed up the TLOG and restored it on Paris with no recovery
– Configured Mirroring of BESMgmt on London GUI without a witness.
OPERATION
There are three methods of failing over / swapping the Mirror-Live (Principal) and Mirror-Standby (Partner) roles.
1. There is a ‘Failover button’ on the Principal SSMS GUI mirror-configuration screen. This should be used for testing, and for planned maintenance of the Mirror-Live server.
2. I believe ‘BES Manager’ can be configured to automatically failover the database. This should be the normal way failover occurs.
3. A sql-command can be executed on the Mirror-Partner as a last-ditch option should the Mirror-Live server become unavailable ( IE: alter database besmgmt set partner force_service_allow_data_loss). Data loss may occur.
MONITORING
The Mirror Monitor (screen within SSMS) can be viewed from either server by right-clicking the BESMgmt database and choosing Tasks / Launch database Mirroring Monitor. I have initiated mirror warnings within this tool which will log an error to the application log if Mirroring becomes slow.
Database Consultant 