AdeptSQL Diff Online Help - Supported versions of MS SQL Server

We extensively test the scripts generated by the Diff in various scenarios. Before each new update is released, it must pass all applicable tests on each of the SQL versions listed above.

At the moment our internal test set contains over 830 individual tests, although naturally not all of them apply to earlier versions of the SQL server.

Note: starting from Diff 1.96, we are phasing out support for SQL 7.0. Which means that normally everything "should" work with SQL 7.0, but future Diff updates might not be fully tested against that server.

Comparison between server version. It is possible to compare two databases residing on different SQL Server versions, which is in fact a common situation when porting existing databases to a newer server version. AdeptSQL Diff usually has no problems synchronizing a schema from older to new versions of MS SQL Server. Synchronizing in the other direction may not always be possible if a particular feature (such as an XML index) is not supported on the older server. The Diff always checks the SQL statements about to be generated against the target server version and issues a warning (and doesn't generate the code) whenever the SQL is not suitable for the target server.

Support for specific SQL features. We try to keep up with whatever new features Microsoft adds into the "classic" SQL schema, i.e. anything related to data types, tables, views, procedures and triggers. Other parts of the schema (such as Service Broker, encryption or MDX objects) are considered of less importance and may be not supported or only partially supported by the current version of AdeptSQL Diff.

The tables below list various kinds of schema objects and SQL featured which are supported, partially supported or ignored by the Diff.

A couple of notes to all these tables:

a) Many of the modern MS SQL features has first appeared in SQL 2000. The Diff still can work with SQL Server 7.0 databases, but it is probably of little interest to discuss "what's new in SQL 2000" now. So wherever you see "all" server versions In the tables below, it actually means SQL 2000+.

b) The "full" support for a particular schema object or feature means that the Diff does all of the following:

·	Correctly loads all relevant information from the database,

·	Compares all loaded attributes against a counterpart object in the other database,

·	Displays the object in the schema tree

·	Generates a synchro script for it

·	If there are any dependencies between this and other objects, the Diff takes them into account when scripting and can drop/recreate or modify the dependent objects as required.

c) The "partial" support of a particular object or feature means that the Diff is at least sufficiently aware of that object or feature to avoid breaking down other parts of the schema or producing unusable scripts.

A) Basic schema features that have been there for a long time and are fully supported by AdeptSQL Diff:

Schema object/Feature	Server versions	Support in AdeptSQL Diff	Comments
Basic tables with: Columns of predefined types Columns of user-defined types Named or unnamed constraints Indexes File groups (for SQL2008-specific options - see below)	all	Full	The Diff tries to generate the minimally disturbing synchro script, using ALTER TABLE and preserving the table data whenever necessary.
User-defined types derived from native data types with optional rule- and default- binding (for CLR types and table types - see below)	all	Full	The Diff supports both "exec sp_addtype" and "CREATE TYPE" syntaxes, depending on the target server and the situation.
Views with any combination of column list, SELECT, options (ENCRYPTION, SCHEMA_BINDING, VIEW_METADATA)	all	Full	a) Diff doesn't parse the column list or the SELECT statement, keeping (and comparing) the entire body of the VIEW in the textual form. b) Views with ENCRYPTION can't be fully loaded. The Diff keeps an empty body for them and knows how to handle this situation gracefully when scripting.
Stored procedures with optional names numbering optional FOR REPLICATION clause and any valid "WITH..." options	all	Full	See the ENCRYPTION note above
Functions of different kinds (scalar, table inline, table multi-statement) with options (ENCRYPTION, SCHEMABINDING, etc) (for CLR functions - see below)	all	Full	a) The Diff keeps the entire function body with only rudimentary parsing. However, it does distinguish different function kinds and would never match e.g. a scalar function against a table one, even if they have the same name. b) See the ENCRYPTION note above
Triggers DML triggers - those defined on a table to handle INSERT, DELETE, UPDATE events (database- and server- level triggers are not supported)	all	Full	See the ENCRYPTION note above
Global constraints, including: Creating RULEs and DEFAULTs, Binding them to user-defined types, Binding them to table columns	all	Full
STATISTICS, both named and unnamed	all	Full
Extended properties on the database level and for all supported objects	all	Full (Optional)	Just as for permissions, the Diff has an option to either synchronize or preserve the original extended properties in the target database.
File groups, including: individual files by name, their physical location, file specs (max size, etc), setting default filegroup	all	Full (Optional)	Filegroups will tend to be different on a test server and on a production server, and physical file locations will almost certainly be different. So comparing filegroups (as well as filegroup changes in tables/indexes) may be tricky. There are options controlling how filegroups should be compared, if compared at all.
Database principals, including: Users, Roles, Application roles	all	Full (Optional)	Again, the users are likely to be different on any two servers and synchronizing them may not be a good idea. Comparing of principals is optional in the Diff.
Permissions, including: Object-level permissions, Database-level permissions, (for SQL2008-specific permissions - see below)	all	Full (Optional)	Same for the permissions. If not synchronizing permissions, it is important that the Diff at least doesn't lose them when synchronizing (re-creating) other objects. By default, the Diff does re-create the original permissions in all situations where they might have been dropped.

B) MS SQL 2005/2008 extensions supported by AdeptSQL Diff 1.96:

