Two years ago I started supporting a Classic ASP application that used SQL 2000 for the data base. One of the complaints of the owner was that the previous person supporting the application did not keep track of program changes. The development environment was undocumented. A test system existed but it was in an unknown state with the production system. It looks like it was created from a restored backup. There was an old copy of SourceSafe and Visual Studio 2003 but I was pretty sure it was not being used. I could not find any commit logs. They had a source control system(SCS) but they had not used it. Initially I tried to like Sourcesafe and the Visual Studio 2003 environment but in a Classic ASP environment it does not bring a lot to the table. The work flow is slow and not very intuitive compared to Notepad++ and TortoiseSVN. Since this is a one man shop, I had some experience with Subversion, and we were not going to upgrade Sourcesafe, I installed Subversion, Notepad++, and TortoiseSVN. Next I synchronized the code for the production and development systems and made my initial load into Subversion. This was an adequate solution for the ASP, XSL, and XML files.
The data base documentation was not existent, too. So I used SQL Enterprise Manager to script all of the tables, stored procedures, functions and views. I loaded these files into Subversion, too. Within a very short period of time I adopted SQL Management Studio Express(SSMSE) as my testing environment for SQL changes. Here is where I ran into my first SCS conflict. SSMSE scripted the SQL objects slightly differently than SQL Enterprise Manager. I wanted to develop and test SQL changes using SSMSE but I wanted the ability to script all of the SQL objects using SQL Enterprise Manager at any time. I also found the scripts created by SQL Enterprise Manager to create tables were more trustworthy. My kludge solution was to manually apply the changes to the script files created by SQL Enterprise Manager every time I wanted to commit the changes. Using WinMerge this was not difficult but it was an extra step. I yearned for a more elegant solution.
On Friday I think I found it. It was not easy to find but it looks like I can make a significant upgrade to my development environment. I think I found it on the second or third Google page. The project resides on CodePlex.com and here is it’s description.
DBSourceTools is a GUI utility to help developers bring SQL Server databases under source control. A powerful database scripter, code editor, sql generator, and database versioning tool. Compare Schemas, create diff scripts, edit T-SQL with ease. Better than Management Studio.
Although it lists it’s status as beta, I installed it and scripted my data base without problems. Here are the features that attract me the most.
- You can script the entire database including the data. I have not checked out the data scripting yet.
- You can edit T-SQL in the same format as you used to script the entire data base if you use DBSourceTools as your editor. This should make it much easier for me to keep the source control system up to date.
- If everything works as advertised this should be a relatively easy way to deploy new development systems. I have been promising to deploy a development system with updated table data for over a year. I really like the idea of deploying new systems as a way to verify the integrity of your source control system.
If I can compare schemas and create diff scripts, that’s frosting on the cake. As a SQL development environment it looks very promising and the documentation is remarkably good for a new project, too. Although it is a pain in the butt I started renaming my SQL subversion files(e.g. .PRC to .sql) on Friday. This will take a long time since I am renaming the files via the repo-browser. The SVN client rename is not a rename. It adds the file to the repository and you lose the history.