Voice of the DBA

Tag: sql server

  • Learn to Use Filegroups

    This editorial was originally published on May 12, 2011. It is being re-run as Steve is away at SQL Relay today.

    In SQL Server, filegroups are a management technique that I don’t see many people using. It’s amazing how many people ask questions about filegroups on the discussion forums, often unsure of how they fit into a well architected SQL Server. I have tended to use filegroups mostly as a space management technique, when I need to add more disks to my server, but they can be used in many more places.

    We continue to grow our data sizes all the time. While many databases are still measured in the single digits of gigabytes (or smaller), it is fairly common to find many database servers with over a terabyte of disk space. Our disks grow larger and larger, but it seems that data grows faster than disks, requiring larger storage subsystems all the time.

    While our storage grows larger, the tolerance for delays shrinks and demands for better performance increase. That means that data professionals need to be more cognizant of not only how their code is written, but also how they design storage. Tiering storage is one idea that I think has merit, but one that requires some planning.

    In SQL Server, we can’t split a table across filegroups. Or can we? We can partition a table (Enterprise Edition and higher), which can allow us to manage performance and storage appropriately. There is also the recommended practice of only having system objects in the primary partition and using separate filegroups for user data. That allows you to bring a partial database online, again, in Enterprise Edition only, while you restore different filegroups.

    This isn’t the first thing I would recommend you learn about SQL Server, but as you advance your knowledge, you should better understand when and how filegroups can help you. You will use them at some point and being comfortable with a filegroup restore is one of the skills that separates the accidental DBA from the data professional.

    Steve Jones

  • Querying Yesterday

    One of the new features coming in SQL Server 2016 is the temporal table. It’s an interesting concept, one that many businesses have wanted for years. If you’re not sure what this is, we’ve got a collection of resources on Learning about Temporal Tables (and other features). Check them out and we’ll keep adding content as we find it.

    Temporal tables give us some amazing capabilities, but at a cost. As with anything in computers, there is a cost for capability. In this case, we can look back at the view of our data as of a particular point in time. In many ways, this means that we don’t need to bolt on, or query into, auditing data.

    However there are other costs. As with any auditing system, we potentially have substantial data that we need to manage somehow. Certainly we need to choose which tables to track. Even if we don’t have to build a process, we will have to deal with the cost of storage and provisioning, as well as determining the retention periods. We also need to really depend on the system times for our various instances to be in sync.

    I think that this is a needed, and very useful feature. I’m sure there will be bugs to patch, as well as enhancements to be built. We’ll find those over time, but I think that this is one of those features that we’ll come to see as essential in a decade and wonder how we ever built systems without it.

    Steve Jones

    The Voice of the DBA Podcast

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

  • The Work of the Ancients

    I was reading a post from someone recently where they noted that they didn’t worry to much about the architecture of the system since it wouldn’t likely last very long. The poster had a comment that many systems are replaced inside of a few years.

    In my experience, that’s not usually the case. In fact, while I don’t expect many applications I’ve worked on to last for 25 years, I suspect many of them will exist for ten years or more, especially if they are receiving regular development resources. With that in mind, I wanted to see how your databases are faring these days. I suspect a database might last longer than a particular application, as it seems most organizations are loathe to ever let data go.

    What’s the age of your oldest, regularly used database?

    I’m looking for an age in years. If the answer is less than one, I’m not sure I’d call that old at all. I am sure many of your  systems are older, and might have changed, but let us know the year when the system went into production.

    I can tell you the SQLServerCentral systems are old in some ways, not so old in others. We’ve grown from one database to three over the years. The oldest database is circa 2003. Some of the data from that one was migrated to other databases around 2007. We’ve got data in the system since 2001, but we’ve certainly changed structures and storage over the years.

    I’d guess that most of you that are working in companies that are older than ten years will have a database that’s at least that old. However let us know this week, and if you have any interesting notes, feel free to share them.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Data Masking for Convenience

    I was at Microsoft in Redmond recently and heard an interesting comment from a SQL Server developer. I was debating the data masking feature with a friend, and we were torn on the value of this for various situations we’d each encountered in the past. There are some restrictions, and it doesn’t seem that data masking is really offering a lot of security.

    The Microsoft developer, however, noted that this isn’t really a high security feature. It’s a developer feature. The design of data masking is to prevent that same code from being rewritten over and over by application developers. The use case is really to help with systems that might read some data, like those that print off part of an account number, ID number, credit card number, etc.

    If you read up on the restrictions, this makes sense. If you are just trying to make development more convenient, the feature makes sense. I hadn’t thought about that use case, but the more I consider this, the more I’m sure that data masking does remove a bunch of code that developers might be re-implementing themselves, perhaps with highly variable levels of quality. It also removes the chance that application developers will accidentally pull sensitive data to a client and (poorly) implement mask replacement there.

    I think this feature is being mis-marketed a bit, really to increase sales to executives and management. I’m sure there isn’t anything we can do about that, but I’d love to see technical documents and information about this for developers and DBAs. Give us a more realistic use case and give us better guidance. I think if we got that for many features, there might be more positive responses and great interest from technical professionals to the changes in the SQL Server platform.

    Steve Jones

    The Voice of the DBA Podcast

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