Voice of the DBA

Author: way0utwest

  • SQL Server 2008 – CDC Retention

    I had done a little work with CDC last year, experimenting with the way that it handles changes in your database. Someone had asked me the question about the retention period since the default of 3 days was not sufficient for their environment.

    BOL lists the default retention period as three days, and this is based on a cleanup job that goes through and removes data from the tables. However you can alter that with a stored procedure: sys.sp_cdc_change_job.

    There are a number of parameters for this procedure, most dealing with managing the load of the cdc jobs. You can alter the cleanup or capture jobs with this, but for the retention period, you want to change the cleanup job.

    @retention is the parameter that indicates the number of minutes that change rows are going to be retained in the CDC tables. If you pass in a NULL, the old retention period is retained. You can include the number of minutes you’d like, up to 52494800 (100 years). I would recommend you choose something less.

    The other thing to be aware of is that you have various intervals that are set, and also a @threshold, which determines how many rows are deleted on each scan. If you limit the cleanup to something less than continuously operating, then be aware that if @threshold is too low, it might not delete all the rows. If you are changing these values to manage the load of deletions, be sure that you are monitoring how many rows are changing as it might grow over time.

  • MERS

    Imagine you have this idea. You’ll build an electronic database designed to record rapidly changing data for assets that move between companies. You make contacts with the various companies and your system acts as the middleman. You are the central database, reducing the costs these companies have by trying to keep their own systems in sync. But what happens when your database becomes the legal record and it’s not up to date?

    That is what might have happened with the MERS system. It is supposed to record the mortgage titles, linking homeowners with their mortgage holder. Since mortgages are often sold many times during their lifetimes, MERS was supposed to reduce the costs and time involved with each sale. By having MERS maintain title, recording fees and time is reduced as everything is centralized. Theoretically, this is what we want centralized databases to do: make things more efficient and accurate.

    But that’s not always what happens. We make mistakes, we have coding bugs, and a single database could easily have many mistakes in it. In fact, this is why having a single database for a distributed system could be a bad idea. Multiple databases can allow you to compare data, and perhaps track down any discrepancies between systems.

    This is one reason that a data warehouse can be a valuable addition to your company. By taking feeds from multiple systems, and standardizing the information, it’s easier to use. However that ETL step can provide a valuable double check of your data, and a good feedback look can enable you to find data quality issues and correct them.

    I don’t know if the MERS system has data quality issues, but if they are going to be a legal system of record, their data has to be accurate.

    Steve Jones

  • T-SQL Tuesday Topics

    I was asked to host a T-SQL Tuesday, and I accepted.

    Once again, my fingers move a bit faster than my brain. “Sure, be happy to host one. Send me a date” flew out of my fingers before I thought through the implications. I’m happy to help the community, and I think Adam Machanic (Blog|Twitter) had a fantastic idea with the T-SQL Tuesday blog party, and I look forward to them every month.

    But I’m busy, and this is a very busy time of year for the next month, with the hope that most of December will be quiet for me. It’s been a crazy year for me and I’m looking to try and better manage life next year.
    Adam immediately responded to my email with a date, and I have December. I was thinking he’s a busy guy, and my mail might get lost in the shuffle. However it didn’t and I ended up with not only an acceptance, but a date that’s not too far in the future.

    So I need a topic.

    There are any number of topics out there, and after all, T-SQL Tuesday has only been around for a year. However I was somewhat stumped as to what a “good topic” will be. After all, I want this to be a good one.
    The ones I’ve seen so far are:

    I’ve got a few ideas, and some work ahead of me to develop something in the next month around SQL Connections, SQL Saturday #59 and a week of family vacation. If you have any ideas of your own, don’t post them. Save them for your own T-SQL Tuesday and contact Adam to host one.

    I’m not sure what will happen in November, and I’ll be jammed up with a post since I’ll be at Connections next week, so whether it’s the first Tuesday or second, I’ll be hustling a bit to participate.

  • Remote Security

    Telecommuting is taking off. It seems I have been reading that for years, but more and more I run into people that are at least able to telecommute part of the  time for their jobs. There are still lots of people that don’t telecommute  at all, especially in Information Technology, but I do think more and more businesses are becoming open to the idea that some portion of their workforce going about their daily tasks from a remote location.

    And that means security is a bigger issue. I ran into this short piece on two ways to provide security for home workers and found it interesting. It mentions VPNs with network access controls as one solution and remote terminals as the second solution. Both of these can work, but both can also be complex to implement.

    The lines between what is private and what is corporate are blurring often on our machines, just as the line between private time and company time is hard to discern. No matter what your company policy is, chances are that for IT workers, some of their personal life will bleed onto their corporate hardware. That’s even more of an issue when you have people working at home.

    While the infrastructure people will  concern themselves about good network security, I think it’s important that DBAs question the data security as well. Is database access protected? Can the user download and save critical data to their machines? Is any encryption employed? It’s important to ask the questions and be sure that those involved with securing corporate systems are aware of potential data security issues.

    Steve Jones