SQL Mirroring for eLearning (Blackboard Learning System CE8)

We finally have a set of servers in another building designated for business continuity of our eLearning Learning Management System (powered by Blackboard Learning System CE8). We have a procedure to failover to that location in the event of a disaster. We even ran a contingency drill back in May to verify the process.

One of the outstanding problems we have is : eLearning's database is so hugh (and is getting larger every day) that in the event of a disaster, it takes a very long time to restore it from backup. In order to minimize the potential down time, we decided to use SQL database mirroring to mirror eLearning's database from our current production to the business continuity server.

Here is how:

• Backup the eLearning's databases on the principal (production) server and restore them to the mirror (business continuity) server using the NORECOVERY option to ensure the log files on both servers match exactly.

restore database webctdatabase from DISK='D:\folder\webctdatabase.bak' WITH NORECOVERY, REPLACE

restore database webctdatabase$PF from DISK='D:\folder\webctdatabase$PF.bak' WITH NORECOVERY, REPLACE

• On both the principal (production) and mirror (business continuity) servers, create database mirroring partner endpoint. (TCP port 5022 is used in this example since that the the default port number number. But that can be changed to any TCP port number available).

CREATE ENDPOINT mirroring_end_point
STATE=STARTED

AS TCP(LISTENER_PORT=5022)

FOR DATABASE_MIRRORING (ROLE=PARTNER)

• On the mirror server, specify the principal server as the mirror partner.

ALTER DATABASE webctdatabase SET PARTNER = 'TCP://BusContServerName:5022'

ALTER DATABASE webctdatabase$PF SET PARTNER = 'TCP://BusContServerName:5022'

• On the principal server, specify the mirror server as the mirror partner.

ALTER DATABASE webctdatabase SET PARTNER = 'TCP://ProdServerName:5022'

ALTER DATABASE webctdatabase$PF SET PARTNER = 'TCP://ProdServerName:5022'

• Now the principal server will start sending transactions to the mirror server.
• Initially, the principal server and the mirror server will be in a synchronizing state. After all transactions from the principal server have been reapplied on the mirror server, their states will change to synchronized.

By default, a mirror session operates in synchronous mode. But you may prefer the session to be asychronous instead due to performance reason (e.g. high latency between the principal and mirror server). You can execute the following command on either the principal or the mirror server to change the mirror session to asychronous mode.

ALTER DATABASE webctdatabase SET PARTNER SAFETY OFF

ALTER DATABASE webctdatabase$PF SET PARTNER SAFETY OFF

Also worth noting that one can set up a SQL server as a witness server for automatic failover. But the application has to support it. In our case, Blackboard CE8 does not. Therefore, we do not pursue this path. We have a procedure, and a script to perform manual failover instead.