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 initial database linking to a source control repository

Description

Dedicated development model

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

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

It serves for storing all information about any changes made against the version 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 prior to the change.

  • 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.
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 in order 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.

Shared development model

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

Tables

  • Objects – is the main table that stores the 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 changes 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 Objects table and to write the information when the status of an object is changed
  • UpdateExcludedObjects – used to access the 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. In addition, it reads the information from the Policies table on each DDL change, and checks if an object state follows the rule from the specified policy
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.

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 ChangeLog table if the logging feature is disabled?

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

Q: Do I have to put framework objects in the same database that I am linking to the repository?

A: For the dedicated development model yes, since the add-in requires one DDL trigger and one system table to be created.

For the shared development model, it requires a separate database for all the objects (except for the DDL trigger in case the SQL Server version is 2005, 2008 or 2008 R2). Tables and stored procedure will be installed in another database but on the same server.

Q: Do I need to back up framework objects?

A: It is advisable if you want to be able to get back in the track after any problems that may occur on the server.

It is also advisable to backup, prior to each update of ApexSQL Source Control

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 any 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.