March 25, 2008 at 7:02 am
Hi,
My company has many SQL Server "instances", which I am told is our standard.
Would someone explain the following:
- Exactly what an instance is?
- Why would one use instances?
- The advantage of instances?
- Give a common scenario or example that would dictate the use of instances
- What's the alternative to instances?
THANKS so much in Advance!
~Scott Man:w00t:
March 25, 2008 at 7:32 am
An instance is an extra copy of the tools that run, the database controller, the SQL Server Agent, etc.
We use one instance and the reasoning for it is security. We have a time clock and an HR management system that needs to have the sa password programmed into them.
I don't want my sa password transmitted across the network through the time clock lines, as someone could theoretically unplug the time clock and plug their machine in, start capturing packets, and walla get the password. The instance uses a different sa password.
We also have all kinds of logging set up to capture who connects to that instance, when, how long they are on there, what they do etc. as HR information is confidential, and even myself as the DBA really doesn't have business to be getting in there on a regular basis, if ever. The network guys can monitor me through the event log on this.
In most cases you don't need an instance, just a different database. I guess you could do them as instances, but if they are different databses you can simply browse them all at once, and you can do cross joins on them, for instance if you're storing information in one database that links to a different database, if they are on one instance you can connect them together with inner joins in a query to compare information.
If you have them on different instances you can't. I'd say unless you HAVE to have ALL information seperated, and don't want them near each other, and can't have anyone looking at the different sets of informaion, yet they need to have sysadmin rights, there's no need for different instances, just set your permissions up properly on the databases.
- Jon
scott dunn (3/25/2008)
Hi,My company has many SQL Server "instances", which I am told is our standard.
Would someone explain the following:
- Exactly what an instance is?
- Why would one use instances?
- The advantage of instances?
- Give a common scenario or example that would dictate the use of instances
- What's the alternative to instances?
THANKS so much in Advance!
~Scott Man:w00t:
March 25, 2008 at 7:49 am
Jon,
Good info, and thanks for taking the time to reply!
Best,
Scott
March 25, 2008 at 7:57 am
no problem, I too was a newbie, not too long ago, it's amazing how fast you can learn things when the responsibility is put on your shoulders. 🙂
March 26, 2008 at 9:17 am
Another reason to have multiple instances is if you need different collations. In my company we develop SW that interfaces with different General Ledger SW and each of the GL's have a different SQL collation.
You might want to read more on collation here:
March 28, 2008 at 9:26 am
Yet another reason to have multiple instances on a single physical server is you can reduce your hardware inventory. We have a development instance and a demo (QA) instance on one server because neither instance needs enough server resources to warrant having it's own server. Each instance has identically named databases, which you can't have in a single instance.
Greg
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply