Real Time Backup Practises

Hi All,
I was questioned by my boss to come out with a solution that can provide 100% data protection.
The best I can provide right now is a 1 hour interval transaction log backup which is still not good enough.
My question is beside making the applications write to 2 different databases is there any other feasible ways of performing real time backup ?
Many thanks in advance for any feedback.
--
Bernard GohHi,
I recommend you to perform a transactional level backup every 10 minutes
time. THis will be the best approch to
have the most recent data backed up in a real time system with less cost.
If you have a seperate system with SQL Server licenses then you can do :-
1. Transactional replication with PUSH Subscription.
This will ensure that you have the latest data in your standby server
all the time. Publisher will Push the data to subscriber immediately
--
Thanks
Hari
MCDBA
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:783933BC-0A18-4C20-8E5D-243E99ECC989@.microsoft.com...
> Hi All,
> I was questioned by my boss to come out with a solution that can provide
100% data protection.
> The best I can provide right now is a 1 hour interval transaction log
backup which is still not good enough.
> My question is beside making the applications write to 2 different
databases is there any other feasible ways of performing real time backup ?
> Many thanks in advance for any feedback.
> --
> Bernard Goh|||I'd suggest if your boss is after 100% data protection, and I would guess
100% uptime, you need to look at clustering. There is no real way to get
100% data protection through backups alone, you can get close, but
completely there. The only option really is to ensure that any hardware
failure does not cause any downtime or data loss, so fail over clustering is
probably the way forward.
Keith
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:783933BC-0A18-4C20-8E5D-243E99ECC989@.microsoft.com...
> Hi All,
> I was questioned by my boss to come out with a solution that can provide
100% data protection.
> The best I can provide right now is a 1 hour interval transaction log
backup which is still not good enough.
> My question is beside making the applications write to 2 different
databases is there any other feasible ways of performing real time backup ?
> Many thanks in advance for any feedback.
> --
> Bernard Goh|||Bernard,
This requirement is a balancing act between 100% data protection and
cost. When you say "data protection", do you mean availability? i.e.
could your data be unavailable for users for 1 day, but still
recoverable within a long time frame?
The best (and most expensive) way I can think of is to use a clustered
server with the data files residing on a SAN. You then replicate this
SAN off-site using the SAN replication technology synchronously to
another SAN. If you do not do this synchronously you will be exposing
yourself to data loss, albeit this will be small on an asynchronous
replication solution over small distances, even transatlantic, the data
loss should only be 30 secs ~ 1 minute, and with my testing I have
managed 30 secs transatlantic on HDS equipment.
Transactional replication is another solution worth exploring, but there
are many caveats in using transactional replication as a high
availability solution. Check out this article:
SQL Server 2000 High Availability Series: Implementing Transactional
Replication
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog03.mspx
Other solutions will likely not be 100% protected. Log shipping is way
short of this requirement, as is backup/restore. I'm looking forward to
Database Mirroring in Yukon, however this even will not provide 100%
guarantee against data loss.
If I had the budget, I would go with a clustered replicated SAN. You may
wish to test the performance of this as replicating synchronously has
performance implications.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
SummerCoke wrote:
> Hi All,
> I was questioned by my boss to come out with a solution that can provide 100% data protection.
> The best I can provide right now is a 1 hour interval transaction log backup which is still not good enough.
> My question is beside making the applications write to 2 different databases is there any other feasible ways of performing real time backup ?
> Many thanks in advance for any feedback.