Continuous Deployments for SQL Server – Part 4

Ok so we have seen in parts 1, 2, and 3 how to go about adding your database to source control, as well as comparing the schema and the data held within our SQL Server databases.

Its time to go about releasing changes made from one database to another (again think of you deploying changes from UAT to Production). There are several ways to go about releasing the changes, here are two of them:-

• Script the schema and data changes as 2 separate scripts, which you can easily combine yourself.
• Use Redgate SQL Packager and even create an .exe to run which will allow us to update the database.

Ok so let me demo how to go about using option two using Redgate SQL Packager which comes with the Redgate SQL Toolbelt.

Start up SQL Packager and you’ll see this start-up screen:-

I want to package an upgrade to a database so I have selected that option already, the next screen shows this:-

Above I have chosen my Database server and the database I want to use as the source and the target database (one we want to update).

Below shows the database objects I wish to package and apply on the target database.

Below shows the tables whose data I want to package and apply to the new database.

Below shows me the script that has been generated for me, first tab is the schema script, second tab is the data script and the third tab is for any warnings.

And the last screen gives us a choice to either package the change as a .exe, package it as a c# project, launch the script in SQL Query Analyser or Save the script for further inspection.

Choose option 1, run the exe and your database updates are complete, that’s all there is to it, any issues found the changes will be rolled back as they are transactional, leaving your database in tact.

Once complete, just run SQL Compare and SQL Data Compare and you can verify all is good – and viola, you’ve just updated production with schema and database changes, and you’ve been given a few different ways to do using RedGate SQL Toolbelt.