Database setup and configuration - MS SQL

MS SQL Server

This section assumes that Microsoft SQL Server 2019 is already installed on DB host. If not, it should be installed before you can proceed with the instructions described below. Before installing you should check all hardware and software system requirements. For MS SQL Server 2019, follow this link:

SQL Server 2019: Hardware & software requirements - SQL Server | Microsoft Learn

1.Restoring the DB Dump on the DB server.

The DB Dump for MS SQL Server is located in the ‘keysurvey_database\keysurvey-mssql.dump' file in the Installation Package.

Restoring the Dump using GUI of MS SQL Management Studio.

For this, right-click on 'Databases' and choose 'Restore Database'. Then type the name of the database, choose 'From Device:' and browse for DB Dump. 'Files of type' should be selected as 'All types'

Once all these steps are done, click 'Ok'. Refresh MS SQL Object Explorer. 'keysurvey' database should now appear in database list.

2. Create a DB user and grant him all privileges on database.

	Select users from Security/Logins; Right click Logins;
	Enter Login Name: member (for example); Choose SQL Server authentication; Enter password: 123456 (this is just an example. Note: this should be a password that cannot be easily guessed and should be the same user and password that was configured before); Uncheck Enforce password policy; Default database: Change to keysurvey; Default language: <default> Go to User Mapping;
	Click on the keysurvey database in the list of databases; Check db_owner role (Note: Public is also checked) for this database; Check Logins to make sure these settings have been updated (for example, check that the member has the dbo role); Logout and login back as the member to verify that username and password is set correctly and that it has correct privileges.

3. Verify that Read committed snapshot option is on

Run the following query to the database. Make sure to replace the ‘DatabaseName’ in the examples below with the name of your Key Survey db.

CODE

SELECT is_read_committed_snapshot_on FROM sys.databases 
WHERE name='DatabaseName'

The result of this request should be 1. If it returns 0 value, run the following script

CODE

ALTER DATABASE 'DatabaseName'
SET READ_COMMITTED_SNAPSHOT ON