February 18, 2010 at 10:13 pm
mattfitz (2/18/2010)
Looks like the business has agreed to a weekly data load. It appears i'll have to copy a compressed full backup file across a relatively slow WAN connection and perform a weekly restore.If anyone has an alternative idea, please let me know.
More importantly, thank you for taking the time to provide insight.
Much appreciated.
Do you need all the objects in the database for reporting? If you don't need all the objects for reporting in DB, and business requirement is daily or weekly. I would recommend Snapshot replication.
EnjoY!
February 19, 2010 at 2:33 am
my strategy, if downtime allowed,
1) take primary database offline.
2) Detach it.
3) Copy file(data and log) to new location.
4) Bring your main database online to restore the service.
4) Attach file with new database name in standby server.
5) Schedule any of the available tools to sync standby with primary(logshipping/mirroring/transactional replication).
From your queries, as you mentioned that you need read only so I think you can decide your standy server update as per your covinience.
----------
Ashish
February 19, 2010 at 2:51 am
Not sure how you guys suggesting mirroring...as per my understanding mirrored DB is not accessible (till failover) .....how that can be used for reporting?
February 19, 2010 at 2:55 am
GT-897544 (2/18/2010)
Do you need all the objects in the database for reporting? If you don't need all the objects for reporting in DB, and business requirement is daily or weekly. I would recommend Snapshot replication.
Good idea. Snapshot replication, possibly with a static row filter, might be an efficient solution.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 19, 2010 at 2:57 am
jshailendra (2/19/2010)
Not sure how you guys suggesting mirroring...as per my understanding mirrored DB is not accessible (till failover) .....how that can be used for reporting?
If you read back, you'll see that I suggested mirroring with a database snapshot created on the secondary. The snapshot allows read-only access to the non-primary database. Requires Enterprise Edition.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 19, 2010 at 3:01 am
ashish.kuriyal (2/19/2010)
my strategy, if downtime allowed,1) take primary database offline.
2) Detach it.
3) Copy file(data and log) to new location.
4) Bring your main database online to restore the service.
4) Attach file with new database name in standby server.
5) Schedule any of the available tools to sync standby with primary(logshipping/mirroring/transactional replication).
From your queries, as you mentioned that you need read only so I think you can decide your standy server update as per your covinience.
There's no need to take the production database offline! :w00t:
Log Shipping and Mirroring are initialised from backup files.
Setting up replication never requires downtime.
Transactional replication is the wrong choice - they want a static, read-only, reporting database, refreshed weekly.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 19, 2010 at 3:01 am
Paul..can you give some link to read on this?..I am not aware of this..need to gain more knowledge on this concept.
February 19, 2010 at 3:06 am
jshailendra (2/19/2010)
Paul..can you give some link to read on this?..I am not aware of this..need to gain more knowledge on this concept.
Sure. Books Online: Database Mirroring and Database Snapshots
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 19, 2010 at 3:08 am
Paul,
Downtime will be there if you want the database files to be copied properly.
----------
Ashish
February 19, 2010 at 3:16 am
mattfitz (2/18/2010)
Looks like the business has agreed to a weekly data load. It appears i'll have to copy a compressed full backup file across a relatively slow WAN connection and perform a weekly restore.
Just to summarize my own thoughts:
1. Copy the compressed full backup, as suggested by George
2. Snapshot replication, as suggested by GT
3. Disk Mail
There are probably other options, but the right choice for you probably depends on many factors only you know.
(For example if your systems are on a SAN, your SAN man might have some suggestions)
Option 2 might be worth exploring if it would significantly reduce the amount of data that needed to be sent.
I have seen option 3 being used in the real world...but it seems dubious to me (security might be an issue!)
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 19, 2010 at 3:18 am
ashish.kuriyal (2/19/2010)
Paul,Downtime will be there if you want the database files to be copied properly.
No. There is absolutely no need to copy database files (*.mdf, *.ndf, *.ldf)
Log shipping and mirroring are initialized from a database backup
Books Online:
Overview of Database Mirroring Setup
You will see that both start with a database restore using the NORECOVERY option.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 23, 2010 at 3:27 am
I also need a read only copy of an OLTP database on separate server for reporting, but the requirement is for ‘Near Real Time’ reports. Using SQL2008 what are the best options for minimal loading on the production server and high availability and minimal latency on the reporting copy.
We have used log shipping in the past on SQL 2000, but this periodically takes the reporting copy offline. Are there now better options in SQL 2008?
Thanks,
Martin
February 23, 2010 at 3:40 am
Buzz-Martin (2/23/2010)
I also need a read only copy of an OLTP database on separate server for reporting, but the requirement is for ‘Near Real Time’ reports. Using SQL2008 what are the best options for minimal loading on the production server and high availability and minimal latency on the reporting copy.
We have used log shipping in the past on SQL 2000, but this periodically takes the reporting copy offline. Are there now better options in SQL 2008?
Thanks,
Martin
Way too large a question to tack on the end of someone else's, but one possible solution is Database Mirroring (improved in 2008 through log compression).
While you can't directly report off a mirror, you can create a Database Snapshot over it (Enterprise Only).
There's a lot of good information about all this in Books Online. I could link to a good fraction of the Internet in an attempt to cover the subject well, but instead I'll point you to Paul Randal's entries concerning Mirroring: http://www.sqlskills.com/BLOGS/PAUL/category/Database-Mirroring.aspx
There are many possible solutions to your requirement - the best depends on detail you haven't yet shared. My suggestion is to start a discussion thread of your own on this very interesting point.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply