1

I have a SQL Server 2012 database which currently used as a transactional database and reporting database. The application reads/writes into the same database and the reports are also generated against the same database.

Due to some performance issue, I have decided to maintain the two copies of the database. One will be a transactional database which will be accessed by the application. The other database will be the exact copy of the transactional database and it will only be used by the reporting service.

Following are the requirements:

  1. The reporting database should be synched with transactional database in every one hour. That is, the reporting database can have stale data for maximum of 1 hour.

  2. It must be read-only database.

  3. The main intension is NOT recovery or availability.

I am not sure which strategy, transactional log shipping, mirroring or replication, will be best suited in my case. Also if I do the synch operation more frequently (say in every 10 minutes), will there be any impact on the transactional database or the reporting service?

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Amey
  • 1,216
  • 18
  • 28
  • Check out the **AlwaysOn** technology - it's mainly intended for high availability, but it works with **replicas** of the database which can also be used for reporting etc. – marc_s Feb 24 '15 at 06:09

1 Answers1

0

I strongly recommend you to use a standby database in readonly state. And every 15 minutes your sqlserveragent has a scheduled job to: a) generate a new .trn logfile within main db, and b) restore it into standby one(your reports db). The only issue is: using this technique your session will be disconnected while agent restores the .trn logfile. But if you can stop the restore job, run your reports and then reactivate it, there is no problem. Seems to be exactly what you need. Or if your reports are fast to run, probably will not be disconnected...if im not wrong restore job can also be configured to wait opened session to finish or to close it. I can check it this last doubt for you tomorrow if you don't find..

Once it is running in the same sql server instance, you don't have to worry about extra licensing...

Hudson Santos
  • 155
  • 10
  • I've confirmed, and YES, you can choose if you want to disconnect active users that are reading only, or not. As you can see that checkbox (unchecked) within this given screenshot: http://www.sqlshack.com/wp-content/uploads/2014/03/SecondaryDatabaseSettingsDBState.png – Hudson Santos Dec 03 '15 at 15:38