-
Título
SQL inventory system upgrade in ApexSQL Source Control -
Descripción
Consider this article as a walkthrough for the upgrade process from the previous ApexSQL Source Control version to the 2021 version. -
Resolución
Working with ApexSQL Source Control assumes the database is linked to the repository in one of two development models, dedicated or shared. Depending on the selected development model additional objects will be created in the database that is being linked or in another database from SQL Server and SQL Server itself.
More about this can be found in the ApexSQL Source Control framework objects inventory article.
Dedicated development model
Working with the ApexSQL Source Control 2019 version or any other of the previous versions, during the linking of a database to the source control process, for dedicated development model, will be created the table and DDL trigger in the database that is being linked:
SQL Server and Amazon RDS for SQL Server framework objects in ApexSQL Source Control 2019
For Azure SQL Database, the framework object table will not be stored under the System Tables node, but under the Tables node:
Azure SQL Database framework objects in ApexSQL Source Control 2019
Since the 2021 version of ApexSQL Source Control brings slight changes in the SQL inventory system, when upgrading from any previous version, the framework objects will need to be upgraded as well. Let’s see how the SQL inventory system upgrade mechanism is working.
Opening SQL Server Management Studio after upgrading to the 2021 version of ApexSQL Source Control will not show any difference, but the first click on any linked database will change that.
User without all needed permissions
From the ApexSQL Source Control 2021 version on, linking a database in the dedicated development model will require one additional permission than in any of the previous add-in version, the Create function permission. If a user does not have the Create function permission on the database that has already been linked in the dedicated development model using any of the previous add-in versions, selection of that database in the Object Explorer panel will raise this message:
If the user does not have all the needed permissions to perform the SQL inventory system upgrade, it still can continue working on that database, but there will be a chance that the changes will not be preserved correctly. From this point on, the user will be able to perform the upgrade only if the missing permission is granted to it.
User with all needed permissions
If a user with all needed permission for working with the database linked in the dedicated development model, selects it in the Object Explorer panel, the following message will be raised:
The upgrade process will take a couple of seconds and after the upgrade process is done successfully, the following message will be shown, and the work can be continued:
Besides the upgrade of the already existing framework objects, a new framework object will be created, the scalar-valued function, under the Programmability – Functions – Scalar-valued Functions node. The SQL inventory system, after the upgrade, will look like this:
Azure SQL Database framework objects for the dedicated database after the upgrade in ApexSQL Source Control 2021
SQL Server and Amazon RDS for SQL Server framework objects the dedicated database after the upgrade in ApexSQL Source Control 2021
SQL inventory system upgrade for databases linked in the dedicated development model will be done per database. This means that the upgrade process will not be started unless the database is selected in the Object Explorer panel.
Shared development model
Based on the hosting server, SQL Server, Amazon SQL Database, or Amazon RDS for SQL Server, the SQL inventory system will be created either in the database that is being linked or in the chosen framework object database and on the Server:
Azure SQL Database or Amazon RDS for SQL Server framework objects for the shared database in Apex SQL Source Control 2019
SQL Server framework objects for the shared database in ApexSQL Source Control 2019
Since SQL Server databases linked in the shared development model share the same framework object database, the SQL inventory system upgrade will be done for all databases present on that SQL Server at once. Click on one database linked in the shared development model will start the upgrade process.
Things are slightly different when it comes to Azure SQL Database and Amazon RDS for SQL Server since framework objects are stored in the database that is linked in the shared development model. So, for those two, the upgrade process will be the same as for the databases linked in the dedicated development model, and it will be done per database when that database is selected in the Object Explorer panel.
The permissions needed for the upgrade process are the same permissions needed for linking a database in the shared development model with one exception. For Azure SQL Database and Amazon RDS for SQL Server additional permission that will be required is the Create function permission, since the new scalar-valued function will be created during the upgrade process.
User without all needed permissions – SQL Server
When a user who does not have the Control server permission on SQL Server selects a database linked in the shared development model with any of the previous add-in version, the following message will be raised:
Again, if the user does not have the Control server permission, work can be continued but with the chance of not correctly saved changes.
User without all needed permissions – Azure SQL Database and Amazon RDS for SQL Server
For Azure SQL Database and Amazon RDS for SQL Server, selecting a database linked in the shared development model by a user without the all needed permissions will raise the following message:
More about needed permissions for the shared development model for Azure SQL Database and Amazon RDS for SQL Server can be found in the following articles:
- Minimal permissions for Amazon RDS in database source control
- Minimal permissions for Azure SQL Database in database source control
User with all needed permissions – SQL Server
When the user with all needed upgrade permissions selects any of the databases linked in the shared development model from that SQL Server, the following message will be shown:
Click on the OK button will start the SQL inventory system upgrade process for all databases from that SQL Server linked in the shared development model.
After the upgrade process is done, the user will be notified with the following message:
User with all needed permissions – Azure SQL Database and Amazon RDS for SQL Server
Since the SQL inventory system upgrade is done per database for Azure SQL Database and Amazon RDS for SQL Server, selecting a database linked in the shared development model by a user with all needed permissions will raise the following message:
The next message will be shown after the SQL inventory system upgrade is done successfully:
Now, the SQL inventory system will look like this:
Azure SQL Database or Amazon RDS for SQL Server framework objects for the shared database after the upgrade in Apex SQL Source Control 2021
SQL Server framework objects the shared database after the upgrade in ApexSQL Source Control 2021
Link information upgrade
The link information upgrade will be done only for the databases linked to Azure DevOps Server or Azure DevOps Services. This upgrade process will be done per machine when any of these databases are selected in the Object Explorer panel.
After SQL Server Management Studio is opened and the SQL Server is connected in the Object Explorer panel, click on any database linked with any previous ApexSQL Source Control versions to Azure DevOps repository and the following progress bar will be shown:
- Note: This process will delete all workspaces created by ApexSQL Source Control in any of the previous versions and will create new ones for that machine and linked databases
When the upgrade process is done, and if everything went successfully the following message will be shown:
From this point on everything will be upgraded and the work can be continued.
Background files upgrade
After the ApexSQL Source Control 2021 version is installed, besides the framework objects upgrade, background files will be upgraded too as a part of the SQL inventory system upgrade.
With any of the previous version of ApexSQL Source Control, the following background files were created with the XML extension on this location C:\Users\%user%\AppData\Local\ApexSQL\ApexSQL Source Control:
- Options.xml – store all settings from the Options and Source control setup window
- MyDefaults.xml – store user options settings from the Options window tabs where the ApexSQL defaults, My defaults, and Save as my defaults buttons are present
- RecentConnection.xml – store information from the System login tab in the Source control setup window for the last successful connection to the repository
The first opening of the SSMS instance with integrated the ApexSQL Source Control 2021 version, will convert/upgrade those files from XML files to JSON files.
- Note: The RecentConnection.xml file will not be converted, but it will not be deleted as well. The RecentConnection.json file will be created after the first successful linking of a database to source control
Now, after the upgrade, the background files will look like this:
Q: Will XML files created by exporting the object filter will be converted to JSON as well?
A: No, the user-created XML files for the object filter will not be converted to JSON.
Q: Can I continue using the object filter XML files created with the older add-in versions with the ApexSQL Source Control 2021 version?
A: No, in ApexSQL Source Control 2021 version, only JSON type of files can be used. To create them, export the object filter again with the ApexSQL Source Control 2021 version.
Q: Will I be notified every time I select the database in the Object Explorer panel that the SQL inventory system needs to be upgraded?
A: No, the user without all needed permissions to perform the upgrade, will be notified only the first time when he/she selects the database in the Object Explorer panel in one SQL Server Management Studio session.
Q: Can I postpone the SQL inventory system upgrade process?
A: If you have all the needed permissions to perform the upgrade, you cannot postpone that process, but it will take only a couple of seconds.