Best Practice for updating the DB structure without losing any data

I and a team-mate are working out an web-based application for our company. SQL Server 2005 is used for data persistence. Since the application keeps evolving, while any latest stable update will be deployed into the production environment. No i开发者_Python百科ndividual DBA, nor SQL Comparator tools in our company.

My way, for updating the database in the production application to the latest release, is separating the process into:

1. Create a new db according to all CREATE SCRIPTS

2. Data Migration by Replication and/or transformation

3. Testing with the new instance

4. Replacement

My major consideration is that we can't guarantee the manually created script is error-free. However, I'm being challenged by the colleague that this approach is troublesome.

So, please kindly advise what will be the better? or the best?

p.s. we do not have budget for purchasing license-needed tools.

Thank you!

William

---

First, database code is like all other code and should be scripted and put in source control. That way you know what code needs to be pushed with what version of the application you are pushing (and can eassily be bundled with it). After all you might have changes that you are not ready to push to prod as the corresponding application code is not yet ready.

Under no circumstances should you ever change the database using the GUI, only make changes using scripts that are in source control.

Once you have data, all changes to tables should be done using alter table. If you can't use alter table for something (like adding an identity field after the fact) then you write a script to create a table with a slighlty diffeernt name in the structure you want, copy the data from the old table, drop the old table and rename the new one. Of course you will have to also drop and recreate indexes, constraints, triggers, etc. if you have them on the table as well. Luckily most changes can be done with alter table.) If you need to create a new field that is not allowed to be null, then you use alter table to script the new field with a default value or alter table to add the field allowing nulls, write updates to populate the field with existing data and then write another alter table to make the field not null.

---

If it is possible i recommend to use SSIS. Main advantage that you can dynamically change database Name to required. In other cases you need to build SQL statement dynamically. Second question is error handling. If you have error in schema script(structure), you should do not continue. If you have error in data insert script, you should process row by row and roll back only error-contained transactions. You can implement this by using ForEachLoop in SSIS. Only one question to preparing data for row-by-row processing. That`s why i recommend do not use "standard" script from SSMO but use another data container, eg XML.