2

I have been tasked to come up with a workable DR solution for our SQL environment. However, the DR box has a major restriction as the data drive is 2.25TB as opposed to a 3.50TB drive used in production. Obviously, this contravenes the fundamental tenet of making the DR hardware the same as production.

Now, to the specifics. We have several large databases (ranging from 100GB to 750GB) that are partitioned into filegroups by date function (monthly filegroups) as these databases hold several years worth of historical data. Hence, filegroups containing data over a year old are marked read-only.

Is it possible to have a DR database which is a subset of filegroups from the larger production database? For example, say production has 3 filegroups: 2008 (r/o), 2009 (r/o) & 2010 (r/w). Can a DR database then consist of only 1 filegroup (2010)? If so, can log shipping be configured for the r/w filegroup?

If not, the workaround would be to create a archive database for historical data which could then be sacrificed in a DR situation, whilst the current database with live data can be log shipped. FWIW, full database backups are made onto tape and held offsite. Is the filegroup method a workable solution?

Mark G
  • 149
  • 5
  • Log shipping operates on the premise that the source database is in full recovery model and it (in its entirety) will be used to create the secondary database. – jl. Nov 16 '10 at 05:04

1 Answers1

1

Log shipping is going to take the entire database into consideration; filegroups are irrelevant. I agree that your archive database solution would work.

Scott Herbert
  • 586
  • 1
  • 6
  • 13
  • not part of my answer, but having DR hardware which is sub-production spec isn't necessarily a terrible thing; HA on the other hand.. – Scott Herbert Nov 23 '10 at 05:01
  • Archive database it is then - thanks to all those that took the time to answer. – Mark G Nov 25 '10 at 00:09