Port For SQL Server - What is default...

Port For SQL Server - What is default port for SQL Server ? OR On which port SQL Server Database Services Runs ? OR ... http://ping.fm/Szo8G

SQL Script to find Expected Finish TI...

SQL Script to find Expected Finish TIME for current running BACKUP / RESTORE http://ping.fm/81Ubh

TOP SQL Server Posts for 2011 - This ...

TOP SQL Server Posts for 2011 - This is a quick review of top posts for year 2011 How to Setup Replication with SQL ... http://ping.fm/x3QBJ

SQL Server Licensing ? Core Based Lic...

SQL Server Licensing – Core Based Licensing - Microsoft SQL Server will no longer be licensed by the CPU socket and ... http://ping.fm/B4lM1

SQL Server Injection - What is SQL In...

SQL Server Injection - What is SQL Injection? The ability to inject SQL commands into the database engine through an... http://ping.fm/wX3Vf

SQL WHERE ? Video Tutorial - Prior to...

SQL WHERE – Video Tutorial - Prior to understand Where clause, lets do a quick recap of SQL and then we will focus o... http://ping.fm/QXklF

How to Backup SQL?Video Tutorial - HO...

How to Backup SQL–Video Tutorial - HOW to BACKUP SQL ? How to backup SQL Server Database ? Ensuring reliable backups... http://ping.fm/qYvKZ

Installing SQL Server 2008 R2 SP1 - I...

Installing SQL Server 2008 R2 SP1 - I was rebuilding my machine this week and I installed three Instances of SQL Ser... http://ping.fm/mSxfh

How to Export SQL Profiler / Trace to...

How to Export SQL Profiler / Trace to SQL Server as table ? - I am running a running a SQL Server Profiler, a graphi... http://ping.fm/TNG3C

SQL Server Version / Builds List Comp...

SQL Server Version / Builds List Complete - In my previous post, where I was maintaining all SQL Server Versions and... http://ping.fm/hPTay

SQL Server Management Studio (SSMS) i...

SQL Server Management Studio (SSMS) is slow - My SQL Server Management Studio (SSMS) takes approx. 2-3 minutes to op... http://ping.fm/azrHs

SQL Server Express with Reporting Ser...

SQL Server Express with Reporting Services - Can we run Reporting Services with SQL Server Express Edition, which is... http://ping.fm/TJa0G

Installing SQL Server Express Edition...

Installing SQL Server Express Edition on Windows 7 (Screen Shots) http://ping.fm/2sNfl

Installing & Configuring SQL Server Reporting Services on Cluster Environment for High Availibility

I was working on project, where there is only a one cluster box which is being used for Database Services to meet high availability requirements.

Customer, understood some reporting Services feature so asked developers to build some required reports, which was being done. Now things need to be pushed to production so a requirement for installing and configuring the Reporting Services in a Clustered environment to meet the high availability requirements.

As per the customer, things were pretty straight forward as he assumed that Reporting Services are also cluster aware as SQL Server Database engine. So customer asked me to asked a install reporting Services on the clustered environment to meet the high availability, one of major business requirement.

When I shared the activities / project plan to customer, he was shocked to see one of the design slide, which I pasted below, he never thought that there will be a Scale OUT solution to meet the high availability requirement.

Customer was not aware that “Reporting Services is not cluster-aware”, YES SQL Server Reporting Services are not cluster aware.

So let me summaries the entire activity / project with detailed description to install and configure reporting services on a clustered environment to meet high availability requirement.

In short a reporting services should be available as one name / url for user to access report server, regardless of fact on which node it is running, a kind of virtual url for report server

Broad Steps / Activites

1. Install first Reporting Services Instance on NODE 1 of clustered environment, choose Install but do not configure server option  during Report Server Installation Options page
2. Configure Reporting Services using Reporting Services Configuration tool
   • Report Server Web service URL,
   • Report Manager URL,
   • Report Server database
   • Report server temp database etc.
3. Install second Reporting Services Instance on NODE 2 of clustered environment, choose Install but do not configure server option  during Report Server Installation Options page.
4. Configure Reporting Services using Reporting Services Configuration tool and Connect report server to the same database you used for the first report server instance. (Instead of creating a new database, opt for Choose an existing report server database)
5. Configure the Report Server Web service URL, which would be definitely a different than the first as this would be set up on NODE 2
6. Join second report server instance to the scale-out deployment.
   • Open the Reporting Services Configuration tool, and reconnect to the first report server instance which is running on NODE 1 and click on Scale-out Deployment to open the Scale-out Deployment page.
   • You should see two entries, one for each report server instance that is connected to the report server database. The first report server instance should be joined. The second report server should display a status of "Waiting to join". If you do not see similar entries for your deployment, verify you are connected to the first report server that is already configured and initialized to use the report server database.
   • On the Scale-out Deployment page, select the report server instance that is waiting to join the deployment, and click Add Server.
7. Till now we have configured the Reporting Services Scale out solution and both individual reporting services instances access common reporting services database. As this point, we should be verify that both reporting services instances are working fine.
   • in my case, was able to verify the first instance with url “http://NODE1/reportserver” and
     “http://NODE2/reportserver”, second reporting instance
   • Now I need to do a configuration that a single url can be used to access services from the both url.
8. Configure view state validation by editing the Web.config file for Report Manager for both report server instance.
   • Generate a validation key. You can use the autogenerate functionality provided by the .NET Framework or you can create the key some other way. Do not use the AutoGenerate option when setting the validationKey attribute.
   • Generate a decryption key. For the decryption key, you can create an explicit value or set decryptionKey to AutoGenerate
   • Open the Web.config file for Report Manager and set the <machineKey> element. You must specify the validation key, decryption key, and the type of encryption used for validation of data. Example <machineKey validationKey="43ajhk3ebc97uj826a7b3v37k903a9d5dy65" decryptionKey="86165h77a9d588a9" validation="SHA1"/>
9. Configure a virtual Server name to access reporting Services. We need to do this for
   1. Report Manager (configuration file RSWebApplication.config), set the <ReportServerUrl> to the virtual server name and remove the entry for <ReportServerVirtualDirectory>. This step ensures that all requests coming through Report Manager are load-balanced to the report servers that are running in the scale-out deployment. The following example illustrates the syntax you should use; it specifies the virtual server name and report server virtual directory: example

      <ReportServerUrl>https://MyReportPortal/reportserver</ReportServerUrl>
      <ReportServerVirtualDirectory></ReportServerVirtualDirectory>

      
      
   
   
   
   2. Report Server Web service (configuration file RSReportServer.config), Set the <UrlRoot> to the virtual server address. This step ensures that all hyperlinks in reports point back to the scale-out deployment and are load-balanced accordingly. This setting is also used to complete report delivery. The following example illustrates the syntax you should use: example
      
      

      <ReportServerUrl>https://MyReportPortal/reportserver</ReportServerUrl>

      
      
   
   
   
   3. This files can be found on each node at C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer
   
   
   
   



10. Now ask your network administrator to create a new DNS record for this new virtual url (report server url) and point that to IP addresses of cluster nodes, so that if client request for that virtual url, the request should reach to any of the node on the cluster and also request if can can built a system to verify the availability of all associated IP addresses, if none of them in not available then do the resolution only with available IP addresses.



11. Once that is being done, you are good to go.

So as part solution your reporting services will be running of both the nodes simultaneously, in case a any one node fail, reporting services will still be available on second node, and will be accessible to users.

It is recommended to use Load balancer, or web farm to build this solution, but none of the option was available in my case, so I worked with this workaround.

Merry Christmas !!! - Bells are ringi...

Merry Christmas !!! - Bells are ringing and everyone is singing, It's Christmas! It's Christmas! Wishing you a Merry... http://ping.fm/Pr0oA

Merry Christmas !!!

Bells are ringing and everyone is singing,
It's Christmas! It's Christmas!
Wishing you a Merry Christmas and a Happy New Year.

How to Give Read Definition Permissio...

How to Give Read Definition Permission to a User for all Procedures and Functions http://ping.fm/ANAM6

SQL Server 2012 Editions Licensing Co...

SQL Server 2012 Editions Licensing Costing - SQL Server Licensing is depends on which edition your are buying and un... http://ping.fm/xgmV1