Archives for March 2018

Join My Session On Deploying Highly Available SQL Server in Azure

March 31, 2018 by Jason Aw Leave a Comment

@Sqlsatnash Deploying Highly Available SQL Server In #Azure Session At SQL Saturday Nashville, Jan 16th

I’ll be heading to Nashville to share about deploying highly available SQL server. While there, there is a couple of things that I can’t wait to catch up on – Technology and music. While I’m there, I certainly hope I am able to have some good music at The Station Inn.

Come By My Session On Deploying Highly Available SQL Server in Azure

Jan 16th is going to be a great day of learning and networking. Hang out with my #SQLPass family and join my session. This hour long session is great for those who are keen in learning about deploying SQL Server in Azure.

On Cloud Database/Application Development & Deployment

As we are already aware, Windows Azure is an excellent IaaS platform to deploy SQL Server. There is a need to plan for high availability and disaster recovery even as Microsoft manages the infrastructure. In this session, learn how to leverage Azure Fault Domains, Upgrade Domains, and Internal Load Balancers to ensure high availability of SQL Server deployments within the Azure cloud. You will learn to see the difference between Azure Classic and Azure Resource Manager. And at the same time, how it would affect your SQL Server availability. While Microsoft Azure offers SLA’s of 99.95%, make sure your SQL Server deployment qualifies. Again, this session is best suited for those with intentions to move or have already moved your SQL Servers instances to Azure. By the way, participants for this session should have a basic knowledge of SQL Server AlwaysOn Failover Clustering as well as Availability Groups. But if you don’t, no fear because you should be able to catch up pretty fast with a little bit of practice and experimenting.

Reproduced with permission from https://clusteringformeremortals.com/2015/12/21/sqlsatnash-deploying-highly-available-sql-server-in-azure-session-at-sql-saturday-nashville-jan-16th/

Advantages of Azure Resource Manager You Must Know

March 31, 2018 by Jason Aw Leave a Comment

Advantages Of Azure Resource Manager And Highly Available SQL Server Deployments In Azure

Azure Resource Manager (ARM) is the latest and greatest way to work with Microsoft Azure IaaS. The advantages of Azure Resource Manager is plenty – template deployments, grouping, simplified billing and many more. This new model promises new features and more interaction.

Catch The Webinar

Microsoft Cloud and Datacenter Management MVP David Bermingham will introduce ARM and take a closer look at how to make use of ARM to enable highly available and scalable SQL Server deployments in the cloud.

Azure Resource Manager And Highly Available SQL Server Webinar

March 31, 2018 by Jason Aw Leave a Comment

Keen To Know How To Leverage ARM For Highly Available And Scalable SQL Server Deployment?

Well, if your answer is yes, then I’ve a great recommendation for you. Especially, if you’re interested to understand why Azure Resource Manager (ARM) is the latest and easiest way to work with Microsoft Azure IaaS, there’s a webinar happening on Dec 15 which would be perfect for you. It’s pretty great to work with ARM. First up, there is template deployments, grouping, simplified billing and much more. You have to try for yourself some of these new features, and the different new interaction ways with this new model.

See What Azure Resource Manager Can Do

Join Microsoft Cloud and Datacenter Management MVP David Bermingham as he introduce ARM. Take a close look at how you can use the power of ARM to deploy highly available and scalable SQL Server deployments in the cloud.

When: December 15th

Time: 1:00 PM EST/10:00 PST

Reproduced with permission from https://clusteringformeremortals.com/2015/11/16/azure-resource-manager-and-highly-available-sql-server-webinar-sqltips/

Get Started with Azure Resource Manager (ARM) and JSON

March 30, 2018 by Jason Aw Leave a Comment

Introduction to Azure Resource Manager (ARM) and JSON

If you are new to ARM and JSON, and want to learn more, there’s a presentation for you to get started. Personally, I highly recommend for you have a look at this informative presentation Freddy vs JSON: Build your cloud with Azure Resource Manager. It was just presented by James Bannan at Microsoft Ignite Australia. The presentation includes a look at the tools used and the structure of ARM templates. I wish I had this when I started looking at ARM and JSON earlier this year.

Reproduced with permission from https://clusteringformeremortals.com/2015/11/25/getting-started-with-azure-resource-manager-arm-and-json/

Different Highly Available SQL Server Storage Configurations in Azure

March 27, 2018 by Jason Aw Leave a Comment

An Overview Of Performance Differences of Highly Available SQL Server Storage Configurations In #Azure: SMB 3.0 File Service Or Premium Storage

