fbpx

DallasDBAs.com

Explaining SQL Server in plain english

Kevin3NF

IRL #7: Test Restores and CheckDB

by 1 Comment

My Pluralsight course for new SQL Server DBAs

Problem:

My client asked me for a way to automate test restores and CheckDB either ad hoc or on a schedule, but not for certain databases that are close to or over 1TB in size.

Background:

My client has a number of SQL Server instances across multiple versions from 2008R2 up to 2016, Standard and Enterprise edition. We recently standardized all of their maintenance (backups, indexes, stats and checkdb) across the board using the Maintenance Solution from Ola Hallengren.

Investigation:

Digging around a bit revealed several choices:

  • Roll my own T-SQL ($$$)
  • Use a 3rd party product ($$)
  • Use DBATools.io (Free)

I was given the OK to spend up to 4 hours for a Proof of Concept on the DBATools option. DBATools is a set of commands that are all built using PowerShell. You don’t need to know PowerShell to use them.

Process:

I have a lab with SQL Server 2016, 2017 and 2019 installed (all Developer edition). Each instance has a few test databases, including WideWorldImporters, StackOverflow and some other local ones used just to test specific ideas.

I am using PSVersion 5.1.x, with the DBATools module installed.

The core command I am using (out of well over 550 available) is Test-DbaLastBackup. I need to start with getting one instance to restore its databases into another, run CheckDB and then drop the DB.

Test-DbaLastBackup -Destination kbh-precision\SQL2019 -SqlInstance  KBH-Precision\SQL2017 `
                   -IgnoreLogBackup -ExcludeDatabase master,model,msdb, StackOverflow

This allowed me to do the minimum, skipping System databases and StackOverflow (due to its size) and work from just the most recent Full backup plus any Differentials. I only have one database that met the criteria, and I have intentionally corrupted it using DBCC WRITEPAGE. DONT DO THIS UNLESS YOU KNOW WHAT YOU ARE DOING!!!

Results:

SourceServer   : KBH-Precision\SQL2017
TestServer     : kbh-precision\SQL2019
Database       : XE_demo
FileExists     : True
Size           : 3.20 MB
RestoreResult  : Success
DbccResult     : Table error: Object ID 565577053, index ID 0, partition ID 72057594041270272, alloc unit ID 72057594046906368 (type In-row data), page (1:560). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. 
                 Values are 133129 and -4.
                 Object ID 565577053, index ID 0, partition ID 72057594041270272, alloc unit ID 72057594046906368 (type In-row data): Page (1:560) could not be processed.  See other errors for details.
                 DBCC results for 'RndNums'.
                 There are 0 rows in 0 pages for object "RndNums".
                 CHECKDB found 0 allocation errors and 2 consistency errors in table 'RndNums' (object ID 565577053).
                 DBCC results for 'sys.plan_persist_query_hints'.
                 There are 0 rows in 0 pages for object "sys.plan_persist_query_hints".
                 DBCC results for 'sys.plan_persist_query_template_parameterization'.
                 There are 0 rows in 0 pages for object "sys.plan_persist_query_template_parameterization".
                 DBCC results for 'sys.plan_persist_wait_stats'.
                 There are 0 rows in 0 pages for object "sys.plan_persist_wait_stats".
                 DBCC results for 'sys.persistent_version_store'.
                 There are 0 rows in 0 pages for object "sys.persistent_version_store".
                 DBCC results for 'sys.persistent_version_store_long_term'.
                 There are 0 rows in 0 pages for object "sys.persistent_version_store_long_term".
                 DBCC results for 'sys.wpr_bucket_table'.
                 There are 0 rows in 0 pages for object "sys.wpr_bucket_table".
                 DBCC results for 'sys.queue_messages_1977058079'.
                 There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
                 DBCC results for 'sys.queue_messages_2009058193'.
                 There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
                 DBCC results for 'sys.queue_messages_2041058307'.
                 There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
                 DBCC results for 'sys.filestream_tombstone_2073058421'.
                 There are 0 rows in 0 pages for object "sys.filestream_tombstone_2073058421".
                 DBCC results for 'sys.syscommittab'.
                 There are 0 rows in 0 pages for object "sys.syscommittab".
                 DBCC results for 'sys.filetable_updates_2105058535'.
                 There are 0 rows in 0 pages for object "sys.filetable_updates_2105058535".
                 CHECKDB found 0 allocation errors and 2 consistency errors in database 'dbatools-testrestore-XE_demo'.
                 repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (dbatools-testrestore-XE_demo).
                 DBCC execution completed. If DBCC printed error messages, contact your system administrator.
RestoreStart   : 2020-04-22 07:36:09.391
RestoreEnd     : 2020-04-22 07:36:10.706
RestoreElapsed : 00:00:01
DbccStart      : 2020-04-22 07:36:10.772
DbccEnd        : 2020-04-22 07:36:12.323
DbccElapsed    : 00:00:01
BackupDates    : {04/16/2020 10:19:19}
BackupFiles    : D:\Backups\KBH-Precision_SQL2017\XE_Demo_20200416.bak

PS C:\WINDOWS\system32>

From there I tested various methods to get the output into something I can look at or, alert on, etc.  Not knowing how deep my client wanted to go at the time, I settled on this:

#Import-Module dbatools

Test-DbaLastBackup -Destination kbh-precision\SQL2019 -SqlInstance  KBH-Precision\SQL2017, KBH-Precision\SQL2016 `
                   -IgnoreLogBackup -ExcludeDatabase master,model,msdb, StackOverflow | `
                    Select-Object -Property SourceServer,Database,RestoreResult,DBCCResult | `
                    Write-SQLTableData -ServerInstance "kbh-precision\SQL2019" -DatabaseName "DBA" -SchemaName "dbo" -Tablename "Test_DBALastBackup" -Force

The last step was to get this into a SQL Agent job (or jobs, depending on the flexibility needed).

I assumed a SQL Agent PowerShell job step would be the best, but my friend Andy Levy(b|t) suggested using this approach.

Results:

With these 4 lines of code and less than 3 hours (ignoring the permissions issues I had), I was able to create a flexible process that can be changed by a simple edit of the .ps1 file.

No doubt there are better ways to implement all of this, error handling, etc. but the ideas got my client what they needed.

“That’s exactly what we wanted”

“How well does it work on Availability Groups?” (Don’t know yet, will test that soon).

Conclusion:

  • DBATools.io has a ton of useful, free, opensource commands…look there before you roll your own T-SQL. Someone else may have already solved your problem.
  • You don’t have to know PowerShell to use these tools, any more than you have to know how fuel injectors work to drive a car.

Other posts in the IRL – In Real Life series:
https://dallasdbas.com/category/sql/irl/

 

The video version:

Thanks for reading!

Kevin3NF

SQL Server HADR overview

by 2 Comments

My Pluralsight course for new SQL Server DBAs

Image credit to Jeff (t)

Back in June of 2019, I published this YouTube video covering the highlights of the various SQL Server High Availability and Disaster Recovery options. But I didn’t do any of it in writing like I usually do in the companion piece…so here we go!

First, some definitions:

  • High Availability (HA)—typically means that the database will be back online in seconds or minutes, not hours or days
  • Disaster Recovery (DR)—the ability to bring the database server and databases online after something really bad happens
  • HADR—An umbrella term covering any feature that encompasses HA, DR or both.
  • RPO—Recovery Point Objective—Essentially governs what point we can recover to (or…how much data loss can we handle)
  • RTO—Recovery Time Objective—How long to get everything running again.

SQL Server comes with a number of features that cover different scenarios, in different ways.  Some are only in the Enterprise edition.

I am going to cover these at a very high level so you can quickly get the idea in your head, and refer back to this post as needed later. We will start with the most basic.

Backup and Restore

It sounds simple, but backing up the databases is the simplest, most cost effective choice in a DR solution.  BACKUP DATABASE is available in all editions of SQL Server. Express edition makes it hard to automate, because there is no SQL Agent functionality.  There are answers for that, such as Windows Task Scheduler and the Ola Hallengren Scripts.

Backups by themselves are great, but in the event of an actual disaster, the ability to RESTORE them is the critical part. You should be backing up and test restoring your databases regularly.

This is database level only.  Backup all user and system databases.

Backup and Restore is DR, not HA.

Log Shipping (LS)

Transaction Log Shipping at its core is just a Backup-Copy-Restore process with some bells and whistles added to it.  GUI for setup, alert jobs to let you know when it gets behind, etc. But the basic concept is that every 15 minutes a T-log backup is taken, then copied to another server, then restored there.  In LS, you can set up the Secondary server to be read-only between restores. You can have multiple Secondaries.

This is database level only, so Logins, jobs, etc. are not copied.

You need to be familiar with File shares, UNC paths, permissions settings, etc. to set LS up.

Log Shipping is DR only, not HA unless you write a bunch of scripts to detect an issue, catch it all up and repoint your applications to the new Primary.

Database Mirroring (DBM):

Database mirroring is a deprecated feature, but it still exists in some recent versions of SQL Server. Check the Microsoft documentation for your version. DBM is available in Standard and Enterprise editions. As the name implies, the Principal copy of the database is mirrored to the Mirror copy on a secondary server.

DBM works at the transaction level, unlike Log Shipping which uses T-log backups. DBM can be set to be either Synchronous or Asynchronous modes, and either Automatic or Manual failover. Not all combinations of these two exist.

This is Database level, so jobs, logins, etc. do not participate.

Since Automatic failover is available, I classify DBM as HA, and DR at the Database level.

Always On—a marketing term from MS that includes 2 features:

SQL Server Failover Cluster Instance (FCI)

This is a traditional Windows Cluster (WSFC) that has been around for ages, with one or more SQL Server Instances installed into it.

Failover is handled by the Windows Cluster service and is usually very quick. A few seconds, but exceptions exist, such as 10000 databases all starting up at the same time.

An over-simplified explanation—2 or more servers (nodes) are connected to a SAN. The databases exist on the SAN, so in a failover situation, they don’t move.

If the SQL Instance on Server A becomes unreachable, the cluster stops the SQL Service there and starts it on Server B.

The SAN is a single point of failure, unless it is being replicated via some non-SQL Server technology. For this reason, a SQL FCI (in my opinion) is not a full HADR solution…but definitely IS HA.

SQL Server Availability Groups (AGs):

AGs are an Enterprise only feature as of this writing.

AGs are essentially a much-improved version of Database Mirroring. Transaction level data movement from Primary to Secondary for one or more databases in a Group. Multiple groups are allowed. Synchronous or Asynchronous. Manual or Automatic failover. Readable secondaries to offload reporting queries.

Standard Edition has a “Basic Availability Group” which has lots of limitations, chief among these being one database per group.

When set up correctly, AGs are both HA and DR for the user databases, with no single point of failure. There are script options to keep the jobs, logins, etc. in sync between the Primary and all Secondary replicas.

A Windows Cluster is required, but a SAN is not. AGs work off local storage, not shared since there is a copy of each database on each server participating.

The licensing of AGs has changed a lot, so I won’t get into it here…but you probably already know that Enterprise Edition licenses are VERY expensive. Plan accordingly with your license vendor.

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.

Replication IS a great option to send a subset of your data to another server for reporting, or for filtering by region or salesperson.

Replication is for Distributed Data Processing. Backup and Restore beats Replication when you have to rebuild an environment, every time.

Summary:

I hope this helps you have a better understanding of the high level concepts of the various HA/DR options available to you. There are “gotchas” and details that are impossible to cover in this post. But, all of these are extensively well documented by Microsoft and the SQL Server Community at large.

Thanks for reading!

Kevin3NF