Options for Version Controlling Databases at Client Sites
I'm an accidental DBA of sorts and have spent most of my effort the last few years working on either BI related stuff or performance tuning stuff. Recently I was asked to take over the database updates and control our base-lining procedures. We already have a decent method in place for our current product, but we are planning to roll out a re-worked .Net version of our core application in the next year or so and I've been asked to investigate tools that can help handle the comparison and applying delta changes on-site once we roll out the new product. I've always been somewhat of a Red Gate fan, so I'm investigating the SQL Compare/Packager combo, as well as the Compare SDK, but some of my developers are harping on trying to do this via DacPac's, which i'm not familiar with at all, nor can i seem to find much info about it being used for off-site client database updates. I just wanted to get some opinions or ideas if we're heading down the right path or is there a better recommended approach that we should look into?
Disclosure: I work for Red Gate. SQL Source Control would be the primary tool you'd want to look at from Red Gate. It integrates between your source control system and SSMS. I've used both the Visual Studio Database Projects and SQL Source Control. I've automated both processes and I've used them both for multi-environment deployments. The strengths of the Microsoft offering are in a direct integration with the developers environment, Visual Studio, so they're more likely to use. The dacpac will work well for most changes, but can be problematic when you have breaking changes that my require special scripts that you can't really plug in. I also like they way it has pre & post deployment scripts that let you set things up or break them down as part of the deployment process. SQL Source Control on the other hand provides a more direct integration with the data pro side of the equation since it sits inside SSMS. You get the ability to customize changes through custom scripts, which allows you to make breaking changes nice and easy. We still don't have the pre & post deployment scripts. Either will get the job done. It's mainly down to how you are going to work rather than what you need to do. You may also want to look at a piece of software we're currently under development on, [Deployment Manager.] It goes way beyond both tool sets, but it's not quite cooked. :