Schema object/Feature	Server versions	Support in AdeptSQL Diff	Comments
All XML-related features in SQL 2005 and 2008 are fully supported:
XML Schema Collections.	2005+	full	No parsing of the XML occurs; the XML bodies of the matched "collections" are compared textually. The result is affected by the code comparison options. Specifically, the comparison can be made case-sensitive an insensitive, literal or "lexical" (ignoring white space).
XML columns in tables, in procedure parameters, with or without XML Schema	2005+	full	a) XML columns in parameters didn't actually require special handling, because the Diff doesn't parse parameter lists. b) When synchronizing down to a SQL 2000 server, the Diff converts any XML columns to NTEXT.
XML indexes, including primarily and secondary XML indexes, with any valid options	2005+	full
XML column sets with sparse columns	2008+	full
New features in types, tables and indexes:
New column types, including: new date times datetime2, datetimeoffset predefined CLR types geometry, geography, hierarchyid	2008+	full
Partial indexes (WHERE clause in indexes)	2008+	full
User-defined table types, including their usage in procedure parameters	2008+	full	The Diff fully support scripting of those "CREATE TYPE..." definitions and correctly resolves dependent procedures. Fortunately, table types can only be used in local variables and parameter lists (that we don't do much with), but not in tables.
EXECUTE AS ... clause in procedures, functions, triggers	2005+	full
FILESTREAM attribute for columns, including FILESTREAM_ON clause after the table	2008+	full
Named schemas, including Objects with distinct schema and owner, User-defined types within named schemas, Schema-level extended properties and permissions	2005+	full	Starting from SQL 2005, "schema" and "owner" are distinct attributes of an object. The Diff fully scripts definitions of named schemas, as well any changes in schema or authorization of objects. (Objects belonging to different named schemas are normally never paired to each other, but there are ways to make them linked)
SYNONYMs	2005+	full

C) SQL Features and objects not supported or only partially supported by AdeptSQL Diff 1.96.

Schema object/Feature

Server versions

Support in AdeptSQL Diff

Comments

Partitioning in Diff 1.XX is supported only as much as necessary to script tables and indexes correctly. Full support for this feature will be available in Diff 2.0.

Partition schema

2005+

Partial

PARTITION SCHEMAs are loaded with other data spaces, but are not displayed, compared or scripted. Their names are used to correctly script partitioned tables and indexes.

Partition functions

2005+

None

In its "partial" support of partitioning, the Diff can safely ignores these functions. They would be needed to properly script PARTITION SCHEMAs, but not for partitioning in tables/indexes.

Partitioning clauses, including:
CREATE TABLE .... ON partition_schema(column)
CREATE VIEW .... ON partition_schema(column)

2005+

supported

a) The Diff does script those, but you must make sure that any relevant partition schemas already exist on the target database before running the update scripts.

b) Scripting of the ON/TEXTIMAGE_ON/FILESTREAM ON clauses is optional and can be turned off.

Replication in Diff 1.XX is supported only as the [NOT] FOR REPLICATION attributes on various schema objects.

NOT FOR REPLICATION clause in
FOREIGN KEYs,
CHECK constraints,
IDENTITY columns,
TRIGGERs

all

supported

The NFR attribute is compared and scripted, but not otherwise used. Even if one database in the comparison is the "publisher" and the other a "subscriber", the Diff won't be aware of that and won't change scripting of NFR entities in any way.

FOR REPLICATION clause in stored procedures

all

supported

If a procedure is defined FOR REPLICATION, the Diff will not be aware of any references to it from the replication schema entities.

Replication schema itself, including:
Publications,
Articles,
Distributors,
Subscribers

all

None

Not planned

.NET-related schema features. Since MS SQL Server 2005, it has been possible to integrate into your database user-defined types and code defined on CLR level. The current version of AdeptSQL Diff has very limited support of such objects, just enough to correctly work with tables even when they contain CLR-defined types. We consider support for CLR-defined objects essential and will introduce it the next major Diff version

.NET Assemblies

2005+

Diff 1.XX ignores any assembly definitions in the database and therefore can't synchronize them.

(will be available in Diff 2.0)

CLR-defined types

2005+

Partial

In tables, the Diff recognizes references to CLR-defined column types and scripts such columns correctly. However, the CLR types themselves are not displayed in the schema tree and not synchronized.

CLR-defined code objects, including
Procedures,
Functions and
Triggers

2005+

Diff 1.XX ignores any such objects. To avoid errors executing synchronization scripts, users should ensure that all relevant CLR entities are created on the target database prior to running the comparison.

(will be available in Diff 2.0)

Other unsupported SQL features and objects

Full-text indexing, including:

FULLTEXT CATALOG,
FULLTEXT INDEX

2005+

(will be available in Diff 2.0)

Service Broker objects including:
MESSAGE TYPE,

EVENT NOTIFICATION,
CONTRACT,
SERVICE,
REMOTE SERVICE BINDING,
QUEUE,

ROUTE,

ENDPOINT

2005+

(will be available in Diff 2.0)

Encryption-related objects, including:
MASTER KEY,
SYMMETRIC KEY,
ASYMMETRIC KEY,
CERTIFICATE,
CREDENTIAL

2005+

(will be available in Diff 2.0)

Data Mining Extensions (DMX), including:
MINING STRUCTURE,
MINING MODEL

2000/2005+

not planned

Multidimensional Expressions (MDX), including:

ACTION,
CUBE, GLOBAL/SESSION CUBE, SUBCUBE,
MEMBER,
CELL CALCULATION,
SET

2000/2005+

not planned