Voice of the DBA

Tag: syndicated

T-SQL Tuesday #31 – Logging

It’s T-SQL Tuesday time again, and this month Aaron Nelson (blog | @sqlvariant) is hosting. The topic is logging, and if you’re like to participate, read Aaron’s post and learn the rules. We do this on the second Tuesday of every month.

If you’d like to host, contact Adam Machanic. It’s easy to do. Get on the schedule, pick a topic, and then write a post.

A list of previous posts is here,

Logging

I’ve found documentation of events to be one of the most important things I can do in my career. Finding out what happened, what changed, or what I did has been important many times, and often helped me come through difficult situations.

Logging is the automated version of documentation. All kinds of applications, including SQL Server, produce logs of the various activity on the system. In SQL Server, we are moving to an eventing system, and if you haven’t looked at Extended Events, you should.

One of the times when I found logging to be lacking was in a startup I worked at a decade ago. We had a number of developers that were working on various development servers. They had full rights, and they were allowed to build their own objects. That was a little concern to a controlling DBA like me, but I allowed it since they often wanted new objects quickly, and if I allowed them to write their own, they’d use stored procedures.

A good compromise, if you ask me.

However in the hectic pace of development, I found that the developers didn’t often keep good notes about what was being built for which features and functions. Since we had to produce a build script fairly quickly every Monday in order to update our QA systems, we would find that developers invariably would forget objects and we would not have a well tested QA script on Monday afternoon.

I decided that we needed to better log the changes on our development server. I didn’t care about every change, especially intermediate changes to objects, but I did care about the gross changes made each day.

This was in the SQL Server 2000 days, with limited tracking of changes outside of SQL Trace. Since I had no desire to move through lots of trace files, even in an automated fashion, I decided on a much simpler method.

In sysobjects (now sys.objects), there was a crdate field, which tells you when the object was created. However that doesn’t change if an ALTER TABLE is run (or any other ALTER). That stumped me briefly, but I decided to search further.

I found that there was a schema_ver field, which is incremented every time the object is changed. Since the majority of our developer changes were ALTERs, I could track the version number and then compare this each day. I tested this out, and it worked well.

The outline of the solution is that I grabbed a copy of the sysobjects table every day and stored it in a temporary table. I then used a left join to compare this with the previous values stored in a table I’d created to store the data. When I found differences, I logged them in a table, along with the date, and sent myself an email. I would then overwrite the stored version of the objects with the version from the temp table, giving me a baseline for the next execution.

At the end of the week, I’d have an aggregate list of all objects changed, which I could then compare against our build script.

At the time we were in an agile environment, releasing new code every Wednesday, and operating on very short timelines. The logging I did cut down on mistakes and allowed us to have a smooth release process that functioned for over 18 months, with code releases nearly every Wednesday outside of holidays.

June 12, 2012
Slipping in a Break

I know some people like long holidays, but I tend to prefer short ones. Today is one of those breaks for me. I’m coming back from Pensacola and SQL Saturday #132, taking the day off as I spent Sunday and the morning with my daughter.

This was a combination of work on Fri night/Sat for the event, and then a mini-vacation on Fri morning, Sunday, and Monday morning for the two of us. A little Daddy/daughter time that we both enjoy and look forward to.

Remember to get those breaks in when you can, and take advantage of the chance to spend time away from work if you are traveling and can fit it in.

June 11, 2012
The Exceptional DBA Awards – 2012

Who will be the Exceptional DBA of 2012? I’ll be judging.

It’s hard to believe that it’s time for the Exceptional DBA awards once again. It was just a few months ago that I watched the presentation of the award to Jeff Moden in Seattle. However time flies, especially as I get older, and the nominations are once again open for the 2012 award. You can enter yourself or someone else between now and June 29th, so take a chance and enter if you think you’re doing a great job as a DBA for your company.

The previous winners have been ordinary people, just like most of you out there, that go to work, do their job, and are relatively unknown. They are exceptional, however, and their applications have shown that. These are people that go above and beyond what others do. They under-promise, over-deliver, and they help others. Those are the types of traits and characteristics that I get from their nomination forms. It’s interesting to see that many of our past finalists not only entered the contest, but were entered by a co-worker. That in and of itself says something about their work when a friend is willing to nominate them.

I’m judging again this year, along with Brad McGehee, Grant Fritchey, and Rodney Landrum. We’ve all down this before, and we all have lots of experience with people that stand out, and do not stand out, at work and in the community. We each have a slightly different take on the award, but we all get together and debate and decide who the finalists will be.

From my perspective, I want to see someone that does two main things. First, they’re effective. I have always been very successful at different jobs because I get things done. I ensure the systems work, and I solve problems, without getting bogged down in arguments, religious wars, or personal conflicts. My job first and foremost is to ensure that things work as people expect. I’m looking for those traits in an Exceptional DBA. Second, I want to see someone that makes others better, that helps the community. It doesn’t have to be by speaking, or blogging, or any one particular thing, but there are lots of smart people out there that get things done. I think the exceptional ones also make others better, either inside their company, in their area, or throughout the world. They share what they know and help lift up everyone else’s skills.

If you think you qualify, take some time and put together a nomination. If you think there’s someone that’s helped you and does a great job, nominate them. Make sure you spend a little time drafting, and proofing what you write. Good communication is essential here, and we all are writers for a living, so make sure you send us your best effort.

Enter today, and you may be the Exceptional DBA of 2012.

June 11, 2012
Contained Databases – Preventing Collation Conflicts
One of the demos from my Contained Databases talk looks at the issues you can have when your database collation does not match your server collation. I’ll walk through the issue here. I’ll show the issue, and then the fix with contained databases.

First, let’s create a database and a table:
```
-- create db without containment
CREATE DATABASE ucdb2
 COLLATE Japanese_CS_AS
;
go
USE ucdb2
;
go


-- Create Unicode Table, add a row
CREATE TABLE MyTable
( mychar NVARCHAR(200)
);
go
INSERT MyTable SELECT 'This is a Japanese Row'
go
```
My server collation is shown here (SQL_Latin1_General_CP1_CI_AS:

Now let’s create the exact same table in tempdb.
```
-- create temp unicode table
CREATE TABLE #MyTable
( mychar NVARCHAR(200)
);
go
INSERT #MyTable SELECT 'This is a Japanese Row'
go
```
If I try to work with these two tables together, I will have problems. As an example, let’s just union query these two items together.
```
SELECT 
  'udcb2'
,  mychar
 FROM MyTable
UNION ALL
SELECT 
  'tempdb'
, mychar
 FROM #MyTable
;
go
```
I get an error, as shown here:

The collation error occurs because the query optimizer can’t decide which collation to use. You can easily fix this, as I’ve blogged about before with a collation clause.

However contained databases mean you don’t have to change code. Let’s do the same thing, this time with a contained database.
```
-- create db with containment
CREATE DATABASE cdb2
 CONTAINMENT = PARTIAL
 COLLATE Japanese_CS_AS
;
go
USE cdb2
;
go

-- Create Unicode Table, add a row
CREATE TABLE MyTable
( mychar NVARCHAR(200)
);
go
INSERT MyTable SELECT 'This is a Japanese Row'
go

-- create temp unicode table
CREATE TABLE #MyTable
( mychar NVARCHAR(200)
);
go
INSERT #MyTable SELECT 'This is a Japanese Row'
go
```
Now if I run the same statement, I see:

The contained database has correctly resolved the collation issues.

You can check the collations with sp_help with the two table names.
June 7, 2012