Voice of the DBA

Author: way0utwest

  • Too Much Data

    Big Data is getting lots of attention. Will that mean more resources for managing it.

    Many data professionals these days feel like they are working with too much data. Queries run slow, storage is constantly a concern, and our hardware is outgrown on a regular basis by the sheer amount of data that we need to manage. It’s a good thing that “Big Data” is in the news since it focuses more attention on the issues of data management and growth in our systems.

    Some companies are taking the idea of managing their data more seriously. Boeing and Nike are talked about in this piece about coping with large amounts of data. With the vast amount of growth in the data stores these two companies work with, they had to find ways to ensure that business people could effectively find the information they needed in a sea of data.

    It’s an interesting read and one that you might pass on to your management. The solutions aren’t new and innovative; they’re back to basics ideas. Have a central warehouse, use a data strategy that includes strong models and enforces data quality. Integrate your systems to allow a better view of your entire enterprise. These are the ideas that many data professionals preach, but are rarely found in many companies.

    Sometimes a new perspective helps, and articles like the one above can help you convince managers of the need to raise your data quality and enforce some standards.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • T-SQL Tuesday #36 – What Does Community Mean?

    tsqltuesdayIt’s T-SQL Tuesday time, this time it’s a non-technical topic, or at least not a directly technically related topic. The SQL Corner, from Chris Yates (b | t), is hosting and the topic this month is “What Does Community Mean To You?”

    You can read the rules on Chris’ blog, and if you’re interesting in hosting, contact Adam Machanic (b | t).

    Community

    I run one of the largest SQL Server communities on the Internet. From the very beginning, Andy, Brian, and I were concerned about building a community. Our goal was to create something that reflected who we were, embodied our ideals, and would be the type of place that we would want to visit ourselves.

    In a little over a decade, it has grown to be an amazing place where people help each other out on a daily basis. Whether it’s with articles written that someone will read weeks in the future, or through near real time interactions in the forums, there are thousands of data professionals on a daily basis interacting with each other at SQLServerCentral.

    Community to me is more than SQLServerCentral, however. It’s more than a web site, or a SQL Saturday, or a group of people that perform the same jobs. Community is more than sharing your knowledge, or asking questions of others. Community includes the bonds of caring, of having each of the members matter to the others. It’s not perfect, and we have our share of disagreements, discontent, and even dislike among members.

    In that case, it’s like family. This SQL Server community reminds me of family: not always pretty or peaceful, but always something I am proud of being a member.

  • Create an Asymmetric Key

    SQL Server has asymmetric keys available for encryption. I wouldn’t recommend them for direct encryption of data since they are slow, i.e. they require lots of resources, but they are good for encrypting the (faster) symmetric keys.

    How can you create one? It’s actually easy, using the standard CREATE ASYMMETRIC KEY DDL. 

    create asymmetric key HRProtection
 with algorithm = RSA_1024
 ENCRYPTION BY PASSWORD = 'Use4SomeStr0ngP@ssword%^'
;
GO

    This create a key with the name and using the RSA algorithm at 1024 bits and protects the key with a password. You could chose other key lengths (512, 1024, 2048), but 1024 is the minimum I would use. It has been shown that 512 bit keys can be cracked relatively cheaply and easily on AWS for less than US$100.

    These keys can be created from key files (a key pair set of files if you used something like SN from the Windows SDK. This would require a path to the files and permissions to read them. Just like protecting the password with this file, you should have limited permissions set on any key pair files that exist on your OS. The ideal way to create these keys is with a hardware EKM device, but those are expensive, rarely used, and not foolproof.

    This requires the CREATE ASYMMETRIC KEY permission, or equivalent. Most of the DDL type permissions include this permission.

    There are no GRANT/DENY/REVOKE permission statements with keys. Make sure you use a strong password and protect it. Do not disclose this unnecessarily.

    That’s the basic of the key creation. I’ll look at some other options with these keys in another post.

  • Monitoring Is Essential

    Watch your systems like a hawk. You’ll be glad you did.

    An old phrase used in business is that knowledge is power. When you understand more about a particular situation, you can develop solutions and better understand which ones would both suit your environment better and solve any issues faster.

    As someone that has worked in a variety of production environments managing all different types of platforms, I have found it extremely career-enhancing to have knowledge about the entire environment. Knowing what is happening at any point in time has allowed me to answer pointed questions about the applications. With comprehensive monitoring set up in database servers, I have often found myself in a position to proactively prevent issues, or make preparations for a quick response when I do find problems.

    Over the years I developed a variety of techniques to monitor my own servers, often using third party solutions, enhanced with my own queries. I have learned that each system has its own idiosyncrasies, which often require custom queries to keep an eye on the pulse of the system. I’ve also learned that a regular exception report is the most valuable tool for me as a DBA. I assume most of my applications and scheduled tasks are working well; I just want a list of those things that have broken, or are not performing as expected sent to me every day, using the data from my various monitoring tools.

    I have found that tracking metrics and analyzing the data is a valuable tool in my DBA tool belt. I would go so far as to say that monitoring the systems that you work with is not only a best practice, it’s essential if you want to be an exceptional DBA.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.