Database Setup for SQL Server

Use this guide in conjunction with the more general Database Setup Guide for Any Database. These instructions add some reference notes specific to SQL Server.

1. Review the known issues for SQL Server.
2. Identify which character encoding to use. To do this, check the encoding currently used by your application server and Confluence. All three must use compatible encoding. For example, the default SQL Server encoding of UCS-2 is compatible with UTF-8.
3. Create a new database (as an SQL administrator). If you set your application server and Confluence to use an encoding incompatible with UCS-2, specify that character encoding for the database.

4. Set the default collation for the database to be 'SQL_Latin1_General_CP1_CS_AS' (case sensitive). You can do this by issuing the following SQL query:

   ALTER DATABASE <database_name> COLLATE SQL_Latin1_General_CP1_CS_AS

   
   Note: if you receive an error at this point stating 'The database could not be exclusively locked to perform the operation', you may need to prevent other connections by setting the mode to single user for the transaction:

   ALTER DATABASE <database_name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
   <your ALTER DATABASE query>
   ALTER DATABASE <database_name> SET MULTI_USER;

   
   

5. Configure the database to use the isolation level, 'Read Committed with Row Versioning'. You can do this by issuing the following SQL query:

   Determine if READ_COMMITTED_SNAPSHOT is enabled

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

   Return value:
   1 = READ_COMMITTED_SNAPSHOT option is ON. Read operations under the read-committed isolation level are based on snapshot scans and do not acquire locks.

   0 = READ_COMMITTED_SNAPSHOT option is OFF (default). Read operations under the read-committed isolation level use share locks.

   ALTER DATABASE <database_name>
      SET READ_COMMITTED_SNAPSHOT ON
      WITH ROLLBACK IMMEDIATE;
   

6. Create a new SQL user account for Confluence (as an SQL administrator). Provide full create, read and write permissions for the database tables. Note that Confluence must be able to create its own schema.

7. If you are configuring a datasource to connect to your MS SQL server database, install the database drivers.
   

   The JDBC drivers for this database are bundled with Confluence.

   • If you are using a direct JDBC connection, you do not need to download or install any JDBC drivers.
   • If you are connecting via a datasource, you must download and install the drivers manually. For information about driver versions and download links, see Database JDBC Drivers.
   • If you are not sure which connection you are using, it is probably JDBC. A JNDI resource must be configured manually, as described in each database's docs: PostgreSQL, MySQL, SQL Server or Oracle

8. If you are configuring a datasource to connect to your MS SQL server database, place the JAR file in <confluence install>/WEB-INF/lib. You may also find this page helpful: http://jtds.sourceforge.net/faq.html
9. If you are installing a new version of Confluence: Start Confluence and visit the home URL (e.g. http://localhost:8090) to start the Confluence Setup Wizard. Select a custom installation, and insert the relevant connection information.
   

   • When prompted for a driver class name in the database setup step enter:

     net.sourceforge.jtds.jdbc.Driver
     

   • When prompted for the jdbc url, the format to use is:

     jdbc:jtds:sqlserver://<server>:<port>/<database>