Offline schema model
Published 31 July 2019
The Offline schema model (OSM) is a read-only object level representation of your SQL Change Automation project and is updated whenever you generate migrations or apply changes. The OSM helps you to keep an audit of changes to your database objects when used with the history feature of your version control system.
The scripts generated as the OSM are grouped by object type into subfolders using their fully-qualified names.
Example file structure
Schema-Model |-- dbo | |-- Tables | | |-- dbo.Customers.sql | | |-- dbo.Orders.sql | |-- Types | | |-- dbo.OrderNo.sql
Integrating with version control
Offline schema model scripts help provide object-level history in conjunction with your version control system.
They are also useful for highlighting conflicts when merging in new migration scripts from version control.
Do not edit these scripts manually. If you do have a version control conflict, it is advised that you fix the issue in your migration scripts, and then regenerate the offline schema model scripts.
Object types included in the offline schema model
The following items are included in the OSM by default:
- Primary & Foreign keys
- Default values
- DML triggers
- File Groups
- Full Text Catalogs/Indices/Stop Lists
- Partition Schemes/Functions
- Search Property Lists
- Services/Bindings/Queues/Contracts etc.
- User Defined Data Types
- XML Schema Collections
All files contain permission GRANT statements, where applicable, plus any extended properties attached to the objects.
By default, programmable objects are not included in the OSM as they are already included in the programmable objects folder.
The programmable object types are:
- Stored procedures
- User defined functions
- Database DDL triggers
By default, SQL Change Automation groups the objects in your OSM folder by schema and then by object type (for example, dbo\Tables\MyTable.sql ). If, however, you would like to revert to the default SQL Compare behavior, grouping only by object type and prefixing each file with the appropriate schema name (for example, Tables\dbo.MyTable.sql ), you can do so by changing the SyncToOfflineSchemaModelBySchema setting in the project settings file .
<PropertyGroup> <SyncToOfflineSchemaModelBySchema>False</SyncToOfflineSchemaModelBySchema> </PropertyGroup>
Controlling which object types to include
You can customize which objects get included in the Offline Schema Model by editing your .sqlproj file:
<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;StoredProcedures;Functions;Views;DDLTriggers </SyncToOfflineSchemaModelObjectTypes> </PropertyGroup>
This is a full list of supported Offline Schema Model object types, including Programmable Objects. You can remove items from the list to control which objects should be included.
Disabling the offline schema model
The OSM can be disabled by editing your .sqlproj file:
<PropertyGroup> <SyncToOfflineSchemaModel>False</SyncToOfflineSchemaModel> </PropertyGroup>