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 current status of database;
- Configure to use MS SQL for all vSEC:CMS data;
- Move the current MS SQL database connection to a new MS SQL database;
- Recover system from MS SQL.
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 Status
Before making any changes it is important to determine the current status of the database.
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.
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 the Windows user that vSEC:CMS service runs under.
3. Change to the root location where vSEC:CMS was installed, normally this is C:\Program Files (x86)\Versasec\vSEC_CMS S-Series for 32-bit installations or C:\Program Files\Versasec\vSEC_CMS S-Series for 64-bit installations.
> CmsService.exe -status
A dialog will popup after sometime. If your database is large this can take a few minutes to appear. If you see similar information reported as highlighted below then you know that your current system is using MS SQL for all vSEC:CMS data. The important information is where it says: SQL read cache status: SQLOnly and No tables excluded from read cache. This means that all data is stored in MS SQL.
If you don’t see above reported then it is recommended to follow the instructions in the Configure MS SQL for all vSEC:CMS Data section below.
Configure MS SQL for all vSEC:CMS Data
It is recommended to configure vSEC:CMS to use MS SQL for all data. This will mean that all vSEC:CMS data will be stored in MS SQL. Previously vSEC:CMS used to store a local cached version of the database and this could become problematic if the data was not synced for whatever reason. Using MS SQL for all data will ensure that the data on MS SQL will always be valid. Additionally, it will be simpler to restore the system if the server where vSEC:CMS is running should become unusable for whatever reason.
There will still be a local database in this scenario. The local database in this case should be viewed as a configuration file where the connection details about the MS SQL server are stored. No other data will be stored in this local database.
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 (x86)\Versasec\vSEC_CMS S-Series 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.
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 the Windows user that vSEC:CMS service runs under.
3. Change to the root location where vSEC:CMS was installed, normally this is C:\Program Files (x86)\Versasec\vSEC_CMS S-Series for 32-bit installations or C:\Program Files\Versasec\vSEC_CMS S-Series for 64-bit installations.
> CmsService.exe -status
A dialog will popup after sometime. If your database is large this can take a few minutes to appear. You should see something similar to below. The important information is where it says: SQL read cache status: ReadCache, WriteCache and No tables excluded from read cache. This means that a local cached database is also maintained.
4. The next step will be to switch off the connection to SQL. This is required as the most reliable data at this time is in the locally stored cached database. From the same command window run:
> CmsService.exe -turnsqloff
You should see a dialog similar to below informing you that the connection is now switched off.
5. We need to now migrate the locally stored cached database back to SQL.
Start the vSEC:CMS Windows service and log onto the console. Navigate to Options - Connections and open SQL Database. You should see the previous SQL connection and the status for active set to no as highlighted below. Click Delete to remove this connection template.
Click Add. Enter the connection details applicable to your environment. You can use the same SQL database as before if required as the migration process will simply overwrite the SQL database tables.
If you need further details on setting up a SQL connection refer to the document here.
When the migration process begins you should see all the checkboxes enabled as in the sample below. Depending on the size of your database the migration process can take some time. Click the Start button to begin the migration process.
This will complete the process.
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.
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 (x86)\Versasec\vSEC_CMS S-Series 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.
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 the Windows user that vSEC:CMS service runs under.
3. Change to the root location where vSEC:CMS was installed, normally this is C:\Program Files (x86)\Versasec\vSEC_CMS S-Series for 32-bit installations or C:\Program Files\Versasec\vSEC_CMS S-Series for 64-bit installations.
> CmsService.exe -configure
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.
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.
The current MS SQL database that vSEC:CMS uses needs to be offline/unavailable for the above command to function properly.
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.
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 was installed. This is typically C:\Program Files (x86)\Versasec\vSEC_CMS S-Series for 32-bit installations or C:\Program Files\Versasec\vSEC_CMS S-Series for 64-bit installations. 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 the Windows user that vSEC:CMS service runs under.
4. Change to the root location where vSEC:CMS was installed, normally this is C:\Program Files (x86)\Versasec\vSEC_CMS S-Series for 32-bit installations or C:\Program Files\Versasec\vSEC_CMS S-Series for 64-bit installations.
> 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.
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.
Restore System from MS SQL
There may be scenarios where vSEC:CMS needs to be restored from a backup of an older MS SQL database. From version 6.1.X it will be necessary to follow the instructions here to successfully restore the system in this case. The instructions below need to be applied otherwise all of the clients that connect to the vSEC:CMS will have out of date cache.
1. Shutdown vSEC:CMS service;
2. Restore the MS SQL database on the SQL server;
UPDATE [_dboptions] SET [optionvalue_bin] = convert(binary(16), newid()) WHERE [optionkey] = 'dbguid'
3. Start the vSEC:CMS services.
This will ensure that when all clients reconnect they will update their cache after the restore of the MS SQL database.