Pros vs. Cons of Instances - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Pros vs. Cons of Instances

Author

Topic

pithhelmet
Posting Yak Master

183 Posts

Posted - 2007-10-10 : 13:42:38

Hi everyone -

Are there any straight forward reasons why one would choose to have a few instances of databases over keeping all the databases under one (the default) instance??

thanks
tony

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2007-10-10 : 14:08:08

Yes.

Kristen
Test

22859 Posts

Posted - 2007-10-10 : 14:16:43

Name two?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-10 : 14:27:21

We use multiple instances for maintenance and performance reasons. Using multiple instances permits you to have different downtime windows when the databases are split up. It also allows you to choose how much memory and CPU each instance has, so if you find a database that is hogging memory, you can split it to another instance and configure the max memory setting so that it doesn't interfere with other databases on other instances.

Multiple instances are required on clusters in active/active environments.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-10 : 16:45:01

i'm wondering if he means server instances or
database instances (more than one db with same structure, maybe for storing old data).

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-10 : 16:52:43

quote:
Originally posted by spirit1

i'm wondering if he means server instances or
database instances (more than one db with same structure, maybe for storing old data).

He mentioned a default instance, which applies to server instances.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-10 : 16:55:19

ok...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-10-11 : 09:42:52

Multiple Databases or Multiple Instances?
http://www.sqlsolutions.com/articles/articles/Multiple_Databases_or_Multiple_Instances.htm

pithhelmet
Posting Yak Master

183 Posts

Posted - 2007-10-11 : 10:45:24

Yes - i did mean multiple instances - multiple named instances

I currently have over 700 databases running under a single, default instance in the server.

Each database has the same structure, in a non-normalized structure (almost denormalized)

The answer(s) posted will lead to a possible restructure of the database system.

I was thinking about creating a named instance for each state, and move the databases
into the proper state.

thank you

take care
tony

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-11 : 11:47:40

So you're the one who sent me an email regarding 700 databases on one instance. IIRC, you've only got something like 2GB or 4GB of memory. That is not enough for this many databases! And you shouldn't even bother with installing multiple instances until you add more memory.

How big are the databases?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

pithhelmet
Posting Yak Master

183 Posts

Posted - 2007-10-11 : 16:28:23

Yes - it is me,

The MDF(s) in the data directory
615 File(s) 107,553,357,824 bytes

The LDF(s) in the data directory
613 File(s) 11,298,013,184 bytes

Dual Xeon
3.0ghz
6gb of ram

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-11 : 16:31:15

That's about 100GB of databases. You need to add a lot more memory.

Don't even bother contemplating multiple instances versus one default instance until you've added more memory. Multiple instances will just make performance worse at this point.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-10-11 : 16:40:10

"So you're the one who sent me an email regarding 700 databases on one instance"

What's the point of that? ... unless they were offering you some consultancy I suppose ...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-11 : 16:46:35

Yeah I just ignore the emails. People should just create threads here and if they want me to look at their thread, then send me the link to the thread. I will not answer questions in email unless I know who you are already or have worked with them in the past. I don't want most people to have my email address. Plus posting a thread here gives visibility to other possible answerers rather than just me.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

pithhelmet
Posting Yak Master

183 Posts

Posted - 2007-10-11 : 16:53:28

The performance seems to be ok -

but we have this thing that does bit level replication to a standby machine
that is consuming 50% of the physical memory....

projections indicate a 100% growth before the end of the year...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-11 : 16:54:26

You need to purchase better hardware then.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-10-11 : 17:08:10

"Plus posting a thread here gives visibility to other possible answerers rather than just me."

Well, that's obvious to the both of us ...

Is it just me or is "we have this thing that does bit level replication to a standby machine
that is consuming 50% of the physical memory...." scary?

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-11 : 17:18:52

Yep. We'd never be able to run something like that if it was going to consume 50% of the memory. Talk about a performance issue!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

pithhelmet
Posting Yak Master

183 Posts

Posted - 2007-10-12 : 08:57:46

well, that is what there is, and, i cannot change it.

While i am sure that upper mgmt has a reason for spending
the money they did on the stuff they have - it isn't for me to
say; and i can only make suggestions on the direction that can be taken
in future routes (i.e. the question that was posted)

thanks for the spirited conversation

take care
tony

Kristen
Test

22859 Posts

Posted - 2007-10-12 : 09:36:25

I've never quite understood the need for the bit-wise-disk-replication I read about periodically on here ... but then I suppose the people who have such things working flawlessly aren't posting on here, so I only see the ones that have Gone West ...

Kristen

OldHippy
Starting Member

5 Posts

Posted - 2007-10-12 : 16:45:38

On the down side with multiple instances, service packs or patches often
have to be applied to each instance. 50 times the work for 50 instances.
SQL Server Maintenance plans already allow you to schedule maintenance
using different downtime windows.

What ever you do, you certainly have your work cut out for you.
Here's an article on consolidating DBs that discusses other pro's and cons
[url]http://www.mssqltips.com/tip.asp?tip=1320[/url]

Mike

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources