Perception vs. reality, how to keep your database schema changes of different environments aligned with VSTSDB and VSDBCMD.EXE Part 1

  • Article

Yesterday at the PASS conference I met an former colleague and he asked me about a particular challenge applying changes to the production database from Visual Studio Team System Database Edition. As he didn’t know about the new features of the deployment utility VSDBCMD.EXE, I decided to make a blog post series and explain the needs, mechanics and ways to do that.

In most scenarios in the database lifecycle you have several environments, development, testing, user acceptance test (UAT), Pre-production, Production. Depending on the methodology you are using it can be more or less. In most environment there is a strict separation of duties due to the reason that it has to be comprehensible who did changes and to restrict the access to the sensitive data which exists e.g. on the pre-prod and prod systems. This comes with the consequences that e.g. the developer has no access on pre-production, UAT and maybe production and may not be able to use his full tool suite to do changes or monitor the changes. Maybe there are also no development tools allowed in the production system which also limits the DA to leverage the functionality of those.

 

So, what does that mean for database development ?

Once you created your database and sent it to the DBA to install the database with its schema (lets call that time 0), you are completely disconnected from your database project. During the database lifecycle you sure always have the version of time0 but as everyone knows, changes might not occur only in the database project in the development environment, some of them are also done in a hotfix manner (at time1) directly on the production server by the DBA or by the developer through the dba itself with no update to the database project at all.

 

Well, you might say two-way schema updates are not a problem,but let me give you an example

I will pick up the example mentioned above. The database is developed and deployed at time0, during the production someone notice that it might be good to place an additional index on a column (time 1). Once the database is running smoothly, the DBA who did the changes forgets to inform the database developers to put in the index information in the database project or cannot do this by himself as he either has no access to the database project or does not care about development stuff (at this time I want to encourage all DBAs to really have a look at VSTSDB, it can, and will if you use it, make your life much more easy and is a perfect tool to administer your changes). Later at time 2 additional changes have been made to the database including a change which includes the simple increase of the above post-indexed column from the data type from INT to BIGINT with even no data motion (Data motion is when the project system and you can also see that in SSMS it has to do a DROP and CREATE table with an intermediate temp table storing the data and the new schema). If the changes of time 1 didn’t make it to the project system and you create your scripts doing a schema compare to the developer known time 0 schema, you will get an error during the deployment process of the changes of time 2, because an additional index will stop the ALTER to be applied successfully with something like the following error message:

 

Msg 5074, Level 16, State 1, Line 2
The index 'IX_SomeIndex’ is dependent on column 'SomeColumn'.
Msg 4922, Level 16, State 9, Line 2
ALTER TABLE ALTER COLUMN SomeColumn failed because one or more objects access this column.

 

Well, yes this is a problem now and at least at this time makes you think if additional changes didn’t make it into the project as well.

Ok, how to deal with that, some of them could be:

 

  • Use a strict development cycle to not make changes to databases unless the changes are also in the project system (well, this is the perfect world which doesn’t exists, even if you have really *hot* hotfixes in place)
  • Instruct the DBAs or the people in charge of the databases to make sure that the additional schema changes applied to the database will be also merge to the database project. (Better solution, but this also leaves a bit of uncertainty if really everyone worked 100% correctly)
  • Do a direct and online (In the meaning that the environment where VSTSDB live is the same as the production environment or there is a connection between them) compare with production database to have a *realtime* comparison (I will comment in this option later)
  • Do something like an offline compare where you compare the “source of truth” (for the schema this is the database project) with the production database without having a direct connection to the production database and apply changes directly or review changes before applying them. (That looks pretty good actually, lets see later how this works for us)

 

In the next part, we will take a look at some of the options to make sure which is the best fit for your environment.

 

-Jens