How to configure Database Mirroring on Transparent Data Encryption (TDE) enabled Database on SQL Server via T-SQL Script

How to configure Database Mirroring on Transparent Data Encryption (TDE) enabled Database on SQL Server via T-SQL Script


Note

Be aware we can not configure via GUI using TDE enabled database on db mirroring

SQL Server Transparent Data Encryption

SQL Server Transparent Data Encryption (TDE), is an encryption mechanism, available in the Enterprise edition of SQL Server 2008 and later. It is used to protect the database physical files, rather than protecting the data itself. This includes the database data, log, backup and snapshot files as mentioned previously. Using the SQL Server Transparent Data Encryption, unauthorized users will not be able to access the database’s data, by preventing them from attaching or restoring the database files to another SQL instance. Also it will be protected inside the backup media if it is stolen.

The SQL TDE encryption, as the name indicates, transparent to the applications that connects to the database, as the encryption is at the page level. The data is encrypted while it is at rest on the disk and decrypted during the read process on its way to the memory. An authorized user will be able to access the encrypted database content, without being aware that the database is encrypted or any action or extra code required from his side.

Encrypting any user database using TDE, the TempDB system database will be encrypted. As this database contains temp data from the TDE-Encrypted database

Encrypting a SQL Server database using TDE is a straightforward operation. First a master key should be created. Then you should create a certificate which is protected by that master key. After that a database encryption key (DEK) secured by the certificate is created in order to protect the user database. Finally you need to enable the encryption in your database.

The dependency of the database encryption key that is secured by the certificate which is protected by the master key prevents the database files from being restored or shown outside the current instance without these keys. Also, this encryption protect the backup files from being opened by the text editors to view its content.

If you decide, for any valid reason, to restore the TDE-Encrypted user database to another SQL Server Instance, you need to have a copy of the same master key on that new instance, in addition to the certificate backup files and certificate private key.

SQL Server TDE is a light encryption method that will not affect the queries performance, as the encryption is at the database files level. On the other hand, using the TDE, the database backup files will not take benefit from the backup compression feature completely.

The scenario

After the brief introduction about the SQL Server Transparent Data Encryption (TDE), and in order to simulate creating SQL Server Mirroring site on a database with TDE enabled on it, we will assume the below scenario:

• We have two SQL Server 2012 Enterprise instances.
• The first SQL Server is USER-PC\MSSQL2012SOURCE hosts the TestTDE_DBmirroring database and will act as the Principal server in the mirroring site.
• The second SQL Server is USER-PC\MSSQL2012DESTSQL and will act as the Mirrored server.
• TestTDE_DBmirroring recovery model is FULL.
• SQL Server Transparent Data Encryption will be enabled on the TestTDE_DBmirroring database.
• A SQL Server disaster recovery site will be created using SQL Server mirroring between the USER-PC\MSSQL2012SOURCE and USER-PC\MSSQL2012DESTSQL SQL Servers.

Demo:

Source Server:

Destination Server:

we can see source db server table info

Create/Backup Master Key and Create Certificate on master

We need to create master key and open master key and backup master key

and also create certificate on master @ source server

Backup Certificate on master

Also we need to take backup of certificate as see below

We can see the certificates on source

Create Database Encryption on User Database

We need to create Database Encryption Key on user database

Enable Database Encryption on User Database

We can enable encryption on user database and see as below

Destination server

Create/ open Master Key on maser @ Destination

Now we need to logged into Destination server and create /open master key

Master key location as see below

Restore  Master Key on maser @ Destination

We should need to restore master key on Destination server (It is already created so it gives identical)

Certificate location info on both



On Destination  we need to create Certificate from backup file

now we are going to backup database on source

database backedup successfully

on Destination server we are going to restore database

locate the backup file from drive

it selected

be aware if we are not restore master key and certificate we will receive following error as example

our case we restored master and certificate from source so no issue

now we need to give relocate option and edit file

click overwrite option with restore norecovery

Restored database successfully

we can see restored db info on destination

Trying to Configure Mirroring via GUI(Graphical User Interface):

We can not do but we can try that as we are choose right click db and choose tasks-->mirror

once wizard launched choose configure security

Principal info

Mirror info

success info on configure endpoints

we should  click do not start mirroring

After we click Start Mirroring we got error as see below it is expected before as we can not configure db mirroring via GUI

Configure DB Mirroring via TSQL

Now we are execute Script as see below

on Mirror Server execute below script

ALTER DATABASE [TestTDE_DBmirroring] SET PARTNER = 'TCP://USER-PC.LOCAL:5022'

on Principal Server execute below script

ALTER DATABASE [TestTDE_DBmirroring] SET PARTNER = 'TCP://USER-PC.LOCAL:5023'

that is it DB mirror is  configured

we need to refresh on both servers

we can see on principal server as see below

we can see on Mirror  server as see below

complete script:

Execute on Source server:


Create master Key

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='Ws@2012';
GO

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Open master Key

USE master

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Ws@2012';

Backedup master Key

use master

BACKUP MASTER KEY TO FILE = 'C:\SQL activity\TDE\Certificate\Master\MasterKeyBack'
    ENCRYPTION BY PASSWORD = 'Ws@2012';
GO

Create Certificate on Master


use master

CREATE CERTIFICATE TDE_Cert_TestTDE_DBmirroring
WITH 
SUBJECT='TestTDE_DBmirroring_Encrypt_DBmirroring';
GO

Backup  Certificate with encryption by password on Master 

BACKUP CERTIFICATE TDE_Cert_TestTDE_DBmirroring 
TO FILE = 'C:\SQL activity\TDE\Certificate\DB\TDE_Cert_TestTDE_DBmirroring'
WITH PRIVATE KEY 
(
    FILE = 'C:\SQL activity\TDE\Certificate\DB\TDE_Cert_TestTDE_DBmirroring_private.pvk',
    ENCRYPTION BY PASSWORD = 'Ws@2012'
);
GO

see Certificates info  

USE master
GO
SELECT name, pvt_key_encryption_type ,pvt_key_encryption_type_desc ,issuer_name ,expiry_date ,start_date 
 FROM sys.certificates
where name='TDE_Cert_TestTDE_DBmirroring'

Create Database Encryption Key on User Database

use [TestTDE_DBmirroring]

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert_TestTDE_DBmirroring
GO

Enable  Database Encryption on User Database

ALTER DATABASE [TestTDE_DBmirroring]
SET ENCRYPTION ON
GO

To see Database Encryption 

SELECT db_name(database_id), EncryptionState =
CASE encryption_state
WHEN 1 THEN 'Unencrypted'
WHEN 3 THEN 'Encrypted'
END

FROM sys.dm_database_encryption_keys

Drop certificate

DROP CERTIFICATE certificate_name

Execute on Destination server:

Create master Key on Destination master

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='Ws@2012';
GO

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Open master Key on Destination master

USE master

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Ws@2012';

RESTORE MASTER KEY
    FROM FILE = 'C:\SQL activity\TDE\Certificate\Master\MasterKeyBack'
    DECRYPTION BY PASSWORD = 'Ws@2012'
    ENCRYPTION BY PASSWORD = 'Ws@2012';
GO

Create Certificate on Destination master

USE MASTER

CREATE CERTIFICATE TDE_Cert_TestTDE_DBmirroring 
FROM FILE = 'C:\SQL activity\TDE\Certificate\DB\TDE_Cert_TestTDE_DBmirroring'
WITH PRIVATE KEY 
(
    FILE = 'C:\SQL activity\TDE\Certificate\DB\TDE_Cert_TestTDE_DBmirroring_private.pvk',
    Decryption BY PASSWORD = 'Ws@2012'
);

GO

Create Endpoint witness on master

CREATE ENDPOINT Mirroring  
    STATE=STARTED   
    AS TCP (LISTENER_PORT=7022)   
    FOR DATABASE_MIRRORING (ROLE=WITNESS)  

GO 

To see Witness endpoint info

SELECT @@servername[ServerName],role_desc, state_desc,* FROM sys.database_mirroring_endpoints

Errors and suggestions

1)If you are trying to restore certificate with encryption by password you will get error 

Resolution:

It should be create certificate with decryption by password

2) you should need to create certificate on destination with private key otherwise you will get error

USE MASTER

CREATE CERTIFICATE TDE_Cert_TestTDE_DBmirroring 
FROM FILE = 'C:\SQL activity\TDE\Certificate\DB\TDE_Cert_TestTDE_DBmirroring'
WITH PRIVATE KEY 
(
    FILE = 'C:\SQL activity\TDE\Certificate\DB\TDE_Cert_TestTDE_DBmirroring_private.pvk',
    DECRYPTION BY PASSWORD = 'Ws@2012'
);
GO


