In this series, I plan on sharing a lot of my recent experience with the SQL Server 2005 to SQL Server 2008 upgrade. Part of our ongoing project was to also use some of the new fantastic full text features in SQL Server 2008, which are now fully integrated. We had some difficulties in turning this on after the upgrade.

Our upgrade strategy was to backup a database on SQL Server 2005 and restore it on SQL Server 2008. The most important to note about that strategy is you will need to set the databases to 10 compatibility mode after you do the restore and you'll need to rebuild your statistics.

After that though, we went to create our first full text and we noticed that our Define Full Text button and all other full text options were greyed out. After some time of troubleshooting, we looked at the sys.databases databases table and noticed that the is_fulltext_enabled was turned off for the database, which was supposedly deprecated (http://technet.microsoft.com/en-us/library/ms403375.aspx). In reading, the Technet articles stated that the enabling full text is no longer supported as well, since it's always on.

To get this working, we had to use an older stored procedure which gladly wasn't deprecated yet:

sp_fulltext_database 'enable'

We ran that on each of the databases, and then we were off and running to the races.

I hope this series saves you some time and aggravation. My next one will be on some issues I ran into with the SSIS data providers during the upgrade.

- Brian Knight