sqlCat

Blog Post: Building Great Cloud Applications - Decoded

jenlussier — Tue, 07 May 2013 23:49:00 GMT

The application you write on premise is written to a piece of software purchased, installed and configured on a piece of computer hardware that you privately own. The application you write in the cloud is written to a set of services that are available to you as well as the public to exploit.

Read here to learn about how they are different. We also provide Cloud Service fundamentals code on Code Gallery.

Blog Post: Be aware of the difference in isolation levels if porting an application from Windows Azure SQL DB to SQL Server in Windows Azure Virtual Machine

SanjayMishra — Wed, 01 May 2013 06:26:00 GMT

Author: Sanjay Mishra, Tim Wieman

Technical Reviewers: Mike Weiner, Shep Sheppard, Prem Mehra, Chuck Heinzelman, James Podgorski, Silvano Coriani, Madhan Arumugam Ramakrishnan, Sunil Agarwal, Guy Bowerman

Windows Azure SQL Database and SQL Server in Windows Azure Virtual Machine are two different data storage options and are best suited for slightly different application scenarios. Refer to the published articles on MSDN for a comparison and how to choose between these options.

There are certain differences between the two storage options. One important different is the isolation level of the database. Depending upon application design, difference in isolation level can impact the application concurrency, and ultimately, application performance and throughput.

It is not a common practice to port databases from Windows Azure SQL Database to SQL Server in a Windows Azure VM environment. However, for some specific unique reasons, not covered here, one of our customers performed such a migration and the only change made was to change the connection string.

During the load test after the porting, we noticed that the performance of the application on SQL Server in Windows Azure VM did not match up to the performance of the application on Windows Azure SQL Database. Upon further investigation, we found much higher volume of lock waits under SQL Server in Windows Azure VM.

The key difference that impacted the application behavior is the isolation level of the two databases. On Windows Azure SQL Database, the isolation level settings for READ COMMITTED SNAPSHOT and SNAPSHOT ISOLATION are turned on:

select name, snapshot_isolation_state, is_read_committed_snapshot_on from sys.databases

name	snapshot_isolation_state	is_read_committed_snapshot_on
CustomerDB	1	1

On a SQL Server database (running on Windows Azure VirtualMachine, or on-premises), the isolation level settings for READ COMMITTED SNAPSHOT and SNAPSHOT ISOLATION are turned off, by default:

select name, snapshot_isolation_state, is_read_committed_snapshot_on from sys.databases

name	snapshot_isolation_state	is_read_committed_snapshot_on
CustomerDB	0	0

The customer application was designed and developed on Windows Azure SQL Database and it did make use of READ COMMITTED SNAPSHOT ISOLATION (RCSI).

The performance comparison of the two data storage options, under default settings:

Data Platform	Application Response Time (sec)	Application Throughput (web pages / sec)
Windows Azure SQL Database	0.83	38
SQL Server in Windows Azure VM (default)	2.94	13.9

As you can see, the application throughput for SQL Server in Windows Azure VM was almost one-third of the throughput with the database on Windows Azure SQL Database. One of the important goals of the load testing was to ensure application response time under 2 seconds. For SQL Server in Windows Azure VM, under default settings, the application response time was more than the 2 second threshold the customer had set. Analyzing waitstats perfmon counters (counter “Lock Waits” under object => “SQL Server:Wait Statistics”, instance => “Average wait time (ms)”), we observed about 1 sec average wait time for locks.

With RCSI turned on, the average lock wait time came down to a few milliseconds, application throughput significantly improved, and the application response time significantly improved.

Data Platform	Application Response Time (sec)	Application Throughput (web pages / sec)
SQL Server in Windows Azure VM (default)	2.94	13.9
SQL Server in Windows Azure VM (with RCSI)	1.14	34.8

Important Note: When turning on RCSI, be aware that SQL Server maintains the version store in the TEMPDB database, and you may observe significant activity in TEMPDB. Therefore, appropriate capacity and throughput must be planned for TEMPDB.

For example, for this specific scenario, after enabling RCSI, the increased throughput demands of TEMPDB necessitated scaling out TEMPDB data files to multiple data disks. Following is the TEMPDB disk I/O throughput comparison before and after RCSI:

Data Platform	Disk Read Bytes / sec	Disk Write Bytes / sec
SQL Server in Windows Azure VM (default)	403,031	1,504,410
SQL Server in Windows Azure VM (with RCSI)	26,570,531	58,219,559

Further Reading:

Blog Post: Access Azure Blob Stores from HDInsight

sqlcat — Thu, 25 Apr 2013 20:38:00 GMT

One of the great enhancements in Microsoft's HDInsight distribution of Hadoop is the ability to store and access Hadoop data on an Azure Blob Store. We do this via the HDFS API extension called Azure Storage Vault (ASV). This allows you to persist data even after you spin down an HDInsight cluster and to make that data available across multiple programs or clusters from persistent storage. Blob stores can be replicated for redundancy and are highly available. When you need to access the data from Hadoop you point your cluster at the existing data and the data persists even after the cluster is spun down.

Azure Blob Storage

Let's start with how your data is stored. A storage account is created in the Azure portal and has access keys associated with it. All access to your Azure blob data is done via storage accounts. Within a storage account you need to create at least one container, though you can have many. Files (blobs) are put in the container(s). For more information on how to create and use storage accounts and containers see: http://www.windowsazure.com/en-us/develop/net/how-to-guides/blob-storage/. Any storage accounts associated with HDInsight should be in the same data center as the cluster and must not be in an affinity group.

You can create a container from the Azure portal....

Read the complete blog post at: http://blogs.msdn.com/b/cindygross/archive/2013/04/25/access-azure-blob-stores-from-hdinsight.aspx

Blog Post: Self-Service BI Works!

sqlcat — Tue, 16 Apr 2013 16:51:00 GMT

When I talk to people about adding self-service BI to their company's environment I generally get a list of reasons why it won't work. Some things I commonly hear:

I can't get anyone in IT or on the business side to even try it.
The business side doesn't know how to use the technology.
This threatens my job.
I just don't know where to start either politically/culturally or with the technology.
I have too many other things to do.
How can it possibly be secure, allow standardization, or result in quality data and decisions?
That's not the way we do things.
I don't really know what self-service BI means.

So what is a forward thinking BI implementer to do? Well, Intel just went out and did it, blowing through the supposed obstacles. Eduardo Gamez of Intel's Technology Manufacturing Engineering (TME) group interviewed business folks....

Read the complete post and get the slides at Self-Service BI Works! http://blogs.msdn.com/b/cindygross/archive/2013/04/16/self-service-bi-works.aspx

Blog Post: Batching Techniques for SQL Database Applications in Windows Azure

Silvano Coriani — Tue, 09 Apr 2013 20:33:00 GMT

Batching operations to SQL Database significantly improves the performance and scalability of cloud services in Windows Azure. This paper first demonstrates the effectiveness of batching. It then covers the techniques, scenarios, and considerations to help you to use batching successfully in your cloud services. There are fixed processing costs to any interactions with a remote service, such as serialization, network transfer, and deserialization. Packaging many separate transactions into a single batch minimizes these costs.

In this paper, we want to examine various SQL Database batching strategies and scenarios. Although these strategies are also important for on-premises applications that use SQL Server, there are two main reasons for highlighting the use of batching for SQL Database:

Blog Post: Windows Azure SQL Database and SQL Server - Performance and Scalability Compared and Contrasted

Silvano Coriani — Fri, 08 Feb 2013 22:03:00 GMT

Curious about the differences in approaching performance and scalability in SQL Server vs. Windows Azure SQL Database (formerly SQL Azure)? Check out this new paper that brings together insights from the Microsoft SQL Engineering and Customer Advisory Teams (CAT) to detail the differences between on-premises SQL Server and Azure SQL Database tuning and monitoring techniques and best practices for database performance testing. If you’re an on-premises SQL Server whiz and currently integrating the cloud or considering a move to Platform as a Service, this paper is a must read!

Paper Overview

While SQL Server and Windows Azure SQL Databases have large and important similarities, they are not identical, and while the differences are relatively small, they affect the way that applications perform on SQL Database compared to SQL Server. As a result, the application architecture and performance evaluation techniques for each platform also differ.

This document explains these performance differences and their causes and includes real-world customer wisdom from experience troubleshooting performance on production customer SQL Databases. This document also examines common SQL Server performance evaluation techniques that do not work on SQL Database.

Read the full paper.

Blog Post: Hurricane Sandy Mash-Up: Hive, SQL Server, PowerPivot & Power View

sqlcat — Sat, 02 Feb 2013 01:36:00 GMT

For my #SQLPASS Summit 2012 talk SQLCAT: Big Data – All Abuzz About Hive (slides available to all | recording available to PASS Summit 2012 attendees) I showed a mash-up of Hive, SQL Server, and Excel data that had been imported to PowerPivot and then displayed via Power View in Excel 2013 (using the new SharePoint-free self-service option). PowerPivot brings together the new world of unstructured data from Hadoop with structured data from more traditional relational and multi-dimensional sources to gain new business insights and break down data silos. We were able to take very recent data from Hurricane Sandy, which occurred the week before the PASS Summit, and quickly build a report to pinpoint some initial areas of interest. The report provides a sample foundation for exploring to find additional insights. If you need more background on Big Data, Hadoop, and Hive please see my previous blogs and talks.

I will walk you through the steps to create the report including loading population demographics (census), weather (NOAA), and lookup table (state abbreviations) data into Hive, SQL Server, Excel, and PowerPivot then creating visualizations in Power View to gain additional insights. Our initial goal is to see if there are particular geographic areas in the path of Hurricane Sandy that might need extra assistance with evacuation. One hypothesis is that people above a given age might be more likely to need assistance, so we want to compare age data with the projected rainfall patterns related to the path of the hurricane. Once you see this basic demonstration you can envision all sorts of additional data sets that could add value to the model, along with different questions that could be asked given the existing data sets. Data from the CDC, pet ownership figures, housing details, job statistics, zombie predictions, and public utility data could be added to Hive or pulled directly from existing sources and added to the report to gain additional insights. Those insights might, for example, help first responders during future storms, assist your business to understand various ways it can help after a storm or major cleanup effort, or aid future research into reducing the damage done by natural disasters.

Read the complete post at http://blogs.msdn.com/b/cindygross/archive/2013/01/31/mash-up-hive-sql-server-data-in-powerpivot-amp-power-view-hurricane-sandy-2012.aspx

Cindy Gross Microsoft SQLCAT PM

Blog Post: SSIS Tips Tricks and Best Practices: SSIS Operational and Tuning Guide

sthoward — Wed, 02 Jan 2013 17:39:00 GMT

SSIS Operational and Tuning Guide

SQL Server Technical Article

Writer: Alexei Khalyako, Carla Sabotta, Silvano Coriani, Sreedhar Pelluru, Steve Howard

Technical Reviewer: Cindy Gross, David Pless, Mark Simms, Daniel Sol

Published: December 2012

Applies to: SQL Server 2012; Windows Azure SQL Database

Summary: SQL Server Integration Services (SSIS) can be used effectively as a tool for moving data to and from Windows Azure (WA) SQL Database, as part of the total extract, transform, and load (ETL) solution and as part of the data movement solution. SSIS can be used effectively to move data between sources and destinations in the cloud, and in a hybrid scenario between the cloud and on-premise. This paper outlines SSIS best practices for cloud sources and destinations, discusses project planning for SSIS projects whether the project is all in the cloud or involves hybrid data moves, and walks through an example of maximizing performance on a hybrid move by scaling out the data movement.

To review the document, please download the SSIS Operational and Tuning Guide Word document.

Blog Post: SSIS Tips Tricks and Best Practices: SSIS for Azure and Hybrid Data Movement

sthoward — Wed, 02 Jan 2013 17:36:00 GMT

SSIS for Azure and Hybrid Data Movement

SQL Server Technical Article

Writer: Alexei Khalyako, Carla Sabotta, Daniel Sol, Silvano Coriani, Sreedhar Pelluru, Steve Howard

Technical Reviewer: David Pless

Published: December 2012

Applies to: SQL Server 2012

Summary: SQL Server Integration Services (SSIS) can be used effectively as a tool for moving data to and from Windows Azure SQL Database, as part of the total extract, transform, and load (ETL) solution and as part of the data movement solution. SSIS can be used effectively to move data between sources and destinations in the cloud, and in a hybrid scenario between the cloud and on-premise. This paper outlines best practices for using SSIS for cloud sources and destinations and for project planning for SSIS projects to be used with Azure or hybrid data moves, and gives an example of maximizing performance on a hybrid move by scaling out the data movement.

To review the document, please download the SSIS for Azure and Hybrid Data Movement Word document.

Blog Post: New version of Partition Management Utility supports SQL2012, ColumnStore Indexes

sqlcat — Mon, 03 Dec 2012 20:02:00 GMT

A new version of the Partition Management Utility for SQL Server is now available on Codeplex at http://sqlpartitionmgmt.codeplex.com/. This is a tool that helps you create necessary staging tables and indexes and associated check constraints, to support partition-switch operations against existing partitioned tables. This solves the problem of keeping staging table scripts in synch when a permanent partitioned table evolves to contain new boundary values or column attributes.

The latest version adds some significant new capabilities:

Support for SQL Server 2012 Columnstore Indexes
Option to create TSQL scripts for staging stables, rather than executing DDL
Date constraints are now specified in a locale-independent format better supporting globalized deployments
Compatible with a broader range of partitioning column types

The utility is a command line tool but it also can be integrated into an SSIS process or used to generate SQL scripts that then become part of a regular "sliding window" partition management scenario. The new version is backward-compatible with SQL Server 2005, 2008 and 2008 R2.

Blog Post: SQL Clinic at PASS Summit 2012

SanjayMishra — Sat, 03 Nov 2012 00:10:00 GMT

SQLCAT joins hands with CSS to bring you SQL Clinic.

Bring your design / architecture questions, and real world troubleshooting issues and get expert help.

Clinic Hours:

Wednesday

07:00 – 08:00
10:00 – 18:15

Thursday

07:00 – 08:00
10:00 – 18:30

Friday

07:00 – 14:00

Note that the Clinic has moved from past years' location to a newer and bigger room.

It is in Room 4C-3 this year.

If you are attending PASS Summit, you don't want to miss out on the SQL Clinic experience.

See you all there.

Blog Post: SQLCAT Sessions at PASS Summit 2012

SanjayMishra — Sat, 03 Nov 2012 00:00:00 GMT

SQLCAT will be deeply involved in the PASS Summit 2012. There wil be several technical sessions providing indepth technical learnings and best practices derived from real-world customer experiences.

Wednesday, November 07, 2012
10:15 AM - 11:30 AM	[AD-307-M] SQLCAT: What Are the Largest SQL Server Projects in the World?	[RM:609]
10:15 AM - 11:30 AM	[BIA-305-A] SQLCAT: Big Data – All Abuzz About Hive	[RM:606-607]
10:15 AM - 11:30 AM	[CLD-309-A] SQLCAT: Best Practices and Lessons Learned on SQL Server in an Azure VM	[RM:613-614]
10:15 AM - 11:30 AM	[BID-302-M] Mobile Business Intelligence	[RM:6E]
1:30 PM - 2:45 PM	[CLD-302-M] SQLCAT: How Do I Troubleshoot My Database Now that It Is in the Cloud?	[RM:305-TCC]
1:30 PM - 2:45 PM	[DBA-403-M] SQLCAT: SQLOS Memory Manager Changes in SQL Server 2012	[RM:301-TCC]
1:30 PM - 2:45 PM	[DBA-305-M] SQLCAT: Many-Core Processors, SSDs, Large Memory: How to Benefit SQL Server	[RM:613-614]
3:00 PM - 4:15 PM	[DBA-315-A] SQLCAT: AlwaysOn HA/DR Design Patterns, Architectures and Best Practices	[RM:609]
4:45 PM - 6:00 PM	[AD-402-A] SQLCAT: Real-World Case Study of Mission-Critical Active/Active Remote DCs	[RM:303-TCC]
4:45 PM - 6:00 PM	[DBA-314-M] SQLCAT: AlwaysOn Unplugged – Everything You Want to Know About AlwaysOn	[RM:305-TCC]
4:45 PM - 6:00 PM	[DBA-306-M] SQLCAT: How Does Microsoft Run Its SAP Landscape on Windows and SQL Server?	[RM:307-308]
4:45 PM - 6:00 PM	[BIA-304-M] Configuring Kerberos for SharePoint 2010 BI in 7 Steps	[RM:606-607]
Thursday, November 08, 2012
10:15 AM - 11:30 AM	[AD-300-M] Bootstrapping Data Warehousing in Azure for Use with Hadoop	[RM:612]
1:30 PM - 2:45 PM	[BID-300-M] Running Reporting Services in SharePoint Integrated Mode: How and Why	[RM:608]
3:00 PM - 4:15 PM	[CLD-300-M] SQLCAT: SQL Azure Design Patterns and Best Practices	[RM:609]
5:00 PM - 6:15 PM	[DBA-323-A] SQLCAT: SQL Server 2012 AlwaysOn HA/DR Customer Panel	[RM:608]
5:00 PM - 6:15 PM	[BIA-206-M] BI Power Hour	[RM:602-604]
Friday, November 09, 2012
8:00 AM - 9:15 AM	[CLD-303-A] SQLCAT: What Are the Largest Azure Projects in the World?	[RM:608]
9:45 AM - 11:00 AM	[CLD-304-M] SQLCAT: Architecture Patterns and Lessons Learned from Azure ISV Projects	[RM:606-607]
1:00 PM - 2:15 PM	[CLD-308-A] SQLCAT: How SQL Azure Supports Large-Scale Customer Deployments	[RM:307-308]

See you all at PASS Summit 2012.

Blog Post: Impact of Adding a Node to a Windows Cluster on the Possible Owners property of existing SQL Server Failover Cluster Instances

SanjayMishra — Wed, 15 Aug 2012 18:33:00 GMT

Author: Sanjay Mishra

Contributor: David P Smith (ServiceU)

Technical Reviewers: Charles (Curt) Mathews, James Podgorski, Eric Kang, Prem Mehra, Min He

It is common to have more than one SQL Server Failover Cluster Instance (FCI) within a single Windows Server Failover Cluster (WSFC). It is also fairly common that not all nodes in the WSFC are the possible owners of all the FCIs within that WSFC. One such scenario is when you have a FCI + AG solution (AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using Failover Cluster Instances and Availability Groups), where multiple FCIs reside within a single WSFC, and an availability group is created across the FCIs, as shown in the Figure 1 below:

Figure 1: An FCI+AG solution across two data centers (FCI being used for HA and AG being used for DR)

In this deployment,

Possible owners of SMFCI601\SM_FCI_601 = {PE2970-01, PE2970-02}.
Possible owners of SMFCI602\SM_FCI_602 = {R900-03, R900-04}.

High level steps to build such a solution are:

Step 1: Build the primary site failover cluster instance SMFCI601\SM_FCI_601 on nodes PE2970-01 and PE2970-02.
Step 2: Add the DR site nodes R900-03 and R900-04, to the same Windows cluster, and then create the secondary failover cluster instance SMFCI602\SM_FCI_602 on R900-03 and R900-04.
Step 3: Create the AG across the two instances SMFCI601\SM_FCI_601 and SMFCI602\SM_FCI_602.

After building the primary site failover cluster instance (Step 1 above, shown in Figure 2 below), you will see the possible owners of the resources for the failover cluster instance resource group SMFCI601\SM_FCI_601 as shown in the Figure 3.

Figure 2: Topology after Completing Step 1 (2-node FCI created at the primary data center)

Figure 3: Possible owners of the FCI network name resource after Completing Step 1 (2-node FCI created at the primary data center)

This is correct, and as expected.

During Step 2, the two nodes from the DR site (R900-03 and R900-04) are first added to the WSFC (Figure 4). Once these two nodes are added to the WSFC, you will see that the newly added nodes have been unexpectedly added as possible owners (Figure 5) for all the resources of the previously existing FCI (SMFCI601\SM_FCI_601).

Figure 4: Two nodes from the DR site added to the WSFC

Figure 5: Possible owners list of the FCI network name resource of the primary FCI, after adding two nodes from the DR site to the WSFC

This is neither intended nor desired. R900-03 and R900-04 can never be the possible owners of any resources of SMFCI601\SM_FCI_601. So, why does this happen? It is the way SQL Server FCI setup works with Windows clustering. When a node is added to the WSFC, the node is added as a possible owner for all existing FCIs. This works well in some scenarios, but doesn’t work well in the scenario we are discussing in this article.

The possible owners list must be manually modified each time you add a node to the WSFC. Figure 6 shows corrected possible owners list for one resource (the FCI network name on the primary).

Figure 6: Possible owners list of the FCI network name resource of the primary FCI, after manually correcting the list

Repeat this for all resources (other than disks, the disk resources don’t demonstrate this behavior), such as:

FCI network name
FCI IP address
SQL instance
SQL Agent
Any other resources part of the resource group must be checked for correct possible owners.

And repeat this for each FCI in your topology. Note that the possible owners list for the resources in each FCI will differ from other FCIs.

The possible owners list must be appropriately modified before proceeding to create the availability group across the FCIs (Step 3 listed above), otherwise AG creation will fail, because there will be overlap between the possible owners of the two instances hosting the AG replicas.

This should not be confused with the possible owners list for the availability group resource. You should not alter the possible owners list for the availability group (DO NOT use Windows Failover Cluster Manager to perform Availability Group Failover).

Blog Post: Review a working option to setup a SQL Server 2012 BI solution using Azure’s IaaS VMs

sqlcat — Wed, 18 Jul 2012 02:13:00 GMT

By: Nicholas Dritsas

Reviewers: James Podgorski, Olivier Matrat, Sanjay Mishra

Overview

Azure’s Iaas VMs plus Virtual Networks can be used to configure easily a set of servers/VMs to host the main components of a BI solution. This process assumes you already have an Azure subscription plus access to add new VMs and Virtual Networks.

Setting up a virtual network

Go to the Azure portal here: https://windows.azure.com/. Ensure you connect to the preview portal.
Select +New bottom left of the welcome screen and choose Network, Custom Create. (for setup details, look here.)

Enter a network name and choose either an existing Affinity Group or choose to create a new one.
For Address space, you can give a range such as 10.4.0.0/16. If you like, you can break it down to a few subnets for better segmentation.
Then select to Create a DNS, give it a name and use as IP address the first valid IP of your range, eg 10.4.0.4. This IP is expected to be used by the first VM we will create below as our Domain controller. Mind you, in Azure a VM keeps its provided IP
the same during its lifetime. So, even if your settings remain DHCP, your IP remains the same even if the VM restartsor fails over. For more details, see here.

Creating a DC (this step must be done first before adding any other VM in the network you just created).
1. Create a new VM from the Gallery using Windows 2008 R2 image. Small or medium is fine since this will be used only as DC and DNS. Then, ensure you choose VM as standalone, choose your storage account in
  the same region to host it and select the Virtual Network name you created above.
2. After the new VM creation is complete, attach to it a new empty disk of 10GB or so.
3. Then, connect to it by selecting the VM name and selecting connect at the bottom.

i. After you connect, ensure its IP is indeed 10.4.0.4.

ii. Using the disk management found in server manager, format the new disk as F: drive. Create 3 subdirectories there to host AD files later on.

iii.Then, we need to promote to it to DC, look here for details. In simple terms, run the program dcpromo.exe from the command prompt and follow instructions to create a new forest.

Choose DHCP and ignore the warning
Store AD db, logs and sysvol on the 3 subdirectories in the F: drive
After all setup is done, restart the VM.

iv. After it restarts, go to Windows Explorer, select Network and Enable file sharing and discovery.

v. Create a new domain admin account user too for future use

4. Now, you have a domain in azure. Create one new VM from the Gallery using SQL 2012. Then, join the domain you just created. Go to Windows Explorer, select Network and Enable file sharing and discovery.
Now, you should be able to ping the DC by IP and name.

i. You have to open inbound ports in the vm that hosts ssas and sql server (or you disable the firewall for domain networks if you like). See here: http://msdn.microsoft.com/en-us/library/bb934283.aspx

ii. You can find the ssas ports or any process ports from this link: http://support.microsoft.com/kb/2466860. I added a new inbound rule to allow ports 1433 and 2383. 1433 is for sql server when 2383 is the port for the default
ssas instance.

5. Create another new VM from the Gallery using Windows 2008 R2. Then, join the domain you just created. Go to Windows Explorer, select Network and Enable file sharing and discovery. Now, you should be able to ping the DC.

Install Excel 2010 and try to connect to SSAS server. It should work fine.

Blog Post: AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using Failover Cluster Instances and Availability Groups

SanjayMishra — Sat, 23 Jun 2012 00:42:00 GMT

Authors: Joseph Sack (SQLskills.com), Sanjay Mishra (Microsoft)

Technical Reviewers: Min He (Microsoft), Chuck Heinzelman (Microsoft), Alexi Khalyako (Microsoft), Charles Mathews (Microsoft), Prem Mehra (Microsoft) , Juergen Thomas (Microsoft), Mike Weiner (Microsoft), Amitabh Tamhane (Microsoft), Brent Ozar (Brent Ozar PLF), Gianluca Hotz (SolidQ), David P. Smith (ServiceU), Michael Steineke (Edgenet), Glenn Berry (SQLskills.com)

Content Program Manager: Glenn Minch (Microsoft)

Summary: SQL Server 2012 AlwaysOn Failover Cluster Instances (FCI) and AlwaysOn Availability Groups provide a comprehensive high availability and disaster recovery solution. Prior to SQL Server 2012, many customers used FCIs to provide local high availability within a data center and database mirroring for disaster recovery to a remote data center. With SQL Server 2012, this design pattern can be replaced with an architecture that uses FCIs for high availability and availability groups for disaster recovery business requirements. Availability groups leverage Windows Server Failover Clustering (WSFC) functionality and enable multiple features not available in database mirroring. This paper details the key topology requirements of this specific design pattern, including asymmetric storage considerations, quorum model selection, quorum votes, steps required to build the environment, and a workflow illustrating how to handle a disaster recovery event in the new topology across participating job roles.

Read on:http://msdn.microsoft.com/en-us/library/jj215886

Blog Post: AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using AlwaysOn Availability Groups

SanjayMishra — Sat, 23 Jun 2012 00:36:00 GMT

Authors: Joseph Sack (SQLskills.com), Sanjay Mishra (Microsoft)

Technical Reviewers: Lindsey Allen (MS), Juergen Thomas (MS), Mike Weiner (MS), Prem Mehra (MS), Yorihito Tada (MS), Curt Matthews (MS), Amitabh Tamhane (MS), Aditya Samant (MS), Daniel Janik (MS), Jimmy May (MS), David P Smith (ServiceU), Richard Waymire (SolidQ), Brent Ozar (Brent Ozar PLF), Wolfgang Kutschera (bwin.party), Paul S. Randal (SQLskills.com), Gianluca Hotz (SolidQ), Ayad Shammout (Caregroup)

Content Program Manager: Glenn Minch (Microsoft)

Summary: SQL Server 2012 AlwaysOn Availability Groups provides a unified high availability and disaster recovery (HADR) solution that improves upon legacy functionality previously found across disparate features. Prior to SQL Server 2012, several customers used database mirroring to provide local high availability within a data center, and log shipping for disaster recovery across a remote data center. With SQL Server 2012, this common design pattern can be replaced with an architecture that uses availability groups for both high availability and disaster recovery. This paper details the key topology requirements of this specific design pattern, including quorum configuration considerations, steps required to build the environment, and a workflow that shows how to handle a disaster recovery event in the new topology.

Read on: http://msdn.microsoft.com/en-us/library/jj191711

Blog Post: AlwaysOn Availability Groups Generate Network Traffic with No User Activity

sqlcat — Thu, 21 Jun 2012 21:31:00 GMT

Author: Kevin Cox

Contributors: Min He,Steve Lindell

Reviewers: Sanjay Mishra, Juergen Thomas, Jimmy May

Creating an availability group needs pings and status checks across the different servers involved. This accounts for approximately 500 bytes per database in the group. The PerfMon counter used to track the activity is “Bytes Received from Replica/sec”. Books Online has recently been changed to reflect a high level description. The purpose of this blog is to provide a bit more detail.

Books Online http://msdn.microsoft.com/en-us/library/ff878472.aspx

Counter Name	Description
Bytes Received from Replica/sec	Number of bytes received from the availability replica per second. Pings and status updates will generate network traffic even on databases with no user updates.

The key to understanding this counter is that the traffic is per database. On one customer project with 40 databases in one availability group, this counter was showing about 8k/sec when there was no user traffic. This consists of bi-directional
ping and status checks.

What does the primary do with this data it gets back from pings and status checks? It displays the information on the Always On Dashboard. There are two main sources for the dashboard data.

sys.dm_hadr_database_replica_states. This runs every second unless the primary is too busy, then it will run as soon as it can. This is used to display the information from the latest status checks, but does not drive any network traffic.
sp_server_diagnostics. It is called by the Availability Group (AG) Resource DLL, which is only run on the AG primary node and only connects to the local instance. It does not contribute to the network traffic mentioned above, but is useful to know that it is running periodically according to the HEALTH_CHECK_TIMEOUT setting. All the Availability Groups on one instance share a single resource DLL.

Hopefully, with this information you will be able to understand why the Bytes Received from Replica/sec counter is showing activity even when there is no user activity.

Blog Post: Running SQL Server in Windows Azure Virtual Machine - Performance Guidelines for Preview

Lindsey.allen — Sat, 09 Jun 2012 00:36:00 GMT

A Windows Azure Virtual Machine is similar to a virtual machine run on premise except that it runs in Windows Azure, Microsoft’s public cloud. Starting with the Windows Azure 2012 Preview release, new Virtual Machine capabilities have been added to the Windows Azure. We support a number of scenarios for SQL Server running in Windows Azure in addition to moving existing SQL Server applications to Windows Azure Virtual Machines. For details on how to migrate your existing SQL Server application, see Migrating with Windows Azure Virtual Machines.

The performance of a relational database in Windows Azure Virtual Machine depends on many factors, including the VM size, the number and configuration of disks, the network throughput and latency, the configuration of the database software and the application workload. We encourage users to benchmark their application on several VM sizes and storage configurations in order to select the most appropriate configuration.

In order to help customers with the task of characterizing the performance for migrating to Windows Azure Virtual Machine, we are working on extending the Microsoft Assessment and Planning (MAP) Toolkit for SQL Server to have more built-in awareness for Windows Azure VMs.

VM Size

To improve the performance of your database, you need to understand which of the server’s resources is the performance constraint. If your database performance is limited by CPU or memory, users can scale up the compute available to the SQL instance by choosing a larger VM. Follow Set-AzureVMsize for more detail on how to upgrade to a bigger VM size. Please note that this involves taking the VM offline and restarting the VM.

Be mindful that this is a cloud environment with a multi-tenant storage and networking infrastructure, so you should expect some variance in performance. In general, for production workloads we recommend running SQL Server on Medium or larger VMs. For best performance use extra-large VMs.

We recommend the default setting for the SQL configuration option “max server memory” in order to allow SQL to manage its memory needs dynamically.

Number and configuration of disks

The number of disks allowed for your Windows Azure VM varies based on the VM size, with each disk up to 1TB. For example, in an extra-large VM, up to 16 disks are supported. For more details, please refer to the table below (source here).

If your performance is strongly disk I/O sensitive, be mindful that a larger VM may be required in order to add enough disks to meet your I/O requirements. For details, please see the How to attach a data disk to a virtual machine topic in How to create and configure a virtual machine in Windows Azure.

Being a shared cloud service, performance of Windows Azure VM, especially the I/O capacity, also depends on concurrent load – performance will vary, for example, based on the time of day. We recommend users to validate the disk configuration to determine its I/O capacity prior to deploying SQL Server. SQLIO Disk Subsystem Benchmark Tool is available for performing such tests.

In terms of configuration, we recommend placing data and log on separate disks from the OS disk for better performance.

Disk cache settings

The default cache settings on the OS disk and the data disk are different.

On the OS disk, the default is ON for both read and write caching.
On the data disk, the default is OFF for both read and write caching.

For running SQL workloads, we recommend keeping the write cache OFF. In case data has to be placed on the OS disk, disable the write caching on the OS disk in the service model. Use Windows Azure Powershell command Set-AzureOSDisk to disable write caching. Please note that changing the cache settings on the OS disk while running will result in a reboot of the VM.

Apart from Windows Azure OS disk and data disk, there is also Temporary Storage available in the VM (usually exposed as drive “D:\”). This temporary storage, however, does not provide data durability as OS disks or data disks do. We recommend users refrain from keeping data that need to be persisted on this drive.

Performance Tuning and Troubleshooting

In general, users have the same database performance tuning options in the Windows Azure Virtual Machine environment that they would have in a physical server environment, this includes resources such as Windows performance counters, Xperf and SQL Server DMVs. Additionally, for logging and troubleshooting performance of the networked drives, the user can leverage Storage Analytics, a tool for performance logging and troubleshooting against Windows Azure storage.

Additional Resources

Blog Post: Memory Error Recovery in SQL Server 2012

Lindsey.allen — Wed, 06 Jun 2012 00:14:00 GMT

Xin Jin; Fabricio Voznika; Madhan Arumugam; Lindsey Allen

SQL Server 2012 has many hidden gems, one of them is the capability of recovering from memory corruption error. We will tell you how it works in this article.

Memory Error Recovery in SQL Server 2012
Hardware errors and taxonomy
Soft error vs. hard error
Corrected error vs. uncorrected error
Fatal error vs. non-fatal error
What is memory scrubbing?
How To: Find if this feature is available
How To: Detect that a page has been repaired
How to: Detect uncorrected hardware memory corruption
How To: Monitor the system

SQL Server is able to recover from memory corruption when hardware support is available. Platforms that support hardware memory scrubber can send notification to applications when memory corruption is detected. SQL Server responds to these notifications and attempts to repair the memory. Clean database pages in buffer pool are restored by reading the page again from disk. This new feature helps SQL Server to remain running even when there are hardware memory errors.

Hardware errors and taxonomy

Please see the Hardware Errors and Error Sources on MSDN for an overview of hardware errors and their definitions.

Soft error vs. hard error

A soft error is an error in a signal or datum which is wrong. After a soft error is observed , there is no implication that the system is any less reliable than before. If detected, a soft error may be corrected by rewriting correct data in place of erroneous data. An example of a soft error is a single bit flip.

Unlike soft error, a hard error is an error that occurs because of a physical hardware issue such as a defect, a mistake in design or construction, or a broken component. These errors require that the hardware causing the error be replaced. Rewriting the data does not correct the error.

Corrected error vs. uncorrected error

A corrected error is a hardware error condition that has been corrected by the hardware or the firmware by the time that the operating system is notified about the presence of the error condition.

An uncorrected error is a hardware error condition that cannot be corrected by the hardware or the firmware. Uncorrected errors are classified as either fatal or nonfatal.

Fatal error vs. non-fatal error

A fatal hardware error is an uncorrected or uncontained error condition that is determined to be unrecoverable by the hardware. When a fatal uncorrected error occurs, the operating system generates a bug check to contain the error.

A nonfatal hardware error is an uncorrected error condition from which the operating system can attempt recovery by trying to correct the error. If the operating system cannot correct the error, it generates a bug check to contain the error.

What is memory scrubbing?

Memory scrubbing is the process of detecting and correcting bit errors in computer memory by using error-detecting codes like ECC. Memory scrubbing can detect and correct soft, correctable errors. For certain soft, uncorrected non-fatal errors, SQL Server captures of this information and checks whether the corrupted memory is part of a clean database page that is in Buffer Pool. If that is the case, this page is tossed and the memory is de-allocated. If corruption is in another region of memory that can not be repaired, only logging is done to notify of the event and no action is taken.

How To: Find if this feature is available

If the memory error recovery feature is available, you will be able to see the following by looking at SQL errorlog.

Machine supports memory error recovery. SQL memory protection is enabled to recover from memory corruption.

How To: Detect that a page has been repaired

If the memory corruption is associated with a clean page in Buffer Pool, SQL Server is able to recover from it and the following message will be logged in the errorlog.

SQL Server has detected hardware memory corruption in database '%ls', file ID: %u, page ID; %u, memory address: 0x%x and has successfully recovered the page.

Also you can monitor the detection and repair of the memory corruption with the following Extended Events (XEvents):

- bad_memory_detected: corrupted memory has been detected and reported to SQL. The memory may or may not belong to a database page.

- bad_memory_fixed: memory corruption has been detected and fixed. Fields of then event contain more details regarding the exact page affected

How to: Detect uncorrected hardware memory corruption

SQL Server is able to recover from memory corruption of clean pages in buffer pool. It cannot recover from memory errors associated with dirty pages or outside Buffer Pool. Upon detection of such uncorrectable hardware errors, the following entry can be observed from errorlog.

Uncorrectable hardware memory corruption detected. Your system may become unstable. Please check the Windows event log for more details.

How To: Monitor the system

The user can monitor the detection of memory corruption using sp_server_diagnostics. The system component state will be set to warning upon detection of a memory corruption. In addition, the data column logs information about count of bad page detected, count of bad page fixed as well as the virtual address of the bad page last encountered.

Blog Post: Comparing I/O characteristics in AlwaysOn Availability Groups and Database Mirroring

ytada — Fri, 01 Jun 2012 22:19:00 GMT

Author: Yorihito Tada

Contributors: Sanjay Mishra, Steve Lindell

Technical Reviewers: Prem Mehra, Mike Weiner, Sunil Agarwal

Microsoft SQL Server 2012 introduces AlwaysOn Availability Groups, which is a high availability and disaster recovery solution. The basic concept of AlwaysOn Availability Groups is similar to Database Mirroring, which was introduced in SQL Server 2005 SP1. AlwaysOn Availability Groups offers improvements over Database Mirroring; for example, with AlwaysOn Availability Groups, you can have multiple databases in one availability group, and you can have up to four secondary replicas for every primary replica. For more information about AlwaysOn Availability Groups, see Overview of AlwaysOn Availability Groups (http://technet.microsoft.com/en-us/library/ff877884(v=SQL.110).aspx).

AlwaysOn Availability Groups not only extends the functionality of Database Mirroring, it also provides performance enhancements. This paper provides some technical details on two of these enhancements:

The I/O efficiency of flushing data pages on the secondary replica
Throughput improvements by optimized I/O for transaction log file using log pool

The paper also discusses the results of tests that we performed to demonstrate these features and their effect on performance.

I/O Efficiency of Flushing Pages on the Secondary

One significant change in SQL Server 2012 is the improved I/O efficiency on the secondary replica (also known as the mirror database in Database Mirroring). Database Mirroring does a continual flush of the dirty pages, because to go from a ‘restoring’ state to an ‘online’ state (that is, from the mirror database to the principal database), all pages are required to be on disk. To shorten recovery time at failover, Database Mirroring writes dirty data pages to disk continuously. Because databases in AlwaysOn Availability Groups are online in both primary and secondary replicas (regardless of whether they are readable or not) , the flush is not required on a role change. Therefore AlwaysOn Availability Groups does not need to force page flushes except at checkpoints or buffer pool memory pressure. This change significantly reduces I/O for the secondary replica, when there are multiple updates on same page.

To compare AlwaysOn Availability Groups and Database Mirroring, I used one OLTP-type database and the same workload for AlwaysOn Availability Groups and Database Mirroring. Each database had 12 data files (6 files on each file group) and one transaction log file. The total size was approximately 80 GB allocated and 35 GB used at the initial state. Both AlwaysOn Availability Groups and Database Mirroring were configured as synchronous replicas or mirrors. The workloads were mixed, with INSERTs, UPDATEs and READs. The concurrent connected user count was 1,000 users. On each node, data files were placed on drive H:(direct attached SSD drive), and transaction log files were on drive E:(SAN storage).

Figure 1: The Data Disk Write Byte /sec counter

In Figure 1, the red line shows the Disk Write Bytes/sec counter values on the secondary node data disk. It shows that there were approximately 500 MB per second of write I/O every minute for AlwaysOn Availability Groups; this write I/O occurred at the checkpoint. There were continuously 200 MB per second of write I/O for Database Mirroring. Note: The blue dotted line tracks the Disk Write Bytes/sec counter on the primary replica (in AlwaysOn Availability Groups) or principal database (in Database Mirroring), showing the checkpoint activity, which is a very similar pattern in both technologies. Average Disk Write Bytes/sec is around 63 MB per second for AlwaysOn Availability Groups and 205 MB per second for Database Mirroring during workload. These test results show that AlwaysOn Availability Groups reduces write I/O on the mirror significantly, compared to Database Mirroring.

Throughput improvements by optimized I/O for transaction log file using the log pool

SQL Server 2012 Availability Groups introduces another improvement, the dynamic cache capability of the log pool. This capability increases throughput capacity on the AlwaysOn Availability Groups databases. The log pool is a new common cache mechanism for log records consumers. When a transaction is run on the primary replica, the transaction log records are written to the log cache, and at the same time they are sent to the log pool to be passed along to the secondary replica. Figure 2 shows an example of this with a single secondary replica, although the logic is the same for multiple secondary replicas. If an unsent log is not in cache, AlwaysOn Availability Groups or Database Mirroring log capture threads have to read the log from the file. The log pool serves as a dynamic cache that can grow until unsent log entries fit into the cache, if there is no memory pressure. Then AlwaysOn Availability Groups adds less I/O to the log file for read than Database Mirroring does.

Figure 2: Data movement architecture of AlwaysOn Availability Groups

In Figure 3, the red line shows Batch Requests/sec, the blue line shows Log write waits, and the green line shows Disk Read Bytes/sec for the transaction log. These results are all taken from the primary replica or the principal database.

Figure 3: Batch Requests/sec

For AlwaysOn Availability Groups, the average value for Batch Requests/sec is about 5000, and for Database Mirroring, the average is 4000. This improvement in throughput is caused by a difference in the Log writes waits counter and heavier read I/O (as measured by the Disk Read Byes/sec counter) on the transaction log file for Database Mirroring. This enhancement comes from the log pool with a dynamically sized log cache. Database Mirroring, on the other hand, uses a fixed-size log cache. These log pool changes enable AlwaysOn Availability Groups to handle spikes in log volume much better, which results in an increase in capacity over Database Mirroring.