How to display SQL Server 2008 (Server Dashboard Performance) on a ASP.Net Web page with Dashboard Stats?

Question

Is there a way to bring and display the Server Dashboard report of SQL Server instance, configurations, and activities on a ASP.Net web page? I know that SQL Server 2008 R2 has that built-in feature to generate a standard report, any ideas on how to replicate and bring that report for end-user view in ASP.Net/C#? Thanks a lot for the help.

Answer 1

These are the steps to configure and display a Server Dashboard Performance with an external URL link and within MS SQL Server 2008 R2:

Display the Performance Dashboard Report within MS SQL Server 2008 R2:

1- Download the Microsoft SQL Server 2012 Performance dashboard Reports from: http://www.microsoft.com/en-us/download/details.aspx?id=29063 Run the script from SSMS by executing setup.sql

2- Once downloaded, you can run the report by going to SQL Server Management Studio and connect to your local server. Under Object Explorer right click (local)(SQL Server) > Reports > Custom Reports… locate C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard\performance_dashboard_main.rdl and Run. You should get the report.

Display the Performance Dashboard Report with an external URL link:

1- Under MS SQL Server 2008 R2 go to Configuration Tools > Reporting Services Configuration Manager and connect to your local Server or click to find it (Start the Report Server).

Specification tips:

• Service Account = Use built-in Account: Network Service (Default)

• Web Service URL = Leave Default for all configurations

• Database = [Current Report Server db] / Click - Change Database

  • Action: choose existing report server db

  • Database Server:
    Server Name = Same as your local server name that you connected
    Authentication Type = Current-User Integrated Security (Test Connection)

  • Database:
    SQL Server Instance = Same as your local server name that you connected
    Report Server db = ReportServer
    Report Server Mode = Native
    Credentials/Login-Password = Leave default/AdministratorAccount to access the external link. (Recommended)
    Authentication Type = Service Credentials
    [Current Report Server db Credentials] / Leave Default

• Report Manager URL = http://<ComputerName>:80/<YourReportName>

• Email Settings = Optional

• Execution Account = Default (Blank)

• Encryption Keys = Default

• Scale-out Deployment = Default

2- Click the Home link of the Report Manager URL and upload: C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard\performance_dashboard_main.rdl

3- Once uploaded, click on it and under Data Sources check on Custom data source and check Windows integrated security > test connection. Now, it should work.

Note: If you are using Chrome or Safari, the page is not going to display anything; so, you have to locate: C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\js\ReportingServices.js ...And, add to the file the following script:

function pageLoad() {
var element = document.getElementById("ctl31_ctl09");
if (element) {
    element.style.overflow = "visible";    }
}

var jQueryScriptOutputted = false;
function initJQuery() {

//if the jQuery object isn't available
if (typeof (jQuery) == 'undefined') {


    if (!jQueryScriptOutputted) {
        //only output the script once..
        jQueryScriptOutputted = true;

        //output the script 
        document.write("<scr" + "ipt type=\"text/javascript\" src=\"../js/jquery- 1.6.2.min.js\"></scr" + "ipt>");
    }
    setTimeout("initJQuery()", 50);
} else {

    $(function () {

        // Bug-fix on Chrome and Safari etc (webkit)
        if ($.browser.webkit) {

            // Start timer to make sure overflow is set to visible
            setInterval(function () {
                var div = $('table[id*=_fixedTable] > tbody > tr:last > td:last > div')

                div.css('overflow', 'visible');
            }, 1000);
        }

    });
}
}

initJQuery();

I hope it helps someone!