A service from Red Gate

in
Home SQL .NET Exchange Opinion Blogs Forums About
Sign in | Join | Help
Home Blogs Forums
Blogs Home > Andras > SQL Compare 6.0 beta - read and sync to and from SQL scripts

Andras

Software Architect - Red Gate Software

SQL Compare 6.0 beta - read and sync to and from SQL scripts

Published Tuesday, May 15, 2007 3:53 PM

We have just released the first public beta for SQL Compare 6.0. The full version of SQL Compare 6.0 is scheduled for release towards the end of June 2007. For the forum and download information visit http://www.red-gate.com/MessageBoard/viewforum.php?f=65

 

So what is new in SQL Compare 6.0 beta?

SQL Compare 6.0 beta introduces a new data source. Until version 5.3 SQL Compare could compare and synchronize live SQL Server databases and SQL Compare schema snapshot files. Version 6.0 extends this with creation scripts. You can now specify a filesystem folder with creation SQL files. These are read in as a virtual database and can be compared with another database, or creation SQL files.

This is handy when you have some SQL scripts, and want to execute them in dependency order.

 

But in SQL Compare 6.0 you will also be able to synchronize to scripts. This means that if you develop on a live database, you can update a set of SQL script files that represent the database schema. This opens doors for source control for your database.

In addition to comparing and synchronizing to and from object-level SQL scripts, SQL Compare 6 contains many enhancements to its user interface. We have improved searching in the comparison results as well as in the detailed differences panels. We have also improved our project selection dialogs.

by András

Comments

 

Phil Factor said:

Does that mean that SQL Compare will just update the original source script to put in the changes, but will leave all the comments, documentation, insert statements and so on, intact?
May 15, 2007 12:41 PM
 

András said:

Depends on. In the cases of textual objects, like stored procedures it will preserve all the comments. In case of tables, if the table body needs to be changed, like a column needs to be removed, we do overwrite the table body, and comments will be lost. However, comments before the table definition will be preserved. Preserving the comments, layout style and exact syntax type (e.g. inline foreign key definitions vs. alter table statements) is not something that one can do at the moment.

SQL Compare it trying to be intelligent, so if you have many different objects in a single script, it will identify the location of the items that need to be changed, and will update only the relevant parts of the individual files.
May 16, 2007 6:14 AM
 

David Atkinson said:

I've just tried it with a table, which had one index and one constraint. I put comments before the table, after the table (before the index statement), between the index statement and the constraint statement, and after the constraint statement.

The comments before the table statement and after the constraint statement were preserved and the inbetween ones were lost. So as long as they're at the start or the end of the file it's fine! :)

David
May 17, 2007 11:04 AM
 

WebMister said:

This thing will update your working build scripts? Interesting.It could be rreeeaaaallllyyyy valuable and useful. It could solve problems for us where we need to occasionally synchronize build scripts when more than one developer is working on the database at once and they forget to check in/out objects.  I can think of a few possible snags. I put a whole lot of insert statements to build the required lookup data in static tables. Will it delete these insert statements? Also I put in all the scripting for SQL Server Agent to do with the database. Will it also perserve this?
Please say yes!
May 19, 2007 6:47 AM
 

András said:

Hi WebMister,
SQL Compare 6 will preserve the insert statements. If the table will need to be modified, the correct location will be identified for the modification, and everything else will stay intact in the script.
May 21, 2007 4:00 AM
 

David Atkinson said:

WebMister,

I would encourage you to download the beta and give it a go to see if it satisfies your needs. It would be great to get your feedback on the forum.

One thing to note is that SQL Compare will not execute the INSERT statements when you synchronize from scripts to a live DB.

David
May 22, 2007 4:08 AM
 

PBeale said:

Could this mean the end to the chore of Check-in/Check-out?

What about a set of  DDL Triggers which track every change to every database object and use it to (check out and) update the portion of the master script that has changed, using SQL Compare? I suspect that, if one also checks this into Subversion, it will work without tears, unless two developers are working on the same object To the source control system, it would just seem as if changes to the database were like a developer working lightning fast on the scripts.

I suspect it would have to cover 100% of the database objects and would have to manage the source control system almost entirely (excluding changes to comment blocks). One could verify occasionally that what was in Source control matched the database by using SQL Compare. How's that for automated source control together with almost all the comments. (I suspect that you guys will soon work out ways of preserving comments within views and tables when they are altered)
May 24, 2007 11:33 AM
 

David Atkinson said:

We have already considered using DDL triggers as a way of mapping live-DB editing to source control systems but we put it on hold for the time being. Most people still use SQL Server 2000 and could therefore not benefit from a DDL-reliant solution. In the meantime, SQL Compare 6 can be used to periodically keep the database and corresponding scripts in synch. For v6.0 you will still have to manually check out the necessary objects from your source control system, but we hope to automate this further by integrating properly with source control. If you have any further source control ideas or requirements, please let me know!
May 24, 2007 3:32 PM
 

Righthand blogs said:

Red Gate is preparing SQL Compare 6 product. The interesting thing about this version is that they are
June 9, 2007 2:19 AM
You need to sign in to comment on this blog

About András

András Belokosztolszki is the architect of SQL Compare versions 4, 5, 6 and 7, SQL Log Rescue and SQL Refactor. He is focused on database internals, database synchronization and database schema evolution.
  Opinion Home

  Opinion Pieces (94 articles)

  Geek of the Week (36 articles)

  Tony Davis - Editor (6 articles)

  Phil Factor (40 articles)

  Damon Armstrong (8 articles)

  Douglas Reilly (40 articles)

  Adrian Furnham (1 articles)

  Claire Brooking (1 articles)

  Richard Morris (32 articles)

  Tim Gorman (1 articles)

  Jesse Liberty (5 articles)

  Sarah Blow (1 articles)

  Anna Larjomaa (1 articles)

  Gaidar Magdanurov (2 articles)

  Regular Columnists (1 articles)

  Ben Hall (2 articles)

  Blogs Home
<May 2007>
SuMoTuWeThFrSa
293012345
6789101112
13141516171819
20212223242526
272829303112
3456789
Reporting on Mobile Device Activity Using Exchange 2007 ActiveSync Logs
 In this new column giving practical advice on all things Sys Admin related, Ben Lye takes on the often... Read more...

The Bejeweled Puzzle in SQL
 Alex Kozak provides another SQL puzzle to hone your SQL Skills with.  Read more...

Using Powershell to Generate Table-Creation Scripts
 For all of us who learn best by trying out examples, Bob Sheldon produces a PowerShell script file for... Read more...

Configuring Exchange Server 2007 to Support Information Rights Management
 In Exchange Server 2007, Information Rights management is easy to set up once you have set up the... Read more...

SQL Response: The dim sum interview
 Richard Morris met David and Nigel of the SQL Response team, in a dim sum Restaurant in Cambridge. They... Read more...