How do -you- (I) use SQL Server?

I was recently tagged in an interesting blog post by A Misplaced New Englander, David Taylor. (dyfhid on twitter). The question sounds simple enough, “How do you use SQL Server where you work?” Of course the answer is not simple. I echo Grant Fritchey’s response in his blog post that it would be easier to post about how it’s not used. That being said we’ll see what we can do…

What’s my day job Vertical?

As a consultant part time nights/weekends I get to experience a lot of different environments and uses of SQL Server. Rather than talk about those, I’ll talk about the day job in general terms. I work for a large, global property and casualty insurer. We don’t write a ton of policies in the subsidiary I work at, but we write some very expensive and interesting policies. From oil rigs to directors and officers insurance to specialty cargo lines we insure it. That means we have to be quite adaptive to very different data sources with widely varying pertinent information about each of a multitude of lines. We don’t have huge databases but they are complex databases as a result.

Our primary claims system is built on DB2 and it feeds several systems that utilize SQL Server as a back end.

How Is SQL Server Used?

Some key uses:

• SharePoint – We use a global SharePoint (MOSS 2007) release to share information between regional teams and business functions. A few hundred GB of content databases so far in a relatively young implementation. As DBA I work closely with the SharePoint team in making sure performance is right, maintenance is right and things are working smoothly. SQL 2005 back-end but currently in the process of migrating to a SQL Server 2008 cluster on Windows 2008 we are building out in the coming weeks.
• Data Warehouses – There are several key data warehouses in SQL Server 2005. These warehouses track information about past claims, customers, financials, etc. These warehouses feed reports (in Reporting Services 2005 currently) and Cubes (analysis services 2005) which also feed those reports. As DBA I work closely with the warehouse teams in analyzing performance concerns, suggesting architecture and query improvements for performance and help with troubleshooting, availability and reliability decisions.
• Enterprise Service Bus – We have several applications built either on SharePoint web parts or .net web services. Lots of work with Biz Talk as our service bus. Data flows through to SQL Server and a lot of xml passing into and out of SharePoint. As DBA, I work closely with the dev teams in getting deployments done, performing code reviews and helping them write better performing code.
• Various ISV Provided apps – SAP is the financial suite we are in the middle of deploying (on SQL 2005) to replace Great Plains. We also support several ISV applications that perform various business back office functions or IT functions.

What do I do?

I am the sole production DBA. There are roughly 20 production SQL instances I manage and at least double that number of dev/test/uat instances I manage. Following the philosophy that the DBA is the data advocate at a company, I perform normal DBA duties but also spend a lot of time working with developers in understanding impacts of coding and design decisions. I also spend a lot of time in meetings discussing future plans, time lobbying the infrastructure team for improved hardware needs and helping to change the way folks access environment for deploys and development support.

My primary duty is to keep the lights on and make sure I am invisible to the end end users (the customers of IT as a whole.. I try to be very visible to developers as needed, but not to the end users clicking on reports, using the Operational Data Store through the ESB. Not invisible in a “not gonna help you” point of view. Invisible in the sense of, you shouldnt’ experience any issues that are database related so you shouldn’t have to know there is a such role as DBA, in a way. Does that make sense?

I help analyze new technology with the architecture teams and try to have a say in balancing technology to solve real business problems or technology for technology’s sake (tough line to tow sometimes at most places with geeky architecture teams… who doesn’t love new stuff?)

I give impromptu courses to developers on performance tuning their own code, spend time hand holding some folks through indexing and design considerations and try to share my knowledge wherever and whenever possible.

What Technologies do I use?

Besides the normal SQL Server tools and other free tools I’ve blogged about (Microsoft Assessment and Planning tool to enumerate all SQL instances on our network or Performance Analysis for Logs for performing benchmarks and tuning) I use several tools day to day…

• Monitoring Tools – We use Systems Center and Operations Manager (SCOM) for a large portion of our monitoring needs.  I also use a few vendor provided SQL Server specific tools to keep track on my environment.
• Development/Comparison/Etc Tools
  
• Word – Documentation is important, I blogged about my thoughts there. I try to use this when possible to at least do screenshots of installation processes but hopefully create more detailed documentation.
• E-Mail – A lot of folks wish I didn’t use it so much. I wish I didn’t. Something to work on in 2010 is improving conciseness and taking things out of e-mail (where it’s too easy to be misunderstood or escalate needlessly) and into in person/on phone meetings.
• Change Data Capture – We are playing with a really neat tool from a vendor (found out about them at a User Group meeting they sponsored.) This tool captures data changes from our source claims system in real time rather than through triggers and offers a huge performance improvement over DB2 OLEDB open queries across a Linked Server.

What are some Challenges I face?

Query performance. A lot of room for improvement and working with developers there. SQL Sprawl – Not multiple servers but “over instancing”, fewer servers  (saves on licensing) but many instances leading to some performance issues. We are going through a process of separating what should be separated and bulking up with either multiple instances (or single instance used for multiple purposes) for the applications that can handle it.

Reporting performance and a “self service” MS Excel culture in the end user community. A lot of the warehouse reports are high row count reports as a result. Lots of rows returned simply to be exported into, and massaged by, Excel. This creates some performance issues with the way that SQL Server 2005 Reporting Services renders reports. There is a plan to investigate SSRS 2008 as some key changes will provide some relief. It’s just tough to find the time with a single DBA 🙂 Q1 2010 at this point. In the meantime, the self service aspect seems to really bode well for Power Pivot. I hope to create some demos in my spare time (I really want to learn Power Pivot and really feel it will be a huge win for my company) to show how cutting out the “middle man” of Reporting Services and giving richer tools for pivoting, formatting and massaging data will improve performance, reduce server load and make our users happier.

Rolling out new policies/procedures. Before me there was no official DBA, just reluctant DBAs from the team of sysadmins. As a result some of the best practices I enjoy seeing with SQL Security and deployment processes aren’t rolled out everywhere. It’s a slow process to go through proper channels but we are getting there. For instance, most environments have finally had their [Full Recovery Model, No Log Backup, Nightly Shrink Job] pattern fixed now.

The Final Verdict?

SQL is working well for our needs. Where there are challenges, I would imagine we would face them regardless of our DBMS. Most problems faced by most companies don’t seem to be specific technology related issues but rather timeline or process related issues blamed on technology. Here we have some good processes, are always working on improvements and our business processes and timelines work well for the most part resulting in a largely positive experience.

I look forward to getting more 2008 implementations out there (using it myself right now for Policy Based Management of other instances and the new SQL 2008 SharePoint cluster will be live soon. Hopefully the RS/AS 2008 for the warehouse reports in production by Q1/Q2). It’s fun watching developers increase their knowledge and watch the improved code over time. Sure there are some long days and weeks. Sure there are some tough weekends but all in all, this is fun. Every day of working with SQL Server as a DBA, “Performance Geek” or self proclaimed Data Architect is a fun time and I still treat the job with excitement most of the time.

Tag – You’re it

Rather than tag anyone specifically as quite a few have been tagged in posts already, what about you? Consider this an open tag (pretend I sneezed in a crowded movie theater and each invisible spore that left my nose is a mini hand reaching out to tag you… Sorry.. “outbreak” was on in the background while writing this post). How do you use SQL? What are some challenges you face? Add a comment, write a post and link back to the original thread Dave started or reference this post so I can see your response.