Voice of the DBA

Tag: syndicated

It’s a Lineup at SQL Intersection

I got this image in the mail recently, and it made me laugh. It looks like a police lineup, perhaps something from The Usual Suspects.

I don’t think there are any suspicious looking characters in there (though maybe the third and fourth from the left in the second row qualify), but you never know.

This is quite a lineup, and I’m honored to be a part if it. SQL Intersection, put together by Paul Randal and Kimberly Tripp of SQLskills, is taking place in April in Las Vegas. From the 8th to the 11th, there will be some amazing sessions being delivered to teach you about SQL Server.

Register today (use the code “Jones” to support me) and I hope to see you there.

March 19, 2013
Customizing SSMS – Hiding the panels

This is a short series on some customizations in SSMS to make it visually more appealing.

As a presenter, I’ve learned how to change the way Management Studio (SSMS) looks to make it easier for people to see the screen on a projector. I had wrongly assumed that so many people knew many of these tricks, which is a poor attitude on my part. That should be especially apparent as I saw a presentation recently where the speaker didn’t know how to make things easier to see.

Note that I often caution writers not to assume too much from the reader, and here I took it one step further, assuming some topics weren’t worth writing about.

Here’s one that I heard asked recently: how do you hide the side panels in SSMS?

The Default View

Here’s what SSMS looks like for most people.

The Object Explorer takes up a good portion of the screen on the left. For some people, they may have this:

Or even this:

In the last two images, the Solution Explorer and Properties panes are on the right. If you have these panes open, you might find yourself squeezing a query into a small space in the middle. If your resolution is 1024×768 (standard for many projectors), you might get very little on the screen.

Customizing

There’s a way to clean things up quickly and easily. All of these panes have this item in their upper right sides:

If you click the middle, pin icon, the “Auto Hide” will be enabled, and the panes will slide to the side. They’ll still be there, but now they’ll be in a side bar, like the Object Explorer is below:

If I hover the mouse over the “Object Explorer” button on the side, it will appear, or I can click it.

I can always use the “View” menu at the top as well (or a shortcut) to expose any of these panes. However when I’m presenting, or want to make a quick change, the “auto hide” feature works very well for me.

March 14, 2013
The Work Life Balance for Men

A great piece by LZ Granderson, and one that really make me stop and think a bit. Work and life is always a balance, and men certainly don’t have it easier. We make different choices, or we set different priorities. It’s worth the read, and as you do, before you dismiss it, consider this: how would work life look if women had been a part of business and work for all of history?

I sometimes look at the way I do things, and wonder if I’m balancing too far one way or the other. Ultimately I don’t know if I’m doing it right, but I keep an eye on it.

March 13, 2013
T-SQL Tuesday #40– File and Filegroups
It’s the second Tuesday of the month and time for T-SQL Tuesday again. This is a monthly blog party, where the participants write on a particular theme. This month Jen McCown, of Midnight DBA fame, invites us to talk about files and filegroups in SQL Server.

If you’re like to participate, write a post and drop a comment (or pingback) on Jen’s blog. Watch the #tsql2sday hashtag on twitter for next month’s invitation.

Filestream and Filegroups

I have a couple talks that deal with Filestream related topics, so I decided on a quick introductory lesson on how this works.

Filestream was built into the AdventureWorks 2008 sample database. Requiring administrators to turn on Filestream caused some confusion and complaints, despite the fact that it’s easy to do.

What does Filestream have to do with filegroups? In a database that is enabled for Filestream data, you need to add a filegroup specifically for the Filestream data. This is actually a folder on your file system, which you can access through T-SQL, or through the Win32 API. If you are using SQL Server 2012 or later, you can also access this data with a Filetable, which is built on Filestream.

Let’s create a database, and add a filegroup for Filestream. We start with the “New Database” dialog in SSMS.

With the normal defaults, we see a data file (FS_Test) and a log file (FS_test_log). For Filestream data, we need a new place to store it. Let’s add a file:

Once I add the file, I mark it as storing Filestream data. The other options are rows (data files) or log files. However this presents a problem. When I scroll right, I see that there is no filegroup for Filestream data. I can’t put this in an existing filegroup.

Let’s add one of those. Here’s the default filegroup dialog.

I can click add, and put in a filestream filegroup. The name doesn’t matter, it’s just for administrative purposes. Once I do that, I can go back to the files dialog, and if I select the dropdown, my new filegroup appears.

Now I need a location. Outside of SQL Server, I created a folder in my data directory. This can be anywhere, but I did it in the default location. It’s called FilestreamDataTest.

I then select this in the files dialog, using the ellipsis to the right of the Path column.

I see my folder in the file picker and choose it.

Once I’ve selected it, I don’t click OK. I click “Script” and get the script below:
```
CREATE DATABASE [FS_test]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'FS_test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FS_test.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [fs_test_fsdata] CONTAINS FILESTREAM  DEFAULT 
( NAME = N'fs_test_fsdata', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FilestreamDataTest\fs_test_fsdata' , MAXSIZE = UNLIMITED)
 LOG ON 
( NAME = N'FS_test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FS_test_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
```
I can run this, and once I do, if I go into the folder that contains my Filestream file, I see this:

As I create tables that hold Filestream data, including FileTable data, I’ll see entries in here for each column (or Filetable) that holds this data. There is a folder that holds logging information for this data, which I do not manage.

Hopefully this is a quick, short piece that helps you understand Filegroups and Filestream.
March 12, 2013