Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
IdeaS
Starting Member
4 Posts |
 Posted - 2006-11-02 : 03:11:50
|
The next week will time the release of the new version of my application, and every time when there is a new version, the headache of upgrading the database structure pops up.Is there a way using SQL Server 2000 or any other product to automatically compare the structure of tables and stored procedures in the old database and the new database and do the upgrade  Thanks,AJ --------------------------------------------------------------------Be nice to nerds. Chances are you'll end up working for one. (Bill Gates) |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-11-02 : 03:16:07
|
| Look for Red - Gate Db Compare, this really a good tool for comparing the structure of the database.Chiraghttp://chirikworld.blogspot.com/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-02 : 04:01:45
|
| "Is there a way using SQL Server 2000 or any other product to automatically compare the structure of tables and stored procedures in the old database and the new database and do the upgrade"There are tools that will do that, but I think you would do better to change the way that you work.Generate a script for every change to your database structure (you can do this in Enterprise Manager : Table : Design by making the changes you want and then using "Save change script" icon rather than actually saving the changes.)Number the script files sequentially.(We also store in our change scripts any data manipulation that a new version needs - maybe we need to set a one-time default value in a new column, or convert a Country Description to a Country Code, or add/change some "meta data" etc.)Have separate files for every SProc / Trigger that you use.Store all these files in a version control system (my preference is SubVersion, anything will do - but NO version control system is NOT an option - and that applies to ANY programming project!)When you need to produce an update checkout of the version control system everything that has changed since the last time, and run the scripts on the target database.As well as being able to get a client from Version 1 to Version 2 this will also enable you to:Restore from yesterday's DEV Database backup and apply the changes SELECTIVELY to fix whatever got screwed up!Upgrade a client from version 1 to version 1.5, rather than the current Version 2Retrieve all the code for a Version 1 system and work out how to fix a client's problem without having to upgrade them to version 2And version control (if you don't already use that) also allows you to do:Peer review (even of your own code) - compare the current version to a previous version and check that the changes you made (even a while ago) look OK - its amazing how even seeing at yesterday's code changes enables one to see improvements etc.!Recover from code changes that don't work - as well as accidentally deletion / overwrite of files etc.Allow multiple developers to safely work on the same source codeProvide source code security (with Sub Version each developer would have a more-or-less complete copy of the code, in addition to whatever central backups you may have - home workers / laptops etc. therefore add geographic backup)Kristen |
|
|
|
Jim77
Constraint Violating Yak Guru
440 Posts |
Posted - 2006-11-02 : 06:30:38
|
| A Very informative read !, cheers Kristen. |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-11-02 : 06:44:28
|
| Kristen, is this is Development Protocol in your company?? Very Informative, thanks for sharing.Chiraghttp://chirikworld.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-02 : 07:50:24
|
"is this is Development Protocol in your company"More or less.We have some String&Gum that produces a concatenated single file of all relevant *.SQL files in changed since a given date (i.e. the date of the last "patch"). This is the new patch! (the BATch file also creates a "yyyymmdd___.___" file, which indicates the date that patch was prepared, and is a useful "marker" for Date/Time Sorted DIR listings)I'm moving from having a Patch File for each DDL set of statements to putting them in a database table, so that we can export them as XML and have a little utility execute them one-by-one, and in the right order, starting from the last one known on the Target Database.One of the problems currently is that if we execute a complete DDL patch script and it has an error in the middle we can't roll it back:BEGIN TRANSGOCREATE TABLE1 ...GOALTER TABLE2 ...GOALTER TABLE3 ...GOCOMMITBasically if ALTER TABLE2 fails then everything before that is rolled back, the transaction block is cancelled, and ALTER TABLE3 onwards runs outside a transaction  So my solution is to have something run the statements one-by-one and ROLLBACK and ABORT!! if it gets any errors.Then we can move to Daily Builds which is high HIGH HIGH! on my list of Must-Haves.That will mean that we can take all today's checked-in SQL code (and all the other stuff, but SQL is the only thing that is holding up this Dream) and run it on the QA database overnight, and also run the automated regression tests on QA, so next-day we can check on all the Regression Test Failures - whilst the tasks we did are still fresh in our mind, and thus fixing them will be 10 times easier.Right now our regression tests are bust because it was a month between regression tests, and when we ran them the regression testing system broke, and we had no time to fix it because there were so many changes in that period that we have no idea how to narrow down what is causing the problem ... and that's a vicious circle because the longer we leave it, and the more "improvements" we make to the application, the harder it is to fix the Regression Tests A full XML "patch" is a bit further down my list of Must Haves.I want to be able to paste an XML file into the application (or use WebServices/SOAP/ROPE /Whatever) so that the application can run all the DB updates, replace any upgraded ASP/JS/CSS/etc files [actually we name those including a version number, so previous-version files would be untouched], and then I will have an automated roll-out too.Currently it takes us about 2 days to prepare a roll-out, and then 2 hours per-client to roll it out. It goes something like this:Prepare for roll-out (i.e. once per version)Apply roll-out (once per SERVER - we may have multiple customers on a single server)Apply roll-out (i.e. once for each client)(*)Will be replaced by XML Patch System - i.e. automated(*)Manually make all concatenated-Script files(*)Apply all Patches and Script files, in order, to SHIPPING database(*)Transfer all Meta Data from QA -> SHIPPING (same from DEV -> QA happened some time before)(*)Clear down all Audit tables on SHIPPING, and Shrink database (to reduce size of backup)(*)Backup SHIPPING & ZIP(*)Upload the SHIPPING database to Client's Server(*)Unzip(*)Restore to TEMP databaseBackup client databases (both STAGING and PRODUCTION)Copy appropriate data from PRODUCTION to STAGING (so Staging is "fresh")(*)Apply all scripts, in order, to STAGING(*)Copy Meta Data from TEMP_SHIPPING DB to STAGING(*)ZIP up all newly changed ASP/JS/CSS/etc. application files (based on Change-date / Version Number in filename)(*)Upload to client's server(*)Unzip on clients server, and move to appropriate folders (NOTE: Version number in filename means that no overwrites are allowed at this time, which means that sites/databases can continue to use old versions until they are retired)Work through release notes making any additional changes (e.g. "In this version to take advantage of Feature X you need to perform Steps Y and Z")Perform client-specific testsHand over staging system to Client for evaluation & testingImplement all the new feature opportunity that client reads in the Release Notes and gets sufficiently excited about that they get their wallet out!Client performs acceptable test (the client has their own ideas about what they want to test, but our application has an inbuilt coverage-test - in Staging on each page is a "Status" and the client can change the status from "Test required" to "OK" or "Broken"; then there is a report of all the pages that are still at "Test required" that the client hasn't seen yet [Specific pages can be set to "Unused", which excludes them from the test, AND provides an Alert if the client happens to find a route through the application to visit such a page!])OK where was I ... Ah yes, client has now signed off the Acceptance Test, and we set a date for roll-out to Production. This is not allowed to be a Thursday or Friday 'coz if it all goes pear-shaped we don't want to be working the weekend, and in any case the client won't be so our chances of even knowing the client's customers are all hacked off is zero over a weekend!Put up "New version coming soon" Holding Page on Production siteSet production database to DBO onlyBackup Staging and Production databases (change of doing the right thing but on the wrong database!)Disable all jobs(*)Apply the SQL Patches and Scripts to productionCopy all MetaData from Staging to ProductionReset Production database from DBO only to NormalChange the Production Application to point to the latest version (i.e. same as Staging has been using)Use special "back door" ASP page to test the Production Application/Database (i.e. Joe Public still excluded)(A fairly simple "does it hold water" test is done at this point - it is time critical that the application is not offline for long, generally the Production roll-out is done 2 hours before the client's day starts, so they arrive to a functional Production database that they can do their final checks on and 30 minutes, or so, after their day starts we can put it live)Remove the holding page from the site, allowing Joe Public accessRe-enable jobs and external accessPerform more detailed tests, including checking that data transfer to 2rd party Apps is working OKSubmit time-sheets to accounts!!Kristen |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-11-04 : 02:11:54
|
| Aha.. Thanks Kristen for sharing.. Bookmarked it, for the future reference .. :-)Chiraghttp://chirikworld.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-05 : 00:36:46
|
| See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=75473 |
|
|
|
|
|
|
|
|
Upgrade Database Structure