tatiyants.com Database Source Control and Deployment

All good devs know that your source code should be in source control. There are many reasons for this, but one of the most important ones is the ability to rebuild and deploy your entire application whenever you want.

Database artifacts (tables, views, triggers, stored procs, etc) are an integral part of the application and should therefore be treated just as any other code. You should be checking them in to source control. You should be able to build and deploy them automatically, along with the rest of the application.

There are probably many strategies for how to do this, but I’d like to focus on two: incremental migration and schema synchronization.

Incremental Migration

With this approach, every time you make a change to your schema, you create two scripts: one to apply it and one to revert it. The scripts are numbered or timestamped in the order they need to be applied. So, deploying a new version of the database is simply a matter of applying these scripts in the right order. This technique is quite popular and widely used (Ruby Migrations is probably the most notable utilizer of it).

This approach is nice because it’s simple. Delta scripts are easy to create and apply, database schemas are easy to version, changes are easy to roll back. The primary complication is that you need to track what version of the database you’re deploying to. Otherwise, you run the danger of not applying all of the changes required.

Schema Synchronization

With this approach, your entire database schema is first checked into source control, with each artifact being represented by a script. It’s important to note that these scripts usually contain only CREATE operations (and not ALTER). To make a change to the schema, you simply modify the corresponding script.

The really interesting part is how deployments happen. To deploy, you point at an existing physical database. Its schema is compared against the schema represented by scripts in source control. A delta script is then generated and ran against the target. Visual Studio database projects use this approach to manage schema changes and deployments, as does Red Gate’s SQL Source Control.

It includes-A primary progressiveRelapsing-remittingSecondary professional viagra progressiveSymptoms of Multiple SderosisVisual disturbanceLoss of sensationMuscular SpasmTremorsGiddinessCognitive DefectsDepressionSpeech ImpedimentsAyurvedic Treatment of MS When it comes to treatment, every person looks for safe and effective treatment of sexual dysfunction. If there was no market, they wouldn’t bother doing this would they? You will find that your customers and potential customers do all the social media marketing work for you! This is called canada sildenafil http://twomeyautoworks.com/?attachment_id=242 viral marketing and in combination with our SMO video, networking and social media services can really help turn your business into a massive online sensation! WorldClass SMO Video: Contact Us!. There is no even a minor difference between them in terms of http://twomeyautoworks.com/?attachment_id=244 generico levitra on line performance. Especially a small number of women in pre-marital sex drive are very weak, cialis canadian prices even more difficult to cure. The cool thing about schema synchronization is that it works with any version of the database. If you point it at a empty local database, it will rebuild it from scratch. If you point at production, it’ll apply only the changes which are actually needed.

Choosing The Right Solution

I find that the schema sync option works a bit better with DBAs because the deployment process produces a single delta script, which they can examine and get comfortable with before applying changes to production.

On the other hand, devs prefer the incremental migration because it’s more widely used, it’s more transparent (i.e. there is no magic in figuring out what the delta is), and it’s more dev friendly. There are even frameworks which allow you to code your migrations as objects (a good example in the .NET space is Fluent Migrator, which lets you code db changes using a DSL in C#).

Whatever option you end up choosing, you should make sure that it works for everyone who will be making db schema changes: devs and DBAs (and anyone else who needs to touch the database). There is danger in picking a solution that’s really dev-friendly, but alien to DBAs.

Remember: if the solution you picked is not accepted by the entire team, it would defeat the purpose of having your db schema under source control in the first place.

Incremental Migration

Schema Synchronization

Choosing The Right Solution

You may also like: