Exclude baseline for sqlcmd script

wimreywimrey Posts: 10 Bronze 1
edited March 10, 2017 4:33AM in ReadyRoll
Hi,

When creating a sqlcmd file, is it posible to only include migrations afther a specific version. E.g. the baseline.

We have about 10mb of baseline scripts (excl most data) and updating a database takes a lot of time, because all the baseline scripts get executed on every database. This is good when creating a new database and is fast enough when you are updating 1 database, but on 500 databases, 10s means 10*500 seconds ~ 83min of checking if statements. We could manually delete Parts of the script, but manual script work is the reason we are now using readyroll...
Fyi: I don't mind a solution that needs to executed some custom code, I understand that this is not something that is needed for every project...

Comments

  • Hi there,

    I understand where you're coming from, in that the package scripts can be slow to process through all the (already deployed) batches with each deployment.

    As a first step, have you tried reducing the number of GO statements in your scripts at all? I know that this is a bit of a workaround but reducing the number of batches will mean less time spent evaluating IF statements.

    There is an alternative approach you can use to deploying your database: the patch script method. Basically this will generate a script that contains just a delta of pending migrations for a given target server/database:
    C:WindowsMicrosoft.NETFrameworkv4.0.30319MsBuild.exe 
      MyDatabase.sqlproj 
      /p:TargetServer=MYSERVERINSTANCE 
      /p:TargetDatabase=MyDatabase
    
    If you'd like to execute the migrations immediately you can add this to the above:
    /p:DBDeployOnBuild=True
    
    Does that help at all?
    Daniel Nolan
    Product Manager
    Redgate Software
  • wimreywimrey Posts: 10 Bronze 1
    Hey,

    when I build the project with these parameters, I get some files in my bin folder:
    {projectname}.sql
    {projectname}_Package.sql

    Am I correct that the {projectname}.sql contains the diff, and the {projectname}_Package.sql contains all scripts?
    it would be awesome if that was the case! then we can generate a script for new databases and generate a script for the incremental upgrade.
  • That's correct, the {projectname}.sql contains just a diff of migrations. It's the same method that is used when you deploy to your local database within Visual Studio and should make for a faster deployment in your case, given the combined size of your migrations.

    Please let me know how you go with this.
    Daniel Nolan
    Product Manager
    Redgate Software
  • I've the same requirement. But the diff/delta script {projectname}.sql doesn't solve the problem for me. I don't want to build the project for every target database in my pipeline and I can't assure that they all have exact the same version. So basically the migration script generated by ready roll fits perfect. The only problem is the execution time (especially on cheaper azure instances used for testing and other pre production).

    So for me it would be perfect to have an option described by wimrey to mark a specific version as baseline and exclude the prior migrations from the deployment script.
  • That's quite a valid scenario for this request, thank you rerezz. I'll forward this onto the dev team for further consideration
    Daniel Nolan
    Product Manager
    Redgate Software
Sign In or Register to comment.