Achieving Application Consistent Recovery Points of SQL Server 2008 R2 With Azure Site Recovery In Azure

Achieving Application Consistent Recovery Points of SQL Server 2008 R2 With Azure Site Recovery In Azure

If you want to use ASR to replicate SQL Server 2008 R2 standalone or clustered instances, you will need to update the SQL Writer  to 2012 or later.

You can use SQL express version as it is a free download.

https://www.microsoft.com/en-us/download/details.aspx?id=29062

Once downloaded, navigate to the download location and run the executable with /x.  This will give you an option to specify a location to extract the files to.

ENU\x64\SQLEXPRADV_x64_ENU.exe /x

Once the extraction completes, navigate to the extracted location and the following location:

SQL\1033_enu_lp\x64\setup\x64

Within that folder you should find SQLWriter.msi.  Run this on the system where you want to update the SQL writer.

You now will be able to use ASR to do application consistent recovery points of SQL Server 2008 R2.

Reproduced with permission from Clusteringformeremortals.com

Major Cloud Outage Impacts Google Compute Engine – Were You Prepared? 

Major Cloud Outage Impacts Google Compute Engine – Were You Prepared?

Google first reported an “Issue” on Jun 2, 2019 at 12:25 PDT. As is now common in any type of disaster, reports of this outage first appeared on social media. Social media seems to the most reliable place to get any type of information early in a disaster now.

Many services that rely on Google Compute Engine were impacted. I’ve three teenage kids at home. Something was up when all three kids emerged from their caves, aka, bedrooms, at the same time with a worried look on their faces. Snapchat, Youtube and Discord were all offline!

They must have thought that surely this was the first sign of the apocalypse. I reassured them this was not the beginning of the new dark ages. And instead they should go outside and do some yard work. That scared them back to reality and they quickly scurried away to find something else to occupy their time.

All kidding aside, there were many services being reported as down, or only available in certain areas. The dust is still settling on the cause, breadth and scope of the outage. But it certainly seems that the outage was pretty significant in size and scope, impacting many customers and services including Gmail and other G-Suite services, Vimeo and more.

While we wait for the official root cause analysis on this latest Google Compute Engine outage, Google reported “high levels of network congestion in the eastern USA” caused the downtime. We will have to wait to see what they determine caused the network issues. Was it human error, cyber-attack, hardware failure, or something else?

Were You Prepared For This Cloud Outage?

I wrote during the last major cloud outage. If you are running business critical workloads in the cloud, regardless of the cloud service provider, it is incumbent upon you to plan for the inevitable outage. The multi-day Azure outage of Sept 4th, 2018 was related to a failure of the secondary HVAC system to kick in during a power surge related to an electrical storm. While the failure was just within a single datacenter, the outage exposed multiple services that had dependencies on this single datacenter. This made the datacenter itself a single point of failure.

Have A Sound Disaster Recovery Plan

Leveraging the cloud’s infrastructure, minimize risks by continuously replicating critical data between Availability Zones, Regions or even cloud service providers. In addition to data protection, having a procedure in place to rapidly recover business critical applications is an essential part of any disaster recovery plan. There are various replication and recovery options available. This includes services provided by the cloud vendor themselves like Azure Site Recovery, to application specific solutions like SQL Server Always On Availability Groups, to third party solutions like SIOS DataKeeper that protect a wide range of applications running on both Windows and Linux.

Having a disaster recovery strategy that is wholly dependent on a single cloud provider leaves you susceptible to a scenario that might impact multiple regions within a single cloud. Multi-datacenter or multi-region disasters are not likely. However, as we saw with this recent outage and the Azure outage last fall, even if a failure is local to a single datacenter, the impact can be wide reaching across multiple datacenters or even regions within a cloud. To minimize your risks, consider a multi-cloud or hybrid cloud scenario where the disaster recovery site resides outside of your primary cloud platform.

The cloud is just as susceptible to outages as your own datacenter. You must take steps to prepare for disasters. I suggest you start by looking at your most business critical apps first. What would you do if they were offline and the cloud portal to manage them was not even available? Could you recover? Would you meet your RTO and RPO objectives? If not, maybe it is time to re-evaluate your Disaster Recovery strategy.

“By failing to prepare, you are preparing to fail.”

― Benjamin Franklin

New Azure “SQL Server Settings” Blade In The Azure Portal

New Azure “SQL Server Settings” Blade In The Azure Portal

There is a new blade in the Azure portal when creating a new SQL Server virtual machine. I’ve been looking for an announcement regarding this new Azure portal experience but to no avail. This feature wasn’t available when I took the screen shots for my last post on creating a SQL Server 2008 R2 FCI in Azure on April 19th. I presume it must be relatively new.

Most of the settings are pretty self explanatory. Under Security and Networking, you can specify the port you want SQL to listen on. It also appears as if the Azure Security Group will be updated to allow different levels of access to the SQL instance: Local, Private or Public. Authentication options are also exposed in this new SQL Server settings blade.

The rest of the features include licensing, patching and backup options. In addition, if you are deploying the Enterprise Edition of SQL Server 2016 or later, you also have the option to enable SQL Server R Services for advanced analytics.

All of those options are welcome additions to the Azure portal experience when provisioning a new SQL Server instance. I’m sure the seasoned DBA probably has a list of a few dozen other options they would like to tweak before a SQL Server deployment, but this is certainly a step in the right direction.

Storage Configuration Options

The most interesting new feature I have found on this blade is the Storage Configuration option.

When you click on Change Configuration, you get the following blade.

As you slide the IOPS slider to the right you will see the number of data disks increase, the Storage Size increase, and the Throughput increase. You will be limited to the max number of IOPS and disks supported by that instance size. You see in the screenshot below I am able to go as high as 80,000 IOPS when provisioning storage for a Standard E64-16s_v3 instance.

There is also a “Storage optimization” option. I haven’t tried all the different combinations to know exactly what the Storage optimization setting does. If you know how the different options change the storage configuration, leave me a comment, or we will just wait for the official documentation to be released.

For my test, I provisioned a Standard DS13 v2 instance and maxed out the IOPS at 25600, the max IOPS for that instance size. I also optimized the storage for Transactional processing.

I found that when this instance was provisioned, six P30 premium disk were attached to the instance. This makes sense, since each P30 delivers 5000 IOPS, so it would take at least six of them to deliver the 25,600 IOPS requested. This also increased the Storage Size to 6 TB, since each P30 gives you one 1 TB of storage space. The Read-only host caching was also enabled on these disks.

I logged in to the instance to see what Azure had done with those disk. Fortunately, they had done exactly what I would have done; they created a single Storage Pool with the six P30 disks and created a Simple (aka, RAID 0) Storage Space and provisioned a single 6 TB F:\ drive.

This storage configuration wizard validates some of the cloud storage assumptions I made in my previous blog post, Storage Considerations for Running SQL Server in Azure. It seems like a single, large disk should suffice in most circumstances.

This storage optimization is not available in every Azure Marketplace offering. For example, if you are moving SQL Server 2008 R2 to Azure for the extended security updates, you will find that this storage optimization in not available in the SQL2008R2/Windows Server 2008 R2 Azure Marketplace image. Of course, Storage Spaces was not introduced until Windows Server 2012, so that makes sense. I did verify that this option is available with the SQL Server 2012 SP4 on Windows Server 2012 R2Azure Marketplace offering.

There is a minor inconvenience however. In addition to adding this new Storage configuration option on SQL Server settings blade, they also removed the option to add Data Disks on the Disks blade. Let’s say I wanted to provision additional storage without creating a Storage Space. To do that, I would have to create the instance first and then come back and add Data disks after it the virtual machine is provisioned.

Final Thoughts

All of the SQL Server configuration options in this new Azure blade are welcome additions. I would love to see the list tunable settings grow. Information text should include guidance on current best practices for each tunable.

What SQL Server or Windows OS tunables would you like to see exposed as part of the provisioning process to make your life as a DBA easier? These tunables make your life easier. They would also make the junior DBA look like a season pro by guiding them through all the current SQL Server configuration best practices.

I think the new Storage configuration option is probably the most compelling new addition. Prior to the Storage configuration wizard, users had to be aware of the limits of their instance size, the limits of the storage they were adding. On top of that, have the wherewithal to stripe together multiple disks in a Simple Storage Space to get the maximum IOPS. A few years ago, I put together a simple Azure Storage Calculator to help people make these decisions. My calculator is currently outdated. That said, this new Storage configuration option may make it obsolete anyway.

I would love to see this Storage configuration wizard included as a standard offering in the Disks blade of every Windows instance type. Instead in just the SQL Server instances. I would let the user choose to use the new Storage configuration  “Wizard” experience. Or even the “Classic” experience where you manually add and manage storage.

Reproduced with permission from Clusteringformeremortals.com

Microsoft Build 2019 Announcements And Sessions On Demand

Microsoft Build 2019 Announcements And Sessions On Demand

Missed the Microsoft Build 2019 because you were unable to get away from the office to attend? You will be glad to know that Microsoft has published all the sessions and are available online at no charge.

Being a developer focused conference most of the announcements were geared towards developers. You can see a complete list of searchable announcements here. https://azure.microsoft.com/en-us/updates/?updatetype=microsoft-build&Page=1

What’s Infrastructure as Code?

I’m more of an infrastructure guy. Some of the more interesting announcements to me were the following.

Azure VMware Solutions is now generally available

If I was heavily invested in VMware and was looking to expand into Azure, the availability of Azure VMware Solutions would certainly open up some interesting possibilities. It looks like if I use a bare-metal instance or dedicated instance, I can basically have an ESX host running in Azure. This makes sense for those planning a hybrid-cloud deployment and want to easily move workloads back and forth between on-prem and Azure. Leave me a comment to tell me why this excites you.

Azure Quickstart Center enables new customers to build cloud projects with confidence

I haven’t looked into this yet. However, this looks VERY interesting to me if it is what I think it is. As cloud adoption continues to grow so does the required skill set of the IT professional. A wise IT professional will want to become comfortable with Infrastructure as Code (IaC). Just two years ago, this skill set was non-existent. Larger IT consultants or cloud providers may have little or no experience as well. Over the past year I have seen this skill set become more common with the customers I work with. This is often the preferred deployment method. At the same time, IaS technology has also matured.

I predict that if you don’t already manage your cloud deployment with IaC, you will be in the near future. I’m hopeful that this new offering from Microsoft could be a great intro to those IT professionals looking to gain some IaC experience and knowledge. I’ll post a follow-up article after I have had a look at it.

Webinar: Availability Options and Requirements for SQL Server 2008 in Azure

Webinar: Availability Options and Requirements for SQL Server 2008 in Azure

Time is running out for your SQL Server 2008/2008 R2 instances. On July 9th the extended support date for SQL Server 2008/2008 R2 officially runs out. This means no more security updates will be issued. But wait, there is an option. By moving your SQL Server 2008/2008 R2 Instances to Azure, Microsoft will provide an additional three years of Extended Security Updates at no additional charge Running. Before taking the plunge on moving your SQL Server 2008/2008 R2 instances to Azure, you need to understand the availability options and requirements to ensure that your high availability and disaster recovery concerns are addressed.

Learn how to deploy SQL Server 2008 /2008 R2 Failover Cluster Instances in Azure properly. This includes addressing the lack of a shared storage option as well as networking requirements including the Internal Load Balancing requirements. Azure Site Recovery will be introduced as a viable alternative as a disaster recovery option which allows for a robust RPO/RTO in an entirely different Azure region.

Register Webinar: Availability Options and Requirements for SQL Server 2008 in Azure