• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

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.
  • 2
1 Solution
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.
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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now