November 1, 2012 at 8:34 am
Ok, I know a lot of you have experience with this one and I hope you can help me out. I need to make a list of Pros and Cons.
The scenario:
Our application vendor wants to apply some changes (tested) to the production database, and import some data to it. So, they ask for a database backup which they restore to their server and do these changes/imports. Then they create a backup of the database and send it to us to restore it over our production database.
I have done this several times and I am NOT feeling this is the best process, I feel it is better to allow they to either a.) login to our network and apply the changes to production (after a good db backup) and or b.) send us the scripts and let us apply the changes to production (after a good db backup)
Here is my list thus far:
Pros for the restore:
- Vendor does not need to access our production server
- Changes are isolated to the database backup
- Ensure that Nobody has access to the database during the change/import process
Cons for the restore: *this is what I am leaning towards
- It breaks the log shipping and it requires us to set it back up each time
- It can unnecessarily interrupt Energov users when only certain modules/items will be unavailable
-It completely flushes the procedure cache for the database and causes all queries to be recompiled after a restore this negatively impacts performance
- A restore is not a logged transaction and therefore cannot be rolled back, whereas executing from a script within a begin/commit tran can be rolled back if there is a problem.
- If the backup is created on a server at the vendor that is a newer version than ours we cannot guarantee a restore will be successful as often backups are not backward compatible (cannot restore a 2012 database back up to a 2008 server for example).
Your feedback/input is invaluable so thank you in advance!
Sandy (hawkeyedba)
Just a midwest DBA doing the best I can
November 1, 2012 at 8:39 am
Is the data in the database static or does it change? Also what is the turn around time from you providing the backup for the 3rd party to do the nessesary work?
November 1, 2012 at 8:43 am
Data is the first thing that comes to mind.
If you save any data of any kind in that database, it would of course be unique to you and your processes.
restoring a vendor database means you lose all the changes that were stored in the database.
Is the database is some sort of lookup database, that could be marked as read only? I cannot image any application that connects to a database and doesn't actually update some portion of it.
Lowell
November 1, 2012 at 8:46 am
Also, how big is the database? That will impact the amount of time that the system is offline while the restore runs.
I am a huge fan of scripting EVERYTHING that gets deployed to production. That includes any and all objects, scheduled jobs, logins and security as well as the data. It provides for a repeatable process that can be applied from development, to test, to staging, to production. When the deployment window arrives, it is a no stress moment for the DBA team as they simply execute the exact same scripts that have been applied to other environments.
November 1, 2012 at 8:54 am
That is a great question!
The data isn't static so the database is unavailable during the process which in the latest case will amount to a few hours of one business day and 1 1/2 week days.
We actually have the database unavailable during the entire process since the application logs data to it continuously. We DO have a standby server used for read-only operations like reporting that remains available during the process.
Right now the db is small, in the future it is expected to grow to 20GB or larger.
November 1, 2012 at 9:06 am
Thats a pretty big downtime window then for something which seems very trivial to do in scripts instead.
I had dealings with a 3rd party who was like this, we then went in with it as a monteary terms, saying 1 hour of downtime costs the company £X pounds, then we said to them if you want to do backup restore, then you pay us £X pounds per hour, they soon went and did things the script way as it took them days to create, test and get back to us with the changes.
Another one is that you can see exactly what is happening to your database and server by looking through the script, where as with a restore, how do you know what they have changed
November 1, 2012 at 9:26 am
That's a good point. I am not sure if it costs us in productivity or not, I will definately bring that up though!
Thanks!
November 1, 2012 at 9:45 am
This is a little silly, IMHO.
Send them a backup, let them make changes, and then use diff tools, like SQL Compare/Data Compare from Red Gate (Disclosure, I work for Red Gate), and send you scripts of changes.
There's no reason they can't script out their changes. That way you can check them and be sure they haven't done something weird.
As it is now, you might not even know if they accidentally deleted old data, changed other things, and sent it back.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 1, 2012 at 9:55 am
Steve, that is another very excellent point!
I really appreciate everyone's feedback - and you're right, it is silly, and now I feel better about making my case 😀
Its times like this you actually think you may be figuring out how to be a good DBA...
Thanks again,
Sandy
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply