Backup and Recovery

Breaking News: Using TDE on 2016? Doing Backups? Time to Patch.

Normally, when you use Transparent Data Encryption (TDE) to secure your database at rest, you can’t get backup compression. Encrypted data basically looks like random data, and random data doesn’t compress well. SQL Server 2016 introduced the ability to compress your TDE databases. Yay! Unfortunately, it has bugs that can leave you unable to restore…
Read More

An Expensive Reason To Avoid AGs In Azure

Cash Rules Most people, when they get through paying for Azure, and SQL Server Enterprise Licensing, are left with a hole in their wallet that could only be filled with something that says “Bugatti”, and has a speedometer with an infinity sign at the end. Recently, while working with a client, I found out that…
Read More

sp_AllNightLog: Creating Jobs Like I’m The President

Look, we need these things The setup for sp_AllNightLog creates jobs for four separate activities 1 job to poll for new databases to back up (primary) 1 job to poll for new databases to restore (secondary) 10 jobs to poll for backups to take (primary) 10 jobs to poll for restores to… whatever (secondary) And,…
Read More

sp_AllNightLog: ¿Por que los queues?

Building stuff I sometimes really hate coming up with ideas, and much prefer someone to just say “I want this” so I can go out into the world and figure out how to do that. Occasionally though, I realize that I’m not going to get all that much direction. That’s what happened with sp_BlitzQueryStore. It…
Read More
Erik Darling blogs kCura Relativity

Introducing sp_AllNightLog: Log Shipping at Scale, Open Source

In our Faux PaaS project, we need a backup plan – or rather, a restore plan. On each SQL Server instance, clients can create as many databases as they want, anytime they want, with no human intervention. We need those databases covered by disaster recovery as quickly as practical. SQL Server’s newer disaster recovery options – Always On Availability Groups and…
Read More
Ola Hallengren at the PASS Summit 2014

Ola Hallengren’s Maintenance Scripts are Now on Github.

For some of you, the headline is self-explanatory, and you just want the URL, so here it is: For the rest of you, here’s what it means. What are Ola Hallengren’s maintenance scripts? Ola Hallengren’s maintenance scripts are a widely used replacement for maintenance plans. His backup, integrity check, and index optimization stored procedures are…
Read More

Let’s Corrupt a SQL Server Database Together, Part 1: Clustered Indexes

Hold my beer. Transact-SQL CREATE DATABASE [50Ways]; GO ALTER DATABASE [50Ways] SET PAGE_VERIFY NONE; /* Normally a bad idea */ GO USE [50Ways]; GO CREATE TABLE [dbo].[ToLeaveYourLover]([Way] VARCHAR(50)); GO INSERT INTO [dbo].[ToLeaveYourLover]([Way]) VALUES ('Slip out the back, Jack'), ('Make a new plan, Stan'), ('Hop on the bus, Gus'), ('Drop off the key, Lee') GO SELECT…
Read More

SQL Server Timeouts During Backups and CHECKDB

So you’re hosting your SQL Server in the cloud – say Amazon EC2, Azure VM, or Google Compute Engine – and you’ve noticed that when you’re running a backup or a DBCC CHECKDB, you suffer from extreme performance problems. Queries run slow, and even worse, applications report timeout errors even just trying to connect to…
Read More