Art of the DBA Rotating Header Image

April, 2012:

The importance of listening

A while back when I was still studying music, I went to a master class conducted by a prominent tuba player. We covered a lot of the usual stuff, like breathing exercises, intonation, and specific excerpts and audition pieces. A major portion of the time, though, was spent on another important aspect of musicianship: listening. As a group, we talked about listening to different musicians to how they would phrase melodies or shape dynamics, discussing guys like Sinatra, Rush, Miles Davis, and many others. It was stressed that spending time listening critically to music was just as important as practicing and something we should be spending a couple hours on daily.

I began to think about this vital part of musicianship recently when I was at SQL Saturday 107, talking with other presenters about how we approached our presentations. For many of us, the practice of public speaking isn’t just about sharing with others of the SQL community, but also about improving our own skills. I’ve written before about how presenting is like performing and, while I’ve been practicing and rehearsing my presentations, I’ve also been trying to watch other presenters to learn what techniques others use and what might help me improve my own skills.

There have been a couple speakers that have taught me a lot, simply by watching them use their craft. Probably my biggest influence to date is Grant Fritchey(b|t). I’ve learned a fair amount from watching him, but one of Grant’s greatest strengths is he presents with passion and excitement. When a speaker is energized about a topic, the audience will be engaged and drawn in by that energy. It’s important because the energy becomes cyclical. The more the audience is engaged, the more comfortable the speaker gets, and the better the presentation flows. I’ve also noticed that Grant doesn’t try to force the audience to respond, but allows his own excitement to resonate in the audience.

Another lesson I’ve learned is how to use humor to relax an audience. Wes Brown(b|t) does some fantastic presentations on storage and part of what makes them work is his easy, natural humor. If you’ve ever met Wes, he’s always got a joke ready. This works for presentations because it relaxes the crowd when everyone shares a laugh. It also gets the audience to respond to the presenter, breaking down the wall between the two. This is important, because it helps create and drive that energy between the performer and audience.

A quick follow up on this, I’ve seen a lot of people use “funny pictures” in their presentations to interject this humor. While this works for some folks, I found this doesn’t work for me. In the style that I give presentations, I find that this approach is a little forced and takes away from the story I’m trying to tell. This isn’t to say that it won’t work for you or for other folks, it’s just a case of observing how others do something, evaluating it for my own use, and making a decision based of that analysis.

Some other thoughts on what doesn’t work. I’ve seen demos blow up on folks, presenters who lose focus, session that try and cover too much material, presentations that end to quickly because a speaker lost control of the pacing, etc. While none of these are related specifically to one another, they always remind me of how important it is to practice. The more you go over your presentation material, the better you will be at presenting it to others, and you can recognize the lack of rehearsal through critical observation.

The key with all of this is to become a student of the craft. Many of us have great technical knowledge, the ability to figure out those tough problems like memory pressure, storage bottlenecks, security, application caching….the list goes on and on. Much of this is because we read and study that craft. If we want to similarly immerse ourselves in the study of public speaking, we should watch what others do. This can be done at the PASS Summit, SQL Saturdays, or your local user group. You also can go online and watch any number of presentations at TED or other webinars given by the community. In fact, it could be very helpful to watch non-technical presentations to add perspective. Just as any musician would spend at least part of his day listening critically to music, you should watch videos, webinars, and other demonstrations with a critical eye.

Now I want to note, you’re not looking for errors just for the sake of errors. I had a music teacher who called those folks “calculator kids”, just figuring out everything that went wrong. That’s not what this is about. By watching presentations critically, you want to catalog what you like and what you don’t like, and try and figure out what things in a presenter’s style works for them. The goal is to find those skills and techniques that will make you a better presenter.

Here’s a little exercise: The next time you watch an online video or go see someone talk about a topic (any topic), write down three things you liked. That’s it, simple enough. Try and do that each time you’re in a session. You don’t even have to say these are three things you will do in future presentations, but by just writing them down you’ll start thinking about those tricks and will choose some for yourself. I promise you, just by doing that, your presentations will better and not only will your audiences get more out of them, you will too.

T-SQL Tuesday #29 2012 Party: Dancing on the FileTable (#tsql2sday)

For this month’s edition of T-SQL Tuesday, Nigel Sammy(b|t) asks us to talk a bit about a feature of SQL 2012 that we’re excited about. Now, while my most favoritist SQL 2012 feature is the ability to install on Server Core, I’ve already written about that and I want to talk about something that hasn’t gotten quite so much publicity. Enter the File Table, new functionality that exposes more of the Windows file system to SQL Server. With it, we get some additional tools for file handling that before we had to do with other applications and workarounds like xp_cmdshell.

A File Table is a special type of table in SQL 2012. It’s got a fixed set of columns and points at a directory managed by SQL’s FILESTREAM technology. If you do a SELECT from the File Table, you’ll get a listing of all the files within that directory. What’s cool is that “inserting” rows into this table is as copying files into the directory. Once a file is in there, we can use use SQL to do other operations, like copying files between two different File Tables, or cleaning up old files based on the file dates or archive flags.

Setting up a File Table doesn’t take much, but since it uses FILESTREAM it can be a little tricky. The first thing you need to do is actually enable FILESTREAM for your SQL Server instance. To do so, just follow the steps from MSDN. For the purposes of this, I’m going to set up my share name as my instance name, SQL2012. This means that to get to my FILESTREAM objects and my File Tables, I’m going to be using the UNC path of \\SHION\SQL2012 for my laptop.

Now that we’ve got FILESTREAM going, we can go ahead and create our File Table. The first step is to set our database options to allow non-transactional access and a default virtual directory for the FILESTREAM.

--Set database specific options for FILESTREAM
ALTER DATABASE demoFileStream
SET FILESTREAM (non_transacted_access=FULL,directory_name=N'FSDemo')

Next, we need to add a FILESTREAM filegroup to the database, which defines the physical directory where our file tables will exist. Note, this directory will be a physical directory, but SQL Server must create it so it cannot already exist.

--Filegroup for the filestream
ALTER DATABASE demoFileStream
ADD FILEGROUP fgFileStream CONTAINS FILESTREAM

--Directory for the filestream filegroup
ALTER DATABASE demoFileStream
ADD FILE (NAME=flFileStream,FILENAME=N'C:\FileStream')
TO FILEGROUP fgFileStream

Finally, we can now create our file table with a simple CREATE TABLE statement. Note, we won’t declare any columns as those are pre-defined for the file table structure.

CREATE TABLE FT_Test
AS FILETABLE

And we’re done! We can now browse to the following UNC filepath: \\SHION\sql2012\FSDemo\FT_Test. This directory is the physical location of our file table. For now, I’ll put a couple test files in there.

At this point, we’ll just run a simple select and, bam! There’s a listing of our files.

SELECT
  name,
  file_type,
  creation_time,
  last_write_time
FROM FT_TEST


Voila! A happy little file table, ready for our use. I’ll be honest, there’s some limited use here and having file tables doesn’t solve a lot of problems. It’s very useful, though, for certain use cases and can be a very powerful tool in your tool kit. If you’re trying to think of situations where you need it, consider times where you need to work with files on the file system:

  • Managing backup files: If you’re like me, you write your backups to a unique filename for each backup. This means I usually need some other tool to clean up my old files. With File Tables, I can manage that clean up completely within SQL.
  • Restores: If you’ve got a lot of transaction logs to restore and you want an easy way to parse through files to find the ones you need, you can now do that using File Tables.
  • Basic ETL: If you need a simple ETL solution, File Tables can give you some additional leverage so you can keep your entire solution within SQL.

Thanks again to Nigel Sammy for hosting this month’s T-SQL Tuesday. There’s so much new with SQL 2012, it’s good for Nigel to give the community a chance to share some of the new features that are now out. Enjoy the rest of T-SQL Tuesday #29!

The Tools to Survive

Last week, I blogged about monitoring backups, service status, and disk space usage. These are the keys to survival for you and your databases. There are several ways that these can be monitored and there are many third party tools that you can use for these tasks. However, third party tools are often not an option.  Fortunately, you can also make use of SQL and Powershell to handle basic monitoring. Here’s a couple scripts that you can use in your environments.

Overdue Backups

The backupset table makes it easy to know when your backups are being taken. By making use of a couple queries, you can find out if your databases are actually being backed up and take appropriate action. This particular script is designed to check for any databases that haven’t had a full backup (type=’D’) in the last three days and email out an alert if any are found.

with db_fulls(dbname,last_backup)
as
(select
	database_name,
	max(backup_start_date)
from
	msdb.dbo.backupset
where
	type='D'
group by
	database_name)
select
	d.name,
	d.recovery_model_desc,
	df.last_backup
into #results
from
	sys.databases d
	left join db_fulls df on (d.name = df.dbname)
