Schema-Model behavior when using ScriptInMigrations option

AdamY

According to this Redgate documentation, using the ScriptInMigrations option in the ProgrammableObjectHandling setting has this effect:

This effectively disables the programmable object feature and scripts all programmable object types out in migration scripts, as with any other object type. All such objects will also be scripted out to the offline schema model.

The way I read this, "all such objects" means/includes programmable objects (sprocs, views, functions, etc).

However, when I use the ScriptInMigrations setting, the Schema-Model folder still only has tables. The programmable objects are not there. Migration scripts, as expected, does include the sprocs now (yay).

Am I misunderstanding that Redgate statement?
Should the programmable objects be saved (individual script for each object) when the ScriptInMigrations option is used?

If the results I see are expected, I plan to open a feature request for a new setting that will still put programmable objects in the migration scripts, but also include scripts for each programmable object in the Schema-Model folder so we can track object-level history in source control without needing to use SQL Source Control combined with SCA.

DanC

Hi @AdamY

I've just done some testing around this and I believe you need to edit the .sqlproj file and configure the following property(see Controlling which object types to include  https://documentation.red-gate.com/sca/developing-databases/concepts/offline-schema-model)

In order for the Programmable Objects to show up, you will need to delete the existing migration scripts and re-generate them so they get applied to the Offline-Schema model

For example, because I want everything scripted out separately in the Offline-Schema model, my properties look like the following:

 <PropertyGroup>

    <SyncToOfflineSchemaModel>True</SyncToOfflineSchemaModel>

    <SyncToOfflineSchemaModelBySchema>True</SyncToOfflineSchemaModelBySchema>

    <SyncToOfflineSchemaModelObjectTypes>Table;Assembly;Contract;Default;EventNotification;FullTextCatalog;FullTextStoplist;MessageType;PartitionFunction;PartitionScheme;Queue;ServiceBinding;Role;Route;Rule;Schema;SearchPropertyList;Service;Sequence;Synonym;User;UserDefinedType;XmlSchemaCollection;StoredProcedures;Functions;Views;DDLTriggers</SyncToOfflineSchemaModelObjectTypes>

    <SchemaModelSubFolder>Schema-Model</SchemaModelSubFolder>

  </PropertyGroup>

Hope this helps!

AdamY

I see that my settings for <SchemaModelSubFolder>, <SyncToOfflineSchemaModelBySchema>, and <SyncToOfflineSchemaModelObjectTypes> are all commented out, but look like this:

<SyncToOfflineSchemaModelBySchema>True</SyncToOfflineSchemaModelBySchema>
<SchemaModelSubFolder>Schema-Model</SchemaModelSubFolder>
<SyncToOfflineSchemaModelObjectTypes>Table;Assembly;Contract;Default;EventNotification;FullTextCatalog;FullTextStoplist;MessageType;PartitionFunction;PartitionScheme;Queue;ServiceBinding;Role;Route;Rule;Schema;SearchPropertyList;Service;Sequence;Synonym;User;UserDefinedType;XmlSchemaCollection</SyncToOfflineSchemaModelObjectTypes>

I guess those are defaults. I'll try uncommenting them and making sure the programmable objects are in the SyncToOfflineSchemaModelObjectTypes setting and see how it goes. I'll update this thread once I've had a chance to try it.

DanC

Hi @AdamY

Absolutely, as you've mentioned those are defaults and so the example I showed was from me adding the programmable objects to my existing default list. 

Also if you already have PO scripted out into a separate folder "Programmable Objects > dbo > Functions\Stored Procedures" once you've got them to be scripted out into the migration scripts you can safely delete this folder.

Let me know how you get on and if you need further help!

AdamY

That did the trick, @DanC. Thanks! We may not use this setup, but I like knowing the options and pros/cons of each.

DanC

Hi @AdamY

You're welcome and that's perfectly fine, I hope it helps you make a balanced decision on your approach!