Voice of the DBA

Author: way0utwest

  • T-SQL Tuesday #83–The Same Old Issues

    tsqltuesdayThis month is an interesting T-SQL Tuesday topic, and it’s brought to us by Andy Mallon, with the topic of the same old issues we’re still dealing with. I think that’s an interesting issue, since I do find myself answering the same old questions over and over.

    If you’ve never participated, T-SQL Tuesday is a day when people should publish a post on the specified topic. This is a way to generate some posts and interest, and perhaps learning, about a topic. Share your thoughts, either on the second Tuesday of each month, or catch up later on your blog.

    We Still Don’t Restore

    I’d like to write something about T-SQL, and I could. Certainly SELECT * is an issue, or why we should not use old style joins (that’s almost gone), but there’s a SQL Server topic that I think bears repeating.

    A backup isn’t enough. You must also test restores.

    The reason isn’t complex, but plenty of people still don’t seem to understand why a backup isn’t enough. After all, if I copied a file, or I ran BACKUP DATABASE successfully, isn’t that enough?

    Suppose you had an issue at 3am and needed to restore a database on a new instance. You run this T-SQL:

    USE [master];
RESTORE DATABASE [Finances]
FROM DISK = N'D:\SQLServerBackup\MSSQL13.SQL2016\MSSQL\Backup\Finances.bak'
WITH FILE = 1,
    NOUNLOAD,
    STATS = 5;

GO

    And you get this error:

    Msg 33111, Level 16, State 3, Line 2
Cannot find server certificate with thumbprint '0xD9B9E685D465E29C11E15346D995DEF59E53B4A3'.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

    What do you do? Hopefully you recognize the issue and can fix the issue. Maybe more importantly, you have a backup of the missing certificate.

    Most people don’t deal with encryption, but you never know when your backup job might start failing, perhaps writing to a damaged file that appears to work (if you write as a device) but really isn’t capturing the backup file. Perhaps you don’t know that your backups are being written to a location and deleted a day later, but the process that is supposed to copy them to tape or a remote file share is broken.

    Any number of things can happen. The point is that you want to be sure that you are actually getting useable backup files.

    That means testing restores.

    It’s 2016. I shouldn’t have to remind anyone of this.

  • The Free-Con Returns to Seattle

    On Monday, October 24, 2016, there will be a Free-Con in Seattle for those that might not be attending a pre-con before the Pass Summit. Put on by Jason Brimhall and Wayne Sheffield, there’s a set of 6 sessions you can attend for free that day.

    Register now to come.

    They are asking for a $25 lunch fee to cover lunch, but you can opt out. However, please don’t register if you can’t come. I’m sure plenty of people want to go and extra registrations can upset planning.

    If you can come, the lineup is impressive:

    • Jason Brimhall
    • Wayne Sheffield
    • Grant Fritchey
    • Tjay Belt
    • Chad Crawford
    • Gail Shaw

    This is an alternative to other pre-conference sessions that PASS puts on, and you have to chance to learn about PowerBI, Temporal Tables, Wait Stats, Impact Analysis, and Azure SQL Data Warehouse.

    If you aren’t attending another event on Monday, perhaps you’d like to come to the Free-con and enjoy learning and discussing SQL Server with a few highly talented SQL Server pros.

  • Monday Night Networking at Summit 2016

    It’s PASS Summit time and that means Andy Warren and I are doing another Monday Night Networking Dinner.

    For those of you that haven’t come in the past, Andy and I aren’t buying you dinner. What we are doing is setting up a time and location for people to come and meet others that are attending the PASS Summit. This is a great chance to meet other data professionals if you don’t have any other plans.

    Details:

    Monday, October 24, 2016

    Location: The Yardhouse, 1501 4th Avenue, Seattle, WA (map)

    We are doing things a little differently to spread the load on the restaurant. It’s Monday night, which means football in the US. To try and ensure people can find a place to sit and meet, we’re asking you to sign up for tickets at a particular time and come down within 10-15 minutes of that time.

    The times are:

    5:30 – Sign up

    6:30 – Sign up

    7:30 – Sign up

    8:30 – Sign up

    Come down, join us, wear your Summit badge, and have a good time.

    You’ll have to buy your own food and drinks (or maybe some for a new friend), but it will be an enjoyable experience.

  • DBA Support

    This editorial was originally published on Oct 28, 2012. It is being republished as Steve is out of the office.

    There was a time when I managed two production databases on SQL Server. Two. I had a development version of one database where we paused development for testing, and only two production databases to manage. Since I had to also handle development, application support and hardware repair/replacement, that seemed like plenty to me. I was the accidental DBA, with database administration being the lowest priority of my day.

    After that I moved on to administer databases in a number of jobs, sometimes as a priority, sometimes not, but in each case, I learned to work more efficiently and effectively. My goal was to automate as much as possible of the routine work so that I could spend my days adding value to the company. I learned to use scripts, alerts, jobs, and more to keep systems running while I was doing other work.

    I’m sure many of you work in a similar manner, or at least I hope you do. This Friday I wanted to ask you at what scale do you need to become efficient, based on the size of your organization. The question this week is:

    How many databases does each DBA in your organization manage?

    I know some of you manage lots of databases in raw numbers, but also let us know if you need to do much with these databases. Is maintenance automated, or is there much active management you need to do in order to ensure these databases are running on a weekly basis. Let us know the size of your load as well, perhaps the amount of data is a better way of measuring the DBA load.

    Steve Jones