Running a Migration: Comprehensive Version

The ANSI_NULLS setting is part of the definition of a table. Since tables contain the data in the database, changing the table definitions might result in losing the data when a database will be patched. Also, it might take a while to migrate tables with many data, using the code that the IsahDBPatchEngine generates automatically. Therefore, Isah provides a migration tool to migrate the tables in a database from ANSI_NULLS = OFF to ANSI_NULLS = ON. This migration tool checks whether data is lost. The migration tool will change the table definition of all tables, including customizing tables. In addition, the scripts generated are optimized to run as fast as possible.

Not all tables can be migrated by the ANSI_NULLS migration tool. The following table types are not supported:

1. MS SQL Server system tables (sys.tables.is_ms_shipped = 1)
2. The table the IsahDBPatchEngine creates to support refactoring (RefactorLog)
3. The table SSMS creates to store diagram information (sysdiagrams)
4. File tables (sys.tables.is_filetable = 1)

   File tables are used to store documents and directory trees. They also show up in the Windows Explorer, which enables working with the documents in a file table in a standard way. The bonus is that the documents are stored in the database and will be backed up when you create a database backup.

5. Temporal tables (sys.tables.temporal_type > 0, SQL Server 2016 and later)

   Temporal tables automatically store a change history. There are two types of temporal tables.

   temporal_type = 1 : This is a history table

   temporal_type = 2 : This is a system versioned temporal table.

6. External tables (sys.tables.external = 1, SQL Server 2016 and later)

   External tables are not stored in the database. Instead, they are either stored in an Azure database in the cloud or they contain non-relational information that is accessible via Hadoop.

7. Graph tables (sys.tables.is_node = 1 or sys.tables.is_edge = 1, SQL Server 2017 and later)

   Graph tables store graphs from graph theory.

As of version 5.6, the database patch contains a check whether tables with supported table types exist where the ANSI_NULLS setting is OFF. If the check fails, the following error message will be displayed:

There are <number> tables with ANSI_NULLS = OFF.

Msg 50000, Level 18, State 1, Line 42

Run the tool "Migrate.bat" to migrate ANSI_NULLS = OFF tables, before patching the database.

With the statement below, you can find the tables still using ANSI_NULLS = OFF.

select schema_name(t.[schema_id]) as [schema]

, t.name

, t.type_desc

, t.uses_ansi_nulls

from sys.tables as t

where t.uses_ansi_nulls = 0

order by name, [schema];