SQL Server Customer Advisory Team - SQL Server Best Practices

Maximizing Throughput with TVPs

sthoward — Thu, 13 Jun 2013 18:01:00 GMT

Author: Steve Howard

Technical Reviewers: Silvano Coriani, Sanjay Mishra, James Podgorski, Mark Simms

Introduction

This technical note looks at considerations of whether to use the SqlBulkCopy, or Table Valued Parameters (TVPs) in a customer scenario encountered as part of a CAT engagement. The decision of which is better depends on several considerations which will be discussed. TVPs offer several performance optimization possibilities that other bulk operations do not allow, and these operations may allow for TVP performance to exceed other bulk operations by an order of magnitude, especially for a pattern where subsets of the data are frequently updated.

Executive Summary

TVPs and MERGE operations make a powerful combination to minimize round trips and batch insert and update data with high throughput. Parallel operation on naturally defined independent sets of data can be performed efficiently like this. The TVP makes optimizations possible that are not possible with bulk insert or other operations types. To get the most out of the operation, you must optimize your underlying table as well as your method for inserting and updating the data. The principles followed in this case emphasize these points:

· Do not create artificial keys with IDENTITY when it is not necessary. This creates a point of contention on heavy, parallel insert operations.

· If old data key values will not expire, use a MERGE operation instead of DELETE and INSERT. This minimizes data operations; rebalancing and page splits, and the amount of data that must be replicated. If old data key values will expire, then test two operations of MERGE followed by a deletion of only the expired keys rather than a DELETE and INSERT of the full data set.

· If not all the data will be changed, modify the “WHEN MATCHED” portion of the MERGE statement to also check that the data that may change has changed, and only update the data that is actually changed. This minimizes the number of rows of data that are actually modified, and thus minimizes the amount of data that must be replicated to secondaries in Windows Azure SQL Database environments.

Although these are best practices in any environment they become increasingly important in a shared environment such as Windows Azure SQL Database.

Scenario

In a recent engagement, a problem was encountered in performance of inserting and updating data in Windows Azure SQL Database. The scenario was:

· Data from hundreds of thousands of devices needs to be stored in Windows Azure SQL Database

· Each device stores approximately 8000 rows of configuration data across three tables in the database

· Data for each device is updated approximately once per day

· Only the most current data is stored in Windows Azure SQL Database

· The data must be processed at a sustained rate of six devices per second (Approximately 48,000 rows per second)

The first concept tried was to delete the data for each device first, then use the BulkCopy API to insert the new rows. Several worker role instances were used to scale out the processing of the data into the database. However; when running this against an Azure SQL Database, this did not give the performance the scenario demanded.

The second approach was to use Table Valued Parameters (TVPs) with stored procedures to do the processing. In the stored procedures, the data was validated first. Next, all existing records were deleted for the device being processed, and then the new data was inserted. This did not perform better than the previous bulk insert option.

We were able to improve the process to meet the performance demands by making optimizations to the tables themselves, and to the stored procedures in order to minimize the lock, latch, and Windows Azure SQL Database specific contention the process initially encountered.

Optimizing the Process

Several optimizations were made to this process.

First, the underlying tables contained identity columns, and data needed to be inserted in sets from several different processes. This created both latch, and lock contention. Latch contention was created because each insert is performed only on the last page of the index, and several processes were trying to insert to the last page simultaneously. Lock contention is created because the identity column was the primary key and clustered index key, so all processes had to go through the process of having the identity value created, then only one at a time could insert. To remedy this type of contention, other values within the data were used as a primary key. In our example, we found composite keys of DeviceID and SubCondition in one table, and a combination of three columns in the second that third tables that could be used to maintain entity integrity. Since the IDENTITY column was not really necessary, it was dropped.

An example of the optimization of the table is

Original Table Definition:

CREATE TABLE dbo.Table1

(

RecordID BIGINT NOT NULL IDENTITY(1, 1),

DeviceID BIGINT NOT NULL,

SubCondition NVARCHAR(4000) NOT NULL,

Value NVARCHAR(MAX) NOT NULL,

SubValue TINYINT NOT NULL,

CONSTRAINT [pk_Table1] PRIMARY KEY CLUSTERED

(

RecordID ASC

)

Optimized Table Definition:

CREATE TABLE dbo.Table1

(

DeviceID BIGINT NOT NULL,

SubCondition NVARCHAR(4000) NOT NULL,

Value NVARCHAR(MAX) NOT NULL,

SubValue TINYINT NOT NULL,

CONSTRAINT [pk_Table1] PRIMARY KEY CLUSTERED

(

DeviceID ASC,

SubCondition ASC

)

The second suboptimal part of the described process is that the stored procedure deleted the old data for a device first, then re-inserted the new data for the device. This is additional maintenance of a data structure as deletes can trigger re-balancing operations on an index, and inserts can result in page splits as pages are filled. Making two data modifications, each with implicit maintenance work that must be done, should be avoided when the data operation can be done with one operation. A “MERGE” operation can be used in place of a DELETE then INSERT provided the updated set of data will not omit previous rows of data. In other words, this works if no SubConditions for any DeviceID in the example table will expire.

Any time data is modified in Windows Azure SQL Database, it must be replicated to two replicas. The DELETE then INSERT method was inefficient in this as well since both the delete and the insert operation must be replicated to the Azure SQL Database replicas. Using a MERGE with only the WHEN MATCHED and WHEN NOT MATCHED conditions will eliminate this double operation, and thus eliminates half of the data replication, but it still modifies every row of data. In the case of this scenario, at most, 10% of the incoming data would actually be different from existing data. By adding an additional condition to the MATCHED condition so that it reads “WHEN MATCHED AND (source.data <> target.data)” only the rows that contained actual data differences were modified, which means that only the data that was actually changed in the incoming data needed to be replicated to secondaries. Making this modification minimized SE_REPL_SLOW_SECONDARY_THROTTLE, SE_REPL_ACK, and other SE_REPL_* wait types.

The last area of optimization we took was to ensure efficient joining with minimal chance for contention among processes. This action was taken because the optimizer tended to want to scan both source and target tables to perform a merge join when processing the MERGE operation. This was not only inefficient, but caused significant lock contention. To eliminate this contention, the query was hinted with “OPTION (LOOP JOIN)”.

An example of the MERGE written to minimize the amount of data that must be processed into the tables is:

CREATE PROCEDURE [dbo].[TVPInsert_test]

@TableParam TVPInsertType_test READONLY

BEGIN

MERGE dbo.Table1 AS target

USING @TableParam AS source

ON target.DeviceID = source.DeviceID

and target.SubCondition = source.SubCondition

WHEN MATCHED AND

(Source.Value != target.Value

OR Source.SubValue != Target.SubValue)

THEN

UPDATE SET Value = Source.Value, SubValue = Source.SubValue

WHEN NOT MATCHED THEN

INSERT (DeviceID, SubCondition, Value, SubValue)

VALUES (Source.DeviceID, Source.SubCondition

, Source.Value, Source.SubValue)

OPTION (LOOP JOIN)

END

The Table Value Type definition created for use with this stored procedure:

CREATE TYPE dbo.TVPInsertType_test AS TABLE

(

DeviceID BIGINT NOT NULL,

SubCondition NVARCHAR(4000) NOT NULL,

Value NVARCHAR(MAX) NOT NULL,

SubValue TINYINT NOT NULL,

PRIMARY KEY CLUSTERED

(

DeviceID ASC,

XPath ASC

)

NOTE: Check the properties of joins before using join hints. Loop joins can explode in cost when scans, including range scans, are performed on the inner table (the table accessed second). However; the join in the MERGE is primary key to primary key. In this case, there is no chance for range scans on the inner table, and therefore, the risk of cost explosion on the loop join is eliminated.

Testing the Optimization

Performance was tested by running multiple concurrent processes to process data. Elapsed time was measured as only the time it took to execute the three stored procedures for the in-processing of the new data. In different tests, the amount of data changed in each incoming data set varied so that measurements could be taken with 10%, 18%, 25%, or 100% modified data. Since 10% was determined to be the most that would ever be seen on any particular processing day, the changed percentage of 10% was used as the main indicator of the amount of improvement the optimizations would yield, and other percentages were used to give an indication of what might happen should an exceptional day produce much different data.

To test headroom, the tests were run with 4, 8, 12, and 16 concurrent processes. 8 was considered to be the number of worker roles that would normally be processing data, so this was the main test of record. Testing with 12 and 16 concurrent processes allowed us to determine if it was likely that adding worker roles improved or hurt throughput, and thus evaluate whether bursts above the normal level of processing could be handled by scaling out the worker role tier.

In the tests, data was processed with no delay between data sets, and the elapsed time to process the data into the database was recorded. Initially, 8000 tests were run and statistics taken on it to give the indication. However; the number of tests was reduced to 1000 when comparing just the original stored procedures with the optimized stored procedures because the original method produced so much contention that it became obvious with the lower number of tests that the optimizations were worthwhile.

The comparison between stored procedures with 8 concurrent processes was:

Milliseconds	Original Stored Procedures and Tables	Optimized Stored Procedures and Tables
AVG	13095.27	422.91
Median	12553.00	356.00
Standard Deviation	3489.15	255.88
Max	32306	2750
Min	4460	210
MS/Device*	1636.91	52.86

* MS/Device was calculated as the average time/number of concurrent processes. It can be read as “On average, one device was processed every ____ milliseconds.” One device every 53 milliseconds is well within the requirement of 6 devices per second.

Conclusion

The question of whether to use SqlBulkCopy or TVP is not always a question of which operates faster. When not all the data that is received actually changes data in the table, using a TVP as a parameter for a stored procedure, and optimizing appropriately can lead to very significant performance advantages over other methods that must delete and insert full sets only.

Additionally, ensuring the underlying tables do not make use of IDENTITY columns or other order-forcing mechanism allows for data modifications to be spread over multiple database pages, thus removing the potential for contention for the single, last-page where ordered writes of new data will be performed.

Performance Guidance for SQL Server in Windows Azure Virtual Machines

SanjayMishra — Sat, 01 Jun 2013 00:41:00 GMT

Authors: Silvano Coriani, Jasraj Dange, Ewan Fairweather, Xin Jin, Alexei Khalyako, Sanjay Mishra, Selcin Turkarslan

Technical Reviewers: Mark Russinovich, Brad Calder, Andrew Edwards, Suraj Puri, Flavio Muratore, Hanuma Kodavalla, Madhan Arumugam Ramakrishnan, Naveen Prakash, Robert Dorr, Roger Doherty, Steve Howard, Yorihito Tada, Kun Cheng, Chris Clayton, Igor Pagliai, Shep Sheppard, Tim Wieman, Greg Low, Juergen Thomas, Guy Bowerman, Evgeny Krivosheev

Editor: Beth Inghram

Summary: Developers and IT professionals should be fully knowledgeable about how to optimize the performance of SQL Server workloads running in Windows Azure Infrastructure Services and in more traditional on-premises environments. This technical article discusses the key factors to consider when evaluating performance and planning a migration to SQL Server in Windows Azure Virtual Machines. It also provides certain best practices and techniques for performance tuning and troubleshooting when using SQL Server in Windows Azure Infrastructure Services.

Read on: Performance Guidance for SQL Server in Windows Azure Virtual Machines.

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.

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:

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)

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.

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

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

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

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.

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

Migration Guide: Migrating to SQL Server 2012 Failover Clustering and Availability Groups from Prior Clustering and Mirroring Deployments

SanjayMishra — Wed, 04 Apr 2012 23:45:00 GMT

Writer: Cephas Lin (Microsoft)

Contributors: Sanjay Mishra (Microsoft), David Smith (ServiceU Corporation), LeRoy Tuttle Jr. (Microsoft)

Technical Reviewers: Prem Mehra (Microsoft), Mike Weiner (Microsoft), Steve Howard (Microsoft), Kun Cheng (Microsoft)

Summary: Using Failover Cluster Instance (FCI) for local high availability within a data center and augmenting it with Database Mirroring (DBM) for disaster recovery has been a very popular HA+DR solution. With SQL Server 2012, this FCI+DBM solution can be replaced with a FCI+AG (Availability Groups) solution. This whitepaper describes how to migrate an existing FCI+DBM solution to a FCI+AG solution.

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

DO NOT use Windows Failover Cluster Manager to perform Availability Group Failover

SanjayMishra — Fri, 30 Mar 2012 19:12:25 GMT

Author: Sanjay Mishra
Contributors: David P Smith (ServiceU)
Reviewers: Chuck Heinzelman, Mike Weiner, Prem Mehra, Kevin Cox, Jimmy May, Tim Wieman, Cephas Lin, Steve Lindell, Goden Yao

Windows Server Failover Cluster (WSFC) is the foundation for SQL Server 2012 AlwaysOn Availability Group functionality. The Availability Group (AG) is registered as a resource group within the Windows Server Failover Cluster. Figure 1 shows an availability group in the Windows Failover Cluster Manager (FCM) interface.

Figure 1: Availability Group service in the Failover Cluster Manager interface

Even though Availability Group (AG) is a resource group within the Windows Server Failover Cluster, DO NOT use the Failover Cluster Manager (FCM) to perform certain operations on the AG:

DO NOT change the preferred owners and possible owners settings for the AG. When an AG is created, the preferred owners and possible owners settings for the AG are established based on the primary and secondary servers information provided to SQL Server. Whenever a failover happens, the preferred owners and possible owners settings for the AG are reset based on the new primary. This is automatically done for you by the AG, so do not try to manually configure these settings.
DO NOT change the preferred owners and possible owners settings for the AG listener. Similar to the AG discussion above the AG Listener settings are handled automatically.
DO NOT move the AG between nodes using the Windows Failover Cluster Manager. The FCM doesn’t provide or have any awareness as to the synchronization status of the secondary replicas. Therefore, if the replica is not synchronized and the AG resource is failed over, the failover will then fail which can lead to extended downtime. The recommended ways to perform AG failover include SQL Server Management Studio and T-SQL statements.
DO NOT add or remove resources in the AG resource group.

Note that the FCM does not prevent you from performing any of these operations. However, we recommend against executing these actions through FCM, as doing so may result in unintended outcomes, including unexpected downtime.

Concurrent ADD NODE operation yields unexpected results in a SQL Server Failover Cluster Instance

SanjayMishra — Fri, 30 Mar 2012 18:43:00 GMT

Author: Sanjay Mishra
Contributors: David P Smith (ServiceU)
Reviewers: Chuck Heinzelman, Mike Weiner, Prem Mehra, Kevin Cox, Jimmy May, Min He

Installing a SQL Server Failover Cluster Instance (FCI) usually involves performing a setup on the first node, and after that, running setup to ADD NODE on the other node(s) in the FCI. A recent SQLCAT lab engagement required a 4-node FCI; and, with the intention of saving overall setup time, we tried to be “smart” by:

First, running setup on Node1.
Then, running setup on Node2 because we wanted to test the user interface when performing an “add node” operation
Then, concurrently running setups on Node3 and Node4 (setup on Node4 started a few seconds later than Node3). These were run concurrently because we just needed to get the nodes added to the FCI as quickly as possible to start application testing.

After the concurrent setups on Node3 and Node4 were complete, we looked at the properties of the FCI, and what we saw surprised us. Node3 was missing from the possible owners list of all the resources. We expected the possible owners list to be Node1, Node2, Node3, and Node4, but what we saw were Node1, Node2, and Node4 listed as possible owners, but not Node3.

After further analysis, we discovered what happened during the concurrent setup on Node3 and Node4.

After initial install on Node1 and Node2(possible owners = Node1 & Node2)
Node3 setup started (possible owners=Node1 & Node2)
Node4 setup started a few seconds later (possible owners=Node1 & Node2)
Node3 setup finished: possible owners set to previous owners (Node1 & Node2) PLUS this node (Node3)
Node4 setup finished: possible owners set to previous owners (Node1 & Node2) PLUS this node (Node4)
End result: Possible owners=Node1, Node2, Node4

Expected result: Possible owners=Node1, Node2, Node3, Node4

Upon further research, we found out that SQL FCI setup is not designed for concurrent setups on multiple nodes of an FCI. In fact, concurrent setup on multiple nodes of an FCI is not a supported operation. The correct approach to install this multi-node FCI is:

Run setup on Node 1, and once it is complete
Run setup on Node 2, and once it is complete
Run setup on Node 3, and once it is complete
Run setup on Node 4

Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery

SanjayMishra — Sat, 25 Feb 2012 17:28:00 GMT

Writer: LeRoy Tuttle, Jr. (Microsoft).

Contributors: Cephas Lin (Microsoft), Justin Erickson (Microsoft), Lindsey Allen (Microsoft), Min He (Microsoft), Sanjay Mishra (Microsoft).

Reviewers: Alexei Khalyako (Microsoft), Allan Hirt (SQLHA), Ayad Shammout (Caregroup), Benjamin Wright-Jones (Microsoft), Charles Matthews (Microsoft), David P. Smith (ServiceU), Juergen Thomas (Microsoft), Kevin Farlee (Microsoft), Shahryar G. Hashemi (Motricity), Wolfgang Kutschera (Bwin Party).

Summary: This white paper discusses how to reduce planned and unplanned downtime, maximize application availability, and provide data protection using SQL Server 2012 AlwaysOn high availability and disaster recovery solutions.

A key goal of this paper is to establish a common context for related discussions between business stakeholders, technical decision makers, system architects, infrastructure engineers, and database administrators.

The content is presented in two major parts:

High Availability and Disaster Recovery Concepts. Provide a brief discussion of the drivers and challenges of planning, managing, and measuring the business objectives of a highly available database environment. This discussion is followed by a brief overview of the high availability and disaster recovery capabilities of SQL Server 2012 AlwaysOn and Windows Server solutions.

SQL Server AlwaysOn Layers of Protection. Provide a deeper discussion of the feature capabilities, rationale, and dependencies of the layers of protection offered by a SQL Server AlwaysOn solution. It will cover infrastructure availability, SQL Server instance-level protection, database-level protection, and data tier application capabilities.

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

SQL Server 2012 AlwaysOn: Multisite Failover Cluster Instance

Mike Weiner — Thu, 22 Dec 2011 22:09:00 GMT

Writers: Mike Weiner, Sanjay Mishra, Min He

Contributors: Lingwei Li, Mike Anderson (EMC Corporation)

Technical Reviewers: Shaun Tinline-Jones, Steve Howard, Prem Mehra, Paul Burpo, Mike Ruthruff, Jimmy May, Matt Neerincx, Dan Benediktson, Michael Steineke (Edgenet Inc.), David P. Smith (ServiceU Corporation)

Applies to: SQL Server 2012

Summary: SQL Server Failover Clustering, which includes support for both local and multisite failover configurations, is part of the SQL Server 2012 AlwaysOn implementation suite, designed to provide high availability and disaster recovery for SQL Server. The multisite failover clustering technology has been enhanced significantly in SQL Server 2012. The multisite failover cluster architecture, enhancements in SQL Server 2012 to the technology, and some best practices to help with deployment of the technology are the primary focus of this paper.

You can download the complete document here: http://download.microsoft.com/download/D/2/0/D20E1C5F-72EA-4505-9F26-FEF9550EFD44/SQLServer2012_MultisiteFailoverCluster%20(2).docx

SQL Server 2012 AlwaysOn High Availability and Disaster Recovery Design Patterns

SanjayMishra — Thu, 22 Dec 2011 08:05:00 GMT

Author: Sanjay Mishra
Contributors: Justin Erickson, Mike Weiner
Reviewers: Prem Mehra, Juergen Thomas, Steve Howard, Chuck Heinzelman, Jimmy May

SQL Server 2012 AlwaysOn provides flexible design choices for selecting an appropriate high availability and disaster recovery solution for your application. SQL Server AlwaysOn was developed for applications that require high uptime, need protection against failures within a data center (high availability) and adequate redundancy against data center failures (disaster recovery). http://msdn.microsoft.com/en-us/sqlserver/gg490638 provides an overview of high availability and disaster recovery solutions available in SQL Server 2012 AlwaysOn.

Through working with customers who are evaluating and deploying SQL Server 2012 AlwaysOn currently, we have seen the following design patterns emerge as end-to-end HA+DR solution:

Using Multi-site Failover Cluster Instance (FCI) for local high availability and disaster recovery solution
Using Availability Groups (AG) for local high availability and disaster recovery solution
Using Failover Cluster Instance (FCI) for local high availability, and Availability Groups (AG) for disaster recovery solution

We expect most of the SQL Server 2012 AlwaysOn deployments to match one of these design patterns or contain slight variations.

So, how do these three design patterns compare and contrast? This blog highlights the salient features of each of these design patterns. A detailed whitepaper on each of these will be developed and published in near future.

Multi-site Failover Cluster Instance (FCI) for HA and DR

The ability to implement a multi-site FCI as a HA and DR solution has been available in the SQL Server product for a number of previous releases, and many customers have been successfully using the solution (example: http://sqlcat.com/sqlCat/b/whitepapers/archive/2010/09/20/sql-server-high-availability-and-disaster-recovery-for-sap-deployment-at-qr-a-technical-case-study.aspx). In earlier versions of SQL Server, multi-site FCI required a stretch VLAN. SQL Server 2012 removes that requirement (along with a number of other improvements to the failover cluster instance technology) enabling multi-site FCI to be more commonly adopted as a HA and DR solution.

Multi-site FCI requires storage level replication (provided by the storage vendor) to maintain a copy of the databases at the DR site. Even though there are separate storage volumes at each site, to SQL Server, this looks like a Shared Storage solution. Other important attributes of this solution are:

The unit of failover for both local HA, and remote DR is SQL Server instance.
No requirement on database recovery model, as storage level replication is used for maintaining the remote copy of the data.
The DR copy of the data is not readable.

The whitepaper http://sqlcat.com/sqlcat/b/whitepapers/archive/2011/12/22/sql-server-2012-alwayson_3a00_-multisite-failover-cluster-instance.aspx provides architecture details and best practices for this solution.

Availability Group for HA and DR

Using Database Mirroring for local high availability, and combining it with Log Shipping for a disaster recovery solution is a popular deployment architecture prior to SQL Server 2012 (example: http://sqlcat.com/sqlcat/b/whitepapers/archive/2010/10/29/high-availability-and-disaster-recovery-for-microsoft-s-sap-data-tier-a-sql-server-2008-technical-case-study.aspx).

With SQL Server 2012, the Database Mirroring and Log Shipping solution can be replaced with an Availability Group solution with multiple secondaries.

This is considered a non-shared storage solution, as each SQL Server in the topology has its own copy of data and does not need to share storage. Other important attributes of this solution are:

The unit of failover for local HA, and DR is the Availability Group (a group of one or more databases).
The database is required to be in the FULL recovery model.
The DR replica can be utilized as an Active Secondary (Readable Secondary Replicas, Backup on Secondary Replicas).

The whitepaper http://sqlcat.com/sqlcat/b/whitepapers/archive/2012/06/22/alwayson-architecture-guide-building-a-high-availability-and-disaster-recovery-solution-by-using-alwayson-availability-groups.aspx provides architecture details and best practices for this solution.

Failover Cluster Instance for local HA and Availability Group for DR

Using Failover Cluster Instance for local high availability, and combing it with database mirroring for a disaster recovery solution is a popular deployment architecture prior to SQL Server 2012 (example: http://sqlcat.com/sqlcat/b/whitepapers/archive/2009/08/04/high-availability-and-disaster-recovery-at-serviceu-a-sql-server-2008-technical-case-study.aspx).

With SQL Server 2012, the Database Mirroring can be replaced with an Availability Group for the DR solution, while continuing to use Failover Cluster instance for local HA.

This architecture is a combined Shared Storage and Non-Shared Storage solution. Other important attributes of this solution are:

The unit of failover for local HA is the SQL Server instance.
The unit of failover for DR is the Availability Group (a group of one or more databases).
The database is required to be in the FULL recovery model.
The DR replica can be utilized as an Active Secondary (Readable Secondary Replicas, Backup on Secondary Replicas).

The whitepaper http://sqlcat.com/sqlcat/b/whitepapers/archive/2012/06/22/alwayson-architecture-guide-building-a-high-availability-and-disaster-recovery-solution-by-using-failover-cluster-instances-and-availability-groups.aspx provides architecture details and best practices for this solution.

Summary

A brief outline and comparison of three common HA/DR design patterns with SQL Server 2012 AlwaysOn is provided above. The detailed architecture guides on each of these design patterns are published as separate whitepapers and the links are provided above.

Considerations for Using Azure Data Sync

sthoward — Wed, 21 Dec 2011 18:30:13 GMT

Author: Steve Howard

Technical reviewers: Prem Mehra, Thomas Kejser, Shaun Tinline-Jones, Murshed Zaman, Praveen M, Sudhesh Suresh

Azure Data Sync offers companies a way of synchronizing data between Azure databases and other Azure databases, or between Azure databases and on premises databases. On selected intervals, only changed rows are synchronized between databases in a synchronization group, thus minimizing the bandwidth requirement at sync time. This makes it an attractive option both for synchronizing data among production databases for locality or functionality, and also for moving data for migration to the cloud to minimize cutover time. However; the minimizing of the bandwidth and synchronization time comes at a cost which must be planned for when Data Sync services are part of your option. This article looks at considerations learned from a recent engagement.

For an introduction on Data Sync, its setup and use, see: http://blogs.msdn.com/b/windowsazure/archive/2011/11/22/new-webcast-series-explores-sql-azure-data-sync.aspx

How the tracking of changes is accomplished

When Data Sync is set up, on all tables to be synchronized, a new table is created using the naming convention DataSync.<tablename>_dss_tracking. This table will have the primary key columns from the base table, and 76 bytes/row of meta metadata about modifications to that row. The primary key values for every row in the tracked table, along with the meta-columns will be stored in this table so that at the end of this initial synchronization step, there will be a 1:1 ratio of rows in the base and tracking tables.

If you run the “Disk Usage by Table” report on a Synchronized database immediately after the provisioning, you will see a pattern like Figure 1.

Figure 1: Immediately after provisioning databases to use Azure Data Sync, a base table and its associated tracking table will have a 1:1 ratio of rows. The size (Reserved (KB)) of the tracking table can be significant - sometimes even more than the base table as seen when comparing dbo.PeopleType with DataSync.PeopleType_dss_tracking.

For tables with only a clustered index, the total space required to store the tracking table data and its indexes (as shone in the “Reserved (KB)” column) may be near to the storage space required for the base table. In fact, for a narrow base table such as the PeopleType table in the example, the tracking table may require more storage space than the base table.

To track data modifications, 3 triggers are created on each base table: one for inserts; one for updates; and one for deletes. For inserts, a new row is inserted in the tracking table. However; for deletes, the row is not immediately deleted from the tracking table. If it were, then it would not be possible for the Sync agent to know what rows were deleted. In the current version of Data Sync, the deleted rows will be marked as tombstoned and will stay in the tracking tables for 45 days after a delete operation before being removed by the Sync agent. This time is not configurable in the current version of Sync. This means that after deletes have been run on a table, there will likely be more rows in the tracking table than in the base table, and the tracking table may require significantly more storage space than the base table.

Planning for Data Sync Implementation

When planning to use Azure Data Sync to synchronize databases, you need to plan to leave enough space to store the tracking data both in any on-premises databases, and in any Azure databases to be synchronized. This may mean provisioning larger databases in Azure. In the example in Figure 1, when a synchronization was attempted with a 10 GB Azure database, less than half the data could be synchronized. You can see that even if the People table was reduced to a size of 9GB, the data still could not be synchronized with a 10 GB Azure database because the tracking table for People is nearly as large as the People table. If you have enough storage in the databases for the initial provision, but run out of space in a subsequent synchronization, then Data Sync will synchronize as much as there is room to synchronize, then will stop synchronizing.

With this in mind, here are a few recommendations when planning to use Data Sync:

· Run the “Disk Usage by Table” report on the database you want to synchronize.

· Start with the estimation that it may require as much space to store the synchronized tables with their associated tracking data as it takes to store just the table.

· Remember that wide primary keys will increase the storage space required as primary keys are stored in the tracking tables.

· Remember that deleted rows are stored for 45 days in the tracking tables. Therefore, with tables with significant numbers of deletes, you may need significantly more than double the storage space of the base table.

Other Considerations and Best Practices

After implementation, you need to consider what happens with schema modifications, or when a synchronized table is truncated.

Schema Modifications

Since only the primary key columns are tracked, the only schema modifications of concern are those that modify the primary key. Consider this chain of events:

1. Create a table with a primary key on col1.

2. Insert a row with col1 = 1 and col2 = 1.

3. Set this table up as a synchronized table.

4. Drop the primary key and re-create it on (col1, col2).

5. Insert a new row with col1 = 1 and col2 = 2.

When step 4 is performed, nothing happens to the tracking table, it will still have its primary key on col1 only. This means when step 5 occurs, another row will not be inserted into the tracking table. Instead, the existing row with col1 = 1 in this table is marked as updated. This creates a situation where the tables in the other synchronized databases cannot be updated properly. For this reason, when making changes to your primary keys on your tables, it may be necessary to drop and recreate your synchronization databases or groups.

Truncating tables

A truncation does not fire triggers. In the current version of data sync, nothing prevents synchronized tables from being truncating. However; truncating a table will cause the tables to be out of sync, and in a state where they cannot be synchronized without manual intervention. Do not truncate tables synchronized by data sync.

Conclusion

By understanding how Sync works, and by following best practices when dealing with tables with triggers, you can make use of Azure Data Sync as part of your migration, and as an ongoing part of your hybrid on-premises and Azure database applications. As with all Azure features, Data Sync is undergoing rapid development and improvement. Sync may operate differently in the future. Check the current documentation on Data Sync to verify operations as new features in SQL Azure and Windows Azure may make changes in Data Sync possible.

SQL Server Customer Advisory Team - SQL Server Best Practices

Maximizing Throughput with TVPs

Introduction

Executive Summary

Scenario

Optimizing the Process

Testing the Optimization

Conclusion

Related Reading

Performance Guidance for SQL Server in Windows Azure Virtual Machines

Building Great Cloud Applications - Decoded

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

Batching Techniques for SQL Database Applications in Windows Azure

Windows Azure SQL Database and SQL Server - Performance and Scalability Compared and Contrasted

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

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

SQL Clinic at PASS Summit 2012

SQLCAT Sessions 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

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

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

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

VM Size

Number and configuration of disks

Disk cache settings

Performance Tuning and Troubleshooting

Additional Resources

Memory Error Recovery in SQL Server 2012

Contents

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

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

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

Who is using AlwaysOn?

Migration Guide: Migrating to SQL Server 2012 Failover Clustering and Availability Groups from Prior Clustering and Mirroring Deployments

DO NOT use Windows Failover Cluster Manager to perform Availability Group Failover

Concurrent ADD NODE operation yields unexpected results in a SQL Server Failover Cluster Instance

Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery

SQL Server 2012 AlwaysOn: Multisite Failover Cluster Instance

SQL Server 2012 AlwaysOn High Availability and Disaster Recovery Design Patterns

Considerations for Using Azure Data Sync

How the tracking of changes is accomplished

Planning for Data Sync Implementation

Other Considerations and Best Practices

Schema Modifications

Truncating tables

Conclusion