Author: way0utwest

It’s time again for T-SQL Tuesday and this month the topic is LOB, or BLOB data. You can read more about T-QSL Tuesday in the entry above, and check out tSQL2sDay.com for the redirect.

For you older DBAs, this refers to text, ntext, and image data. For the youngsters, you know there are newer, more complete data types in SQL Server 2005 and above that are the (n)varchar(max), the FILESTREAM types (SQL 2008), the XML, spatial (2008) and CLR data types. Basically the data types that hold data exceeding the size of a page (8kb) up to the maximum size of 2^32 bytes.

The Age Old Debate

When I started working with SQL Server, it was with two third party products that my company had purchased. One was an imaging piece of software designed to store scanned documents and the other was a piece of fax software that received faxes electronically and stored them in SQL Server. One of these stored the actual image in the database as the image data type and the other stored the image in the file system with a “pointer” or path to the file in the database.

This used to be a constant debate, and indeed, even with the advent of the FILESTREAM data type, there still is a debate about which way to manage data such as image files, audio files, etc. There’s a great white paper from Microsoft Research you should read if you are considering storing LOB data in your database. It basically notes that if the files are 256kb or smaller, on overage, then keep them in the database. If they average greater than 1MB, use the file system and Filestream. In between, well, you might pick the one that works best for you.

Personally I like both methods. Keeping stuff in the database simplifies backup and restore procedures. I once had to restore a piece of softawre that used the store-paths-in-the-database and we had different paths on the DR server. That was not fun trying to sort out. You also have a clearer idea of how much space you are using with everything in the database.

However in the file system you have other advantages. You can have the web server just send images from its cache instead of requiring processing from the database server. I have always been concerned over load, so this appeals to me. It also lets someone that’s not a database person easily view or work with images in things like Paint if they need to.

So What Should I Do?

If you have images, I’d use the MS Research guidelines, but my real recommendation is that you learn how to work with FILESTREAM, learn the basic API and code needed, and integrate it if you can. One big rason to consider FILESTREAM and storage outside of the database is that many of us look to put our data and log files on RAID 1 or RAID 10 storage arrays. Those are expensive, and much of this data could easily be served from RAID 5 volumes, at a much lower storage cost. When you have a lot of data, this can be a significant cost.

If you have large volume of binary data, like audio or video files, you might consider getting a dedicated appliance at some point as well. I’ve worked with one of these before FILESTREAM, and it worked pretty well. These are Remote Blob Storage (RBS) features built into Sharepoint 2010, and I am guessing that as we accumulate more of this data, and as Sharepoint is deployed more, it makes sense to use these features.

So what should you do? It depends on what will work best in your environment. Learn a bit more about Filestream and make the best decision that you can.

I’ve been trying to learn more about the T-SQL changes in SQL Server 2005/2008 over time. I don’t have requirements to write a lot of T-SQL, so I have to futz around with it and just try things I see in articles or presentations. One that I’ve been meaning to play with for some time is the PIVOT command.

This command is designed to (from BOL): “change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.”

That’s confusing, but what this basically does is build a cross tab report. I hate doing this stuff in the db, since I think it’s a waste of resources, but I’m learning, so let’s try something. The examples in BOL work, but I wanted to check them, so I started with Jeff Moden’s Cross Tabs and Pivots, Part 1. That was easier to read, and it’s a great introduction. So I build a table.

CREATE TABLE Repairs 
( AUTO VARCHAR(20) 
  , RepairDate DATE 
  , Repair VARCHAR(20) 
  , cost NUMERIC(6, 2) 
) 
GO 
INSERT Repairs SELECT 'Prius', '7/1/2007', 'Oil Change', 75 
INSERT Repairs SELECT 'Prius', '10/1/2007', 'Oil Change', 75 
INSERT Repairs SELECT 'Prius', '1/1/2008', 'Oil Change', 75 
INSERT Repairs SELECT 'Prius', '4/1/2008', 'Oil Change', 75 
INSERT Repairs SELECT 'Prius', '7/1/2008', 'Oil Change', 75 
INSERT Repairs SELECT 'Prius', '9/1/2009', 'Tires', 360 
INSERT Repairs SELECT 'Truck', '8/1/2007', 'Oil Change', 95 
INSERT Repairs SELECT 'Prius', '11/1/2007', 'Oil Change', 95 
INSERT Repairs SELECT 'Prius', '2/1/2008', 'Oil Change', 95 
INSERT Repairs SELECT 'Prius', '5/1/2008', 'Oil Change', 95 
INSERT Repairs SELECT 'Prius', '8/1/2008', 'Oil Change', 95 
INSERT Repairs SELECT 'Truck', '2/1/2009', 'Tires', 560 
INSERT Repairs SELECT 'Porsche', '5/1/2008', 'Oil Change', 120 
INSERT Repairs SELECT 'Porsche', '4/1/2009', 'Oil Change', 120

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

I have repairs and some dates for my cars. What I was trying to do is take the table and pivot it so I can see the cost per repair per year per car. In other words, take this:

and turn it into this:

I looked over the examples I had, and then wrote this:

SELECT 
    Datepart( yyyy, RepairDate) 'Year', 
    AUTO, 
    Sum(Coalesce([Oil Change],0)) 'Oil Changes', 
    Sum(Coalesce([Tires],0)) 'Tires' 
FROM ( SELECT AUTO, RepairDate, Repair, Cost 
          FROM repairs 
       ) AS Expenses 
PIVOT( SUM( cost) FOR Repair IN ([Oil Change], [Tires])) AS pvt 
Group BY 
    Datepart( yyyy, RepairDate) 
    , AUTO 
ORDER BY  Datepart( yyyy, RepairDate) , Auto

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Which works. I choose the columns I wanted (in this case ‘Oil Changes’ and ‘Tires’) to pivot on and then wrote the statement. It works, but it seems hard to read, to me. I tend to agree with Jeff’s assertion in his article that a simple CASE statement is easier to read.

SELECT 
    Datepart( yyyy, RepairDate) 'Year', 
    AUTO, 
    SUM(CASE WHEN REPAIR = 'Oil Change' then Cost else 0 end) 'Oil Changes', 
    SUM(CASE WHEN REPAIR = 'Tires' then Cost else 0 end) 'Tires' 
FROM repairs 
Group BY 
    Datepart( yyyy, RepairDate) 
    , AUTO 
ORDER BY  Datepart( yyyy, RepairDate) , Auto

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

But that’s me. I haven’t messed with dynamic columns or other changes, but for now I’m not sold that a PIVOT really is that helpful.

Building an audit in SQL Server 2008 is very easy, much easier than in previous versions using SQL Trace. That was painful, and I am glad that it’s not required anymoer.
Building a server audit consists of first setting up the audit. You do that in SSMS through the following steps. First find the “Audits” folder until the Security folder.

Right click this and select “New Audit”

That gives you the main dialog, where you can specify a number of options. First is the name, which isn’t important. It just allows you to identify each audit.

The Queue Delay is used to determine how the audit is recorded in the target file. If this is set to 0, then you have synchronous auditing. Audit records are immediately written to the target location. The default is asynchronous auditing, for performance reasons. This value is in ms, and defaults to 1 sec. This determines up to how long audit records can be held in a buffer in memory. There is a potential for audit record loss if the server crashes here. The minimum delay is 1000ms (other than 0) and the max is 2,147,483,647 (24 days, 20 hours, 31 minutes, 23.647sec)
The next option is the “Shut down server on audit log failure.” This is important if you need to ensure no actions can occur if auditing does not work. If this option (off by default) is checked, then if there is a write failure to the audit target, the server is shut down. Note that if this is checked, the login creating the audit must have the server shutdown permission, then the function fails.
The drop down allows you to choose the target location. You have three choices:

• a file
• The Windows Security Log
• The Windows Application Log

If you choose one of the Event logs, no other options are needed. If you choose the Security log, there are some additional configuration options (coming in a later blog). If you choose file, then you specify other options.
The file path can be specified and the SQL Server service account must be able to write to that location. You don’t get to choose the file name. That is automatically generated in the following format.
<audit_name>_<audit_guid>_nn_<timestamp_as_bigint>.sqlaudit
Once you pick the file name, you have to choose how large each file gets (maximum file) and the maximum number of files (Maximum). The sizes can be specified in MB, GB, or TB. The minimum size if 2MB and the max is 2,147,483,647TB. You can specify an unlimited size, which grows until the disk is full. You can specify the number of files as unlimited as well.
The last option is the RESERVE SPACE option. If checked, it reserves the space on disk of the file size specified and preallocates that to the audit file. This is only used if the maximum size is not set to unlimited.
By default, the server audit is disabled, meaning that no audit records are recorded. Since we haven’t specified any audit specifications at the server or database level, nothing will get recorded anyway. To turn the audit on in SSMS, you need to save the audit and then right click it and select “Enable Audit”

As I mentioned, without any audit specifications, nothing will get recorded. In another blog I’ll look over creating specifications and viewing audit records.

SQL Saturday #49 in Orlando just opened their call for speakers. I’m debating if I want to go out there. The event is on Oct 16, 2010, and this is always one of the larger SQL Saturday crowds. I have a lot of friends there, but I was hoping to get to SQL Saturday #45 in Louisville on Sept 25, just a few weeks before. With two weeks of travel in Nov with PASS and Connections, my travel schedule is getting booked.

I’m debating about going down there since this would be my second time there. If I have to choose, I’ll go to Louisville to a new city instead of an old one, but I’d like to get to both.