Best practices for SQL Server Availability

Microsoft, since 2012, has recommended Always-On Availability Groups as the Tier 1 solution for SQL Server databases.

Here are the “Always On” Architecture guides: https://blogs.msdn.microsoft.com/sqlalwayson/2012/07/03/alwayson-architecture-guides/

In particular, this guide is thorough, is written by still-famous experts in the SQL Server world, who do real world implementations of highly available and scalable solutions. https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/hh781257(v=msdn.10)

Additionally, the Microsoft “SQLCAT” (which stood for SQL Customer Advisory Team) (Largest Enterprises migrating to SQL Server from, shall we say, other platforms) has lots of best practice articles on AlwaysOn Availability Groups. https://blogs.msdn.microsoft.com/sqlcat/tag/availability-groups/

Specifically, 5 years ago, they documented doing Availability Groups in Azure VMs. This is far simpler nowadays. https://blogs.msdn.microsoft.com/sqlcat/2014/09/22/extending-alwayson-availability-group-to-remote-azure-datacenter-powershell/

From SQL 2005 through SQL 2008R2, “Highly Available with Disaster Recovery” would have been Failover Instance clusters, with a secondary copy delivered via Database Mirroring.

This architecture was used by a company known as the NASDAQ stock exchange, and was a case study back then. http://download.microsoft.com/documents/customerevidence/Files/49271/NASDAQ_SQLServer_Word.doc

AlwaysOn is a single-solution way to achieve even higher availability than with Failover Instances, with secondary copies in a different region. NASDAQ and others adopted AlwaysOn Availability Groups in place of old combinations of failover instances and mirroring or log shipping.

I will quote the document, and then the sections below will dive into detail. Note, the below is for SQL 2012. 2014 increased number of secondaries, and 2016 improved number of synchronous copies, as well as throughput. The best solution is on top, and goes down from there in terms of RPO and RTO.

Contrasting RPO and RTO Capabilities

The business goals for Recovery Point Objective (RPO) and Recovery Time Objective (RTO) should be key drivers in selecting a SQL Server technology for your high availability and disaster recovery solution.

This table offers a rough comparison of the type of results that those different solutions may achieve:

High Availability and Disaster Recovery SQL Server Solution	Potential Data Loss (RPO)	Potential Recovery Time (RTO)	Automatic Failover	Readable Secondaries(1)
AlwaysOn Availability Group – synchronous-commit	Zero	Seconds	Yes(4)	0 – 2 (0-3 in 2016)
AlwaysOn Availability Group – asynchronous-commit	Seconds	Minutes	No	0 – 4 (0-8 in 2014)
Failover Cluster Instance	NA(5)	Seconds-to-minutes	Yes	NA
Database Mirroring(2) – High-safety (sync + witness)	Zero	Seconds	Yes	NA
Database Mirroring(2) – High-performance (async)	Seconds(6)	Minutes(6)	No	NA
Log Shipping	Minutes(6)	Minutes -to-hours(6)	No	Not during a restore
Backup, Copy, Restore(3)	Hours(6)	Hours -to-days(6)	No	Not during a restore

(1) An AlwaysOn Availability Group can have no more than a total of four secondary replicas (eight in 2014), regardless of type.

(2) This feature will be removed in a future version of Microsoft SQL Server. Use AlwaysOn Availability Groups instead.

(3) Backup, Copy, Restore is appropriate for disaster recovery, but not for high availability.

(4) Automatic failover of an availability group is not supported to or from a failover cluster instance.

(5) The FCI itself doesn’t provide data protection; data loss is dependent upon the storage system implementation.

(6) Highly dependent upon the workload, data volume, and failover procedures.

Solutions on Azure with Virtual Machines for SQL Server.

Best Practice option: Always On Availability Groups with SQL Enterprise. Backups should also be done and quickly available.

The edition of SQL Server matters. Enterprise is the top tier, and using its features such as Always-On Availability groups are recommended for 24×7 operation. https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2017?view=sql-server-2017

Standard edition, from the link above, shows up as “Basic data management …. For departments and small organizations”. Under high availability from the same link,

“Always On Availability Groups is not available in Standard Edition.”

This automatically makes it a lower uptime. Failover instances were great technology 20 years ago, but they were surpassed by database mirroring for faster failover back in 2005. Today, it’s more of a legacy compatibility feature.

Basic availability groups is basically Database Mirroring, but modernized for SQL 2016.

All up Business continuity and disaster recovery for SQL Server is here:

https://docs.microsoft.com/en-us/sql/database-engine/sql-server-business-continuity-dr?view=sql-server-2017

When you read this, know that we want our customers and partners to be successful, for the application intended.

The modern way is via Always On Availability Groups, which permit going higher level on a secondary copy of data, testing on that secondary copy, and then failing over to the secondary.

Way lower in the article is the non-cluster way:

“Log shipping

If recovery point and recovery time objectives are more flexible, or databases are not considered to be highly mission critical, log shipping is another proven availability feature in SQL Server. Based on SQL Server’s native backups, the process for log shipping automatically generates transaction log backups, copies them to one or more instances known as a warm standby, and automatically applies the transaction log backups to that standby. Log shipping uses SQL Server Agent jobs to automate the process of backing up, copying, and applying the transaction log backups.”

Since words matter, and clarity is key: Mission Critical means using Always On Availability Groups for the best choice.

SLAs are important. We document them clearly:

https://azure.microsoft.com/en-us/support/legal/sla/virtual-machines/v1_8/

• For all Virtual Machines that have two or more instances deployed across two or more Availability Zones in the same Azure region, we guarantee you will have Virtual Machine Connectivity to at least one instance at least 99.99% of the time.
• For all Virtual Machines that have two or more instances deployed in the same Availability Set, we guarantee you will have Virtual Machine Connectivity to at least one instance at least 99.95% of the time.
• For any Single Instance Virtual Machine using premium storage for all Operating System Disks and Data Disks, we guarantee you will have Virtual Machine Connectivity of at least 99.9%.

“Availability Set” refers to two or more Virtual Machines deployed across different Fault Domains to avoid a single point of failure.

To get the best availability, the Always On Availability Group cluster would have to be in an “Availability Set”, which ensures our back end maintenance does not take all of those machines down at the same time.

Also, in reality, monthly patching will require downtime unless you are protected by Always On Availability Groups (Or, at the least, automatic failover by a failover instance).

Therefore, outside of patching and other maintenance, you can expect no better than three 9’s (99.9%) virtual machine connectivity. It might be better, but it is not guaranteed.

The differences between a failover instance and availability groups are laid out here:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/failover-clustering-and-always-on-availability-groups-sql-server?view=sql-server-2017

If you prefer video information, David Pless, of Microsoft, my coworker for 13 years, and great presenter, presented AlwaysOn Availability groups to the PASS (Professional Association of SQL Server) High Availability Disaster Recovery virtual chapter.

https://www.youtube.com/watch?v=UfloWwsyXLM

Finally, for SQL Server on Azure VMs, here is a performance guide, so you can plan or adjust to a well-performing setup. https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-performance

Customer Evidence:

GE Healthcare lifted-and-shifted to the Azure Cloud, and leveraged VMs with .NET and SQL Server in Always On Availability Groups in Virtual Machines.

GE Healthcare and Microsoft kicked off a proof of concept for transitioning apps that were running in a production environment on-premises to Microsoft Azure. The transition was seamless using Microsoft Azure Virtual Machines (VMs). The VMs host multiple application components, including database instances running on SQL Server software and business logic running in Microsoft .NET Framework assemblies. Both SQL Server and .NET on Microsoft Azure VMs provide full compatibility with on-premises infrastructure. In addition, the SQL Server AlwaysOn feature ensures high availability.

https://customers.microsoft.com/en-us/story/731798-ge-healthcare-delivers-core-customer-solutions-on-the

Here is a Retail example customer case study, leveraging SQL Server and Always On Availability Groups on Azure:

https://customers.microsoft.com/en-us/story/vapiano

Outside of failover being automatic: ENTERPRISE edition can keep databases up and running and maintained longer. I collected data from many places to put this “Do you need Enterprise again” after the updates post SP1 of SQL 2016 towards SQL Standard. More features have come to enterprise edition since, for 2017 and soon to come 2019.

https://georgewalters.wordpress.com/2016/12/22/sql-2016-sp1-do-you-ever-need-sql-enterprise-again-tldr-yes/

Mid-Tier option:

Leveraging either Basic Availability Groups or Failover Instances with SQL Standard, would be cheaper, but has significant performance scale limits, and risks around uptime.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/basic-availability-groups-always-on-availability-groups?view=sql-server-2017

Not recommended (Low-tier option):

What about using a single instance of SQL Standard?

That’s pretty much not covered in any talk above about Enterprise, Availability, or Scale.

I would warn strongly against “saving money” by being exposed by a relatively low-availability configuration such as a single virtual machine hosting SQL Server databases.

Upgrades and patching in this scenario has vastly increased risk, as rollback basically includes a reinstall and exporting and importing data. If this matches your needs, that is fine then. Be aware of this when planning your upgrades and downtimes.

Support options

With higher needs, you need better support. Here are the options. Note, Production is basic for most production needs, and then Professional or Premier support is recommended.

https://azure.microsoft.com/en-us/support/options/

https://azure.microsoft.com/en-us/support/plans/

 

Future Strategy

Someday, you might plan to migrate and update code, and get to a world of cloud-native configurations.

I have helped major companies migrate thousands of databases to Azure SQL Database. It took some code rework, but their investment in that led to equal cost as on-prem, but without the following headaches:

• Patching
• Scaling
• Deployment issues

Granted, it took time for them and others to do that all. In the end, you can read about Paychex, a time-tracking company, who did that here:

https://www.businessinsider.com/paychex-microsoft-azure-sql-database-2019-5

The SLAs for this, where you DO NOT have to patch, and you get backups included for free, and easy multi-region deployment as needed is here:

https://azure.microsoft.com/en-us/support/legal/sla/sql-database/v1_2/

• Azure SQL Database Business Critical or Premium tiers configured as Zone Redundant Deployments have an availability guarantee of at least 99.995%.
• Azure SQL Database Business Critical or Premium tiers not configured for Zone Redundant Deployments, General Purpose, Standard, or Basic tiers, or Hyperscale tier with two or more replicas have an availability guarantee of at least 99.99%.
• Azure SQL Database Hyperscale tier with one replica has an availability guarantee of at least 99.95% and 99.9% for zero replicas.

Advertisements

Modern Migration Tour: SQL 2019 upgrade event in Burlington MA

On Saturday June 8th, in partnership with the SQL Tiger team, Boston Business Intelligence and New England SQL User Group leaders delivered a PASS and Microsoft and Intel Modern Migration event, covering SQL 2019 and Intel hardware, in Microsoft’s Burlington, MA office.

The morning was two sessions in lecture format. The afternoon was hands-on labs.

Without a doubt, having 49 people show up on a beautiful, warm, sunny Saturday, when it rained for 6 months, is amazing.

MORE AMAZING: Everyone stayed for the afternoon hands-on labs, where they used free tools Microsoft provides, to diagnose upgrade issues from SQL 2008 to 2017, and teamed up to help each other.

Registrants were told to set up their laptops in advance. The following tools were used:

1. SQL Express 2008R2 edition with advanced services.
2. SQL 2017 developer edition
3. SQL Server Management Studio version 18.0
4. Data Migration Assistant v4.3
5. Database Experimentation Assistant 2.6

Lab details are handed out at the event. Essentially, a SQL 2008 database that had upgrade issues was installed on the SQL 2008R2 instance. The migration assistant was used to find static code issues. The Experimentation assistant is used to help performance-test after resolving upgrade issues.

These real-world migration scenarios, walked through in this lab, up-skill Database professionals to handle these situations as they arise. Years ago this was done through hard manual labor and custom scripts. Now, free tools from Microsoft can ease this upgrade.

On the Intel hardware front, there are amazing new technologies that help reduce the number of cores needed for a particular workload. As a long-time professional in this space, the hardware innovation coupled tightly with Microsoft’s software is an amazing combination.

Feedback for this event was overwhelmingly positive, with long-time user group folks saying it was the best event ever.

From left to right: Paresh, Mitesh, Taiob, George (myself), Elizabeth, and Vladimir. Thank you for helping organize, and for delivering this content.

SQL 2019 Migration Tour speakers

Finally, we thank PASS, who Microsoft helped start 20 years ago to help spread the message of the Microsoft data platform. They, in conjunction with our SQL Tiger team, put together the materials and training for us trainers in this event.

