Introduction

This article is used when you are moving the QES databases to a new SQL server due to a hardware or software upgrade.   System administrators will want to first test the move and after users verify everything is working a restore of the original DB over top of the test DB can be performed to "go live".


  1. Build a new SQL Server
  2. Build a new application machine that will run QES
  3. Install QES and point config file at new SQL server
  4. Restore HZS_System and QES DB to new SQL Server
  5. Fix logins
  6. Fix Connection strings
  7. Verify pointing at new SQL Server
  8. Have users test
  9. Restore new version of QES DB from old SQL server to New SQL server (do not restore HZS_System)
  10. Take old HZS_System and QES DB offline or delete the DB's
  11. Go live

Step 1 Backup

Backup all databases on server that you will and verify that the backups are valid.

Step 2 Install QES

This installation of QES is not on the same machine as your original QES.   it must be on a different machine and eventually it will be pointing to a new SQL Server instance.  

When you install QES and the system asks for a server name enter in the name of the server. DO NOT USE (local).

If QES is already installed and does not currently connect to the new server required then go to the install location and edit the QES.exe.config file and change the server name and initial database to the correct values.

Do NOT start the QES application at this point.

<connectionStrings>
<add name="QES.Properties.Settings.DatabaseConnectionString" connectionString="Data Source=PRES04\QESQA;Initial Catalog=HZS_System;Integrated Security=False;" providerName="System.Data.SqlClient" />
</connectionStrings>

Step 2 Restore Databases

Restore HZS_System and company database(s) using SQL Server Management Studio (SSMS)

Step 3 Fix logins for newly restored DB

Fix up logins in SQL:

Run the following lines separately in both the HZS_System database as well as any company databases. 

The first line gives you a report of all the users that are not properly associated with a SQL Server account. The second line fixes them.

sp_change_users_login‘Report’
sp_change_users_login'Auto_Fix','HZS_User'

With newer versions of SQL the Auto_Fix is no longer used. Use the following command instead:

ALTER USER HZS_User WITH LOGIN = HZS_User

Step 4 Fix connection strings to company database

  • Navigate to the install location of QES - Default is C:\Program Files (x86)\Horizons International\QES
  • Right Click on the QES directory
  • Click properties
  • Click security tab
  • Click the edit button
  • Click on users (or an account you are logged in as)
  • Click Allow in the full control line.
  • Click OK
  • Click Ok 
  • Edit the QES.exe.config file with notepad or other text editor.
<userSettings>
<QES.Properties.Settings>
<setting name="FontSize" serializeAs="String">
<value>10</value>
</setting>
<setting name="ServerChange" serializeAs="String">
<value>0</value>
</setting>
<setting name="NewServerName" serializeAs="String">
<value>New</value>
</setting>
<setting name="OldServerName" serializeAs="String">
<value>Old</value>
</setting>
</QES.Properties.Settings>
</userSettings>
  • Change the ServerChange value from 0 to 1
  • Change the NewServerName setting from New to the name of your SQL Server instance where the QES backups were restored into.
  • Change the OldServerName setting from Old to the name of the server that the database was previously coming from.
  • Save and close the QES config file.
  • Launch QES. After logging in you MUST select the Horizons International company from the drop down.

When you run QES you will get the following message:  "DataSource=XXXXX;Initial Catalog=YYYYYY;...." 

This message is telling you, in the Data Source section, the name of the old server just in case you need it to update the configuration file above.

You will then get an error message saying the company cannot be connected to and then there will be a confirmation message saying that the connection string has been temporarily changed.

Open QES and go to the System Tab and then click On the “Company Registration” button

Use the drop down to open up all your companies and change the Server Instance field to the proper values.

Turn Off the ServerChange setting

Navigate to the install location of QES

Default is C:\Program Files (x86)\Horizons International\QES

Edit the QES.exe.config file with notepad or other text editor.

<userSettings>
<QES.Properties.Settings>
<setting name="FontSize" serializeAs="String">
<value>10</value>
</setting>
<setting name="ServerChange" serializeAs="String">
<value>1</value>
</setting>
<setting name="NewServerName" serializeAs="String">
<value>New</value>
</setting>
<setting name="OldServerName" serializeAs="String">
<value>Old</value>
</setting>
</QES.Properties.Settings>
</userSettings>

Change the ServerChange value from 1 to 0

Save and close file.

QES will now be using your new server location.

IMPORTANT:  Log into QES on that machine and open the System -> Company Registration window and select the/a company from the dropdown and when the server and database values show up confirm they are the NEW SQL server and not the old values.

 Take old HZS_System and QES DB offline or delete them from the old SQL Server to confirm that there are no "rogue" QES installations still pointing to the old SQL server.