In this post, I want to share with you a great solution to one of the biggest problem in the development life cycle. Almost every system work with a data. We usually store it in db and when we do it we will get all db capabilities (Multiple users, Security, Performance, and much more). But one of the most hurtful thing in development process is the db source control. We always have some troubles in managing database entities. Furthermore, when we want to do some maintenance operation on our database, like cleaning all unnecessary tables or just to be sure that the column type in the development table is equal to the one on the production, it could be an adventurer mission.
In past I worked a lot with Oracle db (version 8i, 9i and 10g). We managed our db entities by ourselves. We accomplish this task by writing an organized documentation for each change we did in the db scheme. In addition, we created it as a Sql script file and putted it in the source control with the global excel document that give us some details about the changes that have been made. This solution had some problems. It took some effort to insert a complete table with all foreign keys, triggers, grants, synonyms, constraints and more. In addition, if someone made changes in the table definition which had an existing creation script (and the table already contained a data), the update process became much sophisticated. When I started a new workplace I noticed that they don't have a lot of problem in this process. They are using the Sql Server Project. The Sql Server Project gives us the abilities to manage all db entities. All db scripts can be stored directly in the source control and deploy into a remote db. One of the great features that the Sql Server Project gives us is the abilities to compare between schemes and compare between data tables. For example, if you want to create a new table, you can create it in the visual studio in a new Sql script file and to deploy it to the db in just a couple of seconds. Furthermore, the Sql Server Project can be built, and you get errors if the db scheme wasn't built correctly.
Now let's see some screenshots that help us to figure how to work with it:
Open a new Sql Server 2005 db project:
Then choose the “New Scheme Comparison” in the “Scheme compare” toolbar:
Because we are working on existing db and we want to create a new project that will contains all the db entities, we should choose the target scheme to be the source scheme and our new project to be the target.
After clicking on the “ok” button we’ll get all the db entities with the creation option (This option define the operation that will execute when we want to write the changes to another source).
The next step will be to create a full db project that will contain all existing db entities. To accomplish this task, we need to click on the “Write Updates” button in the “Scheme compare” toolbar. After a confirmation message our project will contain all the necessary data from the db.
Now you can do some powerful things with your project. For example you can deploy it into a new db, and it will look exactly like the original one (You can define your target db in the build tab in the project properties page).
If you are developing with Oracle db you should check the ODT.NET it contains almost all these feature (Maybe without the compare tool)…
We have a powerful tool to manage our db entities. This tool helps us in development and in the deployment processes and it has a full integration with the source control. So, in the next time you are going to think what should be the best way to increase the db management work, just try it.