ScriptInMigrations vs UseRepeatableScriptsForAllObjects

AdamY

Can someone please explain the difference between ScriptInMigrations and UseRepeatableScriptsForAllObjects in the ProgrammableObjectHandling setting? I've already read the definition in the Redgate documentation, but I'm brand new to SCA and I don't understand the documentation.

Even better, when would I want to use one over the other? What are the pros/cons to each?

I see that the migration scripts generated by SCA (I use the SSMS plugin) do not include the programmable objects (views, sprocs, etc). As best I can tell, in order to see the real script that will run on a target database (including programmable objects) I must use the PowerShell cmdlets to generate the full script. If I were to use the ScriptInMigrations setting, would I be able to see the full migration script, including programmable objects, in the migrations script folder without needing PowerShell cmdlets? If so, that sounds nice, but what is the downside?

Thank you very much. This is all very new to me and I appreciate the help.

DanC

Hi @AdamY

I'll try my best to explain from my understanding of the differences and hopefully, this will help you!

Using ScriptInMigrations:
The first thing to note is this will Disable Programmable Objects which will then script all objects into a single migration script. 

Benefit:
- Using migration scripts only are simpler and easier to understand, you will avoid dependency issues and it's easier to understand the deployment order just by looking at the migration scripts

Using UseRepeatableScriptsForAllObjects:
Using repeatable scripts means that Programmable Objects are active, this means objects such as Views, Functions, Triggers, and Stored Procedures will then be scripted into a separate folder for PO's. Which will look something like (Programmable Objects > dbo > Functions). It's also worth noting that in this case, Programmable objects are deployed after Migration scripts

Benefit:
- Programmable objects will be state-based functionality, thus allowing changes to these objects to be branched, merged, and annotated
- Easier to work with Source Controlled branches
- Can see a slight performance improvement

Drawback:
Sometimes you can run into a dependency issue, for example, if a table has a column that requires a function it may fail and will need manual intervention - example

AdamY

Thank you, Dan! That is very helpful. It also helps our team understand the pros and cons of using the migrations-based approach vs state-based vs hybrid. And now I can see that using a hybrid approach combined with the ScriptInMigrations option might be a good option for us.

DanC

Hi @AdamY

Glad I could help provide some extra information to support your decision!

If you have any other questions or come across any issues, don't hesitate to get in touch with us on support.

AdamY

For anyone reading this, I posted a follow-up question here regarding schema-model behavior when using the ScriptInMigrations option.