SQL Server Customer Advisory Team - SQL Server Best Practices

Building Great Cloud Applications - Decoded

jenlussier — Tue, 07 May 2013 23:49:48 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.

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:

Access Azure Blob Stores from HDInsight

sqlcat — Thu, 25 Apr 2013 20:38:57 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...(read more)

Self-Service BI Works!

sqlcat — Tue, 16 Apr 2013 16:51:42 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...(read more)

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:

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...

...(read more)

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...(read more)

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

sthoward — Wed, 02 Jan 2013 17:39:58 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.

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.

New version of Partition Management Utility supports SQL2012, ColumnStore Indexes

sqlcat — Mon, 03 Dec 2012 20:02:13 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...(read more)

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.

SQLCAT Sessions at PASS Summit 2012

SanjayMishra — Sat, 03 Nov 2012 00:00:43 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.

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).

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...(read more)

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

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 more)

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

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...(read more)

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

Migrating Data-Centric Applications to Windows Azure

sthoward — Thu, 07 Jun 2012 21:19:00 GMT

The guide, Migrating Data-Centric Applications to Windows Azure, provides experienced developers and information technology (IT) professionals with detailed guidance on how to migrate their data-centric applications to Windows Azure Cloud Services, while also providing an introduction on how to migrate those same applications to Windows Azure Virtual Machines. By using this guide, you will have the planning process, migration considerations, and prescriptive how to’s needed for a positive migration experience.

Capturing the best practices from the real-world engagements of CAT and the technical expertise of the SQL Database Content team, Migrating Data-Centric Applications to Windows Azure can help you simplify the migration process, provide guidance on the most appropriate migration tools, and drive a successful implementation of your migration plan.

Get started: Migrating Data-Centric Applications to Windows Azure

P.S. eBook versions coming soon!

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.

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.

Fast Track: improving performance through correct LUN Mapping and Storage Enclosure configuration

AlexeiK — Thu, 03 May 2012 14:49:00 GMT

Author: Alexei Khalyako

Reviewers: Thomas Kejser, Marcel Franke (PM One); Kevin Cox; Erik Kraemer; Kun Cheng; Murshed Zaman; Chuck Heinzelman

During the recent ‘Proof of Concept’ we were testing mid-range Fast Track configuration. Fast Track configuration looked like: server with 32 cores, more than 200 GB of RAM and storage capacity about 40TB.

Before getting started looking at the SQL Server database performance, we obviously wanted to measure the maximum throughput of the storage and then to rely on it as the theoretical maximum of IO that system could deliver. According to the documentation of the hardware vendor the Maximum Consumption Rate is around 7,5 GB/sec, therefore in the real life we were expecting to see the IO in the range of 6-6,5GB/sec.

The very first runs of SQLIO brought quite surprising numbers: we couldn’t exceed 4,7 GB/sec.

Comment: following SQLIO command was used for analysis

ECHO ------ Sequential read block size 256 thread on Concurrent --------------------

sqlio -kR -t10 -s%1 -fsequential -o1 –b512 -LS -FCreate_big_test_file_Param.txt >> .\TestCycle3\sqliotest_sr_t10_b256_all.txt timeout /T 60

where:

-kR - means that ‘READ’ workload is used

-fsequential - workload is sequential

-b512 - block size 256, which is quite typical size for the data warehouse type of workload

Looking at the Performance Monitor, we also observed that instead of the nice flat line across all LUNs we have got absolutely uneven performance of the LUNs with the fluctuation up to 100 MB/sec between slowest and the fastest LUN.

All those observations indicated that we have a problem, but what exactly was the reason for such the underperforming behavior? Since we tested only the throughput of the system storage using SQLIO, we may conclude that the problem is on the hardware or hardware configuration site.

What should we then examine? Following components typically impact the data throughput capabilities:

PCI-slots -> HBA-> Network to the Switch ->Switch -> Network to the storage controller-> storage controller.

