Updating MSSQL Database

Updating MSSQL Database

At first, please ensure that all app pools are stopped and that there are no other connections to the relevant database in subject (e.g. from TOAD-s etc.). Then, make a full backup of the corresponding aqua database.
 
For upgrading your database, the attached SQL file (Diff-"your version"-to-"target version".sql) must be applied according to one of the following methods:
-     Using SQL Server Management Studio
-     Using sqlcmd command in batch mode (recommended)
 
Again: Please make sure to perform a backup before you apply the diff (in case of a fail you might not be able to revert easily). We recommend updating an environment for acceptance tests first before you go to production.
 

Updating Database in SQL Server Management Studio

Open SQL Server Management Studio (or your favorite DB management tool) and apply the diff-file named Diff-"your version"-to-"target version".sql as an admin (the aqua user might not have the appropriate rights). Additionally, the appropriate user account requires the standard schema "dbo".
 
Important Note:
Do not apply the diff during a transaction since it will fail with the following error:
Msg 574, Level 16, State 0, Line 3
CREATE FULLTEXT INDEX statement cannot be used during a user transaction.
Msg 574, Level 16, State 0, Line 4
ALTER FULLTEXT INDEX statement cannot be used during a user transaction.
 

Updating Database in Batch Mode

1.     Copy the Diff-"your version"-to-"target version".sql file to the temporary folder
2.     Open the command prompt window and change the current directory to the temporary folder
3.     Execute sqlcmd command as follows:
 
sqlcmd -e -b -k -I -w 65535 -W -m-1 -S localhost\SQLEXPRESS -U admin -P adminpassword -d aqua -i Diff-MSSQL-"your version"-to-"target version".sql -o diff.log
 
Parameters that might require adjustment:
localhost\SQLEXPRESS is SQL server name
admin/adminpassword are credentials of admin user
aqua is name of database
 
4.     sqlcmd should finish without errors. In the case of any errors, please ask for support.

    • Related Articles

    • Connection to MSSQL Database

      In this chapter you will find detailed information on how to install and configure the MSSQL Database in order to connect your aqua server to the database. If you want to use an Oracle Database, please follow the installation steps described in the ...

    • Updating Oracle Database

      First, please ensure that the aqua app pools are stopped (server is not running) and that there are no other connections to the relevant database (e.g. from TOAD-s etc.). Then, make a full backup of the aqua database.   Then, in order to upgrade your ...

    • Updating Server

      Update the aqua Server by executing the installer package “aqua For IIS Installer.exe”.   After Updating to a major aqua version, e.g. from aqua 20.X to aqua 21.X, please copy the new license(s) to the following folder (in default installations): ...

    • Database (Rich Client)

      In order to connect a database agent to aqua, you have to go through the following steps. First, extract the database agent zip file and open the aquaAgentDatabaseGui.exe.config.   Here, enter the agentCode and the aquaServiceURL.             <add ...

    • Updating Hint Message

      After updating the aqua server, users can still work with old aqua client until they close it. When restarting the aqua client, the user receives an error message and a download URL for the new aqua version. You can configure this Download-URL ...