Voice of the DBA

Author: way0utwest

  • 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

  • Evaluating Sessions You Attend

    I have seen more than a few posts about feedback that speakers get from sessions in the last couple months. Last week Buck Woody made an attempt to build a better form that could be used for feedback, which I like, but I think it’s a little too long for most events. If you attend 3-6 sessions in a day, filling out forums like this could be a chore.

    Attendees Should Give Feedback

    I know your time is valuable, but please give some feedback. I agree with Buck that the numeric scores are useless, but I know organizers wants something. If you liked the session, give it a 10, if it was OK, a 5, and if it didn’t work for some reason, drop a 1 in there and leave it at that. Let’s just make it a simpler system because no one knows what a 6 is vs a 7.

    But Be Fair.

    The way you perceive a session is not the way everyone else does. If you think the speaker was technically wrong, tell them. If they didn’t cover what was listed in the abstract, tell them. If they wandered around the topic without making sense, tell them.

    But don’t mark up a form with bad scores because you didn’t like their music, or their shirt, or don’t agree with the approach or thought something was too (or not enough) technical. More often than not I find people are so subjective in this area, and the inappropriately send information to other attendees, or impact the speaker’s ability to present in the future.

    If you have specific complaints, speakers would appreciate hearing them. You don’t have to rate everything, but give one specific piece of criticism that you’d want changed. One thing Buck does well is give you a chance to impact a presentation, and you ought to drop one written comment (not a score) on each evaluation you turn in.

    If you don’t have something bad to say, say something.

    I know that many evaluations are used for raffles, so please feel free to fill them out quickly, but I would appreciate some feedback. I do like seeing all 4s or 5s on my scores, but they often don’t help me improve the presentation. Give me one thought about what I could do better.

    For Speakers

    If you’re a speaker, you have some idea of what goes into building a presentation, writing a description, developing flow in your presentation, and pacing. Therefore, you ought to be able to evaluate a few things for other speakers.

    You ought to be able to tell them if they hit their mark in terms of what they said they’d deliver. I think speakers often have a better perspective on evaluating this. Not that non-speaking attendees’ opinions don’t count, but they sometimes aren’t very well thought out

    If you know the speaker, drop them an email, or take a (private) moment to give them an assessment of what you thought. I routinely let others know when I think they could improve something, or have approached a session in a way that turned me (or others) off.

    If you don’t know the speaker, introduce yourself, and say “I have some constructive feedback on your session. You don’t have to do this, but I thought …”  and then explain your point of view. It’s not rude, or assuming, and believe me it’s appreciated.

    Just be aware that it’s an opinion and don’t get upset if the speaker doesn’t make the change you mentioned. You’re still one of many, and while it’s nice to hear your opinion, it’s not a marching order.