Blog
Installing Multiple Instances of SQL on a Single Computer
By Don Kiely | November 28, 2012
One of the most powerful features of SQL Server is the ability to install multiple instances of the SQL Server relational database engine on a single computer. Each instance of SQL Server consists of both shared and instance features, which can save resources on a single server machine. All of the database objects and many server objects-including security settings-are completely contained within each instance. However, there are a few things you need to know before you install multiple SQL Server instances.
You can install one default instance and multiple named instances, or multiple named instances with no default instance. The only difference between the default and the named instance is that to access the default instance you use only the machine name to access the instance; a named instance requires the machine name and the instance name.
Instances other than the default instance must be SQL Server 2012, but the default instance can be SQL Server 2005 or later. Here are a few examples of how you can configure a single computer:
- A default instance of SQL Server 2005, with multiple named instances of SQL Server 2012. Only SQL Server 2012 can operate as a named instance.
- A default instance of SQL Server 2012 with up to 49 named instances, depending on which SQL Server edition you are using.
- No default instance and up to 50 named instances, depending on which SQL Server edition you are using.
NOTE: The limit on the number of SQL Server 2012 instances you can install no longer depends on which edition of the product you are using. All editions across the board now support and allow 50 instances. It used to be that some editions were limited to 16 editions.
Multiple Instance Scenarios
Implementing multiple instances of SQL Server gives you great flexibility in the kinds of SQL Server applications you can develop and distribute. Here are a few examples of how you might find multiple instances useful:
- Continue to run existing applications on earlier versions of SQL Server side-by-side with a SQL Server 2012 server on the same machine at the same time.
- Run multiple instances of SQL Server on the virtual server of a SQL Server failover cluster. When you install SQL Server on each computer in a cluster, you create a named instance to distinguish that installation from the other instances of SQL Server that are on the same virtual server. Clustering is described fully in Failover Clustering in SQL Server Books Online.
- Maintain a separate independent SQL Server environment for development and testing on the same computer that holds your production server. This is not generally a good idea, but if resources are tight it is doable.
- Roll out a secured SQL Server database in a sandbox environment where administrative rights are curtailed. Structure security differently for each instance, with different administrators. One of the problems in past versions of SQL Server for independent software vendors and application developers was that the system administrator in SQL Server is all-powerful. Multiple instances provide a way to curtail administration on a server instance.
- Install software that comes bundled with the Express edition of SQL Server on a machine that already has a running server.
This post is an excerpt from the online courseware for our SQL Server 2012: Installing course written by expert Don Kiely.
