February 20, 2002 at 1:21 am
HI,
I'm currently looking at the configuration for a new SQLServer and am not sure how to calculate overhead of SQL instances. As far as I can tell the only extra overhead is disk space for the installation and the extra memory that SQL uses on the box to run the instance (which I think is around 30mb per instance). Is this a good guideline to follow or are there are overheads that I'm unaware of. Obviously I dont want to create an instance per database but if the overhead is minimal then I'd be more inclined to create more instances.
regards
Craig
February 20, 2002 at 4:15 am
Another is that each instance will fight to gt the maximum memory it can and try not to give it up, unless you set a fixed amount for it to use.
Also make sure you license is a per instance license and not per Processor with enough client licenses for each, cause it is costly if you get caught not in compliance.
Another item is drive contention if all you DB's are on the same drive/array as each SQL server will access the drives without regard to the other instances.
Also consider you have to maintain each server instance (backups, user accounts and such).
Finally, is there a specific reason to create seperate instances? You gain no performance out of individual databases by doing this, just wasting RAM, if anyone can say otherwise can you point me to a benchmark on it.
February 20, 2002 at 9:51 am
I tend to agree with Antares. The main reason this was developed was to allow MSDE to function like access. Every app that uses it can install itself without a conflict. I'm not sold ir sure there is a good reason to use it on a server.
Steve Jones
February 20, 2002 at 7:24 pm
The only reason we use multiple instances is to get an Active/Active cluster. In the case of a fail-over, both instances will be running on one server. But generally, the instances run on their separate servers. Is there a reason why one instance of SQL Server will not suffice?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 20, 2002 at 11:16 pm
The main reason I am thinking of using 2 instances is that we are installing Siebel with a whole load of our other databases. I want to ensure that Siebel doesnt kill the server so will limit the memory allocation to it. Also Siebel seems to require binary sort order which to my limited knowledge is not the default (is this correct? or should I take this question to another forum?).
February 20, 2002 at 11:20 pm
Binary sort order is not the default, but under SQL 2K, you can do that at the database level.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 21, 2002 at 4:27 am
Just a note but memory is not the only way to hang a server. The most other common problem I see is CPU utilization for extended periods of 100% usually due to expensive queries or errors causing infinite loops. Also once memory hits it level the CPU will start fighting to handle and will hit high utilization so even in a second memory controlled instance you can step on the server as a whole anyway.
February 21, 2002 at 11:07 am
Brian and Antares are right. You can change the sort order at the db level and not affect other dbs.
CPU is more often, IMHO, what kills a server. If you truly do not want these applications to hang each other, put them on separate boxes. You have to license separately anyways. The cost of another server could easily outweigh a bunch of complaints over service availability. Separate instances, are not worth it, IMHO.
Steve Jones
February 21, 2002 at 4:59 pm
I made use of an instance for a while to do testing - needed to test changes being made on the real hardware that would be used in production but the way the app was written there was no way to get it to NOT update production db's. By using an instance I was able to replicate the entire set up and test without worrying about production data. I think this is a valid use near the end of testing. Other than that we have a low end box that serves as the dev server and pretty common to be running the personal editional locally as well.
Andy
February 26, 2002 at 7:50 pm
quote:
If you need to restart one of the instances of SQL Server
Which raises the question of how often is it necessary to restart SQL Server? I'm not sure the extra overhead is worth it except in specific cases. The instances of sharing resources unless there is a massive configuration effort, so one instance can effectively hammer a server.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 27, 2002 at 10:46 am
I agree with Brian. How often would you need to restart an app without affecting others? If you need to restart an instance, it likely is already affecting the other apps and I'd prefer a reboot to clear memory and ensure no leakages to a restart.
Steve Jones
February 28, 2002 at 11:01 pm
We have four Sql 7 servers and more on the way. (Each represents a different development effort; one is a web backend, another for special HP-midrange apps, etc) We were wondering about using instances as a way of consolidating all these physical boxes into one or 2 big boxes. What do you think? Can you recommend a better approach?
TIA,
Bill Salkin
March 1, 2002 at 4:50 am
All depends on the amount of data, the number of read/writes average, network throughput and mostly the physical configuration of the server. But as far as instances you actually are creating extra administration issues and the other issues noted thru this thread. You would be better in a consolidation to hve one instance with multiple DBs from each of your old situations. If you would like to tell us about your current databases (size, reads, writes, reads compared to writes, and other aspects of how they are accessed) I am sure we can make suggestions on how to consolidate and the type of setups that will bennefit you most (with reasons why).
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 1, 2002 at 11:11 am
Tend to agree with Antares. If these are different development efforts, might be easier to have one server with mutliple databases. Less admin and licensing as well.
Steve Jones
March 3, 2002 at 8:45 pm
Another thing to consider is the hardware angle... both Compaq and Sun are starting to produce "blades." With so many vendors requiring servers to be dedicated to their application, this is one method of server "consolidation."
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply