How Do You Use SQL Server

Grant Fritchey took it upon himself to tag me in his most recent blog post abouthow we use SQL Server. The best part about this is I was just hired Senior DBA, so I can write with the unbridled enthusiasm of someone who is showing off a new car. Here’s a little bit of background – my employer provides freight, utility, and telecom expense management services. In short, we pay your bills, we hand key your bills, we figure out how to EDI your bills – we do everything we can to process as many bills as possible every second of every day of the year. We were also a bank – expenses can be justified but you better have a damn good reason for wanting to spend 27k per CPU for Enterprise Edition vs 7k per CPU for Standard Edition. My production servers all run Standard Edition. In a way, though, I’m very lucky. I have a homogenous environment. From development to production, everything is running on SQL Server 2008 sitting on top of Windows Server 2008. Every server gets patched on the same day. On to the reason why you came here: how we actually use SQL Server. Most people have an application tier that sits on top of some kind of data access layer that eventually talks to an RDBMS through some kind of query generation engine. The application tier makes decisions and does most of the work and talks to the database to get a result set and then pukes that into a grid on a screen somewhere. We don’t really have that. Long before I arrived a decision was made to treat SQL Server as an application platform. SQL Server is the core of our business. Before someone gets all up in arms, let me explain. When I said that we process a large quantity of bills I want you to think massive piles of paper bills being hand keyed by an army of people. Bills are being typed in, scanned in, sent in via EDI, pigeon, and Star Trek teleporter at all hours of the day. We are doing everything we can to process as much data as we can. We do batch processing. We do single line item processing. We do all kinds of crazy transformations on the data. Guess where that all happens. That’s right: in the database. Sure, we have an application layer that sits on top of the database, but it accesses the database through stored procedures and views. Yes, there is logic in the stored procedures. I am very strongly of the opinion that universal data logic belongs in the database. We make heavy use of stored procedures, constraints, views, indexed views, summary jobs… all the traditional aspects of RDMBS development. We’re leveraging SQLCLR to keep some heavy lifting in SQL Server but move it into the CLR where it belongs and also to give us the ability to create high performance ad hoc reporting capabilities. Things are very much in their infancy right now, I’m the first full time DBA at this company. Are things smooth right now? Not always. We’ve had runaway processes bring a production machine to its knees for hours. Our SAN isn’t configured optimally and I/O waits account for 1/3 of the time waits on all of our production machines. Data is still coming into the database from a legacy mainframe application written in COBOL. The monitoring is still largely a collection of hand written scripts that were thrown together by myself and the previous DBA – my manager. On the bright side, both my boss and his boss were DBAs at one point. They understand my pain points and they’re willing to give me the time to get things up and running the way they should be. In a few years, we’re replacing it with a brand new .NET application. We’ll still be using stored procedures for data access and business logic, but we’ll be expanding to use even more SQLCLR. We’ll also be picking up SQL 2008 R2 or maybe even SQL11 by that point and leveraging new technology like StreamInsight and Master Data Management. Hopefully I’ll be implementing things that were new in 2005/2008 but haven’t had the opportunity to use like Service Broker, Change Data Capture, and Performance Data Collector. To me, this is heaven. SQL Server is in the place it should be – at the core of the business. Victims, victims… I need victims… I’m going to tag Tim Benninghoff andMladen Prajdić.