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
In the Data Migration Assistant, I created a new project. Be careful, to select “SQL Server” as target and not “Azure SQL Database”(default).
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.
And go on clicking Start Assessment
After the evaluation, in the Review Results Part, I see 2 Issues:
- Full-Text Search has changed since SQL Server 2008
- Deprecated data types Text, Image or Ntext
Tips: You have also the possibility to save the report in a json file.
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! 👿
In my case, I restore the database with a new name on the same server: db_to_mig_2
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
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
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
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.