2

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

Improve this question
1

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.

Improve this answer
  • The database is in standby read-only mode so while Kendra's post is awesome we're already, as far as I can tell, doing everything in that post. – christutty Mar 18 '16 at 3:22
  • And Actifio is still not able to read the database? – Aaron Mar 18 '16 at 3:54
  • @christutty I re-read you post. Have you tried restoring the next log backup after you have put the database in readonly standby to see what would happen? – Aaron Mar 18 '16 at 4:05
  • The log backups work in standby read-only. The problem is that in that mode VSS won't back it up (TechNet talks about full backups not being possible and vss copy being the answer but, of course, I only have control over the SQL Server database). – christutty Mar 18 '16 at 6:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.