Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Article: Software Review: SQLCompare

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-04-27 : 13:30:35
I've reached that horrible stage in a project. We're still doing development but the users are in an extended alpha/beta test of the software. And they don't like to lose the data they've entered. That means as I fix bugs and add new features I need to keep our development database in sync with our test database. I've been using Red Gate's SQLCompare to manage this and I thought I'd tell you a little about how it's going.

Article Link.

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-04-27 : 13:39:59
I use the older version 2.x of SQL Compare and love it! I hear the new version is much faster than the 2.x versions, and that is the only issue I had with it. It's fast enough, but faster is always better :)

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-27 : 13:42:58
Michael, you should upgrade! It would take about 5 minutes on one of my databases to do the compare. It now takes about 10 seconds with the new version. I typically use my own change scripts to update the other environments, but sometimes there is an environment that is several versions behind so it is faster to use SQL Compare scripts than to figure out what version the database is plus dig up all of those scripts.

Tara
Go to Top of Page

jasper_smith
SQL Server MVP &amp; SQLTeam MVY

846 Posts

Posted - 2004-04-27 : 14:32:55
The speed rocks in the latest version! Another feature that is great is the ability to automate the comparison (does require the SQL Toolkit). I think this is a great addition to the range and extremely useful [url]http://www.red-gate.com/sql_comparison_and_synchronization_toolkit.htm[/url]


HTH
Jasper Smith
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-04-27 : 14:57:27
I wish I could afford the upgrade :(
Accounting doesn't understand why I need to upgrade something that works just so that it's "faster".

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-04-27 : 19:40:00
quote:
Originally posted by MichaelP
Accounting doesn't understand why I need to upgrade something that works just so that it's "faster".



Do you have an internal billing rate ? Show them a spreadsheet with number of minutes a month you spend watching SQL Compare work



Damian
Go to Top of Page

SamC
White Water Yakist

3467 Posts

Posted - 2004-04-27 : 23:02:42
I don't use SQL compare and I'm guessing at the application....

Anyone use it to revise a test database and deploy the revisions to the production database?

Sam
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-04-27 : 23:16:52
I have done that.
Now I just follow a really strict routine of scripting everything so I know what has and hasn't been applied on a specific database.

It's great when you come into a new situation and have to figure out someone elses mess.



Damian
Go to Top of Page

csuire
Starting Member

2 Posts

Posted - 2004-04-29 : 10:36:32
Graz (or anyone who knows),

In the article you say, "I have a script that generates all my procedures, views and functions in the proper order."

How do you programmatically generate them in the proper order? I’ve run into the problem before when adding stored procedures of getting a warning message of something like, 'Unable to update dependency tables. An object this stored procedure depends upon is missing. Adding stored procedure anyway.'

Thanks!
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-04-29 : 10:41:37
I know (I wrote the code for Graz ).
It uses SQLDMO, but instead of using the database objects to script directly, you use the Transfer object to handle your scripting and it handles the order. This is what Enterprise Manager uses when you script a database. You might have to play around with some of the options to get the result you want.

I plan to write an article on this (and include the C# code I wrote the tool with) when I get a chance.




Damian
Go to Top of Page

csuire
Starting Member

2 Posts

Posted - 2004-04-29 : 11:11:47
I'm looking forward to that article and code sample.

Thanks for the information.
Go to Top of Page

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2004-05-10 : 23:02:22
SQLCompare Rocks!

Just bought it today, and lovin' it!

Sarah Berger MCSD
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-05-25 : 11:26:21
I had a go with the DEMO. Couple of things I didn't seem to be able to do (Data Compare), please enlighten me if there are options to cover it:

Display a column but fo NOT include it in the comparison - e.g. UPDATE date which may be different on two databases but the row otherwise is NOT different

Use the PK's from one database as the basis of comparing with another database (which has DIFFERENT PKs or NO PKs - this is common for us; we use a LINKED SERVER to do a SELECT * INTO (LocalDatabase&TableName) FROM (Remote) and then want to do a compare locally.

Exclude a column from a compare one I can see the compare - seems like I have to re-edit the "job" and then re-run the whole thing (rather than just, say, right click a column of displaye data and say EXCLUDE or somesuch, and have that one table be re-compared)

Other than that it is a very nicely laid out application.

Kristen
Go to Top of Page

JellyRoll
Starting Member

8 Posts

Posted - 2004-05-26 : 07:38:53
I think you could solve the linked server comparison by using Indexed Views and comparing those instead...

Brian
Go to Top of Page

dazza
Starting Member

5 Posts

Posted - 2004-05-27 : 15:31:54
There are a lot of 3rd party tools available for SQL Server these days. Check http://www.aspfaq.com/show.asp?id=2209 & http://www.aspfaq.com/show.asp?id=2495. It depends on your need for such a tool, but a couple of things I found important when choosing our tool. How well are SQL object dependencies handled? Many vendors still don't appear to have this right. Does it enable a vastly improved process for database change management? If you are involved in database development, then I believe it is essential to implement an end-to-end approach from source control to upgrades to deployment. I've got a whitepaper that provides an overview of automated database change management, if anyone is interested.

Darren
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-05-28 : 03:00:55
quote:
Originally posted by dazza

There are a lot of 3rd party tools available for SQL Server these days. Check http://www.aspfaq.com/show.asp?id=2209 & http://www.aspfaq.com/show.asp?id=2495. It depends on your need for such a tool, but a couple of things I found important when choosing our tool. How well are SQL object dependencies handled? Many vendors still don't appear to have this right. Does it enable a vastly improved process for database change management? If you are involved in database development, then I believe it is essential to implement an end-to-end approach from source control to upgrades to deployment. I've got a whitepaper that provides an overview of automated database change management, if anyone is interested.

Darren


I agreed, and I'd be interested in your white paper.

We've wound up making a Home Grown tool to get closer to just what we need (which does take into account FKey dependancies).

Our application is highly customised per user, so I'm faced with trying to deploy changes without breaking customer-specific enhancements. This applies also to data (fully content managed web site / ecommerce etc. based on data returned from SProcs and so on, thus lots of Meta data involved too, some has our "ownership", some has client ownership - which they may or may not have changed, and may or may not want to replace with changes we have introduced.)

We have systems of GUIDs in records which are then recorded in tables of "ownership"; this includes attributing "assets" to application modules - allowing us to decide what assets should be shipped to a client, and who the owner is etc.

We then have an SProc that does a table-by-table comparison with some fairly slinky Web presentation stuff that allows review of differences, and decisions on what gets upsquirted etc.

These tools are also used in ensuring that the move from Staging to Production is safe - i.e. no emergency fixes made to Production that have not been reversed into Dev/Staging etc.

But its pretty much a nightmare, and I've got a large amount of development time allocated in 2nd half of this year to making it a whole heap more automated.

Kristen
Go to Top of Page

dazza
Starting Member

5 Posts

Posted - 2004-06-01 : 09:48:19
Kristen,

Your situation sounds quite complex, but it should be possible with the tools available to automate your processes a lot more. For example, you could package up your required changes with the tool, deploy it to your customers and have it dynamically apply upgrades. Send me an email and I'll reply with the whitepaper.

Darren
Go to Top of Page
   

- Advertisement -