Prepare an MS SQL Server

The user who prepares the MS SQL Server for common repositories needs to have the following access rights and database roles:

  • SELECT, INSERT, UPDATE, CREATE, ALTER, and DELETE to all tables of the common repository

  • db_owner, db_datawriter, and db_datareader

To prepare your database, follow the steps below:

  1. Create a database on the database server.

  2. To create the tables, run one of the provided scripts. The scripts are located at %COMMANDER_HOME%\SQL.

    • The script for the SQL Server is located in the directory MS SQL Server.

    • The script for Azure SQL is located in the directory Azure SQL Server.

    You have to adapt the script to your local requirements and conditions.

To delete a schema, you need DROP rights: database role db_owner.

Database administrators can also prevent deadlocks and defragment indexes.

Prevent deadlocks

To prevent deadlocks, you can activate SNAPSHOT_ISOLATION via the statements below. Tricentis recommends that you use SNAPSHOT_ISOLATION to prevent deadlocks.

ALTER DATABASE <databasename> SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE <databasename> SET READ_COMMITTED_SNAPSHOT ON

Replace <databasename> with the actual name of the database.

Defragment indexes

Daily defragmenting enhances the performance of common repositories.

To defragment indexes, adapt the sample script Rebuild Indexes MSSQL.sql which is located at %COMMANDER_HOME%\SQL\MS SQL Server.

What's next

Now that you have prepared the database, you can create your workspaces. For information on how to do so, see chapter "Create Oracle, MS SQL Server, or DB2 workspaces".