ApexSQL Source Control framework objects inventory

Applies to

ApexSQL Source Control

Summary

This article gives an overview of additional database objects (called framework objects) that ApexSQL Source Control creates on the initial database linking to a source control repository

Description

Dedicated development model

When linking a database using the dedicated development model, the following framework objects will be created:

  • ApexSQL_SourceControl_DatabaseLog – system table created in a database that is selected to be linked to source control

It serves to store all information about any change made against the source-controlled database objects. When any DDL change occurs in a database, the add-in will write the information in this table, including when/who made a change, against what object, and a T-SQL statement executed before the change:

ApexSQL_SourceControl_DatabaseLog system table that is created in linked database

  • ApexSQL_SourceControl_DDLTrigger – DDL trigger that is also created in a database selected to be linked, and that is used to catch all DDL changes that will be written in the ApexSQL_SourceControl_DatabaseLog table:

ApexSQL_SourceControl_DDLTrigger

Quick tip icon

Quick tip:

The abovementioned table and database DDL trigger will be created in each database that is being linked using the dedicated model. This applies to all supported SQL Server versions

Quick tip icon

Quick tip:

Minimum permissions required for the user against a linked database to use the add-in in the dedicated model are: ALTER ANY DATABASE DDL TRIGGER, SELECT, INSERT, EXECUTE, UPDATE, CONNECT, CREATE TABLE, ALTER permission against dbo schema

Minimum permiissions required for the user against a linked database

Shared development model

When linking a database using the shared development model, a separate database needs to be used for storing the following framework objects:

Tables

  • Objects – is the main table that stores information about all database objects including the status of an object (edited, checked out, or locked). The information stored in this table is used to update the object status icon in the Object Explorer pane
  • Policies – stores the information about active database policies and the logging feature
  • ChangeLog – stores the information about any change made against the database objects while the logging feature is enabled
  • UserMappings – stores the information about users who linked the database. When the user unlinks the database, the information for the corresponding user will be removed from the table
  • ExcludedObjects – stores the information about objects that are excluded in the add-in options, under the Global settings tab. Such objects are not affected by the database policy settings and will not be version controlled

Stored procedures

  • UpdateObjects – used to access the ApexSQL_SourceControl.Objects table and to write the information when the status of an object is changed
  • UpdateExcludedObjects – used to access the ApexSQL_SourceControl.ExcludedObjects table and to write the information about objects excluded in the Global settings tab, in the add-in options

Server level DDL trigger

  • ApexSQL_SourceControl_DDLTrigger – DDL trigger on a Server level that is used to catch all DDL changes that will be written in the corresponding table. Also, it reads the information from the ApexSQL_SourceControl.Policies table on each DDL change, and checks if an object state follows the rule from the specified policy:

Framework objects for the shared development model

Quick tip icon

Quick tip:

If the SQL Server version is older than 2012 (2008, 2008R2, 2005), instead of a server level DDL trigger, a separate database DDL trigger is created in each linked database

FAQs

Q: For how long the data is stored in the ApexSQL_SourceControl_DatabaseLog table?

A: The data in the ApexSQL_SourceControl_DatabaseLog table is stored until the changes are committed to source control, or until the repository is synced with the local database.

Q: Will information about database changes be written in the ApexSQL_SourceControl_ChangeLog table if the logging feature is disabled?

A: No, the information will not be written when the logging feature is disabled.

Q: If the Permissive or Restrictive policy is set, what will happen if I try to edit an object without checking it out, or locking respectively?

A: The DDL trigger will check what policy is set, and if the object status is properly set (Check out for Permissive policy and Lock for Restrictive policy). If the corresponding conditions are not met, you will get warning messages and the change will not be applied.

Q: Is there any performance impact of the framework objects on the database?

A: No, there is no performance impact on the database.

Q: Will uninstalling ApexSQL Source Control uninstall all framework objects? If not, what is the script to delete all of them?

A: Uninstalling ApexSQL Source Control will not delete any of the framework objects. You will have either to unlink the database from a local machine (in case of using the dedicated model) or all the users who linked a database in the shared model need to unlink, before uninstalling. The following scripts can be used to manually remove framework objects:

Quick tip icon

Quick tip:

Please be careful when removing framework objects. For the shared development model especially, if someone else linked any other database from the same server, it is strongly recommended not to delete them

/*DEDICATED DEVELOPMENT MODEL
Instead of linked_db, specify a name of a database 
linked using the dedicated model*/
USE linked_db
GO
/*for Azure SQL database 
execute just the following lines without the USE statement*/
DROP TRIGGER ApexSQL_SourceControl_DDLTrigger ON DATABASE;
DROP TABLE ApexSQL_SourceControl_DatabaseLog;


/*SHARED DEVELOPMENT MODEL

1.DROPPING DDL trigger
  1.1 If the version of SQL Server is: 
      2005, 2008 or 2008R2
Instead of linked_db, specify a name of a database linked using the shared model*/
USE linked_db
GO
/*for Azure SQL database 
execute just the following lines without the USE statement*/

DROP TRIGGER ApexSQL_SourceControl_DDLTrigger ON DATABASE;
GO
/* 1.2 If the version of SQL Server is:
      2012, 2014, 2016, vNext*/
USE master
GO

DROP TRIGGER ApexSQL_SourceControl_DDLTrigger ON ALL SERVER;
GO
/*2. DROPPING other framework objects

Instead of ApexSQL, specify a name of a database used to host additional objects (aka framework objects) created by ApexSQL Source Control. Leave ApexSQL database name if that one is used */
USE ApexSQL
GO

IF OBJECT_ID('UpdateExcludedObjects') IS NOT NULL
  DROP PROCEDURE ApexSQL_SourceControl.UpdateExcludedObjects;
IF OBJECT_ID('UpdateObjects') IS NOT NULL
  DROP PROCEDURE ApexSQL_SourceControl.UpdateObjects;
IF OBJECT_ID('ChangeLog') IS NOT NULL
  DROP TABLE ApexSQL_SourceControl.ChangeLog;
IF OBJECT_ID('ExcludedObjects') IS NOT NULL
  DROP TABLE ApexSQL_SourceControl.ExcludedObjects;
IF OBJECT_ID('Objects') IS NOT NULL
  DROP TABLE ApexSQL_SourceControl.Objects;
IF OBJECT_ID('Policies') IS NOT NULL
  DROP TABLE ApexSQL_SourceControl.Policies;
IF OBJECT_ID('UserMappings') IS NOT NULL
  DROP TABLE ApexSQL_SourceControl.UserMappings;

/* When using Azure SQL database only DROP statements should be used */
DROP PROCEDURE ApexSQL_SourceControl.UpdateExcludedObjects;
DROP PROCEDURE ApexSQL_SourceControl.UpdateObjects;
DROP TABLE ApexSQL_SourceControl.ChangeLog;
DROP TABLE ApexSQL_SourceControl.ExcludedObjects;
DROP TABLE ApexSQL_SourceControl.Objects;
DROP TABLE ApexSQL_SourceControl.Policies;
DROP TABLE ApexSQL_SourceControl.UserMappings;

Quick tip icon

Quick tip:

In case you want to delete the entire database that keeps the framework objects, be careful that it is a database dedicated just for framework objects and that it does not contain any other information