3) Trying to configure Witness Server but got below error ALTER DATABASE command could not be sent to the remote server instance

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://USER-PC.LOCAL:5025'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

till not solved working on to solve this error

To set up a witness for a database, the database owner assigns a Database Engine instance to the role of witness server. The witness server instance can run on the same computer as the principal or mirror server instance, but this substantially reduces the robustness of automatic failover.

We strongly recommend that the witness reside on a separate computer. A given server can participate in multiple concurrent database mirroring sessions with the same or different partners. A given server can be a partner in some sessions and a witness in other sessions.

The witness is intended exclusively for high-safety mode with automatic failover. Before you set a witness, we strongly recommend that you ensure that the SAFETY property is currently set to FULL.

https://msdn.microsoft.com/en-us/library/ms190430(v=sql.120).aspx




Ref:

https://www.sqlshack.com/how-to-configure-sql-server-mirroring-on-a-tde-encrypted-database/

Step by Step Demo of In Place Upgrade from SQL 2005 Standard Edition to SQL 2012 Standard Edition

Step by Step Demo of In Place Upgrade from SQL 2005 Standard Edition to SQL 2012 Standard Edition

Demo:

Source Server SQL 2005 Standard Edition SP4

We have checked linked server running fine on SQL 2005 Before upgrade

We have checked linked server running fine on SQL 2005 Before upgrade

We have checked login info of sysadmin permission on SQL 2005 Before upgrade

We have checked login info of dbowner permission on SQL 2005 Before upgrade

We have checked Full Text Index info  on SQL 2005 Before upgrade

We have checked Compatibility level info on SQL 2005 Before upgrade

We have checked Maintenance plan info on SQL 2005 Before upgrade

We have checked Job info on SQL 2005 Before upgrade

We have Replication info on SQL 2005 Before upgrade

Now we are going to upgrade initiate from SQL 2012 Standard Edtion

Installing upgrade adviser on SQL 2012

See the issue on running upgrade adviser on SQL 2012

We are going to stop SQL 2005 Services

We are going to choose In Place Upgrade on SQL 2012 From SQL 2005

Finalize setup rules 

Select features can not  change

choose instance (default our case)

Full text service account

Full text upgrade with import option

Reporting services asking password

Upgrade rules

Ready to go with upgrade 

Upgrade progress..................

Upgraded successfully with SSRS error (SSRS is not configured earlier)

After upgrade SQL Services automatically started

Now we are going to log in SQL 2012 With upgraded instance(default our case)

upgraded edition info

SQL Jobs are working fine on SQL 2012

Maintenance plan are seems fine on SQL 2012

Linked servers are seems fine on SQL 2012

Full Text  are seems fine on SQL 2012

Full Text  are seems fine on SQL 2012

Trying to log in SQL 2005 with upgraded instance

Trying to log in SQL 2005 with upgraded instance

Logged SQL 2005 with upgraded instance shows some error 

after upgraded to SQL 2012 showing compatibility level info

Now we are going to change compatibility level to latest (SQL 2012) Using GUI(Graphical User Interface)

Now we are going to change compatibility level to latest (SQL 2012) Using TSQL(Transact SQL)

Compatibility with latest

after we running are compatibility level issue detection query it gives error as see below

DECLARE @sql VARCHAR(max),

@Text VARCHAR(max),

@ProcName VARCHAR(200),

@ProcName1 VARCHAR(200)

DECLARE @T TABLE (ProcName VARCHAR(200), sql VARCHAR(max), ErrorMessage VARCHAR(4000))

DECLARE c Cursor FOR

SELECT O.Name, C.Text

FROM sysobjects O

JOIN syscomments C ON o.ID=C.ID

WHERE O.XType IN ('P','TF','FN')

and C.text IS NOT NULL

ORDER BY O.Name, C.colid

Open C

FETCH NEXT FROM c INTO @ProcName, @Text 

SET @sql=@Text

SET @ProcName1=@ProcName

WHILE @@FETCH_STATUS = 0 BEGIN

FETCH NEXT FROM c INTO @ProcName, @Text 

IF @@FETCH_STATUS = 0 AND @ProcName1=@ProcName BEGIN

SET @sql=@sql+@Text 

