Wednesday, February 11, 2009

SQL Server 2008 Express Installation Checklist

It may be available for free download, but SQL Server 2008 Express has cost a lot of time, and therefore money. The installation itself is tedious and requires numerous downloads and prerequisites. Then there is the problem of the "reboot required check" that fails for the wrong reason.
As SQL Server magazine puts it, "Out of the box, SQL Server Express's network connectivity settings aren't enabled". Talk about an understatement! First, find an run the "SQL Server Configuration Manager", and go to the "SQL Server Network Configuration" section"
Enable the TCP/IP protocol, and then go to the "IP Addresses" tab. Make sure "TCP Dynamic Ports" are enabled by setting them to zero (?)
Use the TCP Port 1433 where required.
Make sure that under the "Native Client Configuration" that TCP/IP is enabled in the "Client Protocols" section and in the "Aliases" section.
Now go to the Windows Firewall and open port 1433 for the local network
Next, in the SQL Server Management Studio, go to the server properties and select "Connections". Make sure "Allow remote connections to the Server" is selected.
At this point one would assume that workstations can now connect to the server. Not exactly. You still need to install the "SQL Server Native Client" on the workstations, which is contained in the "Microsoft SQL Server 2008 Feature Pack, August 2008" download under the heading "Microsoft SQL Server 2008 Native Client", which is an MSI package requiring Windows Installer 4.5. After installing sqlncli.msi on each workstation, you can set up the ODBC connection to the server, and it should work. In theory. Watch this space.

No comments: