Component versioning involves keeping track of the current state of an object or group of objects in the database, so that consumers know how to talk to the current state.

This approach involves updating consumers in advance of database changes, allowing these database changes to be performed in a simpler manner without the need for backwards compatibility logic.

Application developers can instead write this logic in their native language. 

Using a dedicated table and static data

Component versioning consists of creating a table dedicated to holding the versions of different components within the system, where a component could be an individual object, a group of objects (e.g. all the objects in a schema), or the whole database.

The data is stored statically so it can be controlled as a schema change.

Whenever a release is needed, versions may be updated in the same transaction as the accompanying object changes they represent. 

Online Walkthrough

1. Create a SQL Change Automation project
   
   

   1. Add a new version table containing static data (see below for example sql) 
      

      CREATE TABLE [dbo].[component_versions](
      	[component] [NVARCHAR](64) NOT NULL,
      	[version] [INT] NOT NULL,
      	CONSTRAINT [PK_component_versions] PRIMARY KEY CLUSTERED 
      	(
      		[component] ASC
      	) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
      ) ON [PRIMARY] 
      GO
      ALTER TABLE [dbo].[component_versions] WITH CHECK ADD CONSTRAINT [CK_component] CHECK  (([component]<>''))
      GO
      ALTER TABLE [dbo].[component_versions] CHECK CONSTRAINT [CK_component] 
      GO
      ALTER TABLE [dbo].[component_versions] WITH CHECK ADD CONSTRAINT [CK_version] CHECK  (([version]>(0)))
      GO
      ALTER TABLE [dbo].[component_versions] CHECK CONSTRAINT [CK_version] 
      GO
      UPDATE [dbo].[component_versions] SET version = 1 WHERE component = N'customers'
      GO
      UPDATE [dbo].[component_versions] SET version = 1 WHERE component = N'regions'
      GO

      
      
      

2. Refresh  SQL Change Automation, select the table, and click "Import and generate script"
   
   
   
   
3. Refresh and then include static data for the table 
   
   
   
   
4. Refresh and generate a migration script containing the static data. It might look like this:
   
   

Now everything is set. 

When the corresponding database objects next need to change, a new component version will be needed.

Update the record in the database and import a new migration script. It will contain the following logic or equivalent: 

UPDATE component_versions SET version = 3 WHERE component = M'customers'

Known Issues

Programmable objects do not work well with component versioning and should be avoided. 

• If any custom transaction handling is needed, it is near impossible to group programmable object changes into the same transaction as changes in migration scripts.
  They will by default run in the same transaction as the last migration, but where transaction customization is needed in the migration scripts this can’t be guaranteed to be correct. 

• You cannot deploy iteratively (i.e. there can be no intermediate steps during deployment, as we always just delete the object and replace it with the latest version after all the migrations have run). This may be needed to ensure backwards compatibility. 

• Combining the programmable objects feature and component versioning magnifies the dependency ordering issue between programmable and non-programmable objects.