Multiple instances of Sql server

Question

Multiple instances of Sql server

Ahmad Osama

SSCertifiable

Points: 7676
More actions
March 6, 2008 at 4:14 am

#120332

Hi All,
Whats the use having multiple instances of a sql server running on a single server.
Regards,
Ahmad
Regards,
[font="Verdana"]Sqlfrenzy[/font]

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

One reason is isolation of databases. If you want to query between databases in multipke instances, you have to use linked servers or similiar query methods. On larger hardware you can also assign specific processors to specific instances of SQL Server, as well as allocating memory between the instances.

😎

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 2

elaborate pls....

Regards,
[font="Verdana"]Sqlfrenzy[/font]

Steve Jones - SSC Editor SSC Guru Points: 734667 More actions · Answer 3

You don't save in licensing unless you have Enterprise edition. If you have 2 standard instances, you license them separately.

You might want to separate out tempdb usage, perhaps with two instances that require lots of worktables. Or you might have a vendor that wants their own instance for support reasons.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Actually, I just read the EULA for SQL Server 2005 Standard Edition, and you can install multiple instances on the same computer. I think that was a change from previous versions of SQL Server.

{Edit}

Check out this link:

http://www.microsoft.com/sql/howtobuy/multipleinstances.mspx

😎

Steve Jones - SSC Editor SSC Guru Points: 734667 More actions · Answer 5

Thanks! Didn't realize they'd changed Std/Wrkgroup. They must have done tha in the last year or so.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Not a problem. I find myself having to check MS licensing on a regular basis to be sure we stay legal (to the best of my knowledge at least).

😎

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

Steve Jones - Editor (3/6/2008)
Thanks! Didn't realize they'd changed Std/Wrkgroup. They must have done tha in the last year or so.

Please note that there still is a difference in supported instances. Meaning - you can put up to 50 instances on an Ent. install, but some much lower number (16?) for all other versions.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Adam Bean One Orange Chip Points: 26631 More actions · Answer 8

The better question is, would you rather have two separate servers and pay for two sets of licensing, or have them consolidated into one if you had the option?

Instancing provides you with the ability to run multiple SQL servers on one physical server. In a way, it's kind of like virtualizing an operating system. There is much more detail about this, but I'd highly recommend you do some reading on the topic.

If it is a route you take, you need to ensure your hardware can support 2 or more instances. It is usually recommended (depending on load), that you set your min/max server settings, and possibly affinity masking. It all depends on what your purpose and objectives are, as well as what the purpose of the SQL servers are and how highly they will be utilized.