DevOps - Continuous Data Migration Using Visual Studio and TFS RRS feed

  • General discussion

  • Continuous integration is a core, accepted practice in modern application development. So why shouldn’t data be treated the same way? Learn how to set up continuous SQL data migration using Visual Studio and Team Foundation Service on a SQL project with an established production data model.

    Read this article in the November 2017 issue of MSDN Magazine

    Wednesday, November 1, 2017 8:45 PM

All replies

  • Pretty good article.  I'm honestly surprised there aren't comments in here.  This problem has plagued every team I've ever been on.

    So, does this process solve the following scenario?

    1. version1:
    2. I've got a table with ColumnA which has data
    3. version2:
    4. I need to add ColumnB with data that's based on a calculation of ColumnA; and ColumnA is then removed
    5. version3:
    6. I need to add ColumnA back (the definition is different than the original ColumnA - just the name is the same)

    So, I'm ready to release version3.  Let's say I have a bunch of deployed instances of the database; and there's no telling which version they're all using.

    I build my database project and I get a dacpac with version3.

    I've got migration scripts in my migration project for each version.  The migration scripts for version1->2 and version2->3 reference a version of ColumnA which doesn't exist in version3.  I'm assuming these migration scripts are simply moving data; the schema changes can be handled by the database project.  (Is this correct?)

    If it's a brand, new database, I don't need any migration scripts.  We go straight to version3, and we're all happy.

    If the database is at version1, what happens?

    If the database is at version2, what happens?

    Thursday, February 22, 2018 8:03 PM
  • Harley,

    The version of the database will be always found by the internal.DatabaseVersion table. So if you have database deployed with version 1 and if you are trying to upgrade it to version 3, the scripts will update it to version 2 and then 3. So in your case it is going to drop the column first and then add it back.

    Now all this has to be done using the custom post deployment scripts in a Database.Migration project. The incremental deployment that comes out of the box from VS wouldnt be able to handle this.

    All your questions about windows ends here

    Friday, February 23, 2018 7:57 AM