Search

Advanced MS SQL Instructions

Anthony - Versasec Support
Anthony - Versasec Support
  • Updated

Introduction

This article will describe how to handle scenarios where MS SQL is used as the database for vSEC:CMS. This article will describe how you can perform the following:

  • Determine curret database in use
  • Move the current MS SQL database connection to a new MS SQL database
  • Recover system from MS SQL
  • Flush trigger table in MS SQL.
Important
Before performing any of the operations described in this article it is required at minimum to be already running version 5.8 or higher of vSEC:CMS. Therefore you will need to follow the instructions in the article Update vSEC:CMS Components and look in the section Updating on Server Side first before continuing with this article.

Determine Current Database

Before making any changes it is important to determine the current database in use.

1. RDP to the server where vSEC:CMS is installed and from the Windows services console stop the vSEC:CMS Service. If you have other services running you will be prompted to stop them as well. Select Yes in this case.

Important
If for whatever reason the CMS services don’t stop gracefully it will be necessary to manually stop the services. This can be done from Windows Task Manager. Open Windows Task Manager and from the Details tab select the following services if they are still running and click the End task button:
  • AdmServer.exe – this is the vSEC:CMS Operator Console Service
  • ApiServer.exe – this is the vSEC:CMS API Service
  • RsdmServer.exe – this is the vSEC:CMS RSDM Service
  • UssServer.exe – this is the vSEC:CMS User Self Service
  • CmsServer.exe – this is the vSEC:CMS Service

2. Open a command Window as administrator.

3. Change to the root location where vSEC:CMS is installed, typically here C:\Program Files\Versasec\vSEC_CMS S-Series (for 64-bit version) or here C:\Program Files (x86)\Versasec\vSEC_CMS S-Series (for 32-bit version).

Command
> CmsService.exe -status

A dialog will appear shortly; if your database is large, this may take a few minutes.

The dialog displays your system's database settings, highlighting the:

  • Internal MS SQL template connector ID used by vSEC:CMS
  • Database server name
  • Configured ODBC connector
  • Database name.

Recommendation: Record this information for future reference.

Untitled.png

Move Current MS SQL to new MS SQL

In this section we will describe how to reconfigure your MS SQL connection when the current MS SQL server is moved to a new MS SQL server.

Important
Before performing any of the operations in this section it is highly recommended to make a backup copy of the current installed version of the vSEC:CMS before performing the update. Copy the folder that the current version is installed in. For example, if the current version is installed in C:\Program Files\Versasec\vSEC_CMS S-Series for 64-bit version, or C:\Program Files (x86)\Versasec\vSEC_CMS S-Series for 32-bit system, stop the vSEC:CMS Windows service (named vSEC:CMS Service) and make a backup copy of this folder on your system. It will be necessary to change the permissions on the dat folder, which is located in the root of the currently installed version, to allow the currently logged on Windows user access to this folder.

Additionally, it is important to make a backup of the current SQL table on your SQL server before performing any operations below.

1. RDP to the server where vSEC:CMS is installed and from the Windows services console stop the vSEC:CMS Service. If you have other services running you will be prompted to stop them as well. Select Yes in this case.

Important
If for whatever reason the CMS services don’t stop gracefully it will be necessary to manually stop the services. This can be done from Windows Task Manager. Open Windows Task Manager and from the Details tab select the following services if they are still running and click the End task button:
  • AdmServer.exe – this is the vSEC:CMS Operator Console Service
  • ApiServer.exe – this is the vSEC:CMS API Service
  • RsdmServer.exe – this is the vSEC:CMS RSDM Service
  • UssServer.exe – this is the vSEC:CMS User Self Service
  • CmsServer.exe – this is the vSEC:CMS Service

2. Open a command Window as administrator.

3. Change to the root location where vSEC:CMS is installed, typically here C:\Program Files\Versasec\vSEC_CMS S-Series (for 64-bit version) or here C:\Program Files (x86)\Versasec\vSEC_CMS S-Series (for 32-bit version).

Command
> CmsService.exe -configure -force

This will result in a dialog appearing where the new MS SQL connection can be configured. The dialog will be similar to below. Configure the new MS SQL connection details as applicable and click the Test button to ensure connectivity. Click Ok to save and close. The new database will now be used.

Note
When you enter the SQL details a dialog will show informing you to start the cms service. DO NOT START THE CMS SERVICE AT THIS TIME.

4. Finally you should create a new database guid after making these changes. Run the below MS SQL query to perform this:

UPDATE [_dboptions] SET [optionvalue_bin] = convert(binary(16), newid()) 
WHERE [optionkey] = 'dbguid'

Recover System from MS SQL

There may be scenarios where vSEC:CMS needs to be completely rebuilt from MS SQL. For example, for whatever reason it is not possible to start the vSEC:CMS operator console because the local database file is corrupted. Follow the instructions here to completely rebuild the system in this scenario.

1. RDP to the server where vSEC:CMS is installed and from the Windows services console stop the vSEC:CMS Service. If you have other services running you will be prompted to stop them as well. Select Yes in this case.

Important
If for whatever reason the CMS services don’t stop gracefully it will be necessary to manually stop the services. This can be done from Windows Task Manager. Open Windows Task Manager and from the Details tab select the following services if they are still running and click the End task button:
  • AdmServer.exe – this is the vSEC:CMS Operator Console Service
  • ApiServer.exe – this is the vSEC:CMS API Service
  • RsdmServer.exe – this is the vSEC:CMS RSDM Service
  • UssServer.exe – this is the vSEC:CMS User Self Service
  • CmsServer.exe – this is the vSEC:CMS Service

2. Open file explorer and navigate to the location where vSEC:CMS is installed, typically here C:\Program Files\Versasec\vSEC_CMS S-Series (for 64-bit version) or here C:\Program Files (x86)\Versasec\vSEC_CMS S-Series (for 32-bit version). You will see a folder named dat. Go into this folder and delete the file named OperatorTool_Audit.sqlite.

3. Open a command Window as administrator.

4. Change to the root location where vSEC:CMS was installed, typically here C:\Program Files\Versasec\vSEC_CMS S-Series (for 64-bit version) or here C:\Program Files (x86)\Versasec\vSEC_CMS S-Series (for 32-bit version).

Command
> CmsService.exe -configure -force

This will result in a dialog appearing where the MS SQL connection can be configured. The dialog will be similar to below. Configure the MS SQL connection details as applicable and click the Test button to ensure connectivity. Click Ok to save and close.

Untitled.png

You should see a dialog informing you to start the vSEC:CMS service. Start the service and log into the console as normal. The system should now be fully functional as before.

5. Finally you should create a new database guid after making these changes. Run the below MS SQL query to perform this:

UPDATE [_dboptions] SET [optionvalue_bin] = convert(binary(16), newid()) 
WHERE [optionkey] = 'dbguid

Flush Trigger Table

When MS SQL is used as the database for vSEC:CMS there my be occasions when the trigger table _updatelog is too large in size and needs to be flushed. When the table is too large a warning will be shown in System Health. This can have an impact in general performance of the system thereby requiring the table to be flushed. Follow the instructions below to flush the table.

  1. Stop vSEC:CMS services.
  2. Run the SQL query:
DELETE FROM [_updatelog]
  1. Generate new GUID with this SQL query:
UPDATE [_dboptions] SET [optionvalue_bin] = convert(binary(16), newid()) 
WHERE [optionkey] = 'dbguid'
  1. Start vSEC:CMS services.