SQL Server Performance

What are people using for Source/Version control in SQL 2005?

Discussion in 'SQL Server 2005 General DBA Questions' started by hominamad, Dec 12, 2008.

  1. hominamad Member

    I've been searching old posts and articles here and other sites. Everyone is talking about how important it is to have source control etc etc etc, but since I've been working with SQL Server (6.5), I have not seen an elegant implemenation or integration with any source control systems. With SourceSafe it was possible to do, although not too elegantly. At least you could set up your structures in VSS and then open your procedures and functions from within the VSS client, and it knew to open up in Query Analyzer.
    I'm working at a place now that is using TFS for their client app code. The version control integration with SQL Server is even worse than VSS. Here are some of the problems/issues I've come across:
    1. When opening a script from TFS, it doesn't know that it's a SQL file and only opens in Visual Studio - which has crappy or no color coding and is not easy to update the database from.
    2. I tried using the TFS plug in for SQL Mgmt Studio. I don't see the advantage. SQL Server projects are useless. It creates 3 folders for you, Queries, Connections and Miscellaneous, and you cannot edit or delete or change this structure at all. I want to have separate folders for Stored Procs, Tables, Functions, etc.
    3. The initial scripting of objects is a pain in the ass - slower than in 2000, and with bugs. If you want to script a stored proc as a check for existence then drop and create, it scripts out the proc as one big string with sp_executesql - getting rid of ALL color coding. Useless for creating scripts to stick into source control. It also takes hours to script out hundreds of objects. I know there are tools people developed on their own to do this, but come on - this is Microsoft.
    4. SQL Server projects in Visual studio are useless. They have all the same problems as above, and there's all kinds of bugs around adding files, etc. It limits how many you can add to the project at once and you have to go into the ini files and hack them.
    I'm really curious to hear how other people are doing version control - specifically with TFS. With 2000 and VSS it wasn't good at all, but at least usable. With 2005 and TFS it's barely usable if at all. I'm working with a team that has been doing things with no version control for 10 years and is happy with the way things are. Everyone agrees version control is a necessary thing to have, but in order for people to actually use it, it has to be easy, make sense, and no more complicated than it is now. Nobody wants to have to open 2 programs and 3 different windows just to edit a stored procedure.
  2. satya Moderator

  3. Andrew Novick New Member

    I've run into a lot of the same problems as you and don't really like any of the solutions that I've worked with for most of the same reasons that you've encountered. About the best that I've come up with are a couple of solutions that keep an up to date version of the database objects by scripting them out on a regular basis and then inserting any changed or new objects into VSS. I've used either dbghost or Red Gate toolset to make the directory trees. I think there may be some free tools to do that now. Then the check in is done with a NANT script. I used SQL Server Agent to run the whole thing every night and we at least had a record of what was in the development databases and when they go changed.
    I've also done it manually with VSS and Red Gate SQL Compare. The check outs are manual, so that can be a problem if you forget. Red-Gate will compare to a directory tree and script show you what changed. It becomes pretty easy to see what you've changed and then check in your modifications wether or not you've checked them out.
    Andrew Novick
    Novick Software
  4. hominamad Member

    Thanks for the reply Andy. I have done something similar to you at another job. We used to script out all the objects once a week, check everything out of VSS then check back in. It was nice to at least have a weekly record of everything that has changed. Even doing something like that though is a major pain in the ass with TFS - more than with VSS. First, scripting out all the objects as I said is much slower with 2005. Then checking everything out in TFS takes forever when you have as many obects as we do. I just can't believe that Microsoft hasn't come up with something for this by now. I've been dealing with this for years and thought for sure that the new Visual Studio with TFS would somehow take care of it.
  5. hominamad Member

    Does nobody else have any ideas for me? The link you provided Satya is helpful, but does not mention anything about TFS. It does talk about 3rd party tools though. Is anyone using any type of environment that has SQL Server and TFS for version control?
  6. satya Moderator

Share This Page