Voice of the DBA

Author: way0utwest

  • The Next DLM Demo Webinar–Feb 23

    A quick note that I will be presenting a webinar on Feb 23rd at 10:00am MST. That’s noon EST and 5:00pm GMT. You can sign up here if you’d like to watch. This is similar to other DLM webinars I’ve done that look at the Redgate stack of products that help you develop database software in a smoother fashion.

    I am changing things up a bit. This time the stack is:

    • Git – version control
    • Team City – Continuous Integration or Build server
    • Octopus Deploy – Release Management tool.

    As with the other webinars, I’ll show how I track the version of the database in each environment, then show how to make changes in SSMS, commit them to my VCS with SQL Source Control, and then deploy them in an automated fashion across various environments. In this case, I’ll have this flow of environments

    • development
    • integration
    • test
    • acceptance
    • production

    It’s an impressive demonstration. As I’ve watched this grow at Redgate, I am impressed with how well things work. I wish I’d have had these tools when I built database software earlier in my career.

  • The Easy Phone Restore

    This is hardware week for me, with a new phone coming in the mail. This was a warranty replacement for one whose battery is dying. I can start the morning at 7am with 100% charge and by noon my iPhone is asking to go into Low Power Mode at less than 20%. Fortunately I have a warranty program with my carrier, so they just sent me a new one.

    I was a bit amazed (again) at how easy it is to move and replace an iPhone. To be fair, my wife got a replacement for her Nexus, and it was about as simple. Both of us use cloud backups, and with an easy restore, the vast majority of our phones are back in service, icons, apps, settings the way they were. Switch SIM cards, and the new phone just works.

    Of course there are some passwords to re-enter and new authentication, but it’s very minimal and once I get my password manager running, things go smoothly.

    I wish it were as simple to upgrade a laptop. Instead there are numerous applications to install and get configured. Fortunately I’ve got a process that worked well last year, and I’m getting the chance to refine slightly this year. I’ll be posting a bit about that as I move forward.

    Now I’m hoping that this phone works a little better than the last one and gets me through some of those long, SQL Saturday days.

  • CONVERT and HEX

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

    In working through the Advent of Code and solving some of the problems in SQL, I found that I needed to take hex values and convert them to strings. In other words, I had a value like this:

    select @hex = 0x3c044139f4fe36d7df0f4e87f948fc52

    and I needed to determine if the first few characters (5 or 6), were 0s. In other words, I wanted to look at this part of the data above as a string.

    3c044

    I thought this would be simple. I tried this

    select @value = CAST( @hex as varchar(50))

    That’s my default, as it reads nicely. However the returned this:

    <A9ôþ6×ßN‡ùHüR

    That’s strange. I then tried CONVERT:

    select @value = convert( varchar(50), @hex)

    I got the same result. Why am I not getting the same value as a string? I looked at a few other code samples from others, and they looked the same, so I checked the documentation for CONVERT. I saw this:

    Binary Styles: When expression is binary(n), varbinary(n), char(n), or varchar(n), style can be one of the values shown in the following table. Style values that are not listed in the table return an error.

    Under the table, the information for 1 or 2 as a style has this:

    If the data_type is a binary type, the expression must be a character expression. The expression must be composed of an even number of hexadecimal digits (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, a, b, c, d, e, f). If the style is set to 1 the characters 0x must be the first two characters in the expression. If the expression contains an odd number of characters or if any of the characters are invalid an error is raised.

    …

    The characters 0x will be added to the left of the converted result for style 1.

    All of that essentially means that if I use the default, 0, or have nothing, I get the binary data converted to to the binary bytes in ASCII. If I use 1 or 2, I get the string. Here’s a shot of the difference:

    2016-02-02 11_00_14-Settings

    Two lessons. First, learn the data types and how they convert. Second, read the documentation carefully when things don’t work as expected.

  • Forcing Strong Passwords

    It seems that every week we have some sort of data breach that results in emails and passwords for users being released. That alone is a good reason why you should not have passwords shared across sites or systems. I can’t stress this enough, and if you do this, please go get a password manager today and stop. Really, I mean it.

    However is it just the users that create poor passwords? In many cases, sure. However as the people that develop software, we can help. In fact, we should. Security should be on the mind of everyone that writes software.

    I ran across a piece on The Hacker News that says far too many sites don’t require users to choose strong passwords. I think this is changing, but far, far too many sites don’t check a user’s password for strength, or reuse. Some even force users to choose less than ideal passwords. I’ve run into plenty of sites that don’t accept the characters in my strong passwords for some reason. Often the length or addition of any special characters if prohibited.

    I’ve said it before, but I’ll note it here. Most of us aren’t competent at writing authentication routines and should just use one that’s already written and been vetted. More importantly, we should be sure the code, application and database, is modular enough and can be replaced if we find that our application needs stronger security. Because it will. We are constantly racing Moore’s law to implement better security.

    Steve Jones

    The Voice of the DBA Podcast

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