17Dec 2013 by Grant Fritchey 3 Comments

Introducing Managed Backups in SQL Server 2014

Azure
Some of the new functionality of 2014 is straight forward, non-controversial and easily welcomed by the community. Think, updateable column store indexes. Some of the new functionality is going to raise an eyebrow or three (most of the time, not on one person, but you know the #sqlfamily, we have some interesting mutations represented). Think... managed backups. Now, why on earth would a process that takes backups for you, including log backups, does it automatically per a schedule and/or when data has changed sufficiently, stores it offsite for protection and is easy to set up and maintain going to cause so much controversy? Hey, I'm wrong, it won't, move along... Or, maybe, I'm right, and this is going to raise more than eyebrows. We're talking about surrendering control over your…
Read More
11Dec 2013 by Grant Fritchey 4 Comments

SQL Server 2014 Backup to URL

Azure, T-SQL
I'm absolutely in love with the concept of being able to create a backup directly to a protected, off-site location. Yeah, you can spend all sorts of money on terribly wonderful technology to enable that within your enterprise. And if you have that kind of money, great. But, what if you're like most everyone else and you just want a little more protection without mortgaging the house? Let's take a look at one possibility, backup to URL. There have been ways to backup to hosted storage, whether it was DropBox, AWS or Azure blob storage, for quite a while. But, every method I tried out involved setting up some type of drive on your system. As soon as you had your K:\ drive mapped out to AWS or whatever, you…
Read More
02Apr 2012 by Grant Fritchey 4 Comments

SQL Server Log Backups

T-SQL
While presenting a session on Common Backup Problems both at SQL Saturday in Orange County and at SQL Connections in Las Vegas, questions came up regarding whether, when, and how your databases should be configured in FULL recovery mode and what that means for your recovery plans. To boil the questions down to as simple a single statement as possible: Should your database be in FULL recovery mode at all times which requires you to run log backups on a regular schedule? The same question came up while Brad McGehee(blog|twitter) was presenting a session on using the settings on your SQL Server instance and your database to ensure you got proper performance. We both answered the question the same way, but our emphasis was slightly different. Brad’s answer was that,…
Read More
11Jan 2012 by Grant Fritchey 10 Comments

Database Backups: Things You Need To Do Now

SQL Server, T-SQL
I want to say a few things about database backups that you need to know. Wait a minute, haven’t you written about backups before? Why, yes. Yes I have. Aaand… you’re doing it again because? Have you noticed the shocking number of questions that come up on SQL Server Central and #sqlhelp regarding backups? Have you noticed the incredibly huge number of people who don’t have backups at all? That’s why. To get the word out. Oh, good point. Carry on. Because backups are so easy, people tend to discount them. That is, until they need them. Then, suddenly, they become extremely important. Here’s a suggestion: Make databases important now. Learn how SQL Server backup works. Make sure you have backups on your systems. Make sure you have the appropriate…
Read More
14Feb 2011 by Grant Fritchey 6 Comments

DBA 101: Why Don’t People Run Backups

SQL Server, T-SQL
It happened multiple times this week. It happens multiple times every week. Some poor soul is posting on a message board, usually with the heading “URGENT” (why that one word so frequently, I just don’t know), that they deleted production data/dropped a production table/updated production data/dropped a database/received a data corruption error/whatever. Now, they need to get the data back. “URGENT, What do I do now?” And so you ask, as you should, what kind of backups do you have? Over and over the answer is: “Backups? What’s a backup” or “Oh, the system guys backup of the MDF files every night” or “We don’t really need those” or “We don’t have room to back up our databases” or some other excuse that simple comes down to, we didn’t set…
Read More
18Jan 2011 by Grant Fritchey 19 Comments

DBA 101: Why is my log file full?

SQL Server
This question comes up constantly in different venues. I see it sometimes 2-3 times a day on SQL Server Central. I know it pops up at least once a week on Ask SSC. I’m sure I’ve seen it on Twitter #sqlhelp. “Why is my log file growing?” and “Why is my log file full” are heard regularly. Or, the variation, “I ran a full backup but the log file is still full/growing.” occasionally comes up. The people asking these questions are frequently, even appropriately, frantic. I’m writing this blog post for two reasons. First, to try to add a little bit of weight to what must surely be one of the most searched for phrases on the internet when it comes to SQL Server. Second, just to have a shorthand…
Read More
30Dec 2009 by Grant Fritchey 1 Comment

Practice Your Restores

SQL Server
Steven Jones posted an excellent editorial today all about how your backups are only good if you know that you can restore from them. He couldn't be more correct. I posted the following thoughts in the comments, but I know not everyone reads the comments in articles & editorials. Although, if it's a good article, you should read the comments, especially on SQL Server Central. Frequently the discussion about the article can be as enlightening as the article itself. But I digress. Steve's point, pretty clearly stated but I'll repeat it, backups don't matter, restores do. I'm going to pile on to this point just a bit, because it can't be emphasized enough. Nothing is more important than verifying backups, except, verifying that you know how to run a restore. You're…
Read More
03Sep 2009 by Grant Fritchey 1 Comment

Debuting: Beginning SQL Server 2008 Administration

Uncategorized
The new book is up on Amazon. I only worked on three chapters of Rob Walter's new book and that was after begging to only work on two, so I can't take much credit for the effort that went into this book. However, thanks to our editor Jonathan Gennick, I was privileged to work with Rob & Carmen, if pretty indirectly. I know I mentioned the book before when it was put up on the Apress web site, but this is Amazon. Once it's up on Amazon, it's real.
Read More
14May 2009 by Grant Fritchey 4 Comments

More PowerShell Basics

PowerShell
Because of the data center move, we have a number of different functions that we're running on totally different sets of servers on different days. None of this is part of our normal maintenance routines, so I've been using it as a great opportunity to stretch a little with PowerShell. I'm still learning, a lot. The latest task was to get the databases of a list of servers backed up. I initially tried it using SMO. It works great. But it's serial across all the servers as well as across the databases. I'm fine with serial backups on the databases (very, very fine, I saw a parallel backup of all databases once, pretty sparks, like the Fourth of July) but I really wanted all the servers to get backed up…
Read More
13Mar 2009 by Grant Fritchey 7 Comments

Disabling Database Encryption

T-SQL
SQL Server 2008 introduced TDE (either Total Database Encryption or Transparent Database Encryption, I've seen both) which is a way to get a passive encryption in place on the database to prevent people from stealing backups or detaching files & stealing them. It works with a Certificate in the server, and there are mechanisms around password protection of the Certificates, management etc. Setting it up & turning it on are very easy. This example is from a chapter on a book I'm working on: USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Chapter8Backup'; GO CREATE CERTIFICATE Chapter8Certificate WITH SUBJECT = 'Chapter 8 Certificate' GO USE AdventureWorksLT GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE Chapter8Certificate GO ALTER DATABASE AdventureWorksLT SET ENCRYPTION ON GO…
Read More