locked
Composite Projects and Incremental Deployments

    Question

  •  I've been able to get composite projects to work for full tear-down and rebuild processes where I don't need to keep the original data around ("Always re-create database" = true). It works great. We can deploy to multiple environments with different security settings and we can automate it. Great stuff!

    Unfortunately, now we need to work out mechanisms for automating, as much as we can, incremental deployments where we will keep the data around. Generally, this works well enough when two things are true, we're not allowing objects to be dropped (setting "Generate DROP statements..." = false) and when there is no chance of a data loss ("Block incremental deployment if data loss might occur"  = true). But we do need to drop objects sometimes and we do need to deal with changes that might result in data loss.

    The question is, how do you automate incremental deployments?

    If I run a compare in the composite environment, I'm either going to show my security settings as being missing because they're in a different project, or, I'm going to be showing all the other database objects as being missing because they're in a different project. Is there a best practice or suggested method or something to help me out a bit here?
    Tuesday, January 20, 2009 7:41 PM

Answers

  • If you are trying to deploy changes from script, you have to run these yourself through SSMS or SQLCMD.  We are working on enhancement to allow scripts to be executed through vsdbcmd for a future release.

    An alternative, is to put this data migration in the pre and post deployment scripts.  That is a common practice and is used to manage data movement necessary during the deployment process.  This is often version specific, so you would need to be defensive in your script like check to see it the migration needs run based on the shape of the target schema, or absense of object, or sqlcmd variable representing the version your are releasing.

    I am putting together a blog posting on this.  Let me know I will send you the early draft.


    Barclay Hill Program Manager VSTS: DB Team (DataDude, DBPro, Database Edition)
    • Marked as answer by StevenPo Thursday, January 29, 2009 12:40 AM
    Tuesday, January 27, 2009 12:40 AM
    Moderator

All replies

  • I don't know completely understand how it works (someone else on the team handles this), but you can automate incremental deployments with MSBUILD or if you are using Team Build.
    Anthony Martin | www.emc.com/mspractice
    Wednesday, January 21, 2009 1:12 AM
  • True, you can. We do it that way for the tear down/rebuild processes. The problem is, incremental builds will only succeed when the changes don't cause data loss. I know for those that cause data loss we need to do some manual work with the deployment scripts, I'm OK there. What I'm stuck on is that the deployment script generation is going to be more than a little problematic if we have to drop objects.

    When this happens, since we have a composite project for security, I guess we're going to have to drop and then recreate the security objects. It's just messy and prevents easy automation of the deployments. We have to have two automation paths, one strictly using msbuild (which works great, I can't emphasize enough how satisfied we are with the ability to automate tear down/rebuild across multiple environments) and another that aggregates multiple tsql scripts. I'm just trying to avoid that, if possible.
    Wednesday, January 21, 2009 12:44 PM
  • Hey Grant,

    You should be able to do this by deploying your outer most project.  You could allow incremental deployments that may introduce data loss and generate drops in this outer project as well.  The outer project would be your base project + security project. In other words, deploying your composite project to script or database.

    We currently have a bug that prevents the deployment of this setup.  We are working on a fix for it. 


    Barclay Hill Program Manager VSTS: DB Team (DataDude, DBPro, Database Edition)
    Saturday, January 24, 2009 2:35 AM
    Moderator
  • Hello,

    So that's why I'm getting errors when I try to do it? I was convinced I had something wrong. Thanks for the update.

    I have a different, but related question. Let's assume I need to add a column that doesn't have a default value, but instead I want to query another table to insert the data. Obviously, I can generate a script, but not deploy it. Then I can edit the script that was generated. From there, referencing only the updated TSQL script, can I still deploy through the DbPro? If not, do you have a suggestion for the right command line to use with msbuild to, I assume, call sqlcmd for the deployment with the script I edited?

    Thanks again,

    Grant

    Monday, January 26, 2009 6:11 PM
  • If you are trying to deploy changes from script, you have to run these yourself through SSMS or SQLCMD.  We are working on enhancement to allow scripts to be executed through vsdbcmd for a future release.

    An alternative, is to put this data migration in the pre and post deployment scripts.  That is a common practice and is used to manage data movement necessary during the deployment process.  This is often version specific, so you would need to be defensive in your script like check to see it the migration needs run based on the shape of the target schema, or absense of object, or sqlcmd variable representing the version your are releasing.

    I am putting together a blog posting on this.  Let me know I will send you the early draft.


    Barclay Hill Program Manager VSTS: DB Team (DataDude, DBPro, Database Edition)
    • Marked as answer by StevenPo Thursday, January 29, 2009 12:40 AM
    Tuesday, January 27, 2009 12:40 AM
    Moderator
  • Thanks for all the good info. Sure, I'd love to see the post. I'm not sure it's an approach we'd take, but I like knowing all my alternatives.
    Tuesday, January 27, 2009 1:42 PM
  • Still working on getting article put together but here is a simple walk through of data motion

    http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/49bf2a88-d80d-4a9f-970e-728bd530332e
    Barclay Hill Program Manager VSTS: DB Team (DataDude, DBPro, Database Edition)
    Friday, March 13, 2009 9:55 PM
    Moderator