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
November 7, 2011 at 8:03 AM
Rohit said…
Thanks a lot Vinay for posting and appreciation of the concept.
November 7, 2011 at 8:08 AM
Post a Comment

Popular posts from this blog

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) { ...
Post a Comment
Read more

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.
1 comment
Read more

MAX pool size reached in ASP.NET

If you are handling large databases and big application(s) that are running on servers in a cluster, then probably this error might have occurred. In an ASP.NET app, we can use connection pooling that helps to share the connection between multiple request(s) rather than creating more connections. The settings relating to connection pool are defined in web.config file. Max pool size reached error in ASP.NET occurs, when the number of connections go beyond the maximum defined limit in Web.config file. Following are the various settings of the Connection Pooling: Name Default Description Connection Lifetime 0 When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by  Connection Lifetime . This is useful in clustered configurations to force load balancing between a runnin...
Post a Comment
Read more