Voice of the DBA

Tag: sql server

  • Proving Your Identity

    I caught this article on Dark Reading that talks about the problems of multi-factor authentication. It’s interesting to me as I had written an editorial on passwords and mentioned that I used the fingerprint reader on my laptop. Someone pointed out that my fingerprints are likely all over the machine and could easily be lifted and used to gain access to the machine. That’s true, and it’s something I hadn’t thought of. To me a fingerprint reader is a convenience, but I might need to disable it for travel.

    As database professionals, we often rely on some other system to prove a person’s identity. For SQL Server, we typically rely on two common choices: a simple name and password, or some security token from the operating system. Those two have worked well, and since SQL Server 2005, we have also had additional encryption options that can be used to protect data, including the ability to use certificates to protect the keys that encrypt data. SQL Server 2008 also allowed Extensible Key Management, so that third party products could be used to secure data.

    As we store more and more data, and this data becomes valuable, it is more and more likely that individuals will try to steal data. While we can’t protect the data from insiders that need legitimate access, we do need to ensure that rights are properly granted and that our security systems have some way to verify the identity of the person or application that connects to SQL Server.

    It still feels like that the security mechanisms for SQL Server as a little immature, and the costs of implementing things like EKM are too high. I am hoping that it becomes more practical to implement better security over time, and we get more tools in SQL Server that help administrators manage permissions.

    Steve Jones

  • More Triggers

    In the old days of T-SQL, back when we wrote “CREATE TRIGGER …. FOR INSERT” we could only have one insert/update/delete trigger for each table. Eventually SQL Server allowed us to have multiple triggers, and even have some control over in what order the triggers fired.

    Triggers are often hidden objects that confound DBAs who aren’t aware they exist. It’s not easy to tell when a table has a trigger on it, and since we don’t often use triggers, it’s not the first place people look when something strange happens.

    However triggers are useful, and it seems that there are many people using them. For this Friday, I wanted to ask how people implement triggers in their applications.

    Do you prefer one trigger for each table action or multiple triggers?

    I’m curious what’s the 80 in your 0/20 rule for triggers. Should all update actions be handled in one trigger? Or should there be one trigger for business logic  and a separate one for auditing? I’m not sure it matters a lot for performance, but I can see that it might be easier to manage and track fewer triggers. The flip side is that something like auditing can be handled with one trigger, and business logic with another: a clean separation.

    Triggers aren’t usually my first solution to a problem, but I do think there is value in using them. However I don’t see a lot of guidance about how to best implement them, so I’m hoping your answers today will help.

    Steve Jones

  • Enterprise Management

    One of the things that I’ve had to do in my career is manage large numbers of servers. There was a time when two of us managed several hundred instances of SQL Server, and managed to do it very well with a lot of automation. Recently I saw someone post a note about having 400+ servers to manage, and they were wondering how to perform enterprise management. In Oracle environments, they mentioned using OEM, RMAN and other tools to manage backups centrally and wanted to know what SQL Server solutions were out there.

    I know things have changed quite a bit from the SQL Server 2000 days when we had to build all our own tools, so I wanted to ask the question as a poll this Friday:

    What do you do for central management of lots of instances?

    If you have more than 50 instances, what tools do you use to try and ensure you have a well managed environment. With the addition of the Central Management Server and Policy Based Management  to SQL Server, it definitely is easier to handle a larger load of instances

    This Friday I’m wondering what tips and techniques you think work well for managing lots of instances. Any tricks you’ve used? Any third party products that are very handy? Share you knowledge this Friday.

    Steve Jones

  • Powershell and Performance Monitoring

    When I was doing performance monitoring of servers, I typically struggled with a good way to get the data. In years past it was cumbersome to keep track of server information and rarely was it done well. So often I found that many problems were the result of simple mis-configuration settings, or a lack of patches. Once a DBA or sysadmin can’t remember everything about every instance, it invites chaos.

    Allen White is showing how to better track a “server inventory” to learn all about the setup, configuration, and performance of SQL Server using Powershell. It’s easy to find this information in an ad hoc manner, but keeping track of it and catching exceptions requires something more formal. Powershell is a great way to do that in a repeatable way.

    Based on an article at Simple Talk, Allen shows how you can put your servers in an XML file and then read that in from Powershell and use it to drive a series of queries against a particular instance and machine.

    Most of this session gives you a good look at a script that will gather various information, including performance data, from your instances.  One of the more practical sessions that can really help you understand a large Powershell script in detail.

    However Powershell takes some practice. It’s a set of fairly concepts that are designed to be put together, but you need to take a little time to understand how the scripts work.

    If you get the chance to see Allen speak, it’s worth it. He does a good job of walking through code and helping you understand how it works.