October 12, 2015 at 4:30 am
Dear All,
I have a process that restores a production DB, overwriting the existing copy each night. I'd like to keep the solution "up" for as long as possible. And this'll be more important if I want to update it in the day (where there are more queries) too. The nature of queries thrown at the system is that there are about 20 per hour, it's underpinning a reporting system, it's not an OLTP system.
It seems to me I could restore the fresh DB copy into a holding DB, then rename it to the production DB name at the end of the process. The rename process should be pretty much instant.
But I need to think about detecting and waiting for queries to complete on the prod DB, before removing/demoting it (actually, I though to rename it, then reusing it as the next copy to update).
Does anyone have any advice on how to go about this, and potential pitfalls/considerations?
Regards, Greg.
October 12, 2015 at 6:00 am
Is the database readonly for query users? If so could you keep the database in NORECOVERY and restore LOG files to it? (i.e. some sort of Log Shipping)
Can you change the APP's config to point at a given named database? Thus you could restore to MyDB2 today, cause all APPs to now point at MyDB2 (once they finish the current query), allowing yesterday's MyDB1 to be used until everyone connected has finished their current query.
Perhaps there is a way to use a SNAPSHOT so connected users query that, whilst you do the RESTORE. I'm guessing though as I know nothing about Snapshots.
October 12, 2015 at 6:06 am
if they need access up to the last second, i'd do this, but it takes twice as much disk space:
restore the production copy to the ReportServer as a different name. ie [ProductionCopyTemp]
once the restore is complete,
make the decision: allow connectiosn to complete, then
ALTER DATABASE [ProductionCopy] SET OFFLINE
if i want to kill any running reports, then
ALTER DATABASE [ProductionCopy] SET OFFLINE with rollback immediate
then drop the [ProductionCopy] database.
and finally rename [ProductionCopyTemp] to [ProductionCopy]
i'd think that the availability downtime time there would be counted in seconds, with the option of allowing running queries to complete.
Lowell
October 12, 2015 at 6:47 am
Thanks all - but I don't want to kill any running queries, I'd rather avoid them. I think this'd be doable bearing in mind the periodic server activity. What's the best way to see if there is a query running (apart from the query I'd be running to check if there's a query running -- aaargh!). I could then put a few retries around this.
October 12, 2015 at 7:34 am
Have you looked into the use of synonyms?
As long as you have space for 2 copies of the database on the server, I believe you could point synonym to the current database. After restoring the database, you point the synonym to the newly restored database. If this works for you, you would have virtually no downtime.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 12, 2015 at 9:05 am
Maybe this'd do it. Thanks for your help one and all!
declare @C int
WHILE (1=1)
BEGIN
SET @C = (select count(*) FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
where req.session_id != @@SPID )
IF (@c = 0)
BEGIN
Print 'Do the rename'
BREAK
END
Print 'Queries in progress'
WAITFOR DELAY '00:00:30'
END
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply