Using the Database Upgrade Tool

The command-line Database Upgrade Tool can be used ahead of the Keyfactor Command software installation to bring the database up to the current version. This can be used to test database upgrades and resolve any issues before going forward with a full Keyfactor Command upgrade. If you have previously encountered upgrade issues or want to test the database upgrade step without risking downtime, this may be a good option for you. Run the database upgrade tool on a duplicate copy of the database without interrupting operations to confirm that the database upgrade will succeed. You can then upgrade the production database as part of the normal upgrade flow (without the database upgrade tool).

System Requirements

The Database Upgrade Tool can be used on any server with access to the SQL server hosting the Keyfactor Command database. It does not need to be run on the Keyfactor Command server.

The Database Upgrade Tool has the following requirements:

• Windows Server 2019 or greater.

• Microsoft .NET Runtime version 8.0 (x64). Version 8.0 is available for download from Microsoft:

  https://dotnet.microsoft.com/download/dotnet/8.0/runtime

  You need only the .NET Runtime (x64), not the ASP.NET Core Runtime or ASP.NET Core Hosting Bundle. At the above link, this would be the Download x64 option under the Run console apps heading.

  

  Figure 637: Select the Download x64 Option Under Run Console Apps

  You can use the following PowerShell command to check the .NET core version(s) installed on a server (if any):

  dotnet --list-runtimes

  Output from this command will look something like this if you have the correct 8.0 x64 version of the .NET Runtime installed (notice the path is in C:\Program Files, not C:\Program Files (x86), indicating this is the x64 version):

  Microsoft.NETCore.App 8.0.4 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]

Using the Database Upgrade Tool

The Database Upgrade Tool does not need to be installed.

To use the Database Upgrade Tool:

1. Copy the zip file to the server on which it will be used and unzip it to a temporary working directory.
2. In the temporary working directory locate the Database Upgrade Tool\Configuration\appsettings.json file and open this file for editing using a text editor.

3. Edit the SqlDirect and EFModel connection strings appropriately for your environment and upgrade plan.

   Important:  Although the database upgrade tool can be used directly on the production database, this will upgrade the production database and create a mismatch between the database and the installed version of Keyfactor Command until you install the upgraded software, interrupting access. Keyfactor recommends running the database upgrade tool against a duplicate copy of the database to assure no interruption of the production environment.
   Copy

   Windows Integrated Authentication Example

   {
     "NLogConfigFile": "Configuration/NLog_DatabaseUpgradeTool.config",
     "ConnectionStrings": {
       "SqlDirect": "Data Source=<SQL_MACHINE_FQDN>;Initial Catalog=<SQL_DB_NAME>;Integrated Security=True;Persist Security Info=True",
       "EFModels": "metadata=res://*/EFModels.csdl|res://*/EFModels.ssdl|res://*/EFModels.msl;provider=Microsoft.Data.SqlClient;provider connection string='Data Source=<SQL_MACHINE_FQDN>;Initial Catalog=<SQL_DB_NAME>;Integrated Security=True;Persist Security Info=True;Multiple Active Result Sets=True;Application Name=EntityFramework'"
     },
     "DBCommandTimeout": 360,
     "SqlRetryConfiguration": {
       "NumberOfTries": "5",
       "DeltaTime": "00:00:00.5",
       "MaxTimeInterval": "00:02:00"
     }
   }

   Copy

   SQL Authentication Example

   {
     "NLogConfigFile": "Configuration/NLog_DatabaseUpgradeTool.config",
     "ConnectionStrings": {
       "SqlDirect": "Data Source=<SQL_MACHINE_FQDN>;Initial Catalog=<SQL_DB_NAME>;User ID=<SQL_USER_NAME>;Password=<SQL_PASSWORD>;Integrated Security=False;Persist Security Info=True",
       "EFModels": "metadata=res://*/EFModels.csdl|res://*/EFModels.ssdl|res://*/EFModels.msl;provider=Microsoft.Data.SqlClient;provider connection string='Data Source=<SQL_MACHINE_FQDN>;Initial Catalog=<SQL_DB_NAME>;User ID=<SQL_USER_NAME>;Password=<SQL_PASSWORD>;Integrated Security=False;Persist Security Info=True;Multiple Active Result Sets=True;Application Name=EntityFramework'"
     },
     "DBCommandTimeout": 360,
     "SqlRetryConfiguration": {
       "NumberOfTries": "5",
       "DeltaTime": "00:00:00.5",
       "MaxTimeInterval": "00:02:00"
     }
   }

   Copy

   Windows Integrated Authentication Example for SQL Named instance

   {
     "NLogConfigFile": "Configuration/NLog_DatabaseUpgradeTool.config",
     "ConnectionStrings": {
       "SqlDirect": "Data Source=<SQL_MACHINE_FQDN>\\<INSTANCE_NAME>;Initial Catalog=<SQL_DB_NAME>;Integrated Security=True;Persist Security Info=True",
       "EFModels": "metadata=res://*/EFModels.csdl|res://*/EFModels.ssdl|res://*/EFModels.msl;provider=Microsoft.Data.SqlClient;provider connection string='Data Source=<SQL_MACHINE_FQDN>\\<INSTANCE_NAME>;Initial Catalog=<SQL_DB_NAME>;Integrated Security=True;Persist Security Info=True;Multiple Active Result Sets=True;Application Name=EntityFramework'"
     },
     "DBCommandTimeout": 360,
     "SqlRetryConfiguration": {
       "NumberOfTries": "5",
       "DeltaTime": "00:00:00.5",
       "MaxTimeInterval": "00:02:00"
     }
   }

