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 tips@mssqltips.com.
- 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.
