Voice of the DBA

Author: way0utwest

  • The Age of Software

    Do you use old software?

    Does the age of software matter? It’s an interesting question and one which recently came to my attention after a blog post by Dan Jones. Dan works on the SQL Server team and posted a note that the SQL 11 (Denali) tools would only be supported on certain operating systems, of which Windows XP was not one. In the comments on his post it seemed that there was a split in how this decision was viewed by readers, with a number of people saying that a ten year old OS didn’t need to be supported and should be upgraded.

    However, in my mind, the age of the OS isn’t an issue. If the OS still works for you, there’s no problem running it. My wife’s company has the majority of their users on XP, and she just received a new laptop with Windows 7 just a few weeks ago. There wasn’t any fundamental reason for the change, as all their business applications work fine with Windows XP, but as new hardware is purchased, there often are not XP drivers, so employees are slowly moving. That seems to be the case with many companies, who find XP to be a very solid OS for most users.

    However for SQL Server, I agree with the decision. The test matrix for SQL Server tools is likely huge, and they are supporting two desktop operating systems (Vista and Windows 7) as well Windows Server 2008 and Server 2008 R2. I would rather that the resources working on adding features, and fixing bugs in SQL Server, not be diverted to working on XP or Windows Server 2003 as those are too old. Not old in age, but old in versioning, being more than a version behind the current operating systems.

    I know there are a lot of people that are not happy with this, and I can understand their reasoning, but I just don’t think that the number of companies that want to go to SQL 11, and are unwilling to allow developers and DBAs to upgrade to Windows 7 is large. Personally I’d rather have more resources devoted to more exhaustive testing on the current set of operating systems and fixing any bugs that are found.

    Steve Jones

    The Voice of the DBA Podcasts

  • More Regulation Coming?

    Is more regulation coming for data handling and storage?

    Recently Citibank had hackers access a large number of credit card numbers and account details for their customers. It was all over the news, but there was a great piece in the New York Times (registration required) about how this seems to be a nagging problem. We’ve had data breaches before, and banks have had security issues, but they don’t seem to be learning. A number of government officials in the article are quoted as seeking new regulations for data security.

    As data professionals, we are often charged with ensuring data is safe, yet accessible to authorized individuals. That responsibility can be hard to manage, especially when our management often doesn’t want to increase our budgets, or accept more restrictive ways of authenticating users or even slower access. I certainly don’t envy the IT folks working at banks and healthcare companies, and am not sure I’d even want to take a job in those industries in the future.

    I don’t think that more regulation is necessarily the answer, at least not direct regulation. Technology changes so quickly, systems are implemented in diverse ways, and direct regulation often leads to rules that exist to help some vendor profit, not provide better security.

    Whether we get new regulation or not, I think that most of us need to learn more about what security methods work well in our systems, the implications of picking a specific type of encryption, and in which ways we can be audit our systems to detect issues. In the days of limited budgets and apathy from management, a little education is the best way to improve the security of your systems.

    Steve Jones

    The Voice of the DBA Podcasts

  • Data Compression in SQL Server

    Do you know much about data compression? It seems that quite a few people I meet don’t really understand how it works, or what the benefits are. I heard about compression first hand from Sunil Agarwal a few years back at TechEd. I visited one of his sessions and then he was gracious enough to spend about 15-20 minutes with me afterwards answering more questions about additional details. At the time, I was just another schmuck in the audience, but Sunil was very pleasant and patient with me.

    I was lucky that day,but Sunil has a video where he explains some of the ways in which compression works for Microsoft customers.

    http://channel9.msdn.com/posts/SQL-Server-Data-Compression/player?w=512&h=288

    The summary is that data compression works in a few ways. One is, obviously, to save space. The second, however, is that fewer I/Os’s are required to move data onor off a disk, which can result in a performance increase. The SQL Server team tried to strike a balance in how they implemented compression to allow for both of these benefits. The tradeoff is that CPU is needed to compress or decompress data, so while more compression is possible, it can negatively impact performance.

    Compression is a cool feature, and consists of a few parts. You can read more about each of them if you are interested:

    The DBA has complete control over what is compressed, but it is also some work to analyze the space savings and then make a determination about whether or not it is worth you compressing your table. If the space savings is too small, you might end up negating any storage gains with additional CPU costs.

    How Do I Decide?

    There is a stored procedure (sp_estimate_data_compression_savings) that can be used on tables or indexes to help you determine what the savings are. Numerous scripts are available, like this one from Paul Neilsen – Whole Database – Data Compression Procs.

    There’s also one easy decision. Only Enterprise and Data Center editions of SQL Server support this feature, so if you aren’t running those, you don’t have to worry about this.

    Alternatives

    There are a couple other alternatives to implementing data compression.  If you want to get compression savings in other editions of SQL Server, my company, Red Gate Software, makes a product called SQL Storage Compress, which implements compression at the file level.

    This is based on Hyperbac technology and is completely transparent to SQL Server. We are working on some extensive case studies now and this is part of the SQL Server I/O Reliability Program, but this can help performance on your system by reducing I/O.

    If space is an issue in test, customer service, and development environments, you can use Virtual Restore to mount a backup file as a database that you can read, or write to. The writes occur in a sparse file, so your backup file is untouched. However you get a quicker “restore” that is transparent to SQL Server.

    As I mentioned, I work for Red Gate software, so take these as mentions of products my company builds and sells. I’ve heard great things about them, and use them in a few places for work at SQLServerCentral. I’d recommend you try them and see if they are a fit in your environment and have a good price/value ratio for your company.

  • Excel in the Clouds

    Excel Forever!

    I think that no matter what happens with Windows, SQL Server, and many other Microsoft products, Excel will be with us forever. It seems as though this quote is true: “Excel is a leading tool for data analysis today” (From Excel to the Cloud). Almost everyone that works with data uses Excel, including business users, and even Powerpivot didn’t bother to try and build a better interface. It’s built on Excel as an add-in.

    Microsoft Research has built a new way for Excel to interact with data sets in the cloud, with an add-in that brings various analysis algorithms to data using cloud computing. It’s built more for research and development, and not necessarily business, but it’s an interesting idea. I know that there are often scale issues with analysis systems, and computing resources can be expensive to maintain full time.

    The cloud , however, brings a more flexible way to scale up computing resources for specific problems. If you are involved in the analysis of large amount of data, this could be a great way to provision resources for people that need to analyze very large amounts of data. I don’t know how the economics would work for large transfers of data, but the scalability of buying CPU power only when you need it can be very attractive.

    The interface choice of Excel is another concession that it’s just the best way to look at data. It also is very familiar to most computer users, and removes the need to construct any type of application for users. I know that most of the time that I want to analyze lots of data, I drop it into Excel because it’s easier to manipulate and play what-if games; it’s certainly easier than using T-SQL aggregates.

    It also means that Excel is likely going to be with us forever. Between Windows and OSX, it’s got to be one of the most used applications in the world.

    Steve Jones

    The Voice of the DBA Podcasts