Skip to main content

SQL Server Reporting Services Scale out Configuration

One of the major benefits that SQL Server provides is the scaling out the SQL Server Reporting Services. SQL Server Reporting Services are used as concept of hosting reports which various business processes requires. SQL Server Reporting Services does an efficient job in hosting all the reports. But the problem comes when the load starts increasing in terms of number of users accessing the reports. This may result in:
1.       Slow processing of SQL Server Reports
2.       Crashing of SQL Server Reports Server
In order to overcome the above issue, SQL Server Reporting Services can be overcome through the concept of scalability. This is implemented with the help of network load balancing (NLB) and Domain Implementation.
The following figure gives a clear cut picture of how to achieve the same:


In the above example, there are there servers in the NLB serving the clients through a Virtual NLB IP i.e. 192.168.1.25. The client only knows the Virtual IP, he/she does not knows the actual NLB setup. So the client sends the request of the report to the Virtual IP. Every server in the NLB is hosting the instance of the Reporting Services component for which the Report Catalog is hosted on 192.168.1.27 and actual database is hosted on the server 192.168.1.26. The above setup helps in dividing the control of report generation in 3 servers serving in the NLB. So if the number of users increase on the report server, then the 3 servers will divide the load and serve the request efficiently.
The above setup is still scalable. We can add more servers in the NLB as and when the load increases.

Comments

Vinay Anand said…
Daily 40000 reports delivered from one server is now distributed to 4 individual server. Load shifted to 10000 reports on each node.

Really Good concept.. Thanks Sir for helping to implementing this solution at LPU

Vinay Anand
Rohit said…
Thanks a lot Vinay for posting and appreciation of the concept.

Popular posts from this blog

SQLServer Error: 15404, Could not obtain information about Windows NT group/user Error code 0x5. [SQLSTATE 42000] (ConnIsLoginSysAdmin)

If we encounter this error "SQLServer Error: 15404, Could not obtain information about Windows NT group/user Error code 0x5. [SQLSTATE 42000] (ConnIsLoginSysAdmin)" in SQL Server in which the job fails because of the user account related problem, then we need to take the following steps to make it work: a. Go to SQL Server Agent. b. Then select the job which is giving error and not running successfully. c. Then choose properties and in the General Tab, change the owner to "SA" or any account that has the administrative privileges. The problem will be resolved.

Connecting Nodejs to SQL Server with Knex ORM - Part 1

Normally we connect Nodejs to the databases like SQLite, MongoDB, Postgres etc . In this multiple part post, I am going to show you, how we can connect Nodejs with Microsoft's SQL Server using Knex and run CRUD operations. 1. Installation of required packages In order to connect Nodejs to SQL Server with Knex, we will need following node packages installation: i. npm install knex ii. npm install mssql Once the packages are installed, we can write the following code to connect: //Code to KNEX connection settings. var knex = require('knex')({ client: 'mssql', connection: { user: 'sa', password: 'pwd', server: 'localhost', database: 'Test' } }); //Code to query the Table DEPT knex.select("*").from("dept") .then(function (depts){ depts.forEach((dept)=>{ //use of Arrow Function console.log({...dept}); }); }).catch(function(err) { ...

How to use QueryExtenderControl in ASP.NET with LINQ

Queryextender control is a control that helps in doing actions like filtration, searching, sorting etc. on the LINQ Data source with few steps. In fact we don’t have to write any code with this. Screenshot given below is just an example that can easily be achieved through this control. In the above screenshot, we can see that there is a GridView Control that has been binded to a LINQData Source Control. And we have a DropDownlistBox that is also binded to another LINQData Source Control and displaying only grouped jobs. The GridView display all the employees who are matching the Job Parameter. This is done through QueryExtender Control. Also we can search for any name in the employee name's through Query Extender control. The code in the HTML goes like: < form id ="form1" runat ="server">     < div >         Select Job: < asp : DropDownList ID ="Job" runat ="server" DataSourceID ="LinqDataSource2...