First we checked if the HBAs are installed in the right PCI-E slots. In the past we observed couple of cases where HBAs were installed in the slots which were not able to deliver the throughput the HBA can consume.

Note: PCIe x1 v1.0 can deliver about 250 MB/sec. PCIe x4 v1.0 slot can deliver aggregated throughput around 1GB/sec. PCIe x1 v2.0 provides with double performance of the v1.0, which is about 500 MB/sec. Accordingly, PCIe x4 v2.0 could deliver up to 2 GB/sec. Therefore, in order to get anticipated throughput, it would be recommended then installing 8Gb dual port HBA into the PCIe x4. However, not all motherboard PCIe slots are equal. You need to read the fine-print for the motherboard spec for full details.

Even though current hardware typically has enough high throughput PCI-E slots, there are still a couple of the ‘slow’ slots on the motherboards and it is quite understandable if during assembling of the box people could overlook in which slot they put the HBA. So, checking this first could be the easy step to do and very fast to fix if this was a reason for the low throughput.

However, in our case the HBAs were ‘sitting’ in the right slots. What’s next then? Since cabling looked correct, we had to check the mapping of the HBAs to the LUNs.

Checking the mapping on our configuration we observed each port A of the each individual HBA was mapped to all ports A of available storage enclosures. Following picture may help to illustrate configuration.

However even according to the vendor there must be explicit mappings created, matching up a single storage port with a single HBA port on the server. So, one HBA port must be connected to only one storage enclosure, like illustrated on the following picture:

The diagram show only how the ‘Active’ MPIO paths were mapped. For failover you can setup two paths per HBA with the MPIO policy ― ‘Failover Only’‖. This will direct MPIO to use a single path only and failover to the second or secondary path when the first one fails.

With described above configuration, where we fixed mapping issue we’ve got throughput increased about 20% and brought it up from 4,7 GB/sec to 5,7 GB/sec. During the SQLIO runs all LUNs were showing much more attractive picture

Additional configuration settings change of the Read Ahead option on the storage from the value ‘Default ‘ to ‘ 32MB’ helped to raise performance to the ~6,7 GB/sec which was additional +14% gain comparable to that we have got from the re-mapping the HBA-Storage Enclosure configuration.

Conclusion: Reference architectures and appliances give us greatly balanced configurations which help to speed up Data Warehouse deployments and vendors give very clear guidance on how to set it up for better performance. However, the old know wisdom “Trust but verify” is still true and may help your setup look way better.

Always On Availability Group: Connection to readable secondary fails when login SIDs are different or missing

sqlcat — Mon, 23 Apr 2012 14:17:00 GMT

When using ApplicationIntent=ReadOnly to access a readable secondary via the Availability Group Listener, the login SIDs have to be the same between primary and secondary. The connection attempt generates an error saying the login does not exist. This happens because the restored database on the secondary has a defined user, but no associated login. Two scenarios can cause this behavior: 1. Logins exist on primary but not on secondary 2. Logins exist on both primary and secondary but have...(read more)

Tuning Spatial Point Data Queries in SQL Server 2012

sqlcat — Wed, 18 Apr 2012 04:48:00 GMT

Written by : Ed Katibah, Milan Stojic, Michael Rys, Nicholas Dritsas Technical reviewers : Chuck Heinzelman Introduction : Spatial Point Data queries require particular tuning efforts to enhance performance and improve overall application throughput. SQL Server 2012 introduces a few key new features and improvements to assist you in that goal. Below, we go over a few key best practices and suggestions on how to achieve that. Read more here: http://social.technet.microsoft.com/wiki/contents...(read more)

Who is using AlwaysOn?

SanjayMishra — Tue, 17 Apr 2012 19:19:00 GMT

Several customers are already running SQL Server 2012 AlwaysOn in production. You can find the case studies with some of these reference customers here:

http://www.microsoft.com/casestudies/Case_Study_Search_Results.aspx?Type=1&Keywords=AlwaysOn&LangID=46