Voice of the DBA

Author: way0utwest

DRI or No DRI?

This editorial was originally published on Feb 19, 2006. We are reprinting pieces this week as Steve is traveling in the UK.

There is a great debate going on in the forums about using PKs/FKs to enforce RI, in other words, declarative referential integrity (DRI) as opposed to using code logic to ensure that proper relationships are maintained in a schema. The opening post is a DBA stunned that developers in his new company do not use DRI. The reason given is performance is degraded, and that’s one that I can buy. There is additional overhead for checks with DRI, but it’s minimal and actually David Poole has a great article about this topic coming soon.

It’s interesting that one of the questions is about how many 3rd party applications do not use DRI in their databases. Who knows why, though my suspicion is that they can easily “fix” issues with the applications with backend updates rather than maintaining good DB design practices. Or that they can easily alter the application to meet changing needs at various customers’ sites.

I think it’s mostly the latter reason. That and laziness. I think the vast majority of developers are lazy by nature and dealing with DRI constraints when building an application is a pain. It’s a pain for me, but I still think it’s a good idea. Just like stored procedures add some overhead, so does DRI and many developers I have seen, both building shrink wrap and corporate software, don’t want to deal with the overhead.

Personally I like having DRI implemented for the same reason that developers use objects, libraries, functions, and other consolidation techniques. It centralizes the “rules” about your application and ensures that they are always used. I agree there is overhead, but it’s a minimal amount and if you are seeing this on most servers then you are probably underpowered.

My big concern is that often multiple applications or even multiple modules of a single application often need the same business rules: like no orders unless we have a valid customer number. If you depend on the application to enforce this, then you are gambling that every developer will do it correctly and the same way. In small, tightly controlled and managed environments, this works great. But as you grow your development teams, then it is easy for someone to forget to implement some RI rule or implement it differently than others.

And those bugs are hard to find.

I don’t think that every application needs DRI setup in it, but if you choose to not implement it, be sure you understand the consequences of your actions. And your boss does as well.

Steve Jones

July 14, 2011
Here’s a Reason to Document

A search engine for code

This editorial was originally published on Feb 17, 2006. Steve is traveling in the UK this week and we are re-printing some old pieces.

A new search engine, Krugle, set to launch next month, is supposed to focus on code. Mostly open source code, but also places like SQLServerCentral.com, which has scripts and other types of code available. Wired has a good article on the idea behind this search.

It’s an interesting idea, though given the types of comments and the wide range of ways that things are described, I wonder if it will work. I know that we get lots of posts in the forums that I easily find answers for using Google when other don’t. I suspect that I am just searching on better terms than others. Course that doesn’t always work for my own questions, so perhaps it’s a second set of eyes that really help.

However this also depends on the actual coders spending some time to properly describe their system. That’s always a difficult task. But even assuming that they will describe their code well, how will you know if it will “plug in” easily to your system? I’ve seen lots of code that wouldn’t easily integrate with something I was writing.

And if I checked out 3 or 4 of these incompatible, or not easily integrated systems, from a search engine, I’d be tempted to just write my own. Actually I think lots of developers write their own code, or build on a base and then do everything themselves because it takes so long to integrate disparate code at times.

Still it’s a great idea and I hope it works.

Update Jul 13, 2011: Has anyone used, or is using, Krugle?

July 13, 2011
Bad Eyes

This editorial was originally published on Jan 9, 2006. Steve is traveling to the UK this week and we are reprinting a few older editorials.

It seems that most DBAs that I know are a little bit older and more experienced. It’s fairly rare that I see someone really young, right out of college, early 20s, etc., as a DBA. So when I saw this question on Slashdot about computers and vision, it caught my eye. I’m one of those “visually challenged” individuals that asked the question. And I’ve been working with computers pretty much since 1991 as a career. In middle school I slowly found out that my vision was failing. That was when my career as a baseball catcher ended. With glasses on, I couldn’t pull off the mask without pulling the glasses with them and ensuring that I’d catch the ball with my cheek instead of my glove 🙂

I’m not a 15 hour a day computer user, but I am a 7-8 or more user. Fortunately it’s not all at one stretch. Usually it’s early am, then a break, then most of the day until the kids come home, and then late at night. At least the last few years it’s been like that. Before that I was mostly a 7-8 hour stretch person and a little at night.

Through all that, including 5-6 years of working as a bartender in a late night, smoky atmosphere, I’ve had pretty much the same prescription for about 15 years. I switched to contacts in high school, but since college I’ve pretty much been the same, blind 20-400 in both eyes. I’m getting an eye exam today, so we’ll see if it holds for another year.

I think the person asking the question has other problems, 15 hours a day in front of the computer being the least of them, but it still is worth asking the question. Is a monitor bad for your eyes? Is it any different than staring at anything in a repetitive work environment.

One of the things suggested is a light source behind the monitor. That’s an interesting one. Especially for the guys that like to work in the dark. I’ve had a number of co-workers that insisted on working in the dark. Personally I’m an outside, ambient light guy. I like to sit on my shaded porch and work with the laptop in nice weather.

So do any of you have issues working in front of your monitor? Any suggestions to take better care of your eyes? I’m sure everyone in the community would be interested in this.

Steve Jones

July 12, 2011
Striping Backups in SQL Backup Pro

There are times you really do want to stripe backups across multiple files. If you have a short backup window, or a large data set, if you can write the backup to multiple files at the same time, you can drastically reduce your backup window. I think I heard one person say they had a client that was backing up a 2TB database in around 30 minutes with striping. Now that’s cooking with gas!

My company, Red Gate Software, makes a nice backup product called SQL Backup Pro, which offers a number of features, compression and encryption being the two most notable ones. However I noticed at one point that it supported striping as well, so I decided to play with it.

I started the backups, picking a database and then getting to the backup file screen.

There’s a drop down in the upper left, which gives you a few choices. One of these is the backup and mirror and the other is a backup to multiple files.

I selected the “Split backup into multiple files”, which equates to striping, and then added a file to the file dialog.

However I wanted to add a second file. I clicked “Add” again.

It’s nice that the names are changed, but adding a second file to the same path isn’t likely to give me much more throughput on the backup. Both files would write to the same I/O device. I was thinking that “Add” might give me a dialog for a different drive/path, but it didn’t.

Instead I needed to click on the ellipsis next to the folder (I’ve added the arrow in the image below):

This brought me to a dialog that shows me the drives my machine can see. I didn’t test UNC paths, but I assume any valid path would work. You can set a path, or you can pick one, and you can even create a new folder. Handy since this could be a remote machine. The SQL Backup Pro client is like SSMS, it’s a client view to a remote service.

I select the D: drive on my machine, which is actually a separate physical drive.

I get back to the main dialog, and this time when I click “Add”, I get the desired result. A second file on a different path.

I run the backup, and sure enough, I have files in two places, each a little over 1MB. There’s some overhead, so it’s not a completely even stripe.

As a comparison, I’ve highlighted a regular backup I made after this, same db, with one file:

That seems a little cumbersome, but I’d anticipate that you would only set this up once for each set up databases, and since you can easily script a SQL Backup set of commands (on the last screen) or create template, this shouldn’t be too hard to manage. In fact, the command for multiple databases does support striping as well, as in this example:

EXECUTE master..sqlbackup ‘-SQL “BACKUP DATABASES [AdventureWorks,AdventureWorks_SSC,AdventureWorks2008R2]

TO DISK = ”F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\<AUTO>_2.sqb”,

DISK = ”C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\<AUTO>_1.sqb”

WITH DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10″‘

Since striping is usually done with only very large databases, this might not even be necessary. Most databases won’t need striping, and you can set a template for them that backs up to whatever location you wish.

SQL Backup Pro has some nice features, and allows you to easily manage lots of backups across lots of servers. The compression ratios are nice and it has some great features, like this striping of backups, that can really improve your backup performance.

July 11, 2011