Apex SQL Diff 2008.07.0227

Apex SQL Diff 2008.07.0227 is a useful tool designed to analyze the differences in tables, procedures, views, users between two Microsoft SQL Server databases in seconds.

You will be able to visually observe the differences in the object's script shown side by side. Apex SQL Diff tool also allows you to programmatically synchronize the databases based on the differential analysis.

Major Features:

1. Support for SQL Server 2008 New Features
   • New Data Types: all new datatypes are fully supported and objects using the ones can be compared and synchronized without limitations
   • • Date and Time – simplifying your work with date and time data, providing increased date range, fractional seconds precision, and time zone support. The application can track and synchronize differences in the datatypes scale where it's applicable, for example, time(5) against time(7)
     • • Date – defines a date within the accuracy of one day
       • Time – defines a time of a day based on a 24-hour clock within the accuracy of 100 nanoseconds
       • Datetimeoffset – defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock within the accuracy of 100 nanoseconds
       • Datetime2 – defines a date that is combined with a time of day that is based on 24-hour clock within the accuracy of 100 nanoseconds: can be considered as an extension of the existing datetime type
     • Hierarchy – allowing you to construct relationships among data elements within a table
     • • Hierarchyid – represents position in a hierarchy and sets the desired relationship between rows
     • Spatial – predefined CLR data types in SQL Server allowing you to represent information about the physical location and shape of geometric objects
     • • Geography – represents data in a round-earth coordinate system and stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates
       • Geometry – represents data in a Euclidean (flat) coordinate system
   • Enhancements in indexes: all new properties of the indexes are fully comparable and can be synchronized without limitations as parts of their parent objects
   • • Spatial Indexes – allowing you to index a spatial column of geography or geometry data types
     • Relational (filtered) Indexes – optimized non-clustered indexes specially developed to allow working with queries from a well-defined subset of data using a filter predicate
   • Enhancements in column properties: the application can track differences in all new column attributes, for example, SPARSE NULL against NULL; all of them are fully supported and can be synchronized in either direction along with parent objects
   • • Sparse columns - ordinary columns that have an optimized storage for null values
     • XML COLUMN_SET - a column of this type is only materialized when selected, and will return you all the non-NULL sparse columns in a row as an XML BLOB
     • FILESTREAM varbinary (max) – the new way of storing BLOB data allowing you to store the data in the file system. Unlike columns defined with FILESTREAM attribute the ordinary varbinary(max) columns store their data in the tables.
   • Enhancements in procedure parameters and user-defined types: all the enhancements are fully supported and processed by the product
   • • User defined table type – allows you to define special types having table definition. Variables of such types can be used to store a result set for processing at a later time and passed as procedure parameters or used within the procedure bodies directly. The application detects differences in table definitions of these types, tracks dependencies on them to any object (for example, a procedure) and is able to synchronize such types and achieve their equality
     • Readonly parameters – indicates that the parameter cannot be updated or modified within the body of the procedure. The application can detect and synchronize differences in READONLY attribute seamlessly to other differences detected in the parameters
   • Data compression in tables, indexed views, indexes and constraints: differences in data compression can be tracked for any respective object and synchronized without limitations
   • • Data (Row and Page) Compression – allows you to compress whole tables, indexed views, indexes, primary key and unique constraints; can be configured for a whole table, indexed view and non-clustered index and for each partition in case of partitioned indexes or tables. ROW compression means storing fixed length data in variable length storage format and PAGE compression means minimizing the data redundancy
   • Change Tracking and Change Data capture: both features are fully supported; the application determines whether CHANGE_TRACKING is enabled or disabled for a table and can synchronize the detected changes making parent tables equal, also any calls to Change Data Capture procedures from within your objects are also be tracked, differences can be reported and synchronized and dependencies to a captured object can be taken into account on synchronizing with the dependencies
   • • Change Tracking – built-in server’s ability that allows you to answer the following questions: “What rows have changed for a user table?” and “Has a row changed?”
     • Change Data capture – this new SQL Server feature provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed. Changes are captured by using an asynchronous process that reads the transaction log and has a low impact on the system
