SQL 2005 servers with multiple instances

I have an interesting configuration that I was hoping others might comment
on. We have 2 SQL 2005 standard servers that need to provide redundancy for
each other, without using clustering. We'll be replicating the databases
from one server to the other, and vice-versa.

Our big question is, should we use multiple instances? The knee-jerk
reaction is to not use instances because of the extra overhead and the
inefficient sharing of memory between the instances. However, I have a few
reasons why it would be beneficial...

First, consider the two servers with many small drives for the 'Live' data
(4 logs, 7 data all 15k), but a small number of large drives for the
'Redundant' data (4 for Data and logs, only 10k). The same RAID controller
is being shared for the Live data and the Redundant data/logs. Also, we're
using processor licensing, so the additional instances won't cost any more.

My concern is the load affecting the Live databases while applying
transaction logs to the Redundant databases, which would be done at least
every 30 minutes. To my knowledge, SQL provides no prioritization, other
than setting proc affinity and boosting priority.

I'm proposing that we use two instances on each server - Live and Redundant.
Server1\Live would replicate to Server2\Redundant, and Server2\Live would
replicated to Server1\Redundant. The Backup instances would be limited to
256MB memory and have its cpu & io affinity set to one of the dual
hyperthreaded processors. This would ensure the Live instance always takes
precedence over the backup instance. Even if everything is at 100%,
theoretically, Live would have 87% of the resources (3 processors + 1/2 of
the fourth). The backup instance wouldn't be doing anything except
restoring logs, so there shouldn't be much loss due to overhead.

Obviously, during a (manual) failover, the memory and affinity would be
adjusted. Not as fancy Clustering, but doesn't have the price tag either.


I totally understand the extra overhead if dealing with two instances that
are very active. But in this situation, I don't think there'd be a problem.
Does anyone have any comments or helpful hints with our configuration?

Thank you,

Chris






.

Relevant Pages

  • RE: BizTalk server Topology & scalibilty
    ... within the BizTalk engine across multiple engines on different servers. ... Hugo points out BizTalk is stateless, that is to say all state is maintained ... only in the SQL server databases (unless one inadvertently starts creating ... > this configuration) servers running SQL in cluster. ...
    (microsoft.public.biztalk.server)
  • Re: SQL 2005 servers with multiple instances
    ... Have you checked out the latest possible hardware configurations for SQL ... I have built two-node clusters with dual-proc dual core brand name servers ... Clustering is no ... controller is being shared for the Live data and the Redundant data/logs. ...
    (microsoft.public.sqlserver.setup)
  • Re: Small Redundant web/mail setup
    ... Subject: Small Redundant web/mail setup ... would serve these files via nfs to the application servers. ... get good hardware. ...
    (freebsd-questions)
  • Re: Roaming profiles
    ... This can potentially slow down the session and depends on how much your applications go back and forth with that folder. ... Hopefully that makes a bit of sense but you are doing things correctly with your setup and I would recommend going a step further and redirecting your My Documents folder. ... I have an an issue I would like to clean up on some TS servers that I'm hoping someone can help with. ... I would like to remove this as it is redundant witht he roaming profiles and it seems to be slowing the servers down not to mention errors in the eventvwr about offline caching. ...
    (microsoft.public.windows.terminal_services)
  • Re: Small Redundant web/mail setup
    ... Subject: Small Redundant web/mail setup ... I need to setup a high-availability setup for mail/web setup ... would serve these files via nfs to the application servers. ...
    (freebsd-questions)
Loading