Voice of the DBA

Category: Editorial

  • Use Your Tools

    I used this tool for the first time and found it very handy.

    Someone asked me recently if I’ve ever exported a table using SQL Packager, a tool from my employer, Red Gate Software. I hadn’t, and in fact, hadn’t ever even opened the tool. So I started it up and exported a table. I was surprised how easy it was, and I wrote up a short blog on it.

    That reminded me of a common issue that many of us have: we get into a rut. It’s easy to stick to doing things the “old way” we’ve learned, and not updating our skills to take advantage of newer features.  It’s also easy to get used to going through a process one way and never trying, or experimenting with different techniques or applications of the tools.

    I have been guilty of this, and am trying to rectify it, working with new features, and trying out new SQL Server 2008 R2 or SQL Server 2012 T-SQL changes where I can. I noticed Grant Fritchey recently talking about Extended Events and the advantages of using them with deadlocks, and also using them for performance tuning. Learning Extended Events is an area I need to spend some time with in the future, and I’d recommend most other SQL Server professionals do the same.

    However it’s not just the changes in SQL Server. There are changes in tools from vendors that can help us do our jobs better, or even tools we don’t know we have. I have spoken with lots of Red Gate customers that weren’t even aware of the capabilities of the tools on their machines. In some cases they weren’t even aware they had the tools because they were purchased in a bundle. Not every tool is useful, and not always appropriate, but it’s worth a little time investment to learn how to use the tools and gain some awareness of their capabilities.

    We publish some information on the Red Gate tools at SQLServerCentral, and I constantly find great blogs written by people that actually use the tools in their daily work. I suspect that’s the case for other tools as well. Take some time and read about the tools you own, and even the ones you don’t. You might find one that will actually save you time, make your job easier, and provide a nice ROI for your company.

    Steve Jones

    The Voice of the DBA Podcasts

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

  • Anonymous Research

    Anonymity is important

    It’s no secret that anonomizing data doesn’t always work well. We have heard about this when Netflix released their data for people to build algorithms with. Some people were identified based on the data released being correlated with other data the people had entered on the Internet themselves. I know that there are dangers with sharing too much information on the Internet, but people are going to share and there will only be more services in the future for us to use that require data.

    I ran across a post recently from Microsoft researchers that showed similar issues with other anonymous data sets that contain IP information. A number of logs containing traffic from Bing and Hotmail were analyzed with the intention of identifying particular hosts. Even when the data was anonymized, it was possible to identify hosts with a high degree of accuracy.

    You might not think this is a big deal, but as more data is gathered by companies and used for secondary purposes, like analysis, it becomes more likely to be inappropriately released. Is a log on a server more secure, or a copies of multiple logs on analysts’ laptops? I’d think the former, or at least I’d hope the former. If that’s true, then we should really be anonymizing data on a regular basis once it leaves hardened server machines.

    That means we ought to have better algorithms for preventing any identifying information from being retrieved. I would hope that this is an area where research can help, and one that receives a lot of attention in the near future.

    Steve Jones

    The Voice of the DBA Podcasts

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

  • Encryption in Production

    WWII era encryption with the Enigma Machine

    The encryption capabilities of SQL Server have been growing in each new version. We have gone from password protected backups to Transparent Data Encryption (TDE), we have moved form PWDENCRYPT() to symmetric, asymmetric keys, and certificates. There are quite a few options available in the current SQL Server platform. However I don’t often see people writing or talking about their use of security.

    It might be the nature of sensitive data means keeping the use of encryption quiet, but that shouldn’t matter. If you are keeping your keys safe, the algorithms and deployments you use should not matter. This Friday I wanted to ask a question about your use of encryption in real world systems.

    If you have sensitive information (identity/financial/medical) data in a database, have you deployed encryption?

    I would like to know if you’ve actually implemented native or third party encryption and if so, in what percentage of the databases that have sensitive information is encrpytion deployed. Do you strip out data before you store it, perhaps only storing something like the last four digits of a credit card in plain text? How diligent do you think your employer is being with regards to encryption?

    If you work with encryption, are you confident that you can recover your systems in a disaster? That’s one question I’d want to be sure I could answer before I deployed any keys in my database.

    Steve Jones

    The Voice of the DBA Podcasts

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

  • API Changes

    Not the best API documentation

    There’s an interesting set of predictions about the possible new APIs (application programming interfaces) that we may see in the future. It’s a look forward into 2012, and a guess that setting standard ways in which we can build systems will become more important in the future. There were two items that I found of particular interest to the data professional.

    The first was the idea that we might have more data APIs in the future, designed to make the exchange of data easier. The people that work with data imports and exports have probably been hoping for his to become more prevalent, and it has gotten better. However I do think that data is becoming the most important part of the technology world. It isn’t the software, or the platform, or the device, but the data that’s available that is the most valuable part of a technology system.

    It isn’t likely that any company can create and manage all this data themselves, especially in a rapidly evolving world where so much of it changes from day to day. A successful business will need to acquire data from other sources and easily integrate the information into its systems,, and an API will make this process smoother. I expect that standards like OData will become something that we work with more in the future.

    The second interesting part of the article talked about the possibility of us seeing more Enterprise APIs, developed and built by individual customers so that their systems can be more easily integrated with others. That would seem to be counterintuitive, but with so much knowledge and talent available outside your company from others that want to build apps to interact with you, I expect this may catch on, at least for those companies that see a demand from partners and customers. One very valuable effect of building an API? You get more data on how your customers use your API, allowing you to better understand how your company might change to enhance its performance.

    Building an API means setting a standard, one you have to support and maintain for years. It can evolve, but the core elements of it will live for a long time, so it helps to gain experience here. Think about providing APIs as you develop software inside yout company, rather than just assuming you’ll build all the functionality needed.

    Steve Jones

    The Voice of the DBA Podcasts

    We have one version of the podcast back and are working to get the others back soon.