Installing multiple default instances on a single server


As you probably know, SQL Server allows only one default instance per server. The reason is not actually something special to SQL Server, but it has to do with the way TCP/IP endpoints work.

In fact, a SQL Server default instance is nothing special compared to a named instance: it has a specific instance id (MSSQLSERVER) and listens on a well-known TCP port (1433), but it has no other intrinsic property or feature that makes it different from any other instance.

Let’s look closely to these properties: the instance id is specific to a SQL Server instance and it has to be unique. In this regard, MSSQLSERVER makes no exception. Similarly, a TCP endpoint must be unique and there can be only one socket listening on a specific endpoint.

Nevertheless, I will show you a way to have multiple “default” instances installed on the same server, even if it might look impossible at a first look.

Install two instances of SQL Server

First of all, you need to have two (or more) instances installed on your server. In this example I will use the server “FANGIO” and I will install two named instances: INST01 and INST02.

Here’s what my Configuration Manager looks like once the two instances are ready:

COnfigManager

In this case I used two named instances, but it would have worked even if I used a default instance and a named instance. Remember? Default instances are nothing special.

Provision IP addresses

Each SQL Server instance must listen on a different TCP endpoint, but this does not mean that each instance has to listen on a different port: a TCP endpoint is made of an IP address and a port. This means that two instances can listen on the same port, as long as the IP addresses are different.

In this case, you just need to add a new IP address to the server, one for each SQL Server instance that you want to listen on port 1433.

TCPIP

Configure network protocols

Now that you have multiple IP addresses, you just have to tell SQL Server to listen on that specific address, port 1433.

Open the Configuration Manager and enable TCP/IP:

NetworkConfig

Now open the properties applet and disable “Listen All”:

ListenAll

In the IP Addresses tab, configure the IP address and the port:

NetworkConfig2

In this case I enabled the address 10.0.1.101 for INST01 and I disabled all the remaining addresses. For INST02 I enabled 10.0.1.102.

Configure DNS

Now the server has two IP addresses and they both resolve to its network name (FANGIO). In order to let clients connect to the appropriate SQL Server instance, you need to create two separate “A” records in DNS to resolve to each IP address.

In this case I don’t have a DNS server (it’s my home lab) so I will use the hosts file:

hosts

 

Final Setup

Now the example setup looks like this:

setup

When a client connects to the default instance on ASCARI, it is connecting to FANGIO\INST01 instead. Similarly, the default instance on VILLENEUVE corresponds to FANGIO\INST02.

ssms

Why would I want to do this?

If you had only default instances in your servers, moving databases around for maintenances, upgrades or consolidations would be just a matter of adding a CNAME to your DNS.

With named instances, the only way to redirect connections to a different server is by using a SQLClient alias. Unfortunately, aliases are client-side settings and have to be deployed to each and every client in order to work. Group policies can deploy aliases to multiple machines at once, but policies are not evaluated immediately, while a DNS entry can propagate very quickly.

Another reason to use this setup is the ability to bypass the SQLBrowser: when a named instance is specified, the client has to contact the SQLBrowser service on port 1434 with a small UDP datagram and receive back the list of instances, along with the port they’re listening on. When the default instance is specified, there is no need to contact the SQLBrowser, because we already know the port it is listening on (it’s 1433, unless it has been changed).

Sometimes the firewall settings for SQLBrowser are tricky to set up, especially with clusters. Another thing I recently discovered is that SQLBrower allows attackers to create huge DDOS attacks using a 440x amplification factor.

Security concerns

Some setup guides recommend that you change the port SQL Server listens on to something different from 1433, which is a well-known port, more likely to be discovered by attackers. I think that an attacker skilled enough to penetrate your server needs much more resistance than just “hiding” your instance to a non-default port. A quick port scan would immediately reveal any SQL Server instance listening on any port, so this is really a moot point in my opinion.

Bottom line

SQL Server allows only one default instance to be installed on a machine, but with a few simple steps every instance can be made a “default” instance. The main advantage of such a setup is the ability to redirect client connections to a database instance with a simple change in the DNS configuration.

Advertisements

Posted on January 29, 2015, in SQL Server and tagged , , , . Bookmark the permalink. 22 Comments.

  1. Hi, nice post.
    Do you think this could be a solution to migrate a couple of servers (with only one default instance per server) to one server with Alwayson AG ?

  2. Good article, well done, thank you! Comes at perfect time for me.

  3. This is how SQL clusters work. Each instance in the cluster has to be bound to a unique IP (so instances can fail over separately) – ergo they are all ‘default instances’

    • Not exactly. You can have named instances in a cluster and you only can have one default instance. Same as a standalone server.
      The port 1433 can be used for all instances to make them “default” using the same technique. With clusters it’s easier because each instance is already bound to its ip address.

  4. Hello mates, its impressive post regarding educationand completely explained, keep it up all the time.

  5. Was about to set this up in a lab and voila i see your post! Saved me one full day !
    Thanks !

  6. Hi! Thanks for this great post!

    Would you know how to set the SPN for these instances for kerberos authentication?

    Cheers!

  7. Thanks, I was trying to do this on a SQL box with a couple of different versions in co-existence but it wasn’t working, your article confirmed to me I was on the right track, not sure what I did (or didn’t do) first time around but tried again after reading your article and it worked.

  8. Somewhere I think I’ve done something wrong, although I followed your tutorial 3 times over starting from scratch.
    When connecting from a “client” to my VM with the 2 SQL Server instances, no matter which IP I use, 10.115.8.198 or 10.115.8.199, they both redirect me to INST01.

    I don’t really understand why it’s doing such.

    For INST02 I have set up in TCP/IP the 10.115.8.199 IP with 1433 port, but still I’m getting redirected to the original instance that was on 10.115.8.198.

    Can you please point me to where my mistake was made?

    Grazie mille!

    • Did you restart the instances?

      • Yes, quote a few times.

      • Do both instances need to have “Listen All” set to “No” in Configuration Manager -> SQL Server Network Configuration?

      • Both instances need to have “listen all” set to “no”.

      • Well, if I do set both instances to have “Listen All” to “No” then the service associated to my instance fails to start up with “Windows could not start the SQL Server on Local Computer. For more infor, reivew Ssytem Event Log .. and refer to service-specific error code 10049”, whereas the error loc says “SQL Server could not spawn FRunCM thread.”

      • Did you enable the individual IP addresses?

      • Yes, IP’s are enabled in Configuration Manager for both instances. I guess I’ll have to keep digging..

      • Honestly, I have no idea. If you need assistance with that, hit me up on Zoom

      • I finally managed to set both instances’s “Listen All” to “No”. The issue seemed to be where I was restarting the instances from Windows->Services instead of from Configuration Manager. When I stopped both instances from “Configuration Manager / SQL Server Services”, redid the configuration and started them again from Configuration Manager, it worked!

        Even so, without your tutorial I would have been lost! Grazie mille ancora!!!

      • Excellent! Glad I could help

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: