Voice of the DBA

Category: Editorial

  • A Data Hub

    Hub and spokes
    The idea of a data hub seems very interesting to me as a DBA.

    One great promise of data warehousing over the years has been a “single view of the truth” for companies. By moving, cleaning, transforming, and standardizing data from other systems, we can put together a single location for the most accurate data a company can have. I suspect there are some organizations that have had success here, but many are struggling. The idea behind some of the newer SSIS tasks, Master Data Services (MDS), and Data Quality Services (DQS) in SQL Server is that we have some functions in the SQL Server platform to make this easier to achieve. Or perhaps to ensure we do so at a high level of success.

    However just moving data to a central location isn’t necessarily the only way to deal with the challenges of data. Perhaps there’s a better way, a more distributed way that provides a framework for centralization, but distributes the ownership and knowledge of the data to others. Buck Woody recently wrote about data hubs as a project and idea that Microsoft is making available. It’s a place to publish data for your organization, but groups inside your company, but available for others to use.

    Many of us have experienced the issues of each developer or each department attempting to manage their own sources and lookup data. Even well known data such as postal codes can change and easily become stale quickly. How many developers are willing to write import routines to update this data for something such as postal codes, let alone internal data such as customer names.

    Ideally I think we should pull lots of our initial data from corporate sources, and establish central data hubs for all new types of information we gather and support. From there, a variety of import and update routines could be written and shared by all applications people use. It wouldn’t provide perfection in terms of data quality and freshness, but it would be better than allowing each individual developer to make their own decisions.

    Steve Jones

    The Voice of the DBA Podcasts

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

  • The Copy Cat Poll

    copy cat shirt
    How many copies of data do you need in our organization?

    One of the interesting facts I saw a few years ago talked about storage in enterprise environments. There was research that showed many enterprise applications had 6 or 7 copies of their large databases inside the organization. In addition to the production copy, there were many other copies in use, resulting in an explosion of growth. That wasn’t surprising, and it was one of the drivers for implementing compression in many databases.

    While the cost of storage is constantly coming down, it’s still expensive for enterprise class hardware, especially in a large SAN device. Today I wanted to ask those of you that work on real world systems to make a quick count of your own system, and let us know. I can’t decide if 6 copies of a production database is high, or low.

    How many copies, on average, of your production databases are in your company?

    I suppose you could count backups as a copy, since it’s disk space usage and you have to pay for it. If you count backups, let us know, but I’m thinking just about the test systems, development systems, HA or DR systems that might receive copies of the data. Some of those secondary systems might be in use for other purposes, such as reporting from readable secondaries in an AlwaysOn scenario. Whether they are or not, they are still copies of your database.

    I used to think that four or five copies would be a lot, but with the advances in technology that allow different DR options, and the cheap local storage available on today’s desktops and laptops, I wonder if seven or eight copies might be more accurate.

    Take a count today; you might surprise yourself with the results.

    Steve Jones

    If you are looking to reduce the cost of storing all those copies of your data, take a look at SQL Storage Compress, Virtual Restore, or SQL Backup Pro from Red Gate Software.

    The Voice of the DBA Podcasts

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

  • No Limits

    Google Compute Engine
    The Google Compute engine isn’t unlimited, but it’s got 770,000 cores and growing.

    We’ve had Amazon Web Services (AWS) available for some time, and used for some interesting projects that wouldn’t be possible if groups had to purchase their own equipment. From password cracking to cancer research, there are some amazing possibilities. Windows Azure works as well, and there are customers such as banks, using the cloud to perform complex analysis of data. Not to be left out, Google announced recently they were provide their Google Compute Engine to anyone, without any limits on scale. You have to pay for it, but if you want 770,000 cores to solve a problem, they’ll provide them if you can pay for the resources. It’s US$2m/day, but it’s there.

    I don’t know what that means for us as data professionals, but I suspect there will be any number of companies that will consider using these types of resources to work on complex analysis of problems. Running a simulation might be something that costs a few hundred dollars a day, using resources for minutes at a time. Looking over some of the different projects people have used cloud computing engines for, it seems that many businesses might find this to be a cost effective way to perform data mining or BI type workloads.

    If you can get the data to the engine. I still wonder how challenging this is, apart from all the security concerns. Just moving that much data around, refreshing it, removing the stale data, could be very complex. It concerns me that as more people attempt this type of work, we will have more and more poor decisions made because of data quality, age, or accuracy. After all, garbage in, garbage out has been a tenet of data professionals for decades.

    If you’re using cloud resources for computation, I’d love to know about it. I know many of the data professionals out there have reservations, and that’s healthy. It shouldn’t blind you to the possibilities that this is a most cost effective way of actually getting analysis and answers for your users.

    Steve Jones

    The Voice of the DBA Podcasts

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

  • The DBA Database

    database
    Having a database to store DBA type data can be very helpful for a busy administrator.

    Do you have a DBA database on all your instances? I’ve always kept a small database on all instances, usually standardized with a set of tables and procedures that I used to monitor and track activity on the instance. By keeping this fairly standard, I could script and deploy it during all new installs as well as easily aggregate information from all instances on a central server, usually in a slightly larger version of my DBA database.

    It’s nice to see more and more DBAs using this same technique in their environments. Over the last few years I’ve seen lots of articles and blog posts that recommend building a DBA database and populating it with DBA-stuff. That DBA-stuff can be anything from tracking backup sizes, to storing performance metrics, to keeping trace data. I’ve seen some neat implementations with Service Broker that use the DBA database as a repository for a queue to which they can send messages. Based on those message, they can have the instance  perform some action.

    There are any number of standards or corporate reasons not to include extra databases, but none of them really make sense. The DBA database, and any administrative tasks that use this database essentially act as a proxy for the DBA. Almost every piece of data stored in this database is data that the DBA would query or use on a regular basis. Keeping it inside a database set aside for this purpose allows the DBA to act more efficiently.

    If you haven’t built a DBA database, I’d encourage you to do so on all your instances. Secure it so only sysadmins can access it, but use it to capture and store information about the ongoing health of your SQL Server.

    Steve Jones

    The Voice of the DBA Podcasts

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