{"id":10318,"date":"2017-07-11T15:06:26","date_gmt":"2017-07-11T13:06:26","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\/"},"modified":"2017-07-11T15:06:26","modified_gmt":"2017-07-11T13:06:26","slug":"migration-sql-server-to-2016-with-deprecated-data-types-and-full-text","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\/","title":{"rendered":"Migration to SQL Server 2016 with deprecated data types and Full-Text"},"content":{"rendered":"<p>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.<br \/>\nTo simulate this scenario, I downloaded and installed the<a title=\"Data Migration Assistant\" href=\"https:\/\/www.microsoft.com\/en-us\/download\/details.aspx?id=53595\" target=\"_blank\"> Data Migration Assistant(DMA).<\/a><\/p>\n<p>Make sure that the .Net Framework 4.5 is installed on your machine before starting the installation of the DMA.<br \/>\nAfter the installation, you start the application which is very simple to use.<br \/>\nI created a database named <strong>db_to_mig<\/strong>.<\/p>\n<p>In this database, I created 2 tables:<\/p>\n<ul>\n<li><strong>Text_With_FullText<\/strong>: table with a text named description and a FullText index on this Column<\/li>\n<li><strong>Text_Without_FullText<\/strong>: same table without the FullText Index<\/li>\n<\/ul>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_02.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\" size-medium wp-image-17506 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_02.jpg\" alt=\"DMA_02\" width=\"300\" height=\"227\" \/><\/a><\/p>\n<p>In the<strong> Data Migration Assistant<\/strong>, I created a new project. Be careful, to select &#8220;SQL Server&#8221; as target and not \u201cAzure SQL Database\u201d(default).<br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_03.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\" size-medium wp-image-17505 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_03.jpg\" alt=\"DMA_03\" width=\"300\" height=\"176\" \/><\/a><\/p>\n<p>After, control that the target version is SQL Server 2016 and I recommend checking both checkboxes: \u201cCompatibility issues\u201d and \u201cnew features\u2019 recommendation\u201d.<br \/>\nThe second step is to select the source, enter your instance and click connect.<br \/>\nSelect your database and click add.<br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_04.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\" size-medium wp-image-17504 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_04.jpg\" alt=\"DMA_04\" width=\"300\" height=\"175\" \/><\/a><\/p>\n<p>And go on clicking Start Assessment<br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_05.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\" size-medium wp-image-17503 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_05.jpg\" alt=\"DMA_05\" width=\"300\" height=\"176\" \/><\/a><\/p>\n<p>Waiting the result\u2026<br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_07.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\" size-medium wp-image-17501 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_07.jpg\" alt=\"DMA_07\" width=\"300\" height=\"176\" \/><\/a><\/p>\n<p>After the evaluation, in the Review Results Part, I see <strong>2 Issues<\/strong>:<\/p>\n<ul>\n<ul>\n<li>Full-Text Search has changed since SQL Server 2008<\/li>\n<\/ul>\n<\/ul>\n<p style=\"padding-left: 120px\"><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_08.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-17500\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_08.jpg\" alt=\"DMA_08\" width=\"300\" height=\"121\" \/><\/a><\/p>\n<ul>\n<ul>\n<li>Deprecated data types Text, Image or Ntext<\/li>\n<\/ul>\n<\/ul>\n<p style=\"padding-left: 120px\"><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_09.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-17499\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_09.jpg\" alt=\"DMA_09\" width=\"300\" height=\"125\" \/><\/a><\/p>\n<p><em><strong>Tips:<\/strong><\/em> You have also the possibility to save the report in a json file.<br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_10.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\" size-medium wp-image-17498 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_10.jpg\" alt=\"DMA_10\" width=\"300\" height=\"161\" \/><\/a><\/p>\n<p>My case was first to convert all TEXT,NTEXT and image to VARCHAR(max), NVARCHAR(MAX) and VARBINARY(MAX) like the link <a title=\"deprecated features for SQL Server 2016\" href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/database-engine\/deprecated-database-engine-features-in-sql-server-2016\" target=\"_blank\">here<\/a> to the Microsoft documentation advise us.<br \/>\nFirst, I did a backup from my database and restored it on another server or on the same with a new name. Don\u2019t touch directly the production database! \ud83d\udc7f<br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_11.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\" size-medium wp-image-17497 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_11.jpg\" alt=\"DMA_11\" width=\"300\" height=\"125\" \/><\/a><\/p>\n<p>In my case, I restore the database with a new name on the same server: <strong>db_to_mig_2<\/strong><\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_12.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\" size-medium wp-image-17496 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_12.jpg\" alt=\"DMA_12\" width=\"300\" height=\"124\" \/><\/a><\/p>\n<p>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:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">USE [db_to_mig_2]\nGO\n\nSELECT id,Description into #before FROM dbo.Text_Without_FullText WHERE Description is NOT NULL\nGO\n\nALTER TABLE dbo.Text_Without_FullText ALTER COLUMN Description NVARCHAR(MAX)\nGO\n\nSELECT id,Description into #after  FROM dbo.Text_Without_FullText WHERE Description is NOT NULL\nGO\n\nSELECT DIFFERENCE(a.Description,b.Description), DIFFERENCE(b.Description,a.Description)\nFROM #after AS a INNER JOIN #before AS b ON b.id = a.id\nGO\n\nSELECT Description from #before\nSELECT Description from #after\n\nDROP TABLE #before\nDROP TABLE #after\n\n<\/pre>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_14.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\" size-medium wp-image-17494 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_14.jpg\" alt=\"DMA_14\" width=\"300\" height=\"129\" \/><\/a><\/p>\n<p>I used 2 temporary tables to compare the result after the column data type change.<br \/>\nAs you can see, the result of the select with difference is 4.<br \/>\nThe value 4 indicates strong similarity or the same values.<br \/>\nTo be sure, I advise you to do a better comparison between both temporary tables.<br \/>\nBut this blog is not the case about comparing 2 strings with different data types.<br \/>\nThe conversion is fast and easy, now, I do the same for the table with Full-Text:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SELECT id,Description into #before FROM dbo.Text_With_FullText WHERE Description is NOT NULL\nGO\n\nALTER TABLE dbo.Text_With_FullText ALTER COLUMN Description NVARCHAR(MAX)\nGO\n\nSELECT id,Description into #after\u00a0 FROM dbo.Text_With_FullText WHERE Description is NOT NULL\nGO\n\nSELECT DIFFERENCE(a.Description,b.Description), DIFFERENCE(b.Description,a.Description)\nFROM #after AS a INNER JOIN #before AS b ON b.id = a.id\nGO\n\nSELECT Description from #before\nSELECT Description from #after\n\nDROP TABLE #before\nDROP TABLE #after\n<\/pre>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_13.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\" size-medium wp-image-17495 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_13.jpg\" alt=\"DMA_13\" width=\"300\" height=\"150\" \/><\/a><\/p>\n<p>And the result as you can see is an error message:<\/p>\n<p style=\"color: red\">Msg 7614, Level 16, State 1, Line 1<br \/>\nCannot alter or drop column &#8216;Description&#8217; because it is enabled for Full-Text Search.<\/p>\n<p>The Full-Text Index prevents me from changing the data type.<br \/>\nI need to drop the column from the FullText Index and create it after the data type change.<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SELECT id,Description into #before FROM dbo.Text_With_FullText WHERE Description is NOT NULL\nGO\n\nDROP FULLTEXT INDEX ON dbo.Text_With_FullText;\nGO\n\nALTER TABLE dbo.Text_With_FullText ALTER COLUMN Description NVARCHAR(MAX)\nGO\n\nCREATE FULLTEXT INDEX ON\u00a0 dbo.Text_With_FullText(Description)\nKEY INDEX PK_Text_With_FullText ON FT_Catalog\nWITH STOPLIST = SYSTEM,CHANGE_TRACKING OFF, NO POPULATION;\nGO\n\nSELECT id,Description into #after\u00a0 FROM dbo.Text_With_FullText WHERE Description is NOT NULL\nGO\n\nSELECT DIFFERENCE(a.Description,b.Description), DIFFERENCE(b.Description,a.Description)\nFROM #after AS a INNER JOIN #before AS b ON b.id = a.id\nGO\n\nSELECT Description from\u00a0 #before\nSELECT Description from #after\n\nDROP TABLE #before\nDROP TABLE #after\n<\/pre>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_15.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\" size-medium wp-image-17493 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_15.jpg\" alt=\"DMA_15\" width=\"300\" height=\"131\" \/><\/a><\/p>\n<p>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)<br \/>\nTo finish, I reran the Data Migration Assistant and I saw that the only remaining issue was the Full-Text Search.<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_16.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\" size-medium wp-image-17510 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_16.jpg\" alt=\"DMA_16\" width=\"300\" height=\"129\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":15,"featured_media":10331,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198],"tags":[49,15,51,566],"type_dbi":[],"class_list":["post-10318","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-administration-monitoring","category-database-management","tag-microsoft","tag-migration","tag-sql-server","tag-sql-server-2016"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.4) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Migration to SQL Server 2016 with deprecated data types and Full-Text - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Migration to SQL Server 2016 with deprecated data types and Full-Text\" \/>\n<meta property=\"og:description\" content=\"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 [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-07-11T13:06:26+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_15.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1340\" \/>\n\t<meta property=\"og:image:height\" content=\"583\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"St\u00e9phane Haby\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"St\u00e9phane Haby\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\\\/\"},\"author\":{\"name\":\"St\u00e9phane Haby\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/d0bfb7484ae81c8980fc2b11334f803b\"},\"headline\":\"Migration to SQL Server 2016 with deprecated data types and Full-Text\",\"datePublished\":\"2017-07-11T13:06:26+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\\\/\"},\"wordCount\":520,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/DMA_15.jpg\",\"keywords\":[\"Microsoft\",\"Migration\",\"SQL Server\",\"SQL Server 2016\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\\\/\",\"name\":\"Migration to SQL Server 2016 with deprecated data types and Full-Text - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/DMA_15.jpg\",\"datePublished\":\"2017-07-11T13:06:26+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/d0bfb7484ae81c8980fc2b11334f803b\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/DMA_15.jpg\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/DMA_15.jpg\",\"width\":1340,\"height\":583},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Migration to SQL Server 2016 with deprecated data types and Full-Text\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/d0bfb7484ae81c8980fc2b11334f803b\",\"name\":\"St\u00e9phane Haby\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"caption\":\"St\u00e9phane Haby\"},\"description\":\"St\u00e9phane Haby has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies such as installation, migration, best practices, and performance analysis etc. He is also an expert in Microsoft Business Intelligence solutions such as SharePoint, SQL Server and Office. Futhermore, he has many years of .NET development experience in the banking sector and other industries. In France, he was one of the first people to have worked with Microsoft Team System. He has written several technical articles on this subject. St\u00e9phane Haby is Microsoft Most Valuable Professional (MVP) as well as Microsoft Certified Solutions Associate (MCSA) and\u00a0Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012. He is also Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008 as well as ITIL Foundation V3 certified. He holds a Engineer diploma in industrial computing and automation from France. His branch-related experience covers Chemicals &amp; Pharmaceuticals, Banking \\\/ Financial Services, and many other industries.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/stephane-haby\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Migration to SQL Server 2016 with deprecated data types and Full-Text - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\/","og_locale":"en_US","og_type":"article","og_title":"Migration to SQL Server 2016 with deprecated data types and Full-Text","og_description":"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 [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\/","og_site_name":"dbi Blog","article_published_time":"2017-07-11T13:06:26+00:00","og_image":[{"width":1340,"height":583,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_15.jpg","type":"image\/jpeg"}],"author":"St\u00e9phane Haby","twitter_card":"summary_large_image","twitter_misc":{"Written by":"St\u00e9phane Haby","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\/"},"author":{"name":"St\u00e9phane Haby","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b"},"headline":"Migration to SQL Server 2016 with deprecated data types and Full-Text","datePublished":"2017-07-11T13:06:26+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\/"},"wordCount":520,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_15.jpg","keywords":["Microsoft","Migration","SQL Server","SQL Server 2016"],"articleSection":["Database Administration &amp; Monitoring","Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\/","url":"https:\/\/www.dbi-services.com\/blog\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\/","name":"Migration to SQL Server 2016 with deprecated data types and Full-Text - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_15.jpg","datePublished":"2017-07-11T13:06:26+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_15.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/DMA_15.jpg","width":1340,"height":583},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/migration-sql-server-to-2016-with-deprecated-data-types-and-full-text\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Migration to SQL Server 2016 with deprecated data types and Full-Text"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b","name":"St\u00e9phane Haby","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","caption":"St\u00e9phane Haby"},"description":"St\u00e9phane Haby has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies such as installation, migration, best practices, and performance analysis etc. He is also an expert in Microsoft Business Intelligence solutions such as SharePoint, SQL Server and Office. Futhermore, he has many years of .NET development experience in the banking sector and other industries. In France, he was one of the first people to have worked with Microsoft Team System. He has written several technical articles on this subject. St\u00e9phane Haby is Microsoft Most Valuable Professional (MVP) as well as Microsoft Certified Solutions Associate (MCSA) and\u00a0Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012. He is also Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008 as well as ITIL Foundation V3 certified. He holds a Engineer diploma in industrial computing and automation from France. His branch-related experience covers Chemicals &amp; Pharmaceuticals, Banking \/ Financial Services, and many other industries.","url":"https:\/\/www.dbi-services.com\/blog\/author\/stephane-haby\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10318","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/15"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=10318"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10318\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/10331"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=10318"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=10318"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=10318"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=10318"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}