Recently, I was setting up Tridion content delivery on my development server. This runs as a VMWare image on my laptop, while the MSSQL database runs without virtualisation on the same laptop. If you read this earlier post, you will know that I like to have a script to check all my settings in advance (the script uses the standard .NET data access classes). I had done this, and everything was fine, so I was mildly surprised to find that as soon as I tried to publish anything I got error messages about being unable to connect to the database. The relevant part of my storage configuration looked like this:

<Storage Id="brokerdb" Type="persistence" 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="WSL117\DEVELOPER" />
    <Property Name="portNumber" Value="1433" />
    <Property Name="databaseName" Value="Tridion_Broker" />
    <Property Name="user" Value="TridionBrokerUser" />
    <Property Name="password" Value="topsecret" />
  </DataSource>
</Storage>

OK - so all those settings were just the same as in my test script, except that the test script didn't specify the port number, but that's the default port, so nothing to see there, eh?

Anyway - the message was clear, it was something to do with the connection, so I went to check that MSSQL was listening on the expected port with a quick "netstat -oan". Lo - and behold, it was nowhere to be seen. Eventually I discovered that in the Sql Server Configuration Manager you can configure the port, and that there there's a setting called "TCP Dynamic Ports", which was switched on.

At this point, I could simply have configured a static port number and moved on, but I was intrigued. If MSSQL wasn't listening on a static port, how did my test script succeed? OK - it didn't seem too unreasonable that whatever mechanism was in play should be understood by the .NET framework, but could I get it to work from a Java-based system. Well after a bit of Googling, it turned out that there's a service called the SQL Server browser, which lets the client know what port it needs to connect to. Not only that, but it seems the Microsoft JDBC driver, which I was using, also supports this mechanism. I commented out the Property element that specifies the port number, restarted IIS (this was an HTTP Upload site) and sure enough, when I tested it again, everything worked great.

All this: the sweet smell of success, and yet somehow I was still troubled. Why on earth would Microsoft have introduced this dynamic mechanism? After all, it just means more configuration. Extra stuff to tweak. Extra stuff to go wrong. So why? It turns out that the answer is pretty straightforward. This quote from the SQL Server Help explains it all:

Prior to SQL Server 2000, only one instance of SQL Server could be installed on a computer. SQL Server listened for incoming requests on port 1433, assigned to SQL Server by the official Internet Assigned Numbers Authority (IANA). Only one instance of SQL Server can use a port, so when SQL Server 2000 introduced support for multiple instances of SQL Server, SQL Server Resolution Protocol (SSRP) was developed to listen on UDP port 1434. This listener service responded to client requests with the names of the installed instances, and the ports or named pipes used by the instance. To resolve limitations of the SSRP system, SQL Server 2005 introduced the SQL Server Browser service as a replacement for SSRP.

I had installed a named instance of MSSQL  alongside the existing SQLEXPRESS instance, so perhaps I should have figured this out myself. Whatever - at least it explains why things are set up this way. I chatted with a colleague from Indivirtual's hosting partner Sentia, and he confirmed that for Tridion infrastructure jobs, one of the tasks they have to do is configure MSSQL to listen on static ports. For a dedicated server, of course, this is the obvious choice.

Still - for the kind of configuration I have for development and research, it's great that the dynamic ports feature works well with Tridion. Of course, that's not the same thing as being a supported configuration. As with any enterprise software vendor, SDL Tridion only generally support configurations they have tested. In order to ensure that this gets "on to their radar", I've created an "idea" on ideas.sdltridion.com. (If you have a login there, you can go and vote it up if you like!) Hopefully in future releases, this will become a tested and supported configuration.

Anyway - there was a time when I did far more infrastructure work than I've done lately. I guess it shows!