writable reporting db copy from log-shipped db
-
I have a server that has a high uptime requirement for when it is in use (actually in use for one day at a time and then brought down, but it cannot be offline when it is running, so actually 100% uptime requirement during the "runs"). We do log-shipping for DR purposes, but during our "run" of this database system one day at a time (in very high input rates), we are also in need of a copy of the database in which we can take the ongoing data and play with it, do what-if analyses, etc., and just doing read-only reporting off of the secondary log-shipped copy of the database just can't cut it, as we need a writable database to do some of our analyses.
Replication of the main database is not a good choice for us, as the schema for the database changes rather frequently, and I know it is tough to change schema during replication publication. Mirroring is not the choice, as the mirrored database is not in readable mode. So I am not sure what is the best way to solve this problem. Latency of the "play" database is not necessarily an issue, it can be many minutes old, so is this a possiblity: Back up the secondary log-shipped database, and just restore it over the "play" database periodically? Or am I missing a basic SQL Server technology answer to solve this problem?
Thanks in advance for assistance,
Brian Stephenson
ThorTech Solutions
-
You need to change your expectations here. Yeah, it would be nice to be able to have it all, but you're going to have to make a sacrifice somewhere along the way.
Repl is prob your best bet, and you'll just have to live with freezing your schema. If you can't do that, then you'll have to do a backup and restore every so often. Log shipping isn't going to work in this case either as you have to kill all the users in the standby to apply the logs, and you can't backup a DB that's in recovery.
And you've already said it has to be writable, so mirroring is out... and I agree.
So your only real choices are repl and backup/restore. And it depends on how fresh your copy of the data needs to be. If it's not too big, you can schedule a backup/restore every hour or so. You'll have to kick people out in order to restore though.
But yeah, you're asking for something you just can't reasonably do.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
If you can live with up to 24h of latency in your copied database and your daily differential backups of your primary server are small maybe you could consider a backup\restore scenario like full backups on the weekend and daily differentials on weekdays. The idea here being that daily differentials are relatively small, won't take all night to transfer from your primary to your copy, and allow you to resynchronize your schema and data every day (you'll lose whatever was done on the copy for that day)...but after a week the differentials start approaching the size of a full backup so you apply the full backup to get your differentials down to a manageable size again (assuming you have more time on the weekend because the system isn't in use like it is on weekdays).
Kendal Van Dyke
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply