Build an effiective Disaster Recovery solution for SQL Server Reporting Services

As a Systems Administrator/DBA  or even as an user have you ever realised "What is your data worth to you?"

If so then an important aspect of data recovery solution is a required step, whatever is the application or system you use data access methods including a disaster recovery plan is essential to ensure your data’s security after a catastrophic event.  It is incredibly expensive to retrieve data from failed hard drives, and not always possible. 

You may expect or no doubt that this blog states on SQL Server databases and its usage, its the time that many critical applications do rely on SQL Server to manage back end data. Many enterprise databases often span multiple volumes and handle thousands of complex transactions a day, so protecting this data with SQL Disaster Recovery is absolutely essential. Even a short outage can have a big price tag, while lost data can damage the business. The usual backup solution or to span the data recovery you may go with standard tape backups to protect the data is often not enough.

Happy, just having a backup solution or spending bucks on backup solution is not enough. Another big question that every user should raise that "Have you ever tested the backup solution, such as the time taken to recover the data"?  When a database gets corrupted or lost due to a hardware failure, you need to restore the data quickly. If your only option is recovering from the previous night’s backup, you face lost data and substantial downtime while you try to recover. everRun DR for SQL Disaster Recovery offers a new approach: efficient continuous data protection with application-aware recoverability.

Talking about Enterprise database then Enterprise Reporting is also a big deal to manage, SQL Server Reporting Services (SSRS) is a server-based enterprise reporting environment, managed through Web services, that delivers a variety of interactive and printed reports. SSRS architecture as per Microsoft page:

SQL Server Reporting Services supports the full reporting life cycle, such as report authoring that helps developers to create reports to be published to a report server by using Microsoft or other design tools that use Report Definition Language (RDL), an XML-based industry standard used to define reports. Also the ease of management using  report definitions, folders, and resources are published and managed as a Web service. Managed reports can be executed either on demand or on a specified schedule, and are cached for consistency and performance. New in SQL Server 2005 Reporting Services, administrators can use SQL Server Management Studio to organize reports and data sources, schedule report execution and delivery, and track reporting history. Not only that delivery of reports is a new feature that supports both on-demand (pull) and event-based (push) delivery of reports. Users can view reports in a Web-based format or in e-mail. Lastly, the report security implements a flexible, role-based security model to protect reports and reporting resources. The product includes extensible interfaces for integrating other security models as well.

Combining both of data access layer and reporting feature the following questions are big to answer:

• What would happen to your data if your server crashed or a hard drive failed?  
• Do you have the appropriate backup files to restore your data or would it be lost completely?   
• Do you have an offsite copy?
• When was the last time you checked on your nightly backup job to make sure everything was successful?
• What is your data worth to you?  

Talking about Disaster Recovery solution there are many ways to perform disaster recovery with SQL Server Reporting Services (SSRS). SQLCAT team has done a fantastic job to explain the solution based upon customer experience and internal testing, this technical note provides guidance around best practices to design and manage robust end-to-end disaster recovery (DR).

Here I would like to refer few whitepapers from SQLCAT team (thanks to MS pros) which are too good to implement, such as Building and Deploying Large Scale SQL Server Reporting Services Environments Technical Note Series is one of my favourite papers, also study about configuring and optimizing report catalogs, see Report Server Catalog Best Practices. Coming back to the DR solution you need to ensure that DR site should closely duplicate the primary data center Reporting Services environment, this is a one of the best practice. Say for instance if this needs to be another geographic location (country/origin) then you need to ensure outer-attributes such as network connectivity and power source is important. Again not by having a connectivity will not help and it will gain by testing all traffic can be redirected to the alternate site with minimal disruption to service.

When it comes to High Availability solution that can offer DR solution to SQL Server users is Clustering which is one of the Enterprise Edition feature. By mixing Windows Server 2008 and SQL Server 2008 each of these clustering techniques has unique advantages; ultimately, it is important to ensure that the report catalogs are consistently running with minimal downtime. So here is the another whitepaper from Microsoft on how to best configure a SQL Server clustering environment, see SQL Server 2008 Failover Clustering.

Denny Lee from SQLCAT team referred on their blog about Report Server Catalog Best Practices, and SSRS configuration for the database connection Configuring a Report Server Database Connection. As it states on the blog another important aspect of keeping the reporting database in sync because it stores all of the report metadata, including report definitions, report history and snapshots, and scheduling information. All Reporting Services operations must connect to this database to access its metadata to perform their functions. The reason asynchronous database mirroring is commonly chosen is that asynchronous mirroring has minimal or no impact on response time performance. Here is another important notes from Microsoft on using another high availability solution Database Mirroring & Replication the blog referred about when using asynchronous mirroring it works well see SQL Server Replication: Providing High Availability using Database Mirroring and Asynchronous Database Mirroring with Log Compression in SQL Server 2008.