Liquibase is a tool for managing database schemas (e.g. diffing schemas and writing migration scripts), and supports most major commercial and open-source relational databases. I’ll show how to use it to compare a migrated database to the original database, to find and fix discrepancies.
To use it with SQL Server, you’ll first need to download Liquibase and the Sql Server JDBC Driver.
Once you’ve done this, you can make a file called “liquibase.properties” where you’re running it – this avoids long command line arguments, and it’s really convenient when you switch from diffing to applying updates, since you don’t need to make any changes to the file later in the process.
Here’s my example properties file – obviously you’ll need to change the paths to fit your environment:
classpath=D:\\Software\\SQL Server JDBC\\sqljdbc_4.0\\enu\\sqljdbc4.jar driver=com.microsoft.sqlserver.jdbc.SQLServerDriver url=jdbc:sqlserver://new host:1433;databaseName=new database name username=new server user password=new server password changeLogFile=ChangeLog.xml referenceUrl=jdbc:sqlserver://old server host;databaseName=old database name referenceUsername=old database username referencePassword=old database password
Once you do this, you can run:
liquibase diff
This prints out a nice report, that tells you what problems it found. One of the risks of using such a tool is that it may not detect some class of difference that is material to your application, but this appears to be fairly thorough.
The nice thing about running a report is that it gives you an early read on how long this is going to take in the future when you run the migration for real (minutes, hours, days).
Unexpected Index(s): NONE
Changed Index(s): NONE
Missing Primary Key(s):
PK_ABExperiments_1 on ABExperiments(UniqueId, Experiment)
PK_AccountCredits on AccountCredits(AccountId)
Unexpected Primary Key(s): NONE
Changed Primary Key(s): NONE
Missing Schema(s): NONE
Unexpected Schema(s): NONE
Changed Schema(s): NONE
Missing Sequence(s): NONE
Unexpected Sequence(s): NONE
Changed Sequence(s): NONE
Missing Stored Procedure(s): NONE
Unexpected Stored Procedure(s): NONE
Changed Stored Procedure(s): NONE
Missing Table(s): NONE
Unexpected Table(s): NONE
Changed Table(s): NONE
Missing Unique Constraint(s): NONE
Unexpected Unique Constraint(s): NONE
Changed Unique Constraint(s): NONE
The previous step was optional. To get the real diff, the following command will make an XML document containing every difference:
liquibase diffChangeLog
Once you have this, you can simply run the update. However, before running the update, I removed the reference database from the properties file as a “belts and suspenders” tactic, although I’m sure it’s safe anyway.
liquibase update
Then you will get a series of failures (and I never promised this would be easy). Fortunately, it rolls back each time. You have to decide how to fix these in your case – while some of them may be defects in Liquibase, you’re more likely to hit problems with the database itself.
For instance, for me the migration I ran to build the new database didn’t bring across auto autoincrement fields, so I get these patches, which fail:
<addAutoIncrement columnDataType="int(10)" columnName="Id" tableName="Accounts"/>
The problem here is two-fold; Liquibase would need to know how to restart the auto-increment field, and even if that was solved, SQL Server doesn’t let you change something to an auto-increment. Dropping the column and re-adding it as an auto-increment is a poor option as well, because the table will have the columns out of order, potentially breaking code that relies on “SELECT *”. This alone indicates that I’ll need to re-migrate this database.
Fortunately each update is a single line, so you can (and should) delete the problem updates from the patch XML and re-run it- most likely you’ll hit several problems, but liquibase stops after hitting one category of problem, so it’s helpful to know how many failures you’re going to get.
I’m not sure why this happens, but Liquibase tries to set default values on my boolean columns, which fails:
Default value of 0 does not match defined type of boolean, D:/Software /liquibase-3.4.0-bin/ChangeLog.xml::1437835822256-139::gary (generated)
Liquibase also tried to re-create all my primary keys. This may be the difference between an “identity autoincrement” column, or just that the original migration changed the names of the primary keys. Fortunately this I can just remove entirely.
Unexpected error running Liquibase: The object 'PK_Accounts' is dependent on col umn 'Id'. [Failed SQL: ALTER TABLE [dbo].[Accounts] ALTER COLUMN [Id] [int]]
Liquibase also attempts to changing column types on many columns, but this fails because keys were already migrated:
<changeSet author="gary (generated)" id="1437835822256-137"> >modifyDataType columnName="Id" newDataType="bigint(19)" tableName="lcsk_Messages"/> </changeSet>
This may be an indication that Liquibase would be better to start the migration, then use a separate tool to migrate data.