Reporting Services Disaster Recovery

  • dave-dj

    SSChampion

    Points: 11230

    Comments posted to this topic are about the item Reporting Services Disaster Recovery

    _____________________________________________________________________________
    MCITP: Business Intelligence Developer (2005)

  • doveb

    SSC Veteran

    Points: 224

    This was a great article thank you!

  • george sibbald

    SSC Guru

    Points: 104200

    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: 11230

    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: 7287

    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

    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: 934

    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: 934

    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

    Old Hand

    Points: 363

    Very good and complete article.

    Thank you.

    Rajesh.

    http://GrabAllOneDayDeal.co.nz

  • SQLRNNR

    SSC Guru

    Points: 281210

    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: 11230

    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: 934

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

  • Donatello

    Right there with Babe

    Points: 793

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

  • dave-dj

    SSChampion

    Points: 11230

    sorry answer to what?

    What was the question?

    _____________________________________________________________________________
    MCITP: Business Intelligence Developer (2005)

  • lilyahuff

    SSC Veteran

    Points: 247

    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?

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

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