17 High availability and disaster recovery
High availability and disaster recovery (HADR) are complicated and important topics when we’re talking about our databases. One of the core responsibilities of a production DBA is ensuring that databases are available so applications and business users can access them. SQL Server has several options we can implement to improve the resiliency and availability of our data, including the following:
Throughout this chapter, we’ll demonstrate how dbatools can help simplify working with each of these HADR solutions, making them easier to configure and monitor. First, let’s talk about log shipping.
17.1 Log shipping
Log shipping is the process of backing up the transaction log of the primary database and copying those backups to one, or many, secondary copies to keep them in sync. Since SQL Server 2000, this method has been one of the most simple and effective options DBAs have to implement HADR solutions.
In our experience, SQL Server Management Studio (SSMS) can fail during log shipping deployments—even simple deployments! In response, we created a set of commands to make this task much easier and, more important, reliable and robust. This section will demonstrate how to implement log shipping, using a single command.
17.1.1 Configuring log shipping with dbatools
dbatools has several functions to assist with setting up and monitoring log shipping within your environment. First, we’ll look at Invoke-DbaDbLogShipping, which is the function we’ll use to set up log shipping from a primary SQL Server instance to a secondary SQL Server instance.
When you run Get-Help Invoke-DbaDbLogShipping, you can see nearly 85 parameters! This may feel overwhelming, but don’t panic: the command offers a lot of parameters, but it also has a lot of the same defaults. Ultimately, only four parameters are required for you to successfully set up log shipping.
We must fulfill a couple of prerequisites before we can execute Invoke-DbaDbLogShipping, as detailed in the help documentation for the command. The schedule for log shipping is controlled by SQL Server Agent jobs on both the primary and secondary instances, which, by default, run as the SQL Server Agent service account. Each of these accounts needs read/write permissions to the backup destination (or copy destination folders). These permissions must be set manually before the jobs will run successfully.
Once we have the file permissions in place, we can set up log shipping for the AdventureWorks database. In the following listing, we show how to set this up with only four parameters. As mentioned previously, we have a lot of options for configuration, but in this example, we’ll just use the defaults for most of them.
Listing 17.1 Setting up log shipping with Invoke-DbaDbLogShipping
PS> $params = @{ SourceSqlInstance = "dbatoolslabsql2017" DestinationSqlInstance = "dbatoolslab" Database = "AdventureWorks" SharedPath= "\dbatoolslablogship" ① } PS> Invoke-DbaDbLogShipping @params The database AdventureWorks does not exist on instance dbatoolslab. Do you want to initialize it by generating a full backup? [Y] Yes [N] No [?] Help (default is "Yes"): y ② PrimaryInstance : dbatoolslabSQL2017 SecondaryInstance : dbatoolslab PrimaryDatabase : AdventureWorks SecondaryDatabase : AdventureWorks Result : Success ③ Comment :
① The shared path is used as the backup destination and the source for the copy jobs. If you are seeing issues, check the permissions on this share.
② In this example, we’re relying on dbatools to initialize the database on the secondary replica, so we get a prompt to confirm we can take a full backup.
③ The output from the command shows it was successful. If there are issues, dbatools will try to capture these to display in the comment.
Log shipping is now configured, and because we didn’t specify timings, the SQL Server Agent jobs are set up to both perform log backups on the primary and copy/ restore them to the secondaries every 15 minutes. In figure 17.1, you can see how the dbatools lab looks after running the code in listing 17.1. The secondary database is in a restoring state, and two jobs on the primary instance and three on the secondary control shipping the transaction logs and keeping our secondary in sync.
17.1.2 When log shipping goes bad: Gathering errors with dbatools
When setting up or monitoring log shipping, we could encounter errors. Perhaps the backup drive runs out of space so the backup fails, or perhaps a network issue means some of the pieces involved can’t communicate. Unfortunately, log shipping isn’t the easiest process to troubleshoot through SSMS. For example, when we look at the job history, sometimes it’s not obvious what has happened, much less what has gone wrong. Fortunately, we have a dbatools command to help us collect errors so that we can resolve log shipping issues quickly and get things back up and running.
In the next code listing, we can see what happens if we set up log shipping without first configuring the file-level permissions. Running Get-DbaDbLogShipError shows that we had access issues and even details the path that couldn’t be accessed.
Listing 17.2 Setting up log shipping with Invoke-DbaDbLogShipping
PS> Get-DbaDbLogShipError -SqlInstance dbatoolslabsql2017, dbatoolslab | Select-Object SqlInstance, LogTime, Message SqlInstance LogTime Message ----------- ------- ------- dbatoolslabSQL2017 10/10/2021 5:45:01 AM Could not delete old log backu... dbatoolslabSQL2017 10/10/2021 5:45:01 AM Access to the path '\dbatools...
17.1.3 Cutting over to a log shipped secondary database
We mentioned earlier that log shipping backs up the transaction log of the primary database and copies those backups to other servers. You may want to cut over to a secondary server for many reasons, such as a disaster occurring in your primary data center or even after completing a migration that involves log shipping. dbatools simplifies this failover with the command Invoke-DbaDbLogShipRecovery.
In the following code sample, Invoke-DbaDbLogShipRecovery is used to bring the secondary replica online. The command performs some checks before bringing the secondary database online and ensures that the last transaction log has been shipped across and restored to what will become the primary database.
Listing 17.3 Cutting over to the secondary replica
PS> $logShipSplat = @{ SqlInstance = "dbatoolslab" Database = "AdventureWorks" } PS> Invoke-DbaDbLogShipRecovery @logShipSplat ComputerName : dbatoolslab InstanceName : MSSQLSERVER SqlInstance : dbatoolslab Database : AdventureWorks RecoverResult : Success Comment :
One thing to note is that dbatools does not take the primary replica offline—in a disaster recovery situation, that database wouldn’t be accessible anyway, but it’s something to keep in mind if you are using log shipping in a migration scenario. However, you could always add a call to Set-DbaDbState to set the databases offline after you cut over.
17.2 Windows Server Failover Cluster (WSFC)
Windows Server Failover Cluster (WSFC) is a technology that can help increase the availability of our SQL Servers. Failover Clusters can serve as the base for either a Failover Cluster Instance (FCI) or an Always On availability group (AG). This section will highlight dbatools commands that help simplify WSFC monitoring without having to install the FailoverCluster module or use the Failover Cluster GUI manager. You can even run these commands easily against remote servers.
Installing more complex scenarios with dbatools dbatools can also help if you are looking to install SQL Server as a Failover Cluster Instance. More details are available in the GitHub discussion, “Using Install-DbaInstance to Install (and Even Uninstall) a SQL Server Failover Cluster Instance” (sqlps.io/installfci).
The commands in this section will help you understand the setup of your clusters and make managing them much easier. Quite often, you’ll be given a virtual cluster name but not the underlying servers, or vice versa. dbatools will help you to get the information you need easily.
As we’ve seen a few times, we can get a list of commands by using Get-Command. In the next listing, we pass in the pattern *wsfc* and specify that the commands should come from the dbatools module.
Listing 17.4 Viewing all the commands that help us manage WSFC
PS> Get-Command *wsfc* -Module dbatools CommandType Name Version Source ----------- ---- ------- ------ Function Get-DbaWsfcAvailableDisk 1.1.50 dbatools Function Get-DbaWsfcCluster 1.1.50 dbatools Function Get-DbaWsfcDisk 1.1.50 dbatools Function Get-DbaWsfcNetwork 1.1.50 dbatools Function Get-DbaWsfcNetworkInterface 1.1.50 dbatools Function Get-DbaWsfcNode 1.1.50 dbatools Function Get-DbaWsfcResource 1.1.50 dbatools Function Get-DbaWsfcResourceType 1.1.50 dbatools Function Get-DbaWsfcRole 1.1.50 dbatools Function Get-DbaWsfcSharedVolume 1.1.50 dbatools
We get a lot of commands returned, and they are all get commands, so they will collect information for us. We recommend you read the help for each of these and test them against your clusters so you can see all the valuable information that dbatools will help you to extract. In this chapter, we’ll focus on three of the most useful commands.
Note As the name suggests, these Windows Server Failover Cluster commands work only on Windows.
First up, let’s find the nodes that make up our cluster by using Get-DbaWsfcNode. The only parameter required for this command is -ComputerName, though you can also provide an alternative Windows credential with the -Credential parameter.
-ComputerName can be either the name of one of the nodes or the hostname of the virtual cluster. The results list all of the nodes of that cluster, as shown here.
Listing 17.5 Viewing the nodes that make up our WSFC
PS> Get-DbaWsfcNode -ComputerName sql1 ClusterName : clsdbatools ClusterFqdn : clsdbatools.dbatools.io Name : sql1 PrimaryOwnerName : PrimaryOwnerContact : Dedicated : NodeHighestVersion : 533888 NodeLowestVersion : 533888 ClusterName : clsdbatools ClusterFqdn : clsdbatools.dbatools.io Name : sql2 PrimaryOwnerName : PrimaryOwnerContact : Dedicated : NodeHighestVersion : 533888 NodeLowestVersion : 533888
In this example, two distinct nodes are returned. The next command we’ll run is Get-DbaWsfcRole, which tells us about the roles that exist within our cluster.
In the next code sample, we can see three roles are returned. The first is an availability group called dbatoolsag, and the other two are standard roles that are returned for all clusters.
Listing 17.6 Viewing the roles on our WSFC using an alternative credential
Get-DbaWsfcRole -ComputerName sql1 -Credential addba ClusterName : clsdbatools ClusterFqdn : clsdbatools.dbatools.io Name : dbatoolsag OwnerNode : sql1 State : ClusterName : clsdbatools ClusterFqdn : clsdbatools.dbatools.io Name : Available Storage OwnerNode : sql2 State : ClusterName : clsdbatools ClusterFqdn : clsdbatools.dbatools.io Name : Cluster Group OwnerNode : sql1 State :
Want to dive deeper and see the resources that make up that availability group role? You can do just that with the command Get-DbaWsfcResource. In the following listing, we can see the details and states for our file share witness and IP address as well as the availability group.
Listing 17.7 Viewing the resource details for our WSFC
PS> Get-DbaWsfcResource -ComputerName sql1 | Select-Object ClusterName, Name, State, Type, OwnerGroup, OwnerNode | Format-Table ClusterName Name State Type OwnerGroup OwnerNode ----------- ---- ----- ---- ---------- --------- clsdbatools dbatoolsag Online SQL Server Ava.. dbatoolsag sql1 clsdbatools Cluster IP Address Online IP Address Cluster Group sql1 clsdbatools Cluster Name Online Network Name Cluster Group sql1 clsdbatools File Share Witness Online File Share Wit.. Cluster Group sql1
17.3 Availability groups
Setting up and managing availability groups (AGs) is complex and requires great attention to detail. Fortunately, we’ve codified all of the manual, tedious steps required to set up and manage availability groups—not just in Windows, but in Docker and Linux as well. As we work through this section of the chapter, you’ll end up having two options to get a working availability group, and we’ll target the lab environment we created in chapter 3.
Availability groups are the newest and, by now, probably the most common HA solution for SQL Server. Using an availability group allows us to send the transactions in a database to another database, perhaps far away in another datacenter. This feature came out in SQL Server 2012 and has seen many improvements over the versions since then. Availability groups are also available in Standard Edition, although there are some restrictions compared to Enterprise Edition AGs. We have many options when configuring these, so we recommend a wander through the Microsoft documentation (sqlps.io/aoag) to refresh your memory and work through the optimal configuration for your own environment.
17.3.1 Creating an availability group with dbatools
Most AGs that we have seen in production so far have been based on WSFC, which means certain prerequisites are required before creating the availability group. We’re not going to go into the details for building that base cluster here, because there are a lot of design decisions you’ll want to make based on your environment and setup, but we’d recommend reading up on them at sqlps.io/fciaoag to learn all about your options.
Note If you don’t want to build your own WSFC, skip to the “Availability group based on containers” section to see how to get an availability group running in your lab environment without a cluster.
Availability group based on WSFC
If you have your WSFC up, you’ll want to install SQL Server on both nodes; look for a check box in the SQL Server Configuration Manager to Enable Always On availability groups, highlighted in figure 17.2.
dbatools offers a command to enable this setting as well. You can see in the next listing that we can enable that check box with the command Enable-DbaAgHadr. Note, like when enabling HA using the GUI, changing this setting using dbatools will also require a restart of your SQL engine service.
Listing 17.8 Enabling Always On availability groups with dbatools
Our WSFC and SQL Server instances are now ready to create an availability group. As mentioned previously, this process can be complicated, but with dbatools, it’s just one command: New-DbaAvailabilityGroup.
Let’s run Get-Help New-DbaAvailabilityGroup -ShowWindow and look at the description for the command shown in the next code snippet. It not only shows how to use -ShowWindow but also provides a solid outline of what happens when you run the command.
Listing 17.9 Description from Get-Help New-DbaAvailabilityGroup
DESCRIPTION Automates the creation of Availability Groups. * Checks prerequisites. * Creates Availability Group and adds primary replica. * Grants cluster permissions if necessary. * Adds secondary replica if supplied. * Adds databases if supplied. * Performs backup/restore if seeding mode is manual. * Database has to be in full recovery mode (so at least one backup has been taken) if seeding mode is automatic. * Adds listener to primary if supplied. * Joins secondaries to Availability Group. * Grants endpoint connect permissions to service accounts. * Grants CreateAnyDatabase permissions if seeding mode is automatic. * Returns Availability Group object from primary. NOTES: - If a backup/restore is performed, the backups will be left intact on ➥ the network share. - If you're using SQL Server on Linux and a fully qualified domain name ➥ is required, please use the FQDN to create a proper endpoint.
The examples in the help are also worth reviewing because this command offers a lot of options and parameters.
In the next listing, we are relying on a lot of the default values for the configuration settings. In this example, a two-node availability group will be created, and the AdventureWorks database will be added using the backup/restore method and a shared path. Note that prior to this step, we’ve set the proper read/write permissions for the SQL Server Service account on our \sql1ackup path.
Listing 17.10 Creating an availability group with dbatools
PS> $agSplat = @{ Primary = "sql1" ① Secondary = "sql2" ② Name = "agpoc01" ③ Database = "AdventureWorks" ④ ClusterType = "Wsfc" ⑤ SharedPath = "\sql1ackup" ⑥ } PS> New-DbaAvailabilityGroup @agsplat ComputerName : sql1 InstanceName : MSSQLSERVER SqlInstance : sql1 LocalReplicaRole : Primary AvailabilityGroup : agpoc01 PrimaryReplica : sql1 ClusterType : DtcSupportEnabled : AutomatedBackupPreference : Secondary AvailabilityReplicas : {sql1, sql2} AvailabilityDatabases : {AdventureWorks} AvailabilityGroupListeners : {}
① The SQL Server instance that will make up the primary replica of our AG.
② The SQL Server instance that will make up the secondary replica of our AG.
④ Database that will be added to the AG, once it’s created
⑤ Default cluster type is none; we’re specifying that it will be built on top of a WSFC.
⑥ Shared path that will be used to back up/restore the database as it’s added to the AG. The engine account for both instances will need access.
This is a pretty simple AG, but it’s created, complete with a database, in just one step. If you have the IP address and permissions into AD to create a computer object for a listener, you can also add that in this command. Check out the help and examples for the parameters needed for this scenario.
Availability group based on containers
If you don’t have a cluster ready to go, don’t fret—there is still an option for you to get an availability group up and running in your lab. If you remember back to chapter 3 when we discussed building a dbatools lab, we had an option to use Jupyter Notebooks to easily run SQL Server instances off of containers. Well, one of these notebooks is all about availability groups!
We recommend downloading the notebooks and following along with 00-CreateContainers and then 03AvailabilityGroups from here: dbatools.io/labnotdotnet. Once you get through these two, you should have something that looks like figure 17.3.
You can see we’ve ended up with two replicas and three databases involved in our AG. It’s a great setup for a simple lab environment, and we’ll use it going forward to review some other dbatools AG-related commands.
17.3.2 Explore existing availability groups
Once our availability groups have been created, dbatools has many options for getting information about them and managing them. First, let’s explore the available Get commands that allow us to retrieve all the information we might need.
Let’s look at Get-DbaAvailabilityGroup. This will get us some good overall information about our availability groups. The simplest way to run it is shown in the following listing. We’re just asking for all AGs on the primary instance. In our case, there is only one—but if there are multiples, you could use the -AvailabilityGroup parameter to specify a certain one.
Listing 17.11 Getting general information about availability groups
PS> Get-DbaAvailabilityGroup -SqlInstance $sql1 ComputerName : sqlsp0100 InstanceName : MSSQLSERVER SqlInstance : sqlsp0100 ① LocalReplicaRole : Primary ② AvailabilityGroup : ACME_01 ③ PrimaryReplica : sqlsp0100 ClusterType : None ④ DtcSupportEnabled : False AutomatedBackupPreference : Secondary AvailabilityReplicas : {sqlsp0100, sqlsp0200} ⑤ AvailabilityDatabases : {AdventureWorks, Northwind} ⑥ AvailabilityGroupListeners : {} ⑦
① The SqlInstance we specified when we called the function
② The role of the SqlInstance—in this case, we’ve connected to the primary.
④ Because we’re using containers, we’ve not used a cluster for this. If we were using a WSFC, it would list wsfc here.
⑤ List of the replicas involved in the AG
⑥ List of the databases involved in the AG
⑦ If we configured a listener for the AG, it would be listed here.
We can also get more information about the specific parts that make up our AG. We have three commands that all work very similarly. To get replica information, we’ll use Get-DbaAgReplica, as shown next. Again, we’re just passing in the SQL Server instance we’re interested in.
Listing 17.12 Getting information about the availability group replicas
PS> Get-DbaAgReplica -SqlInstance $sql1 | Select-Object SqlInstance, AvailabilityGroup, Name, Role, AvailabilityMode, FailoverMode SqlInstance : sqlsp0100 AvailabilityGroup : ACME_01 Name : sqlsp0100 Role : Primary ① AvailabilityMode : AsynchronousCommit ② FailoverMode : Manual ③ SqlInstance : sqlsp0100 AvailabilityGroup : ACME_01 Name : sqlsp0200 Role : Secondary ① AvailabilityMode : AsynchronousCommit ② FailoverMode : Manual ③
① The role of the AG; you can see the first instance is our primary, and we also have one secondary.
② The commit mode; this is how transactions are synchronized. We are currently in AsynchronousCommit mode, which means the AG won’t wait for the transaction to commit on the secondary before committing on the primary.
③ FailoverMode is set to manual; this allows us to control when a failover happens.
If we had a listener set up, we could view more information with Get-DbaAgListener. In our current setup, we will see no results.
17.3.3 Managing existing AGs
In the final section for this chapter, we’ll quickly cover some useful commands that dbatools offers to help us manage our AGs. The first will help us with controlling failovers. This is useful when there is planned maintenance or some activity where you need to fail the AG over from the primary node to a secondary node. You can manage this through SSMS, but it’s even easier with dbatools.
If you’re using a WSFC for our AG and operating in synchronous commit mode, you can invoke a failover as shown in the following listing. This will cause a failover only if your AG is in sync and there will be no data loss. In our container example, or if you’re in a disaster situation where you’ve lost the primary replica and are accepting that some data loss is okay to get back up and running, you can add the -Force parameter.
Listing 17.13 Failing over the AG to a secondary replica with no data loss
You can also use two commands for controlling the data synchronization of your availability groups: Suspend-DbaAgDbDataMovement to stop, and then Resume-DbaAgDbDataMovement to get it going again. This method can also be useful for patching or maintenance when you need to take one of your replicas offline. Note that when data movement is suspended, the transaction log on your primary database will grow because any transactions that haven’t been sent to the secondary will be saved until data movement is resumed again. We can suspend data movement, as shown in the following code snippet. Again, we are specifying the SQL Server instance and the availability group name.
Listing 17.14 Suspending data movement to the secondary replica
If we look at the AG dashboard again in SSMS, shown in figure 17.4, we can see errors and warnings that our databases aren’t in sync, and icons on the databases also show they are in a paused state.
Once the maintenance is complete, we can resume data movement again with a similar command. Depending on how many transactions have been stored to send to the secondary, it might take a little while for the AG dashboard to go back to green and display us in sync again.
Listing 17.15 Resuming data movement to the secondary replica
17.4 Hands-on lab
-
If you have a WSFC handy, run the
New-DbaAvailabilityGroupcommand to set up an availability group. -
If not, run through the Jupyter Notebooks to get an availability group set up and running on containers.
-
Once your AG is set up, explore it with
Get-DbaAgReplicaandGet-DbaAgDatabase. -
Suspend data movement, and then resume it with
Suspend-DbaAgDbDataMovementandResume-DbaAgDbDataMovement.