Connection Issue When Dynamically Publishing to a Tridion Broker Database with an Instance Name (JDBC)?

Question

The commit phase always fails with this error:

Committing Deployment Failed
Phase: Deployment Prepare Commit Phase failed, Unable to prepare transaction: tcm:0515104-66560,
org.hibernate.exception.JDBCConnectionException: Cannot open connection,
org.hibernate.exception.JDBCConnectionException: Cannot open connection, Unable to prepare transaction: tcm:0-515104-66560, 
org.hibernate.exception.JDBCConnectionException: Cannot open connection,
org.hibernate.exception.JDBCConnectionException: Cannot open connection

This is the configuration that works in databases with default instances (DEV/UAT):

<Storage Type="persistence" Id="brokerdb" dialect="MSSQL" Class="com.tridion.storage.persistence.JPADAOFactory">
            <Pool Type="jdbc" Size="5" MonitorInterval="60" IdleTimeout="120" CheckoutTimeout="120" />
            <DataSource Class="com.microsoft.sqlserver.jdbc.SQLServerDataSource">
                <Property Name="serverName" Value="ourServerName" />
                <Property Name="portNumber" Value="1433" />
                <Property Name="databaseName" Value="Tridion_Broker" />
                <Property Name="user" Value="TridionBrokerUser" />
                <Property Name="password" Value="xxxxxxxxpassxx" />
            </DataSource>
        </Storage> 

However, for our production, using a named instance is inevitable. So we tried this configuration to pass the instance's name but to no avail; we still get the error.

<Storage Type="persistence" Class="com.tridion.storage.persistence.JPADAOFactory" 
    Id="brokerdb"
    Url="jdbc:sqlserver://ourServerName/Tridion_Broker;instanceName=THE_INSTANCE_NAME;domain=DOMAIN_NAME" 
    Username="TridionBrokerUser" 
    Password="xxxxxxxxpassxx" 
    Driver="com.microsoft.sqlserver.jdbc.SQLServerDataSource">
    <Pool Type="jdbc" Size="5" MonitorInterval="60" IdleTimeout="120" CheckoutTimeout="120"/>
</Storage>

Is there anything wrong with the connection string? Or is there a way to pass the instance name using the first pattern; say <Property Name="instanceName" Value="THE_INSTANCE_NAME" /> for example?

Answer 1

Both Nikoli and Gertjan's reference made me realize that the instance name is not required. An alternative is to specify the port to which the instance is running on.

This article showed me how to know which port is being used for the instance.

This configuration worked:

<Storage Type="persistence" Id="brokerdb" dialect="MSSQL" Class="com.tridion.storage.persistence.JPADAOFactory">
        <Pool Type="jdbc" Size="5" MonitorInterval="60" IdleTimeout="120" CheckoutTimeout="120" />
        <DataSource Class="com.microsoft.sqlserver.jdbc.SQLServerDataSource">
            <Property Name="serverName" Value="ourServerName" />
            <Property Name="portNumber" Value="43333" />
            <Property Name="databaseName" Value="Tridion_Broker" />
            <Property Name="user" Value="TridionBrokerUser" />
            <Property Name="password" Value="xxxxxxxxpassxx" />
        </DataSource>
    </Storage> 

I also tried the connection string approach and it worked, too:

<Storage Type="persistence" Class="com.tridion.storage.persistence.JPADAOFactory" 
    dialect="MSSQL" 
    Id="brokerdb" 
    Url="jdbc:sqlserver://ourServerName:43333;databaseName=Tridion_Broker;" 
    Username="TridionBrokerUser" Password="xxxxxxxxpassxx" 
    Driver="com.microsoft.sqlserver.jdbc.SQLServerDataSource">
    <Pool Type="jdbc2" Size="5" MonitorInterval="60" IdleTimeout="120" CheckoutTimeout="120"/>
</Storage>

Answer 2

You can try the following:

<Property Name="serverName" Value="ourServerName\ourInstanceName" />

Answer 3

You should combine both server name and instance name.

<Storage Type="persistence" Id="brokerdb" dialect="MSSQL" Class="com.tridion.storage.persistence.JPADAOFactory">
    <Pool Type="jdbc" Size="5" MonitorInterval="60" IdleTimeout="120" CheckoutTimeout="120" />
    <DataSource Class="com.microsoft.sqlserver.jdbc.SQLServerDataSource">
        <Property Name="serverName" Value="ourServerName\\THE_INSTANCE_NAME" />
        <Property Name="portNumber" Value="1433" />
        <Property Name="databaseName" Value="Tridion_Broker" />
        <Property Name="user" Value="TridionBrokerUser" />
        <Property Name="password" Value="xxxxxxxxpassxx" />
    </DataSource>
</Storage>​

Another way, I have changed URL little bit for this old representation

<Storage Type="persistence" Class="com.tridion.storage.persistence.JPADAOFactory" Id="brokerdb" Url="jdbc:sqlserver://ourServerName;instanceName=THE_INSTANCE_NAME;databaseName=Tridion_Broker;integratedSecurity=true;" Username="TridionBrokerUser" Password="xxxxxxxxpassxx"    Driver="com.microsoft.sqlserver.jdbc.SQLServerDataSource">
    <Pool Type="jdbc" Size="5" MonitorInterval="60" IdleTimeout="120" CheckoutTimeout="120"/>
</Storage>

Try these URL for the above Old representation:-

1. jdbc:sqlserver://ourServerName\\THE_INSTANCE_NAME;databaseName=Tridion_Broker;integratedSecurity=true;

2. jdbc:sqlserver://ourServerName\THE_INSTANCE_NAME;databaseName=Tridion_Broker;integratedSecurity=true;

3. jdbc:sqlserver://ourServerName;instanceName=THE_INSTANCE_NAME;databaseName=Tridion_Broker;integratedSecurity=true;​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​

4. jdbc:sqlserver://ourServerName/Tridion_Broker;instanceName=THE_INSTANCE_NAME;domain=DOMAIN_NAME;

For your references:-

1. Building the Connection URL
2. Setting the Connection Properties

Answer 4

According to this msdn article: http://msdn.microsoft.com/en-us/library/ms378428.aspx

you should configure it like this:

jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]

Answer 5

Your original syntax of specifying the URL is correct, however, upon checking the documentation, I can't seem to find a valid property called "domain". This may be your culprit. Try specifying the domain in the username using a '\'.