Applies to
ApexSQL Source Control
Summary
This article describes changes in the Shared model and the upgrade process from the previous version of ApexSQL Source Control to the 2017 version.
Description
In previous versions of ApexSQL Source Control (2016 and previous), the first user who linked a database using the Shared model was able to select a database to store additional objects (tables, stored procedure) aka framework objects or framework inventory, created by ApexSQL Source Control. This option was available in the System selection step of the Source control wizard, under the Shared option:
The information about each new user who linked the same database was added in already created framework table upon linking a database. In this way, the team choose where to keep framework database objects for each database that is linked using the shared model.
The new implementation (starting from version 2017) contains the following changes:
- A single database is used when the first user links a database using the Shared model or after the upgrade (in case there are already linked database in the shared model), containing all the framework objects under a new dedicated schema:
- The DDL trigger created previously by ApexSQL Source Control in each linked database will be dropped and replaced with a server level trigger
|
Quick tip: The Shared model implementation in the new version of ApexSQL Source Control is not backwards compatible. This means that if one user upgrades to the new version, the entire team will need to upgrade. |
|
Quick tip: Server level DDL trigger will be created in case the SQL Server version is 2012 or newer. For SQL Server 2005, 2008 and 2008 R2, database DDL trigger will be created in each linked database, instead of the server level DDL trigger. |
How to upgrade
Let’s check the upgrade process through the use case when the previous version of ApexSQL Source Control is installed and multiple databases are linked in the shared model.
In order to describe the upgrade process, two databases, AdventureWorks2014 and DevDB, are linked in the Shared model by two developers. AdventureWorks2014 database stores framework database objects itself, and for the DevDB database, framework inventory is stored in the ApexSQL_DB database.
Both databases have some changes that are not yet committed to the repository:
Some objects are being checked out, locked and edited by one of developers:
Please see the article Object status icons and visualization for a guide on source control overlay icons
The database policy option is set to Permissive, in the add-in options:
|
Quick tip: There is no need to perform any additional tasks prior to upgrade such as commit changes, saving options, or to keep the information about checked out, locked and edited objects. The only requirement is that all developers upgrade to the new version. |
After installing the new version of ApexSQL Source Control, and connecting to the SQL Server instance where at least one database is linked using the shared model with the previous version of ApexSQL Source Control, the following dialog appears:
|
Quick tip: The user who initiates the upgrade process will need to have CONTROL SERVER permission, for the purpose of creating a DDL trigger on a server level. |
The above shown dialog offers the user a couple of ways to specify a database to be used for additional objects aka framework objects, created by ApexSQL Source Control:
- By default, an option to create a new database called ApexSQL will be shown
-
The user can change this and specify any other name by typing it in the Database field e.g. SQLSourceControl:
- Or the user can choose an existing database on the same SQL Server for this purpose, from the database drop down list
|
Quick tip: The Database drop down list shows databases from a SQL Server instance that are not linked to source control. |
|
Quick tip: In case a database for framework objects is already in place, it can be re-selected during the upgrade process and there is no need to create/use another one. |
Once the upgrade is finished, refreshing the Object Explorer pane shows the following result:
This means that all databases (in this case both previously linked databases) remain linked. A database specified during the linking process will be used containing the framework objects for all linked databases.
The information about object statuses, pending changes, logged changes and database policies that are stored in the framework tables will be saved in the specified database, so there is no need to re-link any database prior to/after the upgrade process.
By checking the Object Explorer pane, all objects that are previously checked out, locked or edited appears as such:
The object status form will keep all the information about the object statuses:
Definitions
For the purposes of this article
Inventory database objects refer to the add-in specific database objects that make up the backend framework that ApexSQL Source Control requires to process and store information
The [ApexSQL] database is the database where the Inventory database objects reside. The default name provided for a new database is “ApexSQL” but this database can be any existing user database or a new one the user creates
FAQs
Q: Does the upgrade process affect databases linked in the dedicated model?
A: Yes, but changes in the dedicated model are just with framework objects rename.
Q: Does the upgrade process automatically remove the existing framework database objects?
A: Yes, this will happen during the upgrade process. When the [ApexSQL] database is created, it will be populated with the data from the existing framework inventory, and the original framework objects will be dropped after that.
Q: Will the upgrade process be performed in the same manner, when the framework database objects are stored in a linked database and in a separate database?
A: Yes, framework database objects will be transferred to the appropriate table in the same way for both scenarios.
Q: Will the upgrade process drop the separate database used to host the framework inventory?
A: No. The database must be dropped manually, and framework database objects will be removed, but not the database itself.
Q: What permissions are required for the new version of ApexSQL Source Control compared to the existing set of required permissions?
A: Check the Permissions in ApexSQL Source Control for a detailed explanation about minimum permissions required for the user, in order to link and version control databases.
Q: Do all developers need the CONTROL SERVER permission
A: No, just the first developer who will link a database, or the one where the upgrade process from the previous version will be performed.
Q: Do I need to have the same set of permissions for linked database as for the [ApexSQL] database?
A: No, the required set of permissions is related only to the [ApexSQL] database. Permissions that are about to be granted on a linked database depends on user needs only, and do not affect the [ApexSQL Source Control] database in any way.
Q: Will another ApexSQL database be created upon linking new databases on the same SQL Server?
A: No. The number of linked databases is not relevant. The [ApexSQL] database will be created if it does not exist, or in other words if there is no at least one database linked in the Shared model. If a new database is linked or a new user links any of the already linked databases, the information about that will be added to the existing [ApexSQL] database
Q: Can I have a single [ApexSQL] database for databases linked across multiple SQL servers?
A: No, one instance of the [ApexSQL] database must be created for linked databases on each SQL server instance.
Q: Can I have a database created on another SQL server to keep the framework database objects?
A: No, it is not possible to create a database on a separate instance of SQL server for this purpose.
Q: Is the [ApexSQL] database name a default one? Can I change it?
A: Yes, there is an option to choose the name for the [ApexSQL] database, as well as to use any of the existing databases, or to leave a default name – ApexSQL. However, once this is set, it cannot be changed later.
Q: Are there any user interface changes in regard to the new framework database objects database?
A: No, each option can be used in the exact same way as it was used in the previous version.
Q: Does the new implementation of the Shared model allow the user to perform regular maintenance tasks against a linked database without the need to relink it?
A: Yes, with the new implementation a database that is being linked to source control does not contain any additional objects and tasks such as database backup/restore, database replication and so on, can be performed without unlinking a database.
|
Quick tip: In case of working with SQL Server 2005, 2008 or 2008R2, a database DDL trigger should be disabled before any maintenance task, and enabled after the task is finished. |
