Link to home
Start Free TrialLog in
Avatar of Graham Hirst
Graham HirstFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Graham Hirst

ASKER

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
The database is also only 5gb in size if that helps?
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...
Probably every hour. Maybe longer. We need an automated approach though
Unfortunately i have no experience with SSIS packages. Are they fairly simple to setup? Can they manage a full database replication?
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.
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 ;)
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?
>... 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.
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.
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
And just to confirm, is it compatible with Standard Edition
Unfortunally not. Only for Enterprise Edition.
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
Transactional Replication worked a treat :)
Cheers
Good. So now you have a syncronized copy of the original database :)