Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jbates99
Constraint Violating Yak Guru
396 Posts |
 Posted - 2010-08-20 : 01:57:35
|
| hi everyone.I have a 2005 Standard database - 40 GB in size - and I want to copy, push or restore it to my reporting server. Each night.Some options I have considered are:- backup database with the destination being the reporting server- snapshot replicationAre there any other good options, maybe something a little more "out of the box" ?Thanks for any tips. John |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-08-20 : 04:03:36
|
| Set up mirroring of the production database to the reporting server and create a snapshot of the mirror every time you need a fresh copy. Since the mirror is continuously updated the snapshot creation/refresh will be instant. Alternatively (if making a snapshot isn't available since it's an enterprise feature) you can do log shipping with a standby file. That would make the database which is in recovery readable.- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
 |
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2010-08-20 : 15:07:00
|
| Thanks for the info, Lumbago.Tara, yes per business users, 24 hour old data will be alright.Thanks, John |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-08-20 : 15:38:57
|
| The snapshot off the mirror is a good solution then if you've got separate hardware. You have to be very cautious with this approach though if any of your data files is over 64GB in size (it could cause a Windows sparse file issue and cause a production issue on the principle server). To avoid the issue, you break up your database into multiple data files so that each file is less than 64GB.Another solution would be to snapshot the database on the same instance. But then you are using the same hardware, so not sure if that'll work for you.Have you considered READ_COMMITTED_SNAPSHOT isolation level? We've been able to use that on our OLTP database so that reports and other things can run on the same database. We only run reports on the OLTP database on our smaller systems though.We use transactional replication for our reporting needs for our bigger systems, but we require up to date information in the reporting database.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2010-08-21 : 17:10:41
|
| Tara you say "The snapshot off the mirror is a good solution then if you've got separate hardware".I assume that would be a Database snapshot. I don't have that option - this is 2005 *Standard* on both servers.I'm leaning toward snapshot replication with the file going to the reporting server, scheduled for once per day, then I restore on the reporting server. I have used transactional replication in the past but it might place more stress on the publishing server than I want.Thanks for your ideas. John |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-08-21 : 19:30:19
|
| Snapshot replication sounds like the right solution for you then.For what it's worth, our transactional replication system is one of the busiest that Microsoft has seen. It's not a very big database (80GB), but it is passing a ton of DML transactions through replication. And this is without impacting the publisher at all. I had a case open with Microsoft regarding replication latency (we had slow IO on the subscriber), and they were shocked at how busy our replication was.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
|
|
|
|
Need a Good Way of Copying a Database Nightly