SSRS reports on the IPad or IPhone has been improved since SQL 2012 SP1

Almost 2 years ago I wrote a blogpost about SQL Server Reporting Service (SSRS) Reports on the IPad or IPhone. I was the first time you could display SSRS reports on the IPad. Unfortunatly not everything was rendered in a correct way. In SQL Server 2012 SP1 this has been improved.

Here is an example of a report in SQL 2008 R2



 Here is an example of the same report in SQL 2012 SP1

As you can see, the strange Blue Question marks are gone.

Starting with SQL 2012 Service Pack 1 (SP1), Reporting Services supports viewing and basic interactivity with reports on Apple iOS devices like IPad en Iphone, with the Apple Safari browser.
Viewing reports in Report Manager (http://myserver/reportserver) is not suppported. You need to start the reports from the report server via http://myserver.reports. Here you can browse to the report and tape the report name to open the report. After opening the report yiu can see that the Export to PDF and TIFF file is supported. More information about the support for Apple iOS devices on SSRS can be found here.
For a video of using SSRS on your Ipad please see:

Microsoft® SQL Server® 2008 R2 Service Pack 2 available for download

Service Pack 2 for SQL Server 2008 R2 is available for download, it includes product improvements based on requests from the SQL Server community and hotfix solutions provided in SQL Server 2008 R2 SP1 Cumulative Updates 1 to 5. A few highlights are as follows:

• Reporting Services Charts Maybe Zoomed & Cropped Customers using Reporting Services on Windows 7 may sometime find charts are zoomed in and cropped. To work around the issue some customers set ImageConsolidation to false. 
• Collapsing Cells or Rows, If Hidden Render Incorrectly Some customers who have hidden rows in their Reporting Services reports may have noticed rendering issues when cells or rows are collapsed. When writing a hidden row, the Style attribute is opened to write a height attribute. If the attribute is empty and the width should not be zero.

You can download SQL Server 2008 R2 Service Pack 2 from here.
Succes with upgrading your SQL Server with this Service Pack.

SQL Denali CTP available for download

The first CTP of the successor of SQL Server 2008 R2, codename SQL 'Denali' is available for download. It contains a lot new stuff. Keep in mind SQL server isn't just a database, it is an entire information platform by making use of SharePoint server. The need for a SharePoint installation can be a challenge for the companies with do not have or a very small ICT department.

Here is a short list of new high level topics:

• Project codename “Crescent”, a web-based, data visualization and presentation solution, and follow-on to the PowerPivot technology that is part of SQL Server 2008 R2
• Project codename “Apollo”, new column-store database technology aiming to provide greater query performance
• SQL Server AlwaysOn, a new high-availability “solution that will deliver “increased application availability, lower TCO (total cost of ownership) and ease of use
• Project codename “Juneau”, a single development environment for developing database, business intelligence (BI) and web solutions
• SQL Server Data Quality Services (based on technology from Microsoft’s 2008 Zoomix acquisition)
• Other data integration and management tools

My personal favorites are:

• Project codename 'Crescent'. This will help the people in the board room to easily analyze and play with the data they are looking at.
• Project codename 'Apollo'. Performance is one of my specialties in SQL Server. For every new SQL version I look forward to the improvements on performance. This new column-store database technology will indeed improve the performance of your datawarehouse significantly.

Another important thing to mention: No big compatibility issues are expected as we have had in the past from SQL 2000 to SQL 2005. This will make it easy to upgrade to a the new version with you current SQL Server databases.
If you are interested to look around in the CTP version, you can download it here

Enjoy testing this new version.

SQL Server 2008 R2 Developers Training Kit (June 2010 update)

SQL Server 2008 R2 offers an impressive array of capabilities for developers that build upon key innovations introduced in SQL Server 2008. The SQL Server 2008 R2 Update for Developers Training Kit is ideal for developers who want to understand how to take advantage of the key improvements introduced in SQL Server 2008 and SQL Server 2008 R2 in their applications, as well as for developers who are new to SQL Server.

Image credit: Knurftendans

The training kit offers the following benefits:

• Learn how to build applications that exploit the unique features and capabilities of SQL Server 2008 and SQL Server 2008 R2.
• Provides a comprehensive set of videos, presentations, demos and hands-on labs
• Contains new content for developers who are new to SQL Server.
• Contains new content for SQL Server 2008 R2.
• Contains all of the existing content from the SQL Server 2008 Developer Training Kit.
• Easy to download and install.

The training kit is designed for developers, technical specialists and consultants.

Content of the training kit:
32 presentations
39 demos
24 hands-on labs
55 videos'


Click here to download the SQL Server 2008 R2 Update for Developers Training kit.

I think you will enjoy it.

Tips to improve performance of MS Reporting service reports (SSRS).

Photo credit: visuloqik

A lot of times I got questions from people how to improve SSRS reports. Of course every report is different, but some tips can be applied to every report. Assume we have a report which takes X seconds to display all data on your screen. The total time to generate a SSRS reports can be split into 2 main parts:

1. Time to executed all queries on the SQL Server. Use the SQL profiler to measure the duration of all executed queries. (Y seconds)
2. Time to generated the report on the reporting server, based on the result set of the executed queries. (Z seconds)

Because we know the total time to execute the report and the total duration of all executed queries, you can calculate the time to generate the report (Z seconds). (X = Y + Z). After you know the values for X, Y and Z, you can start focusing on the biggest part.

• Use the SQL Profiler to see which queries are executed when the report is generated. Sometimes you will see more queries being executed than you expected. Every dataset in the report will be executed. A lot of times new datasets are added during building of reports. Check if all datasets are still being used. For instance, datasets for available parameter values. Remove all datasets which are not used anymore.
• A dataset contains more columns than used in the Tablix\list. A lot of times you will see datasets with next syntax:
  
  SELECT * FROM TableX WHERE Column1 = 'Something'.
  
  This is easy when start building the report. It will retrieve all columns of the table. However all columns are not useful and creates a lot of overhead. Assume you uses 3 columns in the tablix\list. Change the syntax of the dataset to only these 3 columns. This can save bookmark lookups and it will save disk I/O on the SQL Server. So you get a dataset with next syntax:
  
  SELECT Column1, Column5, Column 8 FROM TableX WHERE Column1 = 'Something'
• ORDER BY in the dataset differs from the ORDER BY in the Tablix\list. You need to decide where the data will be sorted. It can be done within SQL Server with an ORDER BY clause in the dataset or in by the Reporting server engine. It is not useful to do it on both sites. If an index is available use the ORDER BY in your dataset.
• Use the SQL Profiler to measure the performance of all datasets (Reads, CPU and Duration). Use the SQL Server Management Studio (SSMS) to analyze the execution plan of every dataset.
• Avoid datasets with result sets with a lot of records like more than 250 records. Greater result sets are more like a data export (dump). A lot of times data is GROUPED in the report without an Drill down option. In that scenario do the group by already in your dataset. This will save a lot of data transfer to the SQL Server and it will save the reporting server engine to group the resultset.
• Rendering of the report can take a while if the resultset is very big. Look very critical if such a big resultset is necessary. If details are used in only 5 % of the situations, create another report to display the details. This will avoid the retrieval of all details in 95 % of the situations.
• Use the database documentation of your applications to understand how to retrieve information from the database. For Exact Globe click here to find the database documentation. For Exact Synergy Enterprise click here to find the database documentattion.

Enjoy building high performing reports.

My first experiences with SQL Azure, the SQL Server database in the cloud.

In one of my previous blog post: Getting Started with SQL Azure, create your first SQL database in the cloud. I described what you need to do to create a SQL Azure database. Overall I was suprised what is already possible. I'm looking forward to the next releases to see the progress in SQL Azure. This document will describe my first experiences with using SQL Azure.

1. Getting started and creating a database is easy to do. It is really straight forward to create and connect to your SQL Azure environment.
2. SQL Azure firewall services functionality is restricted. Access to my database can be blocked on IP version 4 level and user level. With IP address spoofing you can still access the SQL Azure server. In most production environments direct access to the SQL Server is blocked.
3. Firewall does not support IP version 6 adresses. In the coming years there is a shortage of IP version 4 adresses. To support the current growth of all devices which connect to the internet, IP version 6 is developed.
4. You need to create a firewall exception for TCP port 1433. Otherwise you get a connection error: Cannot connet to: Could not open a connection to SQL Server. (Microsoft SQL Server, Error: 53). This can conflict with current firewall rules. Connectivity to the SQL Azure database depends on the network from which you connect to it. In the past the IT department of my company blocked TCP port 1433 because of the SQL Slammer virus. My internet provider at home did not block this port.
5. Your application must support encryption when connecting to SQL Azure. This is good. You don't want send your data unencrypted over the internet.
6. Only support for SQL authentication. From a technical perspective I can image why Integrated Security is not supported. However, according the Books Online of SQL Server it is strongly recommended from a security perspective to use Integrated security to connect to the database. If you have the username and password of the SQL account, you can do what you want. At this moment there are not other ways of establishing more secure connections to the database, for instance making use of certificates. See next screen cast of the current SQL Azure security model.
7. Maximum database size is 1 GB for the Web edition and 10 GB for the Business edition. 10 GB of data is relative small size for a database.
8. Database connection will be closed due to the following conditions: Excessive resource usage, Long running queries, long running single transactions, idle connections. In the current release (10.25.9085) idle connections and long running queries or transactions are closed after five minutes. Automatic reconnect to the server did not work. Only the second execution is working. One example, I execute a simple query in SSMS on my SQL Azure database successfully. After that I do something else for 10 minutes, for instance a coffee break. After 10 minutes I execute the second simple query. Now I got an error: Msg 10053, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.) This is not user friendly.
9. RESTORE and ATTACH database statement is not supported. The SQL Azure platform supports high availability, however a restore is sometimes needed for other purposes than a hardware failure like user or application mistakes. What can a user do if he made a mistake in deleting some transactions? In these scenarios you need to restore a copy of your database.
10. As an alternative to start testing with an local copy of your SQL database in the cloud, you can use SQL Azure Migration Wizard. Please read this blog if you got BCP upload errors with the SQL Azure migration wizard. I used this wizard to upload a copy of on on premise SQL database to my SQL Azure Server.
11. SQL Profiler is not supported. This is a tool I really misses. I use the SQL Profiler to understand what happens on my SQL server, to see what kind of queries are generated by my applications and to analyze my queries to improve performance. On the SQL Azure server I have an additional reason to use the SQL Profiler. In the pricing model for SQL Azure you have to pay for the data transfer sizes of your queries. The SQL Profiler can give you insight in the data transfered from the server to the client.
    







12. I understand from a technical point of view why tools like SQL profiler are currently not supported. They are builded for the on premise SQL Server environments. With the SQL Azure services we have a distinction between the physical and logical administration in which access to the physical components are not allowed. However, from a user perspective these tools are really needed to have. Without these tools the SQL Azure server is a black box. You put something in and you get something out but have no idea how this is done. Especially when you have to pay for the things you get out. You want to be in control what you need to pay to avoid suprises afterwards when you receive the bill.
13. I succeed to run my .NET application with a SQL Azure database. This .NET application is build to run on an on premise SQL Server. Because SQL Azure does not support the security implementation of my .NET application I made some changes in the run time of this .NET application. This was acceptable for this test because I was interested to see how SQL Azure was running with an application which generates a lot of different queries. After changing some stored procedures, I could login to my application and use all functionality.
14. I'm interested to know how much of my data will remain in the procedure cache and the data cache. I have no idea what amount of memory is allocated to my databases. For instance, is it usefull to use parameterized queries? Without the profiler you can't figure this out. 

Overall, I think this is a good start with this first version of SQL Azure and looking forward to see if some of my missing functionalities and tools are included in the upcoming releases.
Enjoy it.