December 10, 2004 at 12:54 am
Hi everyone,
May I know what are the limitations in the multiple instances of SQL SERVER?
Dinesh Asanka
My Blog:
December 10, 2004 at 3:18 am
Could you expand the question a little?
If you mean the maximum number then sql 2000 is limited to 16 instances. Each is licensed individually.
December 10, 2004 at 3:22 am
that is one aspect
are there any performance issues?
My Blog:
December 10, 2004 at 6:51 am
Yes, there can be many performance issues that will need to be looked at and resolved. The top top three are Memory, CPU and Disk. Each one has to be looked in conjuction with the type of applications you intend on running. Are the heavy online, batch, combination? Are all the applications the same?
I am a SQL and Oracle DBA. We tend to need more memory with the SQL applications than Oracle applications. The amount of meory will also relate to the disk activity and how well you code reuses the code in memory. Most of our small to medium sized SQL applications need 512meg to 1.7gig memory. So you could be limited to 1 -3 applications even if your server has 2-4 gig of ram. Don't forget to leave enought memory to run the OS and any software components that might be on it.
Second is the disk. Again the design of the applications with have a big impact. The the database and applications are efficient in using memory, reuse of code and good indexing, the disk usage and utilization will be lessoned. There can be a big impact on the design and layout of the hardware. Will the controller be 1 or 2 channel, how many disk, raid type, ect.
3rd is CPU. We typically don't share a server unless it has 2 - 4 cpu's. With a 4 cpu box have typically have one "important" application and let it use 3-4 cpu's while the others are limited to 1 or 2 cpus.
Not an easy question to answer. You will also need to look at network utilization. If you don't have a big enought pipe going out of the box, you will bottle neck there and the increases in 1-3 will mean little.
December 11, 2004 at 9:35 am
The performance impact will largely depend on what both the instances will be used for. There is very less overhead by just adding a new instance which is sitting idle. From the link below, SQL can run on 64 MB also, (i think i can take that as a baseline for the max. overhead as far as memory is concerened for an idle instance)
http://www.microsoft.com/sql/evaluation/sysreqs/2000/default.asp
Regarding multiple instances, SQL server manages memory dynamically and will try to grab memory as required and will release later on. If you say install two instances on the same server, you can restrict the max. memory used by both. The advantage being that you can assign more memory to the instance which is heavily used and fine tune your resources. It also has a disadvantage. Say if you have 2 GB of memory and one of your instance is used only during off-peak hours for reporting purpose.So you assign 1 GB each to each of your instance.Hence the max. memory is only 1GB. Instead of this, if you had only one instance, it could use 2 GB of memory and it would be an advantage as your requests are staggered over different time intervals. Lastly, i would disagree with the comment below.
"We tend to need more memory with the SQL applications than Oracle applications"
SQL has a tendency to capture as much memory as possible. But this does not mean that it is using all the memory. It will only release when another applications requests.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply