A Fine Slice Of SQL Server
Kevin Hill walks through different topics around high availability and disaster recovery:
Replication—a very Special Snowflake:
SQL SERVER REPLICATION IS NEITHER HA NOR DR.
Not everything in a SQL Server user database CAN be replicated, such as users, or tables with no Primary Key. New objects are not automatically sent from Publisher to Subscriber. System databases are not replicated.
There’s plenty of good information in here, so check it out.
Comments closedRandolph West explains that disaster recovery isn’t just for your servers:
I just completed a chapter for another book where I spoke about the Recovery Point Objective (how much data you are prepared to lose) and Recovery Time Objective (how long you have to bring your environment up again) after a disaster, and while I never get tired of repeating myself, that’s SQL Server. What happens if your development environment — or workstation — experiences a catastrophic failure?
Or what if, say, you’re on a cruise ship in the middle of the ocean with Internet access and a phone (but no laptop) and your on-call person just died? (I’ll leave this as an exercise for the reader to decide if this really happened.)
The answer is, if we do a careful bit of planning using the same disaster recovery principles we already know, the impact could be minimal. Note that this post assumes that you have Internet access and are using Microsoft Windows as your environment.
Click through for some useful suggestions.
Comments closedKevin Chant goes into the fairly recent licensing changes for SQL Server:
Which surprised me a bit because these licensing changes have been in-place for a while now. With this in mind, I thought I would discuss them here to raise awareness about the changes.
To clarify, in SQL Server 2019 there have been some big licensing changes about what you can and can’t do on a passive fail-over instance. Especially if you have Software Assurance.
Which I have to admit I am really excited about. Because it opens up some new possibilities which I will explain below. Of course, there are other significant updates in the licensing guide as well.
Read on for the details.
Comments closedMartin Surasky looks at what SQL Server 2016 changed with respect to High Availability options:
AlwaysOn Availability Groups, first introduced in SQL Server 2012 is a feature that is conceptually similar to database mirroring. I’m going to assume you already know what AlwaysOn Availability Groups are in general, their main purpose and how they are different (essential aspects at least) from other technologies to provide replication such as Database Mirroring.
In SQL Server 2014, the significant enhancement to availability groups was the increase in the number of supported secondary replicas from three to eight. SQL Server 2016 includes a number of new enhancements
The biggest thing about it, as I recall, was stability: I wouldn’t have recommended too many places go into production with Availability Groups in 2012, but by 2016, many of the biggest bugs were ironed out.
Comments closedRyan Adams wraps up a video series on setting up a SQL Server lab environment:
You are going to create a SQL Server Failover Cluster Instance in Part 4 of our series on how to build a SQL Cluster Lab. The FCI will only be installed on Node1 and Node2. FCIs require shared storage so you will make your domain controller an iSCSI target. Last you will create your FCI using the iSCSI drives you presented to the cluster.
Click through for links to the entire series.
Comments closedAllan Hirt looks at a couple announcements from Microsoft:
I’m going to discuss what I feel are the biggest game changers. I knew licensing was changing as I had conversations with Microsoft around this months ago. I was not sure what the final result was going to be, but I’m fairly pleased. Is it perfect? No, but it’s much better than it was.
You’ll definitely want to read Allan’s thoughts on Microsoft’s SQL Server licensing changes, as well as a private preview of Azure Shared Disks.
Comments closedI received an email from a client who is having issues with third-party applications connecting to their three-subnet SQL Server Availability Group. After an exchange with Microsoft Support, they discovered that the applications weren’t specifying MultiSubNetFailover = True in their connection strings. As a result, because RegisterAllProvidersIP = 1 in the cluster, connections were randomly experiencing high latency upon connecting, as client-side DNS queries over time had a 66% chance of returning the wrong IP from the listener.
They set RegisterAllProvidersIP = 0, but before you take that as advice keep reading.
Read on to learn why this is not a first-best solution.
Comments closedBefore we start to build a SQL Cluster Lab, let’s look at the desired result. You will build a 3-node cluster replicating an environment that has two data centers. As a result, the first two nodes will reside in data center 1 and the third node in data center 2. We are creating this architecture because it is the most common architecture I see for Availability Groups. It is multi-subnet and can solve for both HA and DR.
You will notice the domain controller in the middle. That piece is certainly not representative of a production environment. However, we are using it in our lab for several different functions and being a router is one of them.
Part 1 is the only part which is currently up, but this looks like it will be a good one. Go buy a couple more sticks of RAM for your PC and get reading.
Comments closedAndy Mallon explains the concept of Accelerated Database Recovery:
Accelerated Database Recovery(ADR) is a new feature intended to speed up the recovery process, which could be very slow, particularly when there are long-running, large transactions. ADR is not just for recovery after a crash, but also helps in other scenarios where the transaction log needs to be recovered–including Availability Group secondary redo and Failover Cluster Instance failovers.
This is one of the most interesting new features in SQL Server 2019.
Comments closedYeva Byzek walks us through a disaster recovery scenario when running Apache Kafka:
Imagine:
Disaster strikes—catastrophic hardware failure, software failure, power outage, denial of service attack or some other event causes one datacenter with an Apache Kafka® cluster to completely fail. Yet Kafka continues running in another datacenter, and it already has a copy of the data from the original datacenter, replicated to and from the same topic names. Client applications switch from the failed cluster to the running cluster and automatically resume data consumption in the new datacenter based on where it left off in the original datacenter. The business has minimized downtime and data loss resulting from the disaster, and continues to run its mission critical applications.
Ultimately, enabling the business to continue running is what disaster recovery planning is all about, as datacenter downtime and data loss can result in businesses losing revenue or entirely halting operations. To minimize the downtime and data loss resulting from a disaster, enterprises should create business continuity plans and disaster recovery strategies.
Distributed data sources can still succumb to disaster and many of the same policies that people learn when working with relational databases apply to things like Kafka as well.
Comments closed