We have a single production database server right now with a single database
on it. The TempDB is on it's own drive and the the Transaction Logs are on
a separate drive. We currently perform nightly backups but I'm conserned
about loosing the transaction logs drive and not being able to recover it.
What I want to do is setup replication using the current server as a
publisher and another server as a subscriber. I can use dedicated NIC's and
Network with a gigabit connection. I want to use this as a backup to
accommodate for an unrecoverable drive failure so that we only use minutes
(or hours) of data instead of up to a day. The subscriber would be read
only unless the publisher server suffered from an unrecoverable error.
My questions are:
1. Does this make sense or is there a better way to accomplish what I want?
2. Should I be using transactional Replication? (for the most up to date
data mirrored from the publishing server without making an entire copy of
the DB each time)
3. Will my primary keys be replicated without changing the values?
4. What is a reasonable expectation for sync times? i.e. every 15 min,
every hour, etc...
Ola Hallengren - 18 Mar 2008 21:40 GMT
I would go for database mirroring instead of replication.
It's easy to setup and you can have no loss of data if you're running in
synchronous mode (or very little loss of data, if you're running in
asynchronous mode).
The weak point is the reporting needs. You could create database snapshots,
but you can not have the reporting data as online as with transactional
replication.
Ola Hallengren
http://ola.hallengren.com
> We have a single production database server right now with a single database
> on it. The TempDB is on it's own drive and the the Transaction Logs are on
[quoted text clipped - 16 lines]
> 4. What is a reasonable expectation for sync times? i.e. every 15 min,
> every hour, etc...
Bishop - 18 Mar 2008 21:56 GMT
Looks like Database Mirroring is only for SQL 2005 (sorry I didn't mention
I'm using SQL 2000).
>I would go for database mirroring instead of replication.
>
[quoted text clipped - 36 lines]
>> 4. What is a reasonable expectation for sync times? i.e. every 15 min,
>> every hour, etc...
Ola Hallengren - 18 Mar 2008 22:20 GMT
I think that you should consider Log Shipping then. (I haven't used it in SQL
Server 2000, but it works fine in SQL Server 2005 and I know that it exist in
SQL Server 2000.)
Ola Hallengren
http://ola.hallengren.com
> Looks like Database Mirroring is only for SQL 2005 (sorry I didn't mention
> I'm using SQL 2000).
[quoted text clipped - 39 lines]
> >> 4. What is a reasonable expectation for sync times? i.e. every 15 min,
> >> every hour, etc...
rickp - 18 Mar 2008 23:01 GMT
You could backup transactions every hour, it's very easy to
implement..
> We have a single production database server right now with a single database
> on it. The TempDB is on it's own drive and the the Transaction Logs are on
[quoted text clipped - 16 lines]
> 4. What is a reasonable expectation for sync times? i.e. every 15 min,
> every hour, etc...
Vinay - 19 Mar 2008 15:21 GMT
Hi Bishop,
My view, you are good to go with Transactional Replication, as we do, and
depending upon the load you can sync. suggest to go for 1Hrs,

Signature
Thank you.
Regards,
Vinay Thakur
> We have a single production database server right now with a single database
> on it. The TempDB is on it's own drive and the the Transaction Logs are on
[quoted text clipped - 16 lines]
> 4. What is a reasonable expectation for sync times? i.e. every 15 min,
> every hour, etc...