Voice of the DBA

Author: way0utwest

  • Unit Testing T-SQL Code

    Abstract

    Unit testing has become an integrated, expected part of most software development teams. Many database developers have yet to implement unit testing as a regular habit. This session will look at two-unit testing frameworks and show how to implement tests for common types of non-trivial T-SQL queries. You’ll examine the tSQLt framework as well as the Microsoft Unit Testing framework for SQL Server.

    You will learn:

    • How to structure and build unit tests for database code with tSQLt
    • How to structure and build unit tests for database code with database projects
    • Understand the challenges of test data and how to solve them

    Level: 200

    Demos

    These are the testing demos for this talk:

    • Loading and reloading test data
    • Checking standards
    • Checking joins, specifically outer join refactoring
    • Checking function calculations
    • Checking boundary conditions
    • Using the 0-1-Some pattern
    • Checking NULLs

    PPTX and code on github:

    Data Platform Summit – https://github.com/way0utwest/UnitTestingTSQL/tree/master

    VS Live 2017 – Anaheim – https://github.com/way0utwest/UnitTestingTSQL/tree/vs2017Anaheim

  • A Tour of SQL Server Security Features

    Abstract

    Protecting data from unauthorized access becomes more important all the time. SQL Server includes a number of features that make data protection and security easier for developers and DBAs with a framework for protecting data. Come learn how Always Encrypted, TDE, Row Level Security, Dynamic Data Masking, and column level encryption can protect your systems.

    You will learn:

    • About the different encryption and security features in SQL Server
    • Understand the code changes required for encryption mechanisms
    • Gain a basic understanding of RLS and DDM, which do not require code changes to help protect data

    Level: 100

    Demos

    This talk includes the following demos

    • Always Encrypted setup and data access
    • Row Level Security setup and use
    • Dynamic Data Masking for users
    • Column Level Encryption implementation
    • TDE setup and verification

  • Rebooting Ubuntu Linux from the Command Line–#SQLNewBlogger

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

    Since SQL Server 2017 is now out, and Linux is an option, I thought I’d write a bit more about little Linux things that admins might want to know.

    After installing an update, I neede to reboot my Ubuntu install. Apparently there was a dialog, but it was hidden. Unlike on Windows, I didn’t see an easy way to click a button and restart. However, since I had a terminal shell open, I was sure there was an easy way to do this.

    I could have run a quick search, but I fell back to the old standby:

    man shutdown

    This showed that there is a shutdown, as on Windows, with the same –r parameter. This needs sudo to run, and by default it reboots after a minute. The command I ran was:

    shutdown -r

    Learned something, which I should have known. I almost typed shutdown, but I decided to double check.

    Note: I realized later that there is a small menu in the upper right. I had the VM in a window and didn’t notice the icon. Clicking the gear gives a menu.

    2017-10-02 10_38_28-Ubuntu 64-bit SQL Server .210 - VMware Workstation

    This lets you then choose shutdown or restart.

    2017-10-02 10_38_36-Ubuntu 64-bit SQL Server .210 - VMware Workstation

  • Software Patents

    This editorial was originally published on Jan 30, 2014. It is being re-published as Steve is out of town.

    I highly dislike the current patent system in the US. I think awarding patents for an idea like “1-click purchasing” is a poor practice that discourages innovation. There are numerous problems with our patent system, not the least of which is that it can stifle new ideas and prevent small companies from building on similar ideas that are, well, what I’d all common sense. I don’t quite share Richard Stallman’s view of software patents, but I do think we have fundamental flaws in the way we handle this intellectual property.

    I do, however, think that software has value, and the work we do as software engineers deserves compensation, as noted in this post. We should have some patents, and we should reward those that create truly innovative, and not common sense, ideas in the digital world. How we define those, and how we measure innovation isn’t clear to me, but I do think that non-trivial ideas deserve protection.

    Our legal systems around the world are slow to catch up to the digital world, and may never do so. As quickly as we can debate patents, copyright, trademarks, and more, the digital world grows, expands, and changes to stretch and challenge our thinking of how the world works. It almost seems as though digital ideas and software defy analogies and explanations that we can relate to the real world.

    I don’t know how I’d change patents, but I don’t want them to go away completely. I do, however, know what I’d do with copyright. I’d change it back to the original system we had, granting 14 years exclusivity to creators, with a single option to renew for 14 years and that’s it. The world certainly changes too fast to wait 95 years to use someone else’s work.