0

I've a situation, I google it and found a lot of data but isn't clear for me how I should accomplish what I want.

I've a DB (SQL server 2014 enterprise) with 1TB size.

There are customers that needs special reports that the application doesn't offer.

I've another server that daily restores a backup from prodution DB (copy the last backup, Drop current db, and restore the backup)

My idea is use this server for reporting, since there are some of the "special reports" that are very expensive for the production database.

I have readed some of Integration Services and Reporting Services, and I think that one possible solution could be:

Synchronize the Report Server one time a day (or hour, or two hours...). On this report server, maintain some transformed data for the reports (indexed views or some precalculated data). Since if I create this indexed views on production server, it will be a big impact on performance.

¿Is this possible to accomplish?

¿Is my idea correct? ¿or there are another way more efficient/correct?

I want your opinions and advises before break my head doing this.

Thanks!

Mariano G
  • 255
  • 4
  • 14

2 Answers2

2

Yes it is possible to do what you are suggesting. An even more efficient/correct way of doing this would be with Replication, but that requires additional expenses, so it may not be an option for you.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
1

You have a few options here, I'd do some research on them before implementing. Log shipping, replication, drop and reload, ssas, etc... If you have lots of pre-calculated data requirements, definitely check out SSAS for reporting needs.

Jason B.
  • 315
  • 1
  • 12