How to upgrade the SQL Server databases (2008 R2, 2012, 2014, 2016) to SQL Server 2017 Express Edition

This document will help you upgrade your SQL Server databases mentioned above to SQL Server 2017 Express Edition. Starting with version 2.23.0, Ginesys POS supports mandatory audit feature for compliance purposes. However, the same can be activated only if the SQL Server database server edition is 2017 or above. Please refer this guide if you need to upgrade your SQL Server database.

System Requirements

Before proceeding with the installation and migration, ensure your system meets the following minimum requirements:

Pre-Migration Steps

The following pre-migration steps need to be performed very carefully to avoid any data inconsistency errors that may arise after the upgrade.

It is important to know that the POS Store is constantly communicating with the HO Server, either via Data Sync (old) or the Live Sync (new) feature. Assuming that the sync frequency in Data Sync is 15 minutes, it means, that in Data Sync every 15 minutes the data is getting changed. In case of a Live Sync scenario, the sync frequency is 1 minute which cannot be changed. So in case of Live Sync, every minute the data is changed.

To keep the data backup consistent while you port your database, a special option has been introduced in the Database Backup utility, called the “Backup Mode”. Two values are supported here, firstly the “Regular Mode” and the “Migration Mode”. For porting your databases you need to choose the “Migration Mode” option and for regular nightly backups, choose the “Regular Option”. In case of the “Migration Mode”, all the synchronization services are kept in “paused” state, so that no communication happens with the HO and the data backup remains consistent with the data at HO.

It is explained below, how to take a “Migration Mode” backup for database upgrade.

Backup the current database in “Migration Mode”:

1. Open the DbBackup.exe from POS installation folder and run the same in Run as administrator mode.

2. Against the “Backup Mode” option, select the “Migration” option.

3. Execute the utility and take the Backup.
   
   

   Open

   

    

Download SQL Server 2017 Express:

Download link for SQL Server 2017 Express edition from https://www.microsoft.com/en-us/download/details.aspx?id=55994

Click on “Download” and save the installer in our local hard drive.

Installing SQL Server 2017 Express

Please follow the screenshots to understand the selection to be made stepwise -

Post-Installation Steps

1. Restore Databases:

   • Open DBRestore.exe from our POS installation folder and run this utility as Run as administrator mode

   • Select the folder path where the backup file was previously saved.

You may encounter an error message if the default instances name of the old and new databases differ. Regardless of whether you receive the error message, it is essential to proceed with the next step.

Open

Go to Database Connections section of the Restore utility and put the correct credential and click on Test button.

Open

1. Configure other necessary fields and click the Restore button.

2. After restoration, a pop-up will prompt you to configure the database connection settings for the POS system. Follow the steps carefully to complete the configuration.

3. Update Compatibility Level:

   • Right-click on the restored database.

   • Select Properties > Options.

   • Set the Compatibility level to SQL Server 2017 (140).

4. Test the Database:

   • Run a basic SELECT query to ensure that the database functions correctly in the new environment.

5. Validation and Finalization

6. Verify SQL Server Version:

   • Execute the following query in SSMS to ensure that SQL Server 2017 Express is running:

ü  SELECT @@VERSION;

2. Check Event Logs:

   • Review the SQL Server error logs and Windows event logs to ensure there are no issues after the migration.

3. Document the Process:

   • Keep a record of the migration steps, any issues encountered, and their resolutions for future reference.