Something that you will learn to realise with Microsoft Office SharePoint Server 2007 is how important it is to have a handle on the SQL Server aspects of your farm installation. Ok, if you install everything on a single box and use Express edition you don’t really care about SQL much but try to do anything more adventurous and your SQL Server infrastructure will take a bit more thought.
Explorer a bit further and there are a few points regarding network protocols and port numbers that are important to understand too. My background in web development with an added dash of DBA work means I am very interested in this, but it can be easy to forget the basics.
For instance (forgive the pun), I had a reminder recently of how little you learn if you only have to deal with the default situation. For instance, in a lot of cases your separate SQL Server will be available to use on the default instance and default port number, but do you know what to do if you cannot use either? In the case of instancing, remember that you can qualify the server name with the instance i.e. SQLSERVER\Instance. And in the case of port numbers, remember it is SQL Server and not IIS you are talking to so the syntax is to separate the server and port number with a comma, not a colon. So to slightly extend our very small example, the database details for an instance on a non standard port would be “SQLSERVER\Instance, 9999”.
Another thing to remember with any kind of service is that the request by a client of a server can be more of a “conversation” by the end of a sequence of events between agreeing that the two parties can talk to actually getting down to the nitty gritty of exchanging the information at the centre of the discussion. When looking at SQL Server and instancing, we need to be aware of the SQL Server Browser Service which provides SQL clients with instance details. In particular, as each instance requires its own port number, the reserved port of 1434 accepts UDP connections from clients asking for the port number of a particular named instance. Of course you can connect if you have all of the information to hand, but if you try to connect to a particular instance with incomplete parameters then the SQL Browser service may come in to play. And if the service is not running or firewall rules block connections on that port, then the connection will not work. For a better explanation of this technology that came in with SQL Server 2005, look at SQL Server books online on the SQL Server Browser Service.