Category Archives: Reporting Services

Read-Only Routing with SQL Server 2012 Always On Database Availability Groups

Posted by dpless on September 12, 2012 None comments
By Kevin Wingfield - kevinwin@microsoft.com

Contributors – David Pless - dpless@microsoft.com

Read-Only Routing with SQL Server 2012 Always On Database Availability Groups

Overview

In this blog I will go through step by step how to configure Read-Only routing for scale-out report workloads using SQL Server 2012 Always On Database Availability Groups.

In SQL Server 2012, AlwaysOn Availability Groups provides group level high availability for any number of databases on up to four secondaries known as ‘replicas’. The secondary replicas allow direct read-only querying or can enforce connections that specify ‘ReadOnly’ as their Application Intent. The secondaries primary use is to provide high availability or disaster recovery for the groups of databases being replicated. The secondaries can also be used to offset backup operations, DBCC checks, and to offload reporting workloads. The secondary copies of these databases known as secondary replicas have the ability to be read when they are in the secondary role. This is unlike Database Mirroring which requires a Database Snapshot in order to read a static view of the database at the Database Snapshot was created.

AlwaysOn brings in the strengths of Clustering, Log Shipping and Database Mirroring together where the method to transmit data is very similar to Database Mirroring with much more functionality and flexibility. AlwaysOn requires Windows Server 2008 clustering features, but does not require that SQL Server itself be clustered.

