SSRS export to PDF in landscape format.

In reporting server you have the option to export your reports to PDF format. By default your reports are generated in the portrait format. When building your report the body will automaitically resized when you add report items which did not fit. After deploying your report, it renders correct in your browser. However after exporting the report you see that the report is exported in the portrait format. This is not nice and generated extra pages. This can be avoided to export the PDF in landscape format. To export your reports to PDF format in landscape format, you need to do the following:

Retrieve the properties of your report. (Right mouse click, just outside the body of the report).

Set the width of the report to the landscape size of your A4 paper: 29.7 cm
Set the height of the report to 21 cm.

To avoid extra blank pages during export,  the size of the body should be less or equal to the size of the report - margins.
Set the width of the body to 26.7 cm (29.7 -1.5 - 1.5)
Set the height of the body to 18 cm (21 - 1.5 -1.5)

Your report is now defined in landscape which will result in generated PDF exports in the landscape format.

Enjoy building your reports.

The release date of SQL 2008 R2 is set for May 2010

The release date of SQL 2008 R2 is set for May 2010. So few month waiting for the final version. More information about the release date can be found here.
Here are some blogpost I have written about the current CTP Version  of SQL 2008 R2:

SQL Server 2008 R2 with some nice visualization features.
How to enrich your account data with latitude, longitude and geography data?
How to drilldown on a reporting server map report in SQL 2008 R2?

If you are interested in using SQL 2008 R2, you can here download the CTP version.

My SQL Azure Bill: Usage statistics like bandwidth and databases

Image credit: KevinSpencer


By using SQL Azure you do not have to be concerned about your backups, maintaince of your SQL Server, creating a fallback scenario etc. You only have to pay for what you are using. This is nice, but what are you using? You do not want to be suprised when the first bill is received. By using SQL Azure you need to pay for:

1. Database usage. You will be invoiced for the number, type and duration of the database. Type of a database can be Business edition (Up to 10 Gb) or Web edition (Up to 1 Gb). Duration is the number of days the database was available on your SQL Azure server. 
2. Bandwidth usage. You will be invoiced for inbound and outbound data traffic.

Within your database 2 views are available to get insight information about the Database usage and Bandwidth usage. I can imagine that these views are not so user friendly for your controller, I think he has even no access to these views. But you have at least something.

Database usage view: SELECT * FROM SYS.Database_usage. This view contains information about the number of database of eachtype you have everyday. One database for 30 days will be invoiced for the same amount as 30 databases one day.

Bandwidth usage view: SELECT * FROM SYS.Bandwidth_usage. This view contains the amount of bandwidth usage (in kilobytes KB) per database, per hour, data moving into SQL Azure, data moving out of SQL Azure, data from within Windows Azure and data from outside Windows Azure.

With these usage statistics you can calculate your bill for the coming month.
More detail about billing information can be found here. More details about the billing rates can be found here.
Enjoy using SQL Azure and hopefully you will not be badly suprised about the billing amount.

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.

Main differences between SQL Azure and SQL Server 2008

SQL Azure is a service which makes the administration slightly different.  Unlike administration for an on-premise instance of SQL Server, SQL Azure abstracts the logical administration from the physical administration; As an database administrator you continue to administer databases, logins, users, and roles, but Microsoft administers the physical hardware such as hard drives, servers, and storage. Because Microsoft handles all of the physical administration, there are some differences between SQL Azure and an on-premise instance of SQL Server in terms of administration, provisioning, Transact-SQL support, programming model, and features.
 
Logical Administration vs. Physical Administration
To provide this level of physical administration, you cannot control the physical resources of SQL Azure. For example, you cannot specify the physical hard drive or file group where a database or index will reside. Because the computer file system is not accessible and all data is automatically replicated, SQL Server backup and restore commands are not applicable to SQL Azure. Many SQL Server Transact-SQL statements have parameters that allow you to specify file groups or physical file paths. These types of parameters are not supported in SQL Azure because they have dependencies on the physical configuration.

Because SQL Azure performs the physical administration, any statements and options that attempt to directly manipulate physical resources will be blocked, such as Resource Governor, file group references, and some physical server DDL statements. It is also not possible to set server options and SQL trace flags or use the SQL Server Profiler or the Database Tuning Advisor utilities.

SQL Azure does not support all of the features and data types found in SQL Server. Analysis Services, Replication, Reporting Services, and Service Broker are not currently provided as services on the SQL Azure.

Microsoft SQL Azure Database supports a subset of Transact-SQL for SQL Server 2008.

Transact-SQL Features Supported on SQL Azure
• Constants
• Constraints
• Cursors
• Index management and rebuilding indexes
• Local temporary tables
• Reserved keywords
• Stored procedures
• Statistics management
• Transactions
• Triggers
• Tables, joins, and table variables
• Transact-SQL language elements such as
o Create/drop databases
o Create/alter/drop tables
o Create/alter/drop users and logins
o and so on.
• User-defined functions
• Views, including sys.synonyms view

Transact-SQL Features Unsupported on SQL Azure
• Common Language Runtime (CLR)
• Database file placement
• Database mirroring
• Distributed queries
• Distributed transactions
• Filegroup management
• Global temporary tables
• Spatial data and indexes
• SQL Server configuration options
• SQL Server Service Broker
• System tables
• Trace Flags