For those interested, the New England SQL Server user group has around 3000 members, and the Boston BI user group has around 2500 members. Please join us via Meetup:

Link to New England SQL User group posting: https://www.meetup.com/NESQLUG/events/261451694/

Link to Boston BI User group:
https://www.meetup.com/Boston_BI/

SQL Server Tiger team blog:
https://techcommunity.microsoft.com/t5/SQL-Server/bg-p/SQLServer/label-name/SQLServerTiger

 

Microsoft Office and click-to-run technology

Microsoft has had a delivery mechanism called “MSI” which is the windows installer, for installing and updating the components of Microsoft Office for ages. This link has more on MSI.

Starting around Office 2010, we incorporated “Click to run” as an option for updating office.

Office 2019 is EXCLUSIVELY Click to Run.

Office 365 has been delivered by click-to-run primarily since 2013. We still have standalone office, but more and more industry customers are migrating to the cloud, and therefore deploying Office 365.

To get a sense of Office 365, you need to know Microsoft desires to do a more-continuous nonbreaking update model. This article can help explain it

To use a software management solution with Click to Run, check this article out. There is a COM interface you can use!

I hope this helps software vendors who integrate with Office!

What are Azure Hybrid Use Benefits and License Mobility for SQL Server and Windows?

DISCLAIMER: THIS IS NOT A LEGAL DOCUMENT FOR MICROSOFT LICENSING. CONTACT YOUR LICENSING SPECIALIST OR RESELLER OR MICROSOFT DIRECTLY.

 

Since 2012, Microsoft has sold Enterprise SQL Server licenses per core, 4 core minimum (The old processors became 4 cores per processor equivalent) and Standard edition SQL Server licenses either per-core, or per-server with CALs (Client access licenses). No client access licenses are required if your server runs on the per-core licensing model. Windows Datacenter was sold in processors, but as of 2016 is sold by the core, 8 cores per old processor.

​

If you wanted to use virtualization (on-premises or cloud), and also get updated software, and cold disaster recovery benefits, you needed software assurance on your cores. (or standard servers, but that doesn’t matter for the next part).

If you covered all physical cores in your environment with both Windows Datacenter and SQL Server Enterprise licenses, you could spin up as many VMs as you would like, until the hardware got overloaded. This is where fast processors and lots of RAM could benefit you. This is called “Unlimited Virtualization”

​

Microsoft sold licensing on-premises through Enterprise Agreements or through resellers. Big enterprises in virtual environments (VMWare etc) had to maintain software assurance. This is a good deal so far. This vehicle for purchasing licenses allows for greater discounts with volume, as well as 3 year payment plans on enterprise agreements.  More recently, there are available subscription licenses as well, for shorter-term usage.

In general, in Azure, if you are running virtual machines or databases full blast for three years straight, purchasing the licenses and software assurance will be cheaper than paying per-minute billing for Windows and SQL Server.

​

For Azure Hybrid Use Benefits… You can BRING your on-premises Windows and SQL cores to Azure.  We give a little grace period, but basically shut down those 8 cores on-prem, and bring them to Azure… either in a VM with 8 cores, or now, Azure SQL Managed Instance with 8 cores, or Azure SQL Database with the vCore model. Again, core licenses of SQL Server and Windows have to be purchased separately from Azure.

If you started with an Azure VM deployed with a pay-per-minute billing model, you can change it to BYOL (Bring your own license).

If you are purchasing Azure through a Cloud Solutions Provider (CSP): Think of the CSP as the Azure delivery mechanism (they can sell Dynamics and Office 365 as well), and licensed cores of SQL Server or Windows can land on top of the infrastructure you spin up there. If the CSP is also a license reseller, then they can sell you those cores of Windows and SQL Server.

​

Here is a scenario. Let’s say you leverage a CSP who sells Azure. You wish to build a solution which is then deployed and sold to your customers. They wish to have you sell them the Azure. The end customer goes into the azure subscription, and picks a vm, and says “YEAH I HAVE MY OWN CORES AND Software assurance”. The CSP has to ALSO become a reseller for SQL Server and Windows licenses, or you, the end customer, has to purchase those SQL and Windows licenses through a reseller.

