Voice of the DBA

Tag: presentations

  • Bringing DevOps to the Database

    Abstract:

    DevOps is a hot topic in today’s software development world. However most of the knowledge and experience with DevOps is based around application software and ignores the database. We will examine how the concepts and principles of DevOps can be applied to database development by looking at both automated comparison analysis as well as migration script management. Automated building, testing, and deployment of database changes will be shown.

    Level: 200 – Not to technical, but you should have some idea of how software development works to get something out of this talk.

    Goals:

    1. Conceptually understand how DevOps principles can easily be applied to the database in either state based or migrations based changes.
    2. Learn how databases can be automatically built and tested in a CI/CD environment
    3. Understand how database releases can be automated, but are different than those deployment/releases made for other software.

    Demos:

    This presentation has a few demos where I show how I stored database code in a VCS, perform an automated build, and use a scripted release process to move code from one database to the next. The demos are pre-built, so I just show how they work. I can do this with:

    • git, VSTS Build and VSTS Release
    • git or SVN, Team City, and Octopus Deploy

    Downloads:

    There are a few versions, as this talk has evolved.

    GroupBy – Bringing DevOps to the Database Groupby.pptx

    Live!360/VSLive – VSLive-Orlando 2016.pptx

    SQL Saturday Cambridge – Bringing DevOps to the Database.pptx

  • Testing T-SQL Made Easy

    Abstract:

    Everyone tests that code, but most people run a query, execute a procedure and run another query. This ad hoc, non-repeatable testing isn’t reliable, and encourages regression bugs. In this session you will learn how to begin introducing testing into your development process using the proven tSQLt framework. You’ll run tests with a click of a button, using an ever growing test suite that improves code quality. You will see how to handle test data, exceptions, and edge cases.

    Level: 200 (I am assuming you know something about unit testing and tSQLt)

    Demos:

    • Creating exceptions to standards
    • Catching changes to large tables
    • Ensuring the join works correctly
    • Testing Getdate()
    • Isolating functions and stored procedures from other calls
    • Checking boundary conditions

    Downloads:

    Powerpoint: Testing T-SQL Made Easy.pptx

    Code: .zip file or Github repo

    Presentations

    • SQL Bits XV – May 7, 2016

  • End to End Always Encrypted in SQL Server 2016

    Abstract: Protecting our data from unauthorized access becomes more and more important all the time, however it has been difficult to ensure sensitive data is encrypted in SQL Server. The new Always Encrypted feature in SQL Server 2016 makes this much simpler for developers and DBAs with a framework for protecting data from the client, across networks, and inside of the database. This new feature allows for limiting access to the data, even from the DBAs and sysadmins that may control the database instance itself. Learn how to implement and use Always Encrypted in your applications.

    Length: 60-75 Minutes

    • Demos:
    • Always Encryption Setup
    • Working with Data in a client application
    • Certification creation and transfer
    • Certificate Rotation

    Powerpoint Deck: End to End Always Encrypted.pptx

    Code: Github

  • Using Automated Tests to Raise Code Quality

    Abstract

    Agile development practices can speed the development of applications and increase the rate at which you can deploy features for your customers. But unless you include a high level of test coverage in your code, these practices can also increase the number of bugs that are released. Databases can be particularly challenging for developers. This session examines database refactorings that introduce bugs, which are detected by automated tests. This approach allows developers to rapidly fix their code before a customer is affected.

    Level: 200

    Demos

    These are the demos shown in this talk.

    • Adding test data inline
    • Added test data in a procedure
    • Adding test data from a separate set of tables.
    • Exclusions to SQL Cop or other tests with Extended Properties.
    • Using FakeFunction
    • Using SpyProcedure
    • The boundary issues with multiple requirements for a function.
    • 0-1-Some testing
    • Catching dependencies.

    Downloads

    Here are the downloads for the talk.