SchemaDrift – Database Schema Differences – My Weekend Project
This weekend I had some spare time, so I started coding on a new project on Friday night. This project is something that I intend to include in the next release of Database Health Monitor. THis project allows users to compare the database schema between two SQL Server databases.
As you read this, consider two questions that I will ask again at the end of the post, I would really love some feedback:
- Would you use this?
- Would you be interested in helping me test it out as an early release?
This was inspired by working with clients who need to compare the schema between different SQL Servers, either between multiple production servers with the same schema, or between test and production servers.
I know there are other tools out there that do this, but its not easy to convince a client to purchase 3rd party tools, so I am left not having a good tool to do this.
This is just a start, I still have a great deal of work to do, but its the foundation for me to build upon.
How it works:
- Connect to a SQL Server and choose a database to compare (known as the source).
- Connect to another SQL Server and choose another database to compare against (known as the destination).
- Click the compare button.
- The schema is then compared between the two servers (Tables, Sprocs, Functions, Users, Indexes, etc…)
- Results are sorting into 4 categories.
- Matched between the two servers.
- Exist on both servers, but something is different.
- Only on the Source Server.
- Only on the Destination Server.
- You can the browse the code behind those objects, and see the differences.
Database Schema Drift The Demo:
To start with we just run the program.
Next we click the connect button under the Source Server section. For the first server I am going to connect to a SQL Server 2008 R2 instance with SQL credentials.
Next I am connecting to the destination server, a SQL Server called costarica using Windows Authentication. costarica happens to be running the latest SQL Server 2016 CTP release.
Once connected to both SQL Servers, the next step is to pick the database that we want to compare. For fun, lets see whats different between msdb in SQL Server 2008R2 and SQL Server 2016 CTP.
That’s it on the settings. Now we just click the Compare button and while the comparison is running, the status panel is updated with details on what is being compared.
Once the comparison is complete, we use the tabs across the top of the application to switch between Identical, Different, Only in Source, and Only in Destination. Here we can see the objects that are identical between these two databases.
Now we take a look at the Different tab to see what exists in both databases, but is different some how. In the example below we can see that the table dbo.backupmediaset is different, and that the difference is the [is_encrypted] column at the end of the table.
We can also look at items that are only in the source database, like the dbo.sysdtscategories as shown here.
And what only exists on the destination like the table dbo.autoadmin_system_flags.
This is just a start, there are many additional features that I plan to add.
I would really love some feedback:
- Would you use this?
- Would you be interested in helping me test it out as an early release?
Please post a response below. If you would like to try this out, just let me know. I will be working on this over the next several weeks.
-Steve Stedman
Related Links:
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Yes and yes. Let me know what I can do.
Hi Steve,
I think that this it is a great idea to control differences between pre and production. It also helps on monitoring for any unscheduled change on production systems.
I have been working on a similar solution but using the SQL package feature. It basically generates a DACPAC file and then compares it to a previous baseline file generating a diff schema xml. Then checks if this diff schema is empty (no changes):
“C:\Program Files\Microsoft SQL Server\120\DAC\bin\sqlpackage.exe” /a:Extract /scs:Server=SERVER1;Database=MyDb; /tf:”J:\work\MyDb_capture_%date:~-10,2%%date:~-7,2%%date:~-4,4%.dacpac”
“C:\Program Files\Microsoft SQL Server\120\DAC\bin\sqlpackage.exe” /a:DeployReport /sf:”J:\work\MyDb_baseline.dacpac” /tf:”J:\work\MyDb_capture_%date:~-10,2%%date:~-7,2%%date:~-4,4%.dacpac” /tdn:MyDb /op:”J:\work\schema_compare_%date:~-10,2%%date:~-7,2%%date:~-4,4%.xml”
FC “J:\work\empty_schema_compare.xml” “J:\work\schema_compare_%date:~-10,2%%date:~-7,2%%date:~-4,4%.xml”>NUL && (del “J:\work\schema_compare_%date:~-10,2%%date:~-7,2%%date:~-4,4%.xml” & del “J:\work\MyDb_capture_%date:~-10,2%%date:~-7,2%%date:~-4,4%.dacpac” & @ECHO “No changes detected”) || @ECHO “Files are different or runtime error”
I hope this helps you.