Documentation

Skip to main content
How Can We Help?
< All Topics
Print

Installing the Database

Posted
Updated

Database Restore Preparation

By the time HR Manage is installed on your server, we would most likely have done a lot of work already on the database in preparation for going live. So we’ll provide you with a backup of the databases that need to be restored on your SQL Server, rather than a script that creates empty ones.

An HR Manage installation consists of two or more databases:

  • The HR Manage database, usually named HR_CompanyName. This holds all the HR data, configuration and logs.
  • One or more Files databases, usually named HR_CompanyNameFiles1, HR_CompanyNameFiles2 and so on. These hold the contents of attachments, generated documents and employee pictures, which keeps the main database small and fast. The HR Manage database records in which Files database each file is stored, so every Files database in the backup set must be restored, not just the latest one. More Files databases can be added later as attachments accumulate (see External Files Databases under IT Configuration).

SQL Server requirements

  • SQL Server 2017 or later. The databases run at compatibility level 130 or higher; they are supplied at the correct level, so don’t lower it after the restore. Any edition can be used. SQL Server Express is possible for small installations, keeping in mind its 10 GB limit per database. Using SQL Express 2025 will increase that limit to 50 GB per database.
  • Mixed mode authentication (SQL Server and Windows authentication) must be enabled on the instance, since the HR Manage Server application connects with a SQL login.
  • No additional features are required: no SQL Server Agent jobs, CLR, FILESTREAM, Full-Text Search or linked servers. All scheduled work runs inside the HR Manage Server application.
  • Collation: the databases are supplied with their own collation and keep it when restored; the instance collation doesn’t have to match.
  • If the HR Manage Server application is installed on a different server from SQL Server (not recommended, see the Introduction), TCP/IP must be enabled on the instance and the SQL Server port opened on the firewall. For a named instance, the SQL Server Browser service must also be running.

Database Installation / Restore

Step 1: Create the SQL logins

Create the two SQL logins listed under Requirements above, for example HRManageUser for the HR Manage database and HRManageFiles for the Files databases. In SQL Server Management Studio, expand Security › Logins, right-click and choose New Login…, select SQL Server authentication, specify a strong password and untick Enforce password expiration. Don’t map any databases yet; that is done after the restore in step 3. The same can be done with a script:

CREATE LOGIN [HRManageUser]  WITH PASSWORD = N'…', CHECK_EXPIRATION = OFF, CHECK_POLICY = ON;
CREATE LOGIN [HRManageFiles] WITH PASSWORD = N'…', CHECK_EXPIRATION = OFF, CHECK_POLICY = ON;

Keep the passwords safe: the first one is captured (and stored encrypted) in the HR Manage Server configuration, and the second one in the connection details of each Files database inside HR Manage.

Step 2: Restore the databases

In SQL Server Management Studio, right-click on the Databases node and choose Restore Database…. Select Device, add the backup file we provided, and check that the Destination database name is correct (use the names we provided, as the Files databases are referenced by name from the HR Manage database). On the Files page, tick Relocate all files to folder if the data and log files must go to a different location than the default on your server. Leave the Options page on RESTORE WITH RECOVERY. Click OK and repeat for each Files database.

Alternatively, restore with a script, adjusting the names and paths:

RESTORE FILELISTONLY FROM DISK = N'D:\Backups\HR_Company.bak';   -- shows the logical file names to use below

RESTORE DATABASE [HR_Company]
   FROM DISK = N'D:\Backups\HR_Company.bak'
   WITH MOVE N'HR_Company'     TO N'D:\SQLData\HR_Company.mdf',
        MOVE N'HR_Company_log' TO N'D:\SQLData\HR_Company_log.ldf',
        RECOVERY, STATS = 10;

RESTORE DATABASE [HR_CompanyFiles1]
   FROM DISK = N'D:\Backups\HR_CompanyFiles1.bak'
   WITH MOVE N'HR_CompanyFiles1'     TO N'D:\SQLData\HR_CompanyFiles1.mdf',
        MOVE N'HR_CompanyFiles1_log' TO N'D:\SQLData\HR_CompanyFiles1_log.ldf',
        RECOVERY, STATS = 10;

After the restore, set the owner of each database according to your own policy (for example ALTER AUTHORIZATION ON DATABASE::[HR_Company] TO [sa];) and set the recovery model to match your backup strategy. HR Manage has no special requirements in this regard.

Step 3: Link the logins to the databases

The restored databases contain the database users that were used in our environment and a restore leaves such users orphaned: the user exists inside the database but isn’t linked to a login on your server. If you simply map your new login to the database under User Mapping, SQL Server will refuse because a user with that name already exists. Run the following in each database instead. It links the existing user to your login, or creates the user if the names differ and makes it the database owner (db_owner), which is the access the HR Manage Server application requires:

USE [HR_Company];
GO
IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'HRManageUser')
    ALTER USER [HRManageUser] WITH LOGIN = [HRManageUser];
ELSE
    CREATE USER [HRManageUser] FOR LOGIN [HRManageUser];
GO
ALTER ROLE [db_owner] ADD MEMBER [HRManageUser];
GO

USE [HR_CompanyFiles1];
GO
IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'HRManageFiles')
    ALTER USER [HRManageFiles] WITH LOGIN = [HRManageFiles];
ELSE
    CREATE USER [HRManageFiles] FOR LOGIN [HRManageFiles];
GO
ALTER ROLE [db_owner] ADD MEMBER [HRManageFiles];
GO

Repeat the second block for every Files database. If we created the database users with different names than your logins, we’ll tell you which names to use in the ALTER USER statements. To check that no orphaned users remain in a database, run:

SELECT dp.name AS OrphanedUser
FROM   sys.database_principals dp
       LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE  dp.type = 'S' AND dp.authentication_type = 1 AND sp.sid IS NULL;

A database with an orphaned user is the most common cause of the HR Manage services failing to start after a restore. The symptom in the HR Manage Server log is Cannot open database “HR_Company” requested by the login. The login failed. Login failed for user ‘HRManageUser’, even though the login itself works, for example in SQL Server Management Studio.

Step 4: Update the Files database connections

The HR Manage database stores the connection details of each Files database and after a restore these still point to the server and login that were used in our environment. Once the HR Manage Server application has been installed and configured (see Server Configuration) and the services are running, log into HR Manage with an IT Administrator account, open System Configuration › IT Configuration › External Databases and update the server name, login and password of each Files database to your own. HR Manage tests each connection when it is saved. See External Files Databases under IT Configuration for details.

Step 5: Test the connection

The HR Manage Server Configuration tool (installed with the server application and covered in the next article) has a Test Connection button on its Database page. Use it after capturing the SQL Server instance, the HR Manage login and the database name: a successful test confirms that the login, its password and its access to the restored database are all in order.

Backups and copies

  • HR Manage doesn’t back up its databases itself. Include the HR Manage database and all Files databases in your SQL Server backup plan and back them up at the same time, since the HR Manage database references the files stored in the Files databases.
  • Always take a backup of all the databases before an HR Manage upgrade, so that the upgrade can be rolled back together with the application folders.
  • If you restore a copy of your live databases as a test or training system, keep in mind that the copy contains the live e-mail settings and will send real e-mails (for example workflow reminders and escalations) as soon as its HR Manage services start. Contact us beforehand for the script that neutralises the e-mail and integration settings of a copied database.

Table of Contents