Want Coffee with your Workload Simulation?

Hey, train wreck, this isn't your station

Coffee is an OLTP (online transaction processing) workload simulator for SQL Server, Azure SQL Database, and Azure SQL Managed Instance, which mimics the activity of a point-of-sale (POS) system. It simulates the handling of hundreds of orders created, updated, and retrieved hourly from dozens of terminals scattered through the restaurant.

The motivation for Coffee came from several projects needing to evaluate database features under load. Throughout those projects I wished I had a modifiable, simple to use, affordable, and scalable tool that would run a set of OLTP workloads against a simple schema, enabling all sorts of fun things:

  1. Generate test data, useful for testing visualization and reporting tools
  2. Gauge the performance impact of security features like Always Encrypted or Transparent Data Encryption (TDE)
  3. Evaluate different network, database, or system configurations
  4. Perform mock administration tasks (e.g., failing over a replica, modifying a file group) with a live system.

What’s Coffee in a Nutshell?

Servers and kitchen staff place, update, and ring-up orders across several terminals. The simulator mimics this behavior concurrently executing order create, update, and retrieve actions. Action execution is distributed over several threads run in parallel. Each thread deliberately delays triggering an action for a random time interval, which is a random value with-in a set range. The delay avoids all actions executing simultaneously and mimics the ad hoc nature of the workload. The end result is a system that mimics the use pattern of servers and kitchen staff.

What can host a Coffee database?

The project was initially developed with SQL Server 2014 (version-12.x) in mind. However, it has been used with versions of SQL Server through 2019 as well as Azure SQL Databases.

How does Coffee work?

Coffee is written in Windows PowerShell. The project’s repository is hosted in Github. It includes a READ ME that outlines the application’s design, describes usage, and identifies dependencies.

Users interact with the system through a command line interface. Coffee ships with several scripts described in the project’s READ ME. One of the most of these scripts is the launcher script, which initiates workloads. When executed, the launcher idles until the start of the next minute then launches the write, read, and update controllers.

Coffee Execution

The write, read, and update controllers spawn workload threads that generate load against our database. The whole application runs in a single PowerShell process.

Engineers can adjust the workload volume and concurrency from the launcher script. The volume of work is the number of create, read, and update actions to trigger. The concurrency of work describes how many threads are created for each type of action: read, update, and create. By default, Coffee creates, updates, and reads 35,000, 35,000, and 30,000 orders respectively with each controller spawning 5-threads for a total of 15 threads. Because each thread gets its own connection, you will see 15-sessions for Coffee’s PID when running a simulation with default settings.

Once the simulation completes, you will be left with the number of orders you asked the write controller to create, 35,000 by default.

I purposely kept the database’s physical model simple and intuitive to make it easy for developers to manipulate and query. The database has four tables all in the “dbo” schema:

  • dbo.customer, this table captures the restaurant’s customers.
  • dbo.sustenance, contains the restaurant’s menu items.
  • dbo.order, this table contains the restaurant’s orders.
  • dbo.order_detail, hosts the dishes purchased with a given order.

The tables are related as follows:

Coffee Schema Diagram
This is the physical data model for Coffee.

The data generated as part of a simulation remains once the simulation completes.

This data comes in handy when testing visualization and reporting tools, partitioning schemas, or different SQL commands.

Lastly, Coffee saves runtime metrics for each executed simulation in a pair of files: test summary and test detail. The test summary file captures metrics by workload controller. These metrics include controller start and end date and time, total run time, and number of threads.

The test detail file captures metrics for each action executed as part of a given simulation. The metrics report include the action’s type, duration, number of errors encountered, worker thread id, and start time.

Each file includes the name of the machine executing the simulation and the simulation’s start date and time. Engineers can use this data in concert with additional metrics to gauge system health.

Conclusions

Engineers can leverage Coffee whenever they need (a) sample data or (b) to gauge system behavior in the context of a condition or system state change.

This project is far from a polished solution. Despite the many areas for improvement, Coffee remains one of my favorite pet projects, and a tool I find myself using again and again in my work. I use Coffee with cloud and on-premise installations of SQL Server. I use it with cloud based DBaaS solutions like Azure SQL Database. I use it in presentations and training classes. I use it to generate test data when exploring data analysis and visualization tools. For these reasons, Coffee is a project I thought worth sharing.

|      

Mocking .NET Objects in Pester Scripts

You have written several functions in a PowerShell script or module. You want to use Pester, a unit testing framework for PowerShell, to create unit tests for those functions. Unfortunately, several of the functions call methods of .NET objects, and you can’t mock those method calls. As result, the method is executed when you run your test script.

To illustrate the fix we’ll use the following example:

function do-thing {
    Param ($servername)

    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.smo") `
        | Out-Null

    $server = New-Object 'Microsoft.SqlServer.Management.Smo.Server' `
        -ArgumentList $servername

    $Server.ConnectionContext.ConnectTimeout = 5
    $Server.ConnectionContext.Connect()
    $server.Databases
}

When `do-thing` is executed the following occurs:

  1. A Microsoft.SqlServer.Management.Smo.Server object named $server is created
  2. The $server object’s Connect() method is called
  3. The $server object’s database collection is returned

This function will blow-up at step 2 unless its actually connecting to a live SQL Server instance, an external element. It’s a generally accepted best-practice that unit tests should be independent of external elements, components outside the unit being tested. You typically control interactions with external components through the use of mocks.

If you’re running PowerShell 5.x or greater, you can employ a mock object to solve this problem. A mock object according to Margaret Rouse “…is a simulated object that mimics the behavior of the smallest testable parts of an application in controlled ways.” You’ll start by creating a mock class, which you’ll instantiate into a mock object. The following is an example of this concept in practice:

describe 'do-thing' {
    class fake_smo_connnection_context {
        [int] $ConnectTimeout
        [void] connect(){ }
    }

    class fake_smo_server {
        [string[]] $databases = @('foo', 'bar')
        [fake_smo_connnection_context] $ConnectionContext

        fake_smo_server() {
            $this.ConnectionContext = `
                New-Object 'fake_smo_connnection_context'
        }
    }

    context 'when the server exists' {

        Mock 'New-Object' { New-Object 'fake_smo_server' } `
            -ParameterFilter {
                $TypeName -and  
                $TypeName -eq 'Microsoft.SqlServer.Management.Smo.Server'
            }

        it 'should complete successfully' {
            do-thing -servername 'whatever'
        }

        it 'should return databases' {
            $databases = do-thing -servername 'whatever'
            $databases.Count | Should -Be 2
        }
    }

    context 'when the server does not exist' {

        it 'should throw an exception' {
            { do-thing -servername 'whatever' } | Should -Throw
        }
    }
}

We’ve created two classes:

  1. fake_smo_connnection_context, a class to mock the server’s ConnectionContext object
  2. fake_smo_server, a class to mock the server object

fake_smo_connnection_context has single method named Connect() that does nothing and a single property ConnectTimeout. fake_smo_connnection_context mocks the ConnectionContext type. We’re not creating a complete facsimile of the type we want to mimic. We’re only creating the parts of the server object needed to unit test this function. Since fake_smo_server contains fake_smo_connnection_context we’re declaring fake_smo_connnection_context before fake_smo_server. We do this because you can’t create a variable of type fake_smo_connnection_context until type fake_smo_connnection_context has been defined. Lastly, the classes were defined in a describe block. We did this because the context and it child-blocks have visibility into the parent describe block. I tend to define mock-classes one scope up from where I’ll use them.

If you’re familiar with classes but aren’t familiar with them in PowerShell, checkout the post PowerShell v5 Classes & Concepts by Michael Willis. If you’re not familiar with classes at all, …I’m impressed that you read this far. You’ll want to search around for an introduction to classes in PowerShell. However, if you’re in a pinch there is an alternative.

If you can’t create a mock object, you can wrap the method call in a PowerShell function then simply not test the wrapper. The following is the function with a wrapper:

function run-wrapper {
    Param ($server)

    $Server.ConnectionContext.ConnectTimeout = 5
    $Server.ConnectionContext.Connect()
    $server.Databases
}
function do-thing {
    Param ($servername)

    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.smo") `
        | Out-Null

    $server = New-Object 'Microsoft.SqlServer.Management.Smo.Server' `
        -ArgumentList $servername

    run-wrapper -Server $server
}

The code in this post was tested with Pester 4.4.0 and PowerShell 5.1.17134. Pester changed the way assertions were passed with Pester 4.x. With Pester 4.x assertions are passed as parameters. If you are running Pester 3.x, some of the code in this article will not work.

|