This documentation relates to an earlier version of Bamboo.
View

Unknown macro: {spacejump}

or visit the current documentation home.

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 3 Current »

These instructions will help you connect Bamboo to a Microsoft SQL Server 2005 or a Microsoft SQL Server 2008 database.
(Microsoft SQL Server 2000 is not a supported database for use with Bamboo.)

(info) SQL Server Express 2005 and 2008 are not recommended databases due to CPU, memory, database size limitations (please see these pages for full details: SQL Server Express 2005 feature comparison, SQL Server Express 2008 feature comparison). However, the instructions below will allow you to connect Bamboo to SQL Server Express 2005/2008.

(info) Please note, the JDBC driver for SQL Server 2005/2008 (JTDS 1.2.2) is bundled with Bamboo. You do not have to download and install the driver.

1. Configuring SQL Server

 

Before you connect Bamboo to a SQL Server, you need to configure the 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 (click to view larger image)



  • 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 (click to view larger image)



    (warning) 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 (click to view larger image)



  • 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 — via JDBC or via a datasource. JDBC is generally simpler and is the recommended method.

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

Connecting via JBDC

To connect Bamboo to a Microsoft SQL Server database, via JDBC,

  1. Run the Setup Wizard and choose the 'Custom Installation' method.
  2. On the Choose a Database Configuration page, choose 'External Database', select 'Microsoft SQL Server 2005/2008' from the list and click the 'Continue' button. The Database Configuration page will appear.
  3. Ensure that 'Direct JDBC connection' has been chosen and complete the following fields (as shown in the screenshot below):
    • 'Driver Class Name' — Type the following: net.sourceforge.jtds.jdbc.Driver (if different from the default)
    • 'Database URL' — Type the URL where Bamboo will access your database, e.g. jdbc:jtds:sqlserver://localhost:1433/<database>. For details about syntax, please refer to the Microsoft SQL Server documentation.
    • 'Username' — Type the username that Bamboo will use to access your database.
    • 'Password' — Type the password that Bamboo will use to access your database.
  4. Select the 'Overwrite existing data' check box if you wish Bamboo to overwrite any tables that already exist in the database.
  5. Click 'Continue' to finish specifying your connection settings.

Screenshot 1: 'Set Up JDBC Connection SQL Server 2005/2008'

Connecting via a datasource

To connect Bamboo to a MS SQL Server, via a datasource,

  1. Configure a datasource in your application server (consult your application server documentation for details).
    (info) 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', select 'Microsoft SQL Server 2005/2008' from the list and click the 'Continue' button. The Database Configuration page will appear.
  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.
    (warning) If java:comp/env/jdbc/DataSourceName does not work, try jdbc/DataSourceName (and vice versa).
  6. Select the 'Overwrite existing data' check box if you wish Bamboo to overwrite any tables that already exist in the database.
  7. Click 'Continue' to finish specifying your connection settings.

Screenshot 2: 'Setup 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
  • No labels





Except where otherwise noted, content in this space is licensed under a Creative Commons Attribution 2.5 Australia License.