Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How and why would I use multiple instances of SQL Server


By:   |   Last Updated: 2006-08-25   |   Comments (6)   |   Related Tips: More > 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

 
SQL Server 2005

 

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.


Last Updated: 2006-08-25


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, January 09, 2017 - 4:51:29 AM - Raghav Back To Top

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

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 08, 2012 - 7:11:54 AM - Jeremy Kadlec Back To Top

Lumy,

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

HTH.

Thank you,
Jeremy Kadlec


Wednesday, November 07, 2012 - 6:10:33 AM - Lumy Back To Top

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


Monday, April 05, 2010 - 11:22:14 PM - --cranfield Back To Top

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 05, 2010 - 3:22:27 PM - WestCoast917 Back To Top

 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?


Learn more about SQL Server tools