The Freedom to Modify Databases
A couple of weeks back, AppDB.Studio 2019 was introduced at Microsoft Montreal SQL Server user group.
AppDB.Studio is a new SQL Server Management Studio Add-In that helps standardize and accelerate the development of Application Databases.
One of its key feature is the ability to apply changes to a Database without breaking its Application Dependencies, enabling Databases to become as malleable as versioned APIs.
Today we'll have a look at exactly how this works.
The “Can't change my model 'cause it will break Apps” Syndrome
We've all had to deal with it before. The team wants to change the “Company” table name to “Organization” but Developers refuse because they'll have to change all the Apps and SQL Scripts that use that table name, as well as re-test and redeploy them all. Too much grief for a simple renaming.
Databases are a Shared Resource in the Development Team, so now Architects, Devs, DBAs, BAs, QAs and PMs have to contend with an incorrect model.
But worry not! AppDB.Studio's Deprecated Placeholders to the rescue.
There are two types of Placeholders that are automatically generated by AppDB.Studio when you rename something or move an object to another schema.
The first type is for Object changes, and the second is for Column changes.
1 - Renaming Objects: Synonyms
When renaming Objects such as Tables, Views, Procedures and Functions, or change their schema, AppDB.Studio will automatically create a Synonym with the Original Object name that redirects to the New Object. Therefore, all queries in Applications or SQL Scripts that still use the Original Object name will continue to function, and all new development will use the New Object name.
Synonyms are a fairly obscure SQL structure, at least for most Developers I know, so the tool automates and simplifies its management by generating the SQL code for you.
2 - Renaming Columns: Umbrella Views
Renaming a Column is a bit messier, but AppDB.Studio makes it easy to navigate these waters.
When renaming a Column, AppDB.Studio will instead create an “Umbrella” View that will contain both Original and New Column names. Through the use of Aliases, both names will redirect to the Base Table's correctly renamed Column.
Both Umbrella View and Base Table will share the same Object name, but the Base Table will be pushed into a new Schema that indicates that it has been overridden by an Umbrella View.
Therefore, any preexisting Application queries or SQL Script that use the Original Table and Column names will be redirected to the Umbrella View instead. Because SQL Server View Rows can be Inserted, Updated and Deleted as with any Table Row, CRUD operations will continue to work unhindered.
The Base Table is only used to Model your columns.
How do you keep track of these Placeholders?
Well, AppDB.Studio marks Placeholders in Object Explorer with a Red Icon to indicate that the Placeholders shouldn't be used from now on. The Object Explorer Placeholders also display a new Tooltip that redirects users to the new Object.
Another Great Feature of AppDB.Studio is the capacity to optionally give an End-Of-Life date to the Placeholders, by default set to 365 days. So you have the choice to let the placeholders in the Database indefinitely, or give developers a Month, a Year or more to cleanup the Application Dependencies before you remove the Placeholders.
The Deprecation Settings are configurable in the Object Property menu.
In the Tools menu, you have access to a report that lists all Deprecated Objects in the Database, as well as their End-Of-Life date.
Finally, the Refactorings Menu holds a “Remove Placeholders” feature to help you cleanup these Placeholders in a single Batch operation whenever you're done with them.
What happens when you rename the same Object or Column multiple times?
AppDB.Studio deals with modifying existing Placeholders for you, so you don't have to worry about having to manually update existing Placeholders. Even if you don't select the “Generate Deprecated Placeholders” option, AppDB.Studio will automatically generate the SQL Code to update any preexisting Placeholders with your new changes.
For all intents and purposes, Synonyms are Object pointers and bear no performance hit compared to calling the Original Objects directly.
As for Umbrella Views, since the View code simply redirects to a Base table with some additional Column Aliases, any query Execution Plan will be identical to the one that would be generated if the Base Table was used directly, and that for all CRUD operations.
Bonus: SSDT RefactorLog File Generation
Freely modifying database objects is a great thing, but what happens when you deploy these changes with Visual Studio's SSDT Database Projects? How will SSDT link a Production Table with its renamed counterpart?
A file named RefactorLog can be created in all VS Database Projects that holds in XML form all refactoring operations that have been done in the Database since its inception. AppDB.Studio can optionally generate the RefactorLog XML code for you whenever you do a Database Change with it.
Nice, so what's next?
In our best impersonation of Mel Gibson, can we yell “FREEDOM!” yet?
AppDB.Studio is in version 1.1 as the time of this writing. So only a minimal amount of compliant Refactorings have been implemented yet, namely Renamings and Object Schema changes.
In upcoming versions, more and more Refactorings will support Deprecated Placeholders, such as:
- Move Column to another Table
- Split a Table
- Merge Tables
- Split a Column
- Merge Columns
The tool is following the seminal book Refactoring Databases by Scott Ambler and Pramod Sadalage, which contains dozens upon dozens of Refactorings.
So expect even more fun stuff from the AppDB.Studio team!
Étienne Thouin is the founder and CTO of SQLNext Software, a Montreal-based Startup specialized in building MS-SQL tools for Database Developers. He is a Technology Entrepreneur, a Web Platform Architect as well as a MS-SQL Database Architect with 18 years of .Net and SQL consulting experience.