SQL Replication or better option for task?

I have been tasked with a taking a copy of our production database and replicating it to a second server on the network for our report engine to run reports from. Currently we did a backup of the production database and restored it to the report server. We need this type of action to take place every sunday night. Originally I was thinking of scripting a backup and a restore but then I found out about SQL replication.

I wanted to check with some SQL experts to verify this would be the best method of doing this. That is, using replication to basically make a copy of the production database on a secondary report server.

What do you think?

What are the risk?

Any performance issues we may see from the publisher "Production Server"?

Is it a must to have a dedicated server for the distributor or would the publisher server or
report server "Only Subscriber" be ok to use for this role.

And last...Is there a better method of doing this.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PadawanDBAOperational DBACommented:
If you need the ability to read off the secondary, you can also combine database mirroring/ snapshots to allow for it to be readable.  I personally don't care for that option as it's rather painful to maintain.  I think you're definitely heading down the right path for what your needs are.  The performance issues you may see on the publisher depend on what type of replication you decide to use.  Transactional replication keeps the databases in sync near real time; however, there is the overhead associated with replicating the changes at near real time to the subscriber(s).  This can get to be an issue if you do a lot of batch processing, but if you need the data on the reporting database/server to be realtime, your data changes frequently or your database is too large for other methods, it's the best option.  Another option is snapshot replication, which essentially takes a snapshot of the database according to a specified schedule and then transfers the snapshot to the subscribers.  The snapshot can be large, so snapshot replication is generally recommended for databases where the data changes infrequently and the database/published articles aren't very large.  Other options, such as merge replication and peer to peer replication exist, but if you only need read only for the subscriber, then I would say that eliminates merge replication from the playing field and peer-to-peer replication is probably overkill for what you need.  

If your database is highly active from a write perspective, you may want to locate the distributor elsewhere, but you're usually pretty safe having it on the same server if it's not a heavy duty replication scenario.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rburneyAuthor Commented:
Thank you for the input.

I think Snapshot would be sufficient for what we are wanting to do. Out idea or schedule would be this. Oh... and there are a tone of batch processing as this is an EMR database. :)

We are going to schedule the replication for weekends where there are not many users utilizing the EMR. We want to make sure this information is ready and up to date as of a week. So by Monday morning we want the data ready to read and no more replication is to be done until the following weekend. With the snapshots will it basically take a full back up and dump it to a location and then publish the data from that "backup" or can we just do changed data moving forward?

Thanks again!!
PadawanDBAOperational DBACommented:
Sorry for the delay in replying to you!  It does not have any awareness of changed data, but it will be smaller than a full database backup in the scenario where you are publishing a subset of the databases tables/procedures/views/etc.  But it will take a snapshot of the published articles without any notion of whether or not it changed.  Hope that helps and let me know if you have any other questions!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.