Resource database

When SQL Server 2005 was released there were a number of changes to how you accessed system tables, such as sysobjects. The main one being that you were no longer actually accessing tables, but views, and that you should change from using sysobjects to sys.objects. A big part of that change is that many of the tables are actually in the hidden Resource database. If the Resource database is missing, corrupt, or unavailable SQL Server will not start. Now you would think that with the importance of this database you'd want to back it up, right? Well, you can't, at least not using T-SQL. I had not read anything about backing up the Resource database until today in this article, Importance of the Resource Database. So you need to do a file copy if you want this important database backed up. You'd think that Microsoft would make this more well-known if it is so important.

I'm not backing it up, are you?

Triggers in SQL Server

As I was browsing the active threads on SQLServerCentral today I happened on this post about a problem with a trigger. I did not post on the thread because someone had already made the point I would have made. The problem was that the poster had created a trigger that would not work on a batch insert or update. Something like this:

CREATE TRIGGER test_ins_upd ON dbo.test
After INSERT, UPDATE
AS

DECLARE @test_id INT,
@test_name VARCHAR(25)

SELECT
@test_id = test_id,
@test_name = test_name
FROM
inserted

IF @test_name LIKE 'a%'
BEGIN
--Do something
END
ELSE
BEGIN
--Do something else
END

I seem to see this mistake on the majority of forum posts about triggers. SQL is a set-based language and triggers deal with sets as well. I have to admit that when I was new to SQL Server I made the same mistake, but only once. I had thought that since we only allowed access to the database through stored procedures, and these procedures only acted on 1 row that I would be safe. Well, I quickly learned I was wrong. What about what I did? Or, another developer?

The first thing a new person to SQL needs to learn and really understand is that their code needs to work with sets. This will help newbies to write better performing code and help them avoid errors like this one.

Here are a couple of good blog posts about triggers:

The Trouble with Triggers by Conor Cunningham
Triggers...Evil? by Louis Davidson

SSIS Error Logging Custom Component

Well, I finally got around to writing my first custom component for SSIS. It is a synchronous transform component for Error Logging in a DataFlow. I was using a script component, which I wrote about for SQLServerCentral, that I was copying and pasting all over the place and I finally had had enough. Much of the code in the component is based on Josh Twist's XMLify Custom Component which I found through Jamie Thompson's excellent SSIS Junkie Blog. I also used the examples in Professional SQL Server 2005 Integration Services, to get through creating the component.

The code itself is pretty easy to follow, if you know anything about SSIS programming ;). It just accepts the input columns (ErrorCode is required, all others optional) and creates the new columns I want to pass out which are:

1. ErrorDesc - retrieved using the ErrorCode passed in.
2. ErrorTask - the Data Flow which hosts the component.
3. ErrorStep - the name of the Error Details component. If anyone knows how to get the name of the previous component let me know as that is really what I'd like to have here.
   
4. PackageTime - the start time of the containing package.
5. ErrorDetails - an xml document consisting of name-value pairs based on the input columns.

Probably one of the neatest things I learned was how to debug the component. I had never used the Debug -> Attach to Process feature of Visual Studio before. It was really cool to start an SSIS Package with a breakpoint in it (never done that before either), then in my Custom Component solution attach to the SSIS process (DtsDebugHost.exe), continue the SSIS package, and then hit breakpoints in my component code. Man, do I LOVE learning new things!

Feel free to use the component and improve on it. I make no warranties on it.

I have posted the code and component in zip format on my new SkyDrive account (link at the bottom of the post). I had to create the SkyDrive account because I can't put attachments on the blog. The code is also attached to the discussion of the article on SQLServerCentral, here.

SQL Server Database Management

Today's editorial on SQLServerCentral was about what your employer should provide for training and professional development and that got me thinking about what I have been doing. Over the the past year I have really made an effort to catch up with SQL Server 2005 and start managing my professional development. I relocated from northern New Hampshire to central Florida, right outside of Orlando and that has given me the opportunity to take advantage of local user groups (OPASS and ONETUG), Code Camps, SQLSaturday, and other technical events that were not easily available to me in New Hampshire. Through these resources I have probably learned as much about performance and administration as I had learned in the previous 5 years. I have picked up information about backups, profiling, performance tuning and monitoring, security, and reliability best practices. All without paying for a class or book. I've also taken the time to write a couple of articles for SQLServerCentral and I am preparing to speak at the next OPASS meeting. These are great ways to work on your professional development as one of the best ways to learn something is to have to teach it to someone else.

