Stairway to Database Source Control Level 2: Getting a Database into Source Control

  • Comments posted to this topic are about the item Stairway to Database Source Control Level 2: Getting a Database into Source Control

  • One thing worth mentioning that bit me recently - SSMS doesn't script out compression levels by default. We had a handful of indexes that were compressed and I had to track down the options in SSMS to enable this. Tools -> Options -> SQL Server Object Explorer -> Scripting then turn on the options you want such as Data Compression.

    If you don't do this you will lose any compression options you had when generating your scripts.

  • I think you need to mention Visual Studio database projects.

    These have been since VS2008, if not before, and have changed a lot over time.

    You can now load a db schema into a project then edit, and deploy, to target an environments of your choice, e.g. UAT, Integration, etc....

    You can use SQL Cmd, and variables to target different environment, e.g. server name, database name, login/username etc...

    You can also do schema comparison, db1 vs db2, db project 1 vs db project 2, or db project vs database.

    This is available in VS2012 pro and above.

    VS2013 pro also include data comparison, where in VS2012 you need the Premium edition.

    Both versions include data generation, can use regex for generating formatted data. Can use data range, range of values etc...

    The good thing is that this is just another project inside a visual studio solution, and you can use whatever Source code control you fancy, TFS, Vault, etc...

    When you label / branch code, the database comes with it, no special treatment required.

    Db projects also have the notion of 'reference' database, for cross db queries.

    This can be achieve via a project in the same solution, or a dacpac file (basically a compiled database project).

    I have also used linked servers.

    The down side is that it takes some getting used to, as the errors are sometimes rather cryptic.

    The output windows, is normally the best place to look for clues, not the error list.

    I'm currently using this on 2 Visual studio solutions. One with 2 web apps, 4 databases projects and other c# custom libraries. The other solution has 2 database projects, a web app, and a managed windows service, the advanatge for us is that we can have 3 UAT sites (for each solution), and maintain one set of source code in Vault. Also db projects do reference each other inside and across solutions.

    If you have visual studio 2012 pro or later/above you can already do all of this without having to buy any third party product.

    Having said this Redgate has some features not available in db projects, e.g. schema comparison from database backups, and I would expect the data compare to be more advanced, I just have not had the need.

  • I'm using SSDT as well - free to use and works really well with SQL Server (with a learning curve). I'm trusting Dave to go with what he knows and am interested to see what he does. I'll probably stick with SSDT, but it's always interesting know other methods.

  • I am having challenges with the reference data -- version control/keeping track of the reference data especially the large ones (> 100000 rows). I am thinking of using the merge script but merge doesn't work well with the large data set. Look forward to the next instalment.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply