In The Cloud

Introducing sys.database_connection_stats for Troubleshooting Windows Azure SQL Database

2012-11-02T18:40:22Z

Windows Azure SQL Database (formerly Sql Azure) now has a new system view called sys.database_connection_stats to help database Administrators track the status of incoming connections. The sys.database_connection_stats gives insight into how many incoming TDS connections are successful, terminated, throttled, and failed aggregated to a five minute window.

Before this feature improvement, application developers needed to catch and record connection failures and successes on the client-side, reporting back to their database administrator how well SQL Database was coping with the application’s requests. Now with sys.database_connection_stats, this functionality has been moved to the server, allowing administrators to query SQL Databases for aggregated statistics about their database.

Permissions

The view is accessed by connecting to the master database and only Administrators with permission to access the master database have read-only access to this view. Because this data is stored in the master database, the room to store this data is not counted as part of the user’s bill.

Latency

There is some lag from when the connection is made to when an increment for it will show up in the sys.database_connection_stats view. Typically the view will be updated within the hour, however it might take up to 24 hours for the view to update. During that time, the information within a single row may be updated after the row is first written. SQL Database is a multi-tenant system with many nodes that participate in handling the connection, each one of those nodes can add data to the row.

Querying For Database Connection Statistics

Connect to the master database for your server and execute this query to view the database connection statistics:

SELECT *

FROM sys.database_connection_stats

I am using Sql Server Management Studio 2012 and my results look like this:

Notice that the time slices are broken down into 5 minute increments for my database. Also notice that this is a low traffic database so there are some time gaps where there were no connections. If I wanted to graph this data, it would be easiest if the time gaps were filled in with zero counts before graphing. I can do this with T-SQL that looks as follows:

DECLARE @Start datetime = '2012-10-25 00:00';

DECLARE @End datetime = '2012-10-26 00:00';

DECLARE @databaseName nvarchar(100) = 'Kulshan';

WITH Times (start_time) AS

( SELECT @Start

UNION ALL

SELECT DATEADD(minute,5, start_time)

FROM Times

WHERE start_time < @End

)

SELECT Times.start_time, ISNULL(DCS.success_count,0) + ISNULL(total_failure_count,0) 'total_connection_count',

ISNULL(database_name,@databaseName) 'database_name'

FROM Times

LEFT OUTER JOIN sys.database_connection_stats AS DCS ON DCS.start_time = Times.start_time

WHERE ((DCS.start_time >= @Start AND DCS.start_time < @End) OR DCS.start_time IS NULL) AND

(DCS.database_name = @databaseName OR DCS.database_name IS NULL)

ORDER BY Times.start_time

OPTION (MAXRECURSION 0);

The results look like this:

Now I can graph it in Excel and we have a graph of connections for the day:

Conclusion and More Information

The sys.database_connection_stats system view is an important new management and troubleshooting feature for Windows Azure SQL Database. Using this new view, administrators can monitor successful connections, terminated connections and throttled connections, and correlate this to specific time windows during the ongoing operation of applications using the database. You can read more about sys.database_connection_stats on its MSDN page.

{6230289B-5BEE-409e-932A-2F01FA407A92}

Understanding Windows Azure SQL Database Size

2012-10-23T15:50:00Z

The database on Windows Azure Sql Database is stored in two parts: Database data and metadata. Database data is the data in the tables and indexes and is stored in page files. Metadata is the stored procedures, functions, cached query plans, etc… that are related to your database. Metadata is stored in the pages of the system tables of the physical server.

This blog post will discuss how the two types of data that comprise your Windows Azure Sql Database are different and how they are reflected in the bill you are charged monthly for usage.

Page Data

The table and index data are stored in page files on three physical servers. One copy of the data is the primary and the other two copies are the secondary backups. The secondary copies are exact replicas of the primary data, but the physical storage sizes may differ. If the primary fails over or the database needs to be load balanced to another server, Windows Azure Sql Database has the option of promoting one of the secondary copies to a primary. Therefore, switching the primary replica can cause the size of the database to grow or shrink as reported in the bill.

Even though there are at least three copies of the data, the bill is based on the reserve size of the primary. The following query retrieves pages utilized on the primary replica at the time of execution:

SELECT SUM(reserved_page_count) 'pages'

FROM sys.dm_db_partition_stats

To calculate the number of bytes that are reserved for a single copy of your database all you need to know is that there are 8192 bytes per page. You can run this query when connected to your Windows Azure Sql Database to determine the number of reserved bytes:

SELECT SUM(reserved_page_count) * 8192 'bytes'

FROM sys.dm_db_partition_stats

Total database size (the reserved size) is larger than the size of the data in the database. It includes overhead storage structures required to use the database as well as potentially free space on each page that is currently unused. For more information, please refer to Understanding Pages and Extents.

The bill is generated from the size of a single replica’s reserved pages, not the size of the data in the replica. Windows Azure Sql Database samples the size of your database periodically over the billing period and your monthly bill is calculated from those samples. How the sampling of your reserved pages is done and how the bill is calculated will be addressed in another blog pox.

Metadata

Metadata is the data that is kept in the system database on the host server. This includes stored procedures, functions, etc... Meta data is specific to the Windows Azure SQL Database; however it is kept in the system database on the host physical machine. Every user database on the physical host stores it’s metadata in the same system database.

This data is also replicated to the system database on the secondary physical machines in case a replica needs to be quickly promoted to a primary. In this case, the Meta data is a row level replication and not a page level replication. And just like the user databases, the system database uses pages, and has overhead storage structures required to use the database as well as potentially free space on each page that is currently unused

At this time there is no way to query for the size of the metadata in the system database.

In addition to the page data, the bill includes a portion of the metadata. From the system database that is associated with the primary replica on the physical server, we calculate the reserve page size of the system database and divide that amongst the number of rows that the user databases consumes in the system database on that physical server. In other words the metadata proportion is a row level percentage of the user consumption over all the metadata on the physical server. This is the portion of the metadata that is added to the bill.

Because the total system database’s reserve page size, and the number of total rows on the host system can change, and the primary can failover to another host’s system database, the size of the database’s portion of the metadata can grow or shrink slightly as reported in the bill.

Database Maximum Capacity

When you set a cap for the database size, both the primaries reserve page size and the database’s portion of the metadata is figured into enforcing that cap. You can read more about setting the maximum size of your Windows Azure Sql Database by reading: CREATE DATABASE (Windows Azure SQL Database)

Summary

The bill is based on the size of the reserved pages on your primary partition combined with a portion of the metadata for the physical host in which the database resides.
Billing samples the size of your database over the billing period and uses those samples to calculate your bill.
The bill is in part generated from the size of a primary replica’s reserved pages, not the size of the data in the replica. You are not billed for the data in the secondary replicas.
The bill is in part generated from the database’s share of the system database on the primary replica’s host server.

Prevent Data Loss from Force Majeure

2012-09-17T22:49:02Z

The current Windows Azure SQL Database (formerly “SQL Azure”) service level agreement specifically has an exception for factors outside Microsoft’s reasonable control, i.e. Force Majeure. Force Majeure is an extraordinary event or circumstance beyond the control of the parties, such as a war, strike, riot, crime, or a natural event (e.g., flooding, earthquake, volcanic eruption). The consequence is that the data center is damaged in such a way that databases can’t be recovered from the replicas or the on-site backups. Currently, Windows Azure SQL Database doesn’t keep any off site backups; this is the customer’s responsibility. The SQL Database team has published an excellent guide for Maintaining Business Continuity on SQL Database, and I encourage customers to read this guide.

This blog post will show you how to easily create an offsite backup of your Windows Azure SQL database that is hosted in a Windows Azure data center in another region. This type of back up is easily done using Windows Azure Portal to export a Data-Tier Application logical backup file (BACPAC) to a Windows Azure Storage location in another data center.

Creating a Copy of a Your Windows Azure Database

A BACPAC is a logical backup and does not guarantee transactional consistency. That means if users are writing to your database while the export is taking place, the resulting BACPAC will contain inconsistent data--for example broken foreign key references. Therefore, you need to first create a copy of your database then run the export from the copy.

Here is how to create a copy of your database:

Connect to your Windows Azure SQL Database server using SQL Server Management Studio.
Select the database you want to export in the Object Explorer, right click and choose from the drop down New Query.
In the query window type:

CREATE DATABASE <destination_database_name> AS COPY OF <source_database_name>

When you execute this command, it will return immediately. However Windows Azure Data will still be executing the copy in the background. You can monitor the copy process by using the sys.dm_database_copies and sys.databases views. Issue the following T-SQL:
SELECT * FROM sys.dm_database_copies;

SELECT state_desc, * FROM sys.databases;
Once the copy completes, you can use the destination database as the source for the creation of the BACPAC. While the BACPAC is generating don’t execute any INSERTS, UPDATES, or DELETES against it.

For more information see Copying Databases in Windows Azure SQL Database

Finding the Data Center

It might have been sometime since the creation of Windows Azure SQL database or someone else created the database and you might not know where it is located. In order to keep your backup in a separate data center from your database, you need to know where the database is located.

Login To Windows Azure Portal
Now click on the Database button on the lower right.

In the database main view of the Windows Azure Portal, navigate to the subscription that hosts your database.
In the properties view on the right hand side it should show the Region

Now that you know where your database is located you can make sure to back up to a different region (i.e. a different data center).

Create a BACPAC backup file Using the Windows Azure Portal

The Windows Azure Portal allows you to create a BACPAC. It will not output the package to your local hard drive, instead it writes to a Windows Azure Blob Storage.