END ELSE BEGIN

SET @sql = REPLACE(@sql, @ProcName1, 'Temp_TestProc_DeleteMe') -- change proc name

IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType='P') EXEC('DROP PROC Temp_TestProc_DeleteMe')

IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType IN ('TF','FN')) EXEC('DROP FUNCTION Temp_TestProc_DeleteMe')

BEGIN TRY

EXEC(@sql) -- try to create the proc

END TRY

BEGIN CATCH

INSERT @T values (@ProcName1, @sql, ERROR_MESSAGE()) -- record procs that couldn't be created

END CATCH

print @ProcName1

SET @sql=@Text

SET @ProcName1=@ProcName

END

END

CLOSE c

DEALLOCATE c

IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType='P') EXEC('DROP PROC Temp_TestProc_DeleteMe')

IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType IN ('TF','FN')) EXEC('DROP FUNCTION Temp_TestProc_DeleteMe')

SELECT * FROM @T

where ErrorMessage not like '%There is already an object named%'

Issue details

here issue original info

we need to correct in code as see below

Now it did not give any error

Script:

SELECT @@servername as ServerName,NAME,COMPATIBILITY_LEVEL,version_name = 
CASE compatibility_level
    WHEN 65  THEN 'SQL Server 6.5'
    WHEN 70  THEN 'SQL Server 7.0'
    WHEN 80  THEN 'SQL Server 2000'
    WHEN 90  THEN 'SQL Server 2005'
    WHEN 100 THEN 'SQL Server 2008/R2'
    WHEN 110 THEN 'SQL Server 2012'
    WHEN 120 THEN 'SQL Server 2014'
END,LOG_REUSE_WAIT_DESC,recovery_model_desc,state_desc,* from sys.databases 
--where name='dbname'

--ALTER DATABASE  dbname  SET SINGLE_USER

--ALTER DATABASE dbname
--SET COMPATIBILITY_LEVEL = 100

--ALTER DATABASE dbname SET MULTI_USER

select name, compatibility_level 
from sys.databases

select * from sys.triggers


select  * from sys.views


SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'SET' + '%'
and OBJECT_NAME(OBJECT_ID) not like '%sp_%'
AND OBJECT_NAME(OBJECT_ID) not like '%dt_%'
GO

SELECT 
    CASE TYPE 
        WHEN 'U' 
            THEN 'User Defined Tables' 
                        WHEN 'P'
            THEN 'Stored Procedures'
        WHEN 'PC'
            THEN 'CLR Stored Procedures'
        WHEN 'X'
            THEN 'Extended Stored Procedures'
WHEN 'V'
            THEN 'Views'
WHEN 'TF'
            THEN 'SQL_TABLE_VALUED_FUNCTION'
WHEN 'IF'
            THEN 'INLINE_TABLE_VALUED_FUNCTION'
WHEN 'FN'
            THEN 'SQL_SCALAR_FUNCTION'
    

END as 'Objects',  
    COUNT(*) [Objects Count]    
FROM SYS.OBJECTS
WHERE TYPE IN ('U','P', 'PC','V','TF','IF','FN')
GROUP BY TYPE
order by Objects
compute sum(count(*))

Compatibility Level Issue detection 

Behavioral Differences Between Earlier Compatibility Level 80(Lower SQL 2000) and Above Level 90(higher SQL 2005)

Compatibility level setting of 80 or earlier	Compatibility level setting of 90	Possibility of impact
For locking hints in the FROM clause, the WITH keyword is always optional.	With some exceptions, table hints are supported in the FROM clause only when the hints are specified with the WITH keyword. For more information, see FROM (Transact-SQL).	High
The *= and =* operators for outer join are supported with a warning message.	These operators are not supported; the OUTER JOIN keyword should be used.	High
WHEN binding the column references in the ORDER BY list to the columns produced by the SELECT list, column ambiguities are ignored and column prefixes are sometimes ignored. For instance, an ORDER BY expression that is made up of a single two-part column (<table_alias>.<column>) that is used as a reference to a column alias in a SELECT list is accepted, but the table alias is ignored. For example, in the query SELECT DISTINCT c1 = c1*-1 FROM t_table x ORDER BY x.c1, the ORDER BYoperation does not occur on the specified source column (x.c1); instead it occurs on the c1 column that is defined in the query.	Errors are raised on column ambiguities. Column prefixes, if any, specified in ORDER BY are not ignored when binding to a column produced by the SELECT list.	Medium
The UNION of a variable-length column and a fixed length column produces a fixed-length column.	The UNION of a variable-length column and a fixed length column produces a variable-length column.	Medium
SET XACT_ABORT OFF is allowed inside a trigger.	SET XACT_ABORT OFF is not allowed inside a trigger.	Medium
The FOR BROWSE clause is allowed (and ignored) in views.	The FOR BROWSE clause is not allowed in views.	Medium
Views with CHECK OPTION are supported incorrectly if the view or a view it references contains TOP.	Views with CHECK OPTION are not supported if the view or a view it references contains TOP.	Medium
If a passthrough query against a remote data source [OpenRowset or OpenQuery] produces columns with duplicate names, the duplicate column names are ignored unless the columns are explicitly named in the query.	If a passthrough query against a remote data source [OpenRowset or OpenQuery] produces a column with duplicate column names, an error is raised.	Low
Character string constants and varbinary constants of size greater than 8000 are treated as text, ntext, orimage.	Character string constants and varbinaryconstants of size greater than 8000 are treated as type varchar(max) (or nvarchar(max) andvarbinary(max), respectively). This can change the data type of the table created using SELECT … INTO if the SELECT list contains such expressions.	Low
Comparisons between numeric types (smallint,tinyint, int, bigint, numeric, decimal, smallmoney,money) are done by converting the comparand with lower precedence in the type hierarchy to the type whose precedence is higher.	The numeric-type values are compared without conversions. This provides improved performance. However, this can cause some changes in behavior, especially in cases in which the conversion caused overflow exceptions.	Low
Built-in metadata functions that take string arguments truncate their input if the input is longer than 4000 characters.	Built-in metadata functions raise an error if the truncation would result in the loss of nonspace characters.	Low
The set of disallowed characters in an unquoted identifier remains unchanged.	The Transact-SQL parser supports the Unicode 3.2 standard, which changes the character classification for some international characters that are now not allowed in nondelimited identifiers.	Low
SET ANSI_WARNINGS ON does not override the setting of SET ARITHABORT OFF for the case of floating point domain errors [that is, negative arguments for the log() function]. If ANSI_WARNINGS is ON but ARITHABORT is OFF, floating point domain errors do not cause the query to be terminated.	SET ANSI_WARNINGS ON completely overrides ARITHABORT OFF setting. Floating point domain errors in this case will cause the query to be terminated.	Low
Non-integer constants are allowed (and ignored) in the ORDER BY clause.	Non-integer constants are not allowed in the ORDER BY clause.	Low
Empty SET statement (with no SET option assignments) is allowed.	Empty SET clause is not allowed.	Low
The IDENTITY attribute is not derived correctly for columns produced by a derived table.	The IDENTITY attribute is derived correctly for columns produced by derived tables.	Low
The nullability property of arithmetic operators over floating point data type is always nullable.	The nullability property of arithmetic operators over the floating point data type is changed to nonnullable for the case where the inputs are nonnullable and ANSI_WARNINGS is ON.	Low
In the INSERT .. SELECT statement with UNION, the types produced by the individual result sets are all converted to the destination result type.	In the INSERT .. SELECT statement with UNION, the dominant type of the various branches is determined, and the results are converted to that type before being converted to the destination table type.	Low
In the SELECT .. FOR XML statement, the hex(27) (the' character) and hex(22) (the " character) are always entitized, even where not required.	FOR XML entitizes hex(27)and hex(22) only where required. They are not entitized in the following situations: ·         In attribute content, hex(27) (the 'character) is not entitized if attribute values are delimited with ", and hex(22) (the "character) is not entitized if attribute values are delimited with '. ·         In element content, hex(27) and hex(22) are never entitized.	Low
In FOR XML, the timestamp value is mapped to an integer.	In FOR XML, the timestamp value is mapped to an binary value. For more information, see FOR XML Support for the timestamp Data Type.	High (if atimestampcolumn is used); otherwise, low
In FOR XML and OPENXML, high-range Unicode characters (3 bytes) in names are represented using 8 positions. For example, using 8 positions, FOR XML represents the Unicode code point U+10000 as: <a_x00010000_ c1="1" />	In FOR XML and OPENXML, high-range Unicode characters (3 bytes) in names are represented using 6 positions. For example, using 6 positions, FOR XML represents the Unicode code point U+10000 as: <a_x010000_ c1="1" />	Low
In FOR XML, derived table mappings in AUTO mode are treated transparently. For example: Copy Code USE AdventureWorks CREATE TABLE Test(id int); INSERT INTO Test VALUES(1); INSERT INTO Test VALUES(2); SELECT * FROM (SELECT a.id AS a, b.id AS b FROM Test a JOIN Test b ON a.id=b.id) Test FOR XML AUTO; When the compatibility level for AdventureWorks is set to 80, the above example produces: <a a="1"><b b="1"/></a> <a a="2"><b b="2"/></a>	In FOR XML, derived table mappings in AUTO mode are treated opaquely. When the compatibility level for AdventureWorks is set to 90, the preceding example produces: <Test a="1" b="1"/> <Test a="2" b="2"/> For more information about changes to AUTO mode, see AUTO Mode Enhancements.	High (if FOR XML AUTO mode is applied on views); otherwise, low
String to money conversions support using a backslash character (\) as a currency symbol only in the Japanese and Korean languages.	The backslash character (\) is accepted in all string to money conversions in all languages.ISNUMERIC would return true when \ is used as a currency symbol. For databases on versions of SQL Server earlier than SQL Server 2005, this new behavior breaks indexes and computed columns that depend on an ISNUMERIC return value that contains \ and for which the language is neither Japanese nor Korean.	low
The result of an arithmetic operator is always nullable, even if the operands are nonnullable and ANSI_WARNINGS or ARITHABORT is set ON.	When ANSI_WARNINGS or ARITHABORT are set to ON, the result of a floating point arithmetic operator is nonnullable, if both operands are nonnullable. This change in nullability could cause failure when bcp is used to bulk export data that uses the binary format from a SQL Server 2000 table with a computed column that uses a floating point arithmetic operator and bcp or BULK INSERT is then used bulk import that data into a SQL Server 2005 table with the same definition. Note: When both options are OFF, the Database Engine marks the result as nullable. This is the same as in SQL Server 2000.	low
Domain errors are not controlled by ANSI_WARNINGS. ARITHABORT settings are honored, if ANSI_WARNINGS are set to OFF and there is no change to ARITHABORT.	Domain errors are also controlled by ANSI_WARNINGS and are severity 16 errors. If either ANSI_WARNINGS or ARITHABORT are ON, an error is thrown instead of returning NULL value. User scripts that depend upon ARITHABORT being set to OFF might be broken by this change.	medium
In an INSERT SELECT from a UNION of different data types, each UNION branch is directly cast to the type of the target column of the INSERT. Even if the union used by itself could fail because of incompatible type conversions, the INSERT SELECT makes the UNION succeed because the branch to the result type of the UNION is never converted.	In SQL Server 2005, the result type of UNION is derived independently of INSERT SELECT. Each branch of UNION is cast to the result type of UNION, and then cast to the target column type of INSERT. If there are incompatible types in the UNION, the first cast might cause an error. To run in compatibility level 90, you must fix all incompatible type unions used inside INSERT SELECT.	medium
For built-in functions that take nvarchar as a parameter, if the supplied value is varchar, the value is converted to nvarchar(4000). In SQL Server 2000, if a larger value is passed, it is silently truncated.	For built-in functions that take nvarchar as a parameter, if the supplied value is varchar, the value is still converted to nvarchar(4000). However, if a larger value is passed, SQL Server 2005 generates an error. To run in compatibility level 90, you must fix any custom code that relies on the truncation behavior.	low
A union of fixed-length (char, binary, or nchar) string with a variable-length (varchar, varbinary, nvarchar) string returns a fixed-length result.	The union of a variable-size string and a fixed-size string returns a variable-size string. To run in compatibility level 90, you must fix all the places (indexes, queries, and computed columns) that depend on the type resulting from a union of a variable-size type and a fixed-size type.	low
Object names containing the character 0xFFFF are valid identifiers.	Object names containing the character 0xFFFF are invalid identifiers and cannot be accessed. To run in compatibility level 90, you must rename objects that contain this character.	Low
In SELECT ISNUMERIC('<string>'), embedded commas within <string> are significant. For example, the following SELECT ISNUMERIC('121212,12') query returns 0. This indicates that the string 121212,12 is not numeric.	In SELECT ISNUMERIC('<string>'), embedded commas within <string> are ignored. For example, the following SELECT ISNUMERIC('121212,12') query returns 1. This indicate that the string 121212,12 is numeric.	Low

