February 18, 2010 at 6:43 am
I have a 1TB OLTP database in production. I want to create a read only copy of the database on a separate server to perform extensive reporting queries. Don't want to use sql replication because of the amount of data. Simply want to perform a nightly refresh from the production db into the copy db.
Any input is greatly appreciated.
Regards,
matt
February 18, 2010 at 6:53 am
You could consider log-shipping, mirroring with a database snapshot over the secondary, or a backup/restore, possibly using the COPY_ONLY option. It depends on your requirements and which edition of SQL Server you have, amongst other things.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 18, 2010 at 6:58 am
Thanks for the response. Currently running on WIN2008 R2, SQL 2008 64 Bit Enterprise Multi-site cluster.
Just looking for the best way to create a read only copy of the database to run reports.
February 18, 2010 at 7:00 am
with that size of database logshipping with restores held off during the day might be your best bet, certainly in terms of simplicity. I would want backup compression available though, which enterprise edition of 2008 gives you.
without compression database snapshots comes into the frame much more.
---------------------------------------------------------------------
February 18, 2010 at 7:03 am
How up-to-date must the reporting database be?
Do you already have database backups (full/diff/log) available at the report server site?
Is the report server at a different site?
How fast is the link?
And so on.
Log shipping might be a good option, it depends on question one above and also how fast the log grows. The overall size of the database is not that important.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 18, 2010 at 9:18 am
Paul White (2/18/2010)
The overall size of the database is not that important.Paul
might affect whether simple backup\restore is viable and the initiation of log shipping.
---------------------------------------------------------------------
February 18, 2010 at 9:36 am
george sibbald (2/18/2010)
without compression database snapshots comes into the frame much more.
George, correct me if I understood you wrong, are you suggesting database snapshots in the above statement?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 18, 2010 at 9:48 am
Bru Medishetty (2/18/2010)
george sibbald (2/18/2010)
without compression database snapshots comes into the frame much more.George, correct me if I understood you wrong, are you suggesting database snapshots in the above statement?
yes, with mirroring as OP wants it on another server. So thinking more about it compression of initial backup would be nice there, though not quite as important as other methods as only initial backup\restore to worry about.
How good the WAN/LAN link is going to be pretty important.
---------------------------------------------------------------------
February 18, 2010 at 9:51 am
george sibbald (2/18/2010)
Paul White (2/18/2010)
The overall size of the database is not that important.Paul
might affect whether simple backup\restore is viable and the initiation of log shipping.
Yes George, I know. Thanks. I said not that important.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 18, 2010 at 9:53 am
Bru Medishetty (2/18/2010)
george sibbald (2/18/2010)
without compression database snapshots comes into the frame much more.George, correct me if I understood you wrong, are you suggesting database snapshots in the above statement?
I think I mentioned snapshots on the secondary actually.
I didn't mention compression though.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 18, 2010 at 10:02 am
Paul White (2/18/2010)
george sibbald (2/18/2010)
Paul White (2/18/2010)
The overall size of the database is not that important.Paul
might affect whether simple backup\restore is viable and the initiation of log shipping.
Yes George, I know. Thanks. I said not that important.
I'm sure you do Paul, its hard to get the full meaning across with only the writtem word. I felt it needed expanding for the Ops benefit.
:ermm:
---------------------------------------------------------------------
February 18, 2010 at 10:08 am
george sibbald (2/18/2010)
I'm sure you do Paul, its hard to get the full meaning across with only the writtem word. I felt it needed expanding for the Ops benefit.
Oh ok. I did misunderstand you. Must stop posting at 6am - I'm not at my peak. :blush:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 18, 2010 at 10:16 am
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.
February 18, 2010 at 10:25 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.If anyone has an alternative idea, please let me know.
More importantly, thank you for taking the time to provide insight.
Much appreciated.
if timings work out, probably your only option, agreed Paul? 🙂
Paul, glad we sorted that out, saved a PM. Bit disappointed our paths have not crossed enough that I would get the benefit of the doubt. Stop posting before you have woken up. 🙂
---------------------------------------------------------------------
February 18, 2010 at 10:28 am
Before I have gone to bed actually!
Night all. I might post again this afternoon if there's anything left worth saying by then.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply