Voice of the DBA

Tag: sql server

  • Disk Partition Alignment–MCM Prep

    Does disk partition alignment matter to SQL Server? Without a doubt. When new disk partitions are created, there could be a reserved set of sectors that could differ across disks because of the way that the hardware interacts. In the white paper, Disk Partition Alignment Best Practices for SQL Server, there is an image that helps to explain this:

    Starting with Windows 2008, the disk partitions are automatically aligned to help improve performance. In a decade, it is unlikely that this will be a problem for most systems as most of the installed systems will be running Windows 2008 or later, at least for SQL Server.

    However now there are still lots of Windows 2003 and Windows 2000 systems out there at this time. For those systems, they could be experiencing a degradation of up to 30% according to the testing done by Microsoft. Which means that you can get a quick performance improvement in your systems, if they are disk I/O bound, if you can realign partitions.

    How can you do this? The hard part is that you must move everything off the partition (all data), delete, and then recreate the partition and restore data. That can be a time consuming exercise, but it is really a time effort. It doesn’t cost anything if you have extra disk to hold your data and can handle the downtime. In the white paper, there is a section that explains how to align your partitions in Windows 2000 and Windows 2003 using diskpar.exe and diskpart.exe, respectively.

    Note that this is mentioned in another white paper on SQL Server Best Practices. This gives you a number of I/O related pre-deployment best practices that you ought to consider before installing SQL Server on your systems. If you are building a system of any importance, this is a great article to understand, and apply many of these practices before SQL Server is installed.

    This can cause a delay in the deployment of a new server, but performing these tests and establishing a baseline cannot be done later, and discovering potential problems early can help you to build a better performing server from day one. Finding these problems later will ultimately result in way more embarrassment and hassle than implementing a short delay before deployment.

    If you have a group of people responsible for installing Windows and possibly SQL Server as part of your build process, have them review the article, or even give them a checklist that will help them to incorporate this testing and benchmarking into their routine.

  • You Need Two SLAs for Disaster Recovery

    Continuing on with my MCM prep, I was listening to the High Availability/DR prep module today and I was once again surprised by something. Typically I have heard all kinds of talk for SLAs, usually in terms of network traffic. For databases, I have had SLA conversations that were for downtime that usually go like this.

    Me: How much uptime do we need?

    Manager: 100%

    Me: We can’t really do that in a cost effective manner.

    Manager: Why not, the telephone companies are always up?

    Me: Well, even the telcos measure their uptime in terms of 9s?

    Manager: (blank look)

    Me: They talk about 99% reliability, 99.9%, 99.2%, each of those being a “9” of availability. The high water mark seems to be companies aiming for five nines or 99.999%.

    Usually at this point I need to write this down so they can understand why five nines are 99.999 and not 99.99999

    Manager: Let’s go for five nines.

    Me: That’s only 5 minutes of downtime a year. We can’t apply patches in 5 minutes. A better number is usually 99.9 for us, which means across the entire year we get a 8 hours of downtime. That’s a good number to aim for across a year.

    Manager: We can’t be down for 8 hours!

    Me: (blank look)

    At this point I usually give up and go in search of someone that will better understand things.

    However an SLA for downtime/uptime isn’t enough for SQL Server. You also have to think in terms of data loss. If we lose a server, what about transactions in flight? What about things not transferred to the mirror server or log shipped server? What about losing disks and no tail of the log backup?

    An SLA for data loss is important as well. And like the conversation above, your business people will say zero data loss. Quiz them to find out what can be recovered and to what extent lost data costs the company. The compute the cost of your various HA solutions to decide how to handle things.

  • Isolation Levels–MCM Prep

    I was watching the Isolation Levels video for my MCM prep and learned something about the isolation levels. I knew there were four levels, but I hadn’t realized that SQL Server used the default of level 1, out of the 4 ANSI levels. The levels are:

    • 0 – Read Uncommitted
    • 1 – Read Committed
    • 2 – Repeatable Read
    • 3 – Serializable

    You can read more about them here, but what I learned was that these levels are actually organized in a logical manner. If you look at it this way, the levels can also be described as:

    • 0 – Dirty reads allowed, so data that has been changed in a transaction, but not committed could be included in a query. This means that you could return results in a query that don’t exist. The transaction could roll back.
    • 1 – Potential phantom reads or non repeatable read. Queries only read committed data, but since they don’t lock the whole slice of data, a multi-statement body of work could potentially get new rows between statements or return different results.
    • 2 – Repeatable reads guaranteed, but potentially phantom rows could appear.
    • 3 – No repeatable reads or phantoms possible.

    The flip side of the greater data integrity is that more locks are needed, and held longer as you move up the levels. That can impact concurrency.

    As with everything in databases, there is a tradeoff.

    Note that SQL Server allows you to actually get around some of these issues with row level versioning. Of course, this isn’t free. It comes at the expense of space in tempdb.

  • ETL Security Holes

    The addition of DTS to SQL Server meant that more and more of us could grab data from a variety of sources, easily, and move it to SQL Server systems. SSIS enhanced our capabilities and made it fairly simple for non-programmers to grab web service data, and other formats, and quickly decompose them into relational formats. This made it more likely that we would build data warehouses or reporting systems, and easily keep them up to date with data from our OLTP systems.

    However the ease of using ETL (extraction, transformation and loading) processes to move data did not include the moving the same security controls and restrictions to these new systems. That can create a big security holes, especially when you have secondary systems used for decision support that might not be contained in the production network and not audited to the same level as other production systems.

    Whenever you move data around, there ought to be some set of guidelines for the security protocols required. That can be hard to track when you are pulling data from a secure system and your connections look like every other connection. One of the dangers that I suspect we will run into with Powerpivot is that secure data will be downloaded to Excel worksheets on insecure systems, and all of the controls that we have in place for protecting sensitive information are ignored when we pull data for analysis elsewhere.

    Our data security has gotten better, but we have a long way to go. If you are working with ETL processes, and especially with end-users, make sure that some sort of security policy is in place to help protect this data from being accidently disclosed. It’s not a great suggestion, but until we have better security tools to allow policies to follow the data, that is all we can do.

    Steve Jones

    (originally published at http://www.sqlservercentral.com/articles/Editorial/72157/)

    Podcasts