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.

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!

DevOpsHeroes see you next year!

Event details

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

  • Subscription: 150
  • Attendees: 93
  • drop: 38%

Attendees’ County/Region (breakdown):

partecipanti-geografia.png

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:

soddisfazione

As we can see, the overall satisfaction is really high (4/5) and everything on the venue was rated higher!

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

  • will you attend again?
    • Sure, 26%
    • Most likely, 52%
    • Likely, 20%
  • will you suggest the event to other people?
    • Sure, 59%
    • Most likely, 32%
    • Likely, 9%

Additionally, we’ve got 87% of new entries, awesome!

Some considerations

We have to work for the next year’s edition, in order to improve our organisation, but in the end we did very good. The number of event feedback is close to the total number of attendees, so they can be considered the source of any suggestion.

Speaking about tech sessions, including the speech made by Martin Woodward, we’ve received a set of important suggestions. We already know, our speakers are skilled and passionated, but how can we understand the people’s opinions without these surveys?

I’ve spent last two nights in browsing and reading hundreds of those sheets (almost 500, thanks again). Each of them has the following questions:

  • Did the session meet your expectations?
  • Was the topic interesting?
  • Was the speech valuable?
  • What about the quality of the presentation?
  • How’s the speaker? Funny, expert, teacher, motivator, storyteller?

As we can see in the picture below, left side, the overall satisfaction is very high once again (expectations, interesting, valuable and presentation). On the right side, we notice that our speakers are considered experts and somehow funny and storyteller:

screen-shot-2017-10-28-at-02-07-53-e1509149570123.png

Conclusions

We’re really proud of the second edition of DevOpsHeroes. Engage IT Services and Upgrade 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 Martin Woodward (Microsoft), which “crossed the seven seas” for being with us, and also to HPE for sponsoring and supporting the organisation. A great kudos goes to Silvio Di Benedetto and Giuliano Latini, who managed and followed the live streaming and the session recordings (coming soon here).

Download sessions, pics and the event stuff here.

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

See you next year!

Events: DevOpsHeroes 2017 and SQL Saturday Parma 2017

It’s September, a month whose the start is the beginning of many things, especially in Italy. Kids start over the schools, Companies invest, and what about projects and events?

You know, I really love to do both. But in this post I’d let you know how I feel proud about my work in the past four years. Thanks to the help of my employees and co-ops, I did organise four SQL Saturdays in Parma (2014/355, 2015/462 and 2016/566), making our small town a big SQL Server City. Who would’ve thought it? Yes, maybe in my mind, there was something that pushed me to try, but it could have an epic fail. However, I did it, and it was an astonishing set of records. It started as a quest in an Role Play game, and now we’re in front of this great SQL Saturday. Something to be really proud of.

Two years ago, I felt the need to add a new event, which would have to support a new way of thinking about the IT roles that I was aware of. And that was when DevOps came out. A new buzzword that needed to get a deep explanation, in terms of culture and approach. Combining my passion to The Simpsons series (especially considering Homer Simpson) and DevOps stuff, I’ve made up DevOpsHeroes, whose acronym is DOH, the Homer’s exclamation. And now, for the second year, Developers, IT Operations people, DBAs and PMOs will share their experience about how to integrate, cooperate and make IT world more productive. Another event, another thing to be proud of.

DOH_logo

After the introduction, let me give you some important details about these events, sorted by upcoming date:

DevOpsHeroes 2017 – Parma (http://www.devops-heroes.net/)

Sql Saturday 2017 – Parma (http://www.sqlsaturday.com/675)

  • Date and Location: Saturday, 18th November @ Univeristy of Parma
  • Hashtag: #SQLSAT675
  • Entrance fee: free (lunch included)
  • Duration: from 8am (registrations until 9am) to 6.30pm
  • Registration link: https://www.sqlsaturday.com/675/registernow.aspx
  • At the registration desk:
    • you will use the SpeedPASS
    • you will receive the lunch ticket
    • you will receive the welcome kit
  • session languages: ITA/ENG

However, this year I’d to thank my helpers at Engage IT Services Srl,  the great support of Upgrade Srl (Andrea, Federica and Riccardo, that organised with us the DOH event), HPE and also many communities, like DotDotNet.org and mostly our great community GetLatestVersion.it. A special thanks goes to Windowserver.it, which will record sessions, live streaming and interviews. Speaking about SQLSaturday, the sponsor page depicts how the event is supported. A big thank goes to all of them!

What the future will reserve to us in Parma? I think that I could try to add another event, maybe on IoT topics, because I feel that the world will change following that direction and also, hopefully, my newborn Giulio will play with my and some robot 🙂

Alternatives could be changing the format, like with a TED event or something like it. Who knows? I’m pretty sure that I’ll get a lot of help from now on. This year has been a demonstration of it. I started alone, and now we work in five. The last thing to be proud of. Wonderful!

Events after the summer holidays

Summer holidays are coming, finally. This year I’m really tired, maybe because I’ve spent my energies on learning English, working hard for my own Company, following external school and non-school projects. But I can say that one of the most heavy part has been the organization of the following two events:

Continue reading “Events after the summer holidays”

Automatically link databases to Red Gate SQL Source Control

For the ones that have many databases to keep under source control, it can be really useful to speed up the link-to-source-control process. The only way we have now is to use the GUI provided by Red Gate SQL Source Control. Actually, there’s a github project called SOCAutoLinkDatabases by Matthew Flat, a Red Gate engineer, but, unfortunately it works only on a shared database model (centralised) in TFS. Let’s see how to manage the link using Working Folder (which is also good for many SCM) and dedicated database model (distributed).

Continue reading “Automatically link databases to Red Gate SQL Source Control”