Database Setup for SQL Server
This page provides instructions for configuring Confluence to use the Microsoft SQL Server database.
Step 1. Check the prerequisites
Check the following before you start:
- Check that your version of SQL Server is supported. See Supported Platforms. If your version is not supported, please upgrade to a supported version of SQL Server before installing Confluence.
- If you have been evaluating Confluence and wish to transfer your data to a new database, consult the following guide first: Migrating to Another Database.
- If you are migrating from another database, consult the following guide first: Migrating to Another Database.
Step 2. Install SQL Server
Step 3. Set up your SQL Server database and user
In this step you will create a database within SQL Server to hold your Confluence data, and a database user with authority to access that database. The database user should be in the
- 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.
- Using your SQL administrator permissions, create a new database in SQL Server.
- If you set your application server and Confluence to use an encoding incompatible with UCS-2, specify that character encoding for the database.
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 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;
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'
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;
- Using your SQL administrator permissions, create a new SQL user account for Confluence (for example,
confluenceuser). Give this user full create, read and write permissions for the database tables. Note that Confluence must be able to create its own schema.
Refer to the documentation for your database for more information on how to create a new database user.
Step 4. Install Confluence and the SQL Server database driver
- If you plan to set up a direct JDBC connection to SQL Server, you can run the Confluence installation and move directly on to the Confluence Setup Wizard, as described below. The SQL Server JDBC driver is bundled with Confluence, as documented on this page: Database JDBC Drivers.
- If you plan to set up a datasource connection to SQL Server:
- Stop immediately after the Confluence installation, before opening the Confluence Setup Wizard in your browser. If you have already got part-way through the Confluence Setup Wizard, stop at the database setup step. You will be able to restart the setup wizard at the same step later.
- Follow the steps described in Configuring a SQL Server Datasource in Apache Tomcat.
Step 5. Set up your database connection in the Confluence Setup Wizard
Start Confluence, and go to the Confluence Setup Wizard in your browser. Follow these steps to set up the new configuration:
- Follow the initial steps in the Confluence Setup Guide.
- When prompted to choose an evaluation or production installation, choose production installation.
- When prompted to choose My own database, then select your database type (Microsoft SQL Server).
- Choose either the direct JDBC or the datasource connection, to suit the choice you made earlier when setting up the SQL Server database driver.
- For the JDBC connection:
Use the default Driver Class Name:
Enter your Database URL in the following format, replacing your hostname, port and database name:
- Enter the username (for example,
confluenceuser) and password you chose earlier.
- For a datasource connection: Set the Datasource Name to
Congratulations! Confluence is now using your SQL Server database to store its data.
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.
If you have an existing Confluence installation and need to set up a test environment that replicates your production environment (including the jTDS driver), you can do this in the setup wizard by selecting Unsupported Database in the database selection screen and then specifying the jTDS driver as follows:
- Driver class name:
- Database URL (example format): j
- Hibernate dialect:
If you get the following error message
,verify that you have given the
confluenceuseruser all the required database permissions when connecting from
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)'
- If none of the above describes your issue, please create a support ticket at http://support.atlassian.com and be sure to include your logs (found in