If you deploy your own virtual machines onto Azure, there is no Azure-based tracking of licenses. You will need to ensure you have inventory of appropriate licenses to be compliant for those purposes. Software asset management has many paths to help you. Here is a tool that can scan your on-premises and Azure environment for this information.

I hope this helps clear up some confusion around Azure and Windows and SQL Server licensing!

Azure SQL Database, Managed Instance!

Recently announced was general availability of Azure SQL Database Managed Instance.  I am excited by how much easier it is nowadays to reduce your workload by migrating to Azure SQL Database, and lifting and shifting to Managed Instance.

What is this flavor of Azure and SQL Server?

Let’s step back.

Imagine a world where you have an application talking to SQL Server. It’s happy, you’re happy. Well, except for one thing: You hate patching.

Just ping me if you really like patching. That’s almost never true.

Also, what about upgrades? That also is downtime. High Availability? Can be a bit of effort. What other things could you do with your time if you didn’t have to patch?

To help solve these issues, Microsoft offers a variety of options to help you migrate to Azure, and to reduce your workload.

Our Platform as a Service (PaaS) offerings, like Azure SQL Database, are self-managing for the most part, do backups themselves, and can even auto tune!

Some legacy applications however, require too much rework to go to Azure SQL DB. So, what can we do?

Virtual Machines with SQL Server on Azure work, but require… you guessed it, PATCHING. Yes, we have tooling to help there. Additionally, HA options in SQL Server in VMs requires a bit of setup.

Azure SQL Database Managed Instance has most of the features you need from SQL Server, such as multiple databases on a server, SQL Server agent, .NET CLR for extended stored procedures, and a few other things.

This makes lifting-and-shifting with minor adjustments to Azure relatively simple. Here is a guide on how to migrate your database to Azure SQL DB Managed Instance.

To configure a Managed Instance, you will need to understand vnets in Azure. Also, you will have to configure the vnet to accept other connections, or set up a VPN into the vnet.  This article has topics that span this area and others in the form of how-to guides.

Once you get the Managed Instance up and running, you will need to connect your application. This guide will help you, whether the application is in Azure, or crosses on-premises.

New Note: Public endpoints are now available, via a powershell script.

If you wish to do a little more refactoring, migrating your application to use Azure SQL Database can be more efficient. You will need to decide that trade-off. Here are some articles on how to use a single database in Azure SQL Database service.

Here is a presentation I made to the New England SQL users group on Managed Instance.

Feel free to reach out to me for more information.

 

The privilege of teaching others

I want to add to this article, the following material from Bob Ward, around SQL Server training materials. These materials are free! https://microsoft.github.io/sqlworkshops/

George Walters

This past weekend, I realized was blessed by quite a few things:

1) An understanding wife, who lets me go and train people in other cities while dealing with our children.
2) The SQL Saturday program, where I have met wonderful people, and started volunteering my technical talents.
3) An awesome employer, who does well by us, and by the greater community.
4) Being a mentor for many people.

I was fortunate enough to designate SQL Saturday DC as the place where Microsoft would sponsor an after-event for a Cloud meetup centered around Diversity, Inclusion, and what career paths might be out there for people.
We had about 30 people attend, and it was a nice mix of people.

We discussed learning paths for people to “get into” the cloud, which I believe will help almost any IT pro or developer out there.

The list of sites to get training…

View original post 217 more words

How I Work – Using OneNote For My Daily Task List

I love OneNote. I also appreciate hearing how others benefit from using it to organizing documentation, customer meetings, etc.

Caffeinated Data Science

It’s not overstating
things to say that I use Microsoft OneNote
in every part of my life. I use it to create and track projects at work;
categorize, list and annotate the technologies I work with; share
information with my family (from groceries to the punch-down list my wife
uses when she flips houses, to wedding planning with my daughter); I mean every
part of my life. I’m writing this blog post in OneNote, in fact.

To be fair, I’ve always been a note-taker. I taught myself note-taking when I was young, and then was formally taught to do it better. I started with those grey-speckled notepads you buy for school, graduated to a Day-Runner, and then on to ACT! and other Personal Information Managers (PIMS) when those came out on the early computers. I’ve used Outlook Tasks, Microsoft Project, Getting Things Done (GTD)…

View original post 1,342 more words