High Availability Options

From what I've read, there seems to be 4 ways to ensure that if your main
SQL Server 2005 machine dies, that you can have a backup machine
up-and-running relatively quickly.
1. Backup the databases on the master server and restore them to the backup
server on a regular basis using a maintenance plan.
2. Setup database replication, with the master server being the distributer
and publisher, and the backup server being the subscriber.
3. Use the unsupported database mirroring functionality in 2005.
4. Create a virtual server on a cluster.
Any others that I may have missed?
Now onto the pros and cons of the 4 choices, from what I can see anyway.
1. Slow and quite labour intensive when it comes to maintaining the backups,
but simple to implement.
2. A pig to setup, but then everything seems to go smoothly. Probably good
if using a single subscriber against multiple publishers.
3. Seems pretty straight forward and supports rollover, but is unsupported
and requires some jiggery-pokery to get it going.
4. The bees knees. Handles everything well but expensive.
OK, so I sort of summarised the above. There are other pros and cons that
are just too minor to deal with.
I'd like to go with 4, as I have a system with 150 databases that are in use
24/7, but I also have a limited budget and can't be forking out for a pair
of high-end servers and fiber connected SAN.
Any advice or suggestions?
TIA.Hi Andrew
Database mirroring is supported as of SP1.
One option you have missed is traditional log shipping.
For clustering there are multiple types, giving different levels of local
and failover support.
It sounds like you have already made up your mind based on cost! You may
want to read http://msdn2.microsoft.com/en-us/library/ms345600.aspx
John
"Andrew Hayes" wrote:
> From what I've read, there seems to be 4 ways to ensure that if your main
> SQL Server 2005 machine dies, that you can have a backup machine
> up-and-running relatively quickly.
> 1. Backup the databases on the master server and restore them to the backup
> server on a regular basis using a maintenance plan.
> 2. Setup database replication, with the master server being the distributer
> and publisher, and the backup server being the subscriber.
> 3. Use the unsupported database mirroring functionality in 2005.
> 4. Create a virtual server on a cluster.
> Any others that I may have missed?
> Now onto the pros and cons of the 4 choices, from what I can see anyway.
> 1. Slow and quite labour intensive when it comes to maintaining the backups,
> but simple to implement.
> 2. A pig to setup, but then everything seems to go smoothly. Probably good
> if using a single subscriber against multiple publishers.
> 3. Seems pretty straight forward and supports rollover, but is unsupported
> and requires some jiggery-pokery to get it going.
> 4. The bees knees. Handles everything well but expensive.
> OK, so I sort of summarised the above. There are other pros and cons that
> are just too minor to deal with.
> I'd like to go with 4, as I have a system with 150 databases that are in use
> 24/7, but I also have a limited budget and can't be forking out for a pair
> of high-end servers and fiber connected SAN.
> Any advice or suggestions?
> TIA.
>
>|||I personally don't consider replication a valid High Availability solution,
despite what MS says. The simple fact that it won't automatically replicate
new objects is enough for me to stop considering it. There are other gotchas
as well, this is just the tip of the iceburg.
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:u8y735YKHHA.4376@.TK2MSFTNGP03.phx.gbl...
> From what I've read, there seems to be 4 ways to ensure that if your main
> SQL Server 2005 machine dies, that you can have a backup machine
> up-and-running relatively quickly.
> 1. Backup the databases on the master server and restore them to the
backup
> server on a regular basis using a maintenance plan.
> 2. Setup database replication, with the master server being the
distributer
> and publisher, and the backup server being the subscriber.
> 3. Use the unsupported database mirroring functionality in 2005.
> 4. Create a virtual server on a cluster.
> Any others that I may have missed?
> Now onto the pros and cons of the 4 choices, from what I can see anyway.
> 1. Slow and quite labour intensive when it comes to maintaining the
backups,
> but simple to implement.
> 2. A pig to setup, but then everything seems to go smoothly. Probably good
> if using a single subscriber against multiple publishers.
> 3. Seems pretty straight forward and supports rollover, but is unsupported
> and requires some jiggery-pokery to get it going.
> 4. The bees knees. Handles everything well but expensive.
> OK, so I sort of summarised the above. There are other pros and cons that
> are just too minor to deal with.
> I'd like to go with 4, as I have a system with 150 databases that are in
use
> 24/7, but I also have a limited budget and can't be forking out for a pair
> of high-end servers and fiber connected SAN.
> Any advice or suggestions?
> TIA.
>|||"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:ORRSpucKHHA.2028@.TK2MSFTNGP03.phx.gbl...
>I personally don't consider replication a valid High Availability solution,
> despite what MS says.
Hmm, last I heard MS doesn't consider it a valid HA either.
Replication is great for spreading out load, but not really an HA option.
> The simple fact that it won't automatically replicate
> new objects is enough for me to stop considering it.
Honestly, that's not a big issue (and I thought as of SQL 2005 it could.)
Since really adding new objects, should be rare enough and controlled enough
that it's easy enough to to.
> There are other gotchas
> as well, this is just the tip of the iceburg.
I'll agree with this.
(for example, if you use Identity columns on the publisher, the subscriber
generally is NOT set up with Identity columns which can cause problems.)|||Did you want to participate in a solution for the poster, or just contradict
what I wrote?
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%2363aF9gKHHA.2140@.TK2MSFTNGP03.phx.gbl...
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:ORRSpucKHHA.2028@.TK2MSFTNGP03.phx.gbl...
> >I personally don't consider replication a valid High Availability
solution,
> > despite what MS says.
> Hmm, last I heard MS doesn't consider it a valid HA either.
> Replication is great for spreading out load, but not really an HA option.
> > The simple fact that it won't automatically replicate
> > new objects is enough for me to stop considering it.
> Honestly, that's not a big issue (and I thought as of SQL 2005 it could.)
> Since really adding new objects, should be rare enough and controlled
enough
> that it's easy enough to to.
> > There are other gotchas
> > as well, this is just the tip of the iceburg.
> I'll agree with this.
> (for example, if you use Identity columns on the publisher, the subscriber
> generally is NOT set up with Identity columns which can cause problems.)
>|||ChrisR wrote:
> Did you want to participate in a solution for the poster, or just contradict
> what I wrote?
I try to cut down on the caffeine after 6 P.M. Perhaps you should, too. ;-)
--
Message posted via http://www.sqlmonster.com|||Thanks for the comments so far.
John - I had lumped log shipping in with option 1, although that maybe just
my lack of understanding.
Linchi and ChrisR - Thanks for pointing out that replication is mainly for
data and not the schema. I had overlooked that part.
Now... database mirroring certainly has a lot going for it, and MS seem to
be pushing it ahead of using a virtual server. Certainly better than
replication or log shipping. But I don't want to have to deal with setting
up 150 odd mirrors, and then checking all the logs to see if there have been
any problems with any of them.
It's a shame they don't have Server Mirroring as an option, but then I guess
that would be a cluster. :-)|||ChrisR wrote:
> Did you want to participate in a solution for the poster, or just contradict
> what I wrote?
>
Expanding on someone else's answer IS contributing to a solution.
Welcome to newsgroups.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi Andrew:
You might try reviewing the literature at:
http://www.microsoft.com/technet/prodtechnol/sql/themes/high-availability.mspx
for information on High Availability solutions for SQL2K5.
Neither Backup/Restore nor Replication are considered to be "High
Availability" solutions. My understanding is that database mirroring
is to be supported with the upcoming release of SP2. There are 3 true
HA solutions for SQL2K5, in order from highest to lowest HA %:
* Database mirroring
* Virtual clustering
* Log Shipping (which you did not mention)
Most HA solutions I've worked with rely on clustering as their primary
solution. When even more HA reliability is desired we couple
clustering with database mirroring. Log shipping is an option, but
it's downtime is significantly higher than the other two options.
So my base recommendation is that you go with a virtual cluster if
you're looking for the most cost-effective HA solution. If you can't
afford the $ for a cluster with SAN, have you considered a cluster
tied to a local storage array? It sounds like your current
configuration consists of a standalone server and your recovery
solution relies on backups, correct? If you have a system that's truly
24/7, then you'll have to go back to your management and lay out the
risks of your current configuration and the cost to implement a
virtual cluster. Then present the worst-case scenario (i.e., server
hardware failure + damaged backups...that should be good for 2-3+ days
of outage and only a partial restore capability) ask them point blank
if under the current configuration they can sleep at night knowing
that an outage will result in significant downtime. If they say yes,
then you've done your job as DBA and when the outage occurs (and it
will) you'll do your job and remind them, as they're breating down
your neck to get the databases back online, that it was their decision
not to go with an HA solution...and you have the documentation to
prove it :-).
BTW, if they do go for the clustering solution, push for getting an
x64 platform instead of an x86 platform. You'll find that, even with
32-bit SQL2K5, performance is better and memory management issues are
easier to handle. For instance, if you don't install the CLR component
of SQL2K5 you'll be able to access 4 GB of base RAM (1 GB more than on
x86 systems) and AWE is both automatically enabled and better managed
by the OS under x64. An x64 platform is also more expandable, so if
you decide to add another instance at a later time you won't run into
the 16GB limit of the x86 platform.
Good luck to you!
On Wed, 27 Dec 2006 17:05:39 +0900, "Andrew Hayes"
<AndrewHayes@.discussions.microsoft.com> wrote:
>From what I've read, there seems to be 4 ways to ensure that if your main
>SQL Server 2005 machine dies, that you can have a backup machine
>up-and-running relatively quickly.
>1. Backup the databases on the master server and restore them to the backup
>server on a regular basis using a maintenance plan.
>2. Setup database replication, with the master server being the distributer
>and publisher, and the backup server being the subscriber.
>3. Use the unsupported database mirroring functionality in 2005.
>4. Create a virtual server on a cluster.
>Any others that I may have missed?
>Now onto the pros and cons of the 4 choices, from what I can see anyway.
>1. Slow and quite labour intensive when it comes to maintaining the backups,
>but simple to implement.
>2. A pig to setup, but then everything seems to go smoothly. Probably good
>if using a single subscriber against multiple publishers.
>3. Seems pretty straight forward and supports rollover, but is unsupported
>and requires some jiggery-pokery to get it going.
>4. The bees knees. Handles everything well but expensive.
>OK, so I sort of summarised the above. There are other pros and cons that
>are just too minor to deal with.
>I'd like to go with 4, as I have a system with 150 databases that are in use
>24/7, but I also have a limited budget and can't be forking out for a pair
>of high-end servers and fiber connected SAN.
>Any advice or suggestions?
>TIA.
>