Wednesday, October 21, 2015

Adding SSRS instances without configuring scale-out (in Standard Edition Scenario)

 

Below are steps to manually configure SSRS failover to alternative instances based on Standard Edition, lab experimental only, use in production at your own risk.

1. Below Key operations added to Keys table during the Report Server DB configuration

declare @p5 int
set @p5=NULL
exec AnnounceOrGetKey @MachineName=N'VMMSQL02',@InstanceName=N'SE',@InstallationID='74706FB6-0740-4F57-931E-54702B387643',@PublicKey=0x0602000000A40000525341310008000001000100C1284D20B9F13FD33C12CEF6E791A238B78472AB467F32CC4B9B12537F7BAE4FF8D369C75039503EA55AC461C79E9482CBAB08242E6FB67A12596C52A0BD8AED961C1D0175557826388F8B9E1E92FD36EE4BB5DDB46320BD990C928D3D46AAD344015478957CBA0EFB692019DE1215696AF72B5E8AA6C419D1C7CC881F4399FE69F03995AEEFAA9DCDB108F6022428942C05E4405C80E8198584C4D564100DC5F8A39D844A0C68546E9E9E36623604990E7792DDD62919729C2CCCF800995880EEFFBF9E994A196824DBBF41B79A04E8C1FB8FCF87AA7D18EA50BD874D140BF8B2E7D79196FD2FBC14C7FF3AB8D574C3A0298C95BC17D216BA403D272556EABA,@NumAnnouncedServices=@p5 output
select @p5
go

image

2. On VMMSQL01 (original), the “Test Report” runs without problem, but failed on VMMSQL02.

image

image

library!ReportServer_0-1!9f0!10/21/2015-23:06:31:: e ERROR: Throwing Microsoft.ReportingServices.Library.ReportServerNotActivatedException: , Microsoft.ReportingServices.Library.ReportServerNotActivatedException: The report server installation is not initialized.;
crypto!ReportServer_0-1!f9c!10/21/2015-23:07:29:: i INFO: Initializing crypto as user: DBAGLOBE\sqlsvcs
crypto!ReportServer_0-1!f9c!10/21/2015-23:07:29:: i INFO: Exporting public key
crypto!ReportServer_0-1!f9c!10/21/2015-23:07:29:: i INFO: NT Service not activated
library!ReportServer_0-1!f9c!10/21/2015-23:07:29:: e ERROR: Throwing Microsoft.ReportingServices.Library.ReportServerNotActivatedException: , Microsoft.ReportingServices.Library.ReportServerNotActivatedException: The report server installation is not initialized.;
library!ReportServer_0-1!f9c!10/21/2015-23:07:29:: e ERROR: Throwing Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: , Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> Microsoft.ReportingServices.Library.ReportServerNotActivatedException: The report server installation is not initialized.
   --- End of inner exception stack trace ---;

3. Manually Restore the encryption key to VMMSQL02

image

 

image

4. VMMSQL02 still failed to render report due to feature limitation of Standard Edition

image

5. Manually removed the SSRS instance (VMMSQL01) from KEY table, Reports worked on VMMSQL02

select *
into [ReportServer].[dbo].[Keys_BAK]
from  [ReportServer].[dbo].[Keys]
GO

delete from [ReportServer].[dbo].[Keys] where MachineName='VMMSQL01'
GO

image

image

6. In the event of failback required, manually insert the KEY for VMMSQL01 SSRS instance again

insert into [ReportServer].[dbo].[Keys]
select * from [ReportServer].[dbo].[Keys_BAK] where MachineName='VMMSQL01'
GO
delete from [ReportServer].[dbo].[Keys] where MachineName='VMMSQL02'
GO

image