Voice of the DBA

Tag: syndicated

T-SQL Tricks – Customizing SSMS Templates with Parameters
I wrote briefly about templates in Management Studio (SSMS), and showed the default templates that come with SQL Server. I now want to customize some of the templates in a way that makes sense for me.

If I grab a script I use often, like this one, I can make it generic.
```
SELECT
        username
    ,   topic
    ,   COUNT(replies)
    FROM
        users u
        INNER JOIN posts p
        ON u.userid = p.userid
    WHERE
        u.email = 'bob@bob.com'
    GROUP BY
        username
    ,   topic;
```
I run this often to check things, but I rarely need Bob’s information. Instead, I’ll often get different users, and sometimes I need dates. I can add these changes:
```
SELECT
        username
    ,   topic
    ,   COUNT(replies)
    FROM
        users u
        INNER JOIN posts p
        ON u.userid = p.userid
    WHERE
        u.email = '<email, varchar, bob@bob.com>'
    AND startdate > <startdate, datetime, dateadd(m, -1, getdate())> 
    AND enddate <lessthan, char <> <enddate, datetime, getdate()>
    GROUP BY
        username
    ,   topic;
```
I’ve changed some of my variable items to parameters. I do this by taking an item that I want to make variable, like “bob@bob.com” and changing it to “<email, varchar, bob@bob.com>”.

The format for a template is:
- name
- type
- default
all of which are placed inside angled brackets and separated by commas. Now when I click CTRL+Shift+M, I get this:

I can click OK for the defaults to be placed in the script, or I can enter new ones. Either way, I save time and effort with saved queries, but saved as templates, not queries I need to edit constantly.

UPDATE: Someone pointed out that the less than, the <, was . I got this from Stack Overflow, which had a good solution. I made the < a parameter as well.
December 3, 2014
Creating Placeholder Files

I recently wrote about placeholders for disk space. While you can use any file, like large images, video, etc., I’ve found a really simple, easy way to build these files on Windows.

Contig.exe

There’s a sysinternals utility called Contig. You can download it from Microsoft and then unzip it on your system. It’s a command line utility, so you can use the /? to get the parameters from it, but here’s what you need to do: use the -n parameter, with a filename and a size.

That simple. Here’s a sample call:

And in text:

contig -n Hold1.place 1073741824

That creates a 1GB file (roughly) on my system. I can then copy this file as many times as I want to save space.

I use folder in the root called placeholder. I put one file in there and then copy it a few times to reserve space.

Why that size? 1024 * 1024 * 1024.

December 1, 2014
Placeholders for Emergencies

A long time ago I worked in a large corporation where we managed lots of servers, each of which performed a variety of different functions. Some were Exchange, some SQL, some file servers, etc. and they would come and go at a frightening pace.

We had monitoring in place, but we’d also have plenty of situations where a runaway process would use up a lot of disk space at a rate that exceeded the ability of the monitoring software to alert us before the user encountered a problem. To help us more effectively monitor things, we started adding placeholders do our server build process.

Placeholders

What’s a placeholder? It’s a large file that just takes up space on a disk. For example, I’ve created a few on my system:

These can be text files, movies, images, whatever you want. The idea is that you just save a particular amount of space. In this case, I have 3 1GB files that are taking up space.

That’s it.

If I run out of disk space, I just delete one of these, and voila, instant 1GB space available.

These have become really, really handy for me. I actually have a few on my laptop. When I run low on space, which will often happen at THE WORST possible time, like the morning of a talk, I can remove a file and free up space.

Of course, I’ve just deferred the issue, but at least then I can work at that moment and then clean up old files as I have a moment.

Placeholders are a great way to save yourself in emergencies.

November 24, 2014
Encryption in Colorado Springs – Encrypting in the Application?

Last night was my annual presentation at the Colorado Springs SQL Server User Group. I try to make sure I get down there at least once a year, and it’s been only once a year for the last few years. Far too busy, and I’m sorry for that, but I am glad I get invited to go down.

I presented The Encryption Primer, and there were a few interesting questions asked. Always good to see people debating and asking questions.

One interesting one from a developer – If I can perform encryption in the application, why would I do use something like TDE or column level encryption?

To me, I prefer to do encryption as close to the source as possible. If I can do the encryption in the application front end, I’d do it there. It reduces the chances of having the data accidentally disclosed. I don’t have to worry about having data read across the wire, or in a backup tape, or anywhere else.

However that takes time and effort. Developers are expensive, and they have to write good, solid, secure code in the application. They also have to write this encryption code in every application that accesses the database (reports, ETL, etc.).

Something like TDE is much easier to setup and use. Column level encryption, while still coding, is centralized.

It’s a balance, and one you need to consider carefully and thoroughly. It also helps to debate and discuss the decisions about what you protect, why, and what it costs.

November 20, 2014