In AlwaysOn, Read-Only routing refers to the ability of SQL Server to route qualifying read-only connection requests to an available AlwaysOn readable secondary replica (http://msdn.microsoft.com/en-us/library/ff878253.aspx) that is, a replica that is configured to allow read-only workloads when running under the secondary role. To support read-only routing, the availability group must possess an availability group listener (http://msdn.microsoft.com/en-us/library/hh213417.aspx). Read-only clients must direct their connection requests to this listener, and the client’s connection strings must specify the application intent as “read-only.” That is, they must be read-intent connection requests.

This can be seen in the connection string, an example is shown below:

Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID=”";Initial Catalog=”";Data Source=AGListner;Initial File Name=”";Server SPN=”";ApplicationIntent=READONLY

Read-Only routing is a great new feature that can be leveraged to scale out reporting workloads such as SQL Server Reporting Services reports.  All of your reports hosted in SharePoint or on a Native Mode installation of Report Server can specify read-only intent and can be serviced by your secondary replicas.  This takes the heavy read workload that typically causes SQL Server blocking and consumes memory and CPU from your primary read/write database and frees up those resources.

Note: This document assumes that you are familiar with SQL Server 2012 Always On Availability Groups and how to setup AlwaysOn Database Availability Groups.

Overview of SQL Server Always On Infrastructure

To facilitate Read-Only routing I setup three SQL Server 2012 instances and then setup a database availability group using two Adventure Works databases.

Always On Database Availability Group Diagram:

AlwaysOnDB_AG_Diagram

 Diagram of Server Manager:

My Windows 2008 R2 server names are listed below (3 node cluster):

  • SP-DENALI1
  • SP-DENALI3
  • SP-DENALI4

I have loaded a SQL 2012 named instance on each server with the following names:

  • SP-DENALI1\SQL1 (PRIMARY REPLICA)
  • SP-DENALI3\SQL1 (SECONDARY REPLICA)
  • SP-DENALI4\SQL1 (SECONDARY REPLICA)

Note: With AlwaysOn Availability Groups it is a good practice to make all of the named instances the same name for data synchronization.

Availability Group Listener (This is required to be setup for read-only routing):

  • SQL1

Note: The Availability Group Listener is much like a virtual network name in traditional SQL clustering as it is a pointer to the SQL Instance that currently hosts the database availability group.

View from SQL Server Management Studio:

SQL Server Management Studio

 Prerequisites

In this post we are using the new SQL Server Data Tools which replaces Business Intelligence Development Studio. SQL Server 2012 RTM has the correct client driver for .NET 4.0.2 that will expose the connection string property ApplicationIntent.

The syntax for a connection string depends on the client provider an application is using. The following example connection string for the .NET Framework Data Provider 4.0.2 for SQL Server illustrates the parts of a connection string that are required and recommended to work for read-only routing:

Server=tcp:SQL1,1433;Database=Db1;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True

Read-Only Routing Lists

Read-Only routing requires that the availability replica be enabled for read access.  This can be done at the time the Always On Availability Group is setup.  You can also change and view this property by looking at the properties of the Availability Group:

Read Only Routing List

Availability Group property dialogue:

AG Dialogue

Note: In the above screen shot you can see that the two replicas are marked to allow read connections.

To setup the Read-Only routing list you must be using an Availability Group Listener. In essence, you are essentially creating a SQL Server routing table for AlwaysOn that redirects connections to the secondary when the Application Intent is set to Read-Only. Below is the script that I used to setup the list for each replica based on whether it is in the Primary Role or the Secondary Role:

Routing Table Script

Note: For a deeper explanation and a VERY handy script that will generate the ROR URL for you please see Matt Neerincx’s blog post http://blogs.msdn.com/b/mattn/archive/2012/04/25/calculating-read-only-routing-url-for-alwayson.aspx

Running Reports using the Read-Only Routing List and ApplicationIntent=ReadOnly

I have created a report in SQL Server Data Tools which is the new Business Intelligence Development Studio. Within SQL Server Data Tools I created a simple Report Project and a simple report to show how a reporting type of workload can be routed by SQL Server to a secondary read-only replica.

The above screenshot shows that I have two data sets. DataSet1 represents the result set that I am displaying in the report. DataSet2 is a simple “SELECT @@SERVERNAME” which returns the current instance name of the SQL Server for which the data source connection is currently connected to.  I am using this to populate a text box with the SQL Server instance name so that I can show you which instance in my AlwaysOn environment executes the query for my report.

Now let’s take a look at how the SSRS report data source is configured for Read-Only Routing:

Routing1

The above screen shot shows the properties of the shared data source in my report.  Notice the use of the Availability Group Listener name SQL1 as the Data Source property and the ApplicationIntent=ReadOnly option.  These are both required for SQL Server to use the Read-Only routing list I configured earlier.

Another view of the data source properties in SQL Server Data Tools:

Routing2

Now that the data source is configured properly let’s run the report.  Before I run the report I want to verify which instance is in the primary role.

Object Explorer

Running the report:

Running Report

As you can see in the above screenshot SQL Server received the connection and routed the report query to the secondary replica instance named SP-DENALI4\SQL1.  This instance is the instance that we specified first in the Read-Only Routing List when the SP-DENALI1\SQL1 instance is in the Primary Role.

Summary

With the Read-Only Routing capabilities SQL Server is now more scalable than ever.  Customers can utilize secondary replicas for scaling reporting type workloads, increasing the value of their SQL Server infrastructure while increasing performance by offloading heavy read workloads that commonly cause contention.  Applications like SQL Server Reporting Services in SharePoint mode or Native mode can now easily take advantage of using secondary replicas for reporting.

Read-Only routing is yet another great feature of SQL Server 2012!

 

Launching Report Builder 1.0 in Reporting Services – SQL Server 2008 R2

Posted by dpless on December 11, 2010 None comments

By David Pless – dpless@microsoft.com

In this article we are covering how to change the path of the Report Builder link in Report Manager, how to launch Report Builder via a url, and why it might be attractive to customize how Report Builder is launched.

Report Builder 3.0
Report Builder 3.0 is used to create reports in a familiar, Microsoft Office-like authoring environment. You can customize and update all existing reports, regardless of whether they were created in Report Designer or in the previous versions of Report Builder.

Report Builder 3.0 can be downloaded from Microsoft.com/downloads. The latest version can be downloaded and installed from here:

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=d3173a87-7c0d-40cc-a408-3d1a43ae4e33&displaylang=en

Report Builder 3.0 is a great addition to the Reporting Services suite. It is a very powerful application where you can do nearly everything you can do in Business Intelligence Development Studio (BIDS). Report Builder 3.0 now has the ability to leverage Report Parts which can speed report development and make it easier to update parts of reports, such as company logos, that are shared between several reports. Additionally, the process of deploying reports is streamlined as you simply “save” the reports to the Report Server directly from the application. However, this may not be an application you as the DBA want to be installed and launched from Report Manager. In your environment you might choose to have the previous version of Report Builder 1.0 launched from the ‘Report Builder’ link.

Report Builder 1.0
Is a report designer application that was launched from Report Manager. It was released with SQL Server 2005 and allowed report developers to build reports off of report models. Report Builder 1.0 does not have all the bells and whistles that Report Builder 3.0 has, though, Report Builder 1.0’s limitations could be considered a strength in some environments. Report Builder 1.0 would only allow the report developer to build reports against report models. Report models are very tightly controlled similar to using a SQL Server view or stored procedure and do not allow the report developer to freely access and directly query tables or directly write T-SQL statements against the data source.

Additionally, Report Builder 1.0 is a simpler application that has far fewer of the capabilities and complexities of the latest version.

This could be seen as a strength.

Note: Report models require a data source and a data source view. You can create a Report model using the Report Model Wizard in Business Intelligence Development Studio (BIDS). More can be read about Report Models from here: http://msdn.microsoft.com/en-us/library/ms159754(SQL.100).aspx

To launch Report Builder from Report Manager you would simply click the Report Builder ClickOnce link at the menu bar in Report Manager as seen below, by default this will launch Report Builder 3.0.

image

The ClickOnce application has its path in the SQL Server program files ../Reporting Services/ReportServer/ReportBuilder directory. As seen below, the ClickOnce Application Deployment Manifest files are located in the ReportBuilder directory for both Report Builder 1.0 and Report Builder 3.0.

image

Launching Report Builder from a URL

By looking at the directory above you can launch Report Builder from a url by using the following paths:

Launch Report Builder 3.0
http://<servername>/ReportServer/ReportBuilder/ReportBuilder_3_0_0_0.application

Launch Report Builder 1.0
/ReportServer/ReportBuilder/ReportBuilder.application”>http://<servername>/ReportServer/ReportBuilder/ReportBuilder.application

By following the Report Builder ClickOnce Manifest file with .application in Internet Explorer, you can launch this particular version of Report Builder. Double clicking the ReportBuilder_3_0_0_0 file should attempt to install the Report Builder application locally.

Customizing Report Manager to launch Report Builder 1.0

To change the Report Manager path to Report Builder. Go into the site settings at the top right corner of Report Manager and adjust the path in the ‘Custom Report Builder launch URL:’ as shown in the image below.

image

References

Starting Report Builder 1.0
http://msdn.microsoft.com/en-us/library/ms345245.aspx

Configuring Report Server URLs
http://msdn.microsoft.com/en-us/library/ms159261.aspx

Leveraging sys.dm_io_virtual_file_stats

 
Wednesday, December 01, 2010, 6:49:00 PM | jjAnonymousjjjGo to full article

 

By David Pless – dpless@microsoft.com

One of the DMVs I try to utilize on any engagement where customers are complaining about disk issues is the sys.dm_io_virtual_file_stats DMV where you can look at the IO stalls for both reads and writes. The sys.dm_io_virtual_file_stats DMV will show an IO Stall when any wait occurs to access a physical data file. IO Stalls are recorded at the file level and you can also obtain the IO Stalls at the database level directly out of the DMV.

By getting this information it is very easy to ORDER BY io_stall_read_ms, io_stall_write_ms, or by io_stall which is an accumulation of reads and writes.

One addition step I have made in the script below is mapping to the sys.master_files catalog view and using the substring function to get the physical disk drive letter. You will now be able to see IO Stall activity at the file, database, and the drive letter. You can then use Reporting Services or simply use Excel to get a quick view of which of these is absorbing most of the IO Stall impact.http://www.officelabs.com/projects/chartadvisor/Pages/default.aspx

If you use Excel 2007, one of the interesting strategies is to use the Chart Advisor from Live Labs.

This analysis can help make decisions around table partitioning and potentially file and index placement. Of course, this will all depend on the customer’s SAN and other constraints.

Note: Mount points will make getting the drive letter less effective. If you are using mount points then just ignore the drive letter column.

SELECT a.io_stall, a.io_stall_read_ms, a.io_stall_write_ms, a.num_of_reads,
a.num_of_writes,
–a.sample_ms, a.num_of_bytes_read, a.num_of_bytes_written, a.io_stall_write_ms,
( ( a.size_on_disk_bytes / 1024 ) / 1024.0 ) AS size_on_disk_mb,
db_name(a.database_id) AS dbname,
b.name, a.file_id,
db_file_type = CASE
                   WHEN a.file_id = 2 THEN ‘Log’
                   ELSE ‘Data’
                   END,
UPPER(SUBSTRING(b.physical_name, 1, 2)) AS disk_location
FROM sys.dm_io_virtual_file_stats (NULL, NULL) a
JOIN sys.master_files b ON a.file_id = b.file_id
AND a.database_id = b.database_id
ORDER BY a.io_stall DESC

For those looking at disk issues, I have pasted the general guidance on the avg. reads/sec and avg. writes/sec values for perfmon. By using the script above and the guidance here on perfmon, you should be able to take the next steps in addressing disk performance issues with your customers.

I/O Bottlenecks

SQL Server performance depends heavily on the I/O subsystem. Unless your database fits into physical memory, SQL Server constantly brings database pages in and out of the buffer pool. This generates substantial I/O traffic. Similarly, the log records need to be flushed to the disk before a transaction can be declared committed. And finally, SQL Server uses TempDB for various purposes such as to store intermediate results, to sort, to keep row versions and so on. So a good I/O subsystem is critical to the performance of SQL Server.

Access to log files is sequential except when a transaction needs to be rolled back while access to data files, including TempDB, is randomly accessed. So as a general rule, you should have log files on a separate physical disk than data files for better performance. The focus of this paper is not how to configure your I/O devices but to describe ways to identify if you have I/O bottleneck. Once an I/O bottleneck is identified, you may need to reconfigure your I/O subsystem.
If you have a slow I/O subsystem, your users may experience performance problems such as slow response times, and tasks that abort due to timeouts.
You can use the following performance counters to identify I/O bottlenecks. Note, these AVG values tend to be skewed (to the low side) if you have an infrequent collection interval. For example, it is hard to tell the nature of an I/O spike with 60-second snapshots. Also, you should not rely on one counter to determine a bottleneck; look for multiple counters to cross check the validity of your findings.

  • Physical Disk Object: Avg. Disk Queue Length represents the average number of physical read and write requests that were queued on the selected physical disk during the sampling period. If your I/O system is overloaded, more read/write operations will be waiting. If your disk queue length frequently exceeds a value of 2 during peak usage of SQL Server, then you might have an I/O bottleneck.
  • Avg. Disk Sec/Read is the average time, in seconds, of a read of data from the disk. Any number:
    • Less than 10 ms – very good
    • Between 10 – 20 ms – okay
    • Between 20 – 50 ms – slow, needs attention
    • Greater than 50 ms – Serious I/O bottleneck
  • Avg. Disk Sec/Write is the average time, in seconds, of a write of data to the disk. Please refer to the guideline in the previous bullet.
  • Physical Disk: %Disk Time is the percentage of elapsed time that the selected disk drive was busy servicing read or write requests. A general guideline is that if this value is greater than 50 percent, it represents an I/O bottleneck.
  • Avg. Disk Reads/Sec is the rate of read operations on the disk. You need to make sure that this number is less than 85 percent of the disk capacity. The disk access time increases exponentially beyond 85 percent capacity.
  • Avg. Disk Writes/Sec is the rate of write operations on the disk. Make sure that this number is less than 85 percent of the disk capacity. The disk access time increases exponentially beyond 85 percent capacity.

When using above counters, you may need to adjust the values for RAID configurations using the following formulas.

  • Raid 0 — I/Os per disk = (reads + writes) / number of disks
  • Raid 1 — I/Os per disk = [reads + (2 * writes)] / 2
  • Raid 5 — I/Os per disk = [reads + (4 * writes)] / number of disks
  • Raid 10 — I/Os per disk = [reads + (2 * writes)] / number of disks

For example, you have a RAID-1 system with two physical disks with the following values of the counters.

  • Disk Reads/sec -  80
  • Disk Writes/sec – 70
  • Avg. Disk Queue Length – 5

In that case, you are encountering (80 + (2 * 70))/2 = 110 I/Os per disk and your disk queue length = 5/2 = 2.5 which indicates a border line I/O bottleneck.”

Reference(s):

Troubleshooting Performance Problems in SQL Server 2008
http://msdn.microsoft.com/en-us/library/dd672789.aspx

(BOL) – sys.dm_io_virtual_file_stats (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms190326.aspx

David Pless, Senior Premier Field Engineer
http://blogs.msdn.com/dpless/  

David Pless, Senior Premier Field Engineer
http://blogs.msdn.com/dpless/