Backup, Restore, and Database Integrity Archives - Page 12 of 15

Production DBA

Synchronous Always On Availability Groups Is Not Zero Data Loss

September 3, 2015

In theory, when you configure AlwaysOn Availability Groups with synchronous replication between multiple replicas, you won't lose data. When any transaction is committed, it's saved across multiple replicas.

That's the way it works, right? I mean, except when you restart your synchronous replicas, or patch them, or they just stop working for any number of reasons. The primary keeps right on trucking, accepting deletes/updates/inserts, without telling end users that all their eggs are in a single basket.

Bieber Decisions: The Top 5 Mistakes DBAs Regret Later

August 25, 2015

5. Turning on auto-shrink It might make disk space alerts go away for a minute, but it can cause lots of problems. 4. Enabling xp_cmdshell and giving the SQL Server account administrative rights in Windows Young DBAs often do this to try to get a quick fix in place to manage backup files. Later on,…

Back up every database on the production server. No exceptions.

August 20, 2015

Before our consulting engagements start, we send clients a couple of queries to run. One of them outputs a list of databases, and the last backup date for each.

You wouldn't believe how often this query produces surprises like:

Getting the last good DBCC CHECKDB date

August 5, 2015

whether it's a new job or a new (old) server

If you're reading this, you've at some point in your career stared at a server, for the first time, with great trepidation.

The Easiest Way to Restore Transaction Logs to A Point In Time (Spoiler: Use AmazonRDS)

July 16, 2015

... is to use a SQL Server where a robot does it for you! Let's take a look at how you can do this in AmazonRDS's hosted SQL Server these days. Normally, restoring transaction logs is super tedious. You've got to restore all the files in the right order, for every single database. Even if…

13 Questions to Ask Before You Touch a Database Server

July 13, 2015

I've touched a lot of SQL Servers over the years. After my share of trips to HR for inappropriate touching, here's the questions I ask first these days: Is this in production now? If this goes down, what apps go down with it? When those apps go down, is there potential for loss of life…

T-SQL & Development

Replication Won’t Refresh Your Dev and Pre-Production Environments

July 2, 2015

At first glance, SQL Server's transactional replication seems like it's useful for moving data around in all sorts of situations: it works in Standard Edition, it's not synchronous, and you can have multiple subscribers.

Why People Want Replication to Test and Pre-Production Environments

Production DBA

Should You Put Things in the master Database?

June 27, 2015

Growing up as a young boy, my elementary teachers always warned me about the perils of putting stuff in master. But what's the real deal? Why is this such a bad idea? System databases are often located on space-limited drives. The default installation parameters for SQL Server throw master, model, and msdb all on the…

Production DBA

How to Configure Always On AG Backups with Ola Hallengren’s Scripts

June 25, 2015

With SQL Server AlwaysOn Availability Groups, you can offload backups to a replica rather than running them on the primary. Here's how to do it:

1. Install Ola Hallengren's utility scripts on all of the replicas. During the install, it creates a laundry list of SQL Agent jobs, but doesn't set up schedules for any of them. More on that in a couple of steps.

Production DBA

How Do You Manage DBAs? Part 1: Measuring Backups

June 22, 2015

When the DBA reports to you, ask yourself two questions:

"What would I fire the DBA for?"
"How can I prove that they're not doing that?"

Production DBA

Who’s Backing Up That Database?

June 2, 2015

A question came up on Twitter recently-- how you can you figure out where a rogue backup is coming from?

I've run into this question with clients before. In an enterprise environment where backup tools come and go, it's not unusual to find backups being run by some mysterious account.

Find Corrupted Indexed Views with DBCC CHECKDB

April 4, 2015

If you've got indexed views, or are thinking of using them, this is important.

First, go read the blog post, "An Indexed View Bug with Scalar Aggregates" by Paul White. It will take a minute, but it's important to understand.

Upgrading to SQL Server 2014: Frequently Asked Questions

March 26, 2015

Can I upgrade an existing instance without migrating?

This is nothing against SQL Server 2014, but I can't stand in-place upgrades. Over the years I've had in-place upgrades work flawlessly on a few instances, and then had an install issue cause it to fail in the middle on other instances. Usually the critical instances, just because I'm not always lucky. And when upgrade fails, it doesn't always roll back completely, or allow you to just re-run it. You may be down for a good long time.

Always On Availability Groups FAQ

February 23, 2015

Here are some of the Always On Availability Groups questions I get the most often:

Q: How much network bandwidth will I need?

9 Ways to Lose Your Data

February 19, 2015

Every time someone tells me, "This database is mission critical - we can't have data loss or downtime," I just smile and shake my head. Technology is seriously difficult.

To illustrate, here's a collection of client stories from the last few years:

Dropping an Offline Database in SQL Server

February 5, 2015

When you drop a database in SQL Server, the files are normally removed from the file system right away. POOF! Hope you had a backup if you ran DROP DATABASE against the wrong instance.

However, things are a little different if you take the database offline before you drop it. Consider the following code:
[crayon-6aae20384e8ff927616628/]
Here's what S:\MSSQL\Data looks like after I run the whole script, including DROP DATABASE...

Production DBA

How Many Databases Can I Put on One SQL Server?

February 4, 2015

I've seen servers with thousands of databases on a single SQL Server, and it works. Sure, opening the databases list in SQL Server Management Studio is painful, and a lot of third party monitoring tools fall over, but it's not so bad once you know how to work around these issues.

But there's two issues you can't work around: our old archenemies, RPO and RTO. A quick reminder:

Eight Things A DBA Should Always Do with SQL Server

January 8, 2015

I started tweeting various single-words you could start an argument with the other day. I really liked this suggestion from @SQL_Kiwi:

https://twitter.com/SQL_Kiwi/status/550126918503108609

How to Configure Ola Hallengren’s IndexOptimize Maintenance Script

December 17, 2014

If you're a production database administrator responsible for backups, corruption checking, and index maintenance on SQL Server, try Ola Hallengren's free database maintenance scripts. They're better than yours (trust me), and they give you more flexibility than built-in maintenance plans.

However, the index maintenance defaults aren't good for everyone. Here's how they ship:
[crayon-6aae20384ff5c868587019/]
The defaults on some of these parameters are a little tricky:

News & Opinion

The Most Confusing Words, Phrases, and Acronyms in SQL Server

December 16, 2014

Words are hard. Acronyms are even harder. Here's the words that give me the most trouble when talking to people about SQL Server. And thanks to all my Twitter friends quoted here for their suggestions!
Replication
"I have a question about replication..."

"Which kind? Transactional? Merge? Peer to Peer?"