4. If you’re upgrading a database in active use by Keyfactor Command, stop the services for IIS and Keyfactor Command (see Enable and Start the Keyfactor Command Service) on the Keyfactor Command server.
5. Open a command prompt and change to the temporary working directory where you copied the Database Upgrade Tool.

6. Execute the DatabaseUpgradeTool.exe. The tool has the following parameters:

   • update: Run the database upgrade.

   • check: Checks the database version to determine if an upgrade is required. If the database version is earlier than the Database Upgrade Tool version, the output indicates that an upgrade is needed and displays both versions. If the versions match, the output confirms that the database is compatible and no upgrade is required.

   You can also proceed with no parameter A parameter or argument is a value that is passed into a function in an application.. This is the same as providing the update parameter.

7. It will take a few moment for output to appear and then you will begin to see output as the upgrade tool processes through the upgrade modules that are needed for your database.

   Note:  The Database Upgrade Tool stops at the first error. All upgrade scripts are designed to be safely re-runnable, so re-executing them does not cause issues.

   The modules that will appear will vary depending on the starting version of your database, but output will look something like the following:

   2024-10-08 08:36:36.7603  Keyfactor.Command.DatabaseUpgradeTool.Wizard [Info] - Starting Configuration Wizard Console - Upgrade Only.
   2024-10-08 08:36:36.9278  Keyfactor.Command.DatabaseUpgradeTool.Wizard [Warn] - No valid DbCommandTimeout provided, defaulting to 1800 seconds.
   2024-10-08 08:36:37.2713  Keyfactor.Command.DatabaseUpgradeTool.Wizard [Info] - Running module Removed 'CSR Enabled' as an option within App Settings.. 0 of 130 complete
   2024-10-08 08:36:37.2713  Keyfactor.Command.DatabaseUpgradeTool.Wizard [Info] - Running module Increase Name, Display Name, and Default Value's column size to 4000 in table cms_agents.CertStoreTypeProperties.. 1 of 130 complete
   2024-10-08 08:36:37.3030  Keyfactor.Command.DatabaseUpgradeTool.Wizard [Info] - Running module Increase Name, Display Name, and Default Value's column size to 4000 in type CertStoreTypePropertyDetail.. 2 of 130 complete
   2024-10-08 08:36:37.3177  Keyfactor.Command.DatabaseUpgradeTool.Wizard [Info] - Running module Remove F5 certificate store types.. 3 of 130 complete
   2024-10-08 08:36:37.3745  Keyfactor.Command.DatabaseUpgradeTool.Wizard [Info] - Running module Regenerating audit key (~3 mins/1 million records). 4 of 130 complete
   2024-10-08 08:36:37.4624  CSS.CMS.Install.Upgrade.Modules.RegenerateAuditSigningKey [Info] - Successfully regenerated audit log signing key.
   2024-10-08 08:37:19.3760  CSS.CMS.Install.Upgrade.Modules.RegenerateAuditSigningKey [Info] - Successfully re-signed audit log entries
   2024-10-08 08:37:19.3931  Keyfactor.Command.DatabaseUpgradeTool.Wizard [Info] - Running module Add secret ID columns for deterministic Guid removal.. 5 of 130 complete
   2024-10-08 08:37:19.4215  Keyfactor.Command.DatabaseUpgradeTool.Wizard [Info] - Running module Migrate to non-deterministic Guids. 6 of 130 complete
   
   Section removed for brevity.
   
   2024-10-08 08:37:49.4083  Keyfactor.Command.DatabaseUpgradeTool.Wizard [Info] - Running module Adds InstanceGuid to Command API client secret secret. 127 of 130 complete
   2024-10-08 08:37:49.4083  Keyfactor.Command.DatabaseUpgradeTool.Wizard [Info] - Running module Rename EncodeAsPlusSigns to DeltaCRL column for revocation monitoring. 128 of 130 complete
   2024-10-08 08:37:49.4488  Keyfactor.Command.DatabaseUpgradeTool.Wizard [Info] - Running module Refresh all SQL modules (procedures, views, etc.). 129 of 130 complete
   2024-10-08 08:37:49.6618  Keyfactor.Command.DatabaseUpgradeTool.Wizard [Info] - Upgrade completed successfully.

8. Confirm that the upgrade completed successfully.

   Note:  If any issues are encountered, correct them in both the duplicate and production databases to ensure a smooth upgrade. The database upgrade tool can be run repeatedly against the same database—each individual upgrade script is re-runnable—so once an issue is fixed, simply run the tool again.
9. If you upgraded the production database, install the upgraded Keyfactor Command software (see Install Keyfactor Command on a Windows Server Under IIS).

Table 127: Appsetting.json File Parameters

Setting	Description
NLogConfigFile	The relative path and filename of the configuration file for NLog configuration. The default is: Configuration/NLog_DatabaseUpgradeTool.config
ConnectionStrings	SQL connection strings (see Configurable SQL Connection Strings for more information). Setting Description SqlDirect The SQL connection string using plain text values. EFModels The entity framework connection string using plain text values.
DBCommandTimeout	The maximum time (in seconds) the upgrade will wait for a SQL command to finish executing before it’s canceled and a timeout error is raised. This is a client-side setting and applies to query execution, stored procedures, and bulk operations, not to opening the connection.
SqlRetryConfiguration	SQL retry settings (seeKeyfactor Command Changing SQL Retry Settings for more information). Setting Description Number Of Tries The number of times a connection attempt will be made to SQL before an exception is thrown. The default is 5. Delta Time The preferred gap time to delay before the next attempt to connect to SQL will be made. The default is .5 (1/2) second. Max Time Interval The maximum time interval before the next attempt to connect to SQL will be made. The default is 2 minutes.