SQL Server DBA Jules joins us on the show to talk DBA work, Chickens, Charity work, Mentoring and Shepherding (actual sheep!)
See more at Jules’ site
Edit: Honorable mentions: Minionware, Jen and Sean McCown
syndicated
Data Bits – Episode 3 – Ginger Grant
Trainer and Consultant extraordinaire Ginger Grant stops by to talk Machine Learning, Data Bricks, Certifications, Norwegian pastries and proper chocolate frosting
Find Ginger at Desert Isle SQL or on Twitter
SQL Server HADR overview
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!
SQL Server Identity Skipping
My Pluralsight course for new SQL Server DBAs
SQL Server may skip 1000 numbers on an Identity column if the server crashes. Here’s why:
Too long, didn’t watch version:
SQL Caches 1000 numbers at a time to boost insert performance. In a crash and recovery, those numbers are gone.
SQL 2016 and earlier – use instance-wide trace flag 272 to turn off this behavior (performance might suffer).
SQL 2017 and later – its now a database scoped config item:
use MyDB; go ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF GO
Video shows a walk-through of before and after each fix, plus a “Two guys walk into a bar” joke when I disappeared to troubleshoot a broken demo…
Thanks for reading and/or watching!
My Pluralsight course for new DBAs
Dear Junior DBA…
Congratulations on getting your first SQL Server DBA job!
Presumably you have a tech background or education, and have been through some basic training in SQL Server administration. I also assume you intended to be a DBA and want to be really good at it so you can advance your career and get mad raises/bonuses.
With any luck at all, you are in an environment where there is at least one other DBA there that knows more than you do. Ideally a Senior that is really into mentoring that can guide your path.
If not, here are some of the basic things that you may already know how to do in SQL Server Management Studio, but don’t really know the inner workings or the T-SQL to make them happen.
Also, almost everything you can do in current SSMS versions can be scripted. Look for the script button and click it after you make all of your selections so you can start learning the code behind the GUI. In time you’ll prefer going straight to the Query Window for some functions of your job.
The list I want my juniors to get intimately familiar with:
- Backup and Restore…beyond the Maintenance Plans
- Creating/Deleting databases
- Creating Logins and Users (and knowing the difference)
- Creating and maintaining indexes
- Other database maintenance items
- Basics of whatever HA/DR may be in place (Clustering, Log Shipping, Availability Groups)
- Basic performance monitoring via SQL Trace or Extended Events in modern SQL versions
All of these can be setup/monitored in the GUI…so make sure you know all of the options there, and then start working on knowing them deeper. Start with Books Online/MSDN and go from there.
More on each list item:
Backup and Restore – I want you to be able to regurgitate exactly what the difference is between Full, Differential and Transaction Log backups. I want you to know when you would use each. You need to know how to restore to a point in time, to another server or as a new database name. Backwards and forwards…this is DBA 101 and the first question I ask if I interview you. You need to be able to throw down the basic Backup Database syntax on the fly. Also, recovery models…memorize and understand them (including Bulk-Logged)
Creating/Deleting databases – There are many ways to create a database…SSMS, T-SQL, Restore from a backup, deploy from a .dacpac/.bacpac, etc. Know how to do each, when you would use each, what options are available and how they affect behavior. Know about filegroups and best practices for laying out your .mdf, .ldf and .ndf files…for your environment. Know what to do before you delete a database. Does the requester mean Delete, Detach or just take offline? What’s the difference? Know your RPO and RTO by heart.
Creating Logins and Users (and knowing the difference) – I’m amazed at the number of experienced people and non-sql people that use the terms interchangeably. Know the difference. Also learn what server and database roles are. These days, the more you know about the various pieces of the security model in SQL Server the stronger a DBA you are.
Creating and maintaining indexes – If your databases are only a few GB, you can almost ignore indexes…but don’t. Know and be able to explain the difference between clustered and non-clustered indexes. Understand how to determine what indexes are needed and which existing ones are not. What is an included column? What is a covering index? Memorize the ins and outs of Reorganize vs Rebuild and Online vs. Offline maintenance…this matters. A surprising number of application developers will rely heavily on you for help in this area.
Other database maintenance items – DBCC CheckDB and its impact on tempdb. How to respond to CheckDB errors. Statistics…what are they, do they matter and how do you handle them? When?
Basics of whatever HA/DR may be in place (Clustering, Log Shipping, Availability Groups)
Know the difference between the various options available and what they are intended to do. High Availability, Disaster Recovery and Distributed Computing are NOT the same things. Understand that managers use Replication and Log Shipping interchangeably. Teach them gently. Your environment may or may not being doing any of this. Know your RPO and RTO by heart.
Basic performance monitoring via SQL Trace or Extended Events in modern SQL versions – any time you have a chance to get into a performance tuning issue jump on it. Don’t assume the issue is with SQL server just because someone said so. Prove it is or isn’t. Don’t automatically blame the storage or network teams unless you are never going to need them again (you will need them again…). Start at the server level, then drill down into the DB, then queries…jumping straight to queries may just be a waste of time if a rogue application is stealing all the CPU or leaking memory.
Yes, this is a lot of stuff. No, you won’t learn all of this today or next week. If you went from GUI to familiarity with all of this in 6 months I’d be impressed. Ask your mentors and teammates for help and guidance AFTER you do your research.
Also, there is a ton more you will pick up along the way. Ask questions. Go to SQL events if possible. Read blogs from Paul Randal, Brent Ozar and Grant Fritchey (and a bunch of others)
You can make a long career out of just database administration. But don’t deprive yourself of opportunities to learn about storage, virtualization, cloud computing, development, business intelligence, etc. The more you know, the more successful you can be. One of these days, you will be the mentor, not the mentee. Be kind to the new guy 😉
