How to better handle Database Upgrade/ Development

We are in the middle of integrating and upgrading one of our major application. The database a very big(thousands of tables, views and procedures) one and is being redesigned as part of the upgrade. Currently old database is checked into source control along with the data. But there is a discussion to only take the schema and work on that with out the data. But the problem is there are lots of meta data and no one figured out yet which tables user data and which tables are for meta data at this point(former developers are not around anymore and upgrade is done by new vendor in conjunction with new in house developers). Its a new territory for me since I usually i get involved in the last stage of the project, starting deployment, but now I am asked how to better deal with this situation and I need your help.

Question is what is your experience in this kind of projects? How do you handle databases in upgrade projects? what are pros and cons and what is the best practice?

Let me know if you need additional information/clarification.


more ▼

asked Dec 06, 2010 at 06:41 AM in Default

avatar image

4.9k 33 39 43

Thank you all for your advices and its been equally very useful. Since my question was very open ended its hard to choose correct 'answer'. However I am going to pick one anyway...Thanks again.

Dec 06, 2010 at 10:24 AM DaniSQL
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Do you mean upgrade in terms of add new functionality or upgrade in terms of going from an older SQL Server edition to a newer one? I will assume the first.

If you expect to be able to do an upgrade and leave behind old data, you will have problems. It is generally better to perform a gradual upgrade. This can be part of a project that is being done with agile development aspects in mind. You iterate through upgrades in your software and database design, slowly swapping out old for new. This is the method that I would lean towards, as it offers you the ability to "rollback" your upgrade if things don't work so well. It is even possible that you keep both structures running simultaneously for a certain amount of time, to allow for applications that haven't made the step to the new DB/App structure. This is one of the many advantages of having a clean data access layer for your database. You can make thes structural changes and not have to worry if your programs will still work. As long as you offer up the same information to the GUI, you are sound.

The problems can be amplified further with the missing devs from the original design. You will be coding blind for a time if you take structures with you and not the data. You can only find out if something is missing after the fact - that will make your customers less than happy!

If you really want to have a clean break, then design/code without any of the old stuff at all. Forget it even exists and design the new system to the spec that you require (Project Manager and other stakeholders have to tell you what you have to code for here - design specs are VERY important). If you try to take half of what was, you will end up taking some of the mess with you - this can doom the entire project.

Needless to say, this sort of thing is not done on a whim and a weekend. You state that noone has figured out how the old system works and there is noone to ask. Just drop that system as it stands and start anew. If management was tending towards "take structure, forget data" you almost have the blank cheque to start anew anyway.

Either way, good luck. These sorts of projects are brilliant for learning, but can be very challenging (in terms of nerves and your brain in general).

Oh, yeah. Don't forget to document the new system for the next people that may/will have to do upgrades in the future when you have moved onto fame and fortune elsewhere! :)

more ▼

answered Dec 06, 2010 at 07:15 AM

avatar image

26.2k 18 38 48

@WilliamD you are correct,I meant upgrade in functionality and integration with other applications

Dec 06, 2010 at 07:38 AM DaniSQL
(comments are locked)
10|1200 characters needed characters left

We typically use source control as well just for the schema changes. We also try to keep our QA as close to production as possible, but are more flexible with a dev environment as changes are constantly being made.

If we are upgrading a database from a vendor supported application, I typically restore a copy of the production database to a development server, run their upgrade scripts against it and then run a compare from the new dev and production and review all the changes.

Not sure any of this helps you but it is what we do.

more ▼

answered Dec 06, 2010 at 07:05 AM

avatar image

40.9k 39 95 168

@Trad, Thanks. Our application is not vendor application per se but its developed with the help of vendors.

Dec 06, 2010 at 07:46 AM DaniSQL
(comments are locked)
10|1200 characters needed characters left

I put all the DDL into source control. With the tools available today such as Visual Studio, Red Gate SQL Source Control, many of the various database comparison utilities, all supporting TSQL objects under source control, management is very easy. You can also put your meta data into source control, but I generally like to put data into a database and work from there.

There are several keys to making this work. You need to document your processes so everyone knows how it works. You need management buy in so that there is enforcement of the process. It just takes one or two teams that won't follow the process (usually because they didn't invent it) to mess it up for everyone. Finally, you have to be disciplined so that all deployments come out of source control.

It's a lot of work to get set up, but it really pays off because you know what is deployed, you know what you're developing, and you know what you're planning to deploy. Knowledge is the win.

BTW, full disclosure, I wrote the deployment chapters in the book @FatherJack references.

more ▼

answered Dec 06, 2010 at 07:18 AM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

DDL in source control is a must, certainly for teams of >=2 people, I would argue for single devs too.

  • for enforcing source control rollouts to production. Do you automate this in any way Grant? I am working on a semi-automatic deployment using a control table and powershell running against our TFS.

Dec 06, 2010 at 07:24 AM WilliamD

+10 for writing the book. I havent got to your content yet, still learning from PhilFactor...

Dec 06, 2010 at 07:31 AM Fatherjack ♦♦

We automate very little to production. We automate up to staging as much as we possibly can. But we try to run the staging scripts in production manually just as a final eyeball in case things go south. It's more paranoia than real purpose.

Dec 06, 2010 at 07:36 AM Grant Fritchey ♦♦

@Grant - hence the semi-automation for us - heaven forbid we do blind rollouts on the DB. The app team can do what they want as far as I'm concerned, but the DB has to be locked down. My PoSH script spits out scripts to add the objects from TFS and extra spinkles (add items to replication where needed).

Dec 06, 2010 at 07:44 AM WilliamD

You can keep it in either. It's up to you to decide how you want to manage it. I prefer to manage data within databases. So we created a metadata repository. There we keep the meta data, change it, maintain it. Then we can deploy from it using SQL Data Compare (or a similar utility) to generate data scripts.

Dec 06, 2010 at 08:17 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

We dont have any projects so big here but I would recommend anyone to use RedGates SQL Source Control Tool and read this book http://www.simple-talk.com/books/sql-books/the-red-gate-guide-to-sql-server-team-based-development/ so that they get a feel for why they are going through the steps that are needed. The latest version of RedGate's SSC will ship static 'reference' data as well as the schema DDL so if you can identify it you can have it solved.

I guess a lot will revolve around how much this is an upgrade and how much it is demolition and rebuilding. I have found 'retro-fitting' SSC to projects causes more problems than having it on a new-build project from the start.

more ▼

answered Dec 06, 2010 at 07:07 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

  • for the book, and +1(virtual) for the last paragraph. Basically what I wrote, but much more concise and to the point.

Dec 06, 2010 at 07:16 AM WilliamD

@FatherJack, We have SQL Compare and I use it all the time but we dont have the redgate database source control. We use subversion and TFS here. I am getting used to using visual studio unltimate to manage dev and deployment of database projected....

I already downloaded the new Redgate book when it came few weeks ago but didnt get the time to read it. I will go through it asap. As for upgrade/Demolition-rebuilding...its definitely both. many new things are going to be added but we are going to retain part of it too....

Dec 06, 2010 at 08:04 AM DaniSQL

RedGate's Source Control Tool works with SVN fine (it's the config I use) so you might want to take a trial of it and see if its worth using. The book refers to it but not as a prerequisite. Certainly read the book.

Dec 06, 2010 at 08:11 AM Fatherjack ♦♦

You can also control meta data in Redgate Source Control when coupled with SQL Data Compare. I haven't tried it yet, but it looks really good.

Dec 06, 2010 at 08:15 AM WilliamD
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 06, 2010 at 06:41 AM

Seen: 1895 times

Last Updated: Dec 06, 2010 at 06:41 AM

Copyright 2018 Redgate Software. Privacy Policy