These are the steps for creating a BACPAC from the Windows Azure Portal:

Login To Windows Azure Portal
Click on the Hosted Services, Storage Accounts & CDN Button on the lower right.
Once loaded, click on Storage Accounts in the left top navigation.
In the main view, choose a subscription and either use an existing Windows Azure storage account or create a new Windows Azure storage account. Make sure that the Windows Azure storage account is in a different region then your Azure database. Copy the access key where you can get at them later (use notepad) and also the Blob Url to your storage account.
You must also create a container inside the selected Blob storage for your BACPAC files by using a tool such as Azure Storage Explorer. Install this (or another Blob storage tool) and create a container, for this example we are using a container named: dac.
Now click on the Database button on the lower right.
In the database main view of the Windows Azure Portal, navigate to the subscription that hosts your database, then to the server and finally click on the copied version of the database from the steps above.
In the top toolbar click the Export button.
Fill out the Export Database to Storage Account dialog.
1. The Login for the Database Export Settings should be your admin login for your database.
2. The Password for the Database Export Settings should be your admin password for your Azure SQL Database.
3. The New Blob URL for Table Storage Account should be the Blob Url that you noted down in step 4. Prefix with http://, concatenated with a container name and a file name for the location of the BACPAC. There must be a container, you can’t put the file in the root. There must be a file name, something like this:
  https://myblobstorage.blob.core.windows.net/dac/exportedfile.bacpac
4. Double check your blob url and make sure you do not have any spaces, uppercase letters, or special characters in the blob url. Take a minute to get it right and it will save you a lot of frustration.
5. The Access key for Table Storage Account should be the access key that you saved in step 4.
The process of creating the BACPAC is submitted asynchronously. You will get an immediate message that your job has been submitted to the queue. Then it can take a few minutes to several hours for the export to complete.
To check the status, click on the server name in the right hand window and then the status button in the top toolbar. Now fill out the dialog with your Windows Azure SQL Database admin login and password.
If you have done everything correctly, you should see a success message when the BACPAC is finished. You can follow the status of the BACPAC being generated; note that the last update column in the status dialog is UTC.

Restoring the BACPAC

I am going to save that for a later blog post.

Summary

With the Window Azure Portal, you can easily create a Data-Tier Application logical backup package (BACPAC) and store it in an offsite Windows Azure Blog Storage account. This will help mitigate data loss from Force Majeure.

{6230289B-5BEE-409e-932A-2F01FA407A92}

Migrating your Windows Azure SQL Database Using Data-Tier Application Framework (DacFX) Technology

2012-09-12T18:04:27Z

This blog post will cover how to migrate your Windows Azure SQL Database (formerly “SQL Azure”) from Windows Azure onto a local SQL Server (box product) using Data-Tier Application Framework (DACFx) Technology and SQL Server Management Studio 2012. Since DACFx is shipped with SQL Server Management Studio 2012 you can download both at no cost. This blog will show you how to use the Windows Azure Portal to export a Data-Tier Application logical backup file (BACPAC ) and how to use SQL Server Management Studio 2012 to import that backup file into a local SQL Server instance.

The reasons for moving from Azure to a local SQL Server instance vary greatly, the most common reason is for testing or development, where you want to test changes locally on SQL Express without affecting your live database. You can also move an Azure SQL Database to a SQL Server instance running in a Windows Azure virtual machine.

About Data-Tier Application Framework Packages

DACFx technology can be used to create two different types of packages:

DACPAC Packages with the .dacpac extension currently contain only the database schema, including tables, stored procedures, logins, functions, etc.
BACPAC Packages with the .bacpac extension contain both the database schema and data.

Picking the Right Tools

To migrate your database with data & schema, you need have a tool to import a BACPAC package. SQL Server Management Studio 2012 can import BACPAC files and it contains other features for Azure that you will want to take advantage of. SQL Server Management Studio 2008 R2 (the older version of SQL Server Management Studio) cannot import a BACPAC. If you are running SQL Server 2008 R2 you can download SQL Server Management Studio 2012 at no charge, install it side by side with SQL Server 2008 R2, and it will work with your SQL Server 2008 R2 instance. For these reasons, this blog post shows how to use SQL Server Management Studio 2012, the newest version of SQL Server Management Studio.

Creating a Copy of a Your Windows Azure Database

Here is how to create a copy of your database:

Connect to your Windows Azure SQL Database server using SQL Server Management Studio.
Select the database you want to export in the Object Explorer, right click and choose from the drop down New Query.
In the query window type:

CREATE DATABASE <destination_database_name> AS COPY OF <source_database_name>

When you execute this command it will return immediately, however Windows Azure Data will still be executing the copy in the background. You can monitor the copy process by using the sys.dm_database_copies and sys.databases views. Issue the following T-SQL:

