Prepare an MS SQL Server

Before you can create multi-user workspaces where several users have access to the same data, first you need to prepare your common repository.

To prepare an MS SQL Server environment, follow the steps below:

• Meet the database requirements for multi-user mode

• Prepare the database

Prepare the database

Make sure the user who prepares the MS SQL Server for common repositories has the following access rights: SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER.

These rights are usually available to db_datareader , db_datawriter, and db_owner roles.

To prepare your database, follow these steps:

1. Create a database on the database server.

2. To create the tables, adapt one of the provided scripts to your requirements, and then run it.

   • The script for the SQL Server is located in %COMMANDER_HOME%\SQL\MS SQL Server.

   • The script for Azure SQL is located in %COMMANDER_HOME%\SQL\Azure SQL Server.

3. Activate SNAPSHOT_ISOLATION to prevent deadlocks. To do so, use these statements:

   • ALTER DATABASE <name of your database> SET ALLOW_SNAPSHOT_ISOLATION ON

   • ALTER DATABASE <name of your database> SET READ_COMMITTED_SNAPSHOT ON

Defragment indexes

The user who prepares the MS SQL Server can also defragment indexes. This 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".