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.<\/p>\n
To use it with SQL Server, you’ll first need to download Liquibase<\/a> and the Sql Server JDBC Driver.<\/a><\/p>\n 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.<\/p>\n Here’s my example properties file – obviously you’ll need to change the paths to fit your environment:<\/p>\n Once you do this, you can run:<\/p>\n 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.<\/p>\n 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).<\/p>\n The previous step was optional. To get the real diff, the following command will make an XML document containing every difference:<\/p>\n 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.<\/p>\n 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.<\/p>\n 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:<\/p>\n 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.<\/p>\n 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.<\/p>\n I’m not sure why this happens, but Liquibase tries to set default values on my boolean columns, which fails: <\/p>\n 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.<\/p>\n Liquibase also attempts to changing column types on many columns, but this fails because keys were already migrated:<\/p>\n This may be an indication that Liquibase would be better to start the migration, then use a separate tool to migrate data.<\/p>\n","protected":false},"excerpt":{"rendered":" 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 … <\/p>\n\nclasspath=D:\\\\Software\\\\SQL Server JDBC\\\\sqljdbc_4.0\\\\enu\\\\sqljdbc4.jar\ndriver=com.microsoft.sqlserver.jdbc.SQLServerDriver\nurl=jdbc:sqlserver:\/\/new host:1433;databaseName=new database name\nusername=new server user\npassword=new server password\nchangeLogFile=ChangeLog.xml\n\nreferenceUrl=jdbc:sqlserver:\/\/old server host;databaseName=old database name\nreferenceUsername=old database username\nreferencePassword=old database password\n<\/pre>\n
\nliquibase diff\n<\/pre>\n
\nUnexpected Index(s): NONE\nChanged Index(s): NONE\nMissing Primary Key(s):\n PK_ABExperiments_1 on ABExperiments(UniqueId, Experiment)\n PK_AccountCredits on AccountCredits(AccountId)\nUnexpected Primary Key(s): NONE\nChanged Primary Key(s): NONE\nMissing Schema(s): NONE\nUnexpected Schema(s): NONE\nChanged Schema(s): NONE\nMissing Sequence(s): NONE\nUnexpected Sequence(s): NONE\nChanged Sequence(s): NONE\nMissing Stored Procedure(s): NONE\nUnexpected Stored Procedure(s): NONE\nChanged Stored Procedure(s): NONE\nMissing Table(s): NONE\nUnexpected Table(s): NONE\nChanged Table(s): NONE\nMissing Unique Constraint(s): NONE\nUnexpected Unique Constraint(s): NONE\nChanged Unique Constraint(s): NONE\n<\/pre>\n
\nliquibase diffChangeLog\n<\/pre>\n
\nliquibase update\n<\/pre>\n
\n<addAutoIncrement columnDataType=\"int(10)\" columnName=\"Id\" tableName=\"Accounts\"\/>\n<\/pre>\n
\nDefault value of 0 does not match defined type of boolean, D:\/Software\n\/liquibase-3.4.0-bin\/ChangeLog.xml::1437835822256-139::gary (generated)\n<\/pre>\n
\nUnexpected error running Liquibase: The object 'PK_Accounts' is dependent on col\numn 'Id'. [Failed SQL: ALTER TABLE [dbo].[Accounts] ALTER COLUMN [Id] [int]]\n<\/pre>\n
\n<changeSet author=\"gary (generated)\" id=\"1437835822256-137\">\n >modifyDataType columnName=\"Id\" newDataType=\"bigint(19)\" tableName=\"lcsk_Messages\"\/>\n<\/changeSet>\n<\/pre>\n