none
Multiple SQL Server installations on different machines vs multiple instances

    Question

  • Hi,

    i've got a question concerning the topology of SQL Servers in a enterprise. Until now we had a single SQL Server to serve the need of user databases throughout the enerprise.
    Now were facing a change of our infastructure: The servers will be moved to a Hyper-V machine and we are going to install some application servers, that need SQL Server as backend: SharePoint, System Center Essentials, Data Protection Manager. At least one, DPM, says it is to be designed to run on a dedicated server controlling its own SQL Server.
    Now we think of giving each of this servers its own SQL Server. Is this a way to go? Or is it absolute nonsense? Should we try to force all the servers to use named instances on a single SQL Server? Sometimes i have the feeling that SQL Server becomes a part of the OS, so just install it and let the application (SharePoint, DPM, you name it) take over control.

    Any advice or opinion is highly appreciated.

    Thank you,
    Manuel

     

    Tuesday, October 26, 2010 9:46 AM

Answers

  • Hi Manuel,

    We have over 2000 instances running in our environment. We try to decide the best placement of a db, regarding the classification I told you above. I agree that it is not always easy to make the best decision and that it is not always clear on how to create your SQL server infrastructure.

    For our critical applications, we generally use a 2- or 4- way cluster, heavy machines with multiple instances and multiple db's per instance.

    For acceptance, we generally use stand-alone machines with a limited amount of instances (1-3) and multiple db's per instance.

    For design/sandbox we generally use virtual servers.

     

    I hope this gives you enough info. If you have specific questions, you can either post them in the forum, or you can contact me private.

    Tom


    Tom Van Zele | Blog | Twitter | LinkedIn
    Thursday, October 28, 2010 11:46 AM

All replies

  • Hi Manuel,

     

    SQL server is build for using multiple DB's on a single instance, so I would not give every DB it's own instance (overhead will be huge then).

    In our company we use a sort of classification to decide on which server/instance a db should go. We decide on a couple of factors: whether it is dev/acc/prod, whether the app needs HA or not, the type of DB (OLTP, Reporting, ...), ....

    Hope this helps,
    Tom


    Tom Van Zele | Blog | Twitter | LinkedIn
    Tuesday, October 26, 2010 11:29 AM
  • Hi Manuel,

    Simple thing

    Advantage of having single machine and Single SQL server:

    1) Easy to identify

    2) Easy to manage

    3) Good performance

    4)Suitable to Production.

    Advantage Of Single machine and Multiple SQL instance:

    1) Low Cost

    2) Suitable to testing & development environment.

    Disadvantage Of Single machine and Multiple SQL instance:

    1) Poor performance

    2)Difficult to manage.


    Regards, RM Thirunavukkarasu MCP, MCITP, ITIL V3 | http://thiruna.blog.com |Click "Propose As Answer" if solution is helpful to you.
    Tuesday, October 26, 2010 11:50 AM
  • Hi Tom,

    thank you for your answer.

    On our existing server we operate like you said, a couple of (user-)databases on one (default) instance. 

    But now we will change the infrastructure: moving to a virtual environment (hyper-v) and adding some servers to the mix, that will need SQL Server as Backend. Our thoughts are: Should we stay on one mighty mighty SQL Server and let all these other server (SharePoint, SCE, DPM) use this single machine as backend, perhaps divided in named instances? One resulting disadvantage here could be the single-point-of-failure-situation.

    Or should every server get his own SQL Server on his dedicated machine? Disadvantages here: costs and manageability (updates, backups etc.)

    your answer makes me think that you operate more than one singe SQL Server machine in your enterprise. If this is the case, are you happy with it?

    Thnaks again,

    Manuel

    Wednesday, October 27, 2010 9:41 AM
  • Hi Ramasamy Users MedalsUsers MedalsUsers MedalsUsers Medals,

    Thank you for your answer.

    As you can see from my reply to Tom's post, it is not so simple for us.

    But one qustion concerning you answer: Why are there performance penalties when using named instances? And how serious will they be?

    Thanks again,

    Manuel

    Wednesday, October 27, 2010 9:53 AM
  • Hi

    The performance penalty does not come from the use of named instances but from the use of more SQL Server instances one the same windows server. Each SQL Server instance has its own separate memory allocation - this is not shared between instances.So you may have to add RAM if you will run multiple instances on the box.

    Henning

    Wednesday, October 27, 2010 9:18 PM
  • Hi Manuel,

    We have over 2000 instances running in our environment. We try to decide the best placement of a db, regarding the classification I told you above. I agree that it is not always easy to make the best decision and that it is not always clear on how to create your SQL server infrastructure.

    For our critical applications, we generally use a 2- or 4- way cluster, heavy machines with multiple instances and multiple db's per instance.

    For acceptance, we generally use stand-alone machines with a limited amount of instances (1-3) and multiple db's per instance.

    For design/sandbox we generally use virtual servers.

     

    I hope this gives you enough info. If you have specific questions, you can either post them in the forum, or you can contact me private.

    Tom


    Tom Van Zele | Blog | Twitter | LinkedIn
    Thursday, October 28, 2010 11:46 AM