Voice of the DBA

Author: way0utwest

  • Going On-Prem?

    When I started in this business, every company owned their own servers, and had their own data center. Now, some of those data centers were closets (including that for one state government Senate). Some were offices-converted-to-computer rooms, rarely with separate air conditioning until I requested it. Some were actual data center rooms, though usually for larger companies. The idea of putting your computing resources off-site was seen as ludicrous.

    As time passed, the idea of using a co-location facility grew. In fact, SQLServerCentral went from a couple machines in a residential basement to a locked rack at a facility in Denver. Many companies started to use third party data centers, and in the late 90s and early 2000s, this was common, with facilities springing up. Full time employees will often racked equipment and visited data centers when they needed physical access.

    This changed over time as virtualization grew, and more and more companies started to rent a VM, rather than purchase a physical box. Some still used physical machines, but they rented the machine, allowing a third party to setup and mount the equipment, sometimes even installing the OS and then providing access to the lessee. We’ve come to the place now where we have cloud services that are often just platforms or services, where we have no idea of the underlying equipment. In the case of things like Salesforce, Azure SQL Databases, or Amazon’s RDS, we don’t even care, just asking for a certain level of performance.

    Is this the future? Will we now be asking ourselves if we “want to go on-prem” as the default question? I ran across the blog linked, which is from a SaaS perspective, that is trying to always get customers to use the service (or platform) and never have a local install. I know Microsoft would like to do this, especially for Azure databases as they make more profit on compute services.

    Some of you think this is crazy. In fact, I know plenty of industries that struggle with this from a regulatory standpoint. However, I’d point out that the idea of letting someone else run your email system was seen as crazy 20 years ago. Today many people (myself included) would never think to install Exchange or any other email software. We would always purchase this service from someone else.

    Tim Mitchell had a podcast interview recently talking about whether the on-premise data warehouse is dead. While I think the Azure Data Warehouse has some great advantages, and is worth considering, I’m not sure the local DW is dead. In fact, for lots of tasks I perform, including software Continuous Integration builds (.NET or database), I prefer to have some local resource doing the work. Especially when I can’t predict the number of times I’ll build and that task is easy to scale on local machines.

    I don’t think that we are at the point where we’d put more databases in a cloud service than on premise, but I do think that for new applications, it’s a valid question to ask whether the database and application could be hosted in the cloud.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 4.7MB) podcast or subscribe to the feed at iTunes and Mevio .

  • Using sp_executesql Parameters –#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    I haven’t used sp_executesql much. Instead, my habitually way of executing dynamic SQL has been with EXEC(). There are a few differences between these commands, but I had to look at sp_executesql recently and realized I didn’t know much about it.

    One of the neat things with sp_executesql is that you can pass in parameters.  That’s pretty cool. I hadn’t ever bothered, but if you read the docs, you’ll see that if you execute the same code over and over, with different parameters, you might get the same execution plan. This can be a performance boost.

    NOTE: THIS IS NOT ALWAYS BETTER. It can be.

    I’m not going to delve into deep details, but Kimberly Tripp does so read her post (and then write your own thoughts).

    The Code

    Here’s some code to demonstrate. I have a simple table with 3 columns to insert. In this case, here’s my insert:

    INSERT EventLogger VALUES (@m, @d, @u)

    Now, I went to use this over and over, but with different values for the parameters. Obviously I can just do this:

    …

    SET @m = ‘Error Message’

    INSERT EventLogger VALUES (@m, @d, @u)

    SET @m = ‘New Error Message’

    INSERT EventLogger VALUES (@m, @d, @u)

    …

    However, imagine that I’m building this INSERT string dynamically because it’s more complex. How do I execute this over and over with new values? With EXEC(), I rebuild the string. With sp_executesql, I do this:

    DECLARE @cmd NVARCHAR(MAX)
    DECLARE @dt DATETIME = GETDATE();
    DECLARE @msg VARCHAR(200) = ‘An error occured’;
    DECLARE @usr VARCHAR(10) = ‘Steve’;
    DECLARE @p NVARCHAR(500);

    SELECT @cmd = N’INSERT EventLogger VALUES (@m, @d, @u)’

    SELECT @p = N’@m varchar(200), @d datetime, @u varchar(10)’

    EXEC sp_executesql @cmd, @p, @m = @msg, @d = @dt, @u = @usr;

    SELECT @dt = GETDATE()
         , @msg = ‘A new error occured’
         , @usr = ‘Bob’;

    EXEC sp_executesql @cmd, @p, @m = @msg, @d = @dt, @u = @usr;
    GO
    SELECT top 10
      *
    FROM dbo.EventLogger AS el

    Now, I check the table:
    2016-06-22 15_00_08-Settings

    I thought that was cool.

    SQLNewBlogger

    This isn’t a deep post. It’s a light look, with a little explanation. I’ll do more later. However, I’m hoping this serves as a way to show you how to start investigating a topic. I’ve spent a bit of time experimenting and learning. I’m fairly confident I could play and use sp_executesql more.

    You could do this as well, start digging into a topic and then show how you’re learning.

  • Fuzzy Processing

    This is really interesting. A computer chip that doesn’t exactly perform mathematical calculations correctly. That sounds crazy, and in some cases it is. Most of us want math to be exact, and work perfectly every time when we run calculations. Can you imagine if Excel didn’t always add up the totals in your expense report according to the rules of addition?

    However in more complex operations, such as pattern matching for images, we might not want exact calculations. If two radar images (one problem in the article) don’t match exactly at the binary level, would we want a search for similar images to discard one? After all, exactness and perfect matching are great in some areas, such as financial accounting, but in other areas, such as imaging, there is plenty of noise that isn’t important to the content of the image.

    Do we care? Perhaps. As data professionals, I suspect that we will get more and more data of disparate types, including images, that will become part of our databases. There will be clients the need to search and query this data, which means that we may need fuzzy search tools that work well.

    New search tools will likely mean that we will need to learn more about how to tune queries, or even indexes, that might deal with data in a way that isn’t exact. I suspect that there will be opportunities for those that learn how to deal with these types of problems in an effective manner. There might even be really good paychecks that come along with these jobs.

    It’s a bit disconcerting to think that we might want computer applications that don’t work exactly as we expect with regards to calculations. However, I think the science of uncertainty will become more relevant and valuable to us data professionals in the future as we deal with lots of data that is more complex than a simple addition problem.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.6MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • The Bad Data Shutdown

    I’m a car guy. I like cars, I like driving, and I’ve spent a lot of time and money over the years on vehicles. I’ve swapped and enjoyed a few dozen automobiles as part of my life. If you are on Twitter, you might occasionally see @BrentO and myself go back and forth on some car topic, usually Porsche related. This usually results in an hour or so of life wasted on dreaming of a new car (including getting distracted while writing this piece and pasting that last link in, where I spent quite some time drooling over the Macan).

    Recently there was an issue with the navigation system in Lexus vehicles. Apparently bad data was sent during a software update, which is not exactly what you want to happen in a car. I’ve had a few modern vehicles, some of which would be quite handicapped if they onboard computer were frozen or rebooting. In my current vehicle, this would cause issues with climate, navigation, entertainment, and potentially other systems. After all, I suspect many things from door locks to speed control are all integrated together. Certainly the drivetrain is as an open door will automatically shift my car from drive to park, at least at low speeds.

    As we move to more drive by wire, bad data or bad software that disrupts the computer systems could be very dangerous. It’s not just updates, but this could even be some internal Denial of Service issue from a USB device or bluetooth connection. In this case,  Lexus acknowledged the problem, which I’m glad to see. The Internet ensures that problems can be reported from many users quickly and very publicly. That makes it hard to deny a widespread problem.

    Delivering updates across wireless links is great. It’s cheaper for everyone, saves time, and owners appreciate convenience. However, moving to this model often requires some sort of continuous delivery (CD) process, which should also allow for rolling forward, and releasing fixes for problems. However, if the updates you deliver cause the system to cease functioning, then this doesn’t help. At the last, your QA system needs work and you don’t have a well designed software delivery process.

    Various companies are getting better at delivering updates to our systems without downtime, but there’s still work to be done. The smaller your domain of clients, the easier this is. For many of us that work on small systems, an application server or two and a database, we can certainly get much better at ensuring our updates are tested, and more importantly, that we can quickly deploy a second patch if we find an issue. That requires engineering a process that is known and stable, with the ability to respond quickly. For larger systems, with many clients, you need a really solid engineering and deployment process.

    Above all, however, no matter what your deployment mechanism for updates, you need to be sure that any data you include is at the quality level you’d expect would be delivered to you.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 4.7MB) podcast or subscribe to the feed at iTunes and LibSyn.