2. Use Source Control labels and projects as data source
   • With the optional ApexSQL Source Control Add-in, developers can leverage the full power of popular source control systems like Subversion, Microsoft Team Foundation Server, Microsoft Visual Source Safe, and SourceGear Vault. ApexSQL Diff allows you to compare live databases against Source Control labels and projects.
3. Source Control Wizard
   • Source Control Wizard is a special 3 steps dialog that enables you to set up a connection to your Source Control system, map your database objects to the object scripts stored under the Source Control project and customize the way the database objects are exported to the Source Control system.
4. Compare SQL Scripts to other data sources
   • Directly compare SQL files residing in the Script folders or in the Source Control Systems to other data sources (live databases, snapshots, etc.).
5. Export Data Sources
   • The Export Data Source dialog will help you export a live database to a Snapshot, Script Folder, or even to a Source Control management system.
6. Manage Projects and view project information in one compact dialog
7. Filter by Difference Codes
   • The Difference Code Filter allows you more granular filtering of your comparison result set.
8. Generate richly detailed reports: ApexSQL Diff offers you several ways to view the comparison results in a well designed HTML Report. You can also use Printable version of the report.
9. Synchronize databases in one click
   • The Synchronization wizard is designed for “one-click” synchronization using default options.
10. Choose SQL Scripts to run before and/or after synchronization
    • ApexSQL Diff allows you to add custom Pre-Processing and/or Post-Processing Scripts to your synchronization script.
11. View script differences with your favorite file diff tool
    • ApexSQL Diff includes a powerful component for visually comparing script differences but the interface also allows integration with 3rd party stand alone file comparison tools like Compare it!. This allows you to view, print, browse, and even edit, the different structure scripts using the full power of a commercial 3rd party file diff tool.
    • Differences in the internal Difference Viewer
    • Differences in the 3rd party tool
12. Schedule and Run jobs unattended, overnight etc.

Enhancements:

• Improved support for Script Folder and Source Control datasources. Temporary database is no longer required for restoring the information from the script folders or scripts stored under the Source Control
• Added Default Collation property to specify default collation for Script Folder and Source Control datasources
• Added SQL Server Version property to specify script compatibility level for Script Folder and Source Control datasources
• Added "Script USE for Database" option to specify if "USE []" statement should be included in synchronization script
• New /collation1, /collation2 CLI switches to specify default collation for Script Folder and Source Control datasources
• New /compatibility_level1, /compatibility_level2 CLI switches to specify SQL server version for Script Folder and Source Control datasources
• New /no_scr_use_db CLI switch to specify the USE statement should not be included from synchronization script
• Tree view in Source Control Wizard to navigate between pages
• User interface of Login page in Source Control Wizard is specific for every Source Control system
• Last used Source Control settings in Source Control Wizard are retained for every Source Control system
• An ability to configure Folder Mappings for Script Folder datasource
• An ability to review Source Control Project Status (CheckOut status of all files in the source control project)
• Added DatabaseSettings.xml file for Script Folder and Source Control datasources that contains information about default collation and SQL server version
• Added new column with checkboxes to Folder Mappings form that allows to include or exclude types for export to or load from Script Folder and Source Control datasources
• Added new column with checkboxes to Folder Mappings page of Options form that allows to set default values for including or excluding types for Script Folder and Source Control datasources
• Fixes:
• • Incorrect processing of schema bound views on syncing the dependent object
• Changes:
• • GetLatestVersion/GetByLabel options are moved to Source Control Wizard
  • Removed /drop_temp_database1, /drop_temp_database2 CLI switches
• Known Limitations:
• • MSSCCI-compliant Source Control systems are not supported via the console (CLI).