Applies to:
Summary
This article gives an overview of the SQL inventory system objects (called framework objects) that ApexSQL Source Control creates on the initial database linking to a source control repository
Description
To link a database to a source control repository based on the chosen Server, some additional permissions will be needed. More on the needed permissions can be found in the following articles:
- Minimum permissions required to work with ApexSQL Source Control
- Minimal permissions for Azure SQL Database in database source control
- Minimal permissions for Amazon RDS in database source control
Dedicated development model
When linking a database using the dedicated development model, the following SQL inventory system objects will be created in the linked database under the dbo schema for SQL Server and Amazon RDS for SQL Server:
-
ApexSQL_SourceControl_DatabaseLog – system table used 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_DDLTrigger – DDL trigger used to catch all DDL changes that will be written in the ApexSQL_SourceControl_DatabaseLog table:
-
ApexSQL_SourceControl_FrameworkVersion – a function used for upgrading the framework objects when needed:
|
Quick tip: The abovementioned table, function, 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 |
Shared development model
When linking a database using the shared development model, a separate database needs to be used for storing the following SQL inventory system objects under the ApexSQL_SourceControl schema:
Tables
- Objects – is the main table that stores information about all database objects including the status of an object (edited, checked out, or locked) as well as the user who changed the object status. 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 state of the Log changes option. More about this can be found in the Database development policies and How to use the Change log feature in ApexSQL Source Control articles
- ChangeLog – stores the information about any change made against the database objects while the Log changes option 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
Function
- ApexSQL_SourceControl_FrameworkVersion – a function used for upgrading the framework objects when needed
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:
|
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 |
Amazon RDS for SQL Server
Linking Amazon RDS for SQL Server to source control in the shared development model will store all SQL inventory system objects in the databases that are being linked:
Amazon RDS linked in the shared development model
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. More information on how to manually unlink a database linked in any of the development models can be found in the How to manually unlink a database from SQL source control article.
|
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 |
|
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 to framework objects and that it does not contain any other information |
Affected versions
The SQL inventory system explained in this article is implemented in ApexSQL Source Control 2021 version and higher. In the previous versions of ApexSQL Source Control, the scalar-valued function FrameworkVersion will not be created and corresponding permission is not needed.
