Solved

Alternatives to offload Reporting Services workload from main Database

Posted on 2008-06-09
5
521 Views
Last Modified: 2012-05-05
We are running our SQL2005 databases on an HP EVA SAN and are licensed for local mirroringclone, snapshotting and snapcloning. We also have DoubleTake for disaster recovery to a second SAN. We are in the process of implementing reporting services but do not want to drag down the preformance of our production databases with long running queries. We would like to use SQL reporting services for near-real-time reporting but that can include multiple batched reports, so we want to have a second server with SSRS and a near-real-time copy on which we can run the reports. In looking at the various SAN alternatives for creating copies and at double take it appears the destination cannot really be used for reporting unless it is a snapshort (point in time) followed by bringing up the destination SQL instance, as opposed to being a mirror (up to date). Same issue for DoubleTake (must snapshot and bring up instance). SQL Database mirroring does not allow access to the destination database because it is in constant recovery mode. The only thing I can see that might work is basic SQL transactional replication. Anyone else have any possible solutions?
0
Comment
Question by:dennis_boruck
  • 3
  • 2
5 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21744899
You can use transactional replication to setup a near real time reporting environment....
0
 

Author Comment

by:dennis_boruck
ID: 21745049
I proposed alternative in my question. I had hoped there was some other alternative, but if not I will have to live with it. Let me see if anyone else has any other possible ideas because transactional replication has a number of restrictions associated with it that I would prefer to avoid.

Thanks.

Dennis
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21745253
Ummm...what restrictions are you talking about?
0
 

Author Comment

by:dennis_boruck
ID: 21745803
It requires additional fields on the tables to insure uniqueness if there is no primary key (possibly transaction sets). Secondly, when a full snapshot must be propagated tables being snapped are locked, thus preventing writes on the live database on that table. Finally, there are certain kinds of schema changes that cannot be replicated but will cause the database to be re-snapped afterward. The SAN and double-take based solutions do not have these limitations, however their destination databases are not usable while mirroring either, so if we need a near-real-time database that can be used for reporting, I suspect we are stuck with transactional replication.

Have you created separate databases for reporting services using transactional replication?

Dennis
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 250 total points
ID: 21745823
Yes, I've always created the publications at night on databases that have heavy loads.  If you don't have primary keys on your tables, then you may want to start thinking about meaningful ones to put on them before you transfer them....not sure if that is a limitation though.  Some schema changes are limited, but not many.  Its been a perfect solution for me over the years....if you do it right, its a great reporting solution.

>>Have you created separate databases for reporting services using transactional replication?

Not sure what you're asking.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

805 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question