Where Does the Business Logic Go?
A few years ago, when I was at the utility company, we were implementing the new customer management system. This was the first time I had ever seen a system that implemented all of the business logic in the database. Most of my experience was with the logic being on the client application.
Which one is better? I really don’t know.
In the case of the utility company, the system was new, and this was one of the first large implementations of the application. We were rolling out changes almost nightly. If we would have to have upgraded the client nightly, it would have been a pain. Instead, we applied it to a single database server, and the business logic was taken care of.
At my last company, all of the business logic was in the client application, and rolling out a service pack or hot fix to the application was a pain. Not only would our hundreds of Boston users have to be upgraded, but we’d have to upgrade our Citrix servers as well. As well as we’d plan it, it was never as easy as one might hope.
In my current company, we don’t have serious desktop management, and our ERP system has business logic in both places. We could upgrade the desktops in a few hours if necessary. Most of heavy lifting is fortunately done in the database. This thing has nested stored procedures several layers deep.
Our document management system, on the other hand would be a nightmare. All of the business logic is done by the desktop application. And it’s installed all over the company. I cringe at the thought of customizing that application. Fortunately, we haven’t had to.
I go back to the question of where the business logic should live? I like having it in the database because then I can control it. It typically makes patching easier. At the same time, it’s a performance hit. There are more stored procedures and triggers. And yet, my database servers are typically more powerful than user laptops.
I have a feeling this is almost a religious debate, and we’ll never have a “right” answer. Your comments should be interesting, and I look forward to reading them.
Consolidation Planning
Last week, my boss handed me a gift. He asked our network admin and myself what our infrastructure should look like in the next six to eighteen months.
Right now, we have about 35 primary production servers. Many of them have a web/app server and a database server on them. I would love to do some serious consolidation. Our servers almost all fit into one of four generations, all IBM hardware, the 306, 346, 3350/3550, or the 3650.
In my perfect world, our universe would look something like this:
This is what I’m hoping to do:
- Consolidate all of the MS SQL database servers into three clusters. I need to segregate these by network segment.
- Consolidate all of the application servers into three corresponding VMWare server farms.
If we do this right, we can pull it off without buying many servers. Our existing 3350/3550 and 3650 servers are perfectly capable servers. They may need additional processors or memory. I’d rather spend the money on the right SAN and less money on servers. Imagine, consolidating your infrastructure down to half of what it is today!
My question to other DBAs out there is whether or not clustering is the right option here. I’m looking at clusters for high availability. The plan was to leave the servers as physical servers for now and then to virtualize them later. Or would I be better off to have them as virtual servers and leave the clustering to VMWare.
Thoughts on Being a DBA
I’ve been working as a DBA for about five years now, and here are a few observations I’ve made. These are pretty random.
- Most performance tuning issues are caused by bad design
- Vendors should have a DBA on staff to work with their developers
- I spend more time fighting with vendor database issues than things developed in-house
- Arguing with developers keeps your skills sharp
- Triggers are not my friend
- Implementing new systems is much more fun than babysitting existing systems
- I spend more time babysiting existing systems
- It helps to understand the application before diving into the data
- If you don’t understand the business, the data is meaningless
- Having a friend who is a business analyst can be helpful. They can explain what the application is trying to do.
- Vendors rarely comment their stored procedure and trigger code
- It helps to have a plan for when things go bump in the night.
- Things eventually DO go bump in the night
- Developers always want to use the latest and greatest technology. There is something to be said for that.
- There is also something to be said for using proven, stable technologies.
A Time for Change (Management)
I’ve said before that I’m a nut for consistency. What I haven’t said yet is that I’m an even bigger nut for change management. The last two companies where I’ve worked have been small IT shops, and the lack of change management is absolutely maddening.
I have to say that a chunk of my career was spent in an IT shop that started as a mainframe shop. In fact, when I left in 2003, the mainframe still ran a big chunk of the mill. They had serious practices in place. And this is where I learned a lot of what I know about best practices for change management.
When I needed to change anything on one of my Exchange servers, I simply logged the change, my boss signed off on it, and then we moved on. It would be brought up at that day’s daily change/problem meeting. Usually it was a footnote on a report, but once in a while, the director wanted to know what the impact was or what the fallout might be. In most cases, we tried to log things before implementing them, but it wasn’t always practical.
At the utility company, I was working as a developer, and our change management requirements sometimes felt onerous, but in retrospect, they were brilliant. We had to provide a paragraph that indicated what the change would accomplish. Then we had to provide documentation that it was successful in testing. This usually required attaching before and after datasets. It seemed like a lot of work, but it worked for us.
Sometimes I really miss that structure. If I change an index on a table, I really should have a record of that change. Not only does stuff like this keep auditors happy, but it’s an amazing resource in troubleshooting.
If I get a problem with performance and the user states this problem started occurring last Friday morning, my first instinct is to check what changed. Most systems don’t just break. Problems are typically caused by unintended consequences of changes me make. In this case, I’d start looking for any changes I made Thursday night or Friday morning. When you have a system that tracks this for you, it can really work for you.
As DBAs, we tend to look down on more regulations to changing systems, but this is one I will certainly advocate. Changes shouldn’t be made on the fly. Instead, they should be done in a controlled manner, and they should be well documented.
Toys and Tools
In some of my blog posts, I mention tools to put in your arsenal. These are things you build yourself. Obviously, I live by SQL Server Management Studio. But I wanted to talk about the other tools and toys that I use or have used in the past. These are all off-the-shelf products or
- Spotlight on SQL Server – When I first landed in a DBA position, this tool was a godsend. It really helped me get an understanding for the things I should be monitoring on my servers. It also gave me a clue in real time when something was going wrong.
- Change Director – In a previous environment, our developers had administrative rights to several SQL servers. This was fantastic. Change Director has an agent that runs on the server that runs a perpetual trace and analyzes those trace files for schema and permissions changes. It would then send an alert to me (or the person responsible for that system) indicating the changes that had just been made. I’m seriously considering it for next year’s budget in my current environment.
- Toad – SSMS is good, but Toad is better. We bought Toad at my previous company because I was trying to get my users away from using Enterprise Manager for building queries. Enterprise Manager had some nasty habits, like putting locks on tables you were viewing. This was an great product for both developers and DBAs. It does things you can’t do with SSMS.
- Microsoft System Center Operations Manager – My current company has deployed SCOM for monitoring and management of our servers. It does a pretty decent job of monitoring the SQL infrastructure, but it doesn’t get as granular as I sometimes wish it would.
- SQLFarms Combine – This is a change management and deployment tool that we bought at my previous company. It lets you put SQL scripts in a bundle and then you deploy them all at once. The logging was fantastic. I tried finding their site, but it appears to be down for several days now. I wonder if they’re still in business.
- Query Express – One of the developers at my previous company turned me onto this. It’s a lightweight query tool that doesn’t require installation. You can run it from a USB key. I like it because it exports to a CSV better than SSMS.
- SQLPing – Every once in a while I need to scan our network for SQL instances that may have popped up without someone telling me. This is a great little tool to help you find SQL instances. Run it as a domain administrator, and you’re pretty much guaranteed to know everything you need.
Those are some of my toys and tools. What do you use and like?
Fizzbuzz
I recall reading Brent Ozar’s post on questions to ask DBAs in an interview.
Unable to sleep last night, I was trying to solve the Fizzbuzz problem in my head. I knew it could be done with T-SQL, but I hadn’t given it too much thought.
I’m lying in bed mapping out the code in my head. First, I would create a table containing a primary key of integers from 1 to 100. Then I would run several update statements to set the second column to what would be displayed.
I fell asleep thinking I had solved the problem fairly well. This morning, I sat down in front of my computer at work, wanting to test my plan. Suddenly, it hit me. I had made the solution much more complicated than it needed to be.
Dear readers, I give you my solution in twelve lines of code.
declare @i int
select @i = 1
while @i <= 100
BEGIN
PRINT CASE
WHEN @i %3 = 0 AND @i % 5 = 0 THEN ‘FIZZBUZZ’
WHEN @i % 3 = 0 THEN ‘FIZZ’
WHEN @i % 5 = 0 THEN ‘BUZZ’
ELSE CAST(@i AS NVARCHAR(30))
END
select @i = @i + 1
END
I’m always looking for a better way of doing things. While this is a much more elegant solution than I had originally found, I’m wondering if it can be any simpler.
Backup Agents: Good, Bad, or Ugly?
Do you use database backup agents to back up your database or do you write your backups to disk and use a file system backup agent for sending them to long-term storage?
I’ve seen this spark religious debates between DBAs and network administrators.
As a network administrator, I like being able to point the backup software at a server, let it recognize that it’s a SQL instance and tell it to back it up. It’s simple.
As a DBA, the thought of using a backup agent makes me cringe. The agents work for full backups. They work incredibly well. They’re also a complete pain in the ass when you have to do a restore. As a micromanaging control freak, I like being able to tell my restore jobs EXACTLY where a file should go and how it should be named. Most agents don’t give me that granular control.
As a network administrator, the thought of writing a backup to a file first before sending it to long term storage (does anybody actually still use tape?) seems like an extra step we don’t need. It also means we have files sitting around we probably don’t need. (My inner DBA is screaming at that one.)
As a DBA, I worry about my transaction logs. I haven’t seen backup agents handle t-logs very well in the past.
As a network administrator, I like to know that my stuff is working. I trust what I control.
As a DBA, I like to know that my stuff is working. I trust what I control.
I could let my experiences in two different roles argue this out for a while, and it could get quite heated.
At my former employer, we used a backup agent. I hated it. I also had no choice in the matter. Every time I brought it up, I got shot down. In my current environment, we write everything to disk before sending anything off-site.
I’ve seen both. And I’ve seen both work. I’m a fan of writing to disk first. What do you do?
Using sysdepends the old fashioned way
SQL Server gives us a pretty good GUI for most things. Every once in a while, though, it’s pretty rewarding to dig into the system tables to get this information the old fashioned way.
I was working on a problem with a stored procedure on a SQL 2000 instance. This procedure called procedures and functions that called procedures and functions about six layers deep. The GUI wasn’t quite helping me find the data I wanted, so I did it myself.
By joining sysdepends to sysobjects twice, I got everything I needed. One instance of sysobjects is for the name of the object we’re investigating. The other instance is for all of the objects that the first object depends on.
The query itself is pretty simple. Notice that I’m also pulling the type of the object as well. This can come in handy. Then I filtered it to only show me the procedures and functions.
select distinct obj.name, dep.name, dep.xtype
from sysdepends sd
inner join sysobjects obj
on sd.id = obj.id
inner join sysobjects dep
on sd.depid = dep.id
where obj.name = ‘procname’
and dep.xtype IN (‘FN’, ‘P’)
This gave me exactly what I was looking for. But let me take it one more step.
I did find one caveat to this process. If any of these dependent stored procedures call other stored procedures, I’ll need to repeat the process with those stored procedures. In our current ERP system, we have nested stored procedures like you wouldn’t believe.
An Ounce of Prevention
Having worked in some large IT organizations, coming into a small shop can be maddening at times.
When a drive goes bad in a server, we call IBM and they overnight us a new drive. A few months ago, I had a drive go bad, and the server was no longer under warranty. IBM didn’t overnight us a drive, and rightfully so. We had to go through our normal purchasing channels to replace the drive. Lets say that our purchasing process isn’t exactly fast.
Why not have them in stock and then use the incoming drive from IBM to replenish the stock?
The IBM x346 is a workhorse in our environment. We have at least a dozen of them and they’re all coming out of warranty within a few months of each other. If I lose a drive in a database server, I’m not sure I want to wait a week for a replacement drive to arrive. Several weeks ago, I bought a handful of spare drives for this server. My boss thought I was just being a little paranoid.
This morning, my instincts served me well. We lost a drive in the database server that runs our ERP system. I ran to the cabinet and grabbed a spare. The drive was replaced before we put in the support call to IBM. Jack at IBM Support in Atlanta told me what I already knew. This particular server was no longer under warranty. They weren’t going to be sending me a new drive. Had I had to wait a week for a replacement drive, you can bet I wouldn’t sleep well until that new drive had been put in.
My dad always says to trust your gut. I like to think that I’ve developed good instincts over the years. And they’re usually right.
(nolock)
A few years ago, I was working on a big project doing some report writing. This particular client had a rule that every report have an underlying stored procedure. That was one of those things that sounds great in theory, but the practical applications are less impressive.
Most of the reports were scheduled to be run at 4:00 AM, and occasionally, the reports would fight with each other, causing the occasional deadlock. Someone found a reference to WITH (NOLOCK) in the books online. Before too long, this locking hint became pretty standard in the FROM clause of most stored procedures. Since the reports were being run when the system had no updates going on, this was perfectly acceptable.
And then…
Someone wrote a process that imported data from a file and then updated a table with that data. This particular individual was pretty good with Crystal, but she wasn’t quite so versed in SQL. Because she’d been writing stored procedures to use WITH (NOLOCK) on every other SQL statement, she used it in an update statement as well.
DBAs are known to drop the F-bomb once in a while, but what I heard from this particular DBA that day was legendary. He was not impressed. The statement looked something like this:
UPDATE tablename
SET column = value
FROM tablename WITH (NOLOCK)
…
Fortunately, this particular DBA was on top of his game and shot it down before it got approved by the business to be put into production.
Locking hints can be a very powerful tool. They can speed up queries by bypassing locks. But when used improperly, they can cause really bad results.