View SQL database deployment script

Christo Pretorius

How can I view the SQL database deployment package (.sql) file? Part of the development and QA process requires that the DBAs review the deployment package. Is this possible through Deployment Manager/SQL Compare tools to look at the content of the release/package?

Regards

Brian Donahue

SQL Compare doesn't make a package so I am guessing the question is about Deployment Manager or SQLCi.exe or the DM add-in...

I'm not heavily involved with Deployment Manager, but last I checked, it used NuGet packages so you would use a NuGet package editing tool to get at and extract the contents. There was a "NuGet Package Manager" you could get from nuget.org. You can probably just unzip the package with WinZip, if I recall.

If you have any specifics, please let me know and I'll try to find out.

Christo Pretorius

Thank you for the reply Brian.

I'll take a look at the NuGet package editing tool. I did manager to determine that I can also use SQL Compare tool to run the version in SVN of the database against the current version (production) and get the difference listed. This does still have the problem that it is not exactly the same script in use by Deployment Manager, although it was created by the SQL Compare functionality (feel free to correct me if my understanding is incorrect).

Regards

jonathan.hickford

Hi Christo,

This is something that we want to improve to make it easier to see exactly what the changes are. Are you using the dynamic deployment mechanism?

Jon

Christo Pretorius

Jon

Just for the initial deployment as it seems like you can't get the non-dynamic deployment running without the initial dynamic sync process. We are not going to push the build through deployment manager to the production environment as it is a PCI restricted environment. The production deployment will be a manual process through the execution of the build script/s ... thus my requirement to be able to view and retrieve same, knowing that the release SQL script has been used and tested through the different layers of the DEV and QA environments.

Regards

jonathan.hickford

Hi Christo,

That first deployment is a bit tricky, it's something that we want to improve. One flow that could work is:

- Restore a backup of production into a test environment
- Deploy a package that represents the current production stage into test using the dynamic mode
- Ensure test and production are identical (using SQL Compare)
- Switch to static deployment mode in Deployment Manager
- Run a SQL CI build of the changes you wish to release to production, make sure that 'generate upgrade scripts' is set. That will generate an artifact that can update the version that is in test to the desired state, and will save an upgrade script in the Nuget package with the exact changes it will run on test.
- Deploy that change to test using Deployment Manager, ensure it works as expected
- Extract that upgrade script from the Nuget package
- Run that same script manually on staging/production as part of your release process

You can then be confident in exactly what Deployment Manager will run into Test, and you can use the same script in future deployments to staging/production.

Subsequent deployments are easier, as you will have a package that represents the production state so the first three steps are much easier.

Hope that helps

Jon

Christo Pretorius

Thank you for the detailed reply Jon. I'll test and then forward this process to our DEV and QA departments.

Regards