Liquibase 351 Calling Same Xml File Again

Past:   |   Updated: 2016-07-12   |   Comments (1)   |   Related: More > Database Administration

Problem

This is the 3rd and final part of our ongoing series on SQL Server Database Alter Management with Liquibase. In the terminal 2 posts (tip 1 and tip two) we saw how to install and configure Liquibase and rolled out a few database changes with it. Nosotros also learned how Liquibase tin conditionally apply database changes and how to rollback those changes. Although our examples take been around SQL Server, the concepts remain the aforementioned for any supported database platform.

Solution

In this tip, we will talk about some use cases for SQL Server Database Change Management with Liquibase.

Documenting Database Changes

Browsing through the DATABASECHANGELOG table is one fashion of looking at your database change history. Another method is to run the dbDoc command. With dbDoc, Liquibase will look through the DATABASECHANGELOG table and changelog file and generate a JavaDoc mode html certificate. This file will testify when each database component was created or inverse, what changes were made to those components and who made those changes.

In the lawmaking sample beneath, we are running the dbDoc against our liquibase_test database. Notation how we take to specify the original changelog file and the path where the document has to be created:

liquibase  --driver=com.microsoft.sqlserver.jdbc.SQLServerDriver  --classpath="..\\Program Files\\Microsoft JDBC Driver 4.0 for SQL Server\\sqljdbc_4.0\\enu\\sqljdbc4.jar"  --url="jdbc:sqlserver://<SQL Server name or IP>:1433;databaseName=liquibase_test;integratedSecurity=false";  --changeLogFile="D:\\Liquibase\\databaseChangeLog\\dbchangelog.xml"  --username=liquibase  --password=liquibase                          dbDoc                                      D:\\Liquibase\\dbDoc                      

If nosotros scan to the target directory, we will encounter a number of files:

Opening the index.html file in a browser will load the whole documentation. In the prototype beneath, nosotros are looking at the history of changes made to the customer tabular array. We tin run across there are two awaiting changes hither. Note the left side of the page is a navigation pane with links to different objects from the database:

Liquibase and Version Control

For a database nether evolution,  the changelog file can modify frequently. Information technology thus makes sense to save the changelog in a version control system like Git, TFS or Subversion. Basically you treat the changelog file like any other application source code. With a version control system, you can track what changes were fabricated to the changelog file over fourth dimension. Notation that nosotros are tracking the file's changes here, not the database's changes. The file may have been changed a few times over the last few check-ins, only none of those changes may have been applied to the database. Where the database currently stands can be seen from the DATABASECHANGELOG table.

So how does Liquibase integrate database changes from a version control system? If you lot are considering a linear process where there are no code branches for the changelog file, it' fairly elementary. In the following image, nosotros are seeing a database starting with version ane. Database changelog version i upgrades the database to version 2, database changelog version 2 upgrades it to version 3 and so on. This works well when there is only one co-operative for the lawmaking (main branch) and developers are working on its features in a linear fashion. That is, developer A develops database changelog version i, checks it into the version control organisation (VCS) and applies to the database, so developer B develops changelog version two and applies and so on.

In practice though, the picture volition be more similar this:

Here, we take a number of "feature branches" sprouting from the master co-operative. Each of these features branches showtime with a copy of the changelog from the master co-operative. These branches alive inside the developers' workstations every bit they work on the changelog file. When the changelog is fix to be integrated into the chief branch, the local characteristic co-operative is "pushed" to the remote version control organization server. From at that place, the master branch can "pull" the changes in. In other words, the changes in the characteristic branch are now part of the original changelog.

When complex databases are being developed, there can exist any number of feature branches in flight at any time. Possibly none of those branches would be merged dorsum at the aforementioned time. Developers A, B and C could exist working on version 2.5 of the changelog and developers D, E and F could exist working on version four.

When information technology'southward time to run the merged code from the primary branch, how does Liquibase know the merged changesets from different branches will non disharmonize with one some other when they try to modify the same object?

Again, the answer lies in the way Liquibase checks changesets for uniqueness. Each changeset has an author and id attribute and each changelog file has a name and path. Together, these three items make a changeset unique. When it comes to execution, Liquibase runs the changesets from the main co-operative, and for each changeset merged into the primary co-operative, it compares that against what's stored in the DATABASECHANGELOG table. If the changeset has not been applied, Liquibase volition apply it.

Automating Liquibase Runs

So far we have been running Liquibase from the control the prompt merely. In the outset part of this series, nosotros had added the Liquibase directory to our PATH environment variable. If that has not been set up, Liquibase can be called from a coffee control with the -jar option. Since Liquibase itself is independent within a .jar file, the control will be like this:

java -jar path-to-liquibase-directory\liquibase.jar  <options> <command><command parameters>

 Over again, this is a transmission procedure. The command prompt invocation can exist somewhat automated past:

1. Creating a .bat, .cmd or PowerShell script that runs Liquibase with the correct parameters and options
2. Calling the script file either on-demand or from a scheduled SQL Server Agent Chore.

Since Liquibase is bachelor for both Windows and *nix operating systems, same principles apply for Linux and Mac OS X; we can create crush scripts that call Liquibase and employ that beat out script from cron jobs.

Tools like Maven or Ant can also exist used to automate Liquibase runs. We won't get into the details here.

Nigh It shops with a DevOps workflow would perchance want to invoke Liquibase from a Continuous Integration (CI) server as part of a nightly build process. In this scenario, Developers would work on the database changelog during the solar day and cheque it in to the version control system (VCS) once they are happy. During nightly builds, the CI server would invoke Liquibase which will apply the latest database changes from the main branch to a target environment. Awarding code tin can then exist deployed in the same environs and a set of bones integration tests tin be performed.

Popular CI servers include Atlassian Bamboo or Jenkins (free). Other tools are available from IBM, Microsoft or CA. Although Liquibase has an active community and in that location is a Jenkins plugin available for Liquibase, information technology's far better when you consider Datical DB, Liquibase's commercial version. With Datical DB, you get commercially supported plugins like DaticalDB4Jenkins or DaticalDB4Bamboo.

Keeping Changelog Files in Society

Equally the database goes through its lifecycle, more than and more changesets will be added to the changelog file. When y'all write your changelogs in XML, fifty-fifty simple changes tin can take multiple lines. With all the changes being heaped into one file, eventually the file becomes unmanageable. Information technology can take longer and longer to load it in editor, it can seem and then complex that you would not know if your changes are going to break anything. Moreover, every fourth dimension Liquibase runs the changelog, information technology has to work on every changeset from the first: something that will have longer and longer.

Fortunately, just like code refactoring, Liquibase changelogs can be refactored in a hierarchical fashion. That ways we can accept a single "primary" changelog file which can include pointers to a number of "kid" changelog files, which in turn can point to more changelog files and so on. With this arroyo, changelog files tin can have a relationship similar this:

As an example, the following code cake shows how we have refactored the original changelog file for our liquibase_test database (you tin become through the previous 2 posts for reference). Every bit y'all tin see, we have included reference to three unlike XML files and one SQL file here. Each file is a changelog that takes intendance of a discreet alter. When Liquibase starts running this changelog, it runs each included changelog in gild.

<?xml version="1.0" encoding="UTF-viii"?>
            <databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-example" xsi:schemaLocation="http://world wide web.liquibase.org/xml/ns/dbchangelog http://world wide web.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.i.xsd">  <preConditions onFail="HALT" onFailMessage="This changelog needs to run nether the liquibase user account">   <runningAs username="liquibase"/>  </preConditions>  <              include                                      file            ="create_table_customer.xml"/>  <              include                                      file            ="create_table_products.xml"/>  <              include                                      file            ="create_table_orders.xml"/>  <              include                                      file            ="dbchangelog_sp.sql"/> </databaseChangeLog>

If nosotros desire to make further changes to the database, we can create another changelog file and include its reference to the master changelog file.

Liquibase Directory Construction

Related to keeping changelogs in order, there is the question of arranging changelogs in the file organisation. This is a question often asked by developers and project teams when they first start using Liquibase. The official website shows a sample directory structure. In our opinion, it'south best to create a directory for each database release. The principal directory will have the "root" changelog file which will include references to other changelog files in these release-specific subdirectories. Those subdirectories volition comprise "kid" changelog files pointing to other component specific changelog files in other subdirectories. The image below shows the directory hierarchy.

Using this directory structure provides flexibility. When a new release is created, a new directory is created for it. Under this directory, component specific directories are created for tables, views, stored procedures, functions and so on. Each of these component subdirectories will incorporate a changelog file for implementing the components for the release. A changelog file is created under the release directory which will include references to the component changelogs. Finally, the reference to this release changelog is added to the chief changelog.

Some Best Practices

The official Liquibase site lists a number of best practices. Nosotros won't rehash them hither; instead, nosotros will  list some of the things to make your life as a DBA or developer easier.

• When using Liquibase to manage database changes, don't use whatever other tools, methods or processes. Liquibase cannot rail changes that it did non make. From the start twenty-four hours of adopting Liquibase, get in the tool of choice.
• Make use of contexts, pre-conditions and rollbacks. This can ensure you are not running changes meant for DEV in QA or PROD.
• Utilize a consistent directory structure for storing changelogs. This applies for both programmer workstations and the version control system.
• Don't employ a generic author name like "Dev Team" for all your changes. Developers should be encouraged to use their own names in a consequent fashion. For example, if Jane Doe is using her author proper noun as "jane_doe", it should exist used as "jane_doe" for all her changes, not "JANE DOE".
• Encourage developers to apply comments in their changelogs.
• Although changeset ids tin can exist whatever combination of messages and numbers, effort to go along them in easy-to-read format. If you don't want to apply simple sequential values like 1,2 or three, that's fine, use something that tin can be eyeballed easily. Using a long hexadecimal string may ensure uniqueness, simply it may not be easily readable. Again, employ a consistent approach for all changelogs.
• If you are a DBA, generate dbDocs often and see how your database changes are looking. Are there any failed runs? Are in that location too many rollbacks or too many pending changes?
• Employ a logLevel of INFO for each Liquibase run. This would ensure you can capture Coffee exceptions whenever there is an error.
• Endeavor to incorporate Liquibase into your Continuous Integration workflow.

Limitations

In that location are some limitations to consider when using Liquibase with SQL Server databases:

• It can scroll out stored procedures, functions or triggers, but it cannot generate them from an existing database with the generateChangeLog command.
• We have not tested Liquibase with advanced SQL Server features like partitions, XML data blazon, database triggers or full text indexes. They may not exist supported.
• Information technology cannot be used to opposite engineer or coil out SQL Server database user accounts, role memberships or permissions.
• It cannot be used to reverse engineer or roll out SQL Server database properties like partial containment, mirroring, replication, AlwaysOn or recovery modes.

However, if you think nearly it, Liquibase was designed with the developer in mind, not the operational DBA. It excels when it comes to creating and maintaining database schema structures, not vendor specific configuration parameters.

Decision

By at present you should have a adequately expert idea near how Liquibase can help manage your database changes. The product is free and available for multiple operating systems, so in that location is actually naught stopping you from taking it for a test drive. As in the case of most new products, yous are ameliorate off starting small. In fact if yous are working with microservice architecture, it may but be the affair to test Liquibase with.

Microservice architecture is a software development model where a large application is made up of small, contained components with each component performing a specific chore. The components are independent in the sense that they tin be deployed separately without affecting any other part of the awarding. For example, a web site tin take one component taking care of user registration while another component can have intendance of password reset requests. Components communicate with each other through defined interfaces for delivering the overall functionality. And being independent, each component may use completely separate database or schema.

Microservice compages fits neatly with the agile development practice where modest changes are iteratively applied to an application. Since each component is small and lightweight, their database requirements are usually modest as well. For case, a component may need to admission only five tables in a database to store application session data. Managing a minor database'south frequent changes thus becomes extremely easy with Liquibase.

Having said that, there is nothing stopping you from testing Liquibase with large, monolithic database projects.

Next Steps

• Retrieve about minor databases in your organization that can benefit from refactoring. Even if their schema remains fairly static, you lot can generate their structure with Liquibase and save it for future reference.
• Talk with your awarding architects, software developers and DevOps engineers about existing deployment methods. Try to call up how Liquibase tin fit in there. You may be surprised to find that they would want to starting time using Liquibase.
• Once you are comfortable with Liquibase, introduce it to your colleagues and managers. If your role allows you to make decisions, start working on an enterprise-wide adoption.

Related Manufactures

Popular Articles

Nigh the author

Sadequl Hussain has been working with SQL Server since version 6.five and his life as a DBA has seen him managing mission critical systems.

View all my tips

Commodity Last Updated: 2016-07-12

jordanwenew1941.blogspot.com

Source: https://www.mssqltips.com/sqlservertip/4342/sql-server-database-change-management-use-cases-with-liquibase-part-3/

Share this post