Sql-server – bring a warm standby SQL Server database online and then return to standby mode

sql serversql-server-2008standby

What I'm trying to do falls outside of the normal SQL Server work flow so might be impossible.

I'm implementing a system that serves out copies of a large production database to multiple test / dev / training systems. We're using a differencing-based file system (the Actifio implementation of ZFS) that allows these copies to be created quickly with low disk space overhead. That system would normally be connected to a production database but that's making people nervous organisationally so I've been asked to operate from a warm standby database.

My problem is that Actifio can't see the standby database (it seems to use vsswriter to iterate databases and the standby database doesn't appear in the list that vsswriter iterates). The database is in standby read-only mode.

I can bring the warm standby database online so that Actifio can take an image but I then need to put it back to standby mode to continue applying logs and I can't find a reference that describes how to do this (or confirms that it's impossible)

Deleting the database and creating a new warm standby copy of production isn't an option as that disrupts the differencing engine's view of storage changes forcing a new full copy to be taken. To put it another way, this will work but destroys the value of the ZFS-based disk space change tracking that allows us to hand out copies of this database with very low disk overhead.

Can I bring a warm standby database online temporarily and then return to standby mode and continue applying logs? The database version is SQL Server 2008 R2

Best Answer

From what you are describing about what Actifio needs you should be able to put the secondary database in readonly mode after you restore the latest log file. See Kendra Little's post on log shipping secondaries in read only mode, she goes over some things that you need to account for. One of the things she mentions you'll need to account for putting the database in standby readonly is in order to continue restoring the next transaction log file you need to kick all of the users out of the database. That means you need to setup your restore scripts and the Actifio tool to work around each other.

Related Question