Microsoft SQL Server Development Customer Advisory Team

Performance Guidance for SQL Server in Windows Azure Virtual Machines

Sanjay Mishra — Mon, 17 Jun 2013 23:18:58 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.

Access Azure Blob Stores from HDInsight

Cindy Gross — 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 from Hadoop you point your cluster at the existing data and the data persists even after the cluster is spun down.

Azure Blob Storage

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

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

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

Self-Service BI Works!

Cindy Gross — 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, allow standardization, or result in quality data and decisions?
That's not the way we do things.
I don't really know what self-service BI means.

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

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

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

Cindy Gross — Sat, 02 Feb 2013 01:36:00 GMT

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

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

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

Cindy Gross Microsoft SQLCAT PM

New version of Partition Management Utility supports SQL2012, ColumnStore Indexes

Stuart Ozer MSFT — 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 adds some significant new capabilities:

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

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

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

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

By: Nicholas Dritsas

Reviewers: James Podgorski, Olivier Matrat, Sanjay Mishra

Overview

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

Setting up a virtual network

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

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

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

i. After you connect, ensure its IP is indeed 10.4.0.4 by running ipconfig in command prompt.

ii. Using the disk management found in server manager, format the new disk as F: (or any other drive letter you like) drive. Create 3 subdirectories there to host AD db, logs and sysvol files later on.

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

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

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

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

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

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

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

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

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

AlwaysOn Availability Groups Generate Network Traffic with No User Activity

Kevin Cox SQL Server — Thu, 21 Jun 2012 21:31:27 GMT

Author: Kevin Cox

Contributors: Min He,Steve Lindell

Reviewers: Sanjay Mishra, Juergen Thomas, Jimmy May

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

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

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

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

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

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

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

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

Kevin Cox SQL Server — 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 mismatched SIDs

Both scenarios give same results.

Reproducing the problem:

Create several logins and users on a standalone SQL instance.
Create a simple RS report in VS2010 while connected as one of the new users
Click Preview and the report runs fine.
Create an Availability Group with a readable secondary, restore, and make sure everything is active and synchronized.
Create a Listener.
In the Visual Studio report, change the connection string to connet to the listener and add ApplicationIntent=Readonly.
Click Preview. A login error is generated on the screen. And an error gets generated in the SQL Server log.
Add the logins and users to secondary server, but in a different order than on the primary.
Click Preview on the report. Again, the login error is generated.
Sync the SIDs using either method in the links below.
Click Preview. The report is generated immediately.

Fixing the problem:

Either of the following methods can be employed to synchronize Login SIDs:

Create the logins on the secondary with the SID=sid option in the CREATE LOGIN command. In order to do this one, you will have to get the SIDs and logins from the primary before you create them on the secondary. Or you can get them from the user record in the secondary database.
Follow the instructions and use the script from KB 918992. Even though it says it is used to transfer logins from SQL Server 2005 to SQL Server 2008, it can also be used to create a script from your primary server to be run on the secondary to create the same logins with the same SIDs.

Tuning Spatial Point Data Queries in SQL Server 2012

ndr — Wed, 18 Apr 2012 04:48:03 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.

Technical Reference Guides for Designing Mission-Critical Solutions using SQL Server are available

Prem Mehra — Wed, 07 Mar 2012 01:57:00 GMT

The Technical Reference Guides for Designing Mission-Critical Solutions
provide planning and architecture guidance for various mission-critical workloads deployed by users. These guides
reflect the knowledge gained by the CAT team while working with customers on
mission-critical deployments, many of these deployments being the result of
deep customer engagements by the CAT organization. Each guide provides not only
the key technical concepts and information helpful for design, but also
“lessons learned,” best practices, and references to customer case studies.

The Technical Reference Guides for Designing Mission-Critical Solutions cover three workloads:

Online Transactional Processing (OLTP)
These guides cover transactional and batch processing, relational and “beyond relational” data, scale-up and scale-out,
data center optimization, compliance, high availability and disaster recovery,
and data access.
Data Warehousing (DW)
These guides cover massively parallel processing (PDW), symmetric multi-processing (SMP), reference and custom architectures, data
sources, ETL, warehouse consumers, data center optimization, compliance, and
high availability and disaster recovery.
Middleware
These guides cover messaging, workflow, distributed caching, web services, complex
event processing, enterprise integration patterns, high availability and disaster recovery, and operations.

These technical reference guides are useful after one has made the conceptual architectural decisions to address
specific business needs and is ready to work through the technical design alternatives to choose a solution.

You can access these guides through their home page at http://technet.microsoft.com/en-us/sqlserver/hh273157

Applies to: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2