4

I'd like to ask advice on setting up a reporting server please.

We have a SQL Server (on a VM) that handles operations. (SQLOps) This primary SQL server instance has a secondary Log Shipping instance (in another city)

We have a second VM now idle, which I have to set up as a Reporting server.

I have set up log shipping before in my life, but I have no idea how to approach the Reporting server now.

How do I set up the new SQL Server instance (SQLReporting).

How do I get to update the data in the 2nd SQL Server instance? Can I send through updates every 15 minutes, like with Log shipping?

I never want data from SQL Reporting to update the original SQLOps.

Are snapshots the only option?

3
  • So I just wrote an answer below - but now I see I may be confused by the original question. Are you asking about Making a read only source for SSRS? Or are you asking about High Availability for SSRS? I guess a little more clarity may help. My answer focused on your last point about snapshots being the only option.
    – Mike Walsh
    Nov 6, 2013 at 15:00
  • Big thank you Mike Walsh, the answer below is fantastic and I am on at least a path now where I wasn't before. We have an existing application which calls the stored procs from the DB via web front end - so wont be using SSRS. Seems likes it going to be Log shipping 'with standby'. It looks like we could be moving to SQL 2008 in the next few weeks and that I can do the reporting after that. Will SQL 2008 allow me more options? thanks! Nov 7, 2013 at 12:40
  • 2008 offers improvement over the options brought up in 2005 - nothing really new, though. 2012 is the big new one :) And glad that helped.
    – Mike Walsh
    Nov 7, 2013 at 15:15

1 Answer 1

2

If I understand you properly - you want to have a second reporting instance and you are asking how you can keep that updated.

It also appears you are on SQL Server 2005 so your options are limited more so than, say, if you were on SQL Server 2012 with Availability Groups.

Your options are really going to be in the spirit of:

Log Shipping And here you can actually restore your logs in WITH STANDBY mode - this means that you can effectively query your log shipped database for read only queries anytime. You can read more about that here and at the related links on that article. You cannot add indexes, add statistics, do any writes, etc. But you can avoid creating a snapshot. But you still require the blip of connectivity when the standby restore happens - so you haven't saved much.

Mirroring Same idea of log shipping (at a very basic level that is) - and here you can read off of a standby. Still ned that downtime of the reporting users to rebuild your snapshot. You can do A/B switching and effectively abstract some of that through views, content switching/load balancing but you still need a downtime for the snapshot change.

Replication You can use transactional replication and replicate data. This is near real time and updates just happen as they happen on the primary and get replicated (or you can control the frequency). This has more of an overhead on the source, latency really matters, requires a bit more DBA overhead, but it allows you to report on the secondary with no need to worry about standby restores, or new snapshots. Benefit here is you can choose what comes across and what doesn't if the reports only care about certain tables.

ETL You could always roll your own process to extract, transform and load just the data you care about. This way you can create a reporting optimized version of the database, index it appropriately and stream data in at the frequency you want. You need to worry about concurrency and blocking risk during your loads if it needs to be updated throughout the day, but there are ways of dealing with that. More overhead and developer time required.

And I would really encourage you to think about SQL Server 2012/2014 - AlwaysOn Availability Group Active Secondaries are a benefit here potentially. The main benefit being the read only secondary database. There are some optimizations here including the ability to have temporary statistics created and stored in the TempDB database on the secondary instance. The read only doesn't require snapshots, with standby, etc. It is truly read only - so you cannot write into it - but for reporting this can be a big win. Granted the move from 2005 to 2012 is a bigger conversation, requires licensing conversations, etc. but it is an option here for sure.

If you are talking about HA for SSRS that is a separate conversation.

1
  • An addition to the info about AG: in this case you need to make the secondary replica readable. That's an Enterprise feature available only on SQL Server Enterprise. This information is not clear enough in the official docs. The "market segmentation" is Basic AG vs. Advanced AG. AG = Always on Availability Groups. Those things are actually the same thing, only that Basic is a subset of Advanced, available on the Standard edition, while "Advanced", often called just AG, is available on the Enterprise edition. Jul 25, 2019 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.