There are a few options when it comes to SQL server storage configurations in Azure. If you want to know, you can get some good idea from this article Windows Server Failover Cluster on Azure IAAS VM – Part 1 (Storage). It talks about the newly released Azure File Service that can be used to host SQL Server cluster data over SMB 3.0. Remember, till date Azure File Service cannot support Premium Storage. You are bound to about 1,000 IOPS or 60 MB/s per file share. With these limits in mind, Azure File Service is probably going to to be an option for databases with minimal IO demands.

Check Out My Test Results

Different Highly Available SQL Server Storage Configurations in Azure

So the plan was to test a few different SQL Server Storage Configurations. I provisioned a DS4 VM and attached some premium storage. Next, I attached a SMB 3.0 File share using Azure File Service. Here’s how I configured my SQL Server Storage Configurations.

F:\ – Three 1 TB P30 Premium Storage Disks added to a single 3TB pool
G:\ – One 1 TB P30 Premium Storage Disk (no Storage Pool)
Z:\ – SMB 3.0 File share on Azure File Services

The Process

Be really careful when you’re configuring the Storage Pool for use in a cluster. Either you create the Storage Pool before the cluster is up, or use the Powershell script in Sql Alwayson with Windows 2012 R2 Storage Spaces if the cluster had already been created. I’ve created a Simple mirror (RAID o) Please note that I’m not worried about redundancy since Azure storage has triple redundancy on the backend.

To configure the Storage Pool for use in a cluster, you have to be careful on how you proceed. You either have to create the Storage Pool before you create the cluster or if the cluster is already created, use the Powershell script described in Sql Alwayson with Windows 2012 R2 Storage Spaces. For increased performance, the pool I created was a Simple mirror (RAID 0). I’m not concerned about redundancy since the Azure storage on the backend has triple redundancy.

I should get up to three times the performance of a single disk, since I’ve three disk in the Storage Pool in a RAID 0. Now, if I choose to add even more disk to the pool, I’ll enjoy even higher performance. A single P30 disk gives me 5000 IOPS and 200 MB/S. Based on this, I should expect up to 15000 IOPS and 600 MB/S throughput for my pool.

Now that I have the storage out of the way, I configured Dskspd to run the same test on each of the different volumes. Here is what I did with the parameters using Dskspd.

Diskspd.exe -b8K -d60 -h -L -o8 -t16 -r -w30 -c50M F:\io.dat

Diskspd.exe -b8K -d60 -h -L -o8 -t16 -r -w30 -c50M G:\io.dat Diskspd.exe -b8K -d60 -h -L -o8 -t16 -r -w30 -c50M Z:\io.dat

And The Results Are Out

The results on different SQL Server Storage Configurations were rather predictable and summarized below.

Different Highly Available SQL Server Storage Configurations in Azure

Looking at the result, this particular job did not push the upper limits of the theoretical maximum of any of these storage solutions. However, the latency had a significant impact on the overall performance of this particular test. The test used 8k blocks in a mix of 30% writes and 70% reads to simulate a typical SQL Server OLTP workload.

Of course, the more money you want to spend, the more performance you can expect to achieve. It’s relative.

Price Comparison Of SQL Server Storage Configuration in Azure

As of November 24, 2015, the price for the best solution shown here (F:\) would cost $1,216/month. It promises full access to 3 TB of storage with unlimited reads/writes.

The second best solution (G:\) would give you 1 TB of storage at 1/3 the price, $405/month. Azure File Share is priced at $0.10/GB plus additional charges for read/write operations. You are only charged for the actual usage. So estimating the actual cost will be very dependent on your usage. You are at about 25% of the cost of Premium Storage before the additional charges for read/write operations.

Prices, like everything else in the Cloud, tend to change rapidly to address the market demands. Have a look at the latest price information at https://azure.microsoft.com/en-us/pricing/details/storage/ for the latest price information.

Summary

From this compilation and price overview of SQL Server Storage Configurations, Azure File Services does look enticing from a price perspective. The latency at this point does not make it a viable option for any serious SQL Server workload. Instead, have a look at utilizing premium storage and leveraging either host based replication solutions such as SIOS DataKeeper to build SQL Server Failover Cluster Instances (SQL Standard or Enterprise) or look at SQL Server Enterprise Edition and AlwaysOn AG.

Reproduced with permission from https://clusteringformeremortals.com/2015/11/24/highly-available-sql-server-storage-options-in-azure-smb-3-0-file-service-or-premium-storage-a-look-at-performance-differences/