SQL Server 2012 Database Backups in Azure

SQL Server 2012 inlcudes new & updated features to SQL Server Management Studio (SSMS) that provide out-of-the-box integration into the Windows Azure platform-as-a-service Cloud service from Microsoft. It goes beyond just SQL Azure integration as you can now also connect into Windows Azure storage accounts where you can store files and blobs, including DACPAC & BACPAC files, essentially providing DBAs with out-of-the-box backup-to-cloud capabilities.

From a DBA’s perspective, this can be very beneficial because this would allow you to take your SQL Server backups and post them into the Azure cloud where files are automatically protected and replicated for high availability. This will also eliminate the need for you to maintain infrastructure for backups locally on your site. You would utilize Azure Storage for maintenance, retrieval and disaster recovery of your database backups. Here is a link with more details on Azure Storage.

Here are the steps to backup from SSMS 2012 to Windows Azure:

  1. First thing to note is that you will need to sign-up for a Windows Azure account and configure a storage container. You can click here for a free trial.
  2. Now, on SSMS, choose a database to backup. But instead of the normal Backup task, select “Export Data-Tier Application”. This is going to walk you through the process of exporting the schema with the data as a “BACPAC” file output.

3. On the next screen in the wizard, you will select the Azure storage account and container where SQL Server will store the export. Note that it will first backup the database (schema & data) to a local file and then upload it to the cloud for you.

4. Once the process is complete, you will see your exported backup as a BACPAC in your storage container. To restore a BACPAC, you right-click on the file from your container and select “Import Data-Tier Application”.

BTW, this process is identical to the way that you can export & import databases in SQL Azure. You can also easily now move your databases (schema and/or data) to and from SQL Server and SQL Azure with these BACPACs. This is not a full-service TLOG and data file backup like SQL Server native backups. This is more of a database export/import mechanism. But that is pretty much the most interactive that a DBA will get with SQL Azure anyway because you do not perform any TLOG maintenance on SQL Azure database. Microsoft takes care of that maintenance for you.

Measure Server IO Baseline Before Deploying SQL Server

A very important step that a DBA should take whenever deploying a new server that will serve as a SQL Server database server is something that I don’t see enough DBAs take before actually installing SQL Server.

That is, test your IO subsystem with a tool that creates synthetic (aka not-real SQL Server transactions) transactions from the server to your SAN. It is called SQLIO and it is a free tool that really has nothing to do with SQL Server: download it here. Running SQLIO will tell you the kind of performance that you can expect natively from your IO subsystem by simulating read/write transactions similar to SQL Server patterns. This way, you will capture the throughput measurements with empirical data that can be shared with your SAN team before laying down SQL Server bits.

Why is it important to baseline the SAN IO before you install and configure SQL Server? Because you want to measure the raw IO throughput without taking into considering SQL Server configurations and because you want the SAN fully optimized for SQL Server. Once the LUNS have been finalized, then you can use those drive mappings in your SQL Server installation and you have the baseline numbers of what you will expect to receive in terms of SAN performance already in hand.

There are a lot of options available to the command-line for SQLIO so start with this TechNet article that steps you through running SQLIO. That article usually serves us pretty well with customers and there are a few generalizations to keep in mind for SQL Server DBAs when running SQLIO that I’ll list here for you as well:

  1. It helps to measure true SAN bandwidth by setting a file size on the command line that is larger than the storage cache. You want to measure against the disks without assuming that you’ll always read from the SAN’s cache.
  2. Start with a low value for outstanding I/Os (command line option: -o 20) and increase this value until no further gain is noted in aggregate throughput.
  3. When you run the tool, you will get output files that contain information similar to what I have pasted below. Notice that I have bolded the areas in the output to give you IOPS and throughput in MBs/sec. This is your disk performance output:

Sun 01/29/2012

12:03:44.09

sqlio v1.5.SG

using system counter for latency timings, 14318180 counts per second

parameter file used: single_lun_10.txt

file c:\dev\data01\sqlio_1_1.dat with 4 threads (0-3) using mask 0x0 (0)

4 threads reading for 30 secs from file c:\FT\data01\sqlio_1_1.dat

using 512KB sequential IOs

enabling multiple I/Os per thread with 20 outstanding

using specified size: 10 MB for file: c:\dev\data01\sqlio_1_1.dat

initialization done

CUMULATIVE DATA:

throughput metrics:

IOs/sec:   1371.11

MBs/sec:   685.55

latency metrics:

Min_Latency(ms): 24

Avg_Latency(ms): 101

Max_Latency(ms): 118

histogram:

ms: 0  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+

%:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 100

 

 

Data File Autogrow and Policies

Today, I’d like to consolidate 3 different areas of data file growth management from 3 of my favorite sources of best practices and information: SQL CAT, MS SQL TIPS and BOL.

The way that I boil this down for DBAs that I work with is this: for smaller, less critical databases, data file autogrow is a good practice to keep the database functioning and requires less care & feeding. For critical high-activity databases, you can keep autogrow set for data files, but manage the file usage and growth through tools like the UCP dashboard in SQL Server 2008 R2 or a central management tool like System Center. Here is the SQL CAT document with good practices to follow on data file storage in general, with a few good tips on managing data files on critical databases.

A more rudimentary (and complete) discussion on data file growth is on Microsoft’s site here. I point you to that because it is important to note that if you are using a tool that monitors your overall file system utilization, that does not tell you how full your data file is getting and when it will auto-grow. Keep an eye on the free space in the database with a script like the ones here or with Utility Control Point from SQL Server.

Lastly, the really, really awesome new feature in SQL Server 2008 R2 is policy-based management which allows you to apply a standard policy for data file autogrow across your ENTIRE SQL Server footprint. In addition, you can use it to do more than simply evaluate out-of-compliance SQL Server databases in terms of data file management. You can then APPLY that policy to each database. Here is a quick tutorial on how to do exactly that. ENJOY!