| Author |
Topic |
|
Robwhittaker
Yak Posting Veteran
85 Posts |
 Posted - 2004-01-23 : 04:22:22
|
| Hi all,I'm just canavasing opinions (as you lot know far more than me :oD)We're buying a 3rd party product that has a SQLServer database. We also currently have a server that has all our sqlserver databases on it. The 3rd party supplier is requesting that we create another instance of sqlserver on this server, so that their database can have it's own system tables. (I think they mean Master and MSDB databases when they say system tables, but I need to clarify this.)The reason they give is that we can then tune the Master datbase etc specifically to make their database run faster than if more than one database shared these databases. Is this correct or a load of rubbish?ThanksRob |
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-01-23 : 05:19:33
|
| Sounds like rubbish to me, you don't tune the master database or any other database for application or database performance. You could also see a general degradation in performance on your existing databases as the new instance will eat more server memory and be in contention for disk i/o etc with the existing installation.Raymond |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-23 : 12:31:50
|
| Perhaps the vendor didn't explain it clearly. When you split databases onto separate instances, you can then specify how many CPUs and how much RAM the instance is allowed to use. So the benefit will come if you have extra hardware for their instance.Tara |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-01-23 : 19:54:27
|
| ...which is extra hardware TAKEN AWAY from your OTHER databases. It's very suspicious that they recommend a separate instance instead of a separate server, as if they know their database(s) consume a lot of resources but they know no one would buy their product if they needed extra physical hardware. Any single database app that needs its own instance/server has to be a performance hog. And in that case, as Raymond pointed out, it will only make matters worse to divide the resources of one machine between two instances.I'd suggest that unless they tell you EXACTLY what kind of configuration they need that you tell them "no". It's your server, and you have every right to know what they want to do with it. And make absolutely certain they do not use SA as a login, ever. Find out what rights they need and create a separate login for them, whether they have their own instance or not. This is not a negotiable point; if they insist on using SA, look for another vendor. |
|
|
|
Robwhittaker
Yak Posting Veteran
85 Posts |
Posted - 2004-01-26 : 03:57:05
|
| Thanks for the info, I never let anybody use the sa account, even I'm not that stupid :o)So can anybody think of a good reason for doing this? |
|
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2004-02-20 : 14:37:14
|
| What are the advantages of having another SQL instance on the same box?All help appreciated. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-20 : 14:45:49
|
| You can tell the instance to only use a certain amount of RAM and which CPUs to use. So if you've got one server with 4GB and you want one database to use 3GB of RAM and another database to use 1GB of RAM, you can do this with instances.It also allows you to have the same database name on the server. So if you want to host your QA and test environment on the same box, and you don't want to have DBName_QA and DBName_Test, you can put one DBName on the first instance and another on the second instance.Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-02-20 : 14:50:18
|
| Well, if you have a 32 processor box, you can have 32 incredibly inefficient instances running on the same server using only one processor each.:)Besides the obvious though, you can also house multiple environments such as dev, staging, testing, on one machine. Each environment is just a seperate instance. It can also be useful for security purposes. You can have different instances running under different accounts.You have to be very careful when you are doing this to manage your memory and processor space and insure that the overall environment is stable. That can be a nightmare.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
bm1000
Starting Member
37 Posts |
Posted - 2004-02-20 : 14:53:18
|
quote:
Originally posted by rohans
What are the advantages of having another SQL instance on the same box?All help appreciated.
A separate instance has a separate Master Database. You do not have to worry about incompatible security models, or incompatible collating sequences. |
|
|
|
monkeybite
Posting Yak Master
152 Posts |
Posted - 2004-02-20 : 15:51:15
|
| Seperate instances allow us to run different SQL Service pack levels for some apps. For example MAS 500 v6.1 broke on SQL SP3, so we have to run it internally at SP2 until our client springs for the money to upgrade to v6.3. All our other instances are at SP3a+.~ monkey |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-20 : 16:03:32
|
| Also, each instance is protected from the other....if one crashes, the other (well this what they say...never seen it happen) is unaffected....The service pack scenario was pointed out in Brian Knights ADMIN 911 book...It may be that he wants to protect their instance from anything else going on. I would if no other box was available.Brett8-)Oh and MOO |
|
|
|
|
sqlserver instances