You read the title and are still confused? Well I am too.
I've just started a new job as DBA for a new employer and have come across some creative ways of installing SQL Server. My previous experience with SQL Server is all based on single MSSQLSERVER instances running on either virtual or physical hardware. We used to avoid multi-instance installations of SQL Server just to keep everything really separated and simple.
Here at my new employer they have bunched quite a few instances of SQL Server Standard Edition on one piece of virtual hardware. Their (well I guess I should now call it, our...) reasoning:
- Having multiple instances of SQL Server on one piece of (virtual) hardware reduces the amount of SQL Servers and license costs in the whole environment.
I haven't found any other reasoning behind such a configuration yet.
There are absolute no availability groups or transactional replication going on and transaction log shipping has not been implemented.
The servers have been configured to have the default instance and multiple additional instances as explained below.
SQL Server Environment
The SQL Servers are configured to contain multiple instances.
Server to instance relationship
Each SQL Server can have 1 to n instances
SQL_SERVER_01 (Standard Edition SQL Server) \ MSSQLSERVER (default instance) \ VARIOUS_INS (the 2nd instance) \ SOMETHINGNW (the 3rd instance) \ A_NAMEGIVEN (the 4th instance) \ INSTANCENEW (the xth instance)
Instance | IP | Port | Alias (CNAME)
Each instance is related to an IP address and each IP address has an individual Alias (CNAME) so that the SQL Server can always listen on port 1433. This simplifies the firewall configuration as the rules only have to be added for the default SQL Server port. Hmmm.
MSSQLSERVER | 10.0.0.22 | 1433 | SQL_SERVER_01_I00 VARIOUS_INS | 10.0.0.23 | 1433 | SQL_SERVER_01_I01 SOMETHINGNW | 10.0.0.24 | 1433 | SQL_SERVER_01_I02 A_NAMEGIVEN | 10.0.0.25 | 1433 | SQL_SERVER_01_I03 INSTANCENEW | 10.0.0.26 | 1433 | SQL_SERVER_01_I04
So for each SQL Server instance running on the same virtual server the network team has to provide an IP address for the virtual NIC and create a CNAME/Alias for the instance's IP address. Each virtual NIC has to be configured for the correct IP address and the SQL Server Configuration has to be correctly configured for each instance's IP address (listen on this IP address, active for this IP address, ....). The SQL Server will not respond to the typical SERVER\INSTANCE notation, which means the servers are only reachable via the Alias/CNAME (e.g. SQL_SERVER_01_I00)
The (virtual) hardware
Before I forget, I think it might be a good idea to give you an idea of the typical virtual hardware configured for such a bunch of SQL Server instances.
The virtual disks are pre-configured in VMware and attached to the SQL Server. Some hardware vendor in the background. Could be IBM, could be Hitachi, .... A disk for the MDF files and a disk for the LDF files.
Yes, multiple processers. In this example four logical processors @2.9 GHz
A mere 32GB for this server. Each SQL Server instance is configured to consume between 1GB and 4GB of memory. This server has for example 6 instances, each containing between 1 ... 10 databases varying in size from a few 100 MBs to a couple of GBs. Nothing massive.
SQL Server Instance Configuration
Each SQL Server instance will be configured as follows.
max degree of parallelism
Min Memory will be set to 256 MB and Max Memory to between 1GB and 4GB.
cost for max degree of parallelism
From my experience, I learnt that having single instances is the best regarding configuration settings and when analysing issues. But this doesn't seem to be an option here. So no need to start discussing in that direction. I know.
I figure that having only 4 logical processors and MAX_DOP set to 0 for all seven instances and the multiple databases in the background is a bad idea. If one system lags, then they are all going to lag seriously.
Seeing as you got to know my environment, I guess somebody out there will have a similar configuration and will be able to provide me with some script to analyse everything or be able to point me in the right direction towards recommendations.
- Should I have at least one logical processor per instance, given that I currently only have 4 logical processors for 6 instances and MAX_DOP set to 0?
- If I had one logical processor per instance, should I leave MAX_DOP at 0 or limit each instance to MAX_DOP = 1?
- Seeing as it is a Standard Edition should I limit MAX_DOP to 4 as an alternative?
I'm not in a hurry and I do have some time to spend. I'm just curious if anybody has been in the same situation as myself and how you/they came to deal with the situation.
Thanks for your time.