SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reporting Services Disaster Recovery


Reporting Services Disaster Recovery

Author
Message
dave-dj
dave-dj
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2636 Visits: 1149
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
l543123
SSC Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
Points: 218 Visits: 175
thanks Dave, will try this out. will try with 2 test servers.
Donatello
Donatello
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 339
I am wondering the same thing. Did you ever get an answer to this?
dave-dj
dave-dj
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2636 Visits: 1149
sorry answer to what?

What was the question?

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
lilyahuff
lilyahuff
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 92
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?
SimonLiew
SimonLiew
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4268 Visits: 1836
Hi Dave,
Its a wonderful steps you've provided in your article.

I've been SSRS admin for quite some time. Reading through your article, I was particularly looking for the section where you apply permission scripts from the principal to the DR server but couldnt find it.

Particularly, I'm referring to RSExecRole being created on the DR. Would switch to DR still work without this database role in msdb?

Simon

Simon Liew
Microsoft Certified Master: SQL Server 2008
dave-dj
dave-dj
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2636 Visits: 1149
Hi Simon.

the RSExecRole wasn't something I ever really considered - most likley because I never encountered an issue with security and accessing the report server db.

As you mention it, my initial process was to install Reporting Services on both the servers that you want to use in the DR solution, following that i dropped one of the Reporting Server databases and then set up mirroring etc etc as per article. I believe as I done the standard install on both servers, it took care of the RSExecRole for me.

The area where you will need alot of consideration (and I haven't got around to doing this yet) is RS scheduled report jobs.

As the scheduled report jobs are created on th SQL server instance, you will need a mechanism for copy the Principle Report Servers scheduled report jobs to the backup server. I'll document this once I've done it, but my initial thoughts are to look at what jobs are listed in the report server db and those that are in the msdb sysjobs table, copying / deleted the sql agent jobs as appropriate.

let me know it I've not answered your question

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
SimonLiew
SimonLiew
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4268 Visits: 1836
I see. That definitely answered my question.

The installation would've taken care of the permissions.

thanks,
Simon

Simon Liew
Microsoft Certified Master: SQL Server 2008
carl.landry
carl.landry
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2374 Visits: 278
I never looked at this very deeply but I believe that scheduled report executions (for subscriptions, for example) create a SQL Agent task to program the execution at the specified time. It calls the "[ReportServer].dbo.AddEvent" procedure.

If those tasks are not replicated, can I expect that these executions will not happen automatically when switching over?
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32103 Visits: 8672
please start a new thread instead of commenting on a 3 year old one

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search