Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SSRS with DB Mirroring for Disaster Recovery Expand / Collapse
Author
Message
Posted Thursday, January 7, 2010 6:55 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 2:57 AM
Points: 405, Visits: 1,137
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)
Post #843493
Posted Thursday, January 7, 2010 7:34 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 5,992, Visits: 12,941
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


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

Post #843538
Posted Thursday, January 7, 2010 8:24 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 2:57 AM
Points: 405, Visits: 1,137
Thanks George. I'll take a look at the link and let you know how I get on.

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Post #843592
Posted Tuesday, January 12, 2010 5:19 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 2:57 AM
Points: 405, Visits: 1,137
sorted. Thanks George.

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Post #846041
Posted Tuesday, January 12, 2010 5:45 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 5,992, Visits: 12,941
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.


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

Post #846055
Posted Tuesday, January 12, 2010 7:07 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 2:57 AM
Points: 405, Visits: 1,137
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)
Post #846127
Posted Tuesday, January 12, 2010 7:10 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 5,992, Visits: 12,941
cheers Dave. Any article on administring RS would be good, it does seem to ba a neglected area.

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

Post #846130
Posted Tuesday, February 2, 2010 8:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 24, 2014 3:55 AM
Points: 4, Visits: 181
Hello Dave,
I would be interested to know if you get automated because I have the same configuration to install.

Thanks,

Brigitte
Post #858007
Posted Tuesday, February 2, 2010 8:41 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 2:57 AM
Points: 405, Visits: 1,137
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)
Post #858010
Posted Wednesday, February 3, 2010 4:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:51 PM
Points: 41, Visits: 514
could you write the steps?
Post #858517
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse