SQL Replication for the purpose of reporting

Hi Experts,

We are looking at setting up a reporting server with SSRS and replicating a DB from a production server.

My question is, what is the best way to go about it? We are happy for the data to be up to an hour out of date, and are looking for what ever has the minimal amount of impact to the production server
LVL 4
Graham HirstIT EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Which version of SQL Server do you have? If MSSQL 2012 or 2014 you can use AlwaysOn for that.
If you want to go with replication, go for a Transactional Replication that will make you to have a syncronized copy on the Replica.
A Snapshot Replication isn't recomended for you since you want to have at maximum one hour of difference from the Original database and a Snapshot is an heavy operation to make it run every hour.

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
Graham HirstIT EngineerAuthor Commented:
Hi Vitor,

What about mirrored snapshots?
Wouldn't the impact only occur on the mirror server as a result?

We need to replicate the whole DB and i under stand transcriptional isn't suitable for that, and is usually complicated?

We have SQL 2012 standard
Graham HirstIT EngineerAuthor Commented:
The database is also only 5gb in size if that helps?
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Jim HornMicrosoft SQL Server Data DudeCommented:
Exactly how frequently do you need the data in the reporting database to be refreshed, e.g. every minute, every hour, daily?  If we're talking daily, then after the prod daily backup a better approach (?) would be to blow away the current reporting db and then do a restore from the most recent production backup.

>The database is also only 5gb in size if that helps?
Perhaps create an SSIS (or other ETL) package that pulls the data inserted/updated after the last time the package was run...
Graham HirstIT EngineerAuthor Commented:
Probably every hour. Maybe longer. We need an automated approach though
Graham HirstIT EngineerAuthor Commented:
Unfortunately i have no experience with SSIS packages. Are they fairly simple to setup? Can they manage a full database replication?
Vitor MontalvãoMSSQL Senior EngineerCommented:
For 5GB I would try the Snapshot Replication to see if there will be performance issues.
Taking a Snapshot every hour isn't a good idea but if you don't want to go for a Transactional Replication, you should check the Snapshot Replication.

AlwaysOn will be the solution that I'll highly recommend. You can set it asynchronously and configure to syncronize data every hour.
Vitor MontalvãoMSSQL Senior EngineerCommented:
By the way, mirrored snapshots is something complex to implement and it's a trick to let you access a mirrored database. That's why AlwaysOn was inveted ;)
Graham HirstIT EngineerAuthor Commented:
Alas, always on requires enterprise :(

Can you use snapshot replication with mirroring? Ie create a mirror of the DB, and then setup snapshot replication against the mirror copy?
Jim HornMicrosoft SQL Server Data DudeCommented:
>... SSIS packages. Are they fairly simple to setup?
No, as there's a fairly sizable learning curve, so if you're more comfortable with Replication and am following Vitor's recommendations then I'd go with that.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you use snapshot replication with mirroring? Ie create a mirror of the DB, and then setup snapshot replication against the mirror copy?
Then it will be better to go for mirrored snapshot. You create a mirror of the DB and then just create a database snapshot of the mirrored database.
Graham HirstIT EngineerAuthor Commented:
Cheers Vitor

Mirrored snap shots sound good to me
Can the snap shot update be scheduled. And just to confirm, is it compatible with Standard Edition
Vitor MontalvãoMSSQL Senior EngineerCommented:
And just to confirm, is it compatible with Standard Edition
Unfortunally not. Only for Enterprise Edition.
Graham HirstIT EngineerAuthor Commented:
Bugger, so really Transnational Replication may be our only option?

I'm a bit hesitant to use snapshot replication on the production Server as it hosts a number of public facing Databases
Graham HirstIT EngineerAuthor Commented:
Transactional Replication worked a treat :)
Cheers
Vitor MontalvãoMSSQL Senior EngineerCommented:
Good. So now you have a syncronized copy of the original database :)
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
SSRS

From novice to tech pro — start learning today.