Need a good version control for SQL and Crystal Reports

Question

We have several large data stores where we warehouse data for metric reporting. One is SQL 2000 and the other is 2005. We use Crystal Reports 11 as our reporting tool.

Over the past several weeks we've had a couple very visible reports "crash" due to changes in the Db or changes within the reports.

To minimalize these errors I am looking into putting the scripts for our databases and Crystal reports into some sort of version control. Can someone point me the right direction on how I can get these assets under some sort of version control? We do have subversion in our company, would this work?

Answer 1

This:

http://stackoverflow.com/questions/468703/source-control-and-stored-procedures

and

http://stackoverflow.com/questions/115369

are a good start.

Answer 2

You can use standard version control tools for Crystal Report files. However dealing with the databases is a bit more difficult.

Visual Studio Team System 2008 Database Edition (Data Dude)

You can use this version of Visual Studio to manage your database, the definition of the database tables, views, stored procedures, functions, etc are stored as create scripts (as if you were starting from a blank DB). The visual studio functions then will create a database differential (Schema Compare or Data Compare) and generate the scripts that would be required to go from one version of the database to another (i.e. between DEV and TEST instances).

The database definitions are what gets put into version control (so you can see at any point in time what the database looked like) and Visual Studio fills in the rest by generating the proper scripts to go from one version to another.

The Hard Way

Keep track of your databases, scripts that modified the database and the migration pattern. If you wanted to get to a version of the database, you would start with an empty db, then run each script in succession until you reached the desired version of the database.

This is basically what Ruby on Rails does when using the db_migration features, it however can go backwards through versions if you coded the migration files correctly - but I assume you are working with .NET on Windows.