Backup design, restore testing, integrity checks, and corruption response.
292 associated posts117 primary posts
AH-HA! You didn't know there would be one of these, did you, hotshot? You've been reading along in the series, just smiling and nodding, pretending you're keeping up, but not really doing the homework. BUSTED! Put your books away and get your pencils out.
Backup & Recovery Questions:
Let's finish up Research Paper Week with something we're all going to need to read over the next year or two. I know, it says Azure SQL DB, but you boxed-product folks will be interested in this one too: Constant Time Recovery in Azure SQL DB by Panagiotis Antonopoulos, Peter Byrne, Wayne Chen, Cristian Diaconu, Raghavendra Thallam Kodandaramaih, Hanuma Kodavalla, Prashanth Purnananda, Adrian-Leonard Radu, Chaitanya Sreenivas Ravella, and Girish Mittur Venkataramanappa (2019).
You would think that when SQL Server writes your data to disk, it's totally safe.
Forever.
In our last post in this series, I talked about why you should be using Ola Hallengren's maintenance script for backups and sp_DatabaseRestore for faster restores. These two tools have implications for how you build, back up, recover, and fail over your SQL Server.
There's a saying in the cloud: treat your servers like cattle, not like pets.
When I first started out as a SQL Server DBA, I thought things were going well as long as the backup jobs ran successfully. I'd go into SQL Server Agent every now and then, make sure they were still running, and ... that was the end of it. I figured if disaster ever struck, I'd just do a restore. How hard could it be? I was missing the entire point.
Last week, I asked for your worst database horror stories. Hoowee, did you deliver. Make yourself a nice cup of cocoa, gather around the campfire, and listen up for these terrible tales. Illustrations courtesy of Swear Trek (not safe for work.) The mistake we've all made Russ did an update without a where clause, but…
During the setup wizard, SQL Server should ask for your email address. It should suggest that you use a distribution list for people on your team, so that someone gets the alerts when you're out on vacation. Then, it should send these common failure alerts to you rather than making you set them up manually.
Wouldn't it be nice if rollbacks just finished instantly? Wouldn't you love for startup times to be near-zero even when SQL Server crashed just as someone in the middle of a transaction? How much would you pay for all this? (Well, I'm a little afraid to ask that, since we don't know yet whether this is an Enterprise-only feature, but dang, I sure hope not.)
What's the first thing you remember struggling with on SQL Server? Your first memory of, "Dang, I just can't figure this out," and then you had a Eureka moment later when it was so much more clear?"
I asked folks on Twitter, and the answers were great, so I had to round 'em up here to share 'em with you:
You're responsible for managing and troubleshooting SQL Server.
Good news: you're not alone. The SQL Server community has banded together and built a bunch of cool stuff to help you do your job easier. In a one-hour session today for the PASS DBA Fundamentals online user group (free to join, of course), I showed some off.
This week, Brent, Erik, Tara, and Richie discuss whether you need to premake a destination database in order to run sp_databaserestore, restoring production statistics on dev servers to simulate productions behavior, dealing with duplicate SPNs, tools for detecting all SQL Servers in a production environment, SQL performance monitoring, dealing with very large indexes, and Brent's recent cruise.
Tipping the Kilter
I'm intentionally avoiding physical/hardware choices in this post, like how much hardware to buy, and scaling out vs. scaling up.
Hard Enough At this point, we've all probably got a database that's a terabyte or more in size. If you're anything like I was, you've got a ton of multi-terabyte databases, and the SAN admin has a special folder for your emails. When you've got a lot of large databases, normal maintenance is out the…
This week, Erik, Tara, and Richie discuss disaster recovery, Python vs C# for SQL Server, whether you should perform log backups on primary or secondary on AGs, why Postgres is so popular, using mount points for tempdb, attention events, database corruption, MERGE statements, restoring a table from a database, features they look forward to in SQL Server 2019, and how Santa is able to visit all those homes in one night...
This week, Erik and Richie discuss TSQL classes, scalar functions, index reorganize vs rebuild, daily image backups on a fringe SQL server on VMWare, number of databases per AG, best practices for ETL operations, and more!
Here’s the video on YouTube:
This week, Tara and Richie discuss deletes on heaps, Docker, using containers in production environment, advice for new DBA, downgrading server hardware, agent jobs, setting SQL Memory Min and Max to the same value, how to prove CPU pressure to management using the Power BI report using sp_Blitz, corruption, patching, backups, sargability, and more.
This week, Tara, Erik, and Richie discuss using Docker in dev environment, in-place upgrades, disaster recovery, the minimum amount of CPU and RAM for new SQL Server VMs, troubleshooting long-running SQL agent jobs, dbcc checkdb running longer than normal, and more.
Here’s the video on YouTube:
This week, Tara and Richie discuss offline file database and log backup solutions, VSS database backups, monitoring products, parameterization, page latch waits, SQL ConstantCare™, and Availability Groups on 2017 Standard Edition.
Here’s the video on YouTube:
This week, Tara and Richie discuss database corruption, CLR, DBCC issues, adding identity columns to databases, distributed transactions, tracking how recently a procedure was executed, maintenance plans, recompiling stored procs, parameter sniffing issues, troubleshooting replication with Always On, forecasting database growth and estimating capacity issues, tools for database growth forecast reports, RCSI, and much more.
This week, Brent, Tara, Erik, and Richie discuss moving views between databases, Cluster Quorum File Share on AlwaysOn, checking for corruption, rebooting/restarting SQL server after applying cumulative updates, how many drive letter to use for a VM, decimal precision and scale value settings on SQL Server 2016 vs 2008, and nested views.
Here’s the video on YouTube:
