Overview

SQL Server 2005 reporting services support scale-out deployment in Enterprise version. Unfortunately the scale-out deployment is not supported in Standard version of SQL Server 2005.

Some time ago I received a task to make automatic failover solution on reporting services across 3 SQL server reporting services instances on 3 different machines. There was setup a load balancing for that machines and it was necessary do create a solution when all 3 instances are fully synchronized and if some of the servers fails, user cannot be affected by this situation. (User must be able to run their reports and must receive all reports to which they subscribed. Also it is necessary when users create some reports, they must be automatically deployed across all 3 nodes).

When you run 2 or more instances of the SSRS standard version against one SSRS database which is normal in the enterprise version, you will receive a message, that the scale-out deployment is not supported.

So you can make a redundancy making 3 separate instances with 3 separate databases. Unfortunately this solution is hard to maintain in situation when you have hundreds of different reports on the server and new one are often created and some modified. Also it is hard to maintain subscriptions for the reports.

Solution

I started about solution for that situation and was investigating for some time.

Finally I’ve played with Replication which is supported by the standard version and it works. I’ve configured a fully automatic fail-over solution using the standard version of SSRS, which also correctly handles user subscriptions.

Setting up SSRS scale-out deployment on standard version of SQL Server 2005

The steps for successful setup of scale-out deployment using the standard version you need to setup the primary node first and then all the secondary nodes. The term primary node here means the one which database will be published for the other (secondary) nodes. It is also necessary that all nodes runs the same version of SQL Server Reporting services including the same service packs and patches. Also this assumes, that the report server databases will be running on the same instance of SQL Server (or Cluster).

Setup primary SSRS node

So we install and setup first node of the SSRS services. As we will be using more nodes, we have to backup encryption keys of the primary SSRS instance as it will have to be used also by all other instances.

After the primary instance is fully setup, we will create a Merge publication for the primary database. We will publish only tables and will publish all tables except the [Keys], [Events], [Notifications] tables. We publish only the report server database, not the temporary database.

We need to exclude the Keys table, because each instance has it’s own key stored in the table. Event we later restore the encryption key and it will be the same among all instances, each instance has it’s own name and creates a new entry in the Keys table. And when there is more than one entry in the table, it is automatically detected as scale-out deployment, which is supported only by the Enterprise version.

The Events and Notifications tables are used for monitoring of Subscriptions and events in SSRS. To support correct subscriptions processing we cannot replicate those tables.

The Merge replication is necessary as users are automatically directed to the nodes by load balances and we need automatically update all 3 nodes whenever there is any change on any node.

Setup secondary SSRS nodes

Once the primary node is set-up, we can setup all secondary nodes and the steps will be the same for each additional node.

So we install and setup the secondary node as normal standalone installation of SSRS. Once the node is set-up and configured, we restore encryption key of the primary SSRS instance to be able to share encrypted data among all instances.

After the encryption key is restored, we will setup replication for the database of the secondary node. Then we are ready to subscribe to the publication of the primary node database. You can run the replication agent continuously or schedule it depending how quickly you need the changes among nodes to be synchronized. I suggest also to run the subscription under Server subscription type with each node set up to other priority, so you have automatic conflict resolution when multiple users eventually changes the same report at the same time.

Metadata changes on databases

First we need to update [dbo].[Schedule_DeleteAgentJob] trigger on the [dbo].[Schedule]. This update we do on the Primary node database and will be automatically replicated to the secondary nodes. It is necessary, because there is a bug in the trigger (event in SSRS 2008). For each subscription in SSRS there is a job created. And if you delete the job first, then you are unable to delete the subscription at all as the trigger fails and the delete subscription is rolled back. The problem is, that the existence of the job is not checked in the trigger before deletion. And we need this check, because when the table is replicated then the trigger will try to delete the job again and will fail.

ALTER TRIGGER [dbo].[Schedule_DeleteAgentJob] ON [dbo].[Schedule]
AFTER DELETE
AS
DECLARE id_cursor CURSOR
FOR
    SELECT ScheduleID from deleted
OPEN id_cursor
DECLARE @next_id uniqueidentifier
FETCH NEXT FROM id_cursor INTO @next_id
WHILE (@@FETCH_STATUS <> -1) -- -1 == FETCH statement failed or the row was beyond the result set.
BEGIN
    if (@@FETCH_STATUS <> -2) -- - 2 == Row fetched is missing.    
    BEGIN  -- Check that job exists before trying to delete it.         
        IF (EXISTS(SELECT 1 FROM msdb.dbo.sysjobs WHERE name = CONVERT(nvarchar(128), @next_id)))
            exec msdb.dbo.sp_delete_job @job_name = @next_id -- delete the schedule    
    END
    FETCH NEXT FROM id_cursor INTO @next_id
END
CLOSE id_cursor

After update of the trigger we need some metadata changes on all secondary databases. We need to drop tables [dbo].[Event] and [dbo].[Notifications] and Create instead of the aliases to the primary server databases. It is necessary so there is correct processing of the subscriptions and other events. IF we do not make this change, it will often happen, that the event will be processed by more than one node, you will receive exceptions or receive subscribed reports more than once or the processing never ends. By this change it is ensured, that only on node processes the event, as when a node is taking event it puts lock on the tables and no other nodes can process the event.

-- Drop Event Table 
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Event]') AND type in (N'U')) 
    DROP TABLE [dbo].[Event] 
GO 

-- Create Alias for the Event table in the Publisher database 
CREATE SYNONYM [dbo].[Event] 
FOR [PrimaryReportServerDB].[dbo].[Event] 
GO 

-- Drop the Notifications table 
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Notifications_Subscriptions]') AND parent_object_id = OBJECT_ID(N'[dbo].[Notifications]')) 
    ALTER TABLE [dbo].[Notifications] 
        DROP CONSTRAINT [FK_Notifications_Subscriptions] 
GO 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Notifications]') AND type in (N'U'))
    DROP TABLE [dbo].[Notifications] 
GO 

-- Create Alias for the Notifications table in the Publisher database 
CREATE SYNONYM [dbo].[Notifications] 
FOR [PrimaryReportServerDB].[dbo].[Notifications] 
GO

After this modifications you have fully working Load balancing and fail over of SSRS under SQL Server 2005 standard.

Encryption Keys and Not Supported Scale out deployment feature error message

It is essential to have the same key on all nodes to be able to read the data among nodes. Also as mentioned it is essential not to replicate the Keys table. As each node adds its key under its instance and machine name into the table and it is used for scale out deployment of Enterprise edition of SQL server. Unfortunately when standard edition finds more keys in the table it shows error messages about unsupported scale out deployment.

If this eventually on some or all nodes happen it is necessary to delete all records from the table except the record with value -1 in the Client column. Then it is necessary to restore the encryption key again to be able to access encrypted data.