Build a db server just for reporting purpose

Question

Build a db server just for reporting purpose

sqlfriend

SSC Guru

Points: 52769
More actions
January 4, 2019 at 10:00 am

#409409

We are using 2017 Enterprise version of SQL server. Database is dataware house db. Reports are built upon these database either through SSRS or we have PowerBI or Tableau front end to do ad hoc report or queries.
And we found these reports increased the tempdb size on the database server and sometimes affect the performance on db server.
We are considering to use a second copy of database/server for reporting purpose only.
What is the best options?
It comes to my mind these options but do not know what is the best:
AlwaysOn Availability group, log shipping, transaction replication, snapshots db, backup and restore?

Thanks,

Viewing 5 posts - 1 through 4 (of 4 total)

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

Michael L John One Orange Chip Points: 27042 More actions · Answer 1

sqlfriends - Friday, January 4, 2019 10:00 AM
We are using 2017 Enterprise version of SQL server. Database is dataware house db. Reports are built upon these database either through SSRS or we have PowerBI or Tableau front end to do ad hoc report or queries.
And we found these reports increased the tempdb size on the database server and sometimes affect the performance on db server.
We are considering to use a second copy of database/server for reporting purpose only.
What is the best options?
It comes to my mind these options but do not know what is the best:
AlwaysOn Availability group, log shipping, transaction replication, snapshots db, backup and restore?

Thanks,

What are your specific requirements? There are pros and cons of each.
I'm wondering why you need a second copy of a data warehouse. Isn't the purpose of the data warehouse for reporting? Could you really be trying to solve an architecture or performance tuning issue?

An AG will need to be licensed for a read-only secondary. The data will be near real time. You will not be able to tune the secondary specifically for reporting.
Log shipping is easy to implement, the data will be stale by however long you set up the shipping.
Backup and restore is cumbersome, and depending upon how large your database is, may take far longer than your needs.
Replication gives you the flexibility to move only the data you actually need, and you can tune the copy for the reports. That may be somewhat problematic to implement.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

sqlfriend SSC Guru Points: 52769 More actions · Answer 2

Thanks much. The dataware house db is mainly for SSRS reports purpose. But for others users that use PowerBI or Tableau that they can build large ad hoc queries in whatever they want could affect the performance of the database server and increase the size of tempdb.
Is there an approach to improve this process?

Thanks,

Jeffrey Williams SSC Guru Points: 90255 More actions · Answer 3

sqlfriends - Friday, January 4, 2019 10:44 AM
Thanks much. The dataware house db is mainly for SSRS reports purpose. But for others users that use PowerBI or Tableau that they can build large ad hoc queries in whatever they want could affect the performance of the database server and increase the size of tempdb.
Is there an approach to improve this process?
Thanks,

If your PowerBI and Tableau dashboards are setup to use Direct Queries - then there really isn't much you can do...

You need to review how PowerBI and Tableau are set up...instead of having a single dashboard with everything in it you should consider smaller dashboards that has the data locally. This way the users can query that data without calling out to the data warehouse all the time. Schedule a refresh of the data to meet the requirements of each dashboard - either daily, weekly, monthly, etc...

The users should also be trained to create visualizations and save them - so when the refresh occurs they don't have to rerun these ad-hoc queries all the time.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 4

Above all else, one solid truth of the matter is that you're standing up a PRODUCTION server. That means licensing fees for both Windows and SQL Server.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)