by Oliver
23. June 2012 19:26
UPDATE: The SQL Server Browser is actually needed. Don’t know how it worked before without it. See below for instructions.
Recently, we tried to setup a failover SQL server instance but couldn’t get out remote web servers to connect to it. The setup of our current live DB server had been done a few years ago and unfortunately we didn’t write any documentation on the tricky steps back then. This time, we’re smarter ;-) The important parts are:
- Enable TCP/IP in the networking settings:
- Set a static TCP port to connect to – otherwise the port will be dynamic and for every connection you’d need to find out which port is currently used. Enter the properties for TCP/IP connections and set a TCP port in the IPAll section. I use the default SQL Server port 1433:
- In the Sql Server Configuration Manager, make sure both SQL Server and SQL Server Browser services are running and have their start mode set to Automatic:
- Open the firewall to allow connections through the port 1433 for the SQL Server service and through 1434 for the SQL Server Browser, potentially restricting access to only the hand-full IP addresses you need to connect:
- Now you should be able to connect to the SQL server instance using <IP address>\<instance name>.
That’s it – happy SQL’ing!