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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s