November 15, 2010 at 10:08 pm
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
November 16, 2010 at 2:53 am
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)
November 16, 2010 at 4:01 am
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
October 17, 2013 at 7:46 am
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?
October 17, 2013 at 9:13 pm
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
October 18, 2013 at 9:14 am
TheSQLGuru (4/21/2010)
http://sqldbatips.com/showarticle.asp?ID=62
The link did not work
October 22, 2013 at 2:38 am
l543123 (4/21/2010)
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?
> Just realised I already answered this earlier in the discussion thread 🙂
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
October 22, 2013 at 2:49 am
carl.landry (10/17/2013)
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?
Hi Carl.
It's something that I don't cover in this article and frankly I've never got round to doing, but you would need some form of process to compare the SQL agent jobs on both servers and see what gets created / deleted / modified and replicate that across to the other server.
I have had moments where i've looked at doing this using a combination of SSIS and scripting out jobs using .net and smo.
Hopefully one day I will actually write something up! 🙂
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
April 24, 2014 at 11:52 am
Hi guys,
Just a quick FYI that Database Mirroring is deprecated as of SQL Server 2012. Usually that means that the feature will disappear from SQL Server two major versions after the deprecation announcement (i.e. it's not any time soon).
You may want to consider whether you want to use this particular method of DR.
References:
http://technet.microsoft.com/en-us/library/ms143729.aspx
https://www.google.com/search?q=sql+server+mirroring+deprecated
April 7, 2016 at 5:50 am
Hi Dave, this was quite helpful in implementing our first SSRS DR environment.
In this case, the RS hosts are separate from the SQL database instances and I had to modify your jobs to run 'sc' to start and stop the RS services remotely, and use 'psexec' to restore the encryption keys.
Also, the RS data sources connection strings have to use the Failover Partner parameter. And since we're using credentials stored securely on the report server, the RS service accounts that we use must have privileges on both RS hosts.
We also employ Kerberos authentication so it's part of the standard RS and SQL builds that we do.
One thing though, and this is just the perfectionist in me. Your article kept on saying "Principle" instead of "Principal". And it bothered me. But you were forgiven because your scripts helped me tremendously.
Keep up the great work Dave. 😉
April 7, 2016 at 7:56 am
One thing though, and this is just the perfectionist in me. Your article kept on saying "Principle" instead of "Principal". And it bothered me.
Was it the principle of using the correct principal/principle correctly that bugged you? 😀
I'm pleased the article was helpful though and that it was useful as a base to improve on for you 🙂
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
April 17, 2017 at 7:08 am
l543123 - Wednesday, April 21, 2010 10:27 AMNice 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?
Hi,
May be its lts late but i need some help. Have you tried this approach and got answer for your question ?
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply