February 24, 2009 at 8:47 am
I assume that once a website is launched on a remote host, the only real way to update the database (add fields, tables, etc.) is to do it directly on the live version via SQL statements right?
You cannot take the databse down and play with it locally and re-upload it because any number of web users may have edited the database in the meantime.
With this in mind, what use is backing up the database? If the database gets corrupted in some way and you restore from the back-up you will lose any recent changes made by uers, this may involve losing whole recently created users accounts.
Anybody have any tips and tricks to deal with these kind of issues?
February 24, 2009 at 8:54 am
Basically, you need three copies of the database.
One is the production version. That's the one that the web page connects to.
Another is the dev version. That's where you build changes and where you can do things like truncate tables, etc., without worrying about messing anyone up.
The last is a test/QA version. This should only have changes made to it that you're getting ready to take live. You connect a copy of the web page(s) to it and you test the heck out of the changes. Preferably, someone other than the developer tests it here, so that anything the dev didn't think of will come up.
The way I handle moving changes from dev to test, and then from test to live, is to build a single script that makes all the necessary changes. The header for the script includes the necessary database backup commands. The script should include error-handling, and things like making sure an object doesn't exist before issuing a create command.
Test the script in test on a current copy of the production database. Take the most recent full backup of the production database, run the script. You should end up with EXACTLY what you want the production database to end up as. If you don't, you restore test from the backup, fix the script and test again.
Once you have a "perfect run" on the script in QA, and it has passed all tests there, then and only then do you run it on the production database.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 24, 2009 at 9:01 am
garethmann101 (2/24/2009)
I assume that once a website is launched on a remote host, the only real way to update the database (add fields, tables, etc.) is to do it directly on the live version via SQL statements right?You cannot take the databse down and play with it locally and re-upload it because any number of web users may have edited the database in the meantime.
Quick answer:
Once the live system has been set up any updates to the data through SSMS or changes to the data structure will need to be heavily tested on a test system before going to live.
After testing a final round of pre-live testing should be done on the most up to date copy of the live system available. Only after all of that should the actions/scripts (better to script everything) be ran on the live system.
There are some well known steps of taking changes from development, through test to staging and then to go live. Its a subject that deserves some futher reading if you want to avoid any embarressing mistakes.
garethmann101 (2/24/2009)
With this in mind, what use is backing up the database? If the database gets corrupted in some way and you restore from the back-up you will lose any recent changes made by uers, this may involve losing whole recently created users accounts.Anybody have any tips and tricks to deal with these kind of issues?
What use is backing up the database? Well if backups are done regularly but not frequently, say once a day and the database becomes corrupt or similar then you have lost a days worth of data. If you hadn't done any backups then you'll have lost ALL your data.
Differential backups can be run very frequently, every 5 minutes wouldn't be out of the question.
Have a read up on backup and recovery methodologies
February 24, 2009 at 9:18 am
We use a method similar to GSquared except we have a fourth machine, staging. So we testin QA and then test the deployment in Staging.
Yeah, you could experience data loss between one backup and the next... But if you don't have any backups, guess what you've lost then? Everything. That's why you need regular backups. But then, you should also get into doing log backups. These allow for point in time recovery, which means little to no data loss. I wrote this article on the basics of backups[/url].
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply