DevOps journeys series – Vertica release pipeline with Azure DevOps – Ep. 02 – build

In a previous post, we’ve described the “from scratch” approach on the development side. When everything works well there, a push (or check-in) triggers the build engine. We must deal with two SQL Server instances (SSIS Servers hereafter), with an environment for each of them:

The build pipeline

The SSIS Servers keep Vertica‘s test and production mappings as well as test and production connection strings for the SQL Server databases. So we need the right variable mapping for all the scenarios, but this is not in the scope of the post, we will speak about it in the next posts. Anyways, here is how the build pipeline works:

Our build process

You may notice that the task “Copy vertica deploy scripts” is disabled. Well, to be honest, right now we’re waiting for the target integration environment.

Build process explained

In the beginning, the build server gets the source files from the repository and creates the target artifacts folder with a Powershell script. This will be the path from which we will push the artifacts to the release pipeline.

The build server generates the .ispac file for the SQL Server Integration Services packages using the dedicated task. The copy tasks will be executed:

As you can see, we’ve got a set of utilities and transformation tools, that will be executed in the release pipeline as well as the environment script. This one contains the SSISDB variables mapping and the SSIS Project configurations statements. Misc files, .sql files for environments and the .ispac file will be copied to the target artifacts folder.

The tasks above copy our template of the .nuspec file to generate the NuGet file (NuGet pack step). This is what we get using NuGet:

Then, we’re ready to publish the files to the release pipeline. We will see how the release pipeline works in the next posts.

Ehm… you miss Vertica

Yes, you’re right. But, it’ll be just a copy of .sql files to the artifacts folder. We will see how the release manager will execute them, so…

Stay tuned!

Reducing the gap between operations and development using Azure DevOps

Intro

As we all know, DevOps is culture. In a company that is going to adopt its practices and principles, everyone should be “on the same side”. Continuous improvement cannot be part of our work in IT if we wouldn’t reduce the gap between development and operations. People like me, that worked in the 90s, know that dev and ops were always isolated in silos and this was “the only” way that everyone followed as a suggestion taken from the market leaders. Ticketing systems and extreme bureaucracy acted as a man-in-the-middle between those silos, transforming each organization in two people-unaware endpoints.

Speaking from a DevOps perspective, in such circumstances, a developer couldn’t know anything about how to deploy, where and how is an environment configured. On the other hand, an operation guy couldn’t get anything from a package in terms of what the application has been made for. Due to this gap we often see performance issues, underestimated hardware stuff and delayed releases. Last but not least, what about the lack of commitment and accountability of the employees working on the solution? In short, reducing such a gap is possible using a combination of DevOps culture and the right tools. We will see hereafter how my organization tries to do so using Azure DevOps.

Scenario

Let’s get started with our team (DevTeam hereafter), which is working with agile methodologies, composed of ten developers and a PO. A quick note, we are using a process decision framework called Disciplined Agile (https://www.disciplinedagileconsortium.org/). Then, we have three operation professionals (OpsTeam hereafter). Build and deploy pipelines already exist. Builds are hosted by Azure DevOps and deploys are managed by Octopus Deploy. Getting this has been a difficult mission.

Everything is related to infrastructure in terms of servers, operative systems, virtual hosts, DNS, firewalls, security and so on, is the responsibility of our OpsTeam. As a result, they do many tasks for managing the environments. Here comes the problem. DevTeams used to create tasks in a dedicated backlog, but OpsTeam didn’t. It’s not just a matter of end-of-pipeline tasks, but also tasks for their daily basis work.

Our solution

Modify the tool, adapting its shape in order to fit in with that real scenario. A piece of cake, when you’re DevOps “inside”. How did we change Azure DevOps? Let’s describe what we did in three parts:

Team on Azure DevOps

To create a team in Azure DevOps is really a piece of cake (according to the latest releases). Just navigate to the options and select Teams:

We can add many teams clicking on New team:

We can set the team’s administrators, the permission set and an area under which every work item will be created. This area will be one of our best friends, especially when we will make our queries for gathering and analyzing the team’s related data. Additionally, also the other teams’ members could create items with this area in order to make the OpsTeam aware of them.

Team’s backlog

Now let’s navigate to Backlogs:

Good! The new backlog has been created. Going on it, we will see the team’s drop-down as well as the one for iterations. Great features!

Once created, we will see the teams’ drop-down:

Work items

Now, let’s create a new work item type. We call it Ops item. Navigate to Process customization page:

Before adding the new work item, we must ensure that the process is already a custom process, otherwise, all the edits will be blocked as shown in the following picture:

We’ve already created a SimplifiedScrum process. let’s add our item now:

Now we are going to modify the fields of the new type. Each team should be able to understand all the item’s properties. We will leave the item as is in this example. Then, we have to map the type to the backlog item list, since only the default work item types are shown initially. To do this, navigate to the Process customization page, Backlog Levels tab:

As we can see, we can also set the default item for our requirements backlog. Moreover, every Sprint backlog, based on iterations, will enable us to add the new Ops item:

Wrapping up

So, we’ve got a backlog for the IT Operations team’s members. Then, we’ve related it to their Azure DevOps team. Additionally, we’ve got a particular work item type (not mandatory, but really useful for querying it or adding it into dashboards) target of IT Operations’ job and a dedicated Area Path. We can make many relationships between items of both our backlogs. Here is an example of how an activity can be managed and organized (extension: Work Item Visualize):

As you can see, the Ops items are Successor of the “development” Product backlog items. Indeed, the Ops Items will be finished after the PBI, typically. Think about creating s DNS or a network path to let the production app work in production.

Conclusions

With this solution, we’re decoupling backlogs. Moreover, we’re isolating the management maintaining the relationships between work items that reside on different boards. At the same time, we’re making a strong synergy between Development and Operations.Then, in a couple of clicks, we can switch teams and backlogs, using Azure DevOps Boards. We can track changes in both the departments, also for audit requirements. In my opinion, this helps the enterprise awareness and facilitates the continuous improvement of all the teams and any member’s skill.

DevOps journeys series – Vertica release pipeline with Azure DevOps – Ep. 01 – development (part 2)

Intro

In the previous post we’ve described the idea behind the automation we’re trying to implement on a scenario based on MicroFocus Vertica database.

How it works

This “sandbox” is not a real isolated development workstation. Let’s separate it into two parts, the first one for the development on everything but Vertica (Windows local workstation) and the other one for a Vertica instance (probably Unix/Linux VM) shared between developers.

In this shared instance we will get a schema for each developer is working on the solution, in order to let everyone to get his own “environment”.

The source control folder tree (which will be TFVC source control on-premises) will be designed on the desired branch as the following:

/Project
    /Instance
        /Process1
            /_Master
                schema.ps1 
                tables.ps1
                views.ps1
            /Tables
                Table1.sql
                Table2.sql
            /Views
                View1.sql
                View2.sql
            Schema.sql
        /Process2
            /Tables ...
            /Views ...

As you ca see, under the Project folder there is the Vertica database folder, which contains, schema by schema, all the .sql files for Tables and Views DDLs (CREATE TABLE and CREATE VIEW). You can notice also .ps1 files, which contains the list of executions based on a certain order (business driven).

The file for a, let’s say, “Table1”, can be like this one:

CREATE TABLE :SCHEMA.Table1
(
    RowId int NOT NULL,
    RowStringValue varchar(30) NULL,
    CONSTRAINT PK_<schema>Table1 PRIMARY KEY (RowId)
);

We’ve added a :SCHEMA parameter, which allows each developer to create its own schema as described before. This is the only way we’ve found for isolating developers in a Vertica shared instance, avoiding an instance for each developer, which could be resource intensive for available PCs. Running the application locally, before committing any change set to the Source Control, a simple tool will execute .sql files with the new schema name and in the sort order given by the .ps1 file.

The Tables.ps1 file can be as the following:

param(
[parameter(Mandatory=$true)]$hostname,
[parameter(Mandatory=$true)]$port,
[parameter(Mandatory=$true)]$user,
[parameter(Mandatory=$true)]$psw,
[parameter(Mandatory=$true)]$schemaName,  
[parameter(Mandatory=$true)]$scriptsFolder
)

$schemaCommand = "vsql -h $hostname -p $port -U $user -w $psw -v SCHEMA=$schemaName -f $(Join-Path $scriptsFolder "Table1.sql")"
Invoke-Expression -command '$schemaCommand'

$schemaCommand = "vsql -h $hostname -p $port -U $user -w $psw -v SCHEMA=$schemaName -f $(Join-Path $scriptsFolder "Table2.sql")"
Invoke-Expression -command '$schemaCommand'

You may notice the term “vsql”, which is the command line provided by Vertica for executing queries. Further information here.

Also, usernames and passwords will be stored in an external config file (or a secured API), like the following one:

{
"host": "MyHost.Hos.Ext",
"port": 1234,
"user": "user",
"psw": "password",
"schemaName": "MYSCHEMA"
}

We’ve got the DDLs, the PoSh files for executing them and the Vertica command line. Good, in a development environment, however, a set of tools should be prepared for helping us to keep these artifacts on a single pipeline, too. This is the reason why we’ve created a “builder” script, like  this one:

$config = Get-Content (Join-Path $currentFolder "Build-Config.json") | Out-String | ConvertFrom-Json

$schemaCommand = $(Join-Path $scriptsFolder "Tables.ps1") 
$schemaCommand += " -hostname $($config.host)" 
$schemaCommand += " -user $($config.user)"
$schemaCommand += " -port $($config.port)"
$schemaCommand += " -psw '$($config.psw)'"
$schemaCommand += " -schemaName $($config.schemaName)"
$schemaCommand += " -scriptsFolder $scriptsFolder"

Invoke-Expression -command $schemaCommand

This is another layer of management, which allows us to organize every part of the DDLs to be executed against Vertica.

Note: Our scripts will destroy and rebuild any given SCHEMA. But this is the way we like.

Now, let’s see the possible scenarios.

Start from scratch or get started

When someone wants to start from scratch, this is the pipeline to follow:

  1. get latest version of the branch;
  2. check and change the configuration file (JSON);
  3. execute the create-vertica-database-from-scratch.bat file (it contains our powershell “build” script);
  4. that’s it, we’ve got a new schema in Vertica, empty and ready to be consumed.

If you want to preserve your data, this is not the right path for you. Executing the “builder” tool is optional.

New development

When a developer would make and try its changeset:

  1. change Visual Studio application (SSIS or SSRS here) when needed;
  2. change the Vertica schema (adding tables, columns and so on);
  3. get the .sql file of a new object or change the .sql file of an object which has been updated;
  4. replace them into the TFVC file structure;
  5. change the .ps1/.txt files if any DDL has been added (or if something that impacts on the order occurs);
  6. build the Visual Studio application and try it;
  7. When everything works good, check-in.

Now, everyone can get the latest changes in a CI way.

Get delta changes

When a developer is going to get the latest changes that contains an updated version of the Vertica objects, but wants to preserve its data, this is a little bit more tricky. The person who has made the change could share in a collaborative chat tool the ALTER script. This is not so reliable and comfortable, but without any comparison tool, there isn’t any best way to make this happen.

That being said, we’ve implemented our diff-script generator, based on the analysis of Vertica metadata (the catalog, browsing v_internal objects). So, after our friend gets the latest version, he executes a generate-diff-script.bat tool and lets this script to execute the generated ALTER script. Tricky, but it works like a charm (we will speak about this comparison tool in next posts, maybe) . Anyway, we’re looking forward hearing updates from MicroFocus. Hopefully, we’ll get an official diff tool from them soon!

Conclusions

I’ve just shown the way we’re managing tables DDLs and how we’ve created PowerShell scripts, but the real scenario is more complex. We have to drop Vertica schema (CASCADE DROP) before, then re-creating the new parametrized schema, then tables, then views and so on. Sometimes, we’ve got Vertica schemas which are referenced each other, so we have to create for everyone of them tables before, then views. The business logic is managed by how we write the “build” PowerShell script as well as the automated build process will.

Also the build process is not always “straight”. Some of the business processes need to be managed in a dedicated way. Cross reference will occur but this is the job that the “builder” will do. Both for the manual and the automated one. Moving responsibility to the build process allows us to feel more comfortable about our development solution.

Last, but not least, the manual-development-build process allows the developer to choose between re-create the database or not. They should not waste time in managing the things that a script can do repeatedly and efficiently. This is the reason why we kept somehow static the PowerShell instead of writing complicated business logic. Just adding rows of vsql invocation in the right order, that’s it.

Two tech events in Parma, the city of food

SQL Saturday Parma, six years in a row. DevOpsHeroes, four. Parma has been a great place to reach, also for technical events. I’ve started organizing the first SQL Saturday in my birthplace in November 2014. After two years I tried to create a brand-new event, when the DevOps culture started to grow and when the agile became strong. DevOpsHeroes was born in 2016, a month before the SQL Saturday event, again, in Parma. Why change? The audience has been great (more than 200 attendees), the feedbacks, too. People who come here look comfortable with everything. Then, thanks to the University, which has been the selected location, both the events are still growing.

Let’s go in deep with the events.

SQL Saturday Parma (2014-now)

SQL Saturdays, a great format by Professional Association of SQL Server (PASS), is a well-known event all around the world. You can find hundreds of them here. In Parma, the event is completely free, with no pre-conference. It’s located on the Campus of the University of Parma, in order to make also the students as well as the school aware of this kind of events. Unfortunately, the audience doesn’t gather them as does for the professionals, so I’m working to make a better relationship with them, too. About the audience, some numbers:

Event Attendees Track Speaker Feedback
SQL Sat 355 (2014) 128 3 14 4.46/5
SQL Sat 462 (2015) 157 3 18 4.20/5
SQL Sat 566 (2016) 150 3 18 4.48/5
SQL Sat 675 (2017) 210 4 24 4.47/5
SQL Sat 777 (2018) 230 4 24 4.72/5

In 2014 the sessions were driven by, let’s say, classic topics, like DBA, Development, BI. Starting from 2016 the coverage changed a lot. More data visualizations, more automation, more BI in the cloud, more cloud itself. 2017 has been the game-changer about NoSQL sessions (on Microsoft Azure), too. The latest edition of SQL Saturday Parma introduced the AI and this year we are struggling for selecting the right sessions from a bunch of 70 proposals (all over the world). September, 30 the Call for papers (available here) will close and if you are in the area on November 23, or if you want to come in Italy for a weekend of training on Microsoft Data Platform with friends, #sqlfamily and good food you are welcome!

The event is strongly supported by the Italian #sqlfamily, especially my friends in UGISS. A big thanks go to them.

DevOpsHeroes (2016-now)

Started as a one-shot event, this is a four-year-in-a-row one. Riding the wave of enthusiasm derived from the SQL Saturdays in Parma and thanks to my work experience, who moved meanwhile from DBA skills to Data DevOps and automation, this event has been a pleasant surprise, yet it doesn’t gather as many people as SQL Saturday does (SQL Saturdays has got also its noise and the PASS support). The event was born for spreading the DevOps culture, not just the tools. Tools were described there just to pull out the advantages of the culture, which must be “soaked up” before going deeper. So, the event was born for the culture. And this has been (and it still is) one of our mission.

The event is held typically one month before SQL Saturday in the Campus of the University of Parma. It gets more than 120 attendees and this year the organization is expecting more, due to the great sponsors which help the edition.

As you can see, behind the hood there are two main “helpers”. Engage IT Services, which is the company whose I’m a co-founder, and GetLatestVersion.it, a great Italian online community for DevOps and ALM technologies. The event is totally free, and it will get 18 sessions with 3 tracks. The topics will cover Technologies, Methodologies and use cases (or Experience sessions). The call for paper is already closed and we’re finishing the program of that Saturday, October 26.

Wrapping up

SQL Saturday Parma website: https://www.sqlsaturday.com/895/EventHome.aspx

Registration: https://www.sqlsaturday.com/895/registernow.aspx

DevOpsHeroes website: http://www.devops-heroes.net/

Registration: https://www.eventbrite.it/e/biglietti-devopsheroes-2019-66796826105

DevOps journeys series – Vertica release pipeline with Azure DevOps – Ep. 01 – development (part 1)

Intro

As a consultant, life could be difficult when it comes to manage platform like Vertica, a columnar RDBMS by MicroFocus. Speaking about DevOps, database management systems like this one neither is well supported by built-in tools nor by any third party suites.

In my experience, which is focused on the Microsoft SQL Server world, tools like the ones made by Redgate or ApexSQL, plays a crucial role when it comes to DevOps. Unfortunately, this time I cannot find any real help. I think that it’s an exciting task, but I’ve to be careful. It’s like “reinventig the wheel” for a car (Vertica) that didn’t realize that it needs wheels. Strong and reliable wheels.

Scenario

In the scenario I’m working on, the data warehouse is managed by a layer of business logic implemented within this platform. Starting from SQL Server database populated by the application layer, the data rows pass through SQL Server Integration Services ETL packages, which transform them onto Vertica repository, and they end into a Business Objects layer (the presentation layer). Just to get the big picture, see the following diagram:

bi-scenario-scenario

Our mission is to share DevOps knowledge, culture and tools in order to automate a set of processes which are managed manually, right now. Not a simple task, even if the guys I’m cooperating with are well-trained and powerful in tech skills. Additionally, they are enthusiast and ready to change (and we know how this is usually a trouble). We will get obstacles only from a technology perspective, actually.

After days of documentation and questions to our MicroFocus contacts, it looks like that Vertica gives us no built-in way or tool for getting an automated process during the phase of the schema comparison. Also third-party tools aren’t that good. We’re trying to dig more and more in order to find out something on the internet. However, everything it’s a “little bit” tricky. To be honest it seems that no one tried to do DevOps with Vertica, even though this is a very good platform for speeding up heavy queries on huge amount of data. Yes, we downloaded a couple of officially suggested tools, whose documentation says that we can compare from and to Vertica itself, but no one fits our scenario. No command lines, no direct integration, no script generation. If you think about automated pipelines, this can be a big problem to deal with. I think that this should be the foundation of a DevOps approach. In the end, it looks like we’re the the first team that is trying to invest efforts into that. It’s funny, though. Hopefully, someone with experience in Vertica DevOps who reads this post could help us!

(After digging more, I’ve found this article that describes almost the same scenario. I hope to reach out the author out).

Prerequisites

As a kick-off, we’ve moved into the prerequisites, starting from the most important thing: make a process that will facilitate the team’s workday, instead of wasting time due to bad choices. This process should be simple and automated.

Then, we’ve argued about the IDE used by developers, evaluating differences between before and after solutions, getting pro and cons of them all. In the end, we agreed on the following solution, development side:

  • Visual Studio 2017 will be the editor for SQL Server Integration services solutions. This team, “BI team” hereafter, is working on SSIS and is separated from the application development team, at least at the moment;
  • TFVC (Azure DevOps on-premises) will be the Version Control System since the BI team is already using it. We will think about a migration path to git then, but for now we’d like to avoid any distraction out of the scope;
  • every get latest from the source control must synchronize all the DDL scripts for Vertica as well as the SQL Server and SSIS solutions, in order to get a sandbox with source code and the local database provisioning scripts (most likely we’ll get a virtual machine with Unix and a Vertica instance, too);
  • each SSIS project must be deployed to the local SQL Server instance when running solutions into the sandbox;
  • when all the SQL Server Integration Services packages are deployed, the list of Vertica DDL must be executed in order to create the database from scratch;
  • Optionally, mock inserts (also from file) could be added to the database in order to get data to work on.

Our idea

As you can see, in this pipeline, we will get everything we need in our sandbox. Just a note, we can choose how to provision the instance of Vertica, in order to scale out resources from local workstations and also use another O.S. It’s up to the team.

A big picture of this process can be summarized as follows:

001

Conclusions

As we’ve seen, the big picture is ready to be implemented and also the team knows perfectly what are the goals. We already know what technologies will be involved and how to keep them all together in order to make our pipeline.

Unfortunately, we cannot find any tool that helps us, so we’re preparing to jot some lines of code down.

DevOpsHeroes 2018 – Another brick in the wall

Event details

DevOpsHeroes has been a great event, again! We didn’t expect so many people and we could not imagine that the feedback would be so good. Quick facts:

  • Subscription: 244 (150 the past year)
  • Attendees: 122 (94 the past year)
  • drop: 50% (38% the past year)

Attendee’s satisfaction

The following radar chart is about the event date, location, quality of the sessions, quality of the speakers, food, hospitality, event design, and kits:

As we can see, the overall satisfaction is really high (4/5)! The blue line is related to the audience which already has attended our event (40%) and the orange one represents the first-timers (60%).

Indeed, we’ve got a good feedback also for the following questions:

  • will you attend again?
    • Sure, 45%
    • Most likely, 33%
    • Likely, 22%
  • will you suggest the event to other people?
    • Sure, 76%
    • Most likely, 21%
    • Likely, 3%

Conclusions

We’re really proud of the third edition of DevOpsHeroes. Engage IT Services and Xebir did a great job together, and, hopefully, both companies will cooperate in the future in order to provide new formats and events like this one. A special thanks goes to Scott Ambler, and also to Italian Agile Moviment for sponsoring and supporting the organisation.

Download sessions here.

Last but not least, thanks to GetLatestVersion.it and also WindowServer.it which allowed us to get DevOpsHeroes in the best shape possible.

See you next year!

Posting SQL Server notifications to Slack

Introduction

Automation, proactive monitoring, repeatability, reducing waste of time and technical debt. This is something you should know about when trying to do some DevOps.

Why automation? Because you can reduce technical debt and the number of failures that can happen with a manual interaction. You can create environments using a provisioning procedure without falling in common pitfalls like security misconfigurations, wrong configurations and botched monitoring.

Talking about SQL Server, immediate and proactive notifications represent a great step forward toward automation.

We automate whenever we want to stop doing a bunch of recurring or tedious steps manually. At the same time, we are also improving the overall quality and we are reducing the amount of things that can (and will) go wrong.

We are also optimising on how we use our time, because we can just ignore what the automation is doing for us and focus on that something that really needs our attention.

Finally, in this modern and notification-based world, emails generate too much white noise to deal with. In this article, we will learn how to integrate SQL Server tasks’ notifications with one of the most used collaboration tools: Slack.

Keep in mind that this is not the only way to get this done. This guide would help you to better understand why we’re doing this (eventually why DevOps), and not strictly how to do it, even if we’ll see a real working example.

Minimal requirements

You need to setup an account on slack.com (on a paid plan) and a SQL Server edition. I recommend the free developer edition here.

Note: Don’t use SQL Server Express edition. This version doesn’t support any SQL Server Agent task as well as the Database Mail, which we’ll need hereafter. Also, about slack, you must create a paid account, because the integration described below will not work with a free profile.

In order to send emails, we will use an SMTP sever. It can be either a private Microsoft Exchange, PostFix, or any other on-premises solutions, together with a cloud delivery service, like SendGrid, SendInBlue, MailJet, or Office 365.

The scenario

In a team like mine, which uses chat as a daily communication driver, centralizing every business and technical message could be a great step forward for the members of the team in terms of awareness and knowledge sharing. Business roles can use that tool as well, so we can chat to each other switching topics between tech and functional discussions. It’s just a matter of how Slack (in our case) is configured with channels and naming conventions. A good setup helps us to better organize our meetings, small talks and any other topic related to implementations. This is a cool argument to speak about, but a little bit out of the scope of this guide. We will focus on notification bots instead.

SQL Server is able to send emails with its built-in features out-of-the-box, but we’d like to centralize every notification inside Slack, gaining the following advantages:

  • Instant notification
  • Tailored focus (custom sound instead the same popup for all the incoming emails)
  • Opt-out
  • Quickly involve people that are not following the channel by a mention
  • Relay the problem description within the chat
  • Take actions as soon as the notification is received

The proposed solution

Now, how can we send notifications from SQL Server in an easier way than using custom code or a Slack incoming webhook? Is there any integration or a Slack app?  Yes. And guess what? I think you’ll like it because you don’t need to write a single line of code, and you don’t need to choose between CLR, PowerShell or any other language. It’s ironic, but the integration is called “Email”.

Slack

The purpose of this article is just to describe Slack as a collaboration tool. Further details are provided here. As we said before, the following samples work only if you get a Slack account.

The Slack Email integration

This is the app to work with: Email. Its configuration is based on a four-step wizard:

  • Select the channel (or create a new one).

001.png

  • When added, set the name and a short description of the new contact (bot) in Slack.

002.png

  • Change the avatar (it’s important to recognize the bot at a glance)

003

  • After saving, copy the email address the app created for you.

004

A word about the “Hide this address” checkbox: this is useful if you want to hide the address to any other member of your workspace. You will be the only user able to read it if you check that box.

Type of SQL Server notifications and setup

As a DBA, we’re managing the following types of notifications on a daily basis:

  • SQL Server built-in and custom Alerts
  • Job execution status
  • Integration Services custom emails (within the packages)
  • External monitoring tools (which monitor SQL Instances)

With the exception of SSIS custom emails and external monitoring tools, everything is managed by Database Mail. This is a lightweight layer that allows us to send emails directly from a SQL Server Instance, connecting to a SMTP server.

To setup Database Mail you can follow this guide from Microsoft Documentation.

Once this is up and running, you can manage the notifications using SQL Server Operators. An operator is an alias managed by the SQL Server Agent which you can use to send emails and other types of messages, like pagers and Net Send.

Creating an operator is simple, just invoke the following system stored procedure:

USE msdb; 
GO 

EXEC dbo.sp_add_operator 
    @name = N'<name here>',
    @enabled = 1,
    @email_address = N'<email here>';
GO

If you’re asking what email address you should use, it’s easy to say. You must fill the @email_address parameter with the address returned by the Email app integration for the channel you will send to (j8e4b5t2p4y8g4o2@elysteam.slack.com in the example above). But, what about the name parameter? In my opinion, the best name is the one that helps us to understand where the message will be sent to. Suppose that we’d like to notify something about some index maintenance jobs. We could call the operator Slack Indexes Maintenance, Slack Indexes Maintenance Operator and so on. With such names, you will immediately know what we are going to send to Slack as the topic is related to index maintenance.

Thus, you’ll get the following snippet:

USE msdb; 
GO 

EXEC dbo.sp_add_operator 
    @name = N' Slack Indexes Maintenance Operator',
    @enabled = 1,
    @email_address = N'j8e4b5t2p4y8g4o2@elysteam.slack.com';
GO

 

Slack channels naming considerations

I’d like to share with you my thought about the channel naming conventions. The principles to follow when naming channels, are:

  • Readability (clear for everyone)
  • Awareness (know what)
  • Style and Rules (know how)
  • Repeatability (keep using it from now on)

That being said, if the channel name describes a single action (like indexes maintenance in the above example) the operator which will send notifications should be unique. The reason is simple enough: we know that Indexes Maintenance Operator is sending messages to #sql-idx-maint-alerts (readability) and everyone knows that this is a one-to-one communication between a SQL Server Operator and Slack (awareness). Everyone knows that the “sql” channel prefix indicates SQL Server-related notification and the “alerts” suffix indicates that is an issue to pay attention to (style and rules). At the same time, everyone knows how to do the same with another pipeline of messages in the future (repeatability).

On the other hand, using a general purposes channel, like #sql-maint-alerts, allows us to be ready to future changes. Suppose that index maintenance will not be the only operation we’re executing in our servers (and typically isn’t). Does it make sense to create a new operator called for example, Database Concurrency Check Operator, which sends to a specific purpose channel? Clearly not.

In the end, a generic purpose channel gives the opportunity to hold more than one topic. All the notification sent to that channel should be, let’s say, of the same category to avoid too much generalization.

These solutions (one channel for more operators or a one-to-one solution) work equally well, it’s just a matter of how you’re designing your Slack channels. I suggest to avoid the “one channel to rule them all” pattern, because you’ll get thousands of mixed notifications without any clear idea behind them. After all, a noisy channel with messy content is something that will not be considered for a long time and will be eventually dropped.

Binding alerts

Alerts are triggers that communicate to an operator that something went wrong. This Brent Ozar’s article offers a good list of alerts that need attention. Here you can find their descriptions, based on severity. The binding is straightforward. All you need to do is to link the operator to the alert:

005006

When one of those events occur, an operator is alerted. Then, it sends the message using its setup – in our scenario, an email. If the operator uses the Slack Email app, the email will be sent to the Email app, and the integration will redirect it to Slack.

 

Binding job execution statuses

Let’s see how we can use the notification mechanism to monitor SQL Server Agent Jobs. Each job lets you configure what to do in case of failure, success or completion of its execution. The binding is similar to the alert’s one:

007.png

Once the result is collected, based on the configurations you’ve set up, this job will send an email to the app.

 

Binding custom Integration services email

In order to send an email from a SQL Server Integration Services package (aka .dtsx) you need to configure the SMTP server within the package itself. This is a little out of scope, because it’s not really a SQL Server notification. You can leverage the power of SSIS and prepare a rich HTML-formatted message; the result is nice to read and informative like in these examples:

 

 

Cool stuff, isn’t it? It’s simply a .NET script in SSIS, which uses the System.Net namespace. Although the SSIS package is executed within a SQL Server Agent job, the default notification message that SQL generates is not easy to read. The message you always get is:

JOB RUN:<name> was run on <date/time> DURATION: x hours, y minutes, z seconds. STATUS: Failed. MESSAGES: The job failed. The Job was invoked by Schedule xyz (<name>). The last step to run was step xyz (<name>)

Decorating the package with a more detailed email will improve the readability and the accuracy of our notifications.

Setup an external monitor for notifications to Slack

SQL Server is often (hopefully) monitored with specific counters. We’re using PRTG monitoring tool to measure them, and when a baseline changes and a threshold is hit, we send notifications to Slack. How? Again, sending to the Email app integration, specifying the right channel to send to and getting this:

010.png

The above report has been truncated. In a complete version of it, you’ll find the complete details of the measures, like the name of the servers, the sensors links, the grid with all the results, and everything you can see inside a PRTG admin portal.

 

Test

Let’s see a more complete example, using a SQL Server alert. We’ll use the Severity 17 alert. Severity 17 is simple to raise and it describes a missing or insufficient resource when executing a command:

USE msdb; 
GO 

EXEC msdb.dbo.sp_add_alert @name=N'Severity 017',
    @message_id=0,
    @severity=17,
    @enabled=1,
    @delay_between_responses=60,
    @include_event_description_in=1,
    @job_id=N'00000000-0000-0000-0000-000000000000';
GO

Set the Response for the Severity 17 alert to “Notify Operator”, via email:

006

Run the following severity 17 based t-sql script:

RAISERROR(N'An error occurred Severity 17:insufficient resources!', 17, 1) 
WITH LOG; --don’t forget to use WITH LOG
GO

Go to your Slack account. If you’ve configured everything correctly, you should see the following:

011.png

Did it work? Great! If not, continue reading.

 

Troubleshooting

If you don’t see the notification try these steps:

  1. Be sure that your Slack account is confirmed (its email too)
  2. Once the Slack account is confirmed, check if the channel still exists (CTRL+K -> name of the channel)
  3. Click on “Customize Slack” in the drop down menu of your Slack client/webpage, then click on Customize App in order to check whether the Email integration is active or not:

012

013.png

  • Verify Database Mail configuration (try to send the test email)
  • Verify the operator configuration (is it enabled?)
  • Verify the alert configuration (did you bind the response with email to the operator? Is it enabled?)
  • Verify the SQL Server Agent email profile configuration (is it enabled? Is it the right one?)

014

Conclusions

There are some disadvantages when using this kind of integration. For example, you cannot customize the message, unless you do it inside a .NET script. The Slack Email Address is publicly available, albeit hard to discover, so anyone can send message to your private slack channel by sending emails to that special address. Again, you cannot send the notification to more than one Slack channel or outside of the Slack world. In reality native SQL email notifications show the same limits, where email addresses of distribution lists are similar to Slack channels.

For our purposes, this is a very-low-effort automation with a high return in terms of value. With a couple of clicks, you can setup an email address representing a Slack channel, and, with little more, you can get notifications in a smart and comprehensive layout.

Everything is kept inside the collaboration chat tool we are using massively, every day. In the end, this example embeds one of the core DevOps principles (automation) and provides huge cross-role and cross-team value, especially when the channels include also network and server teams.

I hope that you’ll give this a try.