Page 59 - MSDN Magazine, November 2017

Figure 1 Compiled Scripts in the AdventureWorks.Database
backup available at bit.ly/2vPwu4N). As you can see, all the objects are put in as compiled scripts.
The seed data script (containing data needed for the application to function) is put in as non-compiled script and referred in the post-deployment script. Figure 2 shows this. If you are not aware of the significance of post-deployment scripts, I encourage you to read the MSDN Library documentation at bit.ly/2w12Iy4.
To ensure that the post-deployment script can handle incre- mental deployment, I’ve added a NOT EXISTS clause in front of all the INSERT statements. For example:
IF NOT EXISTS (SELECT 1 FROM [Person].[AddressType] WHERE [AddressTypeID] = 1) INSERT [Person].[AddressType] ([AddressTypeID], [Name], [ModifiedDate]) VALUES (1 N’Billing’, CAST (N’2008-04-30T00:00.000’ AS DateTime))
For the sake of simplicity and ease of maintenance, I’ll keep all the seed scripts on their respective files and refer them back in the post-deployment script.
I now have a project that will deploy the latest schema and seed data, at any point in time. It is also capable of performing an
incremental deployment on an existing database if the project is not introducing any breaking changes. However, the restrictions I mentioned at the start of this section come into play.
Finally, there is a bug that breaks the incremental deployment when a user-defined type (UDT) is changed. Unfortunately, the Visual Studio team has marked this bug as won’t fix, which means you’ll need to work around that. You can explore more details about the bug in the Visual Studio Developer Community entry at bit.ly/2w0zTBU.
Versioning
Just as you version any application you ship, it’s important to version the database, as well. Versioning
helps you keep track of the source code so you can easily keep tabs on the features, bugs and fixes made in every release of the soft- ware. If you’re not already familiar with versioning, take a moment and check out the article, “Semantic Versioning 2.0.0” at semver.org. It’s worth a read.
Before I get started, I have to address a challenge: SQL doesn’t actually come with a mechanism for versioning, so I’ll need to create one of my own. I’ll create a table called [internal].[Database- Version] to store the version detail, where “internal” is the schema of the table. It’s good practice to have a separate schema for all the database objects used for internal purposes (that is, they don’t participate in the actual business).
Figure 3 shows the schema I would propose for the table. You can follow your own pattern if you’re not convinced. Just keep in mind that we create versions to keep track of builds and releases.
Every time I make a change to the schema or check in a data migration script, I add a new version entry to the table, which serves
Figure 2 Post-Deployment Scripts msdnmagazine.com
November 2017 55