SQL Server Database Instance-Level Backup Compression Setup

I was fortunate enough to attend Paul Randal’s and Kimberly Tripp’s IETPO1 this past Spring. During the week long training I met Tim Radney (he’s a SQL Consultant at SQLSkills). I approached him, introduced myself and as we were talking, the subject of SQL Server backups came up. I explained my work’s current backup strategy and how I’d like to make it more efficient, both in speed and disk space. Tim suggested I enable the instance-wide backup compression option in SQL Server Management Studio (see image below)

Since then, I have checked that option on all my database servers. In some cases it has compressed the backup file size by 80%. How neat is that!

How to Enable SQL Instance Backup Compression in SQL Server Management Studio

Right click the Instance, click Properties, click “Database Settings” on the left, and make sure there’s a check mark in¬† “Compress backup” check box. Done.

SQL Server Instance Wide Compression

SQL Server Email Alerts Setup

Recently at work we had an issue where a SQL login account’s password expired and the application that uses that login stopped working. The Window’s password¬†policy for our organization requires passwords to expire after 60 days.

Management requested that we get “an email when the password is about to expire so that we can change it to prevent the application from not being able to connect.”

So I created a little script that runs daily (via a SQL Job) to check all SQL Server login accounts for passwords that will expire in ’10’ (you can change the number to whatever you want) days or less. If any results are found, it will generate and send an email with the SQL login as a reminder that you have “X” number of days until the password expires.

Below is the script. Feel free to use/modify it for your specific needs.

USE master;

DECLARE @p_body as nvarchar(max), @p_subject as nvarchar(max)
DECLARE @p_recipients as nvarchar(max), @p_profile_name as nvarchar(max)
DECLARE @SQLAccountUser as varchar(25)
DECLARE @DaysToExpire as varchar(10) = '10'   -- you can pass this number as a parameter when calling this SP.
--Declare Cursor and get all SQL Accounts that have 'Password Expiration' enabled.
select name 
from sys.sql_logins
where is_disabled = 0 and is_expiration_checked = 1 and (
(SELECT CAST((SELECT LOGINPROPERTY(name, 'DaysUntilExpiration')) AS VARCHAR)) > 0 and
(SELECT CAST((SELECT LOGINPROPERTY(name, 'DaysUntilExpiration')) AS VARCHAR)) <= @DaysToExpire )
--Open the cursor
OPEN get_SQLAccount;
FETCH NEXT FROM get_SQLAccount INTO @SQLAccountUser; 
    SET @p_profile_name = N'Your Profile Name'
    SET @p_recipients = N'yourEmail@EmailProvider.com'
  SET @p_subject = N'SQL Account Password Set to Expire for ' + @SQLAccountUser + ' in ' + @DaysToExpire + ' days.'
  SET @p_body = 'SQL Account Password Set to Expire for ' + @SQLAccountUser + ' in ' + @DaysToExpire + ' days.'
  EXEC msdb.dbo.sp_send_dbmail
     @profile_name = @p_profile_name,
     @recipients = @p_recipients,
     @body = @p_body,
     @body_format = 'HTML',
     @subject = @p_subject
  FETCH NEXT FROM get_SQLAccount INTO @SQLAccountUser;
CLOSE get_SQLAccount;

With All Due Respect…

Recently at work, I came back from lunch to my coworkers yelling,

We’ve been looking for you! You need to restore the database!

I was extremely surprised and caught off-guard by the verbal attack. I tried my best to keep my cool, even though my heart was racing fast, and asked for further explanation as to why I should “restore the database?”

They replied, “We can’t connect to our application!

After some reflection I came up with two issues I had with what happened that day:

1. The fact that this application was an internal reporting software application with NO outside client connections. Despite this, they threw professionalism and common respect out the window by ganging up on me. Now, don’t get me wrong. I do understand their frustration…but still people should have basic common respect in how they speak to other people.

2. By yelling, “restore the database!” they belittled my intelligence as a SQL DBA. Telling me what the problem is can be a little insulting. It’s like me going to a doctor and tell him what my problem is.

After further troubleshooting, I figured out the reason why their application was not connecting to SQL Server was due to an expired SQL Account password. I reset the password and the application came online. No “restore the database” needed.

The “takeaway” of this blog post is be comfortable in your duties in whatever you do. Be confident. Be proud. Be humble. Always seek to increase your understanding and knowledge in whatever area you work in. Don’t worry when people (who don’t do what you do) yell at you and tell you how to do your job.

Keep your calm.

People You Meet Along The Way

There are always people you come across in life that stand out from others. They are the ones that end up becoming life long friends.

In terms of my professional career, there are two people that I credit for being a DBA and starting this blog.

One of them is an ex-coworker of mine I met in 2012. I remember meeting him for the first time. He was the Senior DBA and extremely helpful. One day he asked where I see my career going in five years and I told him I’d like to get into “programming.” He advised that I try out database administration. I ended up working with him for the next 2-3 years and I thank him to this day for suggesting that I pursue the path of a SQL Server DBA. I can’t express how content I am to have taken his advice.

The other person that played an important role in the direction my career is going is Tim Radney. Tim is a Principal Consultant with SQLSkills and was at the IEPTO1: Immersion Event on Performance Tuning and Optimization Training I took earlier this year (read the review here). Even though he wasn’t training the course, he took the time in between breaks and lunch to answer my random SQL questions. He advised I start a blog and work on making a name for myself. I took his advice to heart and this blog is a testament to that. If it wasn’t for his suggestion to start this blog, I probably wouldn’t have done it.

My tip to any upcoming professional is that positive change cannot take place unless it’s rooted in humility. It is extremely easy to think that you know everything and you don’t need anyone’s help. That mentality is probably one of the biggest roadblocks to growing as an individual both in your personal and professional life. Be humble. Be open.

Blogging Baby Steps

Looking back, I don’t remember a time that I ever liked to write. I was the kid in school that always took forever to write a paper because it was “never good enough.”

With some encouragement from my friend Tim Radney, I decided to take the plunge and travel down the road of blogging. I do work full time as a SQL Server DBA so most of my posts will be centered around things I learn everyday at work.

Like the title of this blog says, I’m starting out with “baby steps” but hopefully I’ll be up and running very soon!