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. Thanks.
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! :)
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.
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.