Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Data Warehouse Disaster Revcovery Options Expand / Collapse
Author
Message
Posted Sunday, November 11, 2012 12:24 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:06 PM
Points: 4,253, Visits: 4,295
I'm in the process of implementing a Data Warehouse.

I have a Staging Database and a DatawArehouse Database.

I have data feeds from DB2, Oracle and SQL Server.

The DB2 feed is very large, from a remote location and the bandwidth is slow.

I have the Staging Database set to Simple Recovery primarily because I want to minimize the load time to fit within a narrow window.

I currently have the Data Warehouse Database set to simple recovery model.

It was suggested that I use log shipping as a Disaster Recovery methodology but for obvious reasons that is not going to work.

I was considering Mirroring and Snapshot Replication but it is my understanding that Replication was not intended to be a DR Solution?

Any thoughts on this?




For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1383460
Posted Sunday, November 11, 2012 4:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:45 PM
Points: 24, Visits: 152
You say Log Shipping is not going to work for obvious reasons (i.e. because you are in Simple). But I don't understandi your reasons for being in Simple in the first place. You said it was to "reduce load time". Please explain.
Post #1383479
Posted Monday, November 12, 2012 6:37 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:06 PM
Points: 4,253, Visits: 4,295
It takes a long time to load the Data and I can barely complete the load within a narrow window.

I save time by not logging the Inserts, etc.


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1383667
Posted Monday, November 12, 2012 6:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:34 AM
Points: 5,480, Visits: 10,303
Inserts, etc are still logged in Simple mode. They're just removed more quickly. Have you tried switching to Full recovery mode and comparing the time taken?

John
Post #1383672
Posted Monday, November 12, 2012 6:58 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:06 PM
Points: 4,253, Visits: 4,295

That right, simple recovery truncates the transaction log on the checkpoint.

Thanks, I will switch recovery mode and perform comparisons.

So if I'm using full recovery model is log shipping the preferred DR Strategy?


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1383675
Posted Monday, November 12, 2012 7:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:34 AM
Points: 5,480, Visits: 10,303
We need a bit more information, please. Which database(s) will your DR strategy cover? I assume your Staging database is updated from the external sources and then your Data Warehouse database is updated from the Staging database? How often does that happen?

I have found that Database Mirroring is the simplest, cleanest solution, but it won't be suitable for all purposes. If you just have one overnight update to your databases, for example, then you might consider a backup and restore instead, and build that into your batch process.

As far as putting yoour databases into full recovery mode is concerned, make sure your log files are large enough for the frequency of your log backups. If your log files need to grow during the load then any comparison with Simple mode may not be very helpful.

John
Post #1383679
Posted Monday, November 12, 2012 7:51 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:06 PM
Points: 4,253, Visits: 4,295
John Mitchell-245523 (11/12/2012)
We need a bit more information, please. Which database(s) will your DR strategy cover? I assume your Staging database is updated from the external sources and then your Data Warehouse database is updated from the Staging database? How often does that happen?

I have found that Database Mirroring is the simplest, cleanest solution, but it won't be suitable for all purposes. If you just have one overnight update to your databases, for example, then you might consider a backup and restore instead, and build that into your batch process.

As far as putting yoour databases into full recovery mode is concerned, make sure your log files are large enough for the frequency of your log backups. If your log files need to grow during the load then any comparison with Simple mode may not be very helpful.

John


I have Data Feeds from IBM DB2, Oracle, and SQL Server and for each data source I have a number of loads to staging.

The Data Warehouse is finally loaded from Staging.

I have written DTS & SSIS Packages to backup the Database, Compress it, Copy it to another Server, unzip it and finally restore the Databases.

How about snapshot replication?


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1383707
Posted Tuesday, November 13, 2012 1:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:34 AM
Points: 5,480, Visits: 10,303
If you want to copy the whole database, backup and restore is much cleaner. I'd only recommend snapshot replication if there are only certain tables that you want to copy across to DR. As for your staging database, it sounds as if it's getting updated several times a day, so you'll probably want to look at log shipping or mirroring - or transactional replication if you don't want to copy everything. Given that staging databases are often, by their very nature, entirely transient, it may be sufficient just to keep an empty copy of it on your DR server.

John
Post #1383974
Posted Tuesday, November 13, 2012 1:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:15 AM
Points: 13,633, Visits: 11,504
Isn't log shipping and mirroring high availability? I thought having plenty of verified backups is disaster recovery.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1383976
Posted Tuesday, November 13, 2012 1:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:34 AM
Points: 5,480, Visits: 10,303
It's probably just a question of semantics. As I understand it, a high availability server can form part of a DR strategy. I don't think it matters whether the server is referred to as DR or HA as long as the process is properly documented and tested.

John
Post #1383981
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse