Voice of the DBA

Tag: databases

  • Treat the Database like Code

    Source Control can drive people crazy

    In one of Grant Fritchey’s presentations at SQL Server Connections recently, he talked about treating the databaese like code, and storing the changes you make, the DDL for the objects, and more, in a version control system of some sort. Grant had a number of points to make about why this is good, not the least of which is that it brings the DBAs closer to the developers.

    It was a great presentation and it made perfect sense to me. I’ve always tried to store my DDL code in some sort of VCS, checking it and out as needed. I found it helpful for keeping track of which version of an object was released with which deployment. This also helped me to organize the changes which I was going to release, branching them away from the full list of changes stored in source control.

    Databases don’t inherently lend themselves to source control, with the various levels of state that must be stored, the strict ordering of changes, and more, but by imposing a little discipline, you can include the code and keep track of everything important for deploying changes. It’s not easy however, especially since the process of building a current version of your database from VCS would not be the latest change to each object. Objects are built with CREATE statements, but changes over time are made with ALTER statements. A little creativity will help you here, as will any number of third party tools that can keep a current set of CREATE statements handy.

    If you’re not storing your DDL in a VCS, I’d urge you to start. You might not need the features often, but when you do, having versions available can be career-saving. There are a number of free VCS systems available as well, so there’s no monetary reason why you can’t start tracking your code tomorrow.

    Steve Jones

    SQL Source Control from Red Gate Software can make version control possible from within SSMS. Download your trial version today.

    The Voice of the DBA Podcasts

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

  • Too Many Bricks, Too Much Data

    Bricks
    Too many bricks are hard to keep straight

    At one point in my career I was working on a new application and we were debating about storing certain metrics related to the application usage and customer’s behavior. My boss asked for my opinion and I said that more data was better than less and we could always delete the data if we found it was not being used.

    However we rarely delete data. We seem loathe to remove old data, even when we find it’s slowing down our systems. Worse still, there aren’t great methods for even stripping out, and preserving older data other than custom work in each system. I ran across a piece that talks about the tremendous amount of data that we are constantly acquiring, a deluge that overwhelms us in so many scientific areas. There are some endeavors collecting so much data that they must restore to storing data in networked systems, making the data sets available only through software that can combine the information from various databases.

    In the corporate world of data we usually don’t deal with such large amounts of data, but we are often dealing with hardware that constrains our ability to work effectively with the data we have. We find that our disparate systems are spread across so many places that it can become hard to aggregate the bits together and extract information.

    I do think that we will start to feel the stresses of dealing with so much data and finding the meaningful information from it. The people that learn to do this well, and filter their bits effectively will become very valuable to their companies in the future.

    Steve Jones

    The Voice of the DBA Podcasts

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

  • Data Growth

    Data Growth
    Data is growing at many organizations at an unbelievable pace.

    I attended the Special Ops Tour for SQL Server 2012 in Denver recently. It was an event that was showcasing some of the changes in SQL Server, with presentations and networking for the local SQL Server pros. It was fun to get out for a half day and see some friends from the area and extra exciting as the RTM was announced the day before our event.

    During the opening talk, there was a slide with a very interesting statistic. It talks about the last few years had seen 40% data growth for many organizations, but IT budgets had only grown by about 5%. Those two numbers don’t really relate well to each other since the cost of IT operations can dwarf the cost of storage, but for how long? At some point 40% growth will catch up and become a significant cost in your organization.

    The 40% growth does show that data professionals become increasingly important to companies. As “Big Data” becomes something that more companies need to deal with, the skills that we have as data professionals will become more valuable. We have to learn to separating out the information from the noise, enforce data quality, and learn to manipulate large volumes of data more efficiently. SQL Server is growing to help us with enhancements to SSIS, DQS tools, and even Hadoop processing capabilities, but we need to learn how to better use these tools. Storage is also an issue, and so many products and tools are available to help here. We have lots of compression tools, some de-duplication tools for backups, and more, but we need to learn how these tools fit in our environments, and what the ROI is for our organization.

    Budgets are not going to keep pace with the requirements we face. We need to learn to work smarter, more efficiently, and more effectively to get the most out of the limited funds available.

    Steve Jones

    The Voice of the DBA Podcasts

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

  • The Consistency Debate

    How consistent do your systems really need to be?

    One of the questions I constantly see asked about on SQLServerCentral is how can you scale out the database. How can you easily keep multiple copes if your data in sync with one another on different servers, and allow clients to read from any of those servers. The Always On features in SQL Server 2012 will allow readable secondaries, something that wasn’t possible in database mirroring, giving you up to date reads of your data, which is in sync with your primary database.

    However is that really necessary for many applications? The largest applications in the world, Google’s search engine, Facebook, and more, are turning to NoSQL databases and storage to handle their loads. I ran across a nice piece on Ars Technica that talks about how these companies handle the large data storage challenges they have. The one very interesting thing in the piece is the way the consistency challenges are talked about. These phrases, “designed with less concern for consistency of data across the system”, “jobs in progress will still hit stale data”, and “(it) is entirely okay with serving up stale data”, would scare most DBAs I know.

    But should they? For some applications, it is important that we maintain consistency across nodes. The classic bank account example requires that a withdrawal and a deposit between accounts are totally committed or rolled back to ensure the proper balances. However that doesn’t mean that every node in the banking system knows your balance. Only those times when a transaction affects the balance, do we need to be sure of the actual value. Deposits are sometimes not reflected immediately on another node, say a remote ATM. Withdrawals are sometimes approved, even with modern checking machines, when there are insufficient funds, resulting in bounced checks.

    In many of our applications, we are told we need consistency, but I think that’s a goal. It’s like 100% uptime, which is rarely met, and almost never funded. Think about a report. A User might run the report, assuming that the data is accurate, and it may be, but 14ms later it might not be because of changes. If the report had been run 14ms earlier, it might have missed other changes. Most people understand that, even if they are asking for consistent behavior of the system.

    With that in mind, should we be looking for more distributed architectures in our applications? I think that replication and service broker, both excellent techniques for scaling out, should become a more regular tool for our applications, and should receive more attention in future versions of SQL Server. We are only acquiring more and more data, and while hardware continues to get more powerful, we are outpacing the developments in storage bandwidth. We should start thinking about how to anticipate future challenges in application load, not reacting to them later.

    Steve Jones

    The Voice of the DBA Podcasts

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