How and why would I use multiple instances of SQL Server

By:   |   Comments (6)   |   Related: > SQL Server Configurations


Problem

SQL Server 2000 and 2005 have the capability to use multiple instances of the database engine on a single server.  But is going down this path really worth it?  In some situations the answer should be a resounding 'yes'.  So, how are they setup?  When would I want to use them?

Solution

How do I setup multiple instances?

During the SQL Server installation process you are prompted for the installation type of either the default or a named instance, as the images below outline.  The default instance is just the server name and the named instance is the server name + $ + instance name i.e. ProdServer$Inst1.  A single SQL Server can have 1 default instance and up to 15 named instances of the relational engine.

SQL Server 2000
SQL2000 Setup
 
SQL Server 2005
SQL2005 Setup

 

Why would I want to use multiple resources?

The sky is the limit when it comes to why you want to use multiple instances and the reasons may be different from organization to organization.  Nevertheless, below outlines some common reasons why multiple instances of SQL Server are used:

ID Business\Technical Driver Description
1 Application functional or load testing Load Testing - Ability to have multiple code bases loaded, coordinate stopping the needed instances to perform load testing, conduct the load testing then restart all of the instances once you are complete.

Functional Testing - Ability to have multiple code bases and be able to test independently with a pre-allocated amount of resources.
 

2 Server Consolidation One of the recent trends is consolidating multiple instances of SQL Server on to a single server.  With multiple SQL Server instances, it is possible to perform full SQL Server backups and restorations to a single instance to maintain the same functionality as the original server.
 
3 Internal Hosting\Departmental Server Sharing With SQL Server originally considered a departmental solution, it is possible to have multiple instances of SQL Server on 1 physical server to equally share the server's resources (large number of CPUs and memory).

This scenario is ideal for charge backs to the departments for organizations following that paradigm.
 

4 Security Multiple instances are ideal for those pesky third party applications that require elevated SQL Server privileges where you do not want to commingle applications on the same instance.
 
5 Demos Multiple instances are ideal to support demo's where it is necessary to demonstrate a particular application and how it affects SQL Server.

 

Next Steps
  • As you are faced with these issues and others, consider multiple instances of SQL Server to help address the issue from a technology perspective.
  • Setup a naming convention for your instances.  Two broad approaches are to functionally name (i.e. AcctFnctnlTesting, ManufacturingTesting, etc.) or enumerate generic names (i.e. Inst1, Inst2, etc.). 
  • With security on the forefront of so many people's minds, we could not help to mention that with multiple instances of SQL Server be sure to patch all of the instances, not just the default or first instance.  Many of the patches need to be applied per instance and not per server.
  • Share your thoughts with the MSSQLTips.com community about how you use or plan to use multiple instances of SQL Server in your environment by sending an email to [email protected].
  • Here is a white paper that dicusses some of the additional things to think about when consolidating servers and how to setup a server with mulitple instances.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Monday, January 9, 2017 - 4:51:29 AM - Raghav Back To Top (45190)

Hi,

 

Can we create the Multiple Instances in SQL 2012 Cluster? Please guide

 


Thursday, June 20, 2013 - 3:20:29 AM - Vlad Back To Top (25499)

Would it be beneficial to install second instance of SQL 2008 R2 on the same node of the 2 noded Cluster in order to  run DataWarehouse DB on on einstance and the rest of the Dbs on the other one. The goal is to make sure that separate Temp Dbs are used for DW and other Dbs. Potential improvement in performance by freeing Temp DB resources.  Thank you.


Thursday, November 8, 2012 - 7:11:54 AM - Jeremy Kadlec Back To Top (20257)

Lumy,

No - I think it is 1 default instance per server.

HTH.

Thank you,
Jeremy Kadlec


Wednesday, November 7, 2012 - 6:10:33 AM - Lumy Back To Top (20242)

Is it possible having more than one default instance when using different versions on the same server? 


Monday, April 5, 2010 - 11:22:14 PM - --cranfield Back To Top (5182)

a 2-node cluster will already have 2 instances of SQL installed if its a LIVE/LIVE.  I wouldn't recommend installing non-clustered named instances on either of the nodes as these will not be able to failover and will need to use local (non-shared) storage.


Monday, April 5, 2010 - 3:22:27 PM - WestCoast917 Back To Top (5181)

 Would it be advisable to run multiple instances of SQL Server on a 2-node Cluster which has high volumes transactions and needs to be 'up' throughout a normal work day?















get free sql tips
agree to terms