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.
On this page:
Related pages:
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.
2. Create a database and database user
Once you've installed SQL Server, create a database user and database for Confluence as follows:
- Using your SQL administrator permissions, create a new database (for example
confluence) 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_ASIf 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;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;Using your SQL administrator permissions, create a new SQL user account for Confluence (for example,
confluenceuser).Give this user the default schema as follows:
ALTER USER <confluenceuser> WITH DEFAULT_SCHEMA = dboMake sure this user has full create, read and write permissions for the database tables. Confluence must be able to create its own schema, and have the ability to create/drop triggers and functions. Refer to the SQL Server documentation for more information.
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: 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: |
| 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
In Confluence 6.6 we replaced the open source jTDS driver for Microsoft SQL Server with the official Microsoft JDBC Driver for SQL Server. You will be automatically migrated to the new driver when you upgrade to 6.6 or later.
If for some reason the automatic migration fails (for example, if you're using a datasource connection), you'll need to make this change manually. See Migrate from the jTDS driver to the supported Microsoft SQL Server driver in Confluence 6.4 or later.
Troubleshooting
If you get the following error message
,check you've given theconfluenceuseruser all the required database permissions when connecting fromlocalhost.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)'- You may need to open additional ports. See this Microsoft KB about the ports required for SQL Server.
- The following page contains common issues encountered when setting up your SQL Server database to work with Confluence: Known Issues for SQL Server.