Control the database version - an important task for DBA!
Do you need to control the database version?
How often you document the changes of a database?
I find it very difficult and helpless whenever I go to solve a database problem, hearing about they have no clue about database and its properties, sounds familiar eh!?
Why do you need a document or control the version of the database that is more or less important to your application availability?
How quickly you can keep up the version of database, say when you are working as Development DBA?
Simple steps are keep your SQL scripts as part of the source code under the version control, using such scripts automatically recreate the database as part of the build in order to keep them in line and will help a lot of perform the integration tests when it is required to make it up as an actual database for production. There are many strategies you have there but I think the best strategy is to make your application create and update the database itself, this it is easy to see that DDL code is a PART of your application and therefore it is automatically version controlled. Make sure you have documented the complex conditions within the code such as joining of multiple tables or calculations that are involved in the application. This way the part of your data that represents constant or configuration data should definitely be under version control as well. Every time you deploy the database, anything that must be there for the app to run out of the box is a candidate for scripting and versioning. As for the *variable* part of your data, have a synthetic data generation plan for testing.. ideally.
In the recent times I see using the SQLCompare from Red-gate is a very handy tool for a dBA to generate update scripts that will come handy for next roll-out of database creation on the other servers, if required. In many cases I see this as a tool to bridge the gap of support and having a particular version controlled DB schema must consist of the following at minimum:
- An immediate script available from source control to build a blank database for any given major/minor release.
- DDL or DML script itself or an equally available supportable script must also load the basic fact data required for the application to run.
- Say if version n+1 introduces new columns that didn't exist in version n, reasonable defaults must be populated if the app can't cope with NULLs. Such scripts should be able to be chained together also, so a user at version n who wishes to go to n+3 can run three upgrade scripts in a row and arrive at n+3 in a functional state.
- Also if version n-1 removes columns or whole tables that version n introduced, data must be both preserved in other schemas/tables that won't affect the app n-1, and then the new data should be removed or adjusted to be what app n-1 needs to function.
Say if you are developing a code-project kind of simulation then making use of SQLVSS which is a good integration solution for SQL Version Control script database objects into sourcesafe. No doubt that every line of DBA's responsibility says that the database is a critical part of your application and for instance if you deploy version 2.0 of your application against version 1.0 of your database, what do you get? A broken application, that's what. This is where you can fillup the gap by making sure that the database should always be under source control, right next to your application code.
There are various links available on web in highlighting the importance of database version control such as:
So next time when you are rolling out an application with small or big database then make sure to control the version of database even though there is a small change in terms of changing a column properties.
Follow the simple rules to make your life simple!