I also take advantage of online resources like SQLServerCentral, SSWUG, SQLServerPerformance.com, and many blogs. Most MVP's and Microsoft development teams have blogs so there many good ones, along with some you may want to out. So while I believe your employer should provide some time and money, I also think you need to take advantage of the same resources I have. Granted I take time at work to utilize many of these on-line resources, but since I'm not asking my employer to spend on me, I feel justified.

What do you do to "keep up"?

Page Restores

This is just a short post about something I just learned. You can restore a single page of your database. I learned about this in this post, Search Engine Q&A #22: Can all page types be single-page restored?, by Paul Randall on his In Recovery blog. The basic syntax is:

Restore Database Test Page = '2:10' From Disk = 'C:\Backups\Test.bak'

I'm not sure what the practical use of this is, as doing a full database restore will obviously restore any pages that might be corrupted. You need to search a little to find some good documentation in BOL, so here is a link to the documentation, Performing Page Restores.

If you have ever used page restore let me know why and how it worked for you.

Profiler Bug?

I was working with SQL Server 2005 Profiler to prepare for a talk I will be giving at the OPASS September 9th meeting (come on out and see how I do) and I saved my definition as a template. I then re-opened the template and received and error, "Invalid Event ID". I could not figure out what had happened as 5 minutes prior I had this same Trace running, and all I had done was stop the trace and save as a template. Being intrigued by this I tried to determine what the invalid event was, but how could I do that? I used Profiler. I started a new Trace filtering out the SPID for that trace and then removing the default filter that excludes Profiler. I then opened the custom template that was failing. In my new trace I saw all the trace procedures firing and the last sp_trace_setevent was for an event if of 64000 something (I did not write down the actual event id). I then queried the sys.trace_events table to see that the highest event_id is 202. Where do the 64 thousand-something number come from? Any ideas? Is it a bug, or some kind of weird combination of events? I wish I could re-create it, but I have not been able to, any helpers out there?

Disk Alignment?

I attended the Orlando PASS meeting last night where we had 2 speakers. Dolores Tofel, a local and regular attender, who did a short presentation on using Microsoft Access as a UI for SQL Server data. Even though I have developed a distaste for Access, she did a good job showing how it can be used as a RAD tool, particularly for reporting. Andy Warren, president of OPASS, is trying to give local speakers a boost by giving them an opportunity to do a mini-presentation and not be the main draw for the meeting.

The main speaker for the evening was Amy Styers from EMC. She does performance consulting for EMC customers running Microsoft products (SQL Server, Exchange, Sharepoint). Her topic was the top ten ways to improve your SQL Server performance, and I have to admit that most of the list were practices I was already familar with. But, her #10 was new to me and I think to majority of the ~20 folks in attendance. It was to get your disks aligned. She showed us that because the Master Boot Record is 63 k and the tracks are 64k. Basically this means that you will regularly have to do 2 IOs per read or write because you have to cross tracks. Apparently you can see up to a 20% IO performance gain by aligning your partitions. Of course to do this on a production system is a BIG deal as you have to make sure you have good backups and totally wipe your disks. Then you can use the DISKPAR or DISKPART(Windows Server 2003 sp1) to align the partitions and then reformat. Apparently Windows Server 2008 no longer has this issue. Linchi Shea has done some testing of this and posted the results on his blog, here. There is another article here, that deals with the same issue for Exchange with reference to an HP whitepaper. I found this subject very interesting as I had never heard this before and neither had the server admin at my workplace. We don't have any overburdened servers at this time so we won't be making the change, but it is still good to know about in case I meet someone who may be able to do something with it.

This is one reason I go to the user groups meetings even when the featured presentation may not be something I think I'll be using. Last meeting was about SQL Server Mobile and I am using it now for a personal project when I never thought I would before the meeting. I find that I learn something new every meeting and I have a good time meeting other SQL Server specialists.

The next OPASS meeting is scheduled for September 9th, 6:00pm at End to End Training, just outside Orlando. If you are going to be in the area come join us. The featured speaker is going to be great. How do I know that? It's ME! I'll be presenting on SQL Profiler Basics and I think you'll get something from I it. I know I have in preparing for it. If you do come, plan on joining us for the after event as well for some informal geek talk.