Database Setup for SQL Server

Step 2. Install SQL Server

If you do not already have an operational SQL Server database, download the installation package from the Microsoft SQL Server download page and follow the instructions on MSDN.

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 db_owner role.

1. 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.
2. Using your SQL administrator permissions, create a new database in SQL Server.
3. 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 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. 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.

Step 4. Install Confluence and the SQL Server database driver

Decide whether you will set up a direct JDBC connection or a datasource connection to SQL Server, to suit your environment. If unsure, choose direct JDBC.

Install Confluence if you have not done so already. See the Confluence Installation Guide.

• 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:

1. Follow the initial steps in the Confluence Setup Guide.
2. When prompted to choose an evaluation or production installation, choose production installation.
3. When prompted to choose an embedded or external database, select Microsoft SQL Server from the dropdown list and choose External Database.
4. 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:

• • • When prompted for a Driver Class Name, enter the following:

      net.sourceforge.jtds.jdbc.Driver
      

    • When prompted for the Database URL, use this format:

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

       If MS SQL is clustered, use this format:

      jdbc:jtds:sqlserver://<server>:<port>/<database>;instance=<instance>

    • Enter the username (for example, confluenceuser) and password you chose earlier.
  • For a datasource connection: Set the Datasource Name to java:comp/env/jdbc/confluence

Congratulations! Confluence is now using your SQL Server database to store its data.

Troubleshooting

• If you get the following error message, verify that you have 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)'

• The following page contains common issues encountered when setting up your SQL Server database to work with Confluence: Known Issues for SQL Server.
• Additional ports may be required to be opened. See this support document from Microsoft about the ports required for SQL Server.
• If Confluence complains that it is missing a class file, you may have placed the JDBC driver in the wrong folder.
• 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 <CONFLUENCE-INSTALLATION>/logs and <CONFLUENCE-HOME>/logs).