SELECT * FROM sys.dm_database_copies;

SELECT state_desc, * FROM sys.databases
Once the copy completes you can use the destination database as the source for the creation of the BACPAC. While the BACPAC is generating don’t execute any INSERTS, UPDATES, or DELETES against it.

For more information see Copying Databases in Windows Azure SQL Database

Create a BACPAC backup file sing the Windows Azure Portal

The Windows Azure Portal allows you to create a BACPAC; however, it will not output the package to your local hard drive, instead it writes it to Windows Azure Blob Storage. From Windows Azure Blob Storage, that package can be downloaded on to your local drive and then imported to an on-premise SQL Server.

Steps for creating a BACPAC from the Windows Azure Portal

Login To Windows Azure Portal
Click on the Hosted Services, Storage Accounts & CDN Button on the lower right.
Once loaded, click on Storage Accounts in the left top navigation.
In the main view, choose a subscription and either use an existing Windows Azure storage account or create a new Windows Azure storage account. For better performance, make sure that the Windows Azure storage account is in the same region as your Azure database. Copy the access key where you can get at them later (use notepad) and also the Blob Url to your storage account.
You must also create a container inside the selected Blob storage for your BACPAC files by using a tool such as Azure Storage Explorer. So install this (or another Blob storage tool) and create a container, for this example we are using a container named: dac.
Now click on the Database button on the lower right.
In the database main view of the Windows Azure Portal, navigate to the subscription that hosts your database, then to the server and finally click on the copied version of the database from the steps above.
In the top toolbar click the Export button.
Fill out the Export Database to Storage Account dialog.
1. The Login for the Database Export Settings should be your admin login for your database.
2. The Password for the Database Export Settings should be your admin password for your Azure SQL Database.
3. The New Blob URL for Table Storage Account should be the Blob Url that you noted down in step 4. Prefix with http://, concatenated with a container name and a file name for the location of the BACPAC. There must be a container, you can’t put the file in the root. There must be a file name, something like this:
  https://myblobstorage.blob.core.windows.net/dac/exportedfile.bacpac
  Double check your blob url and make sure you do not have any spaces, uppercase letters, or special characters in the blob url. Take a minute to get it right and it will save you a lot of frustration.
4. The Access key for Table Storage Account should be the access key that you saved in step 4.
The process of creating the BACPAC is submitting asynchronously. You will get an immediate message that your job has been submitted to the queue. Then it can take a few minutes to several hours for the export to complete.
To check the status, click on the server name in the right hand window and then the status button in the top toolbar. Now fill out the dialog with your Windows Azure SQL Database admin login and password.
If you have done everything correctly, you should see a success message when the BACPAC is finished. You can follow the status of the BACPAC being generated; note that the last update column in the status dialog is UTC.

Importing a BACPAC into SQL Server

Now that you have your BACPAC generated via the Windows Azure Portal, you can deploy (or import) it into your on-premise SQL Server using SQL Server Management Studio 2012

Here are the steps:

Download and install SQL Server Management Studio 2012, you can side-by-side install this with SQL Server 2008 R2 if you want.
Open SQL Server Management Studio 2012 and connect to the SQL Server where you want your data to be imported.
In the object explorer right click on Databases and choose Import Data-Tier Application.
Click next to get to the second step in the wizard screen. There you have two choices: use a BACPAC from the disk, or download one directly from Windows Azure Blob storage. Since our package is on Blob Storage, let’s download from there. On the Import Settings page of the wizard, select the Import from Windows Azure radio button.
Another dialog will appear to collect your connection information. Enter in your storage account name on Windows Azure Storage where the BACPAC was placed and the Windows Azure Storage Account Key (Primary Access Key). Then click Connect.
SQL Server Management Studio 2012 will traverse and find your BACPAC, if it doesn’t find the right one, use the Container and File name drop downs to choose the Data-Tier application package you want to install. Click the Next button on the Import Setting page.
The next page of the wizard is the Database Settings. Here you can enter your database name, the location of the database data file and transaction log files. Then click the Next button.
Review the summary page and click the Finish button.
In the Results page, the BACPAC will download from Windows Azure, a database will be created, and your schema, data, stored procedures, etc will be imported into the new database.

More Fun…

Your BACPAC from Windows Azure blob storage was downloaded into the Temporary location specified in the Import Settings page. You can save that package off for a backup of your database. Another fun thing to do is rename the extension from .bacpac to .zip, then open it up and look at the Xml files inside. But note that editing the XML files directly is not supported and your package may be rejected by the tools if you make any changes to the XML.

Summary

With the Window Azure Portal, you can easily create a Data-Tier Application logical backup package (BACPAC), store it to your Windows Azure Blog Storage; and then, using SQL Server Management Studio 2012, you can import that package to your local database server.

{6230289B-5BEE-409e-932A-2F01FA407A92}