SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Best Schema Comparison/Scripting Tool
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 11/27/2006 :  16:37:18  Show Profile  Reply with Quote
My current project has just rolled out its 20th build.

I am sick and tired of schema management.

I am looking to buy one of these for my own personal use.

So my requirements are:
1) Cheap! (< $500 US)
2) Easy to use
3) Supports Data and schema comparison
4) Source control (a minor point for me...)

I see three products:

DBGhost
Red-gate SQL Compare
Idera

I am interested in opinions from those who have used any of these in a production environment...

TIA

DavidM

Production is just another testing cycle

Kristen
Test

United Kingdom
22431 Posts

Posted - 11/28/2006 :  00:19:00  Show Profile  Reply with Quote
"I am sick and tired of schema management."

How are you currently doing it?

We script all changes to DDL into sequentially numbered "patch" files.

Then we apply them, in order, from whatever version the target database is already on.

A chum of mine stores each SQL statement in a table, and then exports them as XML to update his application - even more automated than our approach.

Kristen
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 11/28/2006 :  00:59:17  Show Profile  Reply with Quote
>>How are you currently doing it?

Very similar to your process..
Write script, save as file with datestamp on the end...

The problem is that the schema is about to branch...

DavidM

Production is just another testing cycle
Go to Top of Page

peterlemonjello
Yak Posting Veteran

53 Posts

Posted - 11/28/2006 :  08:19:26  Show Profile  Reply with Quote
Check out ApexSQLDiff
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 11/28/2006 :  09:38:13  Show Profile  Reply with Quote
David: Do you think a Comparison tool is what you want?

I'm curious because I'm very wedded to doing everything in script files.

However, we do have tables [containing "Meta Data" if you will] in our database which are a "super-set" of the database's own definitions, and we could:

a) Upload the latest Meta Data
b) Generate script to get from Old State to New State
c) Run script

Not quite the same as using Red Gate Compare etc., I dare say, but might give me some fine control over the process.

We too have circumstances where some clients have modules, or client-only extensions, so we have to cater for those, and our Meta Data includes an Asset/Resource table which helps define which parts of the system the client is entitled to.

I presume your scripts are in some sort of Version Control, so you could cope with branching the scripts if you had to?

Kristen
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 11/28/2006 :  16:01:02  Show Profile  Reply with Quote
>>David: Do you think a Comparison tool is what you want?

I think so :-)

Basically, I want to be able to hack away at the source db schema and then at the end of the day, run a comparison against the target schemas (branched and normal)....

Thanks Peter, I'll give APEXSQLDiff a trial..

On a side note, this is what I have found in installing the 3 tools...

1) DBGhost - Installed OK, Interface is average, Compare was fairly fast...

2) Red-Gate - Installation OK, but it then said the trial has expired! I have the free version of SQL Prompt so maybe there was a clash... Not impressed!

3) Idera... It tried to install the .NET 2 framework almost silently.. NOT IMPRESSED! Deleted it straight away....

DavidM

Production is just another testing cycle
Go to Top of Page

mwjdavidson
Aged Yak Warrior

United Kingdom
735 Posts

Posted - 11/29/2006 :  05:29:51  Show Profile  Reply with Quote
I believe that DBGhost is the only one of the three you mention that will integrate with source control. Also, I've heard they're currently in the process of updating the UI...

Mark
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 11/29/2006 :  08:03:38  Show Profile  Visit spirit1's Homepage  Reply with Quote
i just saw this:
http://www.rollsql.com/

it might be interesting to see if it works for you.



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 11/29/2006 :  09:10:53  Show Profile  Reply with Quote
Personally I would want something that runs each "block" of SQL and checks for error, and if any rolls back.

I would also need "checkpoints" - so once it gets to a Checkpoint it can commit the work so far.

We have things like creating a Unique Index. We know the data is supposed to be Unique, but sometimes something out in the wild catches us out. If we just run the script has run on, and there is no longer any transaction, the database will be mucked up and we'll have to restore. I'd prefer the thing running the script to catch the error at that point and roll back.

Kinda rules out OSQL I reckon ...

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000