<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Database Design
»
Disaster Recovery
»
Data Warehouse Disaster Revcovery Options
14 posts, Page 1 of 2
1
2
»»
Data Warehouse Disaster Revcovery Options
Rate Topic
Display Mode
Topic Options
Author
Message
Welsh Corgi
Welsh Corgi
Posted Sunday, November 11, 2012 12:24 PM
Hall of Fame
Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:46 AM
Points: 3,820,
Visits: 4,044
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
Randy Knight
Randy Knight
Posted Sunday, November 11, 2012 4:11 PM
Grasshopper
Group: General Forum Members
Last Login: Monday, May 13, 2013 8:46 PM
Points: 24,
Visits: 136
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
Welsh Corgi
Welsh Corgi
Posted Monday, November 12, 2012 6:37 AM
Hall of Fame
Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:46 AM
Points: 3,820,
Visits: 4,044
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
John Mitchell-245523
John Mitchell-245523
Posted Monday, November 12, 2012 6:49 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: 2 days ago @ 9:56 AM
Points: 4,418,
Visits: 7,156
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
Welsh Corgi
Welsh Corgi
Posted Monday, November 12, 2012 6:58 AM
Hall of Fame
Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:46 AM
Points: 3,820,
Visits: 4,044
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
John Mitchell-245523
John Mitchell-245523
Posted Monday, November 12, 2012 7:08 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: 2 days ago @ 9:56 AM
Points: 4,418,
Visits: 7,156
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
Welsh Corgi
Welsh Corgi
Posted Monday, November 12, 2012 7:51 AM
Hall of Fame
Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:46 AM
Points: 3,820,
Visits: 4,044
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
John Mitchell-245523
John Mitchell-245523
Posted Tuesday, November 13, 2012 1:30 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: 2 days ago @ 9:56 AM
Points: 4,418,
Visits: 7,156
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
Koen Verbeeck
Koen Verbeeck
Posted Tuesday, November 13, 2012 1:32 AM
SSCrazy Eights
Group: General Forum Members
Last Login: 2 days ago @ 6:54 AM
Points: 9,364,
Visits: 6,462
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
John Mitchell-245523
John Mitchell-245523
Posted Tuesday, November 13, 2012 1:50 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: 2 days ago @ 9:56 AM
Points: 4,418,
Visits: 7,156
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 »
14 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
