Exclude baseline for sqlcmd script

wimrey

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...

dnlnln

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?

wimrey

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.

dnlnln

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.

rerezz

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.

dnlnln

That's quite a valid scenario for this request, thank you rerezz. I'll forward this onto the dev team for further consideration