Skip to content

posts🔗

  • 8 min read

A Smattering of Thoughts About Applying Site Reliability Engineering principles

What's This about

I figured I'd go ahead and take this article which I've gutted several times and share some thoughts, even if it's not an authority on the topic. 😀

In the last year, I've been interested in exploring the DevOps philosophy as it applies to operations as well as software development. I originally started various drafts on these concepts back before April 2019, but never got around to finishing it until now.

This isn't a very focused post, more a smattering of thoughts, so let's get to it! 💪

DevOps Is A Challenge

Having come from a development background, applying DevOps to the operational side of the fence is an interesting challenge.

There are so many things that can be unique to the evolving way a team you are part of is growing and learning, that it can be difficult sometimes to know what is "best practice" and was is "essential practice" in a DevOps culture.

  • What does it mean to plan and scale your workflow in a DevOps culture?
  • How do operational stability and the pressure for new feature delivery in a competitive market meet in a healthy tension?
  • How do you do "everything-as-code" in practice?
  • Lots more!

There are various ways to implement DevOps, and core principles to DevOps. I've found that one that really resonated with me as I've looked for how others have done this.

Site Reliability Engineering

Google's Site Reliability Engineering (SRE) material provides a solid guide on the implementation of SRE teams. They consider SRE and DevOps in a similar relationship to how Agile views Scrum. Scrum is an implementation of Agile tenants. Site Reliability Engineering is an implementation of a DevOps culture, with guidelines on how to approach this in a systematic way.

If you think of DevOps like an interface in a programming language, class SRE implements DevOps. Google SRE

What I like about the material, is that a lot of the fixes I've considered to improvements in workflow and planning have been thoughtfully answered in their guide, since it's a specific implementation rather than a broader philosophy with less specific steps.

Regardless of where you are in your journey, a lot of the principles have much to offer. Even smaller organizations can benefit from many of these concepts, with various adjustments being made to account for the capabilities of the business.

Recommended reading if this interests you:

Link
Deploys: It’s Not Actually About Fridays – charity.wtf
DevOps Topologies
Do you have an SRE team yet? How to start and assess your journey
Google - Site Reliability Engineering
Love (and Alerting) in the Time of Cholera (and Observability) – charity.wtf

Where I Started

At the beginning of my tech career, I worked at a place that the word "spec" or "requirement" was considered unreasonable. Acceptance criteria would have been looked down upon, as something too formal and wasteful.

While moving towards to implementation of any new project, I was expected to gather the requirements, build the solution, ensure quality and testing, and deploy to production. That "cradle to grave" approach done correctly can promote the DevOps principles, such as ensuring rapid feedback from the end-users and ownership from creation through to production.

A Different Take

I've been in a somewhat interesting blend of roles that gives me some insight into this. As a developer, I always look at things from an 🤖 automation & coding perspective. It's the exception, rather than the norm for me to do it manually without any type of way to reproduce via code.

I've also been part of a team that did some automation for various tasks in a variety of ways, yet often resolved issues via manual interactions due to the time constraints and pressures of inflowing work. Building out integration tests, code unit tests, or any other automated testing was a nice idea in theory, but allocating time to slow down and refactor for automated testing on code, deployments, and other tasks were often deemed too costly or time prohibitive to pursue, in addition to requiring a totally different skillset to focus on.

For example, you can't write code based tests against a deployment, unless you have made the time and effort to learn to code in that language and work through the pain of discovery in writing integration oriented tests. It's not a simple feat 🦶 to just pick up a language and start writing tests in it if you've never done this before.

Reactive

Reactive work also challenges DevOps focused teams that do operational work.

  • Difficult to categorize emergency from something that could be done in a few weeks
  • Difficult to deliver on a set of fully completed tasks in a sprint (if you even do a sprint)
  • High interrupt ratio for request-driven work instead of able to put into a sprint with planning. (This is common in a DevOps dedicated team topology that is in some of the Anti-Types mentioned in DevOps Anti-Type topologies)
  • Sprint items in progress tend to stay there for more than a few days due to the constant interruptions or unplanned work items that get put on their plate.
  • Unplanned work items are constant, going above the 25% buffer normally put into a sprint team.
  • Continued feature delivery pressure without the ability to invest in resiliency of the system.

Google has a lot more detail on the principles of "on-call" rotation work compared to project-oriented work. Life of An On-Call Engineer. Of particular relevance is mention of capping the time that Site Reliability Engineers spend on purely operational work to 50% to ensure the other time is spent building solutions to impact the automation and service reliability in a proactive, rather than reactive manner.

In addition, the challenges of operational reactive work and getting in the zone on solving project work with code can limit the ability to address the toil of continual fixes. Google's SRE Handbook also addresses this in mentioning that you should definitely not mix operational work and project work on the same person at the same time. Instead whoever is on call for that reactive work should focus fully on that, and not try to do project work at the same time. Trying to do both results in frustration and fragmentation in their effort.

This is refreshing, as I known I've felt the pressure of needing to deliver a project, yet feeling that pressure of reactive work with operational issues taking precedence.

It's important to recognize the importance of that operational work. It helps ensure a team is delivering what is most important to a business, a happy customer base! At the same time, always living in the reactive world can easily cause the project related work and reliability improvements that will support those efforts to suffer if a intentional plan to handle this is not made.

Reducing Toil

I've always been focused on building resilient systems, sometimes to my own detriment velocity wise. Balancing the momentum of delivery features and improving reliability is always a tough issue to tackle. Automation isn't free. It requires effort and time to do correctly. This investment can help scaling up what a team can handle, but requires slower velocity initially to do it right.

How do you balance automating and coding solutions to manual fixes, when you often can't know the future changes in priority?

It can be applied to immediate needs

A leader I've worked with mentioned one great way is to ensure whatever solution you build is immediately applicable to solving work in progress. By this, he meant work that could immediately benefit a team, rather than building things for future possibilities that haven't yet come into play. This aligns with a LEAN mentality and there is a lot to like about it.

It solves an immediate pain

Another way I've begun looking at this is solving the key pain-points that are mentioned by others. I tend to look at many improvements, and it can be difficult to choose what area to tackle at times, as I can see value in many of them.

However, solving paint points that are directly impacting someone enough that they are asking for help should take precedence in many cases (not all).

Why? This pain-point solved gains you an immediate win.

If you are solving an issue that others are ok with, or don't recognize as an issue, then you have an uphill battle for adoption compared to solving something immediately impacting their work. This doesn't negate addressing problems that others have not yet recognized, it is just something that's become clear can help in moving forward with improvements with less resistance.

Last Priority - Solving Problems No One Yet Knows They Need Solved

There is an important caveat to all of this. There is a realm of problems when you have expertise in an area that you will identify that others don't see. When assessed against goals of a business, these can be critical.

In my experience, the only way for these identified issues to be clearly prioritized is having a strong ability to communicate the problem and design some clear objectives to place this into the team backlog to address.

Verbally communicating, while important, won't have any staying power compared to a clearly defined objective in a backlog and advocated for in a presentation that helps raise concern for the same issue you care about.

They key is that your signal-to-noise-ratio is good, and when you raise the concern about the issue others don't have to work hard to understand why it's a problem.

How to balance product vs reliability improvements

SREs balance the juggling of product velocity and reliability improvements in a unique way... the usage of SLO and the error budget concept.

SLO & Error Budgets

The concepts of having an error budget really struct home for me. Clear Service Level Objectives (SLO) and error budgets to work with helps ensure both the development velocity and stability desired by operations is handled correctly.

Error budgets provide the key ingredient to balancing new feature delivery, while still ensuring happy customers with a stable system.

One of the best sections I've read on this was: Embracing Risk.

Error budgets are discussed, and internal SLI (Server Level Indicators). These are integral to ensuring a balance of engineering effort in balance with new feature delivery. The goal of 100% reliability, while sounding great, is inherently unrealistic.

Product development performance is largely evaluated on product velocity, which creates an incentive to push new code as quickly as possible. Meanwhile, SRE performance is (unsurprisingly) evaluated based upon the reliability of a service, which implies an incentive to push back against a high rate of change. Information asymmetry between the two teams further amplifies this inherent tension.

Wrap Up

I've been mulling over this for a while, and find a lot of the concepts very helpful as I've been challenged with both operational and development focus. As always, these are personal thoughts and don't reflect the opinion of my employer. I hope it gives you some food for thought. If you have some feedback, just post up a comment and let's dialogue!

  • 2 min read

Quickly Create Github Release via Cli

Intro

I've been trying to improve modularization with Terraform. Using Terraform Cloud, you get a private module repository. The modules are linked to tags in git.

I've wanted additionally to specifically create releases for the versions in addition to the tags, to make tracking of usable versions a little cleaner.

There are several ways to do this, including using the GitHub API, npm modules, and more. I wanted a simple CLI tool to do this and ran across this great Go utility that worked seamlessly for me.

I've used the Hub cli but the create release syntax never really worked for me.

github-release

Github-release is a simple golang cli that worked great for me. Note that this is a fork. This fork is more up to date than the original.

With go installed just run this to get it installed and available in PATH.

go get github.com/itchio/gothub

To simplify GitHub access, ensure you set an environment variable for your user called GITHUB_TOKEN.

With PowerShell you can do it quickly like this (you might need to close and reopen vscode/terminal for this to be recognized)

    [System.Environment]::SetEnvironmentVariable('GITHUB_TOKEN','tokenhere','User')

Usage

To use this, you can chain together some steps and see how it can save you time on creating a GitHub release.

{{< gist sheldonhull "53055bbff368a4ebe4e0794076a56c37" >}}

This helped me get moving faster ⚡ on using Github releases without the tedious work to create. If this helped you or have any feedback, drop a comment below and let me know! The comments are powered by Utterances which will open a Github issue to discuss further. 👍

  • 5 min read

Asciidoc Distributed Docs as Code

The Problem

  • I want to keep my code and my documentation in the same place.
  • I want to separate the presentation layer from my documentation content.
  • I want to be flexible to publish documentation to a variety of endpoints and formats as processes evolve, without my content being impacted.
  • I want to write a solid runbook for things that can't be fully automated, but still include scripts and other code in their native format.

Documentation is such an important part of a developer's life. I think we often take it for granted, and it's an afterthought in many projects. However, as I consider my work, I know that I'm not reinventing the wheel very often 😀. Most of what I do is built on the back of others' work. When I use tooling, I'm reading the documentation and using it as my basis to get work done. When I use my notes and blog posts as a reference, I'm using my informal version of knowledge gathering.

INVEST in documenting your work as you go, for the person behind you. You don't find time to do it, you make time to do it while you work, as a first class citizen of your work, not an after-thought. Think of all the times you've had to dig for answers and save someone else that experience.

You code and document not as much for yourself, but for the person that comes behind you.

Asciidoctor

I've found a happy solution in the Asciidoctor documentation format over markdown. You can go google this for more expanded understanding, but I've decided that other than for basic notes and blog posts which are very simplistic, I now choose Asciidoctor.

Why use Asciidoc format over markdown comes down to the needs of technical documentation.

Here are some key reasons why I've found Asciidoc format to be worth learning:

  • I can reference code files with a simple include::file[] statement, while markdown would require me to embed my code directly as a code block.
  • I can generate a table from a csv file, further helping me automate a refresh of the underlying data that is rendered to a table display
  • I can create tables much more cleanly and with control than in markdown, even allowing nested tables for complicated process documentation.
  • Automatic admonition callouts without extensions using simple statements like IMPORTANT: foo

Presentation

Since the common documentation system used where I am at is Confluence, I decided to leverage the incredible confluence-publisher project that made this entire process a breeze. Check the repo and the linked documentation out here: Confluence Publisher

In the future, if I didn't use confluence, I'd explore rendering as a static website through Hugo (that's what this site is generated from) or revisit Antora and maybe merge my content into the format required by Atora programmatically.

Use Docker

Since Asciidoc is written in Ruby, use docker and you won't have to deal with dependency nightmares, especially on Windows.

$RepoDirectoryName = 'taco-ops-docs'
echo "🌮🌮🌮🌮🌮🌮🌮🌮🌮🌮🌮🌮🌮"
echo "Running confluence publisher 🌮"
echo "📃 Publishing $RepoDirectoryName repo contents"

docker run --rm -v $BUILD_SOURCESDIRECTORY/$RepoDirectoryName/docs:/var/asciidoc-root-folder -e ROOT_CONFLUENCE_URL=$ROOT_CONFLUENCE_URL \
-e SKIP_SSL_VERIFICATION=false \
-e USERNAME=$USERNAME \
-e PASSWORD=$PASSWORD \
-e SPACE_KEY=$SPACE_KEY \
-e ANCESTOR_ID=$ANCESTOR_ID \
-e PUBLISHING_STRATEGY=$PUBLISHING_STRATEGY \
confluencepublisher/confluence-publisher:0.0.0-SNAPSHOT
echo "📃 Publishing $RepoDirectoryName repo contents finished"

Yes... I know. I get bored reading log messages when debugging so my new year premise was to add some emoji for variety. Don't judge. 😁

Distributed Docs Structure

So the above approach is fantastic for a single repo. I wanted to take it to a different level by solving this problem for distributed documentation. By distributed I meant that instead of containing all the documentation in a single "wiki" style repo, I wanted to grab documentation from the repositories I choose and render it. This would allow the documentation related to being contained in the repository it is related to.

For instance, what if I wanted to render the documentation in the following structure:

** General Documentation**
taco-ops-runbook
---> building-tacos
--------> topic.adoc
---> eating-tacos
--------> topic.adoc
---> taco-policies
--------> topic.adoc
---> taco-as-code
--------> topic.adoc

** Repo Oriented Documentation**
github-repos
---> taco-migration
--------> category-1
------------> topic.adoc
------------> topic.adoc
--------> category-2
------------> topic.adoc
------------> topic.adoc
---> taco-monitoring
--------> category-1
------------> topic.adoc
------------> topic.adoc
--------> category-2
------------> topic.adoc
------------> topic.adoc

The only current solution found was Antora. Antora is very promising and great for more disciplined documentation approaches by software development teams. The limitation I faced was complexity and rigidity in structure. For Antora to generate a beautiful documentation site, you have to ensure the documentation is structured in a much more complex format. For example, the docs might be under docs/modules/ROOT/pages/doc.adoc and have a nav.adoc file as well. While this promises a solid solution, retrofitting or expecting adoption might be tricky if your team has never even done markdown.

Azure DevOps Pipeline

I ended using an Azure DevOps pipeline (YAML of course 🤘) that provides a nice easy way to get this done.

First, for proper linking, you should follow the directions Azure DevOps gives on the creation of a Github Service Connection which uses OAUTH. This will ensure your setup isn't brittle and using your access token.

{{< gist sheldonhull "053cb176d5c2847a4e323f01207acb82" >}}

Things to Know

  • Ensure you use the format shown here for documentation to render in confluence correctly. You need to have the names match in the doc/folder for it to know to render the child pages.
** Repo Oriented Documentation**
taco-ops-repo
README.adoc  -- optional, but I always include for landing page, and point to the docs folder using link:./docs/myrepo.adoc
---> [docs]
------> [resources]  -- optional, but keeps the scripts organized and consistent, or any images
------> process.adoc
------> another-process.adoc
---> taco-ops-repo.adoc
  • Include your scripts by using include::./resources/_myscript.ps1[]. You may have to test that relative path issue if doing multiple repos.
  • Ensure your non-asciidoc contents are prefaced with an underscore in the title name. I don't like this, but it's a requirement from confluence-publisher. This ensures it won't try to render as a page.
  • Anything in the target directory (ancestor) gets purged in the process. I recommend a dedicated confluence space you create just for this to minimize risk and disable manual edits.

{{< admonition type="info" title="Docker Commands in Microsoft-Hosted Agent" >}} I didn't expect docker commands to work in Azure DevOps agent, thinking nested virtualization would not work and all. However, it works beautifully. Consider using Azure DevOps yaml pipelines for running your docker commands and you take one step towards better build processes. {{< /admonition >}}

  • 5 min read

Running SQL Server in Docker

{{< admonition type="info" title="Updated 2020-05-05" >}}

I've had lots of challenges in getting docker for sql-server working because I've wanted to ensure for my dev use case that there was no need for virtual volume management and copying files into and out of this. Instead, I've wanted to bind to local windows paths and have it drop all the mdf/ldf right there, so even on container destruction everything is good to go.

After working through the changes in SQL 2019 that require running as non-root, I've gotten it work again. No install of sql-server needed. Easy disposable development instance through docker! I'll update my docker compose content when I can, but in the meantime, this should get you running even more quickly with SQL Server 2019.

docker run `
    --name SQL19 `
    -p 1433:1433 `
    -e "ACCEPT_EULA=Y" `
    -e "MSSQL_SA_PASSWORD=ThisIsNotARealPassword@!1}" `
    -v C:\mssql\SQL19:/sql `
    -d mcr.microsoft.com/mssql/server:2019-latest

docker run `
    --name SQL19WithSpaces `
    -p 1434:1433 `
    -e "ACCEPT_EULA=Y" `
    -e "MSSQL_SA_PASSWORD=ThisIsNotARealPassword@!1}" `
    -v C:\mssql\SQL19WithSpaces:/sql `
    -d mcr.microsoft.com/mssql/server:2019-latest

{{< /admonition >}}

Why Use Docker for MSSQL

Say you have an onboarding and need to get a new developer up to speed. Have you gone through the setup process for SQL Server on Windows? It's a bit tedious and even with running scripts to install (or in my case I wrapped up with Chocolatey), it's still a lot of things you need, including possibly a reboot if missing some .net dependencies.

The normal solution is to leverage more localdb for quickly deploying and isolating the database.

This has its own set of challenges that personally I like to avoid. Localdb is more isolated, but it's not truly like running a local SQL Server standard edition, and debugging permissions, sharing, and other things can with its own set of steps to work through. I've seen it common for many devs to just avoid these issues and run a local SQL Server installation to just simplify and work with SQL Server as it's expected to be.

I'd explored Docker SQL Server containers in the past, but one big issue for adoption to me was the issues I had mounting the local Windows folders to the Linux based container. ❓ Why is this important? Ease of usage for a developer. If I proposed this would save effort to many developers working with SQL Server, I'm pretty sure telling them that they'd have to copy a backup or database file in via CLI to a virtual mounted drive that they can't easily manage would be a deal-breaker. I basically wanted to ensure if they spun up SQL Server in a container, the database backups, mdf and ldf files, and any other created files would be able to persist outside of that instance, ensuring easy development workflow.

I'm happy to say I finally have it working to my satisfaction, resolving most of those concerns.

Scenarios This is Good For

If you've desired to do the following, then using Docker might end up saving you some effort.

  • simplify the setup of a new SQL Server instance
  • be able to reset your SQL Server instance to allow testing some setup in isolation
  • be able to switch SQL Server editions to match a new requirement
  • be able to upgrade or patch to a later version of SQL Server with minimal effort

This is not for production. There's more effort and debate that goes on to using SQL Server in containers, Kubernetes (even more complex!), and other containerization approaches that are far outside the scope of what I'm looking at. This is first and foremost focused on ensuring a development workflow that reduces complexity for a developer and increases the ease at which SQL Server testing can be implemented by making the SQL Server instance easily disposed of and recreated.

If this also means I spread some 🐧 Linux magic to some Windows stack developers... then count it as a sneaky win. 😏

SQL Server on Linux or Windows containers

The following example is done with SQL Server on Linux. As I'm already comfortable with SQL Server on Windows, I wanted to be try this on Linux based container. I also wanted to continue using Linux based containers for tooling, and not have to switch back to Windows containers for the sole purpose of running SQL Server. At the time I began this testing, I found it was exclusive. You either ran Linux or Windows-based containers. This is changing with the advent of new features in Docker that are there to allow side by side Windows + Linux based containers.

Release notes indicate:

Experimental feature: LCOW containers can now be run next to Windows containers (on Windows RS3 build 16299 and later). Use --platform=linux in Windows container mode to run Linux Containers On Windows. Note that LCOW is experimental; it requires the daemon experimental option. Docker Community Edition 18.03.0-ce-win59 2018-03-26

The main difference in your local development process will be Windows Authentication vs SQL Authentication. Use SQL Authentication with Linux based SQL Server

Docker Compose Example

The following is the result of a lot of trial and error over a year. Thanks to Shawn Melton 👏 also for providing me with support doing my troubleshooting as part of the SQL Server community in Slack. You can find Shawn's example I used as a starting point for my 🧪testing in this gist.

A few helpful tips:

  1. Remove -d for detached and you can see the SQL Server console output in the console.
  2. See the persisted databases (system and user!) in the artifacts directory after docker-compose begins running.

{{< gist sheldonhull a70a3a731b329b67f47a331c64c72ab5 >}}

Improving Code Tests

Another reason I'm really excited about is the ability to better support testing through tools like Pester. Yes, it's a geek thing, but I love a solid written test 🧪that maintains my code and helps me ensure quality with changes. Better TDD is a goal of mine for sure.

🔨 This supports implementation of better tests by providing the potential for spinning up a local SQL Instance, restoring a test database, and running a sequence of actions against it with pass or fail without the additional infrastructure requirements to have this done on another server. Making your tests that are not full integration testing as minimally dependent on external factors is a fantastic step to saving you a lot of work.

A simple pester (PowerShell) might frame the start of a test like this:

Before All {
    docker-compose up -d
    Import-Module Dbatools
    # Wait Until dbatools confirms connectivity through something like test-dbaconnection, then proceed with tests
    # Test-DbaConnection ....
    # Restore-DbaDatabase ...
}

After All {
    docker-compose down --volume
}

Describe "DescribeName" {
    Context "ContextName" {
        It "ItName" {
            Assertion
        }
    }
}

Wrap Up

Hope this helps someone. I spent at least a year coming back over time to this hoping to actually get it working in a way that felt like a first-class citizen and reduced complexity for development work.

I'm pretty happy with the results. 😁

  • 7 min read

Getting Started With Python For Data Exploration

Intro

To get started with running python queries with SQL Server is actually pretty easy. I waited far too long to tackle this, thinking it was much harder than it turned out to be. Hopefully, this can be a good starting point for you as well.

Why would I want to do this?

When you use great tools like dbatools with PowerShell, you come to think that PowerShell is a first class citizen for working with SQL Server. This is true in regards to administration and general usage. However, data exploration is a different beast.

PowerShell & .NET Are Not Designed for Adhoc Data Exploration

First, can they? Yes. But just because there is a library someone created to give you some of these features, are the languages themselves first class data exploration languages, especially for adhoc and exploratory work. I think most would agree: no.

To be very clear, I know there are possibilities, such as the newer Azure Data Studio powershell notebook feature, and likely other modules that can help with exploration. What I'm trying to be clear about is that those are all exceptions to the rule, whereas exploration in Python is a first class citizen from the start. (coming from a dedicated PowerShell addict 😁)

Traditionally, I've used Azure Data Studio or SQL Management Studio and run individually queries in a large sql script to explore and manipulate the data. However, once you start wanting to pivot, aggregate, sum, and do various other experimental actions, T-SQL can be over complicated to do this for that adhoc work. Just because you can do unpivot doesn't mean it's a smarter use of time than using a pivot table in Excel for adhoc work (as much as it pains me to say).

{{< admonition type="info" title="Azure Data Studio" >}}

Azure data studio is making great progress with SQL Server based notebooks, which I highly recommend checking out. It's still in its infancy. So while it's pretty useful for basics, if you want to stick with a scripted approach to explore, python will be superior. Just recently PowerShell based code cells are available too, I believe.

{{< /admonition >}}

However, even better than playing around with data in an Excel sheet, what if you wanted to stay in your programmatic script and start slicing and dicing the data coming back, molding into "tidy data" for what you want to analyze?

PowerShell and .NET don't have the intuitive and experimental data exploration capabilities that have existed for a long time in the Python libraries. They can easily manipulate, group, mold and visualize the data with minimal effort. This is the forte of Python's libraries, and has much more maturity than doing some homegrown solution work around.

Jupyter Notebooks

I'm sticking with Visual Studio Code, so install the python extension ms-python.python and then create a new notebook directly from the command pallet. Once you open this up you can start writing some markdown notes and then running queries and visualizing the results in one place.

Python Plotly Library

The standard libraries are pretty good. I wanted something that also created graphs that I'd consider pleasing to the eye. The default matplotlib wasn't really what I was looking for, so I used plotly. This fantastic library has an express edition that gets you up and running in minutes playing with data, and can even convert your visualizations into a nice web app dashboard with Dash (also a plotly product).

Pandas

In addition to working with plotly I leveraged pandas. I think from my initial working with it, that this library will be a staple for my adhoc data work for a long time. It basically provides a nice easy way to work with Dataframes, which is kinda of like working with a .NET datatable, albeit, many extensibility, exporting, and other features built into it. I found it intuitive and pretty easy to work with.

Installing pyodbc

{{< admonition type="info" title="platform" >}}

This guide is written for someone using a Windows based development environment. The setup requirements for running in Linux/Mac will be different for drivers

For running in AWS Lambda in python runtime, there are more complex issues requiring the driver to be compiled and uploaded to a Lambda layer for use. Windows dev machine doesn't have this requirement and should just work after running ODBC driver install and pyodbc. 🎉

{{< /admonition >}}

  1. Link for SQL Server ODBC Drivers
  2. pip3 install pyodbc

Generate Connection File

If you are constantly changing the SQL Server instance you are connecting to, then you may just want provide variables for your script. In my test case, I wanted to create a default connection file that I could use in several scripts to avoid having to set in each module file.

I found some great examples online and cobbled this together from several.

import configparser
import keyring
import xlsxwriter

service_account = "DomainOfTheTaco\\TacoEater"
sql_login_name = "TACO-HIGH-PRIEST"
sql_instance = "TACO-SERVER-01"

driver_name = ''
driver_names = [x for x in pyodbc.drivers() if x.endswith(' for SQL Server')]
print("The following ODBC drivers were identified on your system")
print(driver_names)

if driver_names:
    driver_name = driver_names[0]
if driver_name:
    config = configparser.ConfigParser()
    config['SQL_SERVER'] = {
                            'DRIVER': driver_name,
                            'SERVER': sql_instance,
                            'DATABASE': "master",
                            'Trusted_Connection': 'yes',
                            "APP": "PythonTamer",
                            "UID": sql_login_name,
                            "PWD": keyring.get_password(service_account,service_account)
    }
    with open('db_connect.ini', 'w') as configfile:
        config.write(configfile)
else:
    print('(No suitable driver found. Cannot connect.)')

Since I had 2 versions of ODBC drivers available, this is what came up:

The following ODBC drivers were identified on your system
['ODBC Driver 13 for SQL Server', 'ODBC Driver 17 for SQL Server']

This generated a connection file I could use in other py files I wanted. The file generated in my user directory $ENV:USERPROFILE.

[SQL_SERVER]
driver = ODBC Driver 17 for SQL Server
server = TACO-SERVER-01
database = master
trusted_connection = yes
app = PythonTamer
uid = TACO-HIGH-PRIEST
pwd = redactedtacos

This could then be used in python files like below

import configparser
import pyodbc
import pandas

config = configparser.ConfigParser()
config.read('db_connect.ini')
constr = 'DRIVER={{{drv}}};SERVER={srv};DATABASE={db};Trusted_Connection={tc};APP={app};UID={uid};PWD={pwd}'\
              .format(drv=config['SQL_SERVER']['DRIVER'],
                      srv=config['SQL_SERVER']['SERVER'],
                      db=config['SQL_SERVER']['DATABASE'],
                      tc=config['SQL_SERVER']['Trusted_Connection'],
                      app=config['SQL_SERVER']['APP'],
                      uid=config['SQL_SERVER']['UID'],
                      pwd=config['SQL_SERVER']['PWD']
                      )

cnxn = pyodbc.connect(constr)

This connection object then would allow running queries similar to executing ADO.NET queries.

out_xlsx = "C://temp//python-sql-export.xlsx"
cnxn = pyodbc.connect(constr)
result = pd.read_sql("""
select
    name
from
    sys.databases
""",cnxn)
print(result)

print(f"Exporting to {out_xlsx}")
data_frame = pd.DataFrame(result)
data_frame.to_excel(out_xlsx, index = None, header=True)
print("finished query")

To sample in your notebook a few random entries, try this

data_frame.sample(n=20,random_state=1)

You should have an excel workbook exported after this. This was even more succinct than using ImportExcel PowerShell module which is my normal go to.

Group Results Into Ranges

import plotly.express as px

# How many results are being returned?
print(len(data_frame.value))

# These are the buckets I want to return results in
# Ie, how many of my results fit into the 0-250 range, and how many 500. These are not fixed ranges.
# Histograms do this type of work as well, I'm showing the grouping overriden at specific ranges with a dataframe in Pandas.

ranges = [0,250,500,1000,2000,3000,4000,5000,6000,7000,10000]
groupby = data_frame.groupby(pd.cut(data_frame.value, ranges)).count()
print(groupby)

# Plotly express visualization
fig = px.histogram(data_frame, x="time", y="value",title='MyFancyTitle')
fig.show()

               time  metric  value
value
(0, 250]       1652    1652   1652
(250, 500]       22      22     22
(500, 1000]      10      10     10
(1000, 2000]      9       9      9
(2000, 3000]      2       2      2
(3000, 4000]      1       1      1
(4000, 5000]      5       5      5
(5000, 6000]      9       9      9
(6000, 7000]      2       2      2
(7000, 10000]     1       1      1

This is pretty cool, as while you can do this with windowing functions in SQL Server, for adhoc work, I was able to do this in 2 lines. This could save a lot of time and debugging over more complex T-SQL procs.

Scatterchart

Never having really used scattercharts, I'm seeing in data exploration they can be a tremendously valuable tool.

print(len(data_frame.value))
fig = px.scatter(data_frame, x="metric", y="value",title='MyFancyTitle',size="value",render_mode="auto",marginal_y="rug",marginal_x="histogram")
fig.show()

Quick Example

pip3 install pydataset #optional for more than iris data

from pydataset import data
titanic = data('titanic')
print(titanic.sample(n=20,random_state=1))

This results in output like the image below

Notebook Output Sample for Titanic

To generate a more advanced visualization, you can run something to visualize with plotly.

import pandas
import plotly.express as px
from pydataset import data

df = data('iris')
df.sample(n=10,random_state=1)

print(len(df))
fig = px.scatter(df, x="Petal.Width", y="Sepal.Width",title='Iris Data Example',render_mode="auto",marginal_y="rug",marginal_x="histogram")
fig.show()

Scatter Chart

Wrap Up

Lots of great options to visualize data with Python 😀 . I'll definitely be spending more time learning how to manipulate and query datasets with Python, and eventually give Dash by Plotly a try as well. I've enjoyed Grafana, but it's really meant for time series monitoring, not for the flexible data visualization options that can be done so easily in Python.

Hopefully, this gave you a good start on some of the benefits and ease of use of Python if you, like me, are coming from a PowerShell and SQL Server heavy background, and have wanted to know more about Python. 🌮

  • 3 min read

Improving the Quality of Your Automation Logging with Cowsay

Automation Taxes Your Sanity

You have to glue together systems with your amazing duct taped scripts.

You see failure after failure.

You want help predicting the success of your next run, so I'm going to provide you with an advanced artificially intelligent way to do this through the power of npm install.

NPM

npm install cowsay -g
npm install lucky -g
npm install catme -g

 _____________________________________
< Will my run succeed this time? Hmmm >
        \    ,-^-.
         \   !oYo!
          \ /./=\.\______
               ##        )\/\
                ||-----w||
                ||      ||

               Cowth Vader

 ________________________________________
< Will my run succeed this time? No way! >
 ----------------------------------------
   \
    \
     \
        __ \ / __
       /  \ | /  \
           \|/
       _.---v---.,_
      /            \  /\__/\
     /              \ \_  _/
     |__ @           |_/ /
      _/                /
      \       \__,     /
   ~~~~\~~~~~~~~~~~~~~`~~~

Now include the header in your script

"Will my run succeed this time? $( lucky --eightball)" | cowsay -r

Or spice up your console with a friendly cat using catme

 /\     /\
{  `---'  }
{  O   O  }
~~>  V  <~~
 \  \|/  /
  `-----'__
  /     \  `^\_
 {       }\ |\_\_   W
 |  \_/  |/ /  \_\_( )
  \__/  /(_E     \__/
    (  /
     MM

  /\ ___ /\
 (  o   o  )
  \  >#<  /
  /       \
 /         \       ^
|           |     //
 \         /    //
  ///  ///   --

There's a few PowerShell related one's, but I honestly just use other packages for this this.

Python

I just looked and found out there's a few great python equivalents so you could easily run some great stuff. They've got cowsay, a benedict cumberbatch like name generator, and more. I think I fell in love with Python a little bit more today.

Level Up

Level it up by installing lolcat and if running Cmder you'll enjoy the Skittlitizing of your console output.

PowerShell version is: Install-Module lolcat -Scope CurrentUser

"Will my run succeed this time? $( lucky --eightball)" | cowsay -r | lolcat

The resulting majesty:

CLI rendered dragon with lolcat

Linux Powerup

If you want to really wow your friends... just jump into bash inside windows and run from your cli. This is a level so far beyond the normal windows command line experience it might just make you uninstall windows and live purely on a Linux terminal for your remaining days.

This looks like a good background while waiting to start presentations 🚀

# first time if repos out of date
sudo apt update
sudo apt install cmatrix

cmatrix

Resources

{{< admonition type="warning" title="disclaimer" >}}

⚠ There are a couple ascii layouts that are a little inappropriate, so if doing a live demo or something more visible don't use random mode if you don't want something showing up that might embarass you 😁

{{< /admonition >}}

  • 7 min read

Getting Started With Terraform

{{< admonition type="info" title="Updated 2020-07" >}} - Added comments about brand new Terraform users ignoring Terraform Cloud for first time tests. - Added comment about pulling credentials using data source instead of environment variables for AWS as a more advanced option to consider in the future. - Replaced details on creating terraform credential file with the new tf login command {{< /admonition >}}

Getting started with using Terraform for infrastructure can be a bit daunting if you've not dived into this stuff before. I put this together as a write up for those looking to get their feet wet and have a better idea of where to go for getting some momentum in starting. There are some assumptions in this, such as basic familiarity with git for source control automation, basic command line usage, and basic cloud familiarity.

If time permits, I plan on writing up some more detailed walk through in future posts on Terraform iteration methods, object types, dynamic data inputs, and other things I've explored. However, what I've found is just getting the initial start seems to be a blocker for many people interested in trying it. Hopefully, this will give someone a head start on getting a basic plan going so they can understand how this works a little better and the other more detailed tutorials that abound will make more sense then. Give this post a clap or leave a comment if it helps you or you have any feedback. Cheers! :cheers:

Purpose of This Post

{{< admonition type="info" title="Using Terraform Cloud 2020-07" >}} If you are brand new to Terraform, then consider ignoring the "backend" section. This will have all the artifacts that Terraform produces (such as the state file) just sit in your local directory. In retrospect, Terraform Cloud intermixed with getting up and running as a new user might add more complication than required. {{< /admonition >}}

In technical documentation, there is a difference between a tutorial and a getting started. The getting started here is going to focus just on getting up and running, not on all the concepts about infrastructure as code. I found that just doing it the first time was the hardest thing. Terminology about modules and re-usability at the beginning of my efforts with Terraform went straight over my head as I couldn't fully wrap my understanding around how it would work. Now that I've gotten a lot more experience with Terraform for various projects, I've got some personal "best-practices" that I've found as well as insight from the community.

That's for another day 😁

Let's just make sure you can get up and running with a basic deployment Terraform deployment from the scratch.

I had minimal Cloudformation authoring experience, so this was new stuff to me at the time.

What about Cloudformation?

More knowledgeable people than me have written about this. I'll just say these personal subjective observations:

  1. Terraform is recognized for being a great tool in the industry, it's not some "indie open source project about to fail". Hashicorp has some serious vision.
  2. Just because you aren't going "cross provider" with Azure and AWS doesn't rule out Terraform. You aren't necessarily gaining anything special by "sticking with native" AWS CF, like you might think.
  3. Terraform's much more succinct, less prone to whitespace/indentation failures.
  4. IMO re-usability of Terraform provides itself to a better team collaborative experience.
  5. Terraform's preview of changes is more intuitive to me. Less nervous to deploy stuff.
  6. I just like HCL (Hashicorps DSL) better than writing YAML docs.
  7. If you are writing YAML without any generator... just why!

Resources

Links Description
Terraform Documentation Reference Terraform Documentation for CLI
Terraform Documentation For AWS Terraform AWS Provider Documentation

Setup

Installation and setup

Install chocolatey via command prompt as administrator

@"%SystemRoot%\System32\WindowsPowerShell\v1.0\powershell.exe" -NoProfile -InputFormat None -ExecutionPolicy Bypass -Command "iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))" && SET "PATH=%PATH%;%ALLUSERSPROFILE%\chocolatey\bin"

For macOS 

brew cask install terraform

Terraform Cloud Setup

{{< admonition type="warning" title="Subscription" >}} This will require a Terraform Cloud account. At the time of this post they have a plan for around 5 users for free with a few features turned off. {{< /admonition >}}

Setup your Terraform App Account and make sure to enable 2FA.

Once you've been added, create a personal access token at your user settings (this is personal, not team based)

If you are using Terraform Cloud, run tf login to generate your local credential file to allow connecting to Terraform Cloud and easily using the registry and remote state workspaces provided.

Creating Your First Project

Create main.tf. It will be the first file you create.

Create provider.tf

{{< gist sheldonhull 95c3f9533b2111d7d9fa40ff90a917e3 "provider.tf" >}}

Create terraform.auto.tfvars

Note that if you try to create this file with the terraform.tfvars name, it won't work if using Terraform Cloud, as tfvars get generated dynamically from the variables setup in the Cloud workspace.

{{< gist sheldonhull 95c3f9533b2111d7d9fa40ff90a917e3 "terraform.auto.tfvars" >}}

Create variables.tf which is going to house all the input variables we want to declare.

{{< gist sheldonhull 95c3f9533b2111d7d9fa40ff90a917e3 "variables.tf" >}}

Create iam.tf which will provide a nice low risk resource to create that will show you how to use string interpolation for dynamic names in the most simple way, as well as the way to leverage EOT syntax to easily escape mutliline strings. However, if you see yourself doing this constantly, you might reevaluate your approach to ensure you are using objects and properties as much as possible and not just strings.

{{< gist sheldonhull 95c3f9533b2111d7d9fa40ff90a917e3 "iam.tf" >}}

{{< admonition type="info" title="HCL Multiline String Syntax" >}} If you use <<-EOT you get a nice little benefit that's not well documented. The - means it strings buffering whitespace for the following lines. This can allow you to keep your content indented and if you preface the first line with 6 spaces, then all the following lines trim 6 spaces as well to allow you to avoid a bunch of difficult to read string blocks. {{< /admonition >}}

You'll likely want to use a workspace with Terraform to organize this work, so instead of using the default, use the command

terraform workspace new qa

Terraform should select this new workspace by default. You can list the current workspaces using terraform workspace list and then select another one later if you wish by running terraform workspace select qa.

{{< admonition type="warning" title="Terraform Workspace Naming" >}} Personally, I'd recommend to not drive much of your naming or other configuration based on the workspace name, and instead use variables.

Terraform Cloud behavior with trying to use workspace names at the time of this post was not what I expected, so I ended up removing my dependency on workspace names being important for the configuration. See GitHub Issue

Instead, I use it as metadata only to organize the workspaces, not try to build configuration based heavily on using workspace name. {{< /admonition >}}

Deploying Infrastructure

Deploying is as simple as running terraform apply. You'll get a preview of the plan before apply, and then you can approve it to actually apply.

If You Connected This to Terraform Cloud

This is assuming you are running via Terraform Cloud. To run locally, you'll want to go to the workspace you created in Terraform Cloud and in the General Settings set to run locally instead of remote.

This means you'll be able to run the apply directly on your machine instead of running it from the remote location. Running remote is great, but for this to work you need to edit your Terraform remote cloud workspace and add the AWS access keys, as the job is actually running in the remote context and not using your local machine credentials.

{{< admonition type="info" title="Terraform Cloud Credentials" >}} My preferred solution for this is to setup another Terraform workspace to create the credentials and then call this datasource to provide me with access instead of having to configure environment variables per workspace. This is a more advanced operation and not required on your first go-round, but keep it in mind as you scale up to managing many workspaces later on. {{< /admonition >}}

Connecting your git repository to your Terraform workspace can also be done for automatically planning on commit. This forces changes to come through your git commits instead of being able to run locally, which can be great for ensuring source control truly is the equivalent of your release when working with a team.

Tearing Down Infrastructure

To tear down the infrastructure we just deployed, you can run: terraform destroy and approve the resulting preview it gives you.

If you are using Terraform Cloud, in order to destroy a remote workspace (by queuing the destroy then destroying the workspace fully), you'll need to ensure the environment variable is set in the remote workspace for CONFIRM_DESTROY = 1

Wrap up

Terraform documentation is pretty solid on all the provider resources, so you can normally copy and paste (or use vscode extension mentioned). Another great way to learn is to look at github and the various Terraform modules that have been published. You can see how they structure their code a bit better and learn from that as well.

If you are using Visual Studio Code, also download the Hashicorp Terraform extension for extra support.

Good luck! If any steps were unclear or confusing please put in a comment and I'll do my best to improve this for an initial on-boarding experience for a new Terraform user.

  • 1 min read

2019-09-26T14:10:42+00:00

You know you are a dad when you start looking at a minivan as a really cool vehicle and marvel at all the features that have zilch to do with driving. $35-40k for a glorified station wagon with sliding doors would probably make sane humans question reality, while #dadlife looks at sliding doors as less risk for door ding claims from his little rascals.

You know you have a researching mentality when you begin to investigate minivan prejudices impact in the market against widerspread adoption. #bunnytrail

  • 1 min read

2019-09-26T13:58:58+00:00

You know you probably reserved the wrong hotel when you drive around from the back and think it's a condemned building, only to come around the front and realize that's actually your reservation. Needless to say, that reservation got cancelled.

  • 1 min read

2019-09-26T14:04:40+00:00

I have a new term for the wait my daughter eats food. Instead of please stop wearing food all over your face and eat proper I'm going to label her: "An experiential tactile food lover"