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>