Voice of the DBA

Author: way0utwest

  • An Impressive SQL Server

    What would impress you?

    Recently I heard about one SQL Server with 1TB of RAM and another one that was handling over 10,000 transactions a second. I read a note about a 1.1PB database, built on SQL Server to hold data from telescopes. That particular data warehouse is producing 1.4TB a day. I know it’s mostly binary data, but that’s a lot of 1s and 0s being stored in SQL Server.

    Over the years I’ve heard regular complaints and concerns from executives about SQL Server scalability. Those complaints have dwindled, but I think there are people in the relational database world that still question SQL Server’s ability to handle high volumes or data sets.

    This Friday, I wanted to see what might change their mind, or maybe what might make you view SQL Server differently. While I suspect most of you are SQL Server fans, that doesn’t mean that you don’t have concerns at times about how far you can push SQL Server. The question this week is:

    What company would impress you if SQL Server backed their main systems?

    This isn’t scientific, and I’m not defining which systems impress you. It could be their web site, their supply chain management, their largest data warehouse, or anything else. What company talking about using SQL Server in one of their systems would impress you? And which system would you like to see backed by SQL Server?

    When I think about a lot of data, high volumes and rates, I think of a few things. The stock market, sports, and Amazon. If Amazon switched their website and ordering systems to SQL Server, I’d be impressed. If Major League Baseball, or the National Football League were to run all their statistical systems on SQL Server, I’d be impressed. With all the fantasy sports fans out there, the gathering and processing live data for real time calculations of performance, would be impressive.

    I’ve left out other companies. Wal-Mart using SQL Server for supply chain management would be impressive. UPS managing packages on SQL Server would be something. I’m sure have your own ideas, and let us know this week.

    Steve Jones

    The Voice of the DBA Podcasts

  • My 2011 PASS Summit Submissions

    I wasn’t sure that I would submit anything, but in the end I committed to a few events in Seattle and decided that this was something I should do. If I get selected it will save my boss some $$ and it’s part of what I should be doing for my job.

    I’ve done 3 different presentations in the last year for various events and decided on submitting only one of them to the Summit. The other is a variation on one I’m building for SQL in the City. I built that one to show off some Red Gate tools, so the places where I show demos of tool stuff I’ll rip those out and go with generic solutions instead.

    The last one actually came to me one night when I couldn’t sleep. I had been thinking about work and had an upset stomach one night. I was up a few times and while sitting on the edge of the tub, trying to decide if I should go back to bed or just give up, this presentation came to me. I had the iPad with me and spent about 45 minutes or so in Evernote, sketching the outline to the presentation in 10 parts, and then fleshing out 4 or 5 of them into a few paragraphs apiece.

    Here are my three, and no idea how they’ll do against the competition, or even if the committee will consider them, but we’ll see.

    Preparation for Disaster

    Sooner or later some sort of disaster occur on your SQL Server instance. It might be the destruction of a server, the corruption of a page inside the database, or just the unexpected deletion of some data. When disaster does strike, will you be prepared? Steve Jones will cover a number of common disaster occurrences and the ways in which you can be prepared to handle the situations.

    The Top Ten SQL Server Skills You Need

    Come learn the ten most important skills that every accidental DBA, developer, or even manager should be able to do in SQL server. If you want to know where to start working with SQL server or which 10 things will get you through most of your days, some see Steve Jones show you how to accomplish these ten important tasks and explain why they are important. These ten skills represent the 80/20 rule in action; if you can do these ten things, you can successfully get through most of your days at work.

    Branding Yourself for a Dream Job -The Modern Resume

    Everyone wants a dream job that they enjoy going to each week. However finding that job, and getting yourself hired can be hard for most people. Steve Jones will give you practical tips and suggestions in this session that show you how to better market yourself, how to get the attention of employers, and help improve the chances that the job you want will get offered to you. Learn about networking, blogging, writing, speaking , and more from Steve Jones, who has successfully used these techniques to find his dream job.

  • A row has no row number

    It seems that every month I have someone asking the question about ordering or row numbers for a query. Let’s get one thing clear from the start: there are no "row numbers" in a table.

    You can assume that the first row you inserted is row number one, but it’s not. In fact, depending on the indexing or lack of indexing, you may or may not get that row returned first by a query. You can add an ORDER BY when you query the table, and in that case you can get the rows returned in a certain order every time, however the row number is not linked to a row.

    As an example. If I have this People table:

    ID Name
    -- -------
    1  Steve
    2  Gail

    and I query: 

    select ID, name from people order by name

    I get 

    ID Name
    -- -------
    2  Gail
    1  Steve

    I could add a row number 

     
    SELECT row_number() OVER (ORDER BY [name])
           , [Name]
       FROM dbo.People

    and get this: 

       Name
    -- -------
    1  Gail
    2  Steve

    But "Gail" isn’t linked to "1" as a row number. If I do this: 

     
    INSERT people SELECT 3, 'Bob'

    SELECT row_number() OVER (ORDER BY [name])
           , [Name]
       FROM dbo.People

    I now get this: 

       Name
    -- -------
    1  Bob
    2  Gail
    2  Steve


    Now "Bob" is 1. You can get row numbers, but they are only linked to an ORDER BY and a specific result set. If the data changes, the row numbers may move.

    While it might appear in some queries that you are getting consistent ordering of results, don’t confuse coincidence with causality. You might live on those assumptions for years, building code on them, and then make a few changes and lots of things break.

    If you need ordering, use ORDER BY.

  • The Titanic Cloud

    Nothing is Unsinkable

    Is a cloud provider unsinkable? This piece from CNN shows an image of the Titanic as it talks about Amazon’s recent Amazon Web Services outage, which took down parts of many sites, including Reddit, FourSquare and the New York Times.

    While I think Amazon has done an amazing job of building technology well, they are no different than any large provider. Google has had issues with their services and Hotmail recently had a failure that lost data for some people. Salesforce.com has gone down and Oracle’s “unbreakable” moniker has proven to be untrue, and the unsinkable Titanic wasn’t the first large endeavor to fail.

    What’s interesting to me is that AWS wasn’t down for me. I didn’t use any of the services mentioned at the time of the outage, so as far as I was concerned, nothing happened. That’s the luck of the draw and as we move to a more connected world, with our systems reaching people all across the globe, we have a large variety of customers who will never notice our issue. The flip side is that it’s more likely at least someone will notice any of our issues.

    There isn’t a foolproof way to build a computer system that runs 100% of the time. As much as every large provider in the “cloud” will try to convince you otherwise, there will be issues. If the cloud is right for you, and fits your business, make sure that you have good backup plans in place, consider architecting your systems to work across different providers, and ensure that you always, always, always have good backups.

    More than ever I think this mantra applies to people managing data in the cloud: good backup, good resume. You need one of them.

    Steve Jones