upgrading a sql server database

[scotspine]

ok, we've all been there. version 1 of your killer app is on the streets and selling like hotcakes. lots of happy clients have filled lots of tables with lots of data. one year on and you have finished version 2. naturally there are lots of new tables and lots of new fields in existing tables, some movement in the old table structure, relationships etc and of course lots of new stored procs as well as changes to existing ones. question is, what is the best way of managing this? backup all legacy data, re-install the complete new database and import or somehow tiptoe around what is already there, adding here, tweaking there. a friend who works for a large multi told me of a tool they spent 2 years developing to do exactly this and which now works seamlessly [so he says]. does anyone in the congregation know of a similar tool available that would do the job?

 

[scotspine]

interesting. i can't see it but do you know if it can compare an existing installation against a sql script?

 

[scotspine]

yes, thought so. thanks c, i'll check it out.

 

[kramer]

you may want to generate the scripts using SQLDiff and use a DTS to install any new lookup data alongside the create statements....

really you should have kept a change script as you worked on te system .. SQLDiff should only be used to check and verify the SQL scripts you have maintained while developing and testing..
How did you implement your test db?

Tut tut!!!

 

[weboo]

I have used dbghost and it will do everything you are looking for:

http://www.innovartis.co.uk/DBGhost_home.asp

Working in many companies as an application support person, I go through pains of pinpointing problems when we upgrade or roll back releases...often finding that a Database SP or table had changed without knowledge.

The key is change control, but we all know that some things like tuning of indexes etc are often missed after a release. The worst thing ever is to find that a developer has spent months upon months developing against an incorrect baseline that is nothing like the environment in which his/her code is destined.

This product should help keep baselines in sync and get things right each time, its a new company, so perhaps they will come in a demo it for you...they did with my old client, a very nice KIWI bloke came in who actually founded the company.

 

[scotspine]

dbghost looks very cool. i'll def have a look at that. ta. i know kramer i know. things got slightly out of hand a few months back and the position was never recovered. hence the currrent need. tut for sure! but really, sql change management is a black art!

 

[CtrlC]

Have a look at SQL Compare. I have been using it for 2 years. great tool.

 

[f_eriksen]

Red-gates tools are king in this area.

If your needs are (as mine) a bit more complex then you can, by using the dev toolkit, build your own custom upgrade patch generator using .net, including using the data compare for extracting/modifying possible reference data.

it should be noted however that any tool (and there are others too, but my personal pref is red-gate), will only take you "so far", and you might have some issues, or more complex logic for handling of existing data etc, that you will need to look at manually.

- or just hire me

 

[nobody here but us chicke]

sql

sql

can your users customise the system ? add bits ? write reports against your tables ?

how much is going to break when you roll out v2 ?

Do you only give them views or raw tables ?