Learning how to communicate with Microsoft SQL Server.......

Before you ask hell has not frozen over, this blog post is actually about SQL Server. Maybe this should be considered illegal for an Oracle based blog but i think most of us in some form or another will have to play nice with those evil SQL Server databases that seem to keep proliferating.

I thought it would be useful to share how you actually connect to them and try and relate that to the familiar world of oracle connectivity. Now after a lot of messing around today i think i understand it well enough to write this post - but don't shoot the messenger I'm not a SQL Server DBA - I just know some of the basics so there is quite a high possibility that what I'm about to say is not 100% correct - but at this point in time it makes sense to me......here goes then

There is more than one way to connect to sql server (named pipes, shared memory etc) but I'm only going to deal with tcp/ip style connections which in most cases is how you'll probably want to connect.

So first up a quick bit of background on installing/configuring sql server. For a default install you end up with a windows service running the database (and the 'listener' - they ae not separate things in sql world) this is on port 1433 (think 1521 for oracle) and for most simple installations this is probably all you'll need. The port can easily be changed by using the sql server configuration manager tool and navigating to the screen below - here you can see that the port has been changed and is now 1436.

It can also be seen (on the protocol tab) that be default behaviour is to listen on this port on all network cards - this can be changed here is so desired

So in the simple case thats all the information you need to know and as long as the firewall is open you will be able to connect to the database with the information from above - as a test i installed the jtds driver into sql developer to allow me to connect from sql developer to sql server - you can see the connections window is changed as below (just google "sql developer jtds" and you'll find how to do it - it's very easy)

I just needed to populate the hostname and port correctly and i can then connect to sql server just fine.

So in the simple case there is not much to go wrong, however it does get more complicated. If you do a non default install of sqlserver you can create a "named instance" - that is you give the installation a name which is then used to separate out the components from other installs on the server. This was introduced many years ago i think primarily to allow more than one installation of sql server on a host server - you may have seen a sql server instance referred to as server-name\SOME_NAME where SOME_NAME is the 'named' part of the named instance.

The named instance port setup is exactly the same as a default instance and the connectivity is exactly the same in the simple java based connection shown above - you need make no mention of the named instance at all in the connection - as long as you know the server name and the port associated with that instance of sqlserver - the name is actually irrelevant from this style of connection point of view.

The difficult part is if you choose to connect in a slightly different way, you have the option in jtds to actually specify the named instance name as part of the connection string - so what happens differently in this case?

If i change my connection string to mention the named instance - see example below

then some other things come into play and confuse the issue. In the case above 1436 is actually irrelevant (though you can't not include a port , you can actually give it any value at all it's not used - it just needs to be there). What actually happens is that the connection contacts the sql browser service (listening on udp port 1434) and asks it 'tell me which port named instance SOME_NAME is running on within this server' - the browser service then replies telling us it's on 1436 - and we are then connected as before - just with an additional step on the way (and additional firewall opening of udp 1434 if required).

The named instance does allow you to flexibly change the port without having to update the client config and maybe giving the named instance a friendly application name is also of benefit here so it's just not some server name which bears no relation to the thing we are connecting to. Other than that from a java connection point of view the named instance is not relevant.

The browser service also seems to enable the list of sqlservers to be propagated round somehow so when you try and connect from microsoft tools using the default sql client, you get a browse option to give you a list of all the sql servers it knows about (quite how this information is propogated/published around the network i'm not sure).

Another thing i discovered is the concept of aliases, these only exist on a single server and if required elsewhere need to be created on every server. These allow you to give an alias for a sql connection to avoid having to remember the host/port for any connections. This is probably the most tnsnames like thing i could find - it's local to every box and contains connection information for you ti find the database - if you want to update the host/port information you just change the detail of the alias not the alias name itself - exactly how we'd do for tnsnames.

An example of an alias is here

So you can see richtest actually points at server xxx on port 1436 - this is essentially a tnsnames entry as far as i can make out. Aliases though seem to have a bad press in the sql community for some reason.

There is no concept of a central 'alias' function as far as i can make out - so there is no equivalent of ldap lookups of entries as we can have for tnsnames.


A quick summary then of some of the key facts
1. 1433 is the default port
2. Any free port can be configured as the connectivity port
3. SQL Server can be installed multiple times on a single server, but they are seperate - that is a single port on a certain ip address can only connect to one of the instances - this is different to the oracle listener which can server many instances.
4. If a server has multiple ip address, SQL Server can be configured on some or all of them. This would enable 1433 to be used by 2 different installations - but they have to be on different NICS
5. aliases seem to relate well to tnsnames files but are not widely used - and only available for the standard ms way of connecting (you can't use an alias in a java connection for example)
6. The browser service does not need to be used if you know the port
7. If you want to use the instance name you have to use the browser service on udp 1434


I hope that makes some kind of sense to any other Oracle DBA's reading this, the basics are very much like oracle it's only really the named instance and the browser service that complicate things - but once you know what they are doing it's all pretty simple.