Welcome!

@DevOpsSummit Authors: Yeshim Deniz, Elizabeth White, Pat Romanski, Liz McMillan, Jyoti Bansal

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 delaPlex will exhibit at SYS-CON's @ThingsExpo, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. delaPlex pioneered Software Development as a Service (SDaaS), which provides scalable resources to build, test, and deploy software. It’s a fast and more reliable way to develop a new product or expand your in-house team.
Regardless of what business you’re in, it’s increasingly a software-driven business. Consumers’ rising expectations for connected digital and physical experiences are driving what some are calling the "Customer Experience Challenge.” In his session at @DevOpsSummit at 20th Cloud Expo, Marco Morales, Director of Global Solutions at CollabNet, will discuss how organizations are increasingly adopting a discipline of Value Stream Mapping to ensure that the software they are producing is poised to offer continuous improvements to customers’ experience of products and services.
SYS-CON Events announced today that Outscale, a global pure play Infrastructure as a Service provider and strategic partner of Dassault Systèmes, 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 2010, Outscale simplifies infrastructure complexities and boosts the business agility of its customers. Outscale delivers a secure, reliable and industrial strength solution for its customers, which include solopreneurs, fast-growing startups, large multi-national companies and government agencies. The company is a strategic partner to Dassault Systémes, and today powers hundreds of organizations throughout North America, Europe and Asia. Outscale’s U.S. headquarters is located in Jersey City, New Jersey, and its global headquarters is in Saint-Cloud, France.
SYS-CON Events announced today that Outscale 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. Outscale's technology makes an automated and adaptable Cloud available to businesses, supporting them in the most complex IT projects while controlling their operational aspects. You boost your IT infrastructure's reactivity, with request responses that only take a few seconds.
SYS-CON Events announced today that Systena America 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. Systena Group has been in business for various software development and verification in Japan, US, ASEAN, and China by utilizing the knowledge we gained from all types of device development for various industries including smartphones (Android/iOS), wireless communication, security technology and IoT services.
DevOps at Cloud Expo – being held October 31 - November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA – announces that its Call for Papers is open. Born out of proven success in agile development, cloud computing, and process automation, DevOps is a macro trend you cannot afford to miss. From showcase success stories from early adopters and web-scale businesses, DevOps is expanding to organizations of all sizes, including the world's largest enterprises – and delivering real results. Among the proven benefits, DevOps is correlated with 20% faster time-to-market, 22% improvement in quality, and 18% reduction in dev and ops costs, according to research firm Vanson-Bourne. It is changing the way IT works, how businesses interact with customers, and how organizations are buying, building, and delivering software.
Interested in leveling up on your Cloud Foundry skills? Join IBM for Cloud Foundry Days on June 7 at Cloud Expo New York at the Javits Center in New York City. Cloud Foundry Days is a free half day educational conference and networking event. Come find out why Cloud Foundry is the industry's fastest-growing and most adopted cloud application platform.
Cloud promises the agility required by today’s digital businesses. As organizations adopt cloud based infrastructures and services, their IT resources become increasingly dynamic and hybrid in nature. Managing these require modern IT operations and tools. In his session at 20th Cloud Expo, Raj Sundaram, Senior Principal Product Manager at CA Technologies, will discuss how to modernize your IT operations in order to proactively manage your hybrid cloud and IT environments. He will be sharing best practices around collaboration, monitoring, configuration and analytics that will help you boost experience and optimize utilization of your modern IT Infrastructures.
Five years ago development was seen as a dead-end career, now it’s anything but – with an explosion in mobile and IoT initiatives increasing the demand for skilled engineers. But apart from having a ready supply of great coders, what constitutes true ‘DevOps Royalty’? It’ll be the ability to craft resilient architectures, supportability, security everywhere across the software lifecycle. In his keynote at @DevOpsSummit at 20th Cloud Expo, Jeffrey Scheaffer, GM and SVP, Continuous Delivery Business Unit at CA Technologies, will share his vision about the true ‘DevOps Royalty’ and how it will take a new breed of digital cloud craftsman, architecting new platforms with a new set of tools to achieve it. He will also present a number of important insights and findings from a recent cloud and DevOps study – outlining the synergies high performance teams are exploiting to gain significant busin...
SYS-CON Events announced today that Twistlock, the leading provider of cloud container security solutions, 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. Twistlock is the industry's first enterprise security suite for container security. Twistlock's technology addresses risks on the host and within the application of the container, enabling enterprises to consistently enforce security policies, monitor and audit activity and identify and isolate threats in a container or cluster of containers.
SYS-CON Events announced today that CollabNet, a global leader in enterprise software development, release automation and DevOps solutions, will be a Bronze Sponsor of SYS-CON's 20th International Cloud Expo®, taking place from June 6-8, 2017, at the Javits Center in New York City, NY. CollabNet offers a broad range of solutions with the mission of helping modern organizations deliver quality software at speed. The company’s latest innovation, the DevOps Lifecycle Manager (DLM), supports Value Stream Mapping for the development and operations tool chain by offering DevOps Tool Chain Integration and Traceability; DevOps Tool Chain Orchestration; and DevOps Insight and Intelligence. CollabNet also offers traditional application lifecycle management, ALM, for the enterprise through its TeamForge product.
SYS-CON Events announced today that Peak 10, Inc., a national IT infrastructure and cloud services provider, 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. Peak 10 provides reliable, tailored data center and network services, cloud and managed services. Its solutions are designed to scale and adapt to customers’ changing business needs, enabling them to lower costs, improve performance and focus internal resources on core competencies.
SYS-CON Events announced today that Super Micro Computer, Inc., a global leader in compute, storage and networking technologies, 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. Supermicro (NASDAQ: SMCI), the leading innovator in high-performance, high-efficiency server technology, is a premier provider of advanced server Building Block Solutions® for Data Center, Cloud Computing, Enterprise IT, Hadoop/Big Data, HPC and Embedded Systems worldwide. Supermicro is committed to protecting the environment through its “We Keep IT Green®” initiative and provides customers with the most energy-efficient, environmentally friendly solutions available on the market.
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.
This talk centers around how to automate best practices in a multi-/hybrid-cloud world based on our work with customers like GE, Discovery Communications and Fannie Mae. Today’s enterprises are reaping the benefits of cloud computing, but also discovering many risks and challenges. In the age of DevOps and the decentralization of IT, it’s easy to over-provision resources, forget that instances are running, or unintentionally expose vulnerabilities.
With major technology companies and startups seriously embracing Cloud strategies, now is the perfect time to attend @CloudExpo | @ThingsExpo, June 6-8, 2017, at the Javits Center in New York City, NY and October 31 - November 2, 2017, Santa Clara Convention Center, CA. Learn what is going on, contribute to the discussions, and ensure that your enterprise is on the right path to Digital Transformation.
Most DevOps journeys involve several phases of maturity. Research shows that the inflection point where organizations begin to see maximum value is when they implement tight integration deploying their code to their infrastructure. Success at this level is the last barrier to at-will deployment. Storage, for instance, is more capable than where we read and write data. In his session at @DevOpsSummit at 20th Cloud Expo, Josh Atwell, a Developer Advocate for NetApp, will discuss the role and value extensible storage infrastructure has in accelerating software development activities, improve code quality, reveal multiple deployment options through automated testing, and support continuous integration efforts. All this will be described using tools common in DevOps organizations.
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.
In his opening keynote at 20th Cloud Expo, Michael Maximilien, Research Scientist, Architect, and Engineer at IBM, will motivate why realizing the full potential of the cloud and social data requires artificial intelligence. By mixing Cloud Foundry and the rich set of Watson services, IBM's Bluemix is the best cloud operating system for enterprises today, providing rapid development and deployment of applications that can take advantage of the rich catalog of Watson services to help drive insights from the vast trove of private and public data available to enterprises.
SYS-CON Events announced today that delaPlex will exhibit at SYS-CON's @CloudExpo, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. delaPlex pioneered Software Development as a Service (SDaaS), which provides scalable resources to build, test, and deploy software. It’s a fast and more reliable way to develop a new product or expand your in-house team.
SYS-CON Events announced today that EARP Integration 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. EARP Integration is a passionate software house. Since its inception in 2009 the company successfully delivers smart solutions for cities and factories that start their digital transformation. EARP provides bespoke solutions like, for example, advanced enterprise portals, business intelligence systems and mobile applications for international enterprises across different sectors such as Energy and Utilities, GreenTech, MedTech, FinTech, Facility Management and Housing, Automotive Manufacturing, and Sport. EARP also cooperates with international software houses by providing them with highly qualified and well-selected, multilingual teams for bigger projects.
SYS-CON Events announced today that Progress, a global leader in application development, 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. Enterprises today are rapidly adopting the cloud, while continuing to retain business-critical/sensitive data inside the firewall. This is creating two separate data silos – one inside the firewall and the other outside the firewall. Cloud ISVs often get requests to connect these silos using technologies such as VPN; however, these tend to be difficult to manage and are not engineered for accessing business data from the cloud.
As cloud adoption continues to transform business, today's global enterprises are challenged with managing a growing amount of information living outside of the data center. The rapid adoption of IoT and increasingly mobile workforce are exacerbating the problem. Ensuring secure data sharing and efficient backup poses capacity and bandwidth considerations as well as policy and regulatory compliance issues.
SYS-CON Events announced today that Cloud Academy 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. Cloud Academy is the industry’s most innovative, vendor-neutral cloud technology training platform. Cloud Academy provides continuous learning solutions for individuals and enterprise teams for Amazon Web Services, Microsoft Azure, Google Cloud Platform, and the most popular cloud computing technologies. Get certified, manage the full lifecycle of your cloud-based resources, and build your knowledge based using Cloud Academy’s expert-created content, comprehensive Learning Paths, and innovative Hands-on Labs.
SYS-CON Events announced today that Interoute 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. Interoute is the owner operator of Europe's largest network and a global cloud services platform, which encompasses over 70,000 km of lit fiber, 15 data centers, 17 virtual data centers and 33 colocation centers, with connections to 195 additional partner data centers. Our full-service Unified ICT platform serves startups and international enterprises, as well as every major European telecommunications service provider and major operators across the world, global internet giants, governments and universities.