where
	isnull(df.last_backup,'1/1/1900') < dateadd(dd,-3,getdate()) 

if (select count(*) from #results) > 1
begin
	DECLARE @tableHTML  VARCHAR(MAX)
	,@emailrecip varchar(200)

	--SET YOUR EMAIL ADDRESS HERE
	set @emailrecip = 'you@yourcompany.com'

	SET @tableHTML =
		N'</pre>
<h1>Overdue Backup Report</h1>
<pre>
' +
		N'</pre>
<h2>No full backups in the last 3 days</h2>
<pre>
' +
		N'</pre>
<table width="1250" border="1">' + N'
<tbody>
<tr>
<th align="left" width="40%">Database</th>
<th align="left" width="30%">Recovery Model</th>
<th align="left" width="30%">Last Backup</th>
</tr>
' + CAST ( ( SELECT td = rtrim(name), '', td = rtrim(recovery_model_desc), '', td = isnull(convert(varchar(100),rtrim(last_backup),100),'NO BACKUP'), '' 
from #results order by name FOR XML PATH('tr'), TYPE ) AS VARCHAR(MAX) ) + N'</tbody>
</table>
<pre>
' ;

	exec msdb.dbo.sp_send_dbmail
		@recipients = @emailrecip,
		@subject = 'Overdue Backup Report',
		@body = @tableHTML,
		@body_format = 'HTML' ;
end

drop table #results

The basics here can also be modified for your particular needs. In my own environment, I use this and a second script that monitors for log backups(type=’L’). I schedule the full backups to run every morning. For the log backups, I run the report every 4 hours and alert me if a database hasn’t had a log backup in the last 12 hours.

Stopped Services

It’s hard to monitor SQL Services from SQL Server itself, so for this task I turned to Powershell. By making use of the Get-Service commandlet, you can collect all service related information from a computer. Add some functionality for email, and you can build a handy little service monitoring script.

$srvlist = Get-Content $args[0]
$report=@()

foreach ($server in $srvlist)
{
	try
	{
          $svcs=Get-Service -ComputerName $server | where {$_.name -like "*SQL*"}
		foreach ($svc in $svcs)
		{
			$output = New-Object System.Object
			$output | Add-Member -type NoteProperty -name Instance -value $server
			$output | Add-Member -type NoteProperty -name SvcName -value $svc.Name
			$output | Add-Member -type NoteProperty -name DisplayName -value $svc.DisplayName
			$output | Add-Member -type NoteProperty -name Status -value $svc.Status
			$report+=$output
		}
	}
	catch
	{
		$output = New-Object System.Object
		$output | Add-Member -type NoteProperty -name Instance -value $server
		$output | Add-Member -type NoteProperty -name SvcName -value "No_Service_Collected"
		$output | Add-Member -type NoteProperty -name DisplayName -value "No Service Collected - COLLECTION ERROR"
		$output | Add-Member -type NoteProperty -name Status -value "ERROR"
		$report+=$output
	}
}

#Set these for your environment
$smtp="yourmail.server.com"
$from="SvcAlert@yourserver.com"
$to="You@yourcompany.com"

if(($report | where {$_.Status -ne "Running"}).Length -gt 0)
{
	[string]$body=$report|where{$_.Status -ne "Running"}| ConvertTo-HTML
	Send-MailMessage -To $to -from $from -subject "Service Monitor Alert!" -smtpserver $smtp -body $body -BodyAsHtml
}

To call this script, you’ll want a text file that contains a list of your servers (passed through as an argument to the script).  Then, run the script as a Windows Scheduled Task.  I recommend setting it up to check the status regularly every few minutes, running from a different computer than your SQL Server. This guards against your monitoring process failing in case your SQL Server shuts down. You will also need to ensure that whatever account the task runs under has remote access to your SQL Server.

Free Space

There is actually quite a bit written about this particular topic. There are two ways you can go about this, depending on whether or not you want to monitor your space from within SQL Server or externally. One option is to use xp_fixeddrives for querying data from directly within SQL Server. If you’ve upgraded to SQL 2008 R2 SP1, you can also make use of sys.dm_os_volume_stats. Personally, I lean more towards making use of Powershell (again) because of the flexibility it gives me. For this, there’s several options, but a good place to start is this script off of Simple Talk. Any of these choices will give you the information you need.

With these basic tools, you can start monitoring your systems for continued operation as well as being alerted when critical issues arise.  From here, you can then move to monitor other aspects of your server and database health.