Infrastructure at your Service

Stéphane Haby

Migration to SQL Server 2016 with deprecated data types and Full-Text

A few weeks ago, I prepared a migration from SQL Server 2008 to SQL server 2016, I came across a case with deprecated data types and a Full-Text activated on these columns.
To simulate this scenario, I downloaded and installed the Data Migration Assistant(DMA).

Make sure that the .Net Framework 4.5 is installed on your machine before starting the installation of the DMA.
After the installation, you start the application which is very simple to use.
I created a database named db_to_mig.

In this database, I created 2 tables:

  • Text_With_FullText: table with a text named description and a FullText index on this Column
  • Text_Without_FullText: same table without the FullText Index

DMA_02

In the Data Migration Assistant, I created a new project. Be careful, to select “SQL Server” as target and not “Azure SQL Database”(default).
DMA_03

After, control that the target version is SQL Server 2016 and I recommend checking both checkboxes: “Compatibility issues” and “new features’ recommendation”.
The second step is to select the source, enter your instance and click connect.
Select your database and click add.
DMA_04

And go on clicking Start Assessment
DMA_05

Waiting the result…
DMA_07

After the evaluation, in the Review Results Part, I see 2 Issues:

    • Full-Text Search has changed since SQL Server 2008

DMA_08

    • Deprecated data types Text, Image or Ntext

DMA_09

Tips: You have also the possibility to save the report in a json file.
DMA_10

My case was first to convert all TEXT,NTEXT and image to VARCHAR(max), NVARCHAR(MAX) and VARBINARY(MAX) like the link here to the Microsoft documentation advise us.
First, I did a backup from my database and restored it on another server or on the same with a new name. Don’t touch directly the production database! :evil:
DMA_11

In my case, I restore the database with a new name on the same server: db_to_mig_2

DMA_12

On this copy, I change the data type from Text to nvarchar(max) for the first table without Full-Text with the T-SQL Command:

USE [db_to_mig_2]
GO

SELECT id,Description into #before FROM dbo.Text_Without_FullText WHERE Description is NOT NULL
GO

ALTER TABLE dbo.Text_Without_FullText ALTER COLUMN Description NVARCHAR(MAX)
GO

SELECT id,Description into #after  FROM dbo.Text_Without_FullText WHERE Description is NOT NULL
GO

SELECT DIFFERENCE(a.Description,b.Description), DIFFERENCE(b.Description,a.Description)
FROM #after AS a INNER JOIN #before AS b ON b.id = a.id
GO

SELECT Description from #before
SELECT Description from #after

DROP TABLE #before
DROP TABLE #after

DMA_14

I used 2 temporary tables to compare the result after the column data type change.
As you can see, the result of the select with difference is 4.
The value 4 indicates strong similarity or the same values.
To be sure, I advise you to do a better comparison between both temporary tables.
But this blog is not the case about comparing 2 strings with different data types.
The conversion is fast and easy, now, I do the same for the table with Full-Text:

SELECT id,Description into #before FROM dbo.Text_With_FullText WHERE Description is NOT NULL
GO

ALTER TABLE dbo.Text_With_FullText ALTER COLUMN Description NVARCHAR(MAX)
GO

SELECT id,Description into #after  FROM dbo.Text_With_FullText WHERE Description is NOT NULL
GO

SELECT DIFFERENCE(a.Description,b.Description), DIFFERENCE(b.Description,a.Description)
FROM #after AS a INNER JOIN #before AS b ON b.id = a.id
GO

SELECT Description from #before
SELECT Description from #after

DROP TABLE #before
DROP TABLE #after

DMA_13

And the result as you can see is an error message:

Msg 7614, Level 16, State 1, Line 1
Cannot alter or drop column ‘Description’ because it is enabled for Full-Text Search.

The Full-Text Index prevents me from changing the data type.
I need to drop the column from the FullText Index and create it after the data type change.

SELECT id,Description into #before FROM dbo.Text_With_FullText WHERE Description is NOT NULL
GO

DROP FULLTEXT INDEX ON dbo.Text_With_FullText;
GO

ALTER TABLE dbo.Text_With_FullText ALTER COLUMN Description NVARCHAR(MAX)
GO

CREATE FULLTEXT INDEX ON  dbo.Text_With_FullText(Description)
KEY INDEX PK_Text_With_FullText ON FT_Catalog
WITH STOPLIST = SYSTEM,CHANGE_TRACKING OFF, NO POPULATION;
GO

SELECT id,Description into #after  FROM dbo.Text_With_FullText WHERE Description is NOT NULL
GO

SELECT DIFFERENCE(a.Description,b.Description), DIFFERENCE(b.Description,a.Description)
FROM #after AS a INNER JOIN #before AS b ON b.id = a.id
GO

SELECT Description from  #before
SELECT Description from #after

DROP TABLE #before
DROP TABLE #after

DMA_15

Et voila! It is done. Be careful, if your FullText has more columns, you need to replace the CREATE FULLTEXT INDEX with an ALTER FULLTEXT INDEX ADD (column)
To finish, I reran the Data Migration Assistant and I saw that the only remaining issue was the Full-Text Search.

DMA_16

 

Leave a Reply


− four = 1

Stéphane Haby
Stéphane Haby

Delivery Manager