My company has just gone though its annual review process, and I have finally convinced them that it is time to find a better solution to manage our SQL schema/scripts. Currently, we only have a few scripts to manually update.
I've worked with VS2008 Database Edition at a different company and that is an awesome product. My boss has asked me to take a look at SQL Compare by Redgate and to look for any other products that may be better. SQL Compare appears to be a great product also. However, it does not look like they have support for Perforce.
Have you used a variety of products for this?
What tools do you use to manage SQL?
What should be included in the requirements before my company makes a purchase?
I'll link to other answers I gave to other questions related to Source Control for databases
Is RedGate SQL Source Control for me?
Exposing SQL Server database objects as files in a file system
I'm in the "script it yourself" camp, as third-party products will only get you so far at managing database code. I don't have one script per object, because objects change over time, and nine times out of ten merely updating my "create table" script to have three new columns would be inadequate.
Creating databases is, by and large, trivial. Set up a bunch of CREATE scripts, order them properly (create database before schemas, schemas before tables, tables before procedures, called procedures before calling procedures, etc), and your done. Managing database change is not nearly as simple:
Essentially, what I have is a CREATE script for each database, followed by a series of ALTER scripts as our code base changes over time. Every script checks whether or not it can be run: is this the right "kind" of database, have the necessary prerequisite scripts been run, has this script already been run. Only when the checks are passed will the script perform its changes.
Tool-wise, we use SourceGear Fortress for basic source control, Redgate SQL Compare for general support and trouble-shooting, and a number of home-grown scripts based on SQLCMD for "bulk" deployment of the alter scripts to multiple servers and databases and to track who applied what scripts to which databases at what time. End result: all our databases are consistant and stable, and we can readly prove what version any one is or was at any point in time.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With