SQL/Schema comparison and upgrade

views:

130

answers:

+3 Q:

SQL/Schema comparison and upgrade

+2 A:

I still think RedGate is the way to go. It is true that it does not always catch all the dependencies, and you may need to hack on it a bit, but it gets you 95% of the way there, and would be a huge timesaver IMO.

Once you have the script generated, you can easily hack on the way error handling and transactions are done, the output is very well documented, so it is trivial to see what is going on.

One possibility would be, rather than modify each database in place, do this:

create your a new version 8 database (DB_NEW)
migrate all of the data from the old database (DB) (you will need up to 7 different data migration scripts for this)
validate new database
if success, rename DB to DB_OLD and rename DB_NEW to DB

RedFilter 2010-05-10 14:07:04

I've tried RedGate and it helps me to about 50% of my needs. Worse, it doesn't allow me to compare two schema's within a single database. Thus I need to make multiple copies of the database, rename schema's and then do a comparison. It's not saving me any time.

Workshop Alex 2010-05-10 14:12:16

@Workshop: See my update.

RedFilter 2010-05-10 14:13:54

Ormnan, I could create upgrades from 1 to 8, 2 to 8, 3 to 8, etc. However, I prefer to create an upgrades from 1 to 2, 2 to 3, 3 to 4, etc. It allows the application upgrade to be done in little steps too, in case one office can't immediately upgrade to the newest version. There are also other organisations that use the same application, although not with that many offices, thus having scripts that upgrade to the next version is more practical than upgrading everything to the latest version. (Because soon that latest version won't be the latest anymore, after version 9 is created.)

Workshop Alex 2010-05-10 18:33:40

+1 A:

Creating new database then migrating data is the best way. Probably you will need to create number of data transformation scripts, but I assume that differences between data structure are not huge. After migration I recommend to use any data comparison tool which allows sql-query results comparing to verify migration success.

SQLDev 2010-05-12 14:56:02

Forgot to mention: I'm using SQL Data Examiner from sqlaccessories.com for comparing sql-query results.

SQLDev 2010-05-14 11:21:06

+1 A:

Redgate is the answer, you can compare the different schemas and will also generate scripts for you based on the difference.

KZoal 2010-05-15 19:39:42

ansaurus

tags:

views:

answers:

SQL/Schema comparison and upgrade

related questions