Reporting Services Disaster Recovery

Question

Post reply

Reporting Services Disaster Recovery

dave-dj

SSChampion

Points: 11297
More actions
April 21, 2010 at 12:00 am

#386749

Comments posted to this topic are about the item Reporting Services Disaster Recovery
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

Viewing 15 posts - 1 through 15 (of 26 total)

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

doveb SSC Veteran Points: 224 More actions · Answer 1

doveb

SSC Veteran

Points: 224

April 21, 2010 at 6:35 am

#1155294

This was a great article thank you!

george sibbald SSC Guru Points: 104210 More actions · Answer 2

Great job Dave, well done.

The automation of the process and use of proxies is a nice touch.

There is nowhere near enough information on SSRS DR out there and this is a much needed addition.

Its worth pointing out the reporting databases will need to be in full recovery mode to use mirroring, and in fact any HA solution could be used to get the database to the failover server,be it full copy and restores, log shipping or even SAN replication.

---------------------------------------------------------------------

dave-dj SSChampion Points: 11297 More actions · Answer 3

absolutely. This is one possible scenario, but for me, the most appropriate.

Worth saying as well, that we use dns entries in our connection strings to the active report server and active database server, so it's a simple log off & log back on for users to pick up the new dns entry that would point to a different IP address.

Also another point, which I didn't cover in the article, as it's a different topic, is that if you intend on doing something similiar with dns entries, then it's quite likely that you will need to setup SPN's (Service Principle Names), in order to get Kerberos Authentication working.

Glad you liked it though and thanks for pointing me in the direction of the Knowledge Base article.

😎

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

Richard M. SSCertifiable Points: 7401 More actions · Answer 4

Dave, great article. very detailed and nicely outlined. Kudo's......

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

I use a simpler approach: Jasper Smith's Reporting Services Scripter (http://sqldbatips.com/showarticle.asp?ID=62). Keep everything saved off onto a separate machine and you can migrate pretty quickly. Clearly not as 'available' as mirroring but I think everyone should have this layer of recoverability scripted out and ready to go.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

l543123 SSC Eights! Points: 964 More actions · Answer 6

Nice article! Quick question, sorry if its silly. If use is accessing reports from a front end application using url's like http://SQLTEST01/ReportServer/Report1 or http://SQLTEST01/Reports/Report1, then if the failover happens to SQLTEST02, will the url's work? Will the same url's automatically point to SQLTEST02's reportserver? Or is this something which needs to be handle separately?

l543123 SSC Eights! Points: 964 More actions · Answer 7

Hi Dave, can you please let me know how you use DNS entries to point to correct server or change server? We currently hardcode the url's with server name which I think is a bad idea when using failover.

rajesh.shukla SSC-Addicted Points: 413 More actions · Answer 8

Very good and complete article.

Thank you.

Rajesh.

http://GrabAllOneDayDeal.co.nz

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

This was a great article thank you!

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

dave-dj SSChampion Points: 11297 More actions · Answer 10

there are a few things you need to do to use a single address such as http://LiveRS.domain.net/Reports working.

1 - both the servers will need to have a fixed IP address.

2 - you need to install dnscmd (if it's not already installed on the servers)

3 - You will need to amend Reporting Services – Activate RS

to include an additional 'Operating System (CmdExec) job step to add a DNS entry for the server that you would be switching live, eg sqltest01 might need:

dnscmd DNSServer.domain.net /RecordAdd domain.net LiveRS 60 A 192.168.10.1

and sql test02:

dnscmd DNSServer.domain.net /RecordAdd domain.net LiveRS 60 A 192.168.10.2

Also, on each respective server for the Reporting Services Switch Live – Maintenance and Reporting Services Force Live – DR add a job step to remove the dns entry for the server that was live and is now becoming the standy / mirror or has become unavailable.

so sqltest01 would be:

dnscmd DNSServer.domain.net /RecordDelete domain.net LiveRS 60 A 192.168.10.2 /f

and sql test02:

dnscmd DNSServer.domain.net /RecordDelete domain.net LiveRS 60 A 192.168.10.1 /f

4 - you'll have to setup host headers on the SSRS sites. (we tend to set up both the server name as a host header, i.e. sqltest01.domain.net and also the live references you want to use i.e. http://LiveRS.domain.net/Reports.

5 - Then you will have the unenviable task of setting up Kerberos authentication.:-P (this is likely to frustrate the hell out of you, if you haven't done this before, as there are lots of 'moving parts'). In breif, you'll need to setup SPN's for SQL Server and http service (for RS) and trust the servers....

it's better explained in the following locations:

Configuring Kerberos Authentication - http://www.sqlservercentral.com/articles/Security/65169/

Kerberos, Delegation and SQL Reporting Services - http://callumhibbert.blogspot.com/search?q=kerberos+delegation

Troubleshooting Kerberos Delegation (Very useful article)- www.microsoft.com/DownLoads/details.aspx?FamilyID=99b0f94f-e28a-4726-bffe-2f64ae2f59a2&displaylang=en

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

l543123 SSC Eights! Points: 964 More actions · Answer 11

thanks Dave, will try this out. will try with 2 test servers.

Donatello SSC Eights! Points: 853 More actions · Answer 12

I am wondering the same thing. Did you ever get an answer to this?

dave-dj SSChampion Points: 11297 More actions · Answer 13

sorry answer to what?

What was the question?

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

lilyahuff SSC Veteran Points: 277 More actions · Answer 14

did this with ssrs 2008 R2.

kinda works, jobs do not create (msdb has changed some). manual fail-over steps on same old keys.

have to do good old

C:\Users\sqlinstalladmin>rskeymgmt -l

QASQL2008M\MSSQLSERVER - 87d8f5f3-ca94-4a81-94c4-5f477ee03cd6

QASQL2008\MSSQLSERVER - bd25cc94-4826-43b1-a9ea-7fc4524d3dd5

The command completed successfully

C:\Users\sqlinstalladmin>rskeymgmt -r 87d8f5f3-ca94-4a81-94c4-5f477ee03cd6

Are you sure you want to delete this key? Yes (Y)/ No (N): y

The command completed successfully

to kill one of the keys to force SE to work and do not complain that

Reporting Services "Scale-out deployment" is not supported in this edition of Reporting Services. (rsOperationNotSupported)

does anyone know how to make rskeymgmt work in silent mode and do not ask?