AdeptSQL Diff 1.96 Build 95

AdeptSQL Diff 1.96 Build 95 is a perfect and powerful tool for visually comparing and synchronizing two MS SQL Server databases. The program scans both databases and displays the schema tree view, with all differences color-marked. Navigating through the tree, you can see SQL definitions for individual objects (tables, view, stored procedures, etc) compared side-by-side and produce difference scripts for any choosen part or for the whole schema.

The program lets you execute the generated scripts either uninterrupted or step-by-step, set breakpoints, turn on transactions and rollback on errors. A unique schema matching engine generates the minimal non-destructive SQL for any schema changes you you need to synchronize, keeping your data safe. Starting with version 1.50, the program also includes DataDiff - a data comparison and synchronization module. The current version supports MS SQL Server 7, 2000 and 2005.

Major Features:

1. Support for partitioned tables and indexes: Previous Diff versions didn't recognize or script the partition clause in tables and indexes ('ON partition_name(part_column)'). This version does script this clause correctly, although it still does NOT display, compare or script definitions of partition schemes (PS) and functions. When re-creating partitioned tables or indexes, the Diff attempts to preserve the original partitioning in the target (rather than the source) database. More specifically:
   
   • Partition changes are supposed to be ignored. Make sure the "Ignore filegroups" option in "Options/Compare/Details to ignore" is checked! If it isn't, the difference in partition schemes will appear as filegroup difference, but the Diff won't synchronize it.
   • If a new partitioned table (or index) is scripted, and the same PS exists in the target database, it will be scripted as partitioned. If there is no corresponding PS on the target, the Diff won't try to create the PS, will report a warning and script the object without the "ON part_sch(col)" clause
   • When re-creating a table or index that was partitioned in the target database, the Diff will try to script it with the same partitioning as before. If the partitioning column is missing on the source side, the Diff will issue a warning and either substitute it with the partitioning column from the source side (if such exists), or will remove the partitioning.
2. Ignoring index names: An option has been added to ignore name changes for otherwise identical indexes. The "Options/Comparison/Statistics" page is now titled "Statistics and Indexes" and the new option is added there. The reason is that Diff has always had a similar option for named constraints, and index names as usually no more important than constraint names: as long as a table is correctly indexed, nobody would care about the index names.
   
3. Replacing unsupported data types: When synchronizing from a newer version of MS SQL Server to an older one (e.g. from SQL2008 to SQL2005), the Diff now recognizes column data types that are not supported on the target, issues a warning and scripts the affected column(s) with the closest alternative type. This applies to the following types:
   
   • TIME, DATE, DATETIME2 and DATETIMEOFFSET are all scripted as DATETIME when scripting from SQL2008 to any earlier version;
   • Any CLR type is scripted as VARBINARY(n) or IMAGE when scripting from SQL2008/2005 to SQL2000 or earlier;
   • XML columns are scripted as NTEXT when scripting from SQL2008/2008 to SQL2000 or earlier.
   • Please note the following limitations in Diff's handling of such situations:
   • CLR types GEOMETRY, GEOGRAPHY and HIERARCHYID are predefined in SQL Server 2008, however the Diff treats them as just regular CLR types and will script them as is into SQL2005 target server, even though the target won't recognize them. More generally, since the current version of Diff does NOT track loaded assemblies, it will script any columns of CLR types regardless of whether or not assemblies defining these types are loaded on the target server.
   • The Diff does not specially handle a situation when a column already exists on the target with the correct 'replacement' type. For example, if the left-hand database on SQL2005 has an XML column T.A and in the right-hand SQL2000 database the same column T.A is NTEXT, the Diff will still see the columns as different and try to sync them. Since XML columns aren't possible on the SQL2000 target, the Diff will script it as NTEXT, ignoring the fact that it is already NTEXT.
4. SQL2008 introduces a new type of permission, GRANT VIEW CHANGE TRACKING, which was not recognized by previous Diff versions. This permission is supported now.
   
5. GRANT formats unsupported on SQL2000: Starting from SQL2005, permissions can be applied to various types of schema objects using "object_class::name" qualifiers (as in "GRANT ... TO TYPE::MyInt..."). Previous versions of Diff supported this syntax, but didn't check if the target SQL Server version does. Now the Diff issues a warning and ignores those permissions if the target server is SQL2000 or older.
   
6. Permission types unsupported on SQL2000: There are several permission types that first appeared in SQL2005, such as CONTROL, TAKE OWNERSHIP and VIEW DEFINITION. Previous versions of Diff would script these permissions regardless of the target server. Now the Diff issues a warning and ignores those permission if the target server is SQL2000 or older.
   
7. There are changes in the "Schema/Permissions" schema view node, which displays database-level permissions grouped by principal. In previous versions, just principal name (e.g. "User1") was displayed in each sub-node, which made it easy to confuse with Principals subnodes. Now the display is changed to "To [principal]" for the subnodes and to "Database-level permissions" for their parent node, which makes the display visually distinctive. NOTE: if you access these nodes programmaticaly via Diff Automation objects, you might need to make changes in your scripts! The other change is that principals with only the standard GRANT CONNECT permission are not displayed there (but principals with revoked or denied CONNECT are displayed).

Requirements: Access to MS SQL Server 2000 or 7.0