I know it has been forever since I last blogged, and the slew of comments I have gotten in the past few months has deterred me from posting. Apparently, there are thousands of companies out there who think I have a "Great blog! Keep up the good work! By the way...please [get a new mortgage/enter our sweepstakes/buy our ultimate diet plan/etc]." Maybe I need to start paying someone to host my blog so I will keep out the riff-raff.
Anyway, Mike Hodnick recommended a database tool to me today that I am in the process of testing. It is very cool. I have not used other SQL Diff tools, so I am a first-timer. This thing rocks! I ran a diff on my production database and my development database. Not only did the tool tell me about the schema changes, allow me to choose which changes I wanted to resolve, and allow me to choose which database should get altered, it also brought some issues to my attention in the production environment of which I was unaware. For some reason, my foreign keys were missing. The tool generated a script, which I reviewed and then ran against my production database. I did have to clean up some data due to the missing keys, but now I have a quality production database that matches my development environment. I love it!
The tool is called SQL Compare and is sold by Red Gate. By itself, the compare tool cost $295, but you can download a 14-day trial. Check it out.
Subscribe to:
Post Comments (Atom)
10 comments:
I've heard good things about SQL Compare. That being said, I've been pretty happy with the tool DB Ghost from Innovartis.
Can you tell me the price range of buying DB Ghost?
Just their schema compare product is $195, but if you purchase the professional edition, it contains the Scripter, Builder, Schema and Data Compare tools, all for $350, which is only a little bit higher than the Red Gate tool.
One thing that I believe is fairly unique about the DB Ghost tool is that it is very wizardy. The only options for using it are in this "wizard" mode, or from a command line. That makes it very easy to use at first, but may make it more annoying as time goes on. Also, it focuses on using database scripts not as just an end result, but also as using them as the basis for identifying differences between databases.
By the way, you might look at some of the Blogger settings to reduce the comment spam you are getting. I know it is preferable to have unmoderated comments, but moderated comments would at least (I think) keep the crap off your blog.
Part of what I liked about the Red Gate tool is that I could have a fair amount of control over what was and was not synchronized. I saw the change script before it was executed and could save it out and edit it as I wished. Does the wizard in DB Ghost limit your control?
I was considering not allowing anonymous comments to my blog. I just hate to do that, because I don't want anyone to walk away from making a comment because they don't want to log in. I guess that would be preferable to ditching the blog all together.
As you walk through the wizard, DB Ghost let's you see the differences, and selectively choose to apply them. It also will give you a second shot at them if they didn't apply properly the first time. Like, for example, it may show you that a stored procedure is out of sync, but when you try to apply the changes, it fails because it references a table that doesn't exist yet. But then, when it gets to the part where it flags the new table as a difference, and applies that change, it will bring you back to the stored procedure and ask if you want to try again.
I think the thing that annoys me with DB Ghost is that sometimes I want to do things at a more granular level, and it wants to always start at the database level. But it seems to handle dependencies much better than the built-in SQL script generator. Also, it is designed to let you make changes to CREATE scripts instead of worrying about ALTER scripts all the time. Here's what I mean: I can generate scripts from DB Ghost that contain CREATE TABLE / INDEX / WHATEVER statements. That is now my base database script, and I can check that into source code control. If I want to add a column to a table, I can check out the script for that table, and make the change to the script. DB Ghost can use that modified script for comparison to a database. That way, I know I can have a set of scripts that I am maintaining that will completely create a blank database. (I can also add scripts for populating look-up table, if I like.)
It's worth checking out the entire RedGate SQL bundle. It also includes SQL Data compare (so you can synchronise your dev or UAT databases to contain live data), DTS compare, and SQL Packager (to script an entire db into an executable or C# project).
SQL Compare itself allows you to take a snapshot of a db, so that you can compare the db with how it was last release, for example. Great for versioning.
Finally, it's written in .Net; the package comes with a toolkit that exposes the API, allowing you to automate everything.
Nifty little package in my book.
(I don't work for RedGate by the way! lol).
Pete.
I've used SQL Compare, and it is indeed a good tool. The only problem is that I already had SQL queries built that I used for 90% of the functionality I was using in SQL Compare. Comparing table structures, INDEX structures, etc. is so basic it is hard to justify the price for multiple seat licenses.
You may want to see Kentico Compare SQL - it's very fast and costs only $49!
http://www.comparesql.com
Hi Valerie -
Red-Gate's tool is certainly a very good tool. But as a developer of a competing tool - I have an honest question - do you find that you really do use the tool for the synch script capability or is it more important to quickly and easily get a good view of the differences? Many shops maintain individual scripts in source control for each object and prefer to make their changes by hand. Our tool - SQL Effects Clarity (http://www.sqleffects.com) focuses on the best possible graphical and interactive view of the schema differences.
What are your thoughts on the subject?
Regards,
Daniel Alba
Post a Comment