Recently I was asked by a customer to build a high availability solution for their SQL reporting environment. The report server database was installed on a SQL Server Failover Cluster. Alright, let’s install a SSRS scale-out deployment. However… my customer didn’t want to buy a SQL Server 2012 Enterprise Edition, so I had to come up with a solution to provide high availability with a standard edition.
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.
I found following blog post http://www.pawlowski.cz/2009/12/sql-server-2005-reporting-services-scale-out-deployment-ssrs-2005-scale-out-deployment-2/ of Pavel Pawlowski (Blog|Twitter) which actually explains what I needed. The idea is to replicate the report server databases from the primary server to the secondary server. A load balancer provides the failover functionality.
The picture below shows you how the setup should be.
In this blog, I’m going to explain how I installed the solution and what issue’s I had during installation and configuration.
Installing first server
On the first server, I have installed the SQL Server 2012 database engine + the SQL Server 2012 Reporting services. I slipstreamed the installation with SP1 CU2, so I’m using the latest patches of SQL Server. When the installation was finished, I just configured the reporting services in a very standard way as a stand-alone report server. The report server databases were created on the same server.
When the installation and configuration is finished, you have to take a backup of the encryption key. We will need it on the second server.
Installing second server
Next step is installing the secondary server. The method will be exactly the same as the first server. Just install the report server as it was a stand-alone server. Also make sure that you create the reporting server database on the server itself. Once the server has been setup, you have to restore the encryption key from the first node to be able to share encrypted data between the 2 servers. To do so
Open the report server configuration manager
Once you’re connected select Encryption Keys
And click the button Restore
Copy the encryption key backup file from node 1 to node 2.
Select the file location and type in the correct password that you have used for the backup.
Press OK to restore.
Drop the foreign key on the Notifications table as it will cause an error when setting up the replication later on.
Setup merge replication publication
After both report servers have been installed, you have to setup a merge replication for the reportserver DB. Pavel mentioned in his blog to use merge replication because it is necessary as users are automatically directed to the nodes by load balancers and we need automatically update all 3 nodes whenever there is any change on any node.
However, in my situation, the load balancer will always send the requests to the first server. In case of failure, it will point to the second node. The reason is that my customer only has a budget to buy one Standard license. So the second server is considered as a hot standby. This setup may lead to some difficulties with Microsoft licensing, but that’s another discussion.
I tried to work out the same configuration but tried to use another publication type.
- Snapshot publication: not really useful in this situation
- Transactional publication: I was able to setup the publication and subscription; however as soon as you tried to launch a report on the second server, you got primary key violation errors.
- Peep-to-peer publication: Is only available in Enterprise Edition, so no option
Conclusion: Merge replication is the only possible publication that you can use here.
To setup the publication follow these steps:
Connect to the first SQL Server and go to Replication – Local Publications.
Right-click on Local Publications and select “New Publication”
Use the primary server as its own Distributor
Select the reportserver database. It’s not necessary to publish the reportservertempdb
Select the Merge Publication Option
Since I’m using 2 new installations with SQL Server 2012, I select the option”SQL Server 2008 or Later”
Next step is choosing the Articles for the publication. As Pavel mentions in his blog, we need to exclude the Keys table, because each instance has its own key stored in the table. Each instance has its 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.
When the scheduled time comes for a scheduler , the SQL Server Agent generates an event by executing the scheduled job. The job inserts a row in the Event table of the ReportServer database. This row serves as an event for the Scheduling and Delivery Processor. The event record will be deleted after the process.
When you click Next, the wizard returns some Article issues
A uniqueidentifier column will be added to a few tables because they don’t contain a uniqueidentifier column. If you have a look at the tables, they are just containing some upgrade history. I have included those tables in my publication.
Strange issue, I have noticed is that, even if your table has a uniqueidentifier column, there is still an extra column added to the original table…
After I finished my tests, it looks like reporting services doesn’t have any problems with that extra column. I don’t know what will be the behavior when you will perform an update of the reporting services in the future. So possibly you could have some upgrade issues.
I don’t need to filter the data so, just click next
Create the snapshot immediately as we will add a subscription later on.
I have specified the Snapshot Agent security to the SQL Server Agent Account.
At the end, we want indeed to create the publication.
Complete the wizard by clicking Finish
This should be your result after the wizard has finished.
Setup merge replication subscription
Now we are ready to subscribe to the publication of the primary node database.
To do so, connect to the second node. Go to replication – Local Subscriptions.
Right click and select “New Subscriptions”
Select the first node, which contains your publication.
I have chosen to run the Merge Agent on the subscriber and not on the Distributor
Choose the database ReportServer as the subscription database. This is the empty database that was created during the installation.
Choose the impersonate process account option for the Subscriber
As the replication will be used for a high availability solution, I want the agent schedule to run continuously. The changes on both sides must be replicated as quickly as possible to the other server.
Choose to initialize the subscription immediately.
Select “Client” as Subscription Type. Pavel mentions in his blog to choose Server, but my situation is a little bit different. I only have one secondary server and all the report requests are sent to the primary node. So, in my case, it’s better to choose client.
We want to create the subscription at the end of the wizard
Click finish to end the wizard and to configure the subscription
Make sure that the SQL Agent Service account is created as a login
When the replication is working, you should get the following result in the Replication Monitor
I had a few issues with the creation of my snapshot, but that was caused by some security issues.
While I was testing I have noticed that the delete of a subscription gave the following error.
Column name or number of supplied values does not match table definition.
I came to a conclusion that the stored AddSubscriptionToBeingDeleted was giving the error.
An insert was done into the table SubscriptionsBeingDeleted but the number of columns was not correct anymore because of the rowid column that was added for the merge replication. Below you can find the stored procedure that solves this issue. You should execute it on both servers
ALTER PROCEDURE [dbo].[AddSubscriptionToBeingDeleted]
— Delete subscription if it is already in this table
— Delete orphaned subscriptions, based on the age criteria: > 10 minutes
delete from [SubscriptionsBeingDeleted]
where (SubscriptionID = @SubscriptionID) or(DATEDIFF( minute, [CreationDate], GetUtcDate() ) > 10)
— Add subscription being deleted into the DeletedSubscription table
insert into [SubscriptionsBeingDeleted] VALUES(@SubscriptionID, GetUtcDate(),newid())
Now you have a fully operational high-available reporting solution with SQL Server Standard Edition.
The client PC’s are connecting to a network load balancer. This load balancer is always sending the report requests towards the primary report server. The load balancer will point to the secondary server, in case of a failure of the primary server. The secondary server has been setup exactly the same way as the primary node. Between the 2 nodes we I have installed a SQL Server merge replication. This will replicate all the changes between primary and secondary and the other way around. With this configuration we provide a high availability solution.
Have fun with it!