SQL Server Performance

SQL Comparison - Script Generation

Discussion in 'Third Party Tools' started by Dave Wells, Sep 10, 2003.

  1. Dave Wells New Member

    I currently use Erwin as my database comparison tool (the SQL 7 version even though I'm using SQL 2K). I've used the SQL 2K version of Erwin in the past and from what I recall it suffers from poor script generation in the same way as the SQL 7 version. e.g. not taking into account permissions on stored procedures / dropping and rebuilding tables when it doesn't need to etc.

    I've now got to keep half a dozen or so different versions of a database up and running and am looking for a better comparison tool to aid in generating "version to version scripts". i.e. generate a script to take a database from one version to the next.

    Does anyone have any experience using the multitude of tools available?

  2. satya Moderator

    I think REDGATE have such tools to compare data and sql statements in SQL server, may checkhttp://www.red-gate.com/ link.

    Satya SKJ
  3. Dave Wells New Member

    I've looked at SQL Compare now and it is amazingly slow. I was stunned at how slow it is compared to Erwin (but then it is somewhat cheaper). Erwin compares a DB in under a minute and SQL compare was 15 minutes into its compare showing about 30% complete when I stopped it.
  4. DavidB New Member

  5. satya Moderator

    I would suggest to go with Redgate tools though its bit slower, but its reliable.

    Satya SKJ
  6. blockwood New Member

    quote:Originally posted by DavidB

    Check out the following from Lockwood Technologies -http://www.lockwoodtech.com/index_sqldiff.htm

    I have used both this tool and the redgate tool. Both are nice but I think this is faster.

    Hope this helps.


    David - thx for the "props". We have been aggressively updating SQLDiff this month and adding many new features. Current version is 2.7. In last week I have added HTML reporting and Data merge. Soon XML Export and command line interface.

    Investment in LockwoodTech tools is a good idea over time with free support and free upgrades.

    We have much more planned for SQLDiff - as we invest most of our $ in development and not ads - we rely on such word of mouth advertising like this to sell software. I appreciate your good words.

    Brian Lockwood
    LockwoodTech Software
  7. Dave Wells New Member

    I've looked at both the suggestions and would agree that they are both quite good. But the don't give me anything better than ErWin and I've already got that so I shall be sticking with it.

    Thanks for the suggestions.
  8. blockwood New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Dave Wells</i><br /><br />I've looked at both the suggestions and would agree that they are both quite good. But the don't give me anything better than ErWin and I've already got that so I shall be sticking with it.<br /><br />Thanks for the suggestions.<br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br /><br />I've used Erwin too but didn't think that it showed differences in database structure, including data. I used Erwin to model a db and create db creation and sometimes delta scripts.<br /><br />My products don't overlap with Erwin - I actually built SQLInserts to use in conjunction with Erwin to script database structures and data to archive, version and rebuild a db without backup file.<br /><br />I actually contacted this vendor awhile back about lack of data scripting in Erwin and offered to help out. That was 3 years ago and still waiting for answer ... [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Brian Lockwood<br />LockwoodTech Software<br /<a target="_blank" href=http://www.lockwoodtech.com>http://www.lockwoodtech.com</a>
  9. mipmip New Member

    Sure the "old" version from SQL Compare was very slow.<br />Received the new version 3.0 this month.<br /><br />Now the comparison is very fast due to a completly new architecture.<br /><br />The speed of the apps is no longer a reason against this software.<br /><br />Comment only for you information [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />Brgds...MipMip<br /><br /><br /><br /><br /><br /><br /><br /><br /><br />
  10. sb61 New Member

    All - It's been a few months since this posting started, but I'm catching up and thought I had a worthwhile reply for this.
    Our DBA group has been using Embarcadero's Change Manager to run database archives and compares for about the past year. It has been invaluable to us since we have a requirment to sync up databases on two different environments monthly, but we need to be able to identify and track the differences.
    I've been very happy with the flexibility Change Manager gives me when I'm setting up what I want to compare, and how quickly it runs.
    Here are the shortcomings I've found:

    1. I and others have logged this problem with Embarcadero. If you have a database where you have user defined roles assigned to other roles (for instance Developer role is a member of db_datareader), the tool does not return this as part of the archive of the database security.
    2. When you request an archive of database objects, the results are returned so that there is a separate file for each database. I would like to have the option of extracting one set of DDL for all the databases on a server (or the ones I have selected) but this doesn't seem possible. Also, when you extract the DDL for the database objects, the file does not begin with a "use databasename" statment, so if you want to string files together, you have to do this manually.
    3. I've had trouble with the scheduler in the tool and find it hard to use. I'll often schedule jobs to run at a certain times, and sometimes they kick off, and sometimes they don't.
    4. Once you've set up a compare for a database, there is no way to edit it. I would like to be able to change the parameters for compares I've set up and also copy compares, but there doesn't seem to be a way to do this either.

    Hope this is useful. Best Regards, SB
  11. dazza New Member

  12. Mavalot New Member

    We use SQL Effects Clarity from SQL Effects Software. This is a very nice (and fast, compared to the other one I tried) tool that gives you a good visual differences view.

    take a look athttp://www.sqleffects.com

    It actually creates two trees of the complete schema you are coparing, and keeps the trees in synch as you explore the differences.

    As far as code to synch up the schemas? We have a policy that all of our database objects must have a DDL script kept as a seperate script in our source code control - so we do this by hand and don't use a tool to gen a big script.

    For us - its all about knowing and verifying the differences, and this tool is perfect for that.



Share This Page