Disaster Recovery

Hello,

I have a problem. I am trying to set up a disaster recovery plan and have ran into some problems. Just so you are aware, we have tried setting up replication but it failed since the database does not have primary keys(don't ask--I have no control) and we have tried to set up log shipping but we do not have the Enterprise version of SQL server so that won't work. The database is over 200GB so shipping by tape or transferring full backups across the wire is impractical. So basically my question is - "What other options can we explore for setting up disaster recovery that do not involve 'substantial' expenditures?"

There is a direct connection set up between the off site data store.

ThanksPersonally, if you have a 200 GB database without primary keys, you already have a disaster. You have bigger problems than disaster recovery. You can do periodic backups and have them copied (send one backup and then transactions) but I would fight over the issue of primary keys first.|||I'm not sure I agree with Douglas on this. Replication requires identity PKs on all tables, but this is not always desirable. For example, if I have a many-to-many relationship:

Student(StudentID, Name, ...)
Class(ClassID, Number, ...)

I'll typically do this:

StudentClass(StudentID, ClassID)

And in this case my PK will be StudentID + ClassID and thus is not an identity and will not replicate. To make replication work I have to add a third column:

StudentClass(StudentClassID, StudentID, ClassID)

And I don't like that. It sucks. This means that if I now want to set up a FK reference to StudentClass, I am forced to use the StudentClassID, because that is my PK on that table.

So replication is not going to work for me unless I accept and work around issues like this. Not for me thanks.

If, however, you have NO primary keys, then I agree, you already have a disaster. Start looking for a new job before this is discovered...|||Hmm. I happen to have always used Identity PK's (it has always worked out OK for me) but I do not think, at least in SQL 2000, that a Primary Identity Key is a requirement. I could not find an exact reference, but if you search Books Online for "Managing Identity Values", there is an entry that specifically is part of the Replication documentation, and there is a section called "Using Other Columns as Primary Keys", which offers other alternatives as primary keys, including using a RowGuid. It also says:

"If using an identity column is not a requirement, you can eliminate the overhead of managing the uniqueness of identity values in replicated data by using another column as the primary key, or using combinations of columns as the primary key."

In any event, in re-reading the original post, I think the questioner really does not have Primary Keys.|||::Replication requires identity PKs on all tables.

Plain answer: wrong.

Replication requires or some scenarios that a ROWGUID is defined, but it is definitly agnostic (even not liking) identity primary keys.|||Thanks to all for your input...but that is correct...there are not any PK's at all. This is how it is when I came into the picture. The problem is that putting PK's on the table is not an option (lost the battle don't have enough room or time to explain). Is the only option to set up log shipping?

thanks|||Well then you can set up log-shipping, I guess. And that will work for creating periodic snapshots. But I'd like to hear why PK cannot be added. There's no good reason for this.|||I agree...there is no good reason...the problem is that the database was created by a third party vendor for one of their products that is being used. My understanding is that the vendor has refused to change the application to fix the mess they created. I guess the client is going to have to pay the money to upgrade.

thanks|||I agree with Pierre, there is no reason at all for not having primary keys...this what they should have taught you in SQL 101. Although, instead of a new job, maybe a new career.|||Let me remind everyone...this is not a database that I had any part of the design or maintence...i was recently brought in to fix the problem....|||Fix the problem by setting up PKs.|||Does your data truly take up 200GB? Are your performing backups and truncating the transaction logs?

Disaster receovery strategies:
-- log shipping
-- replication
-- backup/restore
-- failover clustering

I think the types of questions you have to ask yourself are:
-- how long can we afford to be down if a disaster strikes?
-- how much data can we afford to lose?

Additionally, Microsoft has a good article onPlanning for Disaster Recovery
and a series of articles onReplication.

Terri|||What I would do if in your situation:

1) Refuse to touch the job unless I'm allowed to add PKs and FKs
2) Add PKs and FKs
3) Find out why the database is 200GB
4) Fix the problem by removing the binary data from the 200GB DB
5) Set up log shipping at regular intervals (1 day, 1 hour, 1 min or whatever)

But 1) is the most important.