Best Practise on Multiple SQL Server Instance Installation on The Same Server

What are some of the pros and cons of installing multiple SQL Server isntances on the same server? What is the best practice on this?

more ▼

asked Mar 31, 2010 at 11:11 PM in Default

Larry Sumuri gravatar image

Larry Sumuri
47 2 2 2

(comments are locked)
10|1200 characters needed characters left

5 answers: sort newest

The major reasons to place multiple instances on a single server are, as stated by my esteemed peers - security, consolidation and reduced licensing costs.

Security isolation between instances eases fears of inadvertant data access betwixt Clients.

Consolidation reduces hardware costs and semi-centralizes management (one SSRS console/one server login). Rather than having under-utilized servers sitting there eating energy you have fewer servers maximizing ROI.

$$$$ :)

On the negative side you do increase the resource contention that may arise. MAX MEM needs to be reviewed/adjusted, placement of data/log files on disks needs review, CPU and network requirements also need to be analyzed.

Generally... installing multiple instances is OK. It does depend on your particular scenario. Plan, Test, Review, Repeat!

more ▼

answered Apr 01, 2010 at 11:07 AM

Blackhawk-17 gravatar image

11.9k 28 31 37

+1 - for the Plan, Test, Review, Repeat. Oh, and the other wise words ;)
Apr 01, 2010 at 11:16 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

We use it here with no real issues. In one example we have a single system with an OLAP and an OLTP database on different instances and replication between them.

In another we have reduced the number of SQL servers to maintain by bringing lots of systems together onto a single instance and we are now moving from that to a new 2008 instance on the same h/w. So for the next year or so we are having 2005 and 2008 on the same box. The actual load isnt any more or less, its just shifting from one instance to another. When we reach a tipping point I will reverse the settings re RAM, CPU etc to be biased towards the 2008 instance as it takes on more load.

I think one of the main points to look for is, are the underlying/shared resources up to the task of running the load - CPU, RAM, HDD (and HDD configuration)?

What is the alternative - all systems on the same instance? If so then another instance will cost you very little (comparatively) in its own resource usage but its another license to buy.

What would you want to achieve by having an extra instance - securtity? That would certainly be more configurable with two instances.

more ▼

answered Apr 01, 2010 at 06:20 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

+1 - but you don't need another license for a named instanct - http://www.microsoft.com/Sqlserver/2005/en/us/pricing-licensing-faq.aspx
Apr 01, 2010 at 06:53 AM Matt Whitfield ♦♦
G'ah, SQL Server licensing - I break out in cold sweats over this every now and again!! Thanks Matt
Apr 01, 2010 at 07:01 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

There is a lot of good advice here, and it is all worth looking at.

Personally, I frequently do this for testing, especially with different versions. I have had 2005 and 2008 sitting side by side on test machines on many instances. I have never had an issue (unless you count the fact that one must be a named instance, but I generally don't see that as a problem.)

As mentioned above there can be some security benefits to having two instances sit side by side in production and it can help reduce licensing costs, but you must think this through carefully if you are going for that. Remember that unless encryption is used there are ways for someone who is a system administrator to effectively bypass most sql server security and that in the default configuration a system administrator is automatically a sql server administrator. You can reap great security benefits using multiple installs, just be sure you realize exactly what those benefits are and where they hit their limitations.

Also, remember that another option is to run virtual machines with installs of Sql Server. I have not tried this myself, but I have read accounts of it working quite well for some people, especially in test environments.

more ▼

answered Apr 01, 2010 at 02:52 PM

TimothyAWiseman gravatar image

15.6k 21 23 32

(comments are locked)
10|1200 characters needed characters left

Just a small thing to add to what Fatherjack said:

The best practice is really to separate instances onto separate disk groups for high volume applications. It is far more often that you will see a SQL Server that is busy because of IO than you will see one that is busy because of CPU. Following that as a general rule leads to separating out the IO to separate disk groups for improved concurrency.

Of course, this does depend on your workload, and nothing can take the place of specific analysis of what the workload on the server is. So... 'It depends'.

more ▼

answered Apr 01, 2010 at 06:56 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

+1 - I started thinking about mentioning all this and then forgot to add it before I hit 'send'. If you are starting on new server then yes certainly config HDDs to match intended SQL installation.
Apr 01, 2010 at 07:03 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

Best practice is "It Depends"

Cons - multiple instances require more resources to be shared between the servers.

Pros - this kind of setup can provide a good division between databases for different clients (for example).

more ▼

answered Apr 01, 2010 at 02:14 AM

CirqueDeSQLeil gravatar image

4.8k 10 11 15

+1 for "It depends" as you are absolutely right! A pro for me its security and memory provisioning, especially in 2005.
Apr 01, 2010 at 04:22 AM sp_lock
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 31, 2010 at 11:11 PM

Seen: 19321 times

Last Updated: Mar 31, 2010 at 11:11 PM