SOURCE

All the code below is freely available on the internet. I first saw it in the SQL2000 SDK from where it has been improved many-many-times-over. Rather than rehash the implementation here is my handover notes.

OVERVIEW OF THIS PROJECT

Transaction-logs (Tlogs) from the London database ‘Actions’ are backed up every hour to a warm standby in Paris and restored there.

DETAILS

Hourly a SQL Job in London (‘Log ship Incidents to Paris’) executes a stored procedure (‘sp_ShipLogOnce’) that backs up the Tlog directly to Paris (to E:Backups shared as ‘Backup$’) then runs the Paris SQL Job (‘sp_ApplyStandByLog’) that restores it.

MAINTENANCE & LIMITATIONS

There is a Windows batch file on Paris (‘PurgeFilesExceptRecent.bat’) that will delete old Tlogs every 2am. It has been initially set to ignore the newest 720 (being one per hour for 30 days).

Logins on Paris need to be regularly updated from London (at least monthly). The stored procedure ‘sp_help_revlogins’ will script out the London logins, from which any new, sql, non-svc, entries should be added to Paris.

The Tlogs need to be restored in strict order. After a temporary network failure all outstanding Tlogs can be restored by running this command at Paris …

C:ApplyStandbyLogs.bat E:Data S:Backups

Alternatively individual Tlog can be restored using the stored procedure …

exec Sp_ApplyStandByLog
@DBName=’Actions’,
@BackupFileName=’S:BackupsActions[name.trn]’,
@UndoFile=’T:DATAActions_undo.ldf’,
@MoveFileTo=’S:BackupsActionsRestored’

SWITCHING ROLES

  1. Disable the London LogShip job
  2. Apply final Tlog (sp_ShipLogOnce with @LocalUndoFile)
  3. Change Paris ‘Actions’ database to read/write, multi-user
  4. Enable the Paris LogShip job

DISASTER RECOVERY

If London is unavailable, only step-3 above is needed. IE:-

exec sp_dboption 'actions', 'dbo use only', 'false'
go
exec sp_dboption 'actions', 'read only', 'false'
go
restore database actions with recovery
go
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