Welcome!

@DevOpsSummit Authors: SmartBear Blog, Cloud Best Practices Network, Toddy Mladenov, Elizabeth White, Yeshim Deniz

Related Topics: @DevOpsSummit

@DevOpsSummit: Blog Feed Post

Creating a Deployment Model for Scripted DB Updates

It is common for enterprises to work with hundreds of applications

Creating a Deployment Model for Scripted DB Updates
By Dave Roberts

It is common for enterprises to work with hundreds of applications, each of those calling for a number of database scripts to be executed in a particular order. Needless to say, the execution and organization of these scripts can become complex and tedious. Instead of creating new steps for every application release, modeling your scripted database updates will organize your script execution into an automated, repeatable process that allows for quick changes and stability within the release.

Your application may call for a number of scripts to be executed in a particular order, where the flow bounces from one schema to another, then back to the first, then to another one altogether. Take close look at the example below:

Let’s look at how to model the above scenario in XL Deploy.

To begin, we’ll model the infrastructure.

Each schema is modeled by a database client that connects to that schema on the proper database. Each schema could be in the same database or in different databases. Although the database is one of the properties of this client object, the schema is not, so we’ll rely on tag-matching to get the correct scripts deployed to it.

 

Next, we’ll create the second part of our model, an Environment with the following containers as members:

Finally, the third part of our model is the application package. We are starting with a repo with a number of database script or SQL files, along with a mapping.txt file that gives us a mapping and ordering between file and schema.

Here the mapping tells us to run the 1*.sql files against schema A, then the 2*.sql files against schema B, and so on, to produce the deployment step list we started with.

But remember that XL Deploy doesn’t deploy individual scripts, rather it handles packages of scripts, and within each package, the scripts are executed in lexicographical order.  See https://docs.xebialabs.com/xl-deploy/concept/database-plugin.html.

 

Enterprise-scale Application Release Automation

Learn about XL Deploy and how it’s Model-based deployment approach can organize your deployments into an automated repeatable process.

 

 

 

 

To package up this repository into objects that XL Deploy can handle, we’re going to run a little packager script that interprets the mapping file and bundles the scripts appropriately. And, we’re going to run it all under Jenkins to take advantage of the XL Deploy post-build action to create a Deployment Package and publish it to XL Deploy’s repository. In the course of that, we’ll place tags on the deployable objects we create in order to facilitate the correct mappings. We can also invoke a deployment from Jenkins, but we’ll stop short of that so we can analyze the resulting mappings in detail.

So, first, the packager script:

SEQ=1
while read NUM SCHEMA
do
  echo $NUM $SCHEMA
  cd $WORKSPACE
  mkdir sql-obj-$SEQ
  cp $NUM*.sql sql-obj-$SEQ
  cd sql-obj-$SEQ
  zip ../sql-obj-$SEQ.zip *.sql
  echo "SQLOBJ$SEQ"=SCHEMA-$SCHEMA >> $WORKSPACE/$2
  SEQ=`expr $SEQ + 1`
done < $1

Jenkins will invoke it with two parameters. $1 is the mapping.txt file. As we saw above, it is committed to the same source-control repo as the SQL files. And $2 is an output file, buildVariables.properties, which we’ll pass to the Jenkins Inject-Variables plugin to help tag our objects later.

Of course, we could get much more sophisticated here as to how we group the source *.sql files into bundles, perhaps using number ranges or regex matching, but this simple script conveys enough of the idea.

The result of this script is a zip file for each line of the mapping file, numbered sequentially:  sql-obj-1.zip, sql-obj-2.zip, sql-obj-3.zip, sql-obj-4.zip, and sql-obj-5.zip. Each zip contains the SQL scripts we instructed the packager to include. In this case, the first argument on the mapping line is matched against the first character of the SQL file’s name, so we bundle scripts 10-13 into the first object, 20-23 into the second object, and so on.

Note that the matching-number here (NUM) and the sequence number (SEQ) are independent — for this example they happen to coincide, but I’ll show later an example where they don’t.

Also resulting from the script is the buildVariables.properties file, where the right side of each assignment is a tag for XL Deploy:

SQLOBJ1=SCHEMA-A
SQLOBJ2=SCHEMA-B
SQLOBJ3=SCHEMA-A
SQLOBJ4=SCHEMA-C
SQLOBJ5=SCHEMA-B

We use the Inject-environment-varibles plugin (https://wiki.jenkins-ci.org/display/JENKINS/EnvInject+Plugin) to set variables holding the tag values.

Finally, we come to the post-build action that creates an application package. We set the app name and version:

And then we have a sql.SqlScripts deployable for each line in the mapping.txt file that makes use of an injected variable for its tag. Here is the first one of the five needed for this example.  Change the ‘1’  in the Name, Tags variable, and Location to ‘2’ for the next one, and then to ‘3’, ‘4’, and ‘5’ for the rest.

Key to our ordering scenario is that these artifacts will be executed in order by the Name field, sql-obj-1, sql-obj-2, etc., while the individual scripts within each one will be ordered by their file names, 10-select.sql, 11-select.sql, etc.

 

Note a limitation here: we have to have one deployable for each line in mapping.txt, and we don’t know how many we will need ahead of time. An easy workaround is to take a liberal guess as to your need and configure a fixed number of objects. The unused ones will be empty and tag-less so they won’t play a role in the deployment. A dynamic approach that adds a variable number of deployables is beyond the scope of this post.

When we run the Jenkins build, we will get the next package into XL Deploy‘s repo, then the drag-and-drop deployment action will give the mapping and ordering we expect:

Finally, let’s change the mapping like this to show the independence of the matching and sequencing numbers. This mapping yields the result shown, demonstrating just how flexible this approach is!

 

The post Creating a Deployment Model for Scripted DB Updates appeared first on XebiaLabs.

Read the original blog entry...

More Stories By XebiaLabs Blog

XebiaLabs is the technology leader for automation software for DevOps and Continuous Delivery. It focuses on helping companies accelerate the delivery of new software in the most efficient manner. Its products are simple to use, quick to implement, and provide robust enterprise technology.

@DevOpsSummit Stories
SYS-CON Events announced today that MobiDev, a client-oriented software development company, will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place June 6-8, 2017, at the Javits Center in New York City, NY, and the 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. MobiDev is a software company that develops and delivers turn-key mobile apps, websites, web services, and complex software systems for startups and enterprises. Since 2009 it has grown from a small group of passionate engineers and business managers to a full-scale mobile software company with over 200 developers, designers, quality assurance engineers, project managers in house, specializing in the world-class mobile and web development.
DevOps is often described as a combination of technology and culture. Without both, DevOps isn't complete. However, applying the culture to outdated technology is a recipe for disaster; as response times grow and connections between teams are delayed by technology, the culture will die. A Nutanix Enterprise Cloud has many benefits that provide the needed base for a true DevOps paradigm.
Virtualization over the past years has become a key strategy for IT to acquire multi-tenancy, increase utilization, develop elasticity and improve security. And virtual machines (VMs) are quickly becoming a main vehicle for developing and deploying applications. The introduction of containers seems to be bringing another and perhaps overlapped solution for achieving the same above-mentioned benefits. Are a container and a virtual machine fundamentally the same or different? And how? Is one technically superior to the other? What about performance and security? Does IT need either one, or both?
Keeping pace with advancements in software delivery processes and tooling is taxing even for the most proficient organizations. Point tools, platforms, open source and the increasing adoption of private and public cloud services requires strong engineering rigor - all in the face of developer demands to use the tools of choice. As Agile has settled in as a mainstream practice, now DevOps has emerged as the next wave to improve software delivery speed and output. To make DevOps work, organizations must focus on what is most relevant to deliver value, reduce IT complexity, create more repeatable agile-based processes and leverage increasingly secure and stable, cloud-based infrastructure platforms.
ChatOps is an emerging topic that has led to the wide availability of integrations between group chat and various other tools/platforms. Currently, HipChat is an extremely powerful collaboration platform due to the various ChatOps integrations that are available. However, DevOps automation can involve orchestration and complex workflows. In his session at @DevOpsSummit at 20th Cloud Expo, Himanshu Chhetri, CTO at Addteq, will cover practical examples and use cases such as self-provisioning infrastructure/applications, self-remediation workflows, integrating monitoring and complimenting integrations between Atlassian tools and other top tools in the industry.
For organizations that have amassed large sums of software complexity, taking a microservices approach is the first step toward DevOps and continuous improvement / development. Integrating system-level analysis with microservices makes it easier to change and add functionality to applications at any time without the increase of risk. Before you start big transformation projects or a cloud migration, make sure these changes won’t take down your entire organization.
SYS-CON Events announced today that Ocean9will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Ocean9 provides cloud services for Backup, Disaster Recovery (DRaaS) and instant Innovation, and redefines enterprise infrastructure with its cloud native subscription offerings for mission critical SAP workloads.
Adding public cloud resources to an existing application can be a daunting process. The tools that you currently use to manage the software and hardware outside the cloud aren’t always the best tools to efficiently grow into the cloud. All of the major configuration management tools have cloud orchestration plugins that can be leveraged, but there are also cloud-native tools that can dramatically improve the efficiency of managing your application lifecycle.
Providing the needed data for application development and testing is a huge headache for most organizations. The problems are often the same across companies - speed, quality, cost, and control. Provisioning data can take days or weeks, every time a refresh is required. Using dummy data leads to quality problems. Creating physical copies of large data sets and sending them to distributed teams of developers eats up expensive storage and bandwidth resources. And, all of these copies proliferating the organization can lead to inconsistent masking and exposure of sensitive data. But some organizations are adopting a new method of data management for DevOps that is delivering transformational business outcomes in faster time to market, lower costs, and great control. In his session at DevOps Summit, Brian Reagan, Managing Director of Blackthorne Consulting Group, an Actifio company, revi...
DevOps is often described as a combination of technology and culture. Without both, DevOps isn't complete. However, applying the culture to outdated technology is a recipe for disaster; as response times grow and connections between teams are delayed by technology, the culture will die. A Nutanix Enterprise Cloud has many benefits that provide the needed base for a true DevOps paradigm. In his Day 3 Keynote at 20th Cloud Expo, Chris Brown, a Solutions Marketing Manager at Nutanix, will explore the ways that Nutanix technologies empower teams to react faster than ever before and connect teams in ways that were either too complex or simply impossible with traditional infrastructures.
SYS-CON Events announced today that CA Technologies has been named “Platinum Sponsor” of SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY, and the 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. CA Technologies helps customers succeed in a future where every business – from apparel to energy – is being rewritten by software. From planning to development to management to security, CA creates software that fuels transformation for companies in the application economy.
SYS-CON Events announced today that Cloudistics, an on-premises cloud computing company, has been named “Bronze Sponsor” of SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Cloudistics delivers a complete public cloud experience with composable on-premises infrastructures to medium and large enterprises. Its software-defined technology natively converges network, storage, compute, virtualization, and management into a single platform to drive unprecedented simplicity in the data center. Customers can start with a base infrastructure and scale to multi-site and multi-geo infrastructures with predictable economics and performance.
SYS-CON Events announced today that Loom Systems will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Founded in 2015, Loom Systems delivers an advanced AI solution to predict and prevent problems in the digital business. Loom stands alone in the industry as an AI analysis platform requiring no prior math knowledge from operators, leveraging the existing staff to succeed in the digital era. With offices in San Francisco and Tel Aviv, Loom Systems works with customers across industries around the world.
What if you could build a web application that could support true web-scale traffic without having to ever provision or manage a single server? Sounds magical, and it is! In his session at 20th Cloud Expo, Chris Munns, Senior Developer Advocate for Serverless Applications at Amazon Web Services, will show how to build a serverless website that scales automatically using services like AWS Lambda, Amazon API Gateway, and Amazon S3. We will review several frameworks that can help you build serverless applications, such as the AWS Serverless Application Model (AWS SAM), Chalice, and ClaudiaJS.
SYS-CON Events announced today that SoftLayer, an IBM Company, has been named “Gold Sponsor” of SYS-CON's 18th Cloud Expo, which will take place on June 7-9, 2016, at the Javits Center in New York, New York. SoftLayer, an IBM Company, provides cloud infrastructure as a service from a growing number of data centers and network points of presence around the world. SoftLayer’s customers range from Web startups to global enterprises.
Interoute has announced the integration of its Global Cloud Infrastructure platform with Rancher Labs’ container management platform, Rancher. This approach enables enterprises to accelerate their digital transformation and infrastructure investments. Matthew Finnie, Interoute CTO commented “Enterprises developing and building apps in the cloud and those on a path to Digital Transformation need Digital ICT Infrastructure that allows them to build, test and deploy faster than ever before. The integration of Rancher software with Interoute Digital Platform gives developers access to a managed container platform that sits on a global privately networked cloud, enabling true distributed computing.”
SYS-CON Events announced today that CrowdReviews.com has been named “Media Sponsor” of SYS-CON's 20th International Cloud Expo, which will take place on June 6–8, 2017, at the Javits Center in New York City, NY. CrowdReviews.com is a transparent online platform for determining which products and services are the best based on the opinion of the crowd. The crowd consists of Internet users that have experienced products and services first-hand and have an interest in letting other potential buyers their thoughts on their experience.
SYS-CON Events announced today that T-Mobile will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. As America's Un-carrier, T-Mobile US, Inc., is redefining the way consumers and businesses buy wireless services through leading product and service innovation. The Company's advanced nationwide 4G LTE network delivers outstanding wireless experiences to 67.4 million customers who are unwilling to compromise on quality and value.
@DevOpsSummit has been named the ‘Top DevOps Influencer' by iTrend. iTrend processes millions of conversations, tweets, interactions, news articles, press releases, blog posts - and extract meaning form them and analyzes mobile and desktop software platforms used to communicate, various metadata (such as geo location), and automation tools. In overall placement, @DevOpsSummit ranked as the number one ‘DevOps Influencer' followed by @CloudExpo at third, and @MicroservicesE at 24th.
SYS-CON Events announced today that Infranics will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Since 2000, Infranics has developed SysMaster Suite, which is required for the stable and efficient management of ICT infrastructure. The ICT management solution developed and provided by Infranics continues to add intelligence to the ICT infrastructure through the IMC (Infra Management Cycle) based on mathematical analysis and forecasting Big Data Analyze and Control.
SYS-CON Events announced today that SD Times | BZ Media has been named “Media Sponsor” of SYS-CON's 20th International Cloud Expo, which will take place on June 6–8, 2017, at the Javits Center in New York City, NY. BZ Media LLC is a high-tech media company that produces technical conferences and expositions, and publishes a magazine, newsletters and websites in the software development, SharePoint, mobile development and commercial UAV markets.
Have you ever noticed how some IT people seem to lead successful, rewarding, and satisfying lives and careers, while others struggle? IT author and speaker Don Crawley uncovered the five principles that successful IT people use to build satisfying lives and careers and he shares them in this fast-paced, thought-provoking webinar. You'll learn the importance of striking a balance with technical skills and people skills, challenge your pre-existing ideas about IT customer service, and gain new insights into how to build your own satisfying and rewarding career by rising above the ordinary and mundane to build an extraordinary life and career as a world-class Compassionate Geek.
SYS-CON Events announced today that HTBase will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. HTBase (Gartner 2016 Cool Vendor) delivers a Composable IT infrastructure solution architected for agility and increased efficiency. It turns compute, storage, and fabric into fluid pools of resources that are easily composed and re-composed to meet each application’s needs. With HTBase, companies can quickly provision resources and deploy unique, mission-critical, self-designed solutions to add-onto or create any type of infrastructure as per the business requirement. HTBase is the first company to enable a true multi-cloud strategy, enabling organizations to automate movement of data and workloads between private and public clouds. This means that organizations can now move data and workloads between pub...
After more than five years of DevOps, definitions are evolving, boundaries are expanding, ‘unicorns’ are no longer rare, enterprises are on board, and pundits are moving on. Can we now look at an evolution of DevOps? Should we? Is the foundation of DevOps ‘done’, or is there still too much left to do? What is mature, and what is still missing? What does the next 5 years of DevOps look like? In this Power Panel at DevOps Summit, moderated by DevOps Summit Conference Chair Andi Mann, panelists looked back at what DevOps has become, and forward at what it might create next.
The essence of cloud computing is that all consumable IT resources are delivered as services. In his session at 15th Cloud Expo, Yung Chou, Technology Evangelist at Microsoft, demonstrated the concepts and implementations of two important cloud computing deliveries: Infrastructure as a Service (IaaS) and Platform as a Service (PaaS). He discussed from business and technical viewpoints what exactly they are, why we care, how they are different and in what ways, and the strategies for IT to transition into and take advantages of these emerging service models.