Voice of the DBA

Author: way0utwest

  • Quick Command Shells–#SQLNewBlogger

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

    This isn’t a SQL post, per se, but since I’ve been using lots of PowerShell for SQL stuff at times, this is a handy trick I picked up.

    I was reading the announcement on the Windows Insider build 14971, which will have a few new things. One of these is the default command shell won’t be cmd.exe any longer. It will be PowerShell.

    Buried in the announcement was a tip I’d never heard. I don’t have any real cmd additions to my Windows machines, so to get to a particular folder in a command shell, I usually do lots of “cd”s or I right click in the address bar, and copy the path. Thankfully Windows 10 lets me paste this easily into a command prompt.

    2016-11-21 16_21_20-EndtoEndEncryptionwithSQL2016

    There’s another way.

    I can click the address bar and type “cmd”.

    2016-11-21 16_21_47-EndtoEndEncryptionwithSQL2016

    When I do this, I get a new command prompt at that location, as shown here. I do install ConEmu on my machines, so that’s where the prompt opens.

    2016-11-21 16_21_55-cmd

    That’s super handy.

    We’ll see how may workflow changes when PoSh is the default shell, but I’m looking forward to it.

    #SQLNewBlogger

    The quick tips that help me work more efficiently are what I’m writing in these posts. Here’s one that took my 5 minutes, and you can write this kind of post as well. Show off what you know, do, and have learned.

  • The Future of Auditing

    This editorial was originally published on Dec 5, 2013. Is it being re-run as Steve is on holiday.

    I was reading Captain’s Share the other day and enjoying a quiet afternoon at home. It’s a science fiction book about one man’s journey in the future as a captain of a space freighter. It’s an interesting series from Nathan Lowell that I’ve enjoyed and recommended to other science fiction fans. In the book, there’s a scene where the main character is leaving his old ship as first mate and moving to a new ship as the captain. However he notes that the formal process is to deactivate his records on the old ship and ensuring they will be read only forever. The book notes they can’t be deleted because they are a part of the ship’s records, log entries, etc.

    That seems to be a far cry from the way auditing takes place in current computer systems. Auditing of systems is under the control of the sysadmins (who are sometimes hackers) and can be altered, changed, etc. We, as software designers, haven’t done a good job of ensuring the integrity and longevity of log records. In some sense, it seems to be a fundamental flaw in OS and software design to not have separated out the auditing and recording of actions from the administration and rights of the rest of the system.

    I’d hope that we would recognize that auditing actions and preserving this data is something that ought to be tightly linked to, but separate from, the rest of system operation. I’d like to think that fundamental changes and actions taken on the system should be written separately to an area that is easily marked as readable by non-sysadmins that are designated to review the information. I know we have the challenges of managing the space and the problems of spurious actions being generated to fill (or rollover) logs, but I’d think after 50+ years of computing we would have considered some sort of event log that isn’t under the control of the people whose actions it is recording.

    SQL Server has improved its auditing features and capabilities, but far too much is still linked invariably to the sysadmin, often the same person the auditing should be watching. This is certainly one area that I hope matures in future versions as the need grows to track and review actions taken by privileged accounts.

  • Test Coverage

    This editorial was originally published on Nov 12, 2013. It is being re-run as Steve is on holiday.

    I’ve never had to work full time in a QA group, but I have had to perform software testing of an application and it wasn’t fun. Even as I worked through the various features, looking for edge cases, common mistakes, etc., I realized that running tests was something that really required more discipline and process than I was giving it at the time. Too often I ‘d realize that my regressions weren’t 100% duplicates of previous executions because I’d allowed too much “human effort” into my process.

    These days most testing of software is automated. I know QA groups still exist, and they need to handle some of the manual checks that are very hard to automate. However more and more testing is being pushed back onto developers to handle, with frameworks like NUnit and JUnit. In the database world, we haven’t done a great job of including testing into the code we write, but there’s a great testing framework we can use.

    TSQLT is a framework written by Sebastian Meine and Dennis Lloyd and it’s free. It’s been developed to help you write tests that can exercise your T-SQL code to determine if it’s doing what you expect. I’ve used it a little, and while I see the potential, I also realize that this will take some practice to learn how to more easily write tests that can cover the various potential places where bugs can be introduced.

    However, if you use version control, and you should, then you can easily spread the load of writing tests to all of your developers. Since the tests are stored procedures, they can be included in your VCS project and shared by all your developers. With a little effort from each member of your team, you might be surprised at the code coverage you can achieve with this framework.

    Testing is important, as we see over and over again as software is released and doesn’t work as expected. I think much of that is our fault, as software developers, for not improving our testing skills and discipline.

    Steve Jones

     

  • A Well Deserved Break

    This week I’m on vacation taking a bit of time away with the family after a week in the Redgate offices.

    I hope everyone has a great holiday week and an enjoyable Christmas.