Replication for read only database

Question

Post reply

Replication for read only database

fedbn

SSC Rookie

Points: 28
More actions
May 18, 2015 at 7:28 am

#318231

Hi guys.
I'm in a project to replicate a database from the production environment to another sql server.
The idea is use it for some biggest querys because in the production database we had experimenting various problems
What is the best way for do it? Transactional replication? Log Shipping? I'm reading absolutely everything but I can not get decide.
Both servers are SQL Server 2008 R2 and they are in the same LAN.
Thanks!

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

How often are log backups taken on production?

How far behind is the reporting database allowed to be?

Is it acceptable to disconnect all users a couple times an hour?

Can the reporting DB be completely read only? (same users, security and indexes as production)

Would you like to be able to add indexes, views, procedures, etc to the reporting database?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

fedbn SSC Rookie Points: 28 More actions · Answer 2

How often are log backups taken on production?

On production every hour

How far behind is the reporting database allowed to be?

The data of reporting can be 1 hour old more or less

Is it acceptable to disconnect all users a couple times an hour?

On production? Is not possible.

Can the reporting DB be completely read only? (same users, security and indexes as production)

yes, is only for selects

Would you like to be able to add indexes, views, procedures, etc to the reporting database?

I need only the data for reports, I supose need indexes and views only.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

fedbn (5/18/2015)
Is it acceptable to disconnect all users a couple times an hour?
On production? Is not possible.

No, on the reporting server. Is it acceptable to disconnect all users every hour, stopping their queries and requiring them to reconnect and start the queries again.

Would you like to be able to add indexes, views, procedures, etc to the reporting database?
I need only the data for reports, I supose need indexes and views only.

Different ones to what the production DB has?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

fedbn SSC Rookie Points: 28 More actions · Answer 4

GilaMonster (5/18/2015)
fedbn (5/18/2015)
Is it acceptable to disconnect all users a couple times an hour?
On production? Is not possible.
No, on the reporting server. Is it acceptable to disconnect all users every hour, stopping their queries and requiring them to reconnect and start the queries again.

The users can be do a query in this moment. I would prefer not

GilaMonster (5/18/2015)
fedbn (5/18/2015)
Would you like to be able to add indexes, views, procedures, etc to the reporting database?
I need only the data for reports, I supose need indexes and views only.
Different ones to what the production DB has?

No, the sames.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

fedbn (5/18/2015)
GilaMonster (5/18/2015)
fedbn (5/18/2015)
Is it acceptable to disconnect all users a couple times an hour?
On production? Is not possible.
No, on the reporting server. Is it acceptable to disconnect all users every hour, stopping their queries and requiring them to reconnect and start the queries again.
The users can be do a query in this moment. I would prefer not

Then log shipping is probably not the best solution, as restoring logs to the secondary requires that users be disconnected.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

fedbn SSC Rookie Points: 28 More actions · Answer 6

GilaMonster (5/18/2015)
fedbn (5/18/2015)
GilaMonster (5/18/2015)
fedbn (5/18/2015)
Is it acceptable to disconnect all users a couple times an hour?
On production? Is not possible.
No, on the reporting server. Is it acceptable to disconnect all users every hour, stopping their queries and requiring them to reconnect and start the queries again.
The users can be do a query in this moment. I would prefer not
Then log shipping is probably not the best solution, as restoring logs to the secondary requires that users be disconnected.

So, replication is the best way?