Microsoft SQL Server

1. Configuring SQL Server

Before you connect Bamboo to a SQL Server, you need to configure SQL Server appropriately.

• Change server authentication to 'SQL Server and Windows Authentication mode' — On a typical SQL Server installation, Windows Authentication mode is the default security mode. However, if you try to connect to the database with a database user using this authentication mode, SQL Server will throw an error. You need to change the server authentication mode to SQL Server and Windows Authentication mode in SQL Server before you can connect Bamboo to SQL Server. Please see this MSDN article for instructions on how to do this.
  
  Screenshot: Changing the SQL Server authentication mode
  
  
• Configure your firewall to allow SQL Server access — If you need to access SQL server through a firewall, you will need to configure your firewall appropriately. The following MSDN article describes how to configure a Windows firewall to allow SQL Server access, however the instructions are applicable to other firewalls: Configuring the Windows Firewall to Allow SQL Server Access.
• Enable the TCP/IP protocol for your database instance — You must enable the TCP/IP protocol for your SQL Server database instance by following the instructions in this MSDN article.

2. Creating your database

After configuring the SQL Server, you need to create the SQL database.

• Create the database for Bamboo — see this MSDN article for instructions.
• Assign the 'db-owner' role on the database for the user that will access the Bamboo database — the 'db_owner' fixed database role allows the user to perform all configuration and maintenance activities on the database. You need to add this role to the Bamboo user used to access your database by updating the login properties for your database user in SQL Server. Read more about login properties for SQL Server.
  
  Screenshot: Adding the 'db_owner' database role to a database user in SQL Server
  
  
  
  Please ensure that you use a SQL Server user account to log into your database, not a Windows user account.
• Configure the database to use case-sensitive collation — to make the SQL Server database respect case differences in the data it stores (which is required for Bamboo), ensure that you configure it using a case-sensitive collation option such as 'Latin1_General_CS_AS'. To access this feature in SQL Server Management Studio, right-click on the database name, select Properties from the resulting menu, then select the Options page.
  
  Screenshot: Configuring the Bamboo database to use 'Latin1_General_CS_AS' collation
  
  

• Configure the database to use the correct isolation level— Ensure that the new database was set to use Read Committed with Row Versioning as its isolation level. You can apply the new isolation by executing the following query:

  ALTER DATABASE <database name>
     SET READ_COMMITTED_SNAPSHOT ON
     WITH ROLLBACK IMMEDIATE;
  

  To verify the changes, use this query which should result in '1':
  
  

  SELECT sd.is_read_committed_snapshot_on
  FROM sys.databases AS sd
  WHERE sd.[name] = '<database name>';
  

3. Connecting Bamboo to SQL Server

Bamboo provides two ways to connect to a Microsoft SQL Server database — using JDBC or using a datasource. JDBC is generally simpler and is the recommended method.

If you are planning to support Unicode in Bamboo please enable unicode settings for SQL Server

Connect to SQL Server using JBDC

1. Run the Setup Wizard and choose the Custom Installation method.
2. On the Choose a Database Configuration page, choose External Database > Microsoft SQL Server 2005/2008 and click Continue. 

3. Ensure that Direct JDBC connection has been selected and complete the following fields (as shown in the screenshot below):

   Setting	Description
   Driver Class Name	Type net.sourceforge.jtds.jdbc.Driver (if different from the default)
   Database URL	The URL where Bamboo will access your database, e.g. jdbc:jtds:sqlserver://localhost:1433/<database>. If you are connecting to a Named Instance, you will need to append ;instance=mssqlnamehere to the connection string, where mysqlnamehere is the name of your named instance. For more details about syntax, please refer to the Microsoft SQL Server documentation.
   Username	The username that Bamboo will use to access your database.
   Password	The password that Bamboo will use to access your database.

4. Select Overwrite existing data if you wish Bamboo to overwrite any tables that already exist in the database.
5. Click Continue.

Screenshot: Set Up JDBC Connection SQL Server 2005/2008

Connect to SQL Server using a datasource

1. Configure a datasource in your application server (consult your application server documentation for details).
   For details about the syntax to use for the SQL Server database URL, please refer to the Microsoft SQL Server documentation.
2. Run the Setup Wizard and choose the Custom Installation method.
3. On the 'Choose a Database Configuration' page, choose External Database > Microsoft SQL Server 2005/2008 and click Continue. 
4. Choose Connect via a datasource (configured in the application server), as shown in the screenshot below.
5. In the JNDI name field, type the JNDI name of your datasource, as configured in your application server.
   If java:comp/env/jdbc/DataSourceName does not work, try jdbc/DataSourceName (and vice versa).
6. Select Overwrite existing data if you wish Bamboo to overwrite any tables that already exist in the database.
7. Click Continue.

Screenshot: Set up Datasource Connection 

Unicode characters not supported by default

Problem
Non-ASCII characters will not be displayed by Bamboo.

Reason
The default SQL Server dialect uses column types that do not support Unicode, specifically the char, varchar and text column types. See CONF-4786 for details.

Solution
To add Unicode support, use the Unicode SQL Server dialect which uses nchar, nvarchar and ntext column types. Unicode SQL Server dialect has the downside of halving the maximum length of each column from 8000 characters to 4000, as every char is stored in two bytes.

Enable Unicode SQL Server dialect on a new setup, perform these steps prior to 'Step 3 - Database Connection Setup'.

1. Open the <bamboo-install>\webapp\WEB-INF\classes\database-defaults\mssql.properties file within your Bamboo installation folder.
2. Comment the line: dialect=net.sf.hibernate.dialect.SQLServerDialect
3. Uncomment the line: #dialect=net.sf.hibernate.dialect.SQLServerIntlDialect
4. Start the Bamboo Setup Wizard