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.

Best Practice in Rebuilding Index in SQL Server

For every Database admin, ensuring the smooth performance of the SQL Server is a headache. They need to perform various tasks and tricks for a productive and fast SQL database. One such common task is to keep the Index fragmentation in check. While this is a challenge for the DBAs, Index Fragmentation can be controlled by reorganizing and rebuilding.

Rebuilding Index in SQL Server is the method often used when the fragmentation level goes higher. If you want to improve the performance of SQL database, you have to rebuild the index. But the question is when and how to perform this task. This write-up will focus on the best practices in rebuilding index of SQL Server. We will also discuss when is the right time to conduct this task easily.

Rebuilding Index in SQL Database – Know Why and When to Perform?

It is known to the SQL Server users that database performance gets significantly hampered if the SQL database becomes full of fragmented indexes. As Index Fragmentation keeps increasing along with the database usage, admins should be careful about the fragmentation rate. Depending on the database size, DBAs should fix a schedule when the index fragmentation will be checked using “sys.dm_db_index_physical_stats” command. When this command is run, users can learn about the percentage of index fragmentation in SQL database.

If the percentage is as low as 10%, no additional action is needed. If the level is between 10%- 30%, you have to rebuild the index to enhance its performance. Only when it crosses the 30% bar, the question of rebuilding index in SQL Server comes to the scene. However, some SQL Server experts recommend performing Index rebuilding only when the fragmentation rate reaches 80% or 90%. Since rebuilding index is a resource-consuming task, database admins should consider how much the fragmentation affects the database performance before rebuilding the index.

Best Practices in Rebuilding Index in SQL Server

If you are interested in rebuilding indexes, it is better to follow certain basic rules, known as the best practices in Index rebuilding. For example, if you are using any SQL Server edition other than the Enterprise Edition, then this task should be done offline. Since the feature of Online Index Rebuilding got introduced in SQL Server 2005 Enterprise Edition, any earlier version users need to perform it offline. With Online index rebuilding, the index never goes offline and table also remains available for use during the process.

In case of SQL Enterprise Editions that support online index rebuild, the online process takes more time than offline rebuilding. That is why, offline index rebuilding is highly recommended if the company can afford downtime. To minimize the downtime, it should be done when minimum people are using the database or it should be done along with scheduled maintenance tasks. Therefore, nighttime is perfect to schedule index rebuilding. It is also suggested to conduct this task at least once a week. If you do not have any maintenance window for your database, you can try doing Online rebuilding.

Rebuilding Index in SQL Server consumes a lot of resources. So performing it too regularly will create inconvenience to the database with scarce resources. Database admins should consider their database capacity and resources before scheduling the index rebuilding.

Note:  Get to know How to Deal with Index Corruption in SQL Server

Concluding Thoughts

Index fragmentation is a common situation in all SQL databases. The productivity of the SQL Server depends on the level of Index fragmentation. Only a controlled Index fragmentation rate is desirable for smooth functioning of SQL Server. Among many approaches that keep the fragmentation level in control, rebuilding Index is a popular one.
In this process, logical index fragmentation is removed, statistics get updated and database page space is emptied. Therefore, users should include this in their maintenance scheduling window. They can also learn about the best practices in rebuilding index in SQL Server from this post. Also, consider the situation of your own SQL database to customize the rebuilding process.

SQL Server Latest Updates (Nov. Dec. 2018)

Directly from the SQL Server Release Service blog, here the latest updates for SQL Server 2016 SP1, SP2, 2017 RTM and 2014 SP2, SP3:

Cumulative Update #1 for SQL Server 2014 SP3

Cumulative Update #15 for SQL Server 2014 SP2

Cumulative Update #12 for SQL Server 2016 SP1

Cumulative Update #4 for SQL Server 2016 SP2

Cumulative Update #13 for SQL Server 2017 RTM

and

Public Preview for SSRS 2017+ Management Pack with Power BI Reporting Server Support

…Stay Tuned, Merry Christmas and a Happy New Year! 🙂

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!

SQL Server Latest Updates (Aug. Sep. 2018)

Directly from the SQL Server Release Service blog, here the latest updates for SQL Server 2016 SP1, SP2, 2017 RTM and 2014 SP2:

Cumulative Update #13 for SQL Server 2014 SP2

Cumulative Update #11 for SQL Server 2016 SP1

Cumulative Update #3 for SQL Server 2016 SP2

Cumulative Update #11 for SQL Server 2017 RTM

…Stay Tuned! 🙂