OpenText often installs with two SQL Server databases. One database is for OpenText and the other database is for Microsoft SharePoint Server. This often leads to random acts of installation including putting two or more instances of SQL Server on the same computer. This situation should be avoided for several reasons:
- Each instance consumes a license, increasing your total cost of ownership
- Instances do not share memory and other resources with each other.
- Poorer performance.
- Less scalability.
- Physical drive contention between instances.
A solid rule of thumb is to have just one SQL Server instance per machine to get the best performance. In this series of posts I will cover:
- How to determine if you have multiple instances on one machine.
- How to move a database from one instance to another instance on the same machine.
- How to disable extra instances.
- How to uninstall extra instances.
This type of issue can occur with SQL Server instances running on multiple virtual machines on the same machine. There are valid reasons for running SQL Server on a virtual machine, but it should never be done unless there is a solid requirement.
Determining the number of SQL Server instances on a machine
The steps to determine the number of installed instances on the same machine are shown by the visual step-by-step below.
- Under Control Panel, click Adminstrative Tools, then click Services.
- The Services dialog will appear. Scroll down until you see SQL Server listed
- Count the number of SQL Server(name) you find — in this case we see three.
- There may be technical reasons for needing multiple instances; if this is the case, consider moving the instances to different physical or virtual machines.
- A good practice is to:
- Make a copy-only backup of each of the databases on all of the instances.
- Install the backup copies on a test machine running just one SQL Server instance
- This instance should be configured to match your destination instance.
Verify that nothing breaks, different databases sometimes require different server configurations.
Multiple instances of SQL Server running on the same machine is an easy way to lose performance and scalability. Often when addressing tuning issues, we focus on what is happening inside of a SQL Server and we miss the forest of instances sitting on the same box. The above steps take less than a minute to perform and clarify if there is an issue.