Share via

Multiple manage instances on the same VM Server

Ashwan 536 Reputation points
2020-12-16T22:54:02.067+00:00

Hi I am looking to to configure multiple instances on same VM to same license perspective. I have few questions to clarify. different instances for different applications

SQL Server 2016 and above versions

  1. How many instances can have on one VM- According to the MS best practice . (I understand need more CPU +memory if we wish to have multiple instances )
  2. Q1 applicable to Always on configuration setup (Multiple instances with AG) or standalone instances
  3. if any issue with one instance may impact on other instances or global VM level. (I understand instance level memory we can limit mx/min level)
  4. do we have to have data/log on different disks
    ex: instance 1 data U1: Log L1:
    instance 2 data U2: Log L2:
    instance 3 data U3: Log L3:

or MS recommended to have on all data files on U: drive and logs in L: drive

Any expert advice would be highly appreciate .

thank you

Developer technologies | Transact-SQL
SQL Server | Other
Accepted answer
  1. Ashwan 536 Reputation points
    2020-12-17T21:37:19.677+00:00

    Great thanks Ben so up any limitation of number of SQL instances per VM server wolud be how much as per the MS best practices
    Secondly if wish to upgrade lets say version 2016 -> 2017 or SP1- SP2. can we do separate instances by instances ? or which individual instance maintain separate binaries ? What would the approach?

    regards
    Udara

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ben Miller (DBAduck) 966 Reputation points
    2020-12-17T04:40:12.89+00:00

    Yes, you can have multiple instances on a single VM.

    Key things to do.
    Always have separate folders for each instance.
    Do not put all files in F:\SQLDATA as the first install will put it's own permissions on that folder and the second install will redo permissions. So have something like this:

    • Separate Drives for each instance
    • Or Separate Folders on the same drive so SQLDATA01 and SQLDATA02 and LOG01 and LOG02 or something like that to keep permissions isolated.

    For management of the data and logs, etc. I would recommend using separate Drives as to not have one instance run the drive out of space and the other instance die because of it.

    Set Max Server Memory to be something that the instances share, so you may want to split the RAM as necessary so if you have 32 GB of RAM, may be you give 16 GB to one and 10 to the other, but do NOT starve the OS as this will impact your memory for connections and you will run out of connection memory having 2 instances on the server.

    Are there any other questions?

  2. CathyJi-MSFT 22,396 Reputation points Microsoft External Staff
    2020-12-18T07:32:21.033+00:00

    Hi @Ashwan ，

    > Secondly if wish to upgrade lets say version 2016 -> 2017 or SP1- SP2. can we do separate instances by instances ?

    Yes, you can upgrade the instance separately.

    >configure multiple instances on same VM

    Suggest you read the blog Q&A: Multiple SQL Server Instances on a VM and Should I Install Multiple Instances of SQL Server? The two blogs will share us the information about Benefits and Drawbacks of this situation.

    Best regards,
    Cathy

    If the response is helpful, please click "Accept Answer" and upvote it, thank you.

  3. David Browne - msft 3,851 Reputation points
    2020-12-18T14:20:24.89+00:00

    The best practice is to run the as few instances as possible. More is definitely worse: more complex, more risky, less efficient. One instance per major version you must support, is a good starting point.

    0 comments
  4. Ashwan 536 Reputation points
    2020-12-22T04:13:07.727+00:00

    Hi David , Thank for that. That is good information

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.