DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
Building Scalable Real-Time Apps with AstraDB and Vaadin
Register Now

Trending

  • Top Six React Development Tools
  • Writing a Vector Database in a Week in Rust
  • Send Email Using Spring Boot (SMTP Integration)
  • Database Integration Tests With Spring Boot and Testcontainers

Trending

  • Top Six React Development Tools
  • Writing a Vector Database in a Week in Rust
  • Send Email Using Spring Boot (SMTP Integration)
  • Database Integration Tests With Spring Boot and Testcontainers
  1. DZone
  2. Data Engineering
  3. Databases
  4. Moving SQL Server 2008/2008 R2 Databases to the Azure Cloud

Moving SQL Server 2008/2008 R2 Databases to the Azure Cloud

Let's look at the considerations and challenges involved when migrating mission-critical SQL Server 2008/R2 databases to the Azure cloud.

David Bermingham user avatar by
David Bermingham
·
Sep. 06, 19 · News
Like (4)
Save
Tweet
Share
15.44K Views

Join the DZone community and get the full member experience.

Join For Free

Image title

It's time to move to the Azure Cloud.

Extended Support for SQL Server 2008 and 2008 R2 ended in July 2019, and Extended Support for Windows Server 2008 and 2008 R2 will end in January 2020. Upgrading the software to the latest versions is always an option, of course, but for a variety of reasons, that may not be viable or cost-effective for some legacy applications. Another option is to pay an additional fee to continue receiving Extended Security Update support for three more years. But for most organizations, the best option will be to get continued support for free by moving the databases to the Azure cloud.

This article highlights the considerations and challenges involved when migrating mission-critical SQL Server 2008/R2 databases to the Azure cloud and offers some useful suggestions to help avoid common pitfalls.

You may also like:  AWS vs. Azure vs. Google: Which Is the Best for Cloud Computing?

Running Legacy Software in a State-of-the-Art Cloud

The Azure cloud offers a full spectrum of services for running mission-critical applications, but only some of these support SQL Server 2008/R2. The most significant limitation derives from the dependency Failover Cluster Instances (FCIs) have on shared storage. Such storage in the form of a storage area network (SAN) or network-attached storage (NAS) is possible in enterprise data centers, but not in the Azure cloud where all storage is local.

Microsoft has addressed the lack of shared storage with two enhancements, but neither supports SQL Server 2008/R2. One is Storage Spaces Direct that debuted with the Datacenter Edition of Windows Server 2016 and SQL Server 2016. The other is SQL Server’s own Always On Availability Groups that became available in SQL Server 2012.

For legacy database applications that are not critical, Azure’s standard service level agreement (SLA) guarantees may be sufficient, eliminating the need to use FCIs to assure high availability (HA). For example, manual backup and recovery processes should provide adequate uptime for most batch database applications. It is important to note, however, that Azure’s money-back SLA guarantee does not assure uptime at the database or application level. In effect, the SLA guarantees only “dial tone” or that at least one instance will have external network connectivity.

Unlike batch applications, most online transaction processing applications have more stringent recovery point and recovery time objectives that will require implementing separate HA and/or disaster recovery (DR) provisions. Azure Site Recovery, Microsoft’s DR-as-a-Service (DRaaS) offering, replicates the entire active instance to a “warm” standby instance in another Azure Region, and it does support SQL Server 2008/R2.

The need for manual processes to detect and recover from a failure makes ASR suitable for those applications with a Recovery Point Objective (RPO) of a few minutes or more, and a Recovery Time Objective (RTO) of several minutes or more. ASR also has a limit of 10 Megabytes per second per disk of WAN bandwidth, which may be inadequate for some applications.

One final consideration is the operating system. When running SQL Server 2008/R2 on either Windows Server 2008 R2 or Windows Server 2012, Microsoft requires this hotfix to work with FCIs: https://support.microsoft.com/en-us/help/2854082/update-enables-sql-server-availability-group-listeners-on-windows-serv. Depending on the configuration, additional hotfixes may be needed. If possible, using Windows Server 2012 R2 or minimizes the need for hotfixes.

Failover Clustering for SQL Server 2008/R2 Databases

Protecting legacy SQL Server 2008/R2 databases with a stringent RTO of four-nine’s (99.99%) and/or an RPO of zero (no data loss) creates the need to use FCIs. And that, in turn, creates the need to use third-party failover clustering software to enable synchronous data replication — sans SANs — across multiple Azure Availability Zones.

Purpose-built failover clustering software facilitates real-time data replication and continuous monitoring capable of detecting failures at the database or application level. Most also integrate seamlessly with the Windows Server Failover Clustering (WSFC) feature used by FCIs and make it possible to configure different failover/failback policies for different applications.

One popular choice is to use third-party failover clustering software for HA and ASR for DR. With this cost-effective combination, data is replicated synchronously across multiple Azure Availability Zones via virtual volumes that appear as shared storage to the SQL Server FCIs. ASR adds DR protection by replicating the entire failover cluster (both the active and standby instances) asynchronously to another Azure Region in a Region Pair.

Another popular choice, shown in the diagram, is to use failover clustering software for both HA and DR purposes. This configuration is also cost-effective and affords the additional advantage of being able to protect virtually all application software, including all versions of SQL Server, in a single solution, making HA/DR configurations easier to implement, test, monitor, maintain and otherwise manage.

Image title

In this common configuration, Azure Region A hosts a two-node HA failover cluster spanning two Availability Zones, and Region B hosts a third instance to facilitate full recoveries from widespread disasters.

An existing SQL Server 2008/R2 failover cluster can easily be “Lifted and Shifted” from the premises to the Azure cloud. The effort involves simply replacing the shared disk resources with the SANless failover cluster’s virtual volumes, and substituting a file share witness for the disk witness. It is also necessary to configure the Azure Internal Load Balancer for client redirection, which requires running a PowerShell script on the local nodes to update the SQL Server cluster IP resource to listen for the ILB probe.

If no on-premises HA failover cluster exists to be lifted and shifted, one will need to be created, and most vendors of failover clustering software provide documentation detailing the steps involved. Here is an example of one such guide for Azure: Step-By-Step: How to Configure a SQL Server 2008 R2 Failover Cluster Instance on Windows Server 2008 R2 in Azure.

While there are some issues involved in migrating legacy SQL Server 2008/R2 applications to the Azure cloud, their “maturity” means that you should not need to navigate uncharted waters.

Further Reading

AWS and Azure Cloud, Head-to-Head

Better SQL Server CPU Defaults in 2019

azure sql Database Cloud clustering application

Opinions expressed by DZone contributors are their own.

Trending

  • Top Six React Development Tools
  • Writing a Vector Database in a Week in Rust
  • Send Email Using Spring Boot (SMTP Integration)
  • Database Integration Tests With Spring Boot and Testcontainers

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: