SSRS with DB Mirroring for Disaster Recovery

  • Background

    I'm trying to create a reporting services solution to allow failover to a secondary server should the primary server fail.

    I need to get this working (if possible) on SQL server 2005 Standard Edition (SP3). I have configured 2 servers both running reporting service and SQL Database Server.

    What I am trying to achieve is this:

    Server 1 acts as the live server that all users connect to. The SSRS service will be started on this server. Additionally the Report Server database and TempDB reside on the server.

    Server 2 acts as the backup server. On this server will reside the mirror of the Report Server DB and TempDB. The SSRS service on the machine will be stopped.

    In the event of server 1 failing, the database will be failed-over to Server 2 and the SSRS service started. Users then connect to Server 2.

    The problem

    To ensure it can decrypt the information, I have restored the SSRS encryption keys from server1 to Server 2.

    I have configured the above and when trying to access the report server I get the message

    The feature: "Scale-out deployment" is not supported in this edition of Reporting Services

    I've checked the Report server configuration on Server 2 which is showing that the Initilzation is ok. (shows a tick, although I can access the screen as it's we are using stanard edition).

    I know this seems to think I am trying to create a scale-out-deployment, but thats not the case. I only want one instance or SSRS to access the DB at a time, not a reporting services webfarm.

    Seems a bit silly that this can't be done with standard edition for DR purposes when standard edition supports database mirroring.

    Given all of the above..........

    1. if I wanted to restore report server database after a server failure, would it allow me to do so if I had to create a fresh build, as it's cleary keeping information about the server / database somewhere?

    2. Does anyone know a way I could get around this issue to implement my solution? (and please don't say buy Enterprise edition 🙂 )

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • Dave, make sure you are following the processes as described in this url

    http://support.microsoft.com/kb/842425

    you will have to make some changes (db restore not required as you are mirroring instead) but make sure the gotchas it refers to are covered. You may have two installation ids and have to delete the one from the primary.

    also be sure configuration via reporting services configuration manager is identical on both boxes (except for the connection server in the database settings tab.)

    You would have to an initial reporting services install on the standby server and then drop the databases and start mirroring from primary

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

  • Thanks George. I'll take a look at the link and let you know how I get on.

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • sorted. Thanks George.

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • thats good to hear.

    This is not a process I enjoy doing (RS error messages are so esoteric). did it go smoothly, any hiccups? was the procedure in the link exactly what you required?

    Interested in other peoples experiences trying to provide failover for RS.

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

  • Yeah - the link was very useful!

    The key information I needed out of it was to delete the keys form the database Keys table and then restore the encryption key from the backup, using RSKeymgmt.exe

    I'm just got to try and automate the process now if I can and I might (if people are interest) write a little article on it at some point, as I can't find much on RS disaster recovery.

    I'll let you know if I get automated.

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • cheers Dave. Any article on administring RS would be good, it does seem to ba a neglected area.

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

  • Hello Dave,

    I would be interested to know if you get automated because I have the same configuration to install.

    Thanks,

    Brigitte

  • I got it working, but just haven't had a chance to write a document about yet.

    the key thing is to restore the report server keys as part of the failover process.

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • could you write the steps?

  • moamidino (2/3/2010)


    could you write the steps?

    see the link in my first post for the process

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

  • yeah - will do.

    I'm planning on writing an accurate on it for sql server central - hopefully Over the course of the next week or so).

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • For those of you interested, I've submitted an article about this (more of a how to guide really), which focusses around SQL Server Standard edition. It will be titled (pending approval) Reporting Services Disaster Recovery

    It may be useful to some of you or at the very least give you some idea about you may want to go about your Reporting Services disaster recovery.

    I hope you find it helpful. (thanks again to george sibbald for pointing me in the right direction for restoring reporting services).

    😀

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • look forward to it dave.

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

  • Dave,

    Will your article be available soon?

    Brigitte

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

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