Skip to main content
Version: v2.7

Upgrading the Databases

The Master, Repository, Utility, Project and Migration databases are installed and upgraded with the database scripts in the dbscripts.zip download. One command upgrades every database of an installation, and the same command with -AllowCreate installs a new one: a new installation is described in Setting up the Master Server. The Portal, Item Manager and Test databases have their own setup scripts; see The Portal, Item Manager and Test Databases. A track's Staging database is neither: you create it, and Hopp maintains its schema, so an upgrade does nothing to it.

The release's deployment notes say which release your databases must be on before you upgrade.

Before You Start​

  • Back up every database.
  • Stop the Hopp services.
  • Use an account with the rights listed under Permissions.
  • Extract and unblock the download, as below.

Run every command below from the extracted folder, and pass the same database names and service accounts on every one (see Database names and Service accounts).

Unblock the download​

Extract the dbscripts.zip download and unblock the extracted files, from a PowerShell prompt in the extracted folder. The scripts are not signed, so PowerShell refuses to run them while Windows still marks them as downloaded from the internet:

Get-ChildItem -Recurse | Unblock-File

Upgrade an Installation​

  1. See what will run. This prints the exact command for each database and changes nothing:

    ./update-installation.ps1 -Server <server> -MasterDatabase MigFx_Master -RepositoryDb MigFx_Repository -UtilityDb MigFx_Utility -RuntimeAgentUser DOMAIN\svc-hopp -PortalUser DOMAIN\svc-hopp

    The command finds every Project database and every published track's Migration database through the Master database, so you do not list them. The examples use the names of an installation from before 2.7 and one shared service account; use your installation's own names and accounts (see Database names and Service accounts).

  2. Upgrade the databases. If your DBA runs the SQL rather than this command, see If Your DBA Runs the SQL instead:

    ./update-installation.ps1 -Server <server> -Mode Execute -BackupDir <path> -MasterDatabase MigFx_Master -RepositoryDb MigFx_Repository -UtilityDb MigFx_Utility -RuntimeAgentUser DOMAIN\svc-hopp -PortalUser DOMAIN\svc-hopp

    Before it changes anything, the command checks every database (it exists, you have the rights, the service-account logins exist) and lists every problem at once. A database that does not exist stops the run, so a wrong name cannot leave the real database behind. It then upgrades one database at a time, first backing up to -BackupDir each existing database it changes (a database with nothing to run is skipped), stops at the first failure, and prints a summary table.

    -BackupDir is a folder on the database server, since SQL Server writes the backup, not on the machine running the command. The same path is used on every database server the run reaches, so it must exist on each of them and be writable by its SQL Server service, or name a UNC share they can all write to.

  3. Check the result:

    ./update-installation.ps1 -Server <server> -Mode Status -MasterDatabase MigFx_Master -RepositoryDb MigFx_Repository -UtilityDb MigFx_Utility -RuntimeAgentUser DOMAIN\svc-hopp -PortalUser DOMAIN\svc-hopp

    It shows each database's release and whether it is up to date. Without the service accounts, an up-to-date database reads as not up to date.

  4. Upgrade the databases the command does not find, if you have any; see Databases the Command Does Not Find.

  5. Upgrade the Portal database, and the Item Manager and Test databases if you use those modules; see The Portal, Item Manager and Test Databases.

Add -LogDir <folder> to the upgrade command (or to update-database.ps1) to keep each database's output in a log file; the preview and the status check print to the console only.

If the Upgrade Stops Partway​

The summary shows which databases are upgraded (OK), which one failed (its own changes are rolled back), and which were not touched (NOT RUN). Keep the Hopp services stopped, then either:

  • fix the cause and run the same command again: upgraded databases have nothing left to do, or
  • restore the backups of every database and stay on the release you had.

If Your DBA Runs the SQL​

If your DBA reviews and runs every change to the database server, the commands can write the SQL to files instead of changing anything. The DBA reviews the files and runs them with sqlcmd, and the databases end up exactly as after the upgrade command, so later upgrades and the status check work as usual. This works for a new installation and for an upgrade.

Generate the files​

  1. For a new installation, create the Master and Repository databases first, empty (for instance Hopp_Master and Hopp_Repository). The files never create a database, and the command refuses -AllowCreate here.

  2. Generate the files, passing your installation's database names (for a new installation, the ones you created in step 1) and service accounts. Run it as a login that only reads (see Permissions). Nothing is changed and no backup is taken:

    ./update-installation.ps1 -Server <server> -Mode Execute -DryRun -LogDir <folder> -MasterDatabase MigFx_Master -RepositoryDb MigFx_Repository -UtilityDb MigFx_Utility -RuntimeAgentUser DOMAIN\svc-hopp -PortalUser DOMAIN\svc-hopp
  3. Check what it wrote in <folder>: a <server>_<database>.review.sql file per existing database, and README-sql-files.txt, which lists the order to run them in, the databases to create first and any service-account login that is missing.

A Master, Repository or Utility database the command cannot find stops it, since that is most likely a wrong name: pass the right one (-MasterDatabase, -RepositoryDb, -UtilityDb). A missing Project or Migration database is listed in README-sql-files.txt to create first; create it, then generate the files again. If the command refuses or stops, it says why and writes no README-sql-files.txt; fix the cause and generate the files again, and never run a file left in the folder from an earlier attempt.

Run the files​

  1. Review the files and back up the databases.

  2. Run each file in the order README-sql-files.txt gives, with sqlcmd, from the folder that holds the files (the -LogDir folder). The readme and each file's header give the exact command line, of this form:

    sqlcmd -S "<server>" -d "<database>" -b -f 65001 -i "<file>"

    sqlcmd uses Windows authentication; add -U <login> for a SQL login, and -C if the server uses a self-signed certificate. Use the ODBC sqlcmd (the Microsoft Command Line Utilities for SQL Server on Windows, mssql-tools18 on Linux); the newer Go-based sqlcmd (for instance installed with winget install sqlcmd) is not supported. SQL Server Management Studio cannot run the files: with SQLCMD mode or without, a file refuses to run there and changes nothing, since Management Studio does not stop it safely at an error. The login that runs the files needs the rights listed under Permissions, except dbcreator.

  3. Check each run. sqlcmd stops at the first error, with or without -b: it shows the error, exits with a nonzero code and keeps none of the scripts' changes. A successful run ends with Finished:, or Nothing to run: for a database that is up to date.

Stop at the first failure; after fixing its cause, run the same file again. If the database scripts or another file applied scripts to a database after its file was generated, the file refuses to run: generate it again.

Afterwards​

Once every file has run on a new installation, return to Setting up the Master Server for the remaining install steps. On an upgrade, continue with step 3 of Upgrade an Installation, checking the result with -Mode Status, and then steps 4 and 5. For a database the command does not find (see Databases the Command Does Not Find), generate its file with update-database.ps1 and -DryRun, which never creates the database, then run that file the same way:

./update-database.ps1 -Server <server> -Scripts MigrationDb -Database <database> -UtilityDb <utility database> -DryRun -LogDir <folder> -RuntimeAgentUser DOMAIN\svc-hopp -PortalUser DOMAIN\svc-hopp

Use -Scripts UtilityDb for a Utility database.

Pass the same service accounts, Utility name and -Environment on every later run, as with the commands: the files record the scripts with these values filled in, so a changed value runs those scripts again.

The Portal, Item Manager and Test databases are not covered: their setup scripts (see The Portal, Item Manager and Test Databases) are already SQL your DBA runs by hand.

Databases the Command Does Not Find​

update-installation.ps1 does not find:

  • an unpublished or expired track's Migration database: upgrade it before you republish the track;
  • the Utility database on a server that holds no published track's Migration database: upgrade it with -Scripts UtilityDb, before any track database on that server, since a track database refers to it.

A track's Staging database needs nothing, since Hopp maintains it.

Upgrade such a database on its own with update-database.ps1, for instance a Migration database:

./update-database.ps1 -Server <server> -Scripts MigrationDb -Database <database> -UtilityDb <utility database> -NoCreate -RuntimeAgentUser DOMAIN\svc-hopp -PortalUser DOMAIN\svc-hopp

-NoCreate makes a mistyped name fail instead of creating a new, empty database. The command applies exactly what that database is missing and records it. Statements run with no time limit by default; -CommandTimeout <seconds> sets one.

The Portal, Item Manager and Test Databases​

update-installation.ps1 does not upgrade these. Run their setup scripts from the same dbscripts.zip download:

DatabaseSetup scriptWhen
Portal (for instance MigFx_Portal)MigFx.PortalDb.Setup.sqlalways
Item Manager (for instance MigFx_ItemMgr)MigFx.ItemMgrDb.Setup.sqlif you use the Item Manager module
Test (for instance MigFx_Planning)MigFx.PlanningDb.Setup.sqlif you use the Test module

Each script is the one used for a fresh install: it applies only the changes the database does not have yet, so it is safe to run on an existing database. A database whose migration history is too old for the script is refused with a message saying to upgrade it to 2.6 first; do that, then reconnect (for instance open a new query window) before running the script again, since the refused connection skips every further statement.

Reference​

The download​

dbscripts.zip contains the database migration tool, one folder of database scripts per database, update-installation.ps1 for every database of an installation, update-database.ps1 for one database, START-HERE.txt, and the setup scripts for the Portal, Item Manager and Test databases.

update-installation.ps1​

Without -Mode, the command only prints what it would run.

You addWhat it does
nothingPrints the exact update-database.ps1 command for every database. Changes nothing.
-Mode ExecuteChecks every database first, then upgrades each in turn, stopping at the first failure, and prints a summary. Changes nothing when a check fails.
-Mode Execute -DryRunWrites a SQL file per existing database for your DBA to run, instead of changing anything (see If Your DBA Runs the SQL).
-Mode StatusShows each database's status, release and whether it is up to date. Exits with a nonzero code unless every database is up to date.
ParameterMeaning
-ServerThe server holding the Master database. The Repository database is expected on the same server.
-RuntimeAgentUser, -PortalUserThe service accounts; see Service accounts.
-BackupDirWhere each existing database is backed up before it is changed; a folder on every database server, or a UNC share.
-AllowCreateCreates a database that does not exist yet. Without it, a missing database stops the run. Not with -DryRun.
-MasterDatabase, -RepositoryDb, -UtilityDbThe names of the Master, Repository and Utility databases; see Database names.
-LogDirWith -Mode Execute, also writes each database's output to a log file in this folder; with -DryRun, where the SQL files go.
-EnvironmentAlso runs the scripts meant for this environment. Pass the same value on every run, -Mode Status included.
-CommandTimeoutA time limit in seconds for each statement; by default there is none.
-UsernameConnects as this SQL Server login instead of your Windows account, and prompts for its password. To run as another Windows account, start PowerShell as that account.

update-database.ps1​

Applies one database's scripts: -Scripts names the folder (MasterDb, MigrationDb, ProjectDb, RepositoryDb or UtilityDb) and -Database the database. It takes -RuntimeAgentUser, -PortalUser, -UtilityDb (for a Migration database), -BackupDir, -LogDir, -Environment, -Username, -CommandTimeout and -DryRun as above, plus -NoCreate.

When a database is created​

CommandCreates a missing database
update-installation.ps1 -Mode Executeonly with -AllowCreate
update-database.ps1yes, unless you add -NoCreate
either, with -DryRunnever: the SQL files never create a database either

A database the commands create takes the server's default collation and file placement. When you need a specific collation or file location, create the database yourself first; the commands then fill it.

Database names​

Pass the names your installation's Master, Repository and Utility databases have, on every command. An installation set up with this guide from 2.7 on uses Hopp_Master, Hopp_Repository and Hopp_Utility; an installation from before 2.7 typically uses MigFx_Master, MigFx_Repository and MigFx_Utility. An upgrade never renames a database. The Utility database has the same name on every server.

When you add a track or set up a project's databases in the Portal, it suggests Hopp_ names for the new databases (for instance Hopp_03_Migration, Hopp_03_Staging and Hopp_Project_<name>), also on an installation from before 2.7. Such an installation then has both namings side by side, which works. To keep the old naming, change the suggested name before you save.

Service accounts​

-RuntimeAgentUser names the account the Runtime Agent service runs as, and -PortalUser the account the Portal service runs as. They grant those accounts db_owner on each database the commands manage (Master, Repository, Utility, Project and Migration), so pass the same accounts whenever you apply database scripts. The two may be the same account: with one shared service account, pass it as both. A name without a backslash is a SQL Server login, which must exist before you run the command.

The commands grant nothing on the Portal, Item Manager, Test and Staging databases. Those grants are made by hand when the database is created, and an upgrade keeps them. When an upgrade moves a service to a different account, grant that account what the old one had (see Granting db_owner by hand): a new Portal account needs db_owner on the Portal, Item Manager and Test databases and db_datareader on every Staging database (db_owner if it is shared with the Runtime Agent), a new Runtime Agent account db_owner on every Staging database. Both also need the folder rights listed under Accounts.

With SQL Server authentication this differs; see Accounts.

Permissions​

The commands run as the installing account (see Accounts): your Windows account, or the SQL Server login you pass as -Username. It needs:

  • dbcreator on the server, unless you create every database yourself;
  • db_owner on each existing database;
  • securityadmin, only if -RuntimeAgentUser or -PortalUser names a Windows account that does not already have a server login.

Provisioning the account in Active Directory does not create that server login. If the operator does not have securityadmin, a DBA must create the Windows login in SQL Server first (CREATE LOGIN [DOMAIN\account] FROM WINDOWS), and the commands will not need securityadmin at all. The simplest choice, if there is no reason to lock the run down further, is a sysadmin-equivalent DBA account.

To generate SQL files with -DryRun, use a login that only reads: a user in each database with SELECT on its hopp_migrations schema once that exists (a database still on 2.6.16 has none yet), and for update-installation.ps1 also db_datareader on the Master database, which it reads the project and track lists from. db_datareader covers both. The db_owner, dbcreator and securityadmin rights do not apply to it. Give it no more: with a login that can write, a database script that changed after it ran gets an error recorded in that database's hopp_migrations tables, even with -DryRun.