SQL Change Automation 3

Overview

Offline Schema Model is a feature that you can enable in your SQL Change Automation project that allows you to browse the contents of your SQL Change Automation projects at the object level. One reason to enable this feature is to help you keep an audit of changes to your database objects (e.g. by using your source control tool’s Annotate/Blame feature).

When enabled, this feature extracts all of the tables, schemas, users and types within the database to a folder right within the project itself. The objects are stored by object type into sub-folders using their fully-qualified names, e.g. Tables\dbo.Customers.sql, Tables\dbo.Orders.sql,Types\dbo.OrderNo.sql etc.

If you’ve used SQL Server Data Tools or SQL Source Control before, then this file layout may be familiar to you.

How do these files differ to typical SQL Change Automation scripts?

The Schema-Model sub-folder hierarchy contains an offline copy of your database schema in T-SQL format that is read-only. The files themselves contain declarative representations of the schema objects, with CREATE statements that are updated to reflect object changes, rather than the imperative statements of migrations, which generally follow an ALTER approach.

The files within the hierarchy are not designed to be deployed, and therefore are not included in the build.

How it works

The Offline Schema Model is created and maintained by the SQL Change Automation tool-window in Visual Studio: whenever you Import new changes from your sandbox database, the model will be updated in the project to match the schema changes being imported.

Click the Refresh button in the tool-window to initialize the offline copy of your database, which will be placed into a subfolder called Schema-Model (as shown in the above screenshot). When you make incremental changes to your schema (e.g. using SSMS or the SQL Server Object Explorer), these changes will be reflected by your Schema-Model folder whenever you click Import and Generate Script within the tool-window. If you happen to add new schema changes by adding/editing a migration or programmable object within your project, to update the schema model first deploy the project and then click Refresh within the tool-window.

In SQL Change Automation, Offline Schema Model is enabled by default in new projects, however if you created your project using SQL Change Automation Core, you will need to open the Project Properties dialog and enable it:

Object types included in the Offline Schema Model (by default)

The following items are included in the folder structure when you enable this feature in Project Settings.

Tables*
Assemblies
Credentials
Defaults
File Groups
Full Text Catalogs/Indices/Stop Lists
Partition Schemes/Functions
Roles
Rules
Schemas
Search Property Lists
Services/Bindings/Queues/Contracts etc.
Sequences
Synonyms
User Defined Data Types
Users
Xml Schema Collections

*Table files include definitions of primary keys, foreign keys, constraints, default values, indices and DML triggers.

All files contain permission GRANT statements, where applicable, plus any extended properties attached to the objects.

Adding Programmable object types to OSM (optional)

Additionally, the following types of programmable objects can also be included in the OSM:

Stored Procedures (StoredProcedures)
User Defined Functions (Functions)
Views (Views)
Database Ddl Triggers (DDLTriggers - Note: this includes DDL Triggers only as DML Triggers are always stored alongside their parent Table)

If you have already enabled Programmable Objects, then it is not necessary to include these programmable types in the Offline Schema Model.

Enabling these object types will actually cause duplication between the two folder structures.

Controlling which object types to include

If you wish to customize which objects get included in your SQL Change Automation project’s Offline Schema Model, edit your .sqlproj file and add the following under the root node of the file:

Object types to include (.sqlproj fragment)

<PropertyGroup>
 <SyncToOfflineSchemaModelObjectTypes>Table;Assembly;Contract;Credential;Default;EventNotification;FileGroup;FullTextCatalog;FullTextIndex;
FullTextIndexColumn;FullTextStopList;FullTextStopListStopWord;MessageType;PartitionFunction;PartitionScheme;Queue;RemoteServiceBinding;Role;
Route;Rule;Schema;SearchPropertyList;Sequence;Service;Synonym;Users;UserDefinedType;XmlSchemaCollection</SyncToOfflineSchemaModelObjectTypes>
</PropertyGroup>

Add or remove any items from the list to control which objects get included (note that all programmable object types have been omitted from the above list).

After making any edits to your project file, build the solution to clear the SQL Change Automation tool-window cache.

Group by schema (into sub-folders)

By default, SQL Change Automation splits the objects in the Offline Schema Model sub-folder hierarchy by object type and prefixes each file with the appropriate schema name (e.g. Tables\dbo.MyTable.sql).

If, however, you would like to group your object files by schema (in addition to object type), then open the project properties window and check the Group object files by schema (into subfolders) option.