Database Setup for SQL Server

This page provides instructions for configuring Confluence to use a Microsoft SQL Server database.

Before you start

Check the following before you start:

  • See Supported Platforms to check your version of SQL Server is supported. You may need to upgrade your database before installing Confluence.
  • If you're switching from another database, including the embedded evaluation database, read Migrating to Another Database before you begin.

1. Install SQL Server

If you don't already have Microsoft SQL Server installed, download and install it now. See Installation for SQL Server on MSDN for step-by-step instructions.

Note about authentication modes...

SQL Server allows two types of authentication: SQL Server Authentication and Windows Authentication. To make sure Confluence will be able to connect to your database you'll need to set your SQL server to allow Mixed Authentication (both SQL Server and Windows modes). This setup is generally found under Properties > Security > Server Authentication.


2. Create a database and database user

Once you've installed SQL Server, create a database user and database for Confluence as follows:

  1. Using your SQL administrator permissions, create a new database (for example confluence)
  2. Set the default collation for the database to SQL_Latin1_General_CP1_CS_AS (case sensitive).  

    ALTER DATABASE <database-name> COLLATE SQL_Latin1_General_CP1_CS_AS

    If you see a 'database could not be exclusively locked to perform the operation' error, 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;
  3. Check the database isolation level of READ_COMMITTED_SNAPSHOT is ON.  

    SELECT is_read_committed_snapshot_on FROM
    sys.databases WHERE name= 'database-name' 

    If this query returns 1, then READ_COMMITTED_SNAPSHOT is ON, and you're good to go. 

    If this query returns 0, READ_COMMITTED_SNAPSHOT option is OFF and you will need to turn it on as follows:

    ALTER DATABASE <database-name>
       SET READ_COMMITTED_SNAPSHOT ON
       WITH ROLLBACK IMMEDIATE;
  4. Using your SQL administrator permissions, create a new SQL user account for Confluence (for example, confluenceuser). 

  5. Give this user full create, read and write permissions for the database tables. Confluence must be able to create its own schema. Refer to the SQL Server documentation for how to do this. 

3. Install Confluence 

Check out the Confluence Installation Guide for step-by-step instructions on how to install Confluence on your operating system. 

4. Enter your database details

The Confluence setup wizard will guide you through the process of connecting Confluence to your database. 

Use a JDBC connection (default)

JDBC is the recommended method for connecting to your database.

The Confluence setup wizard will provide you with two setup options:

  • Simple - this is the most straightforward way to connect to your database.
  • By connection string - use this option if you want to specify additional parameters and are comfortable constructing a database URL. 

Depending on the setup type, you'll be prompted for the following information.

Setup type Field Description
Simple  Hostname This is the hostname or IP address of your database server.  
Simple  Port This is the SQL Server port. If you didn't change the port when you installed SQL Server, it will default to 1433.
Simple Database name  This is the name of your confluence database. In the example above, this is confluence
Simple Instance name

To find out your instance name, connect to your database and run one of the following:

select @@SERVICENAME;
SELECT SERVERPROPERTY('InstanceName');

If you have a default named instance setup in SQL Server, you won't need to specify this parameter.

By connection string Database URL The database URL is entered in this format:
jdbc:sqlserver://<hostname>:<port>;databaseName=<database>

For example:  
jdbc:sqlserver://yourserver:1433;databaseName=confluence

Both Username This is the username of your dedicated database user. In the example above, this is confluenceuser.
Both Password This is the password for your dedicated database user.

Use a JNDI datasource

If you want to use a JNDI datasource, see Configuring a datasource connection for the steps you'll need to take before you set up Confluence, as the setup wizard will only provide the option to use a datasource if it detects a datasource in your Tomcat configuration. 

5. Test your database connection

In the database setup screen, hit the Test connection button to check:

  • Confluence can connect to your database server
  • the database collation and isolation level is correct
  • your database user has appropriate permissions for the database

Once the test is successful, hit Next to continue with the Confluence setup process. 

Database driver changes

We are replacing the open source jTDS driver for Microsoft SQL Server with the official Microsoft JDBC Driver for SQL Server. From Confluence 6.4, all new Confluence installations will use the Microsoft JDBC Driver for SQL Server by default.

Existing instances will continue to use the bundled jTDS driver. We'll help you migrate to the Microsoft driver in a later release. 

Troubleshooting

  • If you get the following error message, check you've given the confluenceuser user all the required database permissions when connecting from localhost.

    Could not successfully test your database: : Server connection failure during transaction. Due to underlying exception: 'java.sql.SQLException: Access denied for user 'confluenceuser'@'localhost' (using password: YES)'
Last modified on Oct 25, 2018

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.