SQL Servings

Moving to new home

2010-12-31T18:03:00.002-06:00

I'm moving SQL Servings to a new site, at http://www.pertell.com/sqlservings. See you there.

SQL and Relational Theory Master Class by C.J. Date

2010-12-16T10:08:00.000-06:00

I recently had a chance to review C.J. Date's video, Relational Theory Master Class. This is an excellent resource and I highly recommend it for anyone who wishes to better understand the theory behind relational database design. But be prepared; this is not a beginner’s class. You should have a basic understanding of SQL and database design before viewing this video. And while the content is expensive at $299 I feel it’s worth it. It’s still cheaper than attending a similar seminar in person, and you can review it whenever you like.

The video is a companion to Date’s book, SQL and Relational Theory, but it’s not required to read or purchase the book too. There’s 16 hours of video content, and I feel the class does a better job of describing relational theory. One of the features of the video I like is that it’s not static; that is, the camera focus is not always on the speaker or a slide deck. Moving between the speaker, the audience, and the content keeps the viewer’s interest. And you can hear the questions asked; it’s almost like you’re attending the class in person.

The content is excellent. It’s not centered on any specific platform. The methods you learn here help you to better understand why your table joins work the way they do, regardless if your database is Oracle or SQL Server. Once you better understand relation theory your code will make more sense.

You can find SQL and Relational Theory Master Class at O'Reilly Books

What I learned today: No backup or restore with the DAC

2010-10-04T16:23:00.001-05:00

For everything I teach, I learn something in return.

I began to train a new junior level DBA today. He’s someone that works with us already as a developer but he’s shown an interest in learning how to be a DBA so I’m beginning to teach him what I know. To start off we began by discussing his daily tasks, then we started to talk about backup and recovery, topics I gave him as an assignment last week. For this week’s homework I told him to read up on the Dedicated Administrator Connection (DAC) and to get familiar with SQLCMD. I mentioned how the DAC was useful in situations where the server was under a strain and you need a way to connect to troubleshoot the problem. Then I said you can use it for backups and recoveries.

I should have quit when I was ahead.

As my new colleague correctly pointed out, you can’t use the DAC for backups or recovery. If you try, you’ll get the following error:

Msg 3637, Level 16, State 3, Line 1
A parallel operation cannot be started from a DAC connection.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

And BOL can’t make it any clearer:

SQL Server prohibits running parallel queries or commands with the DAC. For example, error 3637 is generated if you execute either of the following statements with the DAC:

RESTORE
BACKUP

(Ref: http://technet.microsoft.com/en-us/library/ms189595.aspx)

For some reason I’d forgotten that. It was something that I had read a long time ago and forgot since I don’t use the DAC very often, never in production other than to confirm that it was set up through the firewall. I had assumed that backing up a database in an emergency would be one of the reasons I’d use the DAC. Luckily I was reminded before a situation arose where I relied on it.

So I actually learned two things today. I was reminded about the restriction of the DAC, and I learned to be more careful about what I tell others when it comes to SQL, not to assume facts and to do my own homework. I hope I remember both lessons equally.

Using the IGNORE_DUP_KEY index option

2010-09-30T08:58:00.001-05:00

This is a quick demo I put together for a talk on indexes earlier. It shows the behavior of the IGNORE_DUP_KEY option when you create a unique index.
First, create a small sample table. I used tempdb but feel free to use whatever test database you want. Then create a unique clustered index on the field1 column.

CREATE TABLE Table1
(
    field1 int        NOT NULL,
    field2 char(2)    NOT NULL
)
GO

CREATE UNIQUE CLUSTERED INDEX cl_Table_1 ON Table1 
(
    field1 ASC
)
GO

Next insert a few records into the table. I’m inserting records in two separate blocks to trap the intentional duplicate key error.

BEGIN TRY
    INSERT INTO Table1 VALUES(1, 'aa')
    INSERT INTO Table1 VALUES(2, 'ab')
    INSERT INTO Table1 VALUES(3, 'bb')
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE ()
END CATCH

BEGIN TRY
    INSERT INTO Table1 VALUES(4, 'cc')
    INSERT INTO Table1 VALUES(3, 'ac')
    INSERT INTO Table1 VALUES(5, 'dd')
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE ()
END CATCH

SELECT field1, field2 FROM Table1

You won’t get any errors in the first block because all three values are unique as defined by the key. The second block will generate the duplicate key error when it tries to insert the value 3 into field1. At this point the error is trapped and value 5 doesn’t get inserted. Viewing the values shows the 4 records.

field1	fiels2
1	aa
2	ab
3	bb
4	cc

Now lets alter the index to ignore duplicate key errors. When you use this option any error is ignored and execution of the batch continues. Only the duplicate record is not inserted. We’ll insert three more records and again trap any errors.

ALTER INDEX cl_Table_1 ON Table1 SET ( IGNORE_DUP_KEY  = ON );
GO

BEGIN TRY
    INSERT INTO Table1 VALUES(6, 'zz')
    INSERT INTO Table1 VALUES(2, 'dd')
    INSERT INTO Table1 VALUES(7, 'cc')
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE ()
END CATCH

This time no error is generated. The duplicate value of 2 is ignored and execution continues, meaning that the next record of 7 is also inserted. Viewing the values shows this.

field1	field2
1	aa
2	ab
3	bb
4	cc
6	zz
7	cc

Does your company owe you training?

2010-08-21T09:19:00.001-05:00

Recently I read an interesting post by Steve Jones (Blog | Twitter) on SQL Central about Personal Investments, how you invest in your career. It’s also something I’ve been thinking about lately. I agree with Steve that I’m responsible for my own education. But I feel that companies should be, to some extent, as well.

I fall into the “accidental dba” category. When a senior member of our team left I was assigned to work on maintaining and monitoring our databases, something no-one really did before that. It fell to me because I had the most knowledge about SQL Server, as opposed to just writing T-SQL statements. I learned what I could by going to all the free events I could find. I joined my local SQL User Group and attended as many meetings as possible. I bought and read many excellent SQL books and blogs. I took any free or cheap training resource that was available. Eventually I passed a number of Microsoft certification exams. I’m proud of what I’ve achieved on my own, and I will continue to further my technical education.

There’s a cost to my training, even if it’s free. There’s the hours of attending conferences and viewing webcasts. There’s the price of purchasing books, and the time it takes to read them. There’s even travel related costs, such as attending a SQL Saturday event in another state. I pay the cost gladly, and I’ve already seen the benefits.

But at what point should a company start providing training?

Now I don’t believe your company should always pay for your education. As Steve said in his post, you should make your own investment in your career, showing your managers the value of your training. You’re the one responsible for keeping your skills up to date. But it should be a partnership. Your company also needs to make an investment. They have as much to gain as you do.

This isn’t just to motivate an employee, giving you a nice job perk. Frankly, if you’re not self-motivated to get the basic skills needed to do your job maybe you should start looking for another position. But if your job suddenly requires specialized knowledge they should help you attain that knowledge.

Let’s say, for instance, that your company has decided to use clustering for high availability. And since you’re the SQL ‘expert” they’ve come to you to set it up. You’ve read up on clustering, and you know in theory how it works. But you’ve never worked in a clustering environment, let alone design the architecture involved. Shouldn’t your company help you learn what you need to know to succeed? After all, they have more at stake in this case than you do.

I just feel that companies should also contribute to your technical education. I’m interested in how other’s feel about it.

What I learned today – CHECKDB and database snapshots

2010-08-16T19:15:00.001-05:00

A few days ago I was sitting in my local coffee shop, getting a little light reading done. At work we’re about to move from the Standard Edition of SQL 2005 to the Enterprise Edition and I wanted to learn more about some features of database snapshots. Snapshots are basically just a read-only copy of a database taken at a specific time. Anyway I was reading Chapter 3 – Database and Database Files in the excellent book Microsoft SQL Server 2008 Internals. The discussion turned to space allocated for the snapshots. That’s when I read that DBCC CHECKDB actually runs against a snapshot of the database.

That caught me by surprise. Until then I thought that the CHECKDB ran against the database itself.

Now I thought I was doing OK with protecting our databases. I faithfully run CHECKDB weekly against all databases and check for any errors. I set the CHECKSUM option and verify backups. I occasionally restore databases on a standby server to make sure there’s no problems with the backups. And I monitor database and drive space pretty closely.

But I was flying blind. I have no idea how near I came to having CHECKDB fail because of a lack of space. So I started digging a little deeper into CHECKDB. And I was able to learn a little bit more of how CHECKDB works with snapshots.

For anything concerning CHECKDB, your first resource should be Paul Randal (Blog | Twitter). Read his excellent series on CHECKDB From Every Angle to get a good understanding of what’s going on. And he also wrote Chapter 11 – DBCC Internals in Microsoft SQL Server 2008 Internals, a great book you should read anyway.

CHECKDB needs a consistent view of the database to work with. In SQL 7 it acquired table locks. In SQL 2000 it analyzed the transaction log. Beginning with SQL 2005 and going forward it creates a hidden snapshot on the same volume as the database – you have no control over where it’s placed. If you’re running CHECKDB at the same time that your server has a heavy workload the snapshot can run out of space and you’ll get an error showing that CHECKDB didn’t complete. If that happens you have a few options;

Create your own database snapshot on a drive that has enough space and run CHECKDB against that snapshot. CHECKDB will know that its running against a snapshot and won’t create another one.
Start the SQL service in single user mode.
Put the database in read-only mode.

Once CHECKDB has finished it will delete the snapshot if it created one.

But there are a few gotcha’s with CHECKDB creating the snapshot. First, it has to be created on an NTFS drive. Second, you can’t use the WITH TABLOCK hint. And third, you can’t run against tempdb; but why would you want to?.

I’m sure I’m over simplifying. I still need to finish Chapter 11.

One other thing I’m wondering. If CHECKDB can create a snapshot database in SQL 2005 regardless of the edition, can the Standard Edition create one with a hidden flag?

Why I bought an iPad

2010-08-09T12:23:00.001-05:00

Disclaimer

I bought my new iPad a week ago. I thought I’d write a post explaining why I bought one, and kind of giving it a review against other eReaders I’ve used. But I noticed that Steve Jones wrote something similar at SQL Musings a few days ago. I had almost finished my post when I read it, and I decided to go ahead and finish it with this disclaimer. But please read Steve’s post, he raises some excellent points.

Background

I read. A lot. Always have. And since I have a long daily commute I have a lot of time in which to read. My tastes range from light summer reading, books that are perfect for taking to the beach or on vacation, to heavily technical training manuals.

My problem is what to carry with me. I usually use my morning commute to study or to read the news. The afternoons are most likely spent doing research. Nights and weekends are for reading the latest novel. For instance I’m currently studying for the SQL 2008 Business Intelligence certifications so I’m going through the MS Press Training Kit for 70-448. But I’m also reading Carl Hiassen’s latest novel Star Island. And I’ve got James Lee Burke’s The Glass Rainbow on tap.

Older Devices

I’ve been reading eBooks for a while now, on any number of devices. I started by reading pdf files on my desktop computer. Then came Microsoft Reader and their .lit files. Of course Word documents and text files were included.

But the problem with using a desktop computer was that it wasn’t portable. Once I got an IT job I could justify buying a laptop computer. Even more handy were PDA devices, precursors to the smart phones of today. Each of these devices also had issues. Laptops, at least the ones I had years ago, were bulky, took a while to boot up, and generally didn’t have a lot of extra storage. And PDA screens were too small for my liking. If I made the font large enough to read comfortably then I was turning pages constantly.

My devices now

Enter the Amazon Kindle. I know that the Sony Reader came first, but it never really tempted me. I thought the price was too high and the book selection was too small. I thought the same of the Kindle 1 when it first came out. It wasn’t until the Kindle 2 was released, with a growing number of eBooks and a cheaper price, that I got behind it.

I also had a different laptop now, an HP Pavilion TX2000 that’s a Tablet PC with a swivel screen that can run as a slate or a classic laptop. This 3 year old PC has 4 GB of RAM and a 500 GB hard drive so it can run pretty much anything I want.

And as you can tell from the title of this post, I just bought an Apple iPad. I’ve got the 16 GB WiFi model, more than enough storage for my pdfs and other documents.

I should also say I’ve ruled out the Nook from Barnes and Noble as too much like the Kindle. I know it can share books, but I don’t know anyone else with a Nook and I don’t think the eBook selection is as good as Amazon.

Kindle pros and cons

I’ve been using the Kindle 2 now since it came out and I love most of it. It’s light, easy to hold in one hand while reading. It has a large enough storage capacity of 1.5 GB, free 3G Whispernet connectivity and direct connect to Amazon or other sites to download a book in 60 seconds. You can adjust the font and the number of words per line, and the readability on the Kindle is the best of my three main devices. The battery life is 2 weeks with 3G off, 4 days with it on. And you can read in direct sunlight, so I can read just about anywhere. There’s no back light, but I have a clip-on for the few times I read in bed. The last Kindle upgrade added collections, a way of grouping my books by category.

My only regret about the Kindle is that it’s not the best device to read technical books or pdfs on. Originally, I could email a pdf to a special Kindle account and it would convert it to a Kindle-formatted ebook. Later there was an update that allows me to copy a pdf directly to the Kindle. And there’s always been 3rd party tools like MobiReader and Calibre that could convert pdfs to a format that Kindles can read. However big pdfs are a problem, and converted pdfs never really convert cleanly. The 2.5 update allows zooming into pdfs and you can rotate the screen, but there’s still an uncomfortable amount of scrolling and page turning that makes reading pdfs on the Kindle very difficult, at least for me.

Tablet PC pros and cons

I love my tablet pc. It fits my needs as a laptop computer, allowing me to run every application I want, plus it’s Wacom and touch enabled so I can take handwritten notes. Amazon and other eBook manufacturers have released a Kindle app for pcs so I can sync my Kindle books between devices. I’ve got built-in WiFi and a Sprint 3G data card so I can connect to the internet from just about anywhere. Using Adobe Reader allows me to size my pdfs to any size I need. And when I’m studying its great to have the ability to work directly in another application to try out some of the things I’m reading about in my current pdf.

But the tablet pc is the most expensive device. It’s also has the least amount of battery life, though I have more than enough for my daily commutes. It takes the longest to start up either cold or warm. It’s the bulkiest by far. And the screen is unreadable in sunlight.

iPad pros and cons

I fought getting an iPad for a while, I really did. I thought for the most part it would just duplicate what i could already do on my tablet pc. But it does have some distinct advantages. First, it’s much lighter. Not as light as my Kindle but pounds less that my tablet. It starts up almost immediately. The larger screen makes the iPad ideal for my technical documents. I can browse the web and read emails through the WiFi connection. And like my tablet, there’s a Kindle app so I can sync up my Amazon books.

Again, like the tablet, I can’t read the screen outdoors. It’s far more expensive than the Kindle ($499 vs $139 for the lowest priced models) but not as expensive as my tablet. Battery life is started at 10 hours. That’s acceptable for my commutes but I’d have to carry a charger for vacations.

My conclusions

I really want to have a single ebook reader but my perfect device isn’t out there just yet. I want a device that can easily read all formats, has great battery life, is small enough to be carried easily, can be read in all light conditions, has Internet connectivity through WiFi or 3G, and is affordable. Until that device is available I’ll be using my iPad for technical readings and my Kindle for my more causal readings.

Creating your own default trace

2010-07-27T08:54:00.001-05:00

Are you aware of the default trace that SQL Server runs when it starts? Did you know it’s configurable?

No, you can’t edit it. But you can still run a trace on startup that captures only the events that you want.

The first thing you should do is to see if the default trace is enabled. Simple enough, just run sp_configure with advanced options and look for 'default trace enabled'. Or query sys.configurations for configuration_id 1568.

SELECT * 
FROM sys.configurations
WHERE configuration_id = 1568

On my laptop it is.

There’s a few ways to see what the trace is doing. First find the trace id. It should be 1, but you can make sure…

SELECT * FROM sys.traces

SELECT * FROM ::fn_trace_getinfo(0)

…then query fn_traace_geteventinfo(1) to see what events the default trace is looking at. You can see the descriptions of the events by joining to some system views.

SELECT tc.name AS [Category],
   dt.eventid, te.name AS [Event Name], 
   dt.columnid, tcol.name AS [Column Name] 
FROM ::fn_trace_geteventinfo(1) AS dt
   INNER JOIN sys.trace_events AS te ON dt.eventid = te.trace_event_id
   INNER JOIN sys.trace_columns AS tcol ON dt.columnid = tcol.trace_column_id
   INNER JOIN sys.trace_categories AS tc ON te.category_id = tc.category_id

But what if, after looking at the events that the default trace is looking at, you decide to add to the list? Or maybe you want to skip some events? Remember, you can’t alter the default trace.

First disable the default trace. Use sp_configure, the change doesn’t require a restart. It will also close the trace.

sp_configure 'default trace enabled', 0;
go

reconfigure

Next create a stored procedure in the master database that will create and start your trace. Use sp_trace_create to generate a trace id, use sp_trace_setevent to select the events you want to capture, and finally start the trace with sp_trace_setstatus. Read more about creating traces here. If you aren’t familiar with creating traces via T-SQL use SQL Profiler to capture the events you need and set whatever properties you need then extract the trace properties to a script file.

Finally mark your new procedure to start when SQL does by using sp_procoption. This procedure takes 3 parameters; the name of your procedure, the option ‘startup’, and a true or false value. ‘Startup’ is the only option available, true sets to autostart, and false sets to not autostart.

In my demo script below I’ve created a trace that will track what a certain user does in the database as well as when they log in or out.

USE [master]
GO

CREATE PROCEDURE [dbo].[MyDefaultTrace]
AS

begin    
    declare @rc int
    declare @TraceID int
    declare @maxfilesize bigint
    declare @filecount int
    set @maxfilesize = 100
    set @filecount = 5

    exec @rc = sp_trace_create @TraceID output, 2, 
        N'C:\Perflogs\MyDefaultTrace', 
        @maxfilesize, NULL, @filecount

    declare @on bit
    set @on = 1
    exec sp_trace_setevent @TraceID, 14, 7, @on
    exec sp_trace_setevent @TraceID, 14, 23, @on
    exec sp_trace_setevent @TraceID, 14, 8, @on
    exec sp_trace_setevent @TraceID, 14, 64, @on
    exec sp_trace_setevent @TraceID, 14, 1, @on
    exec sp_trace_setevent @TraceID, 14, 9, @on
    exec sp_trace_setevent @TraceID, 14, 21, @on
    exec sp_trace_setevent @TraceID, 14, 25, @on
    exec sp_trace_setevent @TraceID, 14, 41, @on
    exec sp_trace_setevent @TraceID, 14, 49, @on
    exec sp_trace_setevent @TraceID, 14, 57, @on
    exec sp_trace_setevent @TraceID, 14, 2, @on
    exec sp_trace_setevent @TraceID, 14, 6, @on
    exec sp_trace_setevent @TraceID, 14, 10, @on
    exec sp_trace_setevent @TraceID, 14, 14, @on
    exec sp_trace_setevent @TraceID, 14, 26, @on
    exec sp_trace_setevent @TraceID, 14, 66, @on
    exec sp_trace_setevent @TraceID, 14, 3, @on
    exec sp_trace_setevent @TraceID, 14, 11, @on
    exec sp_trace_setevent @TraceID, 14, 35, @on
    exec sp_trace_setevent @TraceID, 14, 51, @on
    exec sp_trace_setevent @TraceID, 14, 12, @on
    exec sp_trace_setevent @TraceID, 14, 60, @on
    exec sp_trace_setevent @TraceID, 15, 7, @on
    exec sp_trace_setevent @TraceID, 15, 15, @on
    exec sp_trace_setevent @TraceID, 15, 23, @on
    exec sp_trace_setevent @TraceID, 15, 8, @on
    exec sp_trace_setevent @TraceID, 15, 16, @on
    exec sp_trace_setevent @TraceID, 15, 64, @on
    exec sp_trace_setevent @TraceID, 15, 9, @on
    exec sp_trace_setevent @TraceID, 15, 17, @on
    exec sp_trace_setevent @TraceID, 15, 41, @on
    exec sp_trace_setevent @TraceID, 15, 49, @on
    exec sp_trace_setevent @TraceID, 15, 57, @on
    exec sp_trace_setevent @TraceID, 15, 6, @on
    exec sp_trace_setevent @TraceID, 15, 10, @on
    exec sp_trace_setevent @TraceID, 15, 14, @on
    exec sp_trace_setevent @TraceID, 15, 18, @on
    exec sp_trace_setevent @TraceID, 15, 26, @on
    exec sp_trace_setevent @TraceID, 15, 66, @on
    exec sp_trace_setevent @TraceID, 15, 3, @on
    exec sp_trace_setevent @TraceID, 15, 11, @on
    exec sp_trace_setevent @TraceID, 15, 35, @on
    exec sp_trace_setevent @TraceID, 15, 51, @on
    exec sp_trace_setevent @TraceID, 15, 12, @on
    exec sp_trace_setevent @TraceID, 15, 60, @on
    exec sp_trace_setevent @TraceID, 15, 13, @on
    exec sp_trace_setevent @TraceID, 15, 21, @on
    exec sp_trace_setevent @TraceID, 43, 7, @on
    exec sp_trace_setevent @TraceID, 43, 15, @on
    exec sp_trace_setevent @TraceID, 43, 8, @on
    exec sp_trace_setevent @TraceID, 43, 64, @on
    exec sp_trace_setevent @TraceID, 43, 1, @on
    exec sp_trace_setevent @TraceID, 43, 9, @on
    exec sp_trace_setevent @TraceID, 43, 41, @on
    exec sp_trace_setevent @TraceID, 43, 2, @on
    exec sp_trace_setevent @TraceID, 43, 10, @on
    exec sp_trace_setevent @TraceID, 43, 3, @on
    exec sp_trace_setevent @TraceID, 43, 11, @on
    exec sp_trace_setevent @TraceID, 43, 35, @on
    exec sp_trace_setevent @TraceID, 43, 51, @on
    exec sp_trace_setevent @TraceID, 43, 12, @on
    exec sp_trace_setevent @TraceID, 43, 5, @on
    exec sp_trace_setevent @TraceID, 43, 13, @on
    exec sp_trace_setevent @TraceID, 43, 6, @on
    exec sp_trace_setevent @TraceID, 43, 14, @on
    exec sp_trace_setevent @TraceID, 43, 62, @on
    exec sp_trace_setevent @TraceID, 45, 7, @on
    exec sp_trace_setevent @TraceID, 45, 8, @on
    exec sp_trace_setevent @TraceID, 45, 64, @on
    exec sp_trace_setevent @TraceID, 45, 1, @on
    exec sp_trace_setevent @TraceID, 45, 41, @on
    exec sp_trace_setevent @TraceID, 45, 10, @on
    exec sp_trace_setevent @TraceID, 45, 3, @on
    exec sp_trace_setevent @TraceID, 45, 11, @on
    exec sp_trace_setevent @TraceID, 45, 35, @on
    exec sp_trace_setevent @TraceID, 45, 51, @on
    exec sp_trace_setevent @TraceID, 45, 12, @on
    exec sp_trace_setevent @TraceID, 45, 5, @on
    exec sp_trace_setevent @TraceID, 45, 13, @on
    exec sp_trace_setevent @TraceID, 45, 6, @on
    exec sp_trace_setevent @TraceID, 45, 14, @on
    exec sp_trace_setevent @TraceID, 45, 62, @on
    exec sp_trace_setevent @TraceID, 45, 15, @on
    exec sp_trace_setevent @TraceID, 41, 7, @on
    exec sp_trace_setevent @TraceID, 41, 15, @on
    exec sp_trace_setevent @TraceID, 41, 8, @on
    exec sp_trace_setevent @TraceID, 41, 64, @on
    exec sp_trace_setevent @TraceID, 41, 1, @on
    exec sp_trace_setevent @TraceID, 41, 41, @on
    exec sp_trace_setevent @TraceID, 41, 10, @on
    exec sp_trace_setevent @TraceID, 41, 3, @on
    exec sp_trace_setevent @TraceID, 41, 11, @on
    exec sp_trace_setevent @TraceID, 41, 35, @on
    exec sp_trace_setevent @TraceID, 41, 51, @on
    exec sp_trace_setevent @TraceID, 41, 12, @on
    exec sp_trace_setevent @TraceID, 41, 5, @on
    exec sp_trace_setevent @TraceID, 41, 13, @on
    exec sp_trace_setevent @TraceID, 41, 6, @on
    exec sp_trace_setevent @TraceID, 41, 14, @on


    -- Set the Filters
    declare @intfilter int
    declare @bigintfilter bigint

    exec sp_trace_setfilter @TraceID, 11, 0, 6, N'BadUser'
    
    exec sp_trace_setstatus @TraceID, 1

end

GO

EXEC sp_procoption N'MyDefaultTrace', 'startup', '1'

GO

Now when I check to see what traces are running trace id #1 is the trace created in my procedure. The trace start is also written to the SQL error log when the master database starts.

As always, please test this on a server you can play with before putting into production.

Winning the DBA Open

2010-07-21T21:02:00.001-05:00

Last weekend I, like millions of other golf fans around the world, watched as Louis Oosthuizen, an unknown (at least to me) golfer out of South Africa, won The Open Championship at St Andrews by an astounding 7 strokes. He showed confidence in his abilities throughout the tournament, never losing his composure playing in some of the toughest conditions on any course in the world. Congratulations, Louis!

As i watched the game, I began to think that being a DBA and being a champion golfer weren’t that different. Both need to be confident in their abilities, both need to have a solid game plan, and both have to be able to execute their craft under pressure, being prepared for any eventuality.

Preparing for the tournament

A good golfer gets to the course early, days before the tournament begins. They play the course, talk to locals to learn any particular quirks the course may have, and practices hundreds of shots of different lengths or difficulties.

Good DBAs do the same. They find out about the host server, talking to the system team or SAN administrator; what kind of disk subsystem is there? what’s the memory or CPU like? does the server meet the requirements for SQL?

You drive for show…

The golfer starts his round on the tee. He’ll determine the club to use based on experience and conditions; where’s the wind coming from? how long is the hole? where’s the danger spots? A well hit drive will end up far down the fairway, perfectly placed for his next shot.

The DBA does the same when he sets up the server. His experience comes into play knowing what has to be done, and server conditions will help him to correctly place the system databases and data and log files for other databases. He’ll know what options to set and what to set them to. His server will be set up to optimal conditions, perfectly placed for hosting the company data.

…the approach shot…

Now the golfer will try to get his ball onto the green. He’ll again choose the right club, line up the shot, take a practice swing or two, then hits his best shot. He’ll know how his ball will fly because he has hit the same shot hundreds of times before so he can compare against past performance.

The DBAs approach shot is to benchmark his system. He should know how the server will perform during peak and non-peak times.

---getting out of a hazard…

Sometimes, though, the golfer’s approach shot is mishit. Or maybe a gust of wind holds up the shot and it lands in a greenside bunker. A good golfer knows how to get out of those situations and save par.

Similarly, a DBA must know how to get out of disaster scenarios. He knows he has set up and is maintaining a proper backup strategy and has practiced recovering databases until he knows the procedure inside and out. He knows the different options available for DBCC CHECKDB.

---and you putt for dough

This is where the good golfers shine. They don’t fade when standing over a three foot putt with the tournament on the line. They know they’ve made the same putt over and over before, and they’ve practiced for just this occasion. They read the putt’s line, judge the speed, and with millions of eyes watching they calmly knock it hit it center cup.

The DBA also is good under pressure. They’ve written and checked over their scripts, and they’re confidant they’ll perform as written. They don’t wilt when the CIO is standing over their shoulder asking why performance is so bad on their server.

The caddy…

All good golfers know the value of their caddy. A caddy’s job is to help his golfer, pointing out the course yardage, offering the right club, taking some of the pressure of the golfer. Sometimes the caddy is also a teacher, pointing out flaws in the golfer’s swing.

A DBA also needs a caddy. Their caddy can be a mentor or coworker at work. They can use social networks for advice on specific problems or as a sounding board.

Preparing for the next tournament

The golfer is always practicing. He’s at the driving range on his day off, constantly working on correcting swing flaws and working on new shots.

Likewise the DBA is constantly learning. They attend their local PASS chapter meetings or go to national conventions. They’re constantly reading blogs and whitepapers, learning new skills to help them manage their servers or help their company.

The moral? Like in golf, or any endeavor, the secret of being a good DBA is to be prepared. Learn your craft. Ask your caddy when you have questions. Practice when you’re able. Keep learning. And you too can win the DBA Open.

What I learned today – importing mixed data types from Excel

2010-07-19T16:38:00.001-05:00

Recently a coworker came up with an issue I never ran into before. He’s importing a large number of Excel spreadsheets into a SQL 2005 instance. One of the columns contains mixed data; 1, 2, 3, x, y, z for example. What he found is that if the first 20 or so rows are strictly numeric then the connection treats the whole column as numeric, and alphanumeric values are returned as NULLs. He’s querying using OPENROWSET, but it looks like this happens in SSIS as well.

What his research found was that there’s a few registry keys that come into play. There’s also another property he needed to set in his connection string. When he adds “IMEX=1” it allows importing of mixed data types based on the the registry setting “ImportMixedTypes”. The default value is “text”. One drawback to text is that you’re limited to 255 characters. The only other available value is “Majority Type”. When you use Majority Type some of the values are scanned and the data type with the most values is used.

The number of rows scanned is based on the “TypeGuessRow” registry setting. By default it’s set to 8, but valid values are 0 to 16. If you set it to 0 then all rows are sampled. Setting it to a value between 1 and 16 samples that number.

Both keys can be found at HKEY_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/.

Here are a few articles that explain the issue better.

http://jingyang.spaces.live.com/blog/cns!CC21A118B1B5250!318.entry?sa=781031802

http://www.instantpages.ltd.uk/ADODB_WP.htm

UPDATED 7/20/2010: You can read more about the actual issue here.

Changing the name of your SQL server

2010-07-14T07:22:00.001-05:00

My company recently changed their standard naming conventions for computers, so yesterday I had to rename my workstation. Usually this isn’t a big deal, except that I’m running locally a default instance of SQL 2005 and a named instance of SQL 2008.Again, not a big deal since this is just my local playground. But I wanted to sync up the names.

Let’s say that my laptop was named “CHICAGO”. That makes the default instance also “CHICAGO'”, and my named instance “CHICAGO\KATMAI”. Now my laptop name changed to “NEWCHICAGO”. My SQL instances stay as “CHICAGO” and “CHICAGO\KATMAI”. How do you change them to match the new computer name?

Couldn’t be simpler, just execute two procedures. For the default instance.

USE master;
GO

EXEC sp_dropserver 'CHICAGO';
GO

EXEC sp_addserver 'NEWCHICAGO', local;
GO

It’s the same for a named instance. Just add the instance name.

USE master;
GO

EXEC sp_dropserver 'CHICAGO\KATMAI';
GO

EXEC sp_addserver 'NEWCHICAGO\KATMAI', local;
GO
Then, just restart the SQL service and you should see the name change.

Note, though, that this only changes the server name, not the instance name.  I haven’t had an opportunity to try that yet.

Another use for PowerShell and SQL

2010-07-13T09:13:00.001-05:00

A few months ago I wrote a PowerShell script that looped through servers and databases to save the properties inside an Excel spreadsheet (PowerShell and SQL). I thought it’s time to add another one.

One of the daily tasks I set up for myself as a SQL DBA is to go through the error logs for anything that may be an indicator of a problem. This is especially true on Sunday afternoons or Monday mornings, after my weekly DBCC CHECKDB job runs. I’ve configured the jobs to email me the results, but since I’m monitoring 10 instances they can be time consuming to read through. I usually just cut to the bottom, where DBCC reports the number of errors it finds. If the number of errors is 0, all is well and good. Otherwise, of course, further investigation is needed.

This seemed like a natural for a new PowerShell script. One of the great strengths of PowerShell is it’s ability to connect to as many remote servers as needed. And since I knew that the DBCC CHECKDB summary is written in the error log all I need to do is to read the logs. I had read this post by Buck Woody (blog | twitter) about doing almost exactly what I wanted to do. I merged his script with my earlier script and made a few other changes. My final code looks like this:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 

#Get SQL account information
$credential = Get-Credential
$loginName = $credential.UserName -replace("\\","")  
$password = $credential.Password 

#Get variables
$logDate = read-host("Enter log date")
$searchPhrase = "*"
$searchPhrase += read-host("Enter search phrase")
$searchPhrase  += "*"
$srvlist = @(get-content "C:\Power\SQL_Servers.txt") 

#Loop through all instances 
foreach ($instance in $srvlist) 
{
  $instance
  $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
  $srv.ConnectionContext.LoginSecure = $false
  $srv.ConnectionContext.set_Login($loginName)
  $srv.ConnectionContext.set_SecurePassword($password)         
  $srv.ReadErrorLog(0) | where {$_.LogDate -gt $logDate -and $_.Text -like $searchPhrase} 
}

Since I don’t always access the servers from a computer on the domain, I added login credentials. You can remove these if you want to use Windows authentication. After that I read a date and search phrase I type in. I found that this can come in handy when I want to change what I’m searching for. For instance, I can enter the phrase “DBCC CHECKDB” and the script will return the summaries based on the date I entered. Or I can search for “BACKUP” or “ERROR”, whatever my heart desires.

Stop SQL Agent when enabling Service Broker on msdb

2010-07-06T11:02:00.001-05:00

I forget this every time I set up a new server.

When I set up database mail on anew server I usually get the message “Service Broker is not enabled on msdb. Do you want to enable it?” Clicking OK gives me an unresponsive SQL server. Running the command “ALTER DATABASE msdb SET Enable_Broker” runs forever with no message returned.

What I forget each and every time is that the SQL Agent is running. Checking the locks or running sp_who2 shows that my ALTER DATABASE is blocked by the process running the Agent. Simply stopping the Agent allows the statement to finish, whether from the query window or clicking OK in the dialog box mentioned earlier. Then I just restart the Agent and everything’s hunky-dory again.

Maybe now I’ll remember it.

My favorite SQL features

2010-07-05T10:35:00.001-05:00

Recently I ran across a contest sponsored by Microsoft SQL Server MVP Ken Simmons (Blog | Twitter). One of the ways to enter the contest was to create a blog post listing favorite SQL features. But how do you select a favorite? That’s like asking someone to choose a favorite song. Like most things the the SQL world, the correct answer is “it depends”, usually on what I’m trying to do at the time.

So here’s my top 10 favorite SQL features and tips, in the order that they come to mind. I’m ignoring features I’m not currently using, like backup compression and data encryption, and limiting myself to those I use the most often.

Integration with PowerShell. This is a great feature to administer multiple SQL instances easily. For those who haven’t been on the internet in the last few years, PowerShell is Microsoft’s new scripting language based on the .NET framework. And starting with SQL 2008, PowerShell became integrated with SQL. What does this mean to you as a DBA? Well, imagine yourself as the new head DBA for your company, and you’re now in charge of 100 SQL instances. Your boss tells you that your first task is to standardize all instances. How can you quickly view the properties of each server? With PowerShell and remoting, you can easily query each instance and save the results to an Excel spreadsheet for easy analysis. I gave an example of how I do it in an earlier blog post here.
Dynamic Management Views and Functions (DMV). Introduced with SQL 2005, DMVs are a great way to see how your server is performing. I’m not going to go into them in depth here since they’ve been written about by many others much better than I can. If you’re not familiar with them, do yourself a favor and start reading about them. They’ll make your life much easier.
Storing queries in the toolbox. Like all DBAs, I’ve got a number of queries that I run over and over again. There’s numerous ways to store these for easy access, but my favorite for short one-line queries is to store them on the toolbox. Simply open the toolbox (Ctrl + Alt + x) and drag your query over. To recall it, open the toolbox, copy the desired query, and paste it into a new query window.
Policies. Introduced in SQL 2008, Policy Based Management allows DBAs to manage multiple servers from a central server. Policies can standardize server configurations and check them for compliance.
Report Services. Built-in reporting capabilities for the same price as the database engine. ‘nuff said.
IntelliSense. This feature gives the same statement completion that you see in Visual Studio, not surprising since SQL Management Studio uses the same IDE and Visual Studio. Unfortunately it doesn’t work against 2005 servers or earlier. It did in the earlier betas but was removed in the final release. Hopefully Microsoft will put it back.
Object Explorer Details. You can now see details of different database objects using the details view (F7). It’s also configurable. Just right click on the screen and choose what columns you want to see.
Running queries against multiple instances. If you set up Registered Groups and register SQL instances by groups, you can execute a query against all servers in the group. You can select all groups, but you can’t choose to ignore instances inside the group. It’s all or nothing. But still, a significant timesaver when you need to run multiple statements against multiple instances.
Setting variable values. One of the nice things in the .NET world is the ability to use easily change the value of a variable; i += 1 for instance. Now you can do that in SQL too. Instead of

SET @i = @i + 1

you can now do it like this

SET @i += 1

SQL Community. Not technically part of SQL itself, but I feel it’s important nevertheless. I first began learning about SQL on my own and quickly found myself overwhelmed. There was no time to learn at work, and I needed to find the knowledge to do my job. I found the Chicago SQL User Group and found what I needed; experienced DBAs volunteering to talk about a subject they love. ChiSUG lead me to PASS, an international group with tons of free resources. I began following blogs written by the same people authoring the articles on PASS and presenting at the group meetings. Reading these blogs lead me to twitter, where I can follow SQL experts in real-time and ask for help with the #SQLhelp tag. Finally there’s SQL Saturdays, day-long events with SQL experts speaking on various topics. Notice the recurring theme about these resources; they are all free. I’m constantly amazed by the amount of content that SQL DBAs and developers make available for no cost, and the time they put in as volunteers at in-person events. To the entire SQL community, thank you. I wouldn’t be where I am today without your help.

PIVOT compared to CASE for a crosstab result

2010-06-29T14:35:00.001-05:00

Yesterday I was playing with a test query for a coworker using the PIVOT keyword when it dawned on me that I never compared it to using CASE statements. So, here’s the requirements.

There is a table called lab_results, with a partial DDL statement below

CREATE TABLE [dbo].[lab_results](
    [lab_result_id] [char](12) NOT NULL,
    [patient_id] [char](8) NOT NULL,
    [test_code] [numeric](4, 0) NOT NULL,
    [sample_date] [datetime] NULL,
    .............................
    .............................
 CONSTRAINT [PK_lab_results] PRIMARY KEY CLUSTERED 
(
    [lab_result_id] ASC
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_lab_results_3] ON [dbo].[lab_results] 
(
    [test_code] ASC,
    [sample_date] ASC
) ON [Secondary]

This table has approximately 18 million rows, and you need to get a count of test codes during each year. Anything prior to 1/1/2006 would be counted together. The data would look something like this

Prior to SQL 2005, I would use a series of CASE statements to perform a count of each test code

SELECT test_code
      , SUM(CASE WHEN DATEPART(yy, sample_date) = 2010 THEN 1 END) AS [2010]
      , SUM(CASE WHEN DATEPART(yy, sample_date) = 2009 THEN 1 END) AS [2009]
      , SUM(CASE WHEN DATEPART(yy, sample_date) = 2008 THEN 1 END) AS [2008]
      , SUM(CASE WHEN DATEPART(yy, sample_date) = 2007 THEN 1 END) AS [2007]
      , SUM(CASE WHEN DATEPART(yy, sample_date) = 2006 THEN 1 END) AS [2006]
      , SUM(CASE WHEN DATEPART(yy, sample_date) < 2006 THEN 1 END) AS [Pre2006]
FROM lab_results
GROUP BY test_code

Using the new (to SQL 2005 anyway) PIVOT keyword, I could rewrite the query like this

SELECT test_code, [2010], [2009], [2008], [2007], [2006], [2005] AS Pre2006
FROM 
(
      SELECT test_code, sample_date
      , CASE WHEN DATEPART(yy, sample_date) < 2006 THEN 2005
             ELSE DATEPART(yy, sample_date) 
        END AS LabResultYear 
      FROM lab_results
) AS tmp
PIVOT (COUNT(sample_date) FOR LabResultYear IN ([2010], [2009], [2008], [2007], [2006], [2005])) AS p;

But I was never looked at the execution plan before yesterday to see how each was run. So I finally did, and here’s what I saw. First the plan for the CASE statement…

…and then the plan for the PIVOT example.

At first glance they seem identical. Both are using the same operators and both are using an index seek on the non-clustered index. However there are small differences. The CASE statement completes 2 seconds faster than the PIVOT query, 45 seconds to 47. The index seek,while having an overall higher percentage of the whole, has the same operator costs as the PIVOT. The difference shows in the Stream Aggregate operator. The CASE operator is 10.8, which is 10% of the whole query. In the PIVOT example, the Stream Aggregate is 18% of the whole, and the operator cost is 19.8.

One other point about the PIVOT keyword. If you compare my code snippets to the screenshots of the executions, you’ll notice that I’m actually running against the master database and using a fully qualified name. If I ran it directly against the database where the table lives it would fail. Why? because that database, while running in on a SQL 2005 server, is still at compatibility level 7 (I know,I know). It doesn’t work unless you’re at level 9 or higher.

Report Server issues

2010-06-28T08:29:00.001-05:00

I’ve had a lot of Report Server issues in the last few months. I thought we solved it once, only to see it come up again in a slightly different way. But I think I can put it all to bed now.

The first time was a few months ago, right when I left on vacation (isn’t that always the way?). The C drive on one of our reporting servers began to run out of space, filling up 6 – 8 GB in a matter of hours.

Before I get too far ahead of myself, let me describe our layout. We have four regional databases running SQL 2005 Standard Edition, each with data for that region. Two regions are in our corporate headquarters, the other two are in my office. We also have two SQL 2008 Standard Edition servers that run in a virtual environment, one in each datacenter. The reports hosted on each reporting center are identical except for their data sources; the corporate report server fetches data from the two corporate databases, and the local server does the same. The issue we had was on the server in the corporate datacenter.

Our first step was to check the temp files. We noticed a few small files and one massive, 8 GB file that we couldn’t delete without stopping and restarting the report service. A few hours later the massive file reappeared. All of these temp files were prefixed “RSTemp_”.

We moved the location of the temp files to a larger drive to give us more time before the largest temp file grew too large. By default the location is on C:\Program Files\Microsoft SQL Server\MSRS10.<<Instance Name>>\Reporting Services. You can change it by modifying the rsreportserver.config file and adding the entry to the <Service> tag:

    <Service>
      ---
        <FileShareStorageLocation>
            <Path>I:\RSTempFiles</Path>
        </FileShareStorageLocation>
    </Service>

That gave us extra space, but it didn’t solve the issue. A little more digging showed that we had a long running report that was also getting bad data. The query for the report was looking for a scalar value but was returning multiples. The query was also a long running query. What we found was happening was that the report execution was timing out, but the query was still active. The user didn’t see the timeout and constantly tried to re-run the report. Consequently this report was continuously paged out to disk, and it never died. Once we cleaned up the data and fixed the reports query, the problem hasn’t reappeared.

The next issue was related to performance. We noticed at certain times of day the report server would get really sluggish. Reports that would complete in 10 seconds would now take 10 minutes. Looking at the server resources showed no increase in CPU, IO, or memory usage. We didn’t see a repeat of the super-big temp file, though there was an increase in the number of temp files.

A little more investigation showed that, when the performance began to degrade, there were locks being held by one process clearing the sessions that blocked another process that was writing session data. We were finally able to track this to a third party application we use to monitor performance. It didn’t look like the software was configured properly.

In investigating both of these issues we looked at the data in the ExecutionLogStorage table in the ReportServer database. If you’re not familiar with this table, you should check out Robert Bruckner’s excellent post on ExecutionLog2 View - Analyzing and Optimizing Reports.

In the first issue, we were able to identify the report by seeing the pagination values in the additional information data. In the second, we could spot when the slowness would begin by seeing the TimeProcessing values rise.

Catching up

2010-05-14T08:23:00.000-05:00

Nothing SQL related in this post, just clearing my mind and catching up.

First, my laptop went kablooie a few weeks back. I've been using a Pavilion TX 2000 that I ordered custom from HP for the past few years. I chose this model because I wanted a computer that could run the old Tablet XP OS, and I loved the swivel top that let me use it as either a notepad or as a full blown laptop (Apple, you need to add the ability to write on the iPad). Anyway, a few weeks ago my pc wouldn't boot at all, just kept giving me a black screen. I couldn't even get to the BIOS screen or boot from a CD. Luckily I had also bought an extended warranty that was still in effect. So, after opening a ticket with HP, I shipped the unit back to them, and they had it back to me about 10 days later. I was without the laptop for about 2 1/2 weeks, of which 1 was vacation time anyway. And not counting the week between when the unit first went bad and I shipped it back, I hardly missed it.

But I did miss it. I work mostly on my laptop at work and on my commute. This is my lab, where I can install my own programs and other beta apps. It's also the machine I blog from, mostly while on my commute. So it's good to have it back. I'm just finishing installing SQL 2008 R2, VS 2010, and Office 2010. I should be good to go by the time I go back to work on Monday.

Speaking about work, I did mention that I'm just finishing up a two-week vacation. The first week my wife and I took a cruise to the Mexican Riviera. We had a wonderful time. Perfect weather, great scenery, wonderful people. I'd like to go back soon and see some of the sights we missed this time around. This second week is just relaxing, doing yard work, mostly taking it easy.

Next week I'll write about an issue I ran across with our report server just before I left.

What I learned tonight – tempdb size when SQL starts

2010-04-15T21:36:00.001-05:00

I went to the April monthly meeting of the Chicago SQL Server User Group tonight to see Brad McGehee (Blog | Twitter). Brad’s in town to present at SQLSaturday # 31 in Chicago and he also gave a talk to us tonight on optimizing tempdb performance.

It was a good session because it went over tempdb and best practices. Most of the information I already knew but it’s always good to hear it again, even as a refresher. And there’s usually something I took for granted but was wrong about. And tonight was no exception.

I knew that the data and log files for tempdb are recreated when the SQL service starts. But I thought that tempdb got it’s db options from the model database. For instance, if the model database is set at 2 MB for the data and 1 MB for the log then tempdb would inherit those same settings. But i was wrong. As Brad mentioned tonight, tempdb starts out as 8 MB for the data and the log file is 1 MB. On my laptop its’s actually 512 KB but maybe I heard him wrong.

Anyway, to properly resize tempdb you should run the ALTER DATABASE command…

USE master;
GO

ALTER DATABASE tempdb 
MODIFY FILE ( NAME = 'tempdev', SIZE = 25600KB , FILEGROWTH = 5120KB );
GO

ALTER DATABASE tempdb 
MODIFY FILE ( NAME = 'templog', SIZE = 2048KB , FILEGROWTH = 1024KB );
GO

…and tempdb will be correctly resized after every restart. And you’ve heard that you should create multiple tempdb files depending on the number of CPU’s you have? Well, just do that at the same time. Here I’m renaming, moving, resizing, and adding a file to tempdb;

USE master;
GO

ALTER DATABASE tempdb 
MODIFY FILE (NAME = 'tempdev', NEWNAME = 'tempdev1', FILENAME = 'C:\SQL2008\DATA\tempdev1.mdf');
GO

ALTER DATABASE tempdb 
MODIFY FILE (NAME = 'templog', FILENAME = 'C:\SQL2008\DATA\templog.ldf');
GO

ALTER DATABASE tempdb 
MODIFY FILE (NAME = 'tempdev1', SIZE = 25600KB , FILEGROWTH = 5120KB);
GO

ALTER DATABASE tempdb 
MODIFY FILE (NAME = 'templog', SIZE = 2048KB , FILEGROWTH = 1024KB);
GO

ALTER DATABASE tempdb 
ADD FILE (NAME = 'tempdev2', 
          FILENAME = 'C:\SQL2008\DATA\tempdev2.ndf', 
          SIZE = 25600KB , FILEGROWTH = 5120KB);
GO

The usual disclaimers go here. Don’t use this script without understanding it as it can screw up your server. Don’t use it in production until you’ve testes it.

And I’ll leave the discussion of multiple tempdb files for now.

SQL error log finds all copies of resource db after moving db

2010-04-13T09:11:00.001-05:00

Just a short post about something interesting I saw in the SQL error log a while back.

A while ago, I moved the system databases off the C drive’s of our servers to another drive on the SAN. That included moving the resource database. You can read Microsoft’s recommendations on moving system db’s on MSDN.

Anyway, I copied the mssqlsystemresource.mdf and mssqlsystemresource.ldf files to a backup location in case of an emergency. But only one database was attached.

This interesting entry in the SQL error log was after I restarted the server:

The resource database has been detected in two different locations. Attaching the resource database in the same directory as the master database at 'O:\SQL2005\DATA\master.mdf' instead of the currently attached resource database at 'O:\SQL2005\Data\mssqlsystemresource.mdf'.

So, SQL saw both the attached db and the unattached backup copy when I restarted the service. The only time I saw the message was the first reboot, not any subsequent restarts.

Using the Inserted and Deleted tables outside of a trigger

2010-03-24T18:17:00.001-05:00

Just a simple little post about a conversation about triggers I had earlier today with a developer, specifically using them to audit update and delete statements against a table. During our talk I mentioned that, starting in SQL 2005, the inserted and deleted tables were available outside of a trigger. He wasn’t aware of that until I mentioned it. So, for anyone else who’s not familiar with the inserted and deleted tables, here’s a little summary.

Briefly, when you insert, update, or delete records from a table, SQL stores those records in a virtual table; inserted for inserts and deleted for updates and deletes. These tables are available inside of table triggers. Microsoft SQL Books Online has an article explaining the use of the inserted and deleted tables inside of triggers; read that first as a refresher. In our shop we use them for monitoring who is updating and deleting certain critical tables, and we can roll back changes if necessary.

Starting with SQL 2005, you can use the OUTPUT clause to access the inserted and deleted tables outside of DML triggers. You can read the article in Books On Line.

Here’s a simple demo script to run. I added a primary key and a non nullable column to show what happens in error conditions.

USE tempdb;
GO

SET NOCOUNT ON;
GO

-- Create a testing table
USE [tempdb]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestOutput]') AND type in (N'U'))
DROP TABLE [dbo].[TestOutput]
GO

USE [tempdb]
GO

CREATE TABLE [dbo].[TestOutput](
    [Field1] [int] NOT NULL,
    [Field2] [char](1) NOT NULL,
    CONSTRAINT [PK_TestOutput] PRIMARY KEY CLUSTERED 
    (
       [Field1] ASC
    )ON [PRIMARY]
) ON [PRIMARY]

GO

--Testing of Outupt statement
DECLARE @TestOutput_Audit TABLE
(
      Field1        INT,
      Field2        CHAR(1),
      [Action]        CHAR(1),
      Action_by        VARCHAR(25),
      Action_Date    DATETIME
)

-- Testing Insert
INSERT INTO TestOutput (Field1, Field2)
OUTPUT inserted.Field1, inserted.Field2, 'I', SUSER_NAME(), GETDATE()
INTO @TestOutput_Audit (Field1, Field2, [Action],Action_by, Action_Date)
VALUES (1, 'A')

INSERT INTO TestOutput (Field1, Field2)
OUTPUT inserted.Field1, inserted.Field2, 'I', SUSER_NAME(), GETDATE()
INTO @TestOutput_Audit (Field1, Field2, [Action],Action_by, Action_Date)
VALUES (1, 'A')

SELECT * FROM TestOutput
SELECT * FROM @TestOutput_Audit

-- Testing Update
UPDATE TestOutput
SET Field2 = 'B'
OUTPUT deleted.Field1, deleted.Field2, 'U', SUSER_NAME(), GETDATE()
INTO @TestOutput_Audit (Field1, Field2, [Action],Action_by, Action_Date)
WHERE Field1 = 1

UPDATE TestOutput
SET Field2 = NULL
OUTPUT deleted.Field1, deleted.Field2, 'U', SUSER_NAME(), GETDATE()
INTO @TestOutput_Audit (Field1, Field2, [Action],Action_by, Action_Date)
WHERE Field1 = 1

SELECT * FROM TestOutput
SELECT * FROM @TestOutput_Audit

-- Testing Delete
DELETE TestOutput
OUTPUT deleted.Field1, deleted.Field2, 'D', SUSER_NAME(), GETDATE()
INTO @TestOutput_Audit (Field1, Field2, [Action],Action_by, Action_Date)
WHERE Field1 = 1

SELECT * FROM TestOutput
SELECT * FROM @TestOutput_Audit

The INSERT statement simply inserts a single record into my table. Afterwards you can see the same record in the table as well as my audit table. The second record wasn’t added to either table because it’s a primary key violation.

Msg 2627, Level 14, State 1, Line 18

Violation of PRIMARY KEY constraint 'PK_TestOutput'. Cannot insert duplicate key in object 'dbo.TestOutput'.

The statement has been terminated.

Field1 Field2

----------- ------

1 A

Field1 Field2 Action Action_by Action_Date

----------- ------ ------ ------------------------- -----------------------

1 A I PERTELLT3\John 2010-03-24 17:58:03.407

I make a simple UPDATE. Now the table shows the update and the audit shows the value before the update took place. The second update fails because Field2 doesn’t allow NULL values; the main record isn’t updated nor added to the audit table.

Msg 515, Level 16, State 2, Line 33

Cannot insert the value NULL into column 'Field2', table 'tempdb.dbo.TestOutput'; column does not allow nulls. UPDATE fails.

The statement has been terminated.

Field1 Field2

----------- ------

1 B

Field1      Field2 Action Action_by                 Action_Date

----------- ------ ------ ------------------------- -----------------------

1           A      I      PERTELLT3\John            2010-03-24 17:58:03.407

1           A      U      PERTELLT3\John            2010-03-24 17:58:03.413

Finally, when I delete the record I can see the record is gone from the main table but stored in the audit table.

Field1 Field2

----------- ------

Taking 70-448 SQL BI Exam

2010-03-22T09:10:00.001-05:00

One of my goals has been to learn Microsoft’s SQL Business Intelligence stack. I’ve been reading different books, blogs and whitepapers to learn as much as I can, but I wasn’t very structured in my approach. While I picked up lots of new information and tips, I felt I was spinning my wheels. And I keep getting sidetracked with work projects and learning the basics of other new technologies.

I’ve been successful in the past with Microsoft certifications. Beginning way back in the SQL 7 days, I took and passed exams to achieve MCP, MCDBA, and MCSD status. I also took and passed SQL 2005 Database Administrator and Database Developer exams a few years back.

I’ve been pretty involved with Reporting Services since it first came out as an add on to SQL 2000. But I’ve only played around briefly with the old DTS and newer Integration Services. And I’ve never even opened an Analysis Services database. So overall I have a long way to go.

So I’ve settled on a method that I believe will help me. I’ve decided to take the 70-448 exam, Microsoft SQL Server 2008 – Business Intelligence Development and Maintenance. I feel by studying for this specific exam I’ll get a head start on learning BI. I know I’m not going to learn everything there is about BI with one exam, but it should give me enough background to go ahead and finish up on my own. And setting a goal of passing 70-448 will, hopefully, keep me on track.

Now when I took other exams I also struggled to keep to my studies. It was only when I actually scheduled the exam that I really sat down and concentrated. I’m not going to do that just yet. For the SQL 2005 exams I passed I knew enough that I could get by with using only the Microsoft Press Training Kits. I don’t feel anywhere near that comfortable with 70-448. But I’m still going to set a date. Today is Monday, March 22, 2010. I’m going to give myself just over 6 months to take and pass this exam. I’ll schedule the test on August 22nd, and I will pass the test by October 1st. That will also give me a little leeway if I fail the first time.

I plan on blogging on my progress for 3 reasons. First, if I write about it, it will help me understand the concept better. As someone once said, you don’t understand a concept until you can explain it to your mother. Second, it should keep me on schedule. I don’t want to get to October 1st and admit publically that I didn’t take the test. And third, it will act as a study group for others who also want to take 70-448. The more the merrier.

Resources

I’ve only got a few resources so far. I’ll add to this list as I go.

The first is the Microsoft Press 70-448 Training Kit, written by Eric Veerman, Teo Lachev, and Dejan Sarka of Solid Quality Mentors. I’ve always liked the MS Press TK’s. They come with practice exams, as well as a coupon for 15% your test.

The second book I’ll be using is Knight’s 24-Hour Trainer, published by Wrox and written by Brian Knight, Devin Knight, and Mike Davis. I chose this book because it includes a CD that contains videos of the lessons.

There will be more resources listed later, as I progress towards my personal D-Day.

PowerShell and SQL

2010-03-16T14:10:00.003-05:00

When PowerShell was first released a few years back I wasn’t too excited. I didn’t use scripting very much, mainly for cleaning out old backup files from our archived storage. I thought that using VBScript would suffice for my limited means. But I started reading up on PowerShell V1, even tinkering around with it a bit. However learning PowerShell soon dropped down on my “Must Learn” technologies. There was just too many other things being introduced.

My mind didn’t change much when PowerShell V2 was in beta. Then Microsoft told us how PowerShell was going to be integrated into SQL Server 2008 (among other products) and PowerShell began moving back up my list. I’d read articled on MSDN and blogs by SQL experts, but my thinking never really changed. I figured that PowerShell was just one more way of doing things I already did in other ways.

One of the items on my “To Do” list has always been to document the servers I’m responsible for. I toyed with different methods of gathering the information, from running SSIS packages to a central repository to executing T-SQL queries in a Reporting Service report to manually (and tediously) typing the data into Word docs.

Then I read an article by Edwin Sarmiento on MSSQL Tips. In his article, he gave an example of using SMO inside PowerShell to write the information to an Excel spreadsheet. Finally the light bulb went on; I could use PowerShell to create my Run Book!

Now I’m not going to go into an in-depth explanation on PowerShell, or SMO. There’s ton’s of information available, and they explain much clearer than I can. I’m still learning myself. Check out Allen White (blog), Buck Woody (blog | twitter), Aaron Nelson (blog | twitter) and many others that I’m not mentioning here. I just wanted to share the method I’m beginning to use. It’s still evolving as I learn new tricks.

The basis of my script was the one used by Edwin Sarmiento in this article. I’ve added a few things of my own to make it more like what I want. For instance I add server information as well as database information. And I put each server onto a different worksheet.

The first thing my script does is to load the SMO namespace. After that I use the Get-Credential cmdlet to get an account to connect to the servers (I won’t always be connecting with Windows Authentication), create an Excel object and get a list of servers from a text file. After that I make sure that there’s enough worksheets in the workbook for all servers listed. Since a new Excel workbook has 3 worksheets by default I start my loop at 4.

#Get list of servers 
$srvlist = @(get-content ".\SQL_Servers.txt")

#Counter variable for rows
$c = $srvlist.Count 
$intRow = 1

#Verify there's a sheet in the workbook for each server 
for ($i = 4; $i -le $c; $i++) 
{
$Workbook.Sheets.Add() 
}

Then it’s just a matter of looping through the servers and databases, writing the values to the workbooks and applying formatting. My final step is to rename each sheet.

$name = $instance -replace("\\", "-")
$Sheet.Name = $name

Here’s an example of what one of my sheets looks like…

…and here’s the whole script. For this example I stopped at 7 columns so it would all show on the screen without scrolling. But my final script will show more information about both the servers and the databases.

#region LoadAssemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
#endregion

#Get SQL account information
$credential = Get-Credential
$loginName = $credential.UserName -replace("\\","")   $password = $credential.Password

$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $False
$Workbook = $Excel.Workbooks.Add()

#Get list of servers 
$srvlist = @(get-content ".\SQL_Servers.txt")

#Counter variable for rows
$c = $srvlist.Count 
$intRow = 1

#Verify there's a sheet in the workbook for each server 
for ($i = 4; $i -le $c; $i++) 
{
$Workbook.Sheets.Add() 
}

$a = 1
#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in $srvlist) 
{
$Sheet = $Workbook.Worksheets.Item($a) 
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
$srv.ConnectionContext.LoginSecure = $false
$srv.ConnectionContext.set_Login($loginName)
$srv.ConnectionContext.set_SecurePassword($password)    
#set headers
$Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,2) = $instance
$Sheet.Cells.Item($intRow,2).Font.Bold = $True
$intRow += 1
$Sheet.Cells.Item($intRow,1) = "VERSION:"
$Sheet.Cells.Item($intRow,2) = "EDITION:"
$Sheet.Cells.Item($intRow,3) = "COLLATION:"
$Sheet.Cells.Item($intRow,4) = "OS VERSION:"
$Sheet.Cells.Item($intRow,5) = "PLATFORM:"
$Sheet.Cells.Item($intRow,6) = "PHYS MEM:"
$Sheet.Cells.Item($intRow,7) = "NUM CPU:"
for ($col = 1; $col –le 7; $col++)
{
$Sheet.Cells.Item($intRow,$col).Font.Bold = $True
$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
}

#get values
$intRow += 1

$Sheet.Cells.Item($intRow,1) = $srv.Information.Version 
$Sheet.Cells.Item($intRow,2) = $srv.Information.Edition
$Sheet.Cells.Item($intRow,3) = $srv.Information.Collation
$Sheet.Cells.Item($intRow,4) = $srv.Information.OSVersion
$Sheet.Cells.Item($intRow,5) = $srv.Information.Platform
$Sheet.Cells.Item($intRow,6) = $srv.Information.PhysicalMemory
$Sheet.Cells.Item($intRow,7) = $srv.Information.Processors

$intRow += 2

$Sheet.Cells.Item($intRow,1) = "DATABASES"
$Sheet.Cells.Item($intRow,1).Font.Bold = $True

$intRow += 1

$Sheet.Cells.Item($intRow,1) = "DATABASE NAME"
$Sheet.Cells.Item($intRow,2) = "COLLATION"
$Sheet.Cells.Item($intRow,3) = "COMPATIBILITY LEVEL"
$Sheet.Cells.Item($intRow,4) = "AUTOSHRINK"
$Sheet.Cells.Item($intRow,5) = "RECOVERY MODEL"
$Sheet.Cells.Item($intRow,6) = "SIZE (MB)"
$Sheet.Cells.Item($intRow,7) = "SPACE AVAILABLE (MB)"
for ($col = 1; $col –le 7; $col++)
{
$Sheet.Cells.Item($intRow,$col).Font.Bold = $True
$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
}
$intRow += 1

$dbs = $srv.Databases
ForEach ($db in $dbs) 
{
#Divide the value of SpaceAvailable by 1KB 
$dbSpaceAvailable = $db.SpaceAvailable/1KB 
#Format the results to a number with three decimal places 
$dbSpaceAvailable = "{0:N3}" -f $dbSpaceAvailable
$Sheet.Cells.Item($intRow, 1) = $db.Name
$Sheet.Cells.Item($intRow, 2) = $db.Collation
$Sheet.Cells.Item($intRow, 3) = $db.CompatibilityLevel
#Change the background color of the Cell depending on the AutoShrink property value 
if ($db.AutoShrink -eq "True")
{
$fgColor = 3
}
else
{
$fgColor = 0
}
$Sheet.Cells.Item($intRow, 4) = $db.AutoShrink 
$Sheet.Cells.item($intRow, 4).Interior.ColorIndex = $fgColor
$Sheet.Cells.Item($intRow, 5) = $db.RecoveryModel
$Sheet.Cells.Item($intRow, 6) = "{0:N3}" -f $db.Size
#Change the background color of the Cell depending on the SpaceAvailable property value 
if ($dbSpaceAvailable -lt 1.00)
{
$fgColor = 3
}
else
{
$fgColor = 0
}
$Sheet.Cells.Item($intRow, 7) = $dbSpaceAvailable 
$Sheet.Cells.item($intRow, 7).Interior.ColorIndex = $fgColor

$intRow += 1

}
$Sheet.UsedRange.EntireColumn.AutoFit()
$name = $instance -replace("\\", "-")
$Sheet.Name = $name
$intRow = 1
$a += 1
}
#Save file and close Excel. 
$xlExcel8 = 56
$timeStamp = Get-Date -Format "yyyyMMdd_HH_mm"
$fileName = ".\ServerInfo_" + $timeStamp + ".xls"
$Workbook.SaveAs($fileName, $xlExcel8)
$Excel.Quit
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)

I’ve run this script using PowerShell V2 and Excel 2007 and the Excel 2010 beta with no problems. I don’t believe there’s anything specific to V2 in the script. But I do have a warning, though. If, while you run the script, you get impatient and click somewhere inside Excel, you’ll get an error and the script will stop (NOTE TO SELF: Add error handling!). The error is HRESULT 800ac472 and it’s an Excel error, not PowerShell.

Issues installing SQL 2005 SP3

2010-03-01T13:12:00.001-06:00

Before I get to the details, I’d like to ask a few questions. I’d be interested in hearing from anyone who’s had problems installing SQL service packs or hotfixes. Has anyone else had the errors I’m mentioning below, or anything similar? What procedures or best practices do you follow when installing patches?

Last week I finally was able to install SQL 2005 SP3 on our production servers. I’ve installed countless service packs and hotfixes in the past on numerous servers both physical and virtual, development and production. And I’ve never run into the problems I had last week.

In this case, I’m upgrading four production sites from SQL 2005 SP2 with the CU7 hotfix installed, build 3239 (see this article from SQLServerCentral that matches build numbers to service packs). When I installed SP3 on our dev and test servers I received the usual warning about running software. So last week I tried to stop the SQL services on the first 2 servers. This brought up Error #1: The services were telling me that I lacked the proper permissions to stop the SQL service. Now my account is an administrative account on the servers so it has plenty of rights, certainly enough to stop the service. ISP3.

Now comes Error #2: The system databases on a SAN drive were read only, and again I didn’t have proper permissions to rewrite them! Again, I’m an administrator and should have been able to do this with no problems. Reading the install logs just gave me the same information. And again, this error only occurred on the first 2 servers, not the final 2.

Next I logged off the servers and logged back in using the same account that the SQL services run under. I continued to get the same 2 errors on Server 1, but Server 2 let me complete the installation. Still no errors at all on Servers 3 or Server 4. Now I’m getting concerned.

My next step was to restart Server 1. After the server came back up, I could finally install the service pack. When I finished I checked the Windows and SQL error logs for any clues that would explain why my permissions were being denied. I must admit my Windows admin skills aren’t the greatest so I probably missed the cause. I did notice that the anti-virus program we run had similar issues at the same time, but the anti-virus doesn’t hit the database files and I wasn’t trying to do anything with the anti-virus service. My guess is that somehow Server 1 and Server 2 somehow couldn’t validate my account after I logged in.

Anyway, I thought I was finished. Not quite. My final step was to verify the new version. So I ran SELECT SERVERPROPERTY('ProductLevel') and all four servers returned SP3. The build number now showed as 9.00.4035. All looks good, right? A few days later I ran EXEC sp_server_info.

Finally we get to Error #3. On Server 2, remember that I had problems at first with permissions but I thought the SP installed correctly on the 2nd try. But now looking at the value for DBMS_VER and SYS_SPROC_VERSION I’m seeing the build is still showing 9.00.3239! Why? I’m not sure. I think it’s due to 1 of 2 reasons.

First, even though the SP3 install looked to be successful the second time it somehow skipped a component. During the reinstall I thought I selected all components, but it’s possible that I only thought I did, skipping something. NOTE TO SELF: for future installs document what components you’re installing. At least take a screenshot of what I’ve checked.

The second possibility is because of Analysis Services. SSAS was installed on Server 2 but the service is disabled. That makes sense since we’re not using SSAS currently, but the reason it was installed on just one server is lost to the mists of time. In any case the SP3 install logs show that SSAS was disabled and it’s at build 3239. But by that logic it would only be as 3239 if it was running during other patches. Again, I can’t be sure.

How I save table sizes for all databases

2010-02-16T11:43:00.003-06:00

Back when I first started monitoring our production servers, I wanted a way to track the size and growth of all tables in our databases. At the time, we were really only worried about 1 legacy database, and all the objects in that database were in the dbo schema. So tracking size growth was pretty easy; I ran the following script against that database weekly and compared results.

declare @TableName    sysname

declare mySpace cursor for
select name from sys.sysobjects where xtype = 'U' 
order by name
open mySpace
fetch next from mySpace into @TableName
while @@fetch_status = 0
begin
insert into PerfDB.dbo.TableSpace(TableName, NumRows, Reserved, DataUsed, IndexUsed, Unused)
exec sp_spaceused @TableName
fetch next from mySpace into @TableName
end
close mySpace 
deallocate mySpace

After a while, we began to create new projects that required new databases. And these databases had objects that were created into separate schemas. So now, if I wanted to track changes to all tables in all databases, I’d have to run the above code against all databases. Do-able, certainly, but not exactly what I wanted to maintain. I’d have to add the code to each new database we added to the server. And sp_spaceused only returns the name of the table, not the schema name. I wanted to separate the tables by database as well as schema.

So I played around with using two well known undocumented procedures, sp_MSForEachDB and sp_MS_ForEachTable. I’ve used them before, but I never nested foreachtable inside foreachdb. And I still needed a way to separate the schema from the table name. And I wanted to get away from using a cursor.

I found the following script on a SQLServerCentral forum that gave me an idea. Running this script showed that the value of ‘?’ when running sp_MSForEachTable showed the schema name as well as the table name.

declare @command1 varchar(8000)
select @command1 = 
'
IF ''@'' <> ''master'' AND ''@'' <> ''model'' AND ''@'' <> ''msdb'' AND ''@'' <> ''tempdb'' 
begin
use [@] execute sp_MSForEachTable ''print ''''?'''' '' 
end    
'
exec sp_MSforeachdb @command1, '@'

So now I can just run sp_spaceused inside the sp_MSForEachTable block and strip out the schema into a separate column.

Here’s the code I use now

CREATE TABLE  #TableSpace (
DatabaseName    nvarchar(128) NULL,
SchemaName        nvarchar(128) NULL,
TableName        nvarchar(128) NOT NULL,
rows            int NOT NULL,
reserved        varchar(15) NOT NULL,
data            varchar(15) NOT NULL,
index_size        varchar(15) NOT NULL,
unused            varchar(15) NOT NULL)
DECLARE @SQL varchar(8000)
SELECT @SQL = 
'
IF ''@'' <> ''master'' AND ''@'' <> ''model'' AND ''@'' <> ''msdb'' AND ''@'' <> ''tempdb'' 
BEGIN
USE [@] EXECUTE sp_MSForEachTable ''INSERT INTO #TableSpace (TableName, rows, reserved, data, index_size, unused) EXEC sp_spaceused ''''?''''; 
UPDATE #TableSpace SET SchemaName = LEFT(''''?'''', CHARINDEX(''''.'''', ''''?'''', 1) - 2) WHERE SchemaName IS NULL;
UPDATE #TableSpace SET DatabaseName = ''''@'''' WHERE DatabaseName IS NULL; ''
END    
'
EXEC sp_MSforeachdb @SQL, '@'

In my example I’m creating a temporary table, but in practice I use a permanent table. Line 16 is obvious, it skips the system databases. Line 18 calls the sp_spaceused procedure for each table inside each database.

Line 19 is where I strip out the schema name. I just use CHARINDEX function to return the position of the dot separator, then I use the LEFT function to return the characters before it. Since ‘?’ returns the table name as [Schema].[TableName] I also want to skip the brackets, so I move the position one more place to the left to take care of the ] character. I could have been even fancier and did even more string manipulation to take care of the [ character, but it was just as easy to use the REPLACE function on the whole column.

And Line 20 stores the database name. Running the script on my laptop running SQL 2008 gives me results like these

I’ve run this against SQL 2005 and SQL 2008 servers. I haven’t tested it against SQL 2000. As with any code you find on the internet, please test it and become familiar with it before you put it into production.

My Review of Windows PowerShell™ 2.0 Best Practices

2010-02-09T14:06:00.001-06:00

Originally submitted at O'Reilly

Learn field-tested solutions, best practices, and proven techniques for Windows PowerShell 2.0—including expert tips and lessons learned from the insiders on the Windows PowerShell Team at Microsoft.

First impressions

By jaype from Chicago IL on 2/9/2010

Pros: Easy to understand, Accurate, Well-written, Concise, Helpful examples

Best Uses: Expert, Intermediate

Describe Yourself: Developer, Sys Admin

I'm just beginning to read this book and I thought I'd add my first impressions. I'm a SQL Server DBA and I'm looking to learn Powershell as a means of remotely administering my servers. I've only used Powershell briefly so I'm far from an expert. I'm hopeful this book will help my understanding.

First off, this book is not a book for beginners. A background in Powershell is helpful. I'm having to look up some of the concepts. But that's not the fault of the book, and the authors say as much in the Introduction. My only suggestion for other Powershell beginners is to read Chapter 6, on configuring the environment, first.

I've finished the first two chapters so far and I find the book is well written and informative. Chapter 1 is about Version 2, what's new, and how to deploy it on different OS's. It also shows how to use a version tag for backwards compatibility with version 1. Chapter 2 shows how to use WMI functionality and how remoting works. Remoting is also new to Version 2.

There's 2 things that make this book invaluable as a reference. First, the appendices are full of great information. For example, Appendix A lists all the cmdlets in Version 2 with a short description. Second, on the companion CD, there's a Quick Reference Guide pdf file that summarizes the points of each chapter.

I'll update my review after I finish the book.

(legalese)

Using the RELOG utility – Part 2

2010-01-18T19:10:00.001-06:00

In my previous post (What I Learned Today - the RELOG utility) I discussed what I discovered the first time I learned about the RELOG utility. In this post I’ll continue with my education. Again, I’ve used Ask The Performance Team, Microsoft TechNet, and this blog post at My Torn Data Pages as my references.

I found that I could output a list of counters from a perfmon counter log using RELOG (RELOG counterfilename –q –o counterfilename). But you can take that a step further. Simply edit your counter file and delete the counters you don’t want. Then rerun RELOG and use the –cf switch to import a list of counters to filter for. My examples use the same counter log file as before:

RELOG SQL1_20100106.csv – q –o counters.txt -- to get my list of counters

RELOG SQL1_20100106.csv –cf counters.txt –c csv –o filtered1.csv -- to filter by those counters after editing the counters.txt file.

And of course I can still filter by time with the –b and –e switches:

RELOG SQL1_20100106.csv –cf counters.txt -b "1/5/2010 9:00:00AM" -e "1/5/2010 10:00:00 AM" –f csv -o filtered2.csv

You can save your settings in a config file…

[cf]
counters.txt
[b]
1/5/2010 9:00:00AM
[e]
1/5/2010 10:00:00AM
[f]
csv
[o]
configresults.csv

…and use just the –config switch
RELOG SQL1_20100106.csv –config relogconfig.txt

Now there’s plenty of ways to filter your perfmon files. The last way is to save the results in a SQL database.

Inserting into a SQL database

The –o switch specifies where to output the results of a perfmon counter log file. You have the option to export to a flat file, which I’ve been doing so far, or directly to a database. And that’s simple. First, create a DSN pointing to the server and database of your choice. Stan Segers, whose blog I’ve been referencing at My Torn Data Pages, is correct in noting that you need to use the SQL Driver. Like Stan, I wasn’t able to connect if I chose the SQL Native Client. Then, when you use the –o switch, specify SQL: and the name of your DSN. In the example my counter name is Counters.

RELOG SQL1_20100106.csv –cf counters.txt –o:SQL:Counters

RELOG will create these 3 tables:

The tables are created the first time you run RELOG to that database. The utility generates a GUID for each run. Each time you run it it will insert records into the CounterDetails and CounterData tables. But it won’t insert into or update the DisplayToID table. So to keep a record of each time you run the utility, specify a different DisplayString. Everything after the exclamation point (!) is the string.

RELOG SQL1_20100106.csv –cf counters.txt –o:SQL:Counters!DCICHISQL1

Now that all my performance counters are in the database, I can easily query against them.

I hope I’ve shown you ways to use the RELOG utility. As you can see, it’s pretty powerful.

What I learned today – the RELOG utility

2010-01-18T08:52:00.001-06:00

At the Chicago SQL User group meeting on 1/14/2010 John Jones of NetApp gave a talk on storage performance. it was an informative meeting. For instance, did you know that the latency of a 15K Fibre Channel drive is the same as a 15K SAS drive? But SAS drives are smaller; NetApp is able to put 24 drives in a 4 U rack.

During the presentation, John mentioned the RELOG utility and how it can convert a perfmon counter log from the default .blg extension to a .csv file. I’d never heard of RELOG before so I did a little reading up on it. I googled RELOG and found lots of information. The sources I used for this article were Ask The Performance Team, Microsoft TechNet, and this blog post at My Torn Data Pages. And I found that RELOG can do much more than just reformat a file. It can also filter the original counter log to show data from a sample time slice or only pull out certain counters.

RELOG is a command line utility. It’s available in Windows XP or higher machines, and it’s located in the \system32 directory. If you don’t have it you can get from Microsoft downloads. Also the utility isn’t just for SQL counters. you can run it against any perfmon counter file.

Here’s a list of the switches that RELOG uses:

In my examples I’m using a benchmark counter log I capture from our production servers. It’s already saved as a .csv file so I don’t need to convert it but I certainly could if I needed to. Also I’m typing RELOG in caps, though it’s not necessary.

Using RELOG with just the filename of the counter log will give you the start and end time of the capture plus the number of samples in the file…

…and using the –q switch will list the counters used in the file.

Adding the –o switch and specifying a file name will output the counter names to a separate file (RELOG SQL1_20100106.csv –q –o counters.txt).

So now that I know the times and the counters in my baseline, I can filter for the counters between 9 and 10 AM on 1/5(RELOG SQL1_20100106.csv -b "1/5/2010 9:00:00AM" -e "1/5/2010 10:00:00 AM" –f csv -o SQL1_filtered.csv). I can see that I’ve filtered out most of the sampling and now I’ve got the 237 samples I took between 9 and 10.

And I can also filter by individual counters.Let’s say I want to see the transactions per second against a specific database between 9 and 10 AM: RELOG SQL1_20100106.csv -b "1/5/2010 9:00:00AM" -e "1/5/2010 10:00:00 AM" -c "\MSSQL$MISDB:Databases(tempdb)\Transactions/sec" –f csv -o SQL1_filtered2.csv. Running this, I see the same 237 samples returned, but if I open the file, I’ll just see the counters I asked for:

I can also use a wild card to see the transactions/sec counters against all databases: RELOG SQL1_20100106.csv -b "1/5/2010 9:00:00AM" -e "1/5/2010 10:00:00 AM" -c "\MSSQL$MISDB:Databases(*)\Transactions/sec" –f csv -o SQL1_filtered3.csv

I haven’t tried to save the results to a database yet. According to My Torn Data Pages, there’s a problem with the syntax cited by Microsoft TechNet. I want to test both scenarios, using a DSN and the Native Client.

Quick access to your common scripts

2010-01-12T07:18:00.001-06:00

Everyone has their own library of SQL scripts they run often, and everyone has their own methods for storing them. These a a few ways you may not be aware of.

My first method is pretty well documented. Have you every just clicked Ctrl + F1 and see the results of sp_who? Well, you can add your own procedure calls here. In Management Studio simply open Tools > Options and select Keyboard. Here you’ll see 12 slots with shortcuts of Alt + F1, Ctrl + F1, and Ctrl + 1 through 0. Three of these are already defined; sp_who, sp_help, and sp_lock. You can leave these or replace them with your own, and you can add your own in any available slot. So now you have a way of calling 12 procedures with just a keyboard shortcut.

The second way I ran across by accident and works best for small queries. In Visual Studio you have the ability to store snippets of code in the Toolbox. One day I thought I’d try it in SSMS, since it’s similar to the VS IDE. And it worked. What you need to do is open the Toolbox ( Ctrl + Alt + X). You’ll most likely only see the General tab with no controls; that’s fine since there aren’t any. Right click in the Toolbox, select Add Tab, and give it a name; mine is simply Scripts. Next, open or write your script in a query window. Then highlight the script and drag it onto the Toolbox. You probably won’t see it yet; right click the Toolbox again and make sure both List All and Show All are selected. And voila! There’s your script! To run it just right click the snippet, copy the code, and paste in a new query window.

If you hover the mouse over the snippet you just added you’ll see the entire code. Notice this is added with Text: appended to the start. you can change this by right clicking the snippet and choosing rename, then just replace the word “Text’ with something more meaningful.

The third way is even better. Most people know that there are templates with SQL scripts you can use. Again, you can add your own and have them available anytime you open SSMS.

If you open the Template Explorer (Ctrl + Alt + T) you’ll see the templates that Microsoft gives. There’s two ways to add your own templates. Right click SQL Server Template and choose New > Template. You can also choose Folder to categorize your scripts, otherwise the new template will be in the root. Give your new template or folder a descriptive name. Then right click the template, choose edit, write your code and save it. Now just double click the template to open it in a new query window.

The other way to add your script to the Template Explorer is to add it to folder where the other templates are. On an XP or a Windows 2003 Server that’s at Documents And Settings\your account\Application Data\Microsoft\Microsoft SQL Server\100 (for SQL 2008) or 90 (for SQL 2005)\Tools\Shell\Templates\Sql. On a Vista or Windows 7 box you can find it in Users\your account\AppData\Roaming\Microsoft\Microsoft SQL Server\100 (for SQL 2008) or 90 (for SQL 2005)\Tools\Shell\Templates\SQL.

One word of warning. If you do save your scripts as templates, remember to back them up occasionally. It would be easy to overlook them if you ever rebuild your computer and need to restore them. But of course you already knew that.

How I track database size

2010-01-11T06:14:00.001-06:00

One of my DBA tasks is to track the space used on our database servers. I want to know how big the database file sizes are, how big the tables are, and how much free space is available on each server. This is a common task for DBAs, and there’s probably a different method for each DBA. This is mine.

When I first started, I created a database on each production server to store all stored procedures, tables, and views that I use as a DBA. The tables are mostly repositories of server state at the time I run a query or procedure. In the case of database sizes I created the procedure GetDBFileStats to get the filesize, total space, free space, and space used for each file in each database:

CREATE PROCEDURE [dbo].[GetDBFileStats]
AS

EXEC sp_msforeachdb 'USE [?]

CREATE TABLE #tmpspc (Fileid int, FileGroup int, TotalExtents int, UsedExtents int, Name sysname, FileName nchar(520))
                  
INSERT #tmpspc EXEC (''DBCC SHOWFILESTATS'')
 
INSERT INTO PerfDB.dbo.dbStats            
SELECT GETDATE() AS RunDate,
    ''?'', 
    s.name AS [Name],
    s.physical_name AS [FileName],
    s.size * CONVERT(float,8) AS [Size],
    CAST(tspc.UsedExtents * CONVERT(float,64) AS float) AS [UsedSpace],
    (s.size * CONVERT(float,8) - CAST(tspc.UsedExtents*convert(float,64) AS float)) AS FreeSpace,
    s.file_id AS [ID]
FROM sys.filegroups AS g
    INNER JOIN sys.master_files AS s ON (s.type = 0 AND s.database_id = db_id() AND (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
    LEFT OUTER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
ORDER BY [ID] ASC

DROP TABLE #tmpspc

INSERT INTO PerfDB.dbo.dbStats            
SELECT GETDATE() AS RunDate,
    ''?'', 
    s.name AS [Name],
    s.physical_name AS [FileName],
    s.size * CONVERT(float,8) AS [Size],
    CAST(FILEPROPERTY(s.name, ''SpaceUsed'') AS float) * CONVERT(float,8) AS [UsedSpace],
    (s.size * CONVERT(float,8) - CAST(FILEPROPERTY(s.name, ''SpaceUsed'') AS float)* CONVERT(float,8)) AS FreeSpace,
    s.file_id AS [ID]
FROM sys.master_files AS s
WHERE (s.type = 1 AND s.database_id = db_id())
ORDER BY [ID] ASC'
   
GO

This was as far as I went until a few months ago. At that time I started to think of using SSIS for some of my daily tasks. So I created this simple package that gets the results from my procedure and puts them into a central database repository. I also truncate the tables and change the default date to just the date;

Finally I created a report that got the data back. I send 2 parameters; a from date and a to date. Now when I run the report I can view what the database size was for the two dates and easily see how much the files have grown. I also created a sub report that, when I click on the database in the main report, opens to show the growth history of that database on a week to week basis.

Here’s the table I use to store the data and the procedure I use to populate the summary report. I also run it as an ad-hoc query;

CREATE TABLE [dbo].[dbStats](
    [Servername] [sysname] NOT NULL,
    [RunDate] [datetime] NOT NULL,
    [DBName] [sysname] NOT NULL,
    [Name] [sysname] NOT NULL,
    [FileName] [nchar](520) NOT NULL,
    [TotalSize] [float] NOT NULL,
    [UsedSpace] [float] NOT NULL,
    [FreeSpace] [float] NOT NULL,
    [FileID] [int] NOT NULL
) ON [PRIMARY]

CREATE PROCEDURE [dbo].[prc_DBSizeCompare] (
    @minDate    DATETIME,
    @maxDate    DATETIME)
    
AS

SET NOCOUNT ON

;WITH DBCTE(ServerName, DBName, Name, MaxRunDate, MinRunDate) AS
(
    SELECT ServerName, DBName, Name, 
        MAX(RunDate) AS MaxRunDate,
        MIN(RunDate) AS MinRunDate
    FROM dbo.dbStats
    WHERE RunDate BETWEEN @minDate AND @maxDate
        AND DBName <> 'tempdb'
    GROUP BY ServerName, DBName, Name
)
SELECT db.Servername, db.DBName, db.Name, db.RunDate, db.TotalSize, db.UsedSpace, db.FreeSpace 
FROM dbo.dbStats db
    INNER JOIN DBCTE CTE ON db.Servername = CTE.ServerName
        AND db.Name = CTE.Name
WHERE db.RunDate = CTE.MinRunDate OR db.RunDate = CTE.MaxRunDate
ORDER BY db.Servername, db.DBName, db.FileID, db.RunDate

GO

Booting to a VHD in Windows 7

2009-12-29T09:59:00.001-06:00

This is something I tried before but didn’t complete for some reason. Now that I’m on vacation this week and I’ve got some extra time to play, I went back to finish it.

As you probably know by now, Windows 7 has the ability to boot into a .vhd file. This is awesome, as you can create a virtual testing environment that you can run directly against you hardware.

There are a few gotchas, though. You’re limited in the OS’s you can run on the virtual side to Windows 7 and Windows 2008. I’ve seen post of people getting other OS’s to run but I haven’t tried. And I’ve seen warnings not to do this on a laptop, though I’ll try it, once I install my new bigger laptop hard drive next week.

Anyway, this is how I did it. I had created a Windows 7 virtual machine in Virtual PC 2007. I used the vhd from that VM instead of creating a new one, though you certainly could if you wanted to.

The first thing I did was to run sysprep inside my VM. I’m not an expert on sysprep, I just followed instructions I found on the web. Briefly, sysprep is a GUI tool that prepares the image to be configured to use the hardware on the new server. You’ll find it in C:\Windows\system32\sysprep. Run it as an admin. Choose the default target of out of the box experience and also choose to generalize. Also choose to shut down. After sysprep is finished it will power down your VM.

I didn’t do this, but it’s probably a good idea to make a copy of the vhd at this point.

The next thing I did was to set up Windows bootloader to see the vhd file. I opened a admin command prompt and ran the following: bcdedit /copy {current} /d “Win 7 VHD”. This returns a GUID I saved to notepad. “Win 7 VHD” is the description I wanted to see on the boot menu. After that I ran these three commands:

bcdedit /set {guid} device vhd=[C:]\VM\Win7\Win7.vhd

bcdedit /set {guid} osdevice vhd=[C:]\VM\Win7\Win7.vhd

bcdedit /set {guid} detecthal on

In my example I replaced guid with the guid I saved earlier. VM\Win7\ is the path to my vhd file, and Win7.vhd is the file I’m using. Note that the drive letter is in square brackets: [C].

And that’s just about it. Once I restarted my computer I could see both my original Windows 7 installation and my new vhd boot option. When I chose the vhd, Windows started and applied the hardware changes. After that I just logged in and ran my Windows vhd. Once in the virtual environment, I can see all the drives on the computer, including those for my “real” Windows 7. Notice Disk 1 is a blue icon; this shows that it’s a vhd file. It also shows the reserved system partition. I can also see the files on the other physical drives.

I don’t get this if I’m running my physical Windows 7. I can mount the vhd file (on the menu go to Action > Attach VHD). But it doesn’t stay mounted between reboots. I haven’t tried mounting it with DISKPART yet, I’ll try that when I create my laptop VM.

The only drawback is the vhd is not portable, and I can’t run it in Virtual PC 2007 anymore. I can probably run sysprep again to get it back, but I think I’ll keep it as it is for now.

Year End Wrap-up

2009-12-27T15:34:00.001-06:00

This is my wrap-up for 2009 and a preview for 2010.

A big chunk of my year was testing virtualizing our production servers with different configurations. After months of testing we learned that our workload wasn’t conducive to virtualization.

During the last few months I began testing SnapManager from IBM for use as a backup tool. While SnapManager is a great tool for bringing a server online quickly, I can’t see a way of restoring individual objects. Unfortunately in our shop it’s far more likely to have a table accidentally updated or deleted than have a whole database go corrupt. I’d like to find a way for both to work, having the best of both worlds.

I started to learn Microsoft’s BI stack. I’m already pretty familiar with Reporting Services, but I’ve only played around with Integration Services and I’ve done nothing with Analysis Services. So far I’ve just scheduled a simple package to collect database sizes so I can track growth. I’ve got a long way to go.

I’m on vacation this week, not going back to work until January 4th. I’m using my downtime to create a virtual Win 7 pc to run all the 2010 betas coming down. I also want to set it so I can boot directly to it, using the new functionality in Windows 7.

So what’s coming up next year?

I’ll finish my testing of SnapManager and I’ll post my results.
I’m going to attend the first SQL Saturday in the Chicago area in April. I’ll post info about this early next month.
Continue my education into BI using the SQL tools.

One of my resolutions for next is to continue studying new technologies – new to me that is. I want to become more proficient in Powershell and Silverlight, among others.

Happy holidays to all, and I’ll see you next year.

Virtualization – final wrapup

2009-12-15T11:08:00.001-06:00

This is another topic I want to wrap up before the end of the year.

A quick overview. earlier this year, I did some testing of virtualizing our production servers hosted on VMWare servers. I captured a typical workload from our busiest server and replayed it on servers set up for testing; a 32-bit physical server with 16 CPU set up as a production server, the same server with hyper threading turned off, a 32-bit and a 64-bit virtual server with 4 CPUs,and a 32-bit and a 64-bit virtual server using vSphere and 8 CPUs. All servers had 16 GB of RAM.

After running the workload multiple times on each server configuration we compared results. What we saw was understandable – neither the 4 CPU or 8 CPU servers matched the 16 core baseline. Even the physical server with HT off fell short.

What I didn’t show in earlier posts was the counter for latches. Latches are the #1 wait type on our servers, and this held true on all testing servers. Our servers aren’t running optimized and this is being magnified in a virtual environment. Having 16 cores lessens the performance hit from all those latches in production.

So we’re not going to virtualize,at least the production servers. While virtualization is useful and can be used for SQL, it’s not a good idea to try and virtualize servers with performance problems. We’re currently using VMWare to run our development and testing environments as well as a few smaller, less used SQL servers, and we’re not seeing any issues with them.

I’m not going to published a summary of all my counters, but I’ll make them available on request if anyone is interested.

Testing Google feeds - ignore

2009-12-15T08:47:00.002-06:00

Testing Google cross-posting

What I learned today – temp file created when reading from multiple trace files

2009-11-24T13:34:00.001-06:00

I’m going to do some updating on past posts before moving forward on my BI learning. Today I want to close the book on using Profiler.

First, there’s an excellent series of three webcasts on Profiler on the MSDEV website. MSDEV is a great resource for anyone looking for webcasts on Microsoft developer products. Check out the site even if you’re familiar with Profiler, chances are you’ll find something you didn’t know before in one of the other webcast series.

Second, Profiler will create a temporary file when you read from multiple trace files. Like a lot of things, I found this out by accident. I had created a trace for replay on one of our production servers. It wasn’t until the trace had been running for a while that I noticed that I had forgotten to change the maximum size of the trace files. I let the trace complete, knowing I could read them all later. My trace ended up creating 369 .trc files totaling about 1.89 GB.

So I began reading all of the .trc files, meaning to save the results into one .trc file. While the trace was running, I noticed that the amount of free space on the C drive of my test server was dropping. It eventually bottomed out when the trace finished reading the files. And when I investigated, I found a file in my Local Settings\Temp folder that was the same size as the total size of the 369 .trc files. The temp file was deleted automatically when I closed the Profiler session. Finally I opened the single large .trc file I saved when reading the multiple smaller .trc files. This time no temporary files at all were created.

I wasn’t expecting the temp file to be created. Luckily this happened on my test server where i had enough free space. But it’s something to consider anyway. You may easily run out of space on your C drive without knowing why. I don’t know what would happen if I read the same trace with fewer, larger .trc files, that’s something I’ll need to look at for sure.

What I learned today – GID in sysusers table

2009-11-12T09:22:00.001-06:00

I had a question from a colleague yesterday about the GID field in the sysusers table. He had a SQL 2000 database that he had moved to a SQL 2005 instance. When he did this, his third party application stopped working. When he investigated, he noticed that the app was using the GID field in sysusers to validate logins and set roles. After he restored the database on the new server the GID field was 0 for everyone. At this point no one could connect to the database through the app.

In SQL 2000, when you add a user to a database, the GID is 0 for the public role. If you add them to a database role, the GID becomes the UID of the role. I’m not sure what happens if you add a user to more than one database roles, that’s something I’ll have to check out. In SQL 2005 this has changed. The GID for all database users is 0. This is not a field that you can update.

This is a good example of why you should never depend on the system tables being static. Microsoft always warns that they may be updated when you apply patches or when you upgrade.

SSIS Expressions Cheatsheet

2009-11-09T10:07:00.002-06:00

Maybe you’re new to SSIS, like I am, or maybe you just need a little refresher on expressions in SSIS. I just ran across the SSIS Expressions Cheatsheet. This is a page on the PragmaticWorks website. It’s also available as a downloadable .pdf file. For me, anyway, it’s a great one-page reference. I’ve got it printed and pinned to my cube wall.

I learned about this resource from Dougbert.com

My first data warehouse – Part 1

2009-11-03T12:57:00.002-06:00

I’m going to lay a little ground work for future posts here. My plan to learn BI, and also to create a useful data warehouse from scratch, is to build one based on reporting server performance for my company’s eventual use. I want to be able to drill down to an individual report to view performance data, as well as summarize report performance by locations, by dates, by users,by date ranges, as well as other factors that I don’t envision right now.

Today I’m going to discus the download I found on the CodePlex site, thanks to Tyler Chessman from SQL Magazine. This package and the accompanying reports will be incorporated in my data warehouse project.

To use the downloaded code you’ll need to create a database. The examples use RSExecutionLog as the database so I kept the same name. The download includes a script to create the necessary tables and relations. The tables it creates are used to store data extracted from the ExecutionLogStorage table in your SQL 2008 report server database. Mine has the default name of ReportServer, created when I configured Report Server for the first time. This database stores the report catalog and also useful information pertaining to when reports are run.

There are two Visual Studio solutions in the download; one SSIS solution called RSExecutionLog with a single package called RSExecutionLog_Update.dtsx, the other is a reporting solution with three sample reports to use against the extracted report server data and two to view schema and table information. I’m going to concentrate on the first two.

I had some original issues with the package and reports. They were written using SQL 2005; I’m using SQL 2008. After converting the reports (basically just opening the report solution in VS 2008) I still had issues with the package and it’s connections. There were places where the connection to the source or destination seemed to be hard-coded inside different components of the package, even after I changed the source and destination connections. I ended up building a new package based on the original.

The package is pretty sweet. Not only does it populate tables for users and reports, it also creates one for the report parameters. When the parameters is stored in the report server catalog, it’s something like “PARAM1=123&PARAM2=456”. The package shreds the report server string into individual values, so now I can easily search by parameters. I was thinking of doing the same thing, only storing the values as one record as opposed to one or more.

So I can use this package with very little customization. My next step is to pull data into my dw that is dependent on each report execution. This package should be fairly straight forward.

My next post will show my database design.

What I learned today – Report Server Execution log package

2009-10-27T06:33:00.001-05:00

My first attempt at a data warehouse is going to be collecting data from our report servers. In our environment we have two SQL 2008 instances that host the reports themselves, and they connect to four data centers for the report data. In the data centers we have a table that’s populated with parameters needed for each report; dates, locations, etc. The reports themselves only have two parameters; the datacenter where the data for the report is (we’re using dynamic connection strings) and a guid that identifies the other needed parameters in the data center.

My goal was to build my warehouse from the four data center report parameter tables and the Execution Log tables on the report servers. The report server logs information from each time a report is run; if it was successful, the parameters for the report, the user, and more that would be helpful in debugging a report performance. I wanted to be able to view summaries for each report; for instance how often each report was run for each location during a specified date range, average report durations, or the number of aborted reports.

I was going to build an SSIS package to gather the data for me. Then I read an article in the November 2009 issue of SQL Server Magazine by Tyler Chessman that defined about half of what I want to do. In his article SQL Server Reporting Services Questions Answered, Mr Chessman describes sample reports from Microsoft that you can find on CodePlex. The reports will be interesting enough and I’ll be able to use them, but the best part is Microsoft has already created a package to extract the data from the report server execution log!

This post is meant to be an overview. I’ll post a review of the CodePlex samples soon, and I’ll start laying out my data warehouse design.

What I learned today – auto generate create table DDL in SSIS

2009-10-21T08:10:00.001-05:00

I’m pretty new to SSIS, only having played around with it to see how things work. This is probably well known to SSIS developers but I just ran across it accidentally. I’m learning SSIS and I’m currently working through Brian Knight’s book “Knight’s 24 – Hour Trainer Microsoft SQL Server 2008 Integration Service”(I’ll review the book when I’m finished). Lesson 15 is walking me through loading the contents of a flat file into a new table in a SQL database with the OLE DB Destination.

When I’ve done this before, I’ve always written a DDL statement to create the table. This time, I connected the Flat File Source, which has my sample data, to the OLE DB Destination.This time, when I clicked to create a new table, SSIS generated the CREATE TABLE script based on the definition of the flat file! The only thing I had to change was the table name, and even this would probably have been handled if I had renamed my destination first.

I didn’t think the table would be created until the task was run. But after clicking OK, the table was listed in my test database. I hadn’t even finished defining the destination!

SQL Saturday in Iowa

2009-10-19T06:33:00.001-05:00

SQL Saturday in Iowa has come and gone. It was a great conference, and it’s a shame that we can’t get a day like this somewhere in the Chicago – Milwaukee – Madison region. Hopefully soon.

But back to the SQL Saturday. Thanks to the East Iowa SQL User Group for putting this all together, bringing in the speakers, finding the venue, lining up the sponsors, and doing all the little things that make an event like this possible. There were only a few minor glitches with equipment (projectors mostly). Registration was probably the easiest I’ve ever seen for any event. Food and beverages were both tasty and plentiful all day long. And the sponsors were only there for the event, not to sell products.

But the best part of the event was the content. I can only comment on the sessions I attended but I’m sure that the rest were equally as good. I have a list of the sessions offered in an earlier post.

I didn’t go to Alex Kuznetsov’s sessions since I’ve seen them. The first session I went to was Janis Griffin’s Tuna Helper. She went over a process that Confio uses to spot issues on servers using wait types and DMVs. Next up were the three BI sessions that Jessica Moss presented. The session on reports was basic, probably because that’s the part of the BI stack I’m most familiar with. And her session on Database Dimensions in SSAS had a glitch with an overheated projector and became more of an overview of SSAS itself, which was fine with me as I know very little about SSAS. The session on SSIS was great. I may finally be getting a handle on data warehousing dimensions and facts thanks to her.

Tim Ford also had an issue with a bulky projector so his session wasn’t as informative as it would have been. But I have the links to his blogs where he’ll have his scripts. They’ll come in handy. And finally Louis Davidson gave his second session of the day, on database design patterns. This was my first chance to see Louis present live. He’s a great speaker.

Sidekick data loss should never have happened

2009-10-12T12:51:00.001-05:00

It was just a coincidence that I posted earlier on cloud computing. I took a long time writing a simple summary of the TechNet and MSDN events I went to on the Azure services. I was (and still am) impressed by the potential of cloud services.

Over the last few days some details are coming out about what happened with T-Mobile’s Sidekick. T-Mobile was using Microsoft’s cloud computing to handle Sidekick data. At some point Microsoft performed a SAN upgrade and outsourced the job to Hitachi. What is completely baffling is that there was no backups taken prior to the upgrade (you can read a better summary at HipTop3.com).

How in the world could Microsoft and/or Hitachi have neglected the backup step? This should have been caught by the most junior member of the team running the upgrade. It’s going to be interesting to hear how they try to spin this; all parties are equally to blame.

And how does Microsoft try to sell their cloud now?

Microsoft Azure event summary

2009-10-12T08:19:00.001-05:00

On October 1st I went to the Technet and MSDN events on Azure. I’ve never really read much into what Azure was all about so I wanted to learn more about it; what it was, and most important what it could do for me.

There are a few data centers that Microsoft built for their Azure services. They just finished one here in Chicago, there’s one in Dublin, and I believe the third is in Quincy, Washington. It’s pretty cool how they set it up. Dell loads up semi trucks with the components needed. The trucks are shipped to the data centers where they are plugged into the network. I don’t think they use USB cables. Each data center can hold, I believe, 350,000 nodes.

Azure is actually three parts. The first is Windows Azure. This is exactly what it sounds like, running on a Windows Server platform.

The second part is SQL Azure, again no surprises. There wasn’t much talk about SQL Azure other than to mention that it was available. However the costs seemed to me to be fairly high; 1 GB for $9.99 per month, or 10 GB at $99.99 per month. You would probably be better off finding a different hoster, there are cheaper options available.

The third part of Azure is .NET services. This would be your messaging services. Again, there are probably cheaper options. Microsoft wants $.15 per 100K per message.

On top of all this there are the bandwidth costs; $.10/GB inbound and $.15/GB outbound

West Monroe Partners also gave a demo at the end of the TechNet event. They build the website the City of Chicago used for the Taste of Chicago last summer. It was impressive, especially since this was 3 months ago on a CTP build.

At Microsoft today

2009-10-01T09:28:00.001-05:00

I’m attending the TechNet and MSDN events on using Azure, cloud computing. Some cool stuff on tap. I’ll post about the individual sessions later.

Lessens learned – Report Server administration

2009-09-28T11:00:00.001-05:00

It’s a long commute in the morning, so I thought I’d use this time to compile a few things I learned and noticed about administering a server hosting SQL 2005 or 2008 Report Services.

Check the recovery model of the databases you create on your report server, including the ReportServerTempdb (or whatever you named the SSRS temp database). By default they will be in full recovery mode unless you’ve changed your model database. If they are still in full, the log files will grow and may cause you space problems later. Consider using simple mode. If you use full remember to schedule log backups.
Backup the SSRS tempdb as well as the database holding your catalog. You’ll need this database if you need to restore the server, it’s not recreated when SQL starts up like tempdb. I’ve seen a few maintenance plans that skip this.
Speaking of backups, remember to backup the SSRS encryption key. You’ll need that key if you ever want to restore the catalog database and you don’t want to re-enter all encrypted content, like data source credentials. I’ve successfully moved catalogs from production to a testing server using the key to restore the connections. You can backup the key from the Report Server Configuration Manager. I suggest that you also copy the backup somewhere safe in case you can’t get to the server.
If you don’t want your reports exported in a specific format then disable that format. For instance, not a lot of reports look good if you export them in a comma delimited format. Just remove or comment out the entry in the rsreportserver.config file. You’ll find this file in the path for your<instance path>\Reporting Services\ReportServer. Be careful removing the image format – this could cause some problems, especially if you open the report in the report viewer control available in Visual Studio.

<Render>
            <Extension Name="XML" Type="Microsoft.ReportingServices.Rendering.DataRenderer.XmlDataReport,Microsoft.ReportingServices.DataRendering"/>
            <Extension Name="NULL" Type="Microsoft.ReportingServices.Rendering.NullRenderer.NullReport,Microsoft.ReportingServices.NullRendering" Visible="false"/>
           <!-- <Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering"/> -->
            <Extension Name="PDF" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PDFRenderer,Microsoft.ReportingServices.ImageRendering"/>
            <Extension Name="RGDI" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.RGDIRenderer,Microsoft.ReportingServices.ImageRendering" Visible="false"/>
            <Extension Name="HTML4.0" Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html40RenderingExtension,Microsoft.ReportingServices.HtmlRendering" Visible="false"/>
            <Extension Name="MHTML" Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.MHtmlRenderingExtension,Microsoft.ReportingServices.HtmlRendering"/>
            <Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering"/>
            <Extension Name="RPL" Type="Microsoft.ReportingServices.Rendering.RPLRendering.RPLRenderer,Microsoft.ReportingServices.RPLRendering" Visible="false" LogAllExecutionRequests="false"/>
            <Extension Name="IMAGE" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRenderer,Microsoft.ReportingServices.ImageRendering"/>
            <Extension Name="WORD" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordDocumentRenderer,Microsoft.ReportingServices.WordRendering"/>
</Render>

You’ll find some good information in the ExecutionLogStorage (SQL 2008) or ExecutionLog (SQL 2005) tables. These tables can help you troubleshoot problems with the report as it shows the name of the person running the report, the time it took for retrieving data or rendering the report, the rendering format, and any parameters used. It will also show if the report was opened successfully or if it couldn’t open because of a rendering issue.

Another place to look at during troubleshooting is the logs. You’ll find these at <instance path>\Reporting Services\LogFiles. You may need to clean these files up on occasion since some report errors will generate dump files.

What’s the best practice for starting SQL services?

2009-09-24T12:05:00.001-05:00

An incident that occurred to us this morning causes me to rethink how I have SQL services set to start when the server is started or rebooted.

Early this morning we had a power failure at one of our data centers. It caused one SQL server to be rebooted and left another server online. But it caused the SAN controllers to reboot also. Server 1, which didn’t reboot, began reporting error 823 when it couldn’t see the databases, even after the SAN came back online. Restarting the SQL services cleared that up. Server 2, which did reboot, came up reporting no problems. Consistency checks on all databases showed no problems so luckily we were good to go.

I have the SQL services set to restart automatically when the server starts. But now I’m wondering if that is a good idea.What if the SAN wasn’t available when SQL first restarts? What if there is data corruption? Would it be better to always start the services manually so I could check the logs first, before I restart SQL?

I’m interested in hearing how you handle SQL service startups.

SQL Saturday in Iowa Oct 17th

2009-09-18T09:27:00.001-05:00

The East Iowa SQL User Group just listed the sessions for the SQL Saturday event in Iowa City on October 17th. There’s some good stuff scheduled. Go to SQLSaturday.com to register or email sqlsaturday#19@sqlsaturday.com for more information. They can always use more sponsors, too.

Track	Start Time	Session	Speaker
Admin / Platform	9:00 AM	High-Availability Options for Microsoft SQL Server	Tim Plas
App Dev I	9:00 AM	Tuna Helper - A Proven Process for Tuning SQL	Janis Griffin
App Dev II	9:00 AM	Defensive database programming	Alex Kuznetsov
Admin / Platform	10:00 AM	The Benefits of SQL Azure	Aaron King
App Dev I	10:00 AM	ABCs of CTEs	Jason Strate
App Dev II	10:00 AM	SQL Server 2008: The New and Cool...	Zakir Durumeric
BI / Relational Theory	10:00 AM	Building Reports in SQL Server Reporting Services	Jessica Moss
App Dev I	11:10 AM	Extended Events, Work Smarter Not Harder	Jason Strate
App Dev II	11:10 AM	2 Years of Database Unit Testing: Lessons Learned	Alex Kuznetsov
BI / Relational Theory	11:10 AM	Extract, Transform, and Load your Data Warehouse	Jessica Moss
Admin / Platform	1:00 PM	Service Broker: A Brief Introduction	Zakir Durumeric
App Dev I	1:00 PM	Introduction to PowerShell	Keith Dahlby
App Dev II	1:00 PM	SQL Server 2008 - Practical Uses for New Features	Chuck Heinzelman
BI / Relational Theory	1:00 PM	Introduction to SSAS Dimensions	Jessica Moss
Admin / Platform	2:00 PM	Tips Tricks For Administering SQL Server	Timothy Ford
App Dev I	2:00 PM	Drive Your Way to the DMV	Jason Strate
App Dev II	2:00 PM	SQL Server 2008 R2 - Moving to the Next Level	Chuck Heinzelman
BI / Relational Theory	2:00 PM	Database Design	Louis Davidson
App Dev I	3:10 PM	Improving Daily Imports with Partitioned Tables	Jason Strate
App Dev II	3:10 PM	An Introduction to ASP.NET MVC	Chris Sutton
BI / Relational Theory	3:10 PM	Database Design Patterns	Louis Davidson

Report Service error

2009-09-17T06:19:00.000-05:00

Yesterday I helped a developer troubleshoot an issue he was experiencing with a report. The report is hosted on a SQL 2008 instance used for testing and was being viewed in a Win Form application using the Report Viewer control. We congigured the viewer control opens the reports as an image since this would be what the client sees when they print it. He couldn't tell me the exact error message but it referred to not being able to find the stream. He also stated that the report worked fine if he ran it with less data.

I first checked the execution log table in the report database and I saw he was getting a rendering error. Next I had him rerun the report in the viewer. This time he received a generic error saying that there was a problem. But if he clicked the page layout button to render the report in HTML the report ran fine.

My next step was to check the logs on the report server instance. Sure enough I found the error. It was an IO issue; not enough space on the drive. Luckily this was on a testing server and not production.

Now the testing server is not set up the same as a production server. This box is a jumble of programs and databases, running multiple SQL instances for 2000, 2005, and 2008. The report server temp database is on the same drive as the report server database and the error logs and dumps. At the time I saw this the drive had about 100 MB free. And while 100 MB isn't a lot of space nowadays, I would have thought it would have been enough to run the report (it had over 800 pages when we freed up space for it).

At this point I'm not sure where that image is being produced, in the reports database or the report temp database. I've never dug into the report temp db before. I'd like to head from anyone who has had similar problems, or can show me where else I should be looking.

Updates to previous posts

2009-09-14T08:32:00.001-05:00

In my posts on virtualization I forgot to mention something. While I did say I was running with the multi-thread option instead of the replay in order option, I left the number of threads at the default of 4. I ran it with 64 threads earlier, but with the replay in order option. Those replays took well over 5 hours, as opposed to 2 1/2 – 3 hours with multi-thread at 4. But if I compare multi-threading at 4 with multi-threading at 64, then the times are much better – 3 1/2 hours compared to 2 3/4 hours on the same virtual server. I’ll summarize all my tests in a few weeks when I’ve completed all my tests.

The second post I need to update regards repeating data in a page header using SSRS. I solution was to use a shared variable. This had potential problems which I neglected to mention. If you use a shared variable then anyone running the report at the same time could overwrite each other values. I knew this at the time I put the report into production, but I thought the odds of that happening would be slim. Later I found I could produce that exact behavior in testing; when I ran the report simultaneously with another tester we did indeed cross values.

I changed the report to use a hashtable instead. The value in each record would be unique since it will depend on the user id of whoever is running the report. As an example of repeating a customer name on each page I

Added a hidden row to the top of the table control on the report. The table was already there and the grouping was already set on the customer name.
Added the customer name field from the table’s dataset to the hidden row.
In the report code section I added a shared hashtable – DIM htCustomer AS system.collections.hashtable = NEW system.collections.hashtable. The hashtable still needs to be shared.
Still in the code section, I added a new function to add a record to the hastable if it didn’t exist or update it if it did. The function takes three parameters; the value from the hidden field, a group name, and the user id. It returns the customer name for the current user

DIM key AS STRING = groupName & UserID
IF NOT group IS NOTHING
   DIM g  AS STRING= CTYPE(group, STRING)
      IF NOT (htCustomer.ContainsKey(key) THEN
          htCustomer.Add(key, g)
      ELSE
          IF NOT (htCustomer(key).Equals(g)) THEN
              htCustomer(key) = g)
          END IF 
      END IF
END IF
RETURN htCustomer(key)
5. Add a page header to the report. Add a new text box that uses an expression to call the function and pass in the parameters

=Code.SetCustomerName(ReportItems!txtCustomer.Value, “Customer”, User!UserID)

ReportItems!txtCustomer is the hidden field; Customer is my group name, and User!ID is a global parameter available to the report.

Job Scheduled for Sunday

2009-09-11T18:27:00.002-05:00

It's scheduled to run about 7:30PM

DBCC GoBears

Documenting servers

2009-09-11T09:19:00.000-05:00

I'm starting to create a Run Book (not my term, but I don't remember where I heard it) to store all documents relating to the databases and their servers I'm responsible for. That list is growing, and before it gets out of hand I'd like to create a template of what needs to be included. I'm trying to determine just what needs to be included. Drafting the template should help me not to go back and redo what I've already done.

At this point I'm only documenting the servers, not any processes like database backups and restores. Those are important and I've already done most of those, just not formally. I'll do that also.

I'll start with the server itself. What I'm adding is the server name, ip address, if the server is physical or virtual (if virtual the name/location of the host server), the OS and any SPs of patches installed, number of CPUs, amount of memory, size/location of the page file, and the drives and total space. If the drives are on a SAN then I should include a mapping of the SAN.

For the SQL installation, the edition and any SPs or hot fixes, the instance name (we only run one instance per server), any non-default options that were configured, accounts used to run services, configured alerts, jobs and their schedules, if components like SSRS are installed and info about their configurations if they are.

For the databases, the db names (including the virtual name and physical path), compatibility level, recovery model, auto growth and size restrictions, and any db options.

For database backups, the type of backup, where it backs up to, and when it backs up. This info may be duplicated since our backups are handled by jobs.

Is there anything else I'm missing?

Alas, no booting to a .VHD for me

2009-09-09T16:41:00.003-05:00

I’ve been reading quite a bit lately about attaching a virtual drive to a computer in Windows 7 and Windows 2008 Server R2. It would be nice to have the ability to boot directly into a virtual server. But I’m not seeing a way I can achieve it, at least right now.

My hardware would be my current laptop, an HP Pavilion TX 2000 (I wanted the tablet capabilities) running a 64 bit version of Windows 7 Ultimate. It has 2 CPUs, 4 GB of RAM and a 250 GB hard drive, certainly enough to run one virtual machine. I wanted to run a virtual 64 bit Windows 2008 Server R2 as my BI sandbox. Unfortunately, neither Virtual Server or Virtual PC support running 64 bit guests, only 32 bit. So I built my VM using VMWare’s Workstation. But those virtual disks can’t be mounted.

So I created another VM, this time a 32 bit version of Windows 2008. I created a fixed disk, installed the OS, and followed the directions on Charlie Calvert's blog. Mounting the .vhd file was simple, as was using bcdboot. When I rebooted both servers showed in the boot launcher. Everything good to go, right?

Wrong. When I tried to boot into the .vhd, I'd get an error message that the computer was not correct because of hardware changes. And the computer manager no longer showed the .vhd drive as mounted. That’s when I went back and reread the fine print.

Windows 7 only supports mounting Windows 7 or Windows 2008 Server R2. My VM was only 2008. At this point my options are to restore my VMWare VM (backed up luckily) or to try to install R2 directly into a vhd. But I don’t think that will work, either; it wouldn’t be compatible with Virtual PC.

Well, VMWare Workstation is still a great option. It’s just disappointing I haven’t figured out a way to do this yet.

East Iowa SQL User Group meeting

2009-09-09T06:40:00.003-05:00

This may be one of the nuttiest things I’ve ever done, at least from a technical side.Yesterday I drove to Cedar Rapids to attend the monthly meeting of the I380 Corridor (East Iowa) SQL User Group. That’s a 9 hour round trip drive for a 2 hour meeting, not a very efficient use of my time. But I was glad I did.

The East Iowa group is headed by Michelle Ufford, writer of the SQL Fool blog and Ed Leighton-Dick. Last night’s meeting included a tip of the month by Michelle on using Adam Machanic’s latest version of his who is active script. This was followed by a Live Meeting with Jessica Moss who gave an introduction to data warehousing. That’s what I came for. Ed taped the meeting and hopefully it will come out clean enough so they can post it. I’d love to see it again.

Jessica discussed just what data warehousing is. She went into the design and life cycle of a dw, the definitions of fact and dimension tables, the types of dimensions, the differences between a star and snowflake schema, and how SSIS, SSAS, and SSRS fit into all this. She also discussed the different methodologies, Kimball and Inmon. Finally she gave a demo of a dw, from ETL to reporting, using the AdventureWorksDW2008 database. There was only one question, from Michelle, about using procedures to load data as opposed to a Slowly Changing control in SSIS. Jessica recommended using the SSIS approach.

Jessica’s presentation was exactly what I was looking for, a beginning point for how to build a data warehouse. There’s a lot of information and discussions out there but most of it assumes some knowledge already. And classes usually focus on the tools, not the methods. I need to learn both if I’m to create a good design.

Everyone at the user group was friendly. It was a small gathering, maybe 15 people total. The only difference between them and the Chicago group is they cut their pizza in wedges rather than squares.

There was one piece of bad news, however. The Iowa group is sponsoring a SQL Saturday on October 17th in Iowa City. But they are far behind what they need as far as registered attendees. They need to hit a certain level for funding from their sponsors to kick in. I think they are running into an issue where there’s another SQL Saturday scheduled the same day in Orlando and others in October. That’s a shame, because I really want to go to this one. Jessica is supposed to come and expand on last night’s presentation. They’ll make the decision Friday if they need to cancel.

Replaying Traces – What I’ve Learned

2009-09-04T10:07:00.003-05:00

So I’ve been running these replays for a while now, with mixed results. There are a number of lessons I’ve learned along the way.

Check what’s running inside the workload you’re replaying. There may be something that will affect the replay. In my case there was a collection trace that was started by the third party software I use for monitoring the production servers for performance. Since this trace had no corresponding stop, it kept running after the rest of my workload was completed, making me think there was a different issue. The only thing I don’t understand is why it stopped on it’s own in half the replays.
After you load the workload, you can apply a filter and reload the workload. This is how I eliminated any call for sp_trace_setstatus.
Restore the master database to eliminate any replay errors due to mismatched database id’s. This comes directly from BOL. I was ignoring it because at first I wasn’t seeing any errors. But later, especially after I started on the virtual servers, I was seeing more and more errors where SQL wasn’t running against the right database. So when I started the last round of replays I restored the master db first.

I’m in the process of replaying all scenarios again so the data is consistent. So far I’ve completed the 4 physical tests. Coming next are the 3 32-bit virtual, 2 64-bit virtual, and 1 final with VMWare’s 8 CPU server.

24 Hours of PASS

2009-08-25T17:21:00.002-05:00

If you haven't already heard, PASS is hosting 24 hours of PASS on September 2nd. These are a series of free, one hour long webcasts on a variety of SQL 2008 subjects. You can check out the schedule on the PASS website. The first webcast is actually at 7 PM on September 1st Central Time. It's not too late to register.

First look at SQL 2008 R2

2009-08-21T15:42:00.002-05:00

I’ve started to install the CTP of SQL Server 2008 R2. The first change I ran across was this screen during installation. I don’t have a copy of SharePoint Fourteen(?) so I chose the more familiar SQL Server Feature Installation. I’m installing this on a virtual server so I can always change options later.

Replaying traces, Part 5

2009-08-20T17:13:00.003-05:00

So I’m now testing my workload on a virtual 64 bit server, again hosted by VMWare, again with only 4 CPUs. And again I find more issues.

I ran this replay 3 times so far. All three times the replay appeared to have hung at the 99% mark, similar to the 8 CPU physical test. The only difference was that, for the final test, I didn’t save the results to disk. I did this because on the second test there appeared to be SQL activity that was suspended by the TRACEWRITE wait type. The drive it was writing to was the only virtual drive; all databases are on SAN storage.

While all three hung at the 99% mark after about 3 hours, each test had different results. The first test was showing transactions against the database throughout the trace (the black line. The blue line is % Processor Time, the yellow line is % Privilege Time, the red line is transactions/sec against tempdb). The Processor time showed activity from the Profiler after the 99% mark .
The second trace showed no transactions after the 99% mark. This is the one that showed the TRACEWRITE wait type. Notice that there are no transactions against the db after the 3 hour mark this time.
Test 3 shows the same pattern as test 1. There were no wait times for tests 1 and 3.

There is one more issue with these three tests. After stopping the replays, I had to manually kill the Profiler process because profiler became unresponsive. The third test stayed alive and was stopping the process and I let it go. However the next morning I checked and it was still “stopping” after 13 hours. And the Profiler was still responding.

The more I run these replays the more confused I get. I’m going to try one more time. This run will be from a remote machine, I’m only going to capture the counters on the virtual server.

(Mostly) disappointing meeting

2009-08-14T08:41:00.002-05:00

Last night I went to the monthly meeting of the Chicago SQL User group, held on the second Thursday of the month. You can check them out and register for meeting notifications here. This is a good resource for anyone wishing to attend. Anyway,there were two topics last night. The first was on SQL and Powershell, the second on clustering with SQL 2008 and Windows Server 2008.

I don’t have any experience with clustering since we don’t use it at work but it’s one of those topics I feel I should know more about. But last night I was really looking forward to the discussion on Powershell. I’m looking for reasons why I should use Powershell over other methods like SMO or WMI to monitor servers I’m responsible for. I wanted to see some amazing Powershell cmdlets in action.

Unfortunately the presenters of the Powershell segment were not prepared very well. It started with having problems connecting a laptop to the projector and went downhill from there. The demos were filled with errors; most flatly didn’t work. The first presenter dwelled too long on the basics of Powershell itself, the second barely did more than read from the slide deck. Their presentation ate into the second segment on clustering. I wonder if their book was written better.

The second presenter did a much better job. He knows his topic extremely well, being a Microsoft MVP on clustering, and you can tell he is passionate about the subject. And he’s an effective speaker. But since he didn’t have much time he had to cut his demos short.

Don’t get me wrong, I appreciate everyone who presents at these meetings. It’s not easy getting up in front of a room of 80 strangers and talking about a deep subject in a short period of time. I just wish they could have presented their material in a more effective manner.

SQL Server 2008 R2 CTP is now available

2009-08-12T06:57:00.003-05:00

Earlier this week Microsoft announced that the first CTP for SQL Server 2008 R2 is available for download. You can get a copy through TechNet or MSDN, or on the site page here.

According to Mary Jo Foley, R2 is tied tightly in with the upcoming Office 2010 release. Both can be expected in the first half of 2010, around May or June.Personally I’m hoping for April, before my TechNet subscription runs out.

Technorati Tags: SQL 2008 R2,CTP,SQL

Replaying traces, Part 4

2009-08-12T06:39:00.004-05:00

So far we’ve replayed the same workload on 3 virtual servers, each with 4 CPUs, and 3 physical servers, 2 with 4 CPUs and 1 with 16 CPUs. We also ran one with 8 CPUs but that replay appeared to hang at 99%.

All the physical replays showed the same trends. A 16 CPU server performed better than 4 CPUs, Hyper-threading off performed better than hyper-threading on. The average for IO and transactions/sec followed the same curve; the longer the replay time the lower the average.

But the virtual server replays showed a different trend. All of these completed in less time

than the 4 CPU tests on a physical server. And the averages for IO and transactions/sec were noticeably less. Since the workload was the same in each case we expected the averages to be consistent with the trend set by the physical since the total number of transactions and IO should have bee the same across the board.

I’m guessing that there were more deadlocks on the virtual than the physical. In all cases the CPU usage was the same, averaging higher than 80%. If deadlock victims are rolled back it would explain the decrease in total IO. But I don’t know if a rolled back transaction is still considered a transaction by the Windows counters. This is going to take some research.

We have some more tests scheduled using a 64 bit virtual server and one test using VMWare’s newest host that supports virtual servers running 8 CPUs.

Technorati Tags: SQL,replays

SQL Server 2008 Training Kit is available

2009-08-05T12:25:00.003-05:00

One of the things I like about Microsoft is the wealth of training they make available for their products. There was a training kit for ASP.NET, one for Visual Basic 2008, and now there’s one for SQL Server 2008. You can get yours at http://www.microsoft.com/downloadS/details.aspx?familyid=E9C68E1B-1E0E-4299-B498-6AB3CA72A6D7&displaylang=en

Replaying traces, Part 3

2009-07-24T09:15:00.003-05:00

I had a few surprises when I began replaying a production workload on a virtual server. I'm using the same workload I captured from a production server and replayed on a physical server in different scenarios. The first virtual test was on a 32 bit server hosted on a VMWare box with CPU affinity turned on. A limitation of virtual servers (at least for now) is that they are limited to 4 CPUs.

The first surprise was that the replay was faster than the 4 CPU test on a physical server, 3:07 virtual compared to 3:40 physical. The second surprise is that the total number of transactions appeared to have dropped.

I'll be looking into this to see what I'm missing, and I'll post more details soon.

Replaying traces, Part 2

2009-07-22T13:28:00.002-05:00

When I left off, I was discussing using SQL Profiler to replay a trace taken on a production server on different test boxes with different configurations. The end result is to see if we can virtualize our production OLTP database servers.

Our next step in testing was to test replaying a trace on the same server it was captured on. We couldn’t do this on a production server so we used our testing server and assigned our developers to hit the database with our application for one hour. Afterwards we restored the databases to the same state they were in and replayed the trace using the same options we use on the other server tests; using multi-threading, not displaying results, and not replaying server SPIDs. The replay of the trace took only 45 minutes. We probably didn’t capture a large enough workload. But we did rule out the overhead of running the replay when comparing to performance of the production server when the workload was captured.

Back to testing. Since we can’t exactly duplicate the replay of the workload to match the production server, we decided that running the replay on the test server would be our new baseline since we could duplicate that for every test. Testing on the physical box showed the following:

	Production	16 CPU (new baseline)	4 CPU, HT ON	4 CPU, HT OFF
Time	1:07	2:37	3:40	2:44
Avg CPU	17%	24%	83%	77%

We expected the 4 CPU tests to take longer and use more CPU. We were surprised that the 4 CPU test with hyper-threading turned off performed better than with hyper-threading on. So we ran one more test, using all physical processors and turning ht off. This gave us 8 physical processors to work with. This test, though, was incomplete. We ran it twice and both time the replay appeared to hang after completing 99%. Both times we needed to kill the replay after 5 hours. The counters we captured weren’t valid.

I’ll post on testing on a virtual server in a future post.

Technorati Tags: SQL,Trace replay

Testing posting with Live Writer and a proxy

2009-06-30T12:49:00.001-05:00

I’m seeing if I can get through the firewall.

Using Live Writer

2009-06-30T06:39:00.001-05:00

I’ve seen quite a few blogs about Windows Live Writer. Opinions seem to vary as to the usefulness of the tool. So I thought it was time I tried it out for myself.

This is the first post I’ve made with it. I’m just using basic word processing so it shouldn’t be too different than what I use now. That would be OneNote and Word, sometimes just entering right from blogger. But this looks to have most, if not all, the same capabilities. So we’ll see.

Technorati Tags: Live Writer

Replaying traces with SQL Profiler

2009-06-17T07:15:00.004-05:00

One of the projects I've become involved with at work has been the virtualization of our regional databases. However, when we hooked up a copy of one of the databases in a virtual environment we noticed a huge performance degradation, mainly in regard to CPU usage. The only part of the server that was virtualized was the drive containing the OS. User databases were mainly placed on a SAN so the IO looked to be acceptable, at least based on the small workload we generated. We know that the servers were not comparable; the current, physical server has 16 CPUs, where the virtual server is limited by VMWare to 4. Since performance was much worse than expected, we decided to try and replay a workload from one of our production servers on a virtual server and compare the results.

We thought of a few different ways to do this, but we eventually decided on using the replay ability of SQL Profiler. So we captured a trace from production, set up a copy of the databases on a second physical server configured identically to the production server, then used the different options inside profiler for the replay. What we saw was surprising.

The original trace was run for an hour. Using the multithreading option on the test box, the replay took twice as long, and the CPU usage was at least 10% higher for the length of the replay. When we set the number of threads to 64, the replay took 5 hours, and the CPU usage was maybe 25% of the original trace.

So the next step is to replay a trace on the same server to see what the results are. I'll post more on this in a few days, after a few more test cases. I'll also include some of the actual numbers.

How to change the display name of mail sent with DB Mail

2009-04-09T06:41:00.003-05:00

Recently I had a manager complain that emails from some of our SQL 2005 servers were displaying different "From" aliases. These emails were being sent via DB Mail in jobs that were on all servers. I thought all the servers were configured the same; the same email account was being used on all, the same Exchange server, all the profiles had the server name as the display name.

However I missed one thing. Two servers were using Windows Authentication of the database engine and two were using Anonymous. The two using Windows Auth picked up the display name from Exchange, the 2 using Anonymous used the display name of the DB Mail profile.

A small thing, but useful in the future if I need to change the display name of email sent from a server.

Start with the basics when troubleshooting

2009-02-17T13:32:00.002-06:00

Recently I began troubleshooting an issue on one of our servers. The issue is that the alerts I set up for jobs and on the server itself weren't sending emails to the operator I configured. This was happening for a while but I never had time to look at it in detail until now. And besides, I kept a close eye on the server so I would know if there were any problems. I had our other servers set up the same way, and none of them were having any issues. All the servers were using the same email account, and I set up all the database profiles to be identical. And I never had issues sending emails using send_dbmail.

So of course I checked and rechecked all the email account settings against a server that worked. I even went so far as deleting the email profile and creating a new one. Nothing I did had any effect. Finally I did what I should have done in the first place; check to see if the SQL Agent had email enabled for alerts. Sure enough if didn't.

The moral of my story is to start at the beginning when troubleshooting, no matter how basic it seems.

2009-01-20T08:37:00.004-06:00

One of the first things you'll probably notice is the Surface Area Configuration Tool, introduced in SQL 2005, is not there. Actually it's still part of the server, only its moved. Now you access it by connecting to the server using the Management Studio, right clicking on the server name and choose "Facets" from the menu. When the View Facet screen opens, select "Surface Area Configuration" from the Facet combo box. Notice that by default all options are turned off; I specifically turned Database Mail on.

Moving a reporting database

2009-01-05T12:57:00.001-06:00

Another task I just finished in regards to SSRS was moving the reporting database. We have an older server we were using for developing the report on. The system team gave us a virtual server (we're going to run virtual when we go live). I wanted to copy the existing catalogs to the new server so I wouldn't have to re-deploy them all. These are the steps I took. In my case the new report server was already installed and configured

1. Backup the encryption key on the source server using the Report Server Configuration Manager. This should be done regardless.
2. Backup the reporting database on the source server.
3. Restore the backup from the source to the destination server.
4. Apply the encryption key from the source onto the destination.

After I completed these steps all the reports and their data sources and credentials appeared on the new server's Report Manager. Hopefully I haven't missed anything.

Getting data into a page header in SSRS

2009-01-05T08:20:00.002-06:00

As I've mentioned before, we're moving MS Access97 reports to SSRS 2008. Recently I came across another issue, repeating data in a page header. You may or may not know it, but you can't directly add a data field to a page/report headers and footers. If you want your data to repeat you need to employ a workaround. One way is to use parameters and use a second data set for the parameters. I tried this method, but I had a problem when the page changed; the header didn't always mirror what was in my group. I also tried group headers that repeated on each page, but the report I'm working with also has sub reports that may or may not span pages. I found when that happened the group headers didn't repeat properly.

The solution I came up with is to set the values in code. The first step was to declare a public variable for each field I needed on my page header. The second step was to create a function that set the value of the variables to a parameter passed into the function. I then added a hidden text box on the body of my report that called my functioned and passed in the values I needed on the page header. Finally I added the page header section, added a text box for each field I needed, and used an expression to call the public variable I declared in the first step.

This isn't my invention; there are examples of this on the web. I don't have a source handy but I'll cite a few that I used later.

Happy Holidays!

2008-12-24T07:14:00.001-06:00

I hope everyone has a very healthy, happy, and Merry Christmas.

Reporting Service "features"

2008-12-18T08:13:00.001-06:00

I've been a proponent of Microsoft's SQL Server Reporting Services (SSRS since 2005) since it was first released in beta for SQL 2000. I thought the product was a natural, combining a report writer with the database. I especially loved the ease of deploying individual reports. The first product was rough, but I figured it would improve with time. By the time SSRS 2005 came out it was a stable product, but it lacked some features, like rich textbox formatting and exporting to MS Word. SSRS 2008 added those features, so I thought all was right with the world. Silly me.

My client has been using some reports written for SSRS 2005. Recently we began converting their existing reporting application from MS Access 97 (please don't snicker too loud) to SSRS 2008. We started with small, simple reports. These worked out nicely, and developers were able to learn how to use the report designer fairly quickly. I began converting a much more complex report that had tons of Access code-behind and sub reports. Much on the report is determined by what the user wants to see. Creating the report in SSRS 2008 was time consuming, but it worked out nicely.

The "features" came out after I created the first, main report and checked the reporting project into Visual Source Safe. After that I started running into all kinds of problems, some of which are;

○ Very often, with no reason, credentials for the data sources would change from what we use to no credentials. I first noticed this after I checked the solution out of VSS. At first I thought the last person to check out the file changed it for some reason. Then I noticed that it didn't matter who last touched the file; even files that were never checked out were affected. And this also happened to files that were never checked into VSS.
○ Another annoyance is when a dataset changed from running a stored procedure to running text. This can happen if you modify the data source (see above), and also happened in SSRS 2005 if you merely opened the dataset definition to see what it was pulling.
○ Inconsistent behavior. For instance, the main report has 2 very similar sub reports. The user can choose to see sub report A, sub report B, or neither. Simple, right? Just set the sub-report visibility to display based on the user's choice. It worked fine if the user chose A or B; the correct sub report was displayed and the correct sub report was hidden. But if the user chose to hide both, then both display. I'm not going to post the code here, but suffice it to say the code was correct; it's being used on many other sub reports on the same main report. So how did I fix it? I moved the code to a report function. The bottom line is the very same code that didn't work as an expression of the visibility property of the sub report worked correctly when called as a function!

There's more, but this is enough for this post. I'll be adding to it soon.

Object Explorer Details in 2008 SSMS

2008-12-05T07:33:00.003-06:00

One of the things I love most in SQL 2008 SSMS is the way Microsoft redid the Object Explorer Details page. Now it shows some really useful information. You can change what columns display by right-clicking on the column bar and checking which columns you want to see.

I need to catch up

2008-12-04T08:35:00.000-06:00

It's been quite a while since I've blogged about anything. Mostly it's because I've been busy at work; creating a new reporting interface, critical disaster at one of our data centers, busy time of year. But in reality it's because I've been too lazy. I mean, really, I've got a long commute to work (1 1/2 hours by train each way) so there's no excuse why I can't do more. If nothing else, it's a way for me to capture my thoughts/actions of the day.

So, beginning today, I (once again) vow to blog more often. And I'll try to keep this blog technical; no one really wants to read about my personal life.

SQL 2008 on August price list

2008-07-10T08:14:00.002-05:00

OK, I guess this means we can order it next month. However this articlefrom a TechNet blog only states that 2008 will be on the August list, not that it will ship then.

But this is still good news. If it doesn't ship in August, surely it will be very soon after.

Catching up after vacation

2008-07-10T08:09:00.002-05:00

First and foremost, I passed 70-447 on June 27th. I'm done with the 2005 exams, not going to worry about the BI track since I'm not that fluent in SSAS or SSIS (I do know SSRS pretty well, though). So I made my goal of being certified in SQL 2005 before SQL 2008 came out.

Speaking of which, Microsoft just announced yesterday that 2008 will ship in August. More on that later.

Installing RC0, Part 1

2008-06-10T08:35:00.002-05:00

OK, so I installed SQl 2008 RC0 yesterday. It was mostly successful. But it was missing the Business Intelligence stuff. I know that there's an issue where you can't upgrade an existing BI install. But I'm running SQL2005 and I was able to install CTP6 with no problems. Unfortunately I don't remember the steps I took to install BI for SQL2008. I did uninstall CTP6 before starting in on RC0.

I've reverted back to CTP6 and I'll try again. This time I'll document the uninstall steps. I obviously missed something.

SQL 2008 RC0 is out

2008-06-06T19:08:00.002-05:00

At least it's available through TechNet and MSDN. I'm downloading it now, and I'll play with installing it on my train rides next week. I can't really dig deep since I'm concentrating on 70-447. But once that's over...

One to go..

2008-06-03T08:15:00.002-05:00

I passed 70-442 on 5/30. Now I've got my MCITP as aDatabase Developer. Next up, and probably my final SQl 2005 exam,will be the update exam 70-447. I've scheduled that one for 6/27. That means I'll have taken 3 exams in 2 months. It seems to be working. Scheduling the test early forces me to be more prepared. I can focus better on the study material when I have a real deadline in place.

Taking 70-442 Next

2008-05-05T10:14:00.002-05:00

I must be crazy, or overconfident, or both. I just scheduled to take 70-442 on May 30th. I'm figuring 2 things; 1, I'm pretty sure of the test material since I work with SQL 2005 daily and I've already read the MS Press training guide and 2, Microsoft's free Second Shot voucher expires at the end of the month. So this way, even if I fail (but that won't happen, right?)I get to retake it at no extra cost. So there's less pressure.

And since I've already passed 70-431 and 70-441, this will make me MCITP as a database developer.

Passed 70-441

2008-05-02T09:33:00.004-05:00

Yesterday I passed Microsoft's 70-441 exam, Designing Database Solutions by Using Microsoft SQL Server 2005. Since I've already passed 70-431 and I'm also a MCDBA, I just need to take (and pass) 70-442 and 70-447 to become MCITP as both a Database Developer and a Database Administrator. Maybe I can run across another free exam voucher!

I don't want to break any confidentiality rules, so I won't talk about the exam in detail. But I will say that this exam was the easist Microsoft exam I've taken to date. I've been working with SQL on a daily basis for nine years now, and 2005 since it first was a CTP. That experience, plus now being the de facto DBA on my team at work, was major in preparing for the exam. The MS Press Training Kit was good for studying areas I don't know as much about, like the Service Broker and Notification Services, and it has a good exam simulation. And of course I must thank Microsoft for having the series of 4 webcasts on 70-441 (they're available on demand, I'll post the link later), and Tim Kashani for the great job of presenting the material. Not to mention receiving the free voucher.

SQL 2005 SP3 on the way

2008-04-16T07:54:00.003-05:00

The good news: It looks like Microsoft is finally going to release Service Pack 3 for SQl 2005. The bad news: It won't be until Q4 of 2008, after the release of SQL 2008. Aaron Bertrand on SQLBlogs talks more about it here.

This is something that was a long time coming. Microsoft has something like 7 cumulative hot fixes. Some of the things on that list are things I want to start testing. One patch is for a problem I see on occasion, where our production servers kick out a SQL dump on a non-yielding process. This was addressed in SP2, which we're running, but not fully. I've seen other blogs that referenced a fix in one of the patches.

Taking 70-441 in 3 1/2 weeks

2008-04-07T15:26:00.002-05:00

Hopefully I'll have enough time to study, especially since I'm on vacation for the 2 weeks previous to that.

I've read the MS Press training kit for this exam, and I've already passed 70-431 last summer. I know that won't be enough to pass this one, bit at least i've got a starting point for my studying. I also watched 4 webcasts that the Microsoft Partner program had on last year; I'll re-watch those. Too bad they're not downloadable. They'd be perfect for my daily commute.

But I can't complain about those webcasts. I got a voucher to take the exam for free!. The only thing was that it expires on May 1st, hence the quickly approaching date.

CTP6 issue with leap year

2008-02-29T14:22:00.003-06:00

I just ran across this, ironic since I just updated a test computer from CTP5 to CTP6and wondered why it wasn't working;

"We have recently discovered an issue with SQL Server 2008 CTPs that result in SQL Server 2008 not starting or installing on Feb 29 GMT only. We recommend that you do not run, install or upgrade this CTP on Feb 29 GMT to minimize any impact in your environment. You can install starting on March 1 GMT. If you have an immediate issue that cannot wait until March 1st GMT contact csskat@microsoft.com before taking any further steps."

I mean, I know it's pre-release and all but come on - leap year?

Microsoft 2008 Launch Event

2008-02-27T07:57:00.001-06:00

Today is Microsoft's launch event for Windows Server 2008, Visual Studio 2008, and SQL 2008. If you haven't already signed up, you should do so at Heroes Happen Here. Beside's today's launch in Los Angeles there is a list of cities where the event will take place. There are a number of different track you can attend based on your interests. And the content should also be on-line for a while. The event is free to anyone who registers. Sign up soon - spots are going fast!

SQL 2008 won't be released until the 3rd quarter of this year, but I still plan to attend the launch when it comes to Chicago on March 11th. It's a good idea to get a head start on the new features. I'm still working my way through all the new features in 2005! But the good news is that most, if not all, features introduced in 2005 will stay in 2008. The one noticeable difference is Notifications - that's gone. And I still need to see what happened to the Surface Area tool introduced in the last release.

CTP6 - Backup compression

2008-02-21T18:43:00.003-06:00

The Enterprise Edition of SQL 2008 now offers the ability to compress database backups. If you use the GUI for the backup, the compression option is the last choice on the Options page

Once there you'll have 3 choices; Use the default server setting (set to 0 after installation, use sp_configure to change it), Compress backup, and Do not compress backup. If you use a script, just add the COMPRESSION keyword:

BACKUP DATABASE [AdventureWorks] TO DISK = N'C:\ADWorks.bak'
WITH NOFORMAT, INIT, NAME = N'AdventureWorks-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO

I tested backing up the AdventureWorks sample database (always a good idea if you're going to play with it). The uncompressed file size is roughly 175 MB; the compressed is about 40.25 MB. There's not much free space in the database, less that 1 MB, and the size of the database matches the uncompressed size, which is what I expected.

What I didn't expect is that the compressed backup appears to have run faster than the uncompressed backup. When testing I ran each backup 3 times and I only used the default settings (other than the compression option). Both backed up to the same disk drive and nothing else was running at the time that would have affected the backup.

Here's a sample output from the uncompressed backup:

BACKUP DATABASE [AdventureWorks] TO DISK = N'c:\ADWorks_Uncompressed.bak'
WITH NOFORMAT, INIT, NAME = N'AdventureWorks-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Processed 21864 pages for database 'AdventureWorks', file 'AdventureWorks_Data'
on file 1.
100 percent processed.
Processed 1 pages for database 'AdventureWorks', file 'AdventureWorks_Log'
on file 1.
BACKUP DATABASE successfully processed 21865 pages in 20.349 seconds (8.394 MB/sec).

And here's a sample from the compressed version:

BACKUP DATABASE [AdventureWorks] TO DISK = N'C:\ADWorks.bak'
WITH NOFORMAT, INIT, NAME = N'AdventureWorks-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO

Processed 21864 pages for database 'AdventureWorks', file 'AdventureWorks_Data'
on file 1.
100 percent processed.
Processed 2 pages for database 'AdventureWorks', file 'AdventureWorks_Log'
on file 1.

BACKUP DATABASE successfully processed 21866 pages in 13.003 seconds (13.137MB/sec).

But then I remembered Idera's SQLSafe program. I ran the evaluation version a while back. I thought the speed increase there was due to some trick of Idera's. That's not really the case. Read this from the CTP's BOL:

Performance Impact of Compressing Backups
Because a compressed backup is smaller than an uncompressed backup of the same data, compressing a backup typically requires less device I/O and therefore usually increases backup speed significantly.

By default, compression significantly increases CPU usage, and the additional CPU consumed by the compression process might adversely impact concurrent operations. Therefore, you might want to create low-priority compressed backups in a session whose CPU usage is limited by Resource Governor. For more information, see How to: Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL).

That makes sense. It takes less time to write a smaller file than a larger file. So I was looking in the wrong area. I should have been checking the CPU counters.

SQL 2008 February CTP is available

2008-02-21T13:13:00.004-06:00

CTP6 is available at http://www.microsoft.com/downloads/details.aspx?familyid=749bd760-f404-4d45-9ac0-d7f1b3ed1053&displaylang=en&tm

I've just finished installing it and I'm starting to poke around a bit. Now I haven't been keeping up with the new or changed features in SQL2008, and I havn't played with the previous CTP's. So I was surprised to see that the Area Configuration Tool is not there. What's replaced it? Or is it just missing from the CTP and it will be in the final rollout? Definitely something to look into.

The installation went pretty smooth. SQL 2008 is running next to SQL 2005 on this computer and there were no conficts during the installation. I did have one issue, though. When I tried to set up the accounts to run the different SQL services, it told me the password was wrong. No, I wasn't typing it incorrectly, I tried many times. I finally got past that by creating a new administrator account and used that to run the services. Later I'm going to go back and change what account they run under, to see if it will allow me to do that.

I'll be back when I poke around a bit more.

New SQL 2005 training resource

2008-02-16T10:28:00.003-06:00

Kalen Delaney, the author of the Inside Microsoft SQL books, is publishing a set of DVD's relating to SQL 2005. The content is also valid against the upcoming SQL 2008. You can either view the content on-line or buy the DVD. Both are great deals and far cheaper than other training videos.

Check out the site at http://sqlserverdvd.com/

Introduction, Part 2

2008-02-15T13:57:00.000-06:00

I guess it's time I give a little info about myself. I currently working as a consultant for a company in Chicago (my client is based in Nashville). Most of my duties nowadays are DBA tasks, specifically performance tuning Microsoft SQL 2005 databases.

I've been with the same company since January 2000, working for the same client the whole 8 years. I started in an entry level position, mostly writing and maintaining MSAccess 97 reports running against Sybase databases. Right after I started we migrated the databases to SQL 7, keeping Access for the reports.

We're now in the final stages on migrating all the SQL 7 individual servers into 4 regional servers, 2 here in Chicago, 2 in Nashville. In addition to merging the data, we're also upgrading to SQL 2005. It's kind of ironic that we're just finishing up when SQL 2008 is right around the corner!

This last 8 years has been a huge learning experience for me. When I started, I barely knew T-SQL and I had never worked with a true relational database before. But I slowly started getting more involved with SQL, reading everything I could get my hands on, going to user group meetings, attending different seminars, whatever would teach me about SQL. And while most of it was over my head at first, gradually some of it started to sink in. I was able to remember enough to become a MCDBA a few years back. And I recently passed Microsoft's 70-431 exam. So I'm getting there.

But the more I learn the more I realize how much I don't know. Especially in the area of performance tuning. Right now for instance I'm investigating the CMEMTHREAD wait type, something that's starting to affect us. I'll be posting a more detailed explanation when i understand exactly what's going on.

In the meantime, I'm using this blog as a place to clear my head, "talking" a problem through so I understand it better. I'll put up some neat things I learn as I go. I'll try not to be too dry, though I'm definitely not a writer of any sort.

In summary, welcome to SQL Servings!

Introduction

2008-02-01T15:54:00.000-06:00

Hi All

This is another blog about Microsoft's SQL Server products. There are numerous, more knowledgable blogs out there, and I'll reference them. This blog is more about my learning path, things I've learned about SQL, questions I have, rants I want to get off my chest.

I'll put up a bit about me later.