Voice of the DBA

Category: Editorial

  • Native Audits

    As our databases contain more and more information, it is becoming increasingly more common to implement some sort of auditing feature to ensure that our data is not being inappropriately accessed or altered. It’s not only the management of our organizations, but also regulatory rules are becoming more numerous, and some may think onerous, which increases the burden on the DBA.

    There are numerous products available to help here, but SQL Server has included it’s own internal features since SQL Server 2008. However, it seems that I encounter many people that are unaware of the SQL Server Audit feature, and indeen, may not be aware of the Extended Events system on which it’s based. This week I wanted to ask if you are using this feature.

    Are you using SQL Server’s native Server Audit and Database Audit features?

    If you can share and reasons or details on how you are using the audits, or how easy it is to administer them, it would be interesting. I think we do need to increase the level of auditing on our systems at least to ensure that we are not experiencing any inappropriate data access. At a minimum, we should be monitoring for privileged level access or account changes.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 1.8MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Naming Confusion

    When I first worked in a corporation that had many servers, I had to learn the encoding of our systems. We had SUIS01, SUIS02, NAIS01, NAOP01, etc. Each name designated a location and department, but no information as to what applications were running. That was a separate thing to learn, which thankfully didn’t change often as procuring and setting up a new system was a relatively rare event.

    However I also ran into a few systems named “Dorothy” and “Wiz”. The other administrators griped constantly about the unprofessionalism of those names. I’m not sure I think SUIS01 is better or worse than Dorothy, but that’s a separate debate. In both cases, we had to memorize what applications were actually running on which systems. Since all of them were managed by IS, regardless of the department that owned them, we were responsible for understanding the purpose of the server.

    I ran into a situation recently where a company had a server called SalesSQL, which was there production server. The development server was SalesSQL_Dev, which is fine until you see someone opening SSMS, thinking they’ve connected to the right server since they see “SalesSQL” in the connection dialog and starting to execute code. Granted, even a rookie should be able to notice the “_Dev” (or a similar “Dev_SalesSQL” name), but mistakes happen.

    I wonder sometimes if naming systems too closely to similar systems in different environments is a good or bad idea. I especially get concerned if shortcuts are taken with database names like “Sales” and “Salesqa”. I think it can be easy to confuse those systems when you become stressed or are in a hurry and are glancing at a small drop down box.

    Ultimately I’m not sure there’s a good method for naming systems that prevents confusion, and ensures people are working in the correct environment. I have tended to use suffixes in my career to denote development and test environments as I can more easily differentiate a dev environment, but I wonder if I shouldn’t be using prefixes instead. Having to retype the beginning of a name (or delete the “dev”) in the connection dialogs might force me to do extra work to connect to a production system. That little extra bit of effort might be just the thing to reduce the chance of performing development work on a live system.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Controlling Alerts

    None of us likes to be on call for our organizations. Late night phone calls, especially for something silly like adding a new user to a system or rebooting a machine, can make for irritable employees in the morning. Too many nights in a row of problem calls, legitimate or not, and I’d argue that an employee’s effectiveness during the day is substantially diminished.

    Since many of us are paid to be creative and improve systems over time, is regular firefighting a good use of our knowledge? This might be even more true if we find ourselves receiving escalation calls on a regular basis and our daily workload doesn’t change.

    I ran across a company that had set up a rule for alerts and pages. If any alert was set up in an automated system, it had to have corresponding documentation, with a resolution, in an operations manual. That’s an interesting idea, and certainly promotes the idea of performing root cause analysis and understanding your systems in advance. However does this really reduce any escalation? 

    Perhaps if there is a team available 24/7 that triages initial alerts this helps, but if there’s a small team of people that must support systems, is it worth the effort to document every issue? I suspect that a better policy is that repeating alerts, those that occur regularly, need to be analyzed and a more permanent solution implemented. I know that’s easy to say and hard to do, but that really is the best policy. At least if you want to ensure your employees are working efficiently over time.

    For those calls that are ad hoc, I recommend a log of actions taken be kept in an easily accessible location, in a text format for searching. That way if a different person receives a similar alert, at least they’ll have some idea of what actions the last person performed.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.6MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

  • VMs are not VMs

    I was at VMWare recently. One of the main things that all of the SQL Server professionals that were there tried to emphasize is that SQL Server workloads are not like other workloads. The impact on the various host resources, the stress on the storage systems, these are fundamentally different in a database server. The loads tend to be higher, but not always, however the tolerance for delays tends to be lower than for many other types of applications.

    This becomes an issue if you work in an organization that doesn’t understand the challenges of database systems. It’s entirely possible that your virtualization administators, or your storage administrators don’t recognize that the SQL Server might need more resources. Or they don’t believe the impact is greater for the organization. To be fair, that might be true, but someone other than the DBA or system administrator should decide if the database is more important than the file server and should be treated differently from an infrastructure perspective.

    No matter what level of resources your database server need, it’s not going to run like other systems. Typically this means that the density of VMs has to change when a database server is involved. As an example, I know of a system that typically has a 10:1 guest:host ratio for most of their server systems. However for SQL Servers it’s 4:1 or lower. The same is true for storage. Aggregate bandwidth doesn’t always reflect the ability of a storage system to keep up with database requests. It becomes important that both you and your storage administrators learn to speak the same language and understand what requirements exist for SQL Server VMs.

    Virtualization really starts to highlight the advantages of a DevOps environment. DBAs and developers should work closely with the virtualization and storage administrators to learn what each others’ requirements are and how each of us can help the other perform their particular job at a higher level. Infrastructure staff can help prepare standard environments and ensure production looks like staging. Developers and DBAs can help a vSphere admin learn a little PowerCLI and programming. That might get them to be more cognizant of the particular requirements of your SQL Server and be more willing to work with you.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.8MB) podcast or subscribe to the feed at iTunes and LibSyn.