Tag: administration

One of the interesting facts I saw a few years ago talked about storage in enterprise environments. There was research that showed many enterprise applications had 6 or 7 copies of their large databases inside the organization. In addition to the production copy, there were many other copies in use, resulting in an explosion of growth. That wasn’t surprising, and it was one of the drivers for implementing compression in many databases.

While the cost of storage is constantly coming down, it’s still expensive for enterprise class hardware, especially in a large SAN device. Today I wanted to ask those of you that work on real world systems to make a quick count of your own system, and let us know. I can’t decide if 6 copies of a production database is high, or low.

How many copies, on average, of your production databases are in your company?

I suppose you could count backups as a copy, since it’s disk space usage and you have to pay for it. If you count backups, let us know, but I’m thinking just about the test systems, development systems, HA or DR systems that might receive copies of the data. Some of those secondary systems might be in use for other purposes, such as reporting from readable secondaries in an AlwaysOn scenario. Whether they are or not, they are still copies of your database.

I used to think that four or five copies would be a lot, but with the advances in technology that allow different DR options, and the cheap local storage available on today’s desktops and laptops, I wonder if seven or eight copies might be more accurate.

Take a count today; you might surprise yourself with the results.

Steve Jones

If you are looking to reduce the cost of storing all those copies of your data, take a look at SQL Storage Compress, Virtual Restore, or SQL Backup Pro from Red Gate Software.

The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

• Watch the Windows Media Podcast – 23.5MB WMV 
• Watch the iPod Video Podcast – 19.8MB MP4 
• Listen to the MP3 Audio Podcast – 4.0MB MP3 

Do you have a DBA database on all your instances? I’ve always kept a small database on all instances, usually standardized with a set of tables and procedures that I used to monitor and track activity on the instance. By keeping this fairly standard, I could script and deploy it during all new installs as well as easily aggregate information from all instances on a central server, usually in a slightly larger version of my DBA database.

It’s nice to see more and more DBAs using this same technique in their environments. Over the last few years I’ve seen lots of articles and blog posts that recommend building a DBA database and populating it with DBA-stuff. That DBA-stuff can be anything from tracking backup sizes, to storing performance metrics, to keeping trace data. I’ve seen some neat implementations with Service Broker that use the DBA database as a repository for a queue to which they can send messages. Based on those message, they can have the instance  perform some action.

There are any number of standards or corporate reasons not to include extra databases, but none of them really make sense. The DBA database, and any administrative tasks that use this database essentially act as a proxy for the DBA. Almost every piece of data stored in this database is data that the DBA would query or use on a regular basis. Keeping it inside a database set aside for this purpose allows the DBA to act more efficiently.

If you haven’t built a DBA database, I’d encourage you to do so on all your instances. Secure it so only sysadmins can access it, but use it to capture and store information about the ongoing health of your SQL Server.

Steve Jones

The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

• Watch the Windows Media Podcast – 14.2MB WMV 
• Watch the iPod Video Podcast – 16.0MB MP4 
• Watch the MP3 Audio Podcast – 3.2MB MP3 

This editorial was originally published on Sept 5, 2007. It is being re-run as Steve is traveling.

I know some people get a kick out of running the KILL command. Heck, I’m sure all of us enjoy stopping a runaway process from an annoying user at times. But not everyone seems to understand exactly how databases work. It’s not necessarily a knock on the original poster, after all, most of us had to learn about the ACID properties at some point. Perhaps even after someone dropped a database in our lap without warning.

If you kill a spid, I saw some confusion about data loss in a recent thread. You won’t lose data, but you could still have a problem. It’s not a technical problem and the storage engine inside SQL Server ensures that the transaction conforms to the ACID properties to ensure data integrity.

The users, however, might or might not realize that their “work” was not done. It depends on the error handling of your application, what message (if any) that is presented to the user, and if they’re even still around. I’ve seen people start processes they expected to run long and then leave their workstation. If there wasn’t a message on it later, they might assume the transaction had gone through.

Even if you tell them you’re killing the process, they might still think that some amount of work is completed. There are all sorts of users, with all different kinds of expectations out there, so you should be sure that they understand exactly what is happening.

And be sure that they know their “work” is lost. From the point of view of someone doing data entry, if the transaction rolls back and the application can’t handle it, they will have to enter information again. So their work is essentially lost.

Those of us in technology sometimes forget the impact of our systems in the real world. Even when things work well or as designed, they may still be a problem for real people that have real tasks to get done.