February 24, 2011 at 12:02 pm
I have a product configuration web app talking to a 2008 R2 SQL Server in the backend. When new products are updated, it goes into a staging environment, where it is user acceptance tested with an identical web-UI as the production version. Basically we have two identical, mirrored environments - Staging and PROD.
What I would like to do, is to create a script, which will take the Staging DB file, and replace the current one on Production, without having to take the PROD system down. The DB file is about 400MB and they are on different machines - although both machines are hosted by the same provider. Let me state the requirements again:
- maintain continuous up time on the PROD application
- utilize a simple mechanism to transfer all the data from Staging to PROD
- upstream of Staging, the upload process basically drops or deletes ALL the product data, and reloads them, regardless of whether a specific row item has changed or not (All or Nothing). I can't change this process.
- The application is non-transactional, its basically a product lookup/configurator.
Thank you,
Edward
February 24, 2011 at 12:08 pm
i would copy the stage database to production
called new_db then ren db to old_db
ren new_db to db
smallest downtime i would think
February 24, 2011 at 12:15 pm
This needs to be done about every 3 months. So I will write a shell script that does:
1) FTP the DB file from Staging to PROD
2) Rename and archive the existing PROD DB file.
3) Rename the new DB to the one that PROD is always referring to
Do I need to stop the DB instance on PROD while the renaming/swapping is taking place? Ideally there is no down time - performance slow down would be fine during the operation.
Much appreciated.
February 24, 2011 at 12:20 pm
There are a variety of ways to pull this off.
Since it sounds like you are dealing with a master set of data which gets approved via the UAT process, you could use red gate' SQL Data Compare. It'll diff the whole db and make a change script to sync the data. Simple to use. 400MB should not be a problem if you do this irregularly. Oh, this will cause zero down time - except for perhaps some locking/blocks if a significant amount of data is modified on a single table.
It will impact disk contention since a full read is done on both databases. This means it will also cause some network contention as well, unless the Stage database is on the same box. Shouldn't be a huge deal for a db so small though, and if done here and there.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
February 24, 2011 at 12:24 pm
edwardw 90939 (2/24/2011)
Do I need to stop the DB instance on PROD while the renaming/swapping is taking place? Ideally there is no down time - performance slow down would be fine during the operation.
No, you can leave it running. AND... if you wrap the rename in a transaction any users will be blocked while the swap occurs and connections won't be dropped. Testing, as always, should be done.
One note on this, you might want to flush the cache as I have seen funny things happen where data from cache was being pulled from now obsolete and unavailable data.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
July 18, 2012 at 2:58 am
SQL SERVER does not allow the following in a transaction so how would this work?
ALTER DATABASE oldName MODIFY NAME = newName
We have a messy system to replace a database at the moment involving killing transactions and switching to and from single user mode, it sometimes fails so we use a backup table and when it fails some manually intervention...it is not ideal at all but is currently our only option as we have a very legacy application that direct uses the 'old style' database.
July 18, 2012 at 6:58 am
Sorry about that.
So, you would have to use the alter database set single_user with rollback immediate, make the name change and then change it back to multiple user.
I would be curious why it fails. That should work every time. Agreed it is not pretty but there is no way to go about doing a rename with users in the database.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
July 19, 2012 at 12:36 am
We keep our websites running while we switch out the database so there is always the 'possibility' that a new connection will be made immediately AFTER we kill user connections...however I have spotted a problem when writing the process out without our specific database names...
1. Stop SQLSERVERAGENT
2.1 Kill user connections to 'backup'
2.2 Stick 'backup' database in single user mode.
2.3 Delete our 'backup' database.
...Run a x number of retries loop trying to:
3.1 Stick 'current' database in single user mode...EXECUTE ('ALTER DATABASE ' + @oldDBName + ' SET single_user WITH ROLLBACK IMMEDIATE');
3.2 Rename 'current' database to 'backup'
3.3 Set the 'now backup' database into multi user (this may seem a little pointless but it meant we can just manually rename to restore to the backup if something fails)
...Run a x number of retries loop trying to:
4.1 Stick 'new' database in single user mode. (Perhaps unrequired as there should NOT be anyone connected)
4.2 Rename 'new' database to 'current'
4.3 Set the 'now current' database into multi user (...perhaps unrequired if don't do 4.1)
5. Does similar stuff as above for another database.
6. Starts SQLSERVERAGENT
...the problem I have spotted is that we are not killing connections to 'current' which is of course the most important one to kill connections to. HOWEVER...as you see above we ARE using WITH ROLLBACK IMMEDIATE so should this even matter???
July 19, 2012 at 7:06 am
The rollback immediate will take care of killing those connections. There is a note in the documentation about ensuring that AUTO_UPDATE_STATISTICS_ASYNC is off as that can take priority and getting a connection could be troublesome.
http://msdn.microsoft.com/en-us/library/ms345598.aspx
Might be worth reviewing.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
July 19, 2012 at 7:32 pm
Thanks, the ASYNC option is False though.
We can handle the occasional issue, but it is perplexing.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply