Bitbucket Data Center, a powerful version control and collaboration platform, can be seamlessly integrated with Microsoft SQL Server databases to ensure robust data management and enhanced performance. Whether you are setting up Bitbucket for the first time or migrating from another database, understanding how to connect Bitbucket to SQL Server is crucial for maintaining data integrity and system stability, especially when considering integrations with other Atlassian tools like Jira. This guide provides a detailed walkthrough on how to establish this connection, focusing on best practices and optimization for environments that leverage the Atlassian suite, including potential Bitbucket Jira Sql Server Integration scenarios.
This document outlines the process for connecting Bitbucket Data Center to a Microsoft SQL Server database. You can establish this connection during the initial Bitbucket setup using the Setup Wizard or at a later stage through the Database Migration Wizard. These instructions assume that you have already installed and configured your SQL Server instance and it is running and accessible within your network.
The general procedure for utilizing a SQL Server database with Bitbucket involves the following key steps:
- Installation of SQL Server in an environment accessible to your Bitbucket Data Center instance.
- Creation of a dedicated database and user account within SQL Server specifically for Bitbucket’s operational needs.
- Installation of Bitbucket Data Center on your chosen operating system (Windows, Linux, or Mac). Refer to the comprehensive Bitbucket installation guide for detailed instructions on the installation process.
Before proceeding, ensure that your SQL Server version is compatible with your Bitbucket Data Center version. Consult the official Bitbucket supported platforms documentation for a list of compatible SQL Server versions.
Prerequisites
Before you begin the connection process, ensure you have the following prerequisites in place:
- Installed and Running SQL Server: A functioning SQL Server instance accessible from the machine where Bitbucket Data Center is installed.
- Administrative Access to SQL Server: Necessary permissions to create databases, users, and configure settings within SQL Server.
- Bitbucket Data Center Installation: Bitbucket Data Center should be installed but not yet fully configured with a database connection if you are performing this during initial setup, or already running if you are migrating.
- Backup of Existing Data: If you are migrating from an existing database (internal or external), ensure you have a complete backup before proceeding.
Backing Up Your Current Database
Migrating from the Internal Bitbucket Database:
If you are currently using Bitbucket’s built-in internal database and planning to migrate to SQL Server, it is imperative to back up your Bitbucket home directory. This directory contains critical data and configurations.
Migrating from Another External Database:
If you are transitioning from a different external database to SQL Server, create a backup of that external database using the backup procedures recommended by your database vendor. This ensures data safety and allows for rollback if necessary.
Refer to the official Bitbucket documentation on data recovery and backups for detailed guidance on backup strategies.
Creating the Bitbucket Database in SQL Server
To prepare SQL Server for Bitbucket Data Center, you need to configure it appropriately. Follow these steps to create and configure the database:
| Step | Configuration Details |
|---|---|
| Create a Database | Establish a new database specifically for Bitbucket. For example, name it bitbucket. Remember this database name; you will need it during the Bitbucket connection setup. |
| Set Collation Type | Configure the database collation to be case-sensitive. A recommended collation is ‘SQL_Latin1_General_CP1_CS_AS’ (where CS denotes Case Sensitive). Case sensitivity is crucial for Bitbucket’s data integrity and operation. |
| Set Isolation Level | Set the database isolation level to Read Committed Snapshot Isolation (RCSI). This setting ensures data consistency and concurrency. Configure the database to use the isolation level, Read Committed with Row Versioning. |
| Create a Database User | Create a dedicated SQL Server user for Bitbucket to access the database. For instance, you might name this user bitbucketuser. Important: This user should not be the database owner but must be assigned to the db_owner role. This role is required during the initial setup and throughout Bitbucket’s operation due to its database interaction patterns. Retain this username for the connection steps later. |
| Grant Database User Permissions | Ensure the bitbucketuser has the necessary permissions: – Connect to the database. – Create and drop tables, indexes, and other constraints. – Insert, update, and delete data within the bitbucket database. These permissions are essential for Bitbucket to manage its schema and data effectively. |
| Enable TCP/IP Protocol | Verify that TCP/IP protocol is enabled in SQL Server Configuration Manager. SQL Server must be configured to listen on the correct TCP port, which is typically 1433 for a default SQL Server installation. Note this port number for the connection configuration. |
| Check Authentication Mode | Confirm that SQL Server is operating in the appropriate authentication mode. By default, SQL Server often uses ‘Windows Authentication Mode’. However, if the Bitbucket service account is not a trusted SQL Server login, you may encounter ‘Microsoft SQL Server, Error: 18452’ during Bitbucket startup. In such cases, switch to ‘Mixed Authentication Mode’ to allow SQL Server authentication. Bitbucket instances running on Windows can support SQL Server in ‘Windows Authentication Mode’, but this requires manual configuration as detailed later in this guide under “Use Integrated Authentication (Optional)”. |
| Disable SET NOCOUNT Option | Ensure the SET NOCOUNT option is disabled. This setting can be managed in SQL Server Management Studio: 1. Navigate to Tools > Options > Query Execution > SQL Server > Advanced. Ensure SET NOCOUNT is unchecked. 2. Go to Server > Properties > Connections > Default Connections and uncheck the no count option. Disabling SET NOCOUNT prevents interference with JDBC driver operations. |
It’s important to note that Bitbucket Data Center typically requires approximately 25 to 30 connections to the database to handle its operations efficiently.
Additionally, Bitbucket mandates that the SQL Server database maintains idle connections for at least 10 minutes. If the database connection timeout is set to less than 10 minutes, you might experience intermittent connection errors.
Here’s an example of SQL commands to create and configure the database from the SQL Server command line. Execute these commands if Bitbucket and SQL Server are on the same machine and accessible via localhost. Replace bitbucketuser and password with your chosen credentials.
SQL Server> CREATE DATABASE bitbucket
SQL Server> GO
SQL Server> USE bitbucket
SQL Server> GO
SQL Server> ALTER DATABASE bitbucket SET ALLOW_SNAPSHOT_ISOLATION ON
SQL Server> GO
SQL Server> ALTER DATABASE bitbucket SET READ_COMMITTED_SNAPSHOT ON
SQL Server> GO
SQL Server> ALTER DATABASE bitbucket COLLATE SQL_Latin1_General_CP1_CS_AS
SQL Server> GO
SQL Server> SET NOCOUNT OFF
SQL Server> GO
SQL Server> USE master
SQL Server> GO
SQL Server> CREATE LOGIN bitbucketuser WITH PASSWORD=N'password', DEFAULT_DATABASE=bitbucket, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
SQL Server> GO
SQL Server> ALTER AUTHORIZATION ON DATABASE::bitbucket TO bitbucketuser
SQL Server> GOThis script creates a database named bitbucket and a SQL Server login bitbucketuser with full access to this database. The user is granted permissions to create and drop tables, indexes, and other constraints within the bitbucket database, essential for Bitbucket’s schema management.
Connecting Bitbucket to the SQL Server Database
Once the SQL Server database is prepared, you can connect Bitbucket Data Center in two primary scenarios:
- During the initial setup process using the Setup Wizard.
- When migrating to SQL Server from an existing database via the Database Migration Wizard.
Connecting During Initial Setup (Setup Wizard)
- Database Selection: In the Bitbucket Setup Wizard, at the ‘Database’ step, choose External.
- Database Type: Select SQL Server as the Database Type.
- Form Completion: Fill out the connection details form with the following information (refer to the table below for details on each field).
- Proceed with Setup: Click Next and follow the remaining instructions in the Bitbucket Setup Wizard to complete the installation.
Connecting During Migration (Database Migration Wizard)
- Access Database Settings: In the Bitbucket administration console, navigate to Database under ‘Settings’.
- Initiate Migration: Click on Migrate database.
- Database Type Selection: Choose SQL Server as the Database Type.
- Form Completion: Complete the form with the necessary connection details as described in the table below.
- Start Migration: Click Start Migration to begin the database migration process.
Alt Text: Bitbucket Database Configuration form displaying fields for Hostname, Port, Database name, Database username, and Database password, specifically for setting up a connection to a SQL Server database.
| Field | Description |
|---|---|
| Hostname | Enter the hostname or IP address of the server where your SQL Server database is running. This is the network address Bitbucket will use to locate the database server. |
| Port | Specify the TCP port number that Bitbucket should use to connect to SQL Server. The default SQL Server port is 1433. Change this value only if your SQL Server instance is configured to listen on a different port. |
| Database name | Enter the name of the SQL Server database you created for Bitbucket (e.g., bitbucket). This is the database Bitbucket will use to store its data. |
| Database username | Provide the username of the SQL Server user you created for Bitbucket (e.g., bitbucketuser). This user account must have the necessary permissions to access and modify the database. |
| Database password | Enter the password for the specified Database username. Ensure this password is securely managed. |
Named Instances in SQL Server
If your SQL Server instance is a named instance, you will need to manually configure the bitbucket.properties file. Refer to the Atlassian Knowledge Base article on “Connecting to named instances in SQL Server from Bitbucket Server” for detailed instructions on how to configure this setting.
Optional: Using Integrated Authentication (Windows Authentication Mode)
Please note that starting from Bitbucket version 8.0, hosting on Windows is no longer supported, which also means Windows authentication is not supported for newer versions in a Windows hosting environment.
Windows authentication is an option only for Bitbucket instances running on Windows and connecting to SQL Server. It cannot be used on Linux because Microsoft does not provide the necessary shared libraries for Linux environments. For Linux-based Bitbucket servers connecting to SQL Server, mixed-mode authentication must be enabled on the SQL Server side.
Integrated authentication leverages native DLLs to use the credentials of the Windows service account under which Bitbucket is running to authenticate with SQL Server. Bitbucket includes both 32-bit and 64-bit DLLs; there is no need to download them separately from Microsoft.
Currently, Bitbucket does not provide a UI option to configure integrated authentication. To enable it, you need to manually modify Bitbucket’s configuration files after it has been initially set up to use SQL Server (e.g., post-Setup Wizard configuration).
To enable integrated authentication:
- Select and Rename DLL: Based on the JVM architecture (32-bit or 64-bit) used by Bitbucket, rename either
x64sqljdbc_auth.dllorx86sqljdbc_auth.dlltosqljdbc_auth.dlland place it in the<Bitbucket home directory>/lib/nativedirectory. Use thex64DLL only if you are using a 64-bit JVM. - Enable JVM Library Path: In the
<Bitbucket installation directory>/<span>_start-webapp</span>.batfile, uncomment the line definingJVM_LIBRARY_PATHby removingremat the beginning of the line. If you placedsqljdbc_auth.dllin a different location, adjustJVM_LIBRARY_PATHaccordingly. - Modify
bitbucket.properties: Edit the%BITBUCKET_HOME%sharedbitbucket.propertiesfile and append;integratedSecurity=trueto the end of thejdbc.urlline. Note that whilejdbc.userandjdbc.passwordproperties are still required to be defined in this file, they will be ignored when integrated authentication is enabled. Bitbucket will fail to start if these properties are removed. - Service Account Configuration: Ensure the Bitbucket service is running under a Windows Domain User Account that has the necessary permissions to access the SQL Server instance. This account should generally not be a member of any administrative groups (domain, local, or enterprise).
Integrated authentication via Kerberos is also possible and is documented in Microsoft’s JDBC documentation.
Installing the JDBC Driver
This section is relevant only if you are using specific Bitbucket distributions, such as running Bitbucket via the Atlassian Plugin SDK or if you have compiled Bitbucket from source. In standard Bitbucket Data Center installations, the JDBC driver is typically bundled.
If the SQL Server JDBC driver is not included in your Bitbucket distribution, you must download and install it manually.
- Download JDBC Driver: Download the appropriate JDBC driver from the Microsoft download center.
- Install Driver: Place the downloaded driver file in the
<Bitbucket home directory>/libdirectory (for Bitbucket 2.1 and later versions). - Restart Bitbucket: Stop and then restart Bitbucket Data Center for the changes to take effect.
For configurations that involve manually specifying a JDBC URL for SQL Server, please consult the guide on transitioning from jTDS to Microsoft JDBC Driver for SQL Server for specific instructions.
By following these steps, you can successfully connect Bitbucket Data Center to a Microsoft SQL Server database, ensuring a robust and scalable backend for your source code management and facilitating seamless bitbucket jira sql server integration and overall enhanced team collaboration.