Differences Between Compatibility Level 90(SQL 2005) and Level 100(SQL 2008)

This section describes new behaviors introduced with compatibility level 100.

Compatibility-level setting of 90	Compatibility-level setting of 100	Possibility of impact
The QUOTED_IDENTIFER setting is always set to ON for multistatement table-valued functions when they are created regardless of the session level setting.	The QUOTED IDENTIFIER session setting is honored when multistatement table-valued functions are created.	Medium
When you create or alter a partition function, datetime and smalldatetime literals in the function are evaluated assuming US_English as the language setting.	The current language setting is used to evaluate datetime and smalldatetime literals in the partition function.	Medium
The FOR BROWSE clause is allowed (and ignored) in INSERT and SELECT INTO statements.	The FOR BROWSE clause is not allowed in INSERT and SELECT INTO statements.	Medium
Full-text predicates are allowed in the OUTPUT clause.	Full-text predicates are not allowed in the OUTPUT clause.	Low
CREATE FULLTEXT STOPLIST, ALTER FULLTEXT STOPLIST, and DROP FULLTEXT STOPLIST are not supported. The system stoplist is automatically associated with new full-text indexes.	CREATE FULLTEXT STOPLIST, ALTER FULLTEXT STOPLIST, and DROP FULLTEXT STOPLIST are supported.	Low
MERGE is not enforced as a reserved keyword.	MERGE is a fully reserved keyword. The MERGE statement is supported under both 100 and 90 compatibility levels.	Low
Using the <dml_table_source> argument of the INSERT statement raises a syntax error.	You can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view. This is done using the <dml_table_source> argument of the INSERT statement.	Low
Unless NOINDEX is specified, DBCC CHECKDB or DBCC CHECKTABLE performs both physical and logical consistency checks on a single table or indexed view and on all its nonclustered and XML indexes. Spatial indexes are not supported.	Unless NOINDEX is specified, DBCC CHECKDB or DBCC CHECKTABLE performs both physical and logical consistency checks on a single table and on all its nonclustered indexes. However, on XML indexes, spatial indexes, and indexed views, only physical consistency checks are performed by default. If WITH EXTENDED_LOGICAL_CHECKS is specified, logical checks are performed on indexed views, XML indexes, and spatial indexes, where present. By default, physical consistency checks are performed before the logical consistency checks. If NOINDEX is also specified, only the logical checks are performed.	Low
When an OUTPUT clause is used with a data manipulation language (DML) statement and a run-time error occurs during statement execution, the entire transaction is terminated and rolled back.	When an OUTPUT clause is used with a data manipulation language (DML) statement and a run-time error occurs during statement execution, the behavior depends on the SET XACT_ABORT setting. If SET XACT_ABORT is OFF, a statement abort error generated by the DML statement using the OUTPUT clause will terminate the statement, but the execution of the batch continues and the transaction is not rolled back. If SET XACT_ABORT is ON, all run-time errors generated by the DML statement using the OUTPUT clause will terminate the batch, and the transaction is rolled back.	Low
CUBE and ROLLUP are not enforced as reserved keywords.	CUBE and ROLLUP are reserved keywords within the GROUP BY clause.	Low
Strict validation is applied to elements of the XML anyType type.	Lax validation is applied to elements of the anyType type. For more information, see Wildcard Components and Content Validation.	Low
The special attributes xsi:nil and xsi:type cannot be queried or modified by data manipulation language statements. This means that /e/@xsi:nil fails while /e/@* ignores the xsi:nil and xsi:type attributes. However, /e returns the xsi:nil and xsi:type attributes for consistency with SELECT xmlCol, even if xsi:nil = "false".	The special attributes xsi:nil and xsi:type are stored as regular attributes and can be queried and modified. For example, executing the query SELECT x.query('a/b/@*') returns all attributes including xsi:nil and xsi:type. To exclude these types in the query, replace @* with @*[namespace-uri(.) != "insert xsi namespace uri" and not (local-name(.) = "type" or local-name(.) ="nil".	Low
A user-defined function that converts an XML constant string value to a SQL Server datetime type is marked as deterministic.	A user-defined function that converts an XML constant string value to a SQL Server datetime type is marked as non-deterministic.	Low
The XML union and list types are not fully supported.	The union and list types are fully supported including the following functionality: Union of list Union of union List of atomic types List of union	Low
The SET options required for an xQuery method are not validated when the method is contained in a view or inline table-valued function.	The SET options required for an xQuery method are validated when the method is contained in a view or inline table-valued function. An error is raised if the SET options of the method are set incorrectly.	Low
XML attribute values that contain end-of-line characters (carriage return and line feed) are not normalized according to the XML standard. That is, both characters are returned instead of a single line-feed character.	XML attribute values that contain end-of-line characters (carriage return and line feed) are normalized according to the XML standard. That is, all line breaks in external parsed entities (including the document entity) are normalized on input by translating both the two-character sequence #xD #xA and any #xD that is not followed by #xA to a single #xA character. Applications that use attributes to transport string values that contain end-of-line characters will not receive these characters back as they are submitted. To avoid the normalization process, use the XML numeric character entities to encode all end-of-line characters.	Low
The column properties ROWGUIDCOL and IDENTITY can be incorrectly named as a constraint. For example the statement CREATE TABLE T (C1 int CONSTRAINT MyConstraint IDENTITY) executes, but the constraint name is not preserved and is not accessible to the user.	The column properties ROWGUIDCOL and IDENTITY cannot be named as a constraint. Error 156 is returned.	Low
Updating columns by using a two-way assignment such as UPDATE T1 SET @v = column_name = <expression> can produce unexpected results because the live value of the variable can be used in other clauses such as the WHERE and ON clause during statement execution instead of the statement starting value. This can cause the meanings of the predicates to change unpredictably on a per-row basis. This behavior is applicable only when the compatibility level is set to 90.	Updating columns by using a two-way assignment produces expected results because only the statement starting value of the column is accessed during statement execution.	Low
Variable assignment is allowed in a statement containing a top-level UNION operator, but returns unexpected results. For example, in the following statements, local variable @v is assigned the value of the column BusinessEntityID from the union of two tables. By definition, when the SELECT statement returns more than one value, the variable is assigned the last value that is returned. In this case, the variable is correctly assigned the last value, however, the result set of the SELECT UNION statement is also returned. Copy ALTER DATABASE AdventureWorks2012 SET compatibility_level = 90; GO USE AdventureWorks2012; GO DECLARE @v int; SELECT @v = BusinessEntityID FROM HumanResources.Employee UNION ALL SELECT @v = BusinessEntityID FROM HumanResources.EmployeeAddress; SELECT @v;	Variable assignment is not allowed in a statement containing a top-level UNION operator. Error 10734 is returned. To resolve the error, rewrite the query as shown in the following example. Copy DECLARE @v int; SELECT @v = BusinessEntityID FROM     (SELECT BusinessEntityID FROM HumanResources.Employee      UNION ALL      SELECT BusinessEntityID FROM HumanResources.EmployeeAddress) AS Test; SELECT @v;	Low
The ODBC function {fn CONVERT()} uses the default date format of the language. For some languages, the default format is YDM, which can result in conversion errors when CONVERT() is combined with other functions, such as {fn CURDATE()}, that expect a YMD format.	The ODBC function {fn CONVERT()} uses style 121 (a language-independent YMD format) when converting to the ODBC data types SQL_TIMESTAMP, SQL_DATE, SQL_TIME, SQLDATE, SQL_TYPE_TIME, and SQL_TYPE_TIMESTAMP.	Low
The ODBC function {fn CURDATE()} returns only the date in the format 'YYYY-MM-DD'.	The ODBC function {fn CURDATE()} returns both date and time, for example 'YYYY-MM-DD hh:mm:ss.	Low
Datetime intrinsics such as DATEPART do not require string input values to be valid datetime literals. For example, SELECT DATEPART (year, '2007/05-30') compiles successfully.	Datetime intrinsics such as DATEPART require string input values to be valid datetime literals. Error 241 is returned when an invalid datetime literal is used.	Low

To find Objects(SPs,Views,UDF,Table)

T-SQL:

SELECT OBJECT_NAME(OBJECT_ID),

definition

FROM sys.sql_modules

WHERE definition LIKE '%' + 'union' + '%'

AND OBJECT_NAME(OBJECT_ID) NOT LIKE '%' + 'sp_' + '%'

 GO