Multiple instances or many databases

Question

Post reply

Multiple instances or many databases

Niyala

SSCrazy

Points: 2778
More actions
October 31, 2008 at 4:31 am

#124583

Hi all,
I understand that this topic has been discussed elsewhere. However, I am lacking a clear understanding of what multiple instance installation is all about.
Does it mean that 2 or more SQL 2005 on the same machine?
Or it applies only if we install, say, 2000 and 2005 on the same machine?
I am facing a decision whether or not to have many databases on the same instance and restrict access to the databases or alternatively to install multiple instances, if I understand well what multiple instance stands for.
I would appreciate any advice as it would greatly help me make a decision about a method of data conolidataion.
Thanks in advance and regards
Niyala

Viewing 7 posts - 1 through 6 (of 6 total)

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

John H Marsh SSChampion Points: 11671 More actions · Answer 1

Hello,

You can have multiple instances of the same version or different versions of SQL Server on one machine.

I would suggest that if your DBs need to work together e.g. one application accesses more than one DB then keep them in the same instance. That way you won’t have to worry about using Linked Servers.

If the DBs are totally independent then you could consider separate instances for each one.

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

You may want to have more than one instance in a box when:

1- You are planing to have different setups in each one

Benefits...

...independent setups may benefit when you have two or more environments that require different tuning, like having an OLTP and a Reporting instance in the same box.

2- You want to isolate the environments.

Benefits...

...reliability: if one instance crashes the other one would not.

...security: login information may be different in each instance.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Johan Bijnens SSC Guru Points: 135361 More actions · Answer 3

Adding to PaulBs reply...

-application lifecycle (do all applications need the same software level and/or can they tollerate a sqlserver instance level upgrade)

- cross database ownership only works within an instance (activate only if needed)

- if you have more than one instance, the instances will have to battle to get cpy/memory/io-cycles,... at OS level. But using e.g. WSRM you can prevent one instance chocking the other one.

- If you have more than one database, you can have the same conflicts but within the instance itself. e.g. a huge query will starve all other queries from execution or at least make them perform slow , causing timeouts,...

(unless you use SQL2008 with DMF which acts like WSRM at SQL-instance level.)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Niyala SSCrazy Points: 2778 More actions · Answer 4

Thanks for the nice explanation.

How about the issue of licensing? If I have many databases in one instance, I guess I have to pay licence fee for a single use?

If I install multiple instances, then do I need to purchase licence for each instance installed? I am not quite sure about this.

Regards

Niyala

Johan Bijnens SSC Guru Points: 135361 More actions · Answer 5

Niyala (10/31/2008)
Thanks for the nice explanation.
How about the issue of licensing? If I have many databases in one instance, I guess I have to pay licence fee for a single use?
If I install multiple instances, then do I need to purchase licence for each instance installed? I am not quite sure about this.
Regards
Niyala

MS has 3 kinds of licensing:

- Device Client access license (CAL): so a pc is allowed to use sqlserver (any number of users)

- User Client access license (CAL): so a user is allowed to use sqlserver (any number of devices)

- processor license: per physical cpu socket (any number of cores) in use for sqlserver

Best is to read the MS doc : http://www.microsoft.com/Sqlserver/2005/en/us/licensing.aspx

downloadable at http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensingv1.1.doc