SQL Mirroring Manual Failover for eLearning (Blackboard Learning System CE8)

In my previous SQL Mirroring for eLearning post, I listed the detail on how to setup SQL database mirroring for Blackboard Learning System CE8. Since the application (Weblogic) does not support automatic failover, we need to have a procedure and scripts ready to perform manual failover in the event of a diaster (when the production database server is no longer available).

Here is how:

• Stop WebCT service on all frontend nodes.
• If you use asynchronous mirroring, you can execute the following commands on the mirror (business continutiy) server to force failover. The databases will then be in "In Recovery" status. It will take a minute for it to change to "Principal, Disconnected" status. The Disconnected status means that there is no mirrored session going on for this new principal server which is correct.

ALTER DATABASE webctdatabase SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

ALTER DATABASE webctdatabase$PF SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

• If you use synchronous mirroring instead, execute the following commands to failover:

ALTER DATABASE webctdatabase SET PARTNER FAILOVER

ALTER DATABASE webctdatabase$PF SET PARTNER FAILOVER

• After failover, you need to execute the following in the new principal server to set the webct and webct$PF users' SID.

Use webctdatabase

go

sp_change_users_login 'report'

Use webctdatabase

go

sp_change_users_login 'update_one', 'webct', 'webct'

Use webctdatabase

go

sp_change_users_login 'update_one', 'webct$PF', 'webct$PF'

Use webctdatabase$PF

go

sp_change_users_login 'report'

Use webctdatabase$PF

go

sp_change_users_login 'update_one', 'webct', 'webct'

Use webctdatabase$PF

go

sp_change_users_login 'update_one', 'webct$PF', 'webct$PF'

• Then you will need to change the jdbc connection string on the admin node and all managed nodes to point the database to the new principal server.

cd \bea92\weblogic92\config\WebCTDomain\config\jdbc\

copy /y CP-WebCTConnectionPool$PF-jdbc.xml CP-WebCTConnectionPool$PF-jdbc.before_db_migration.xml

copy /y CP-WebCTConnectionPool-jdbc.xml CP-WebCTConnectionPool-jdbc.before_db_migraton.xml

copy /y CP-WebCTJMSConnectionPool-jdbc.xml CP-WebCTJMSConnectionPool-jdbc.before_db_migration.xml

cat CP-WebCTConnectionPool$PF-jdbc.before_db_migration.xml | sed 's/ProdServer/BusContServer/' > CP-WebCTConnectionPool$PF-jdbc.xml

cat CP-WebCTConnectionPool-jdbc.before_db_migraton.xml | sed 's/ProdServer/BusContServer/' > CP-WebCTConnectionPool-jdbc.xml

cat CP-WebCTJMSConnectionPool-jdbc.before_db_migration.xml | sed 's/ProdServer/BusContServer/' > CP-WebCTJMSConnectionPool-jdbc.xml

diff CP-WebCTConnectionPool$PF-jdbc.before_db_migration.xml CP-WebCTConnectionPool$PF-jdbc.xml

diff CP-WebCTConnectionPool-jdbc.before_db_migraton.xml CP-WebCTConnectionPool-jdbc.xml

diff CP-WebCTJMSConnectionPool-jdbc.before_db_migration.xml CP-WebCTJMSConnectionPool-jdbc.xml

• Bring up the Blackboard CE8 cluster.