Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to upgrade my SQL Server 2005 database to SQL Server 2012. In SQL Server 2005 you can set the location of the full text catalog when you create it. But in SQL Server 2012 (and in SQL Server 2008 from what I can deduce from online chatter) you cannot specify the location.

In SQL Server 2005 it was important to be able to set this location because, for performance reasons, we put the catalogs on separate disk. I understand that the full text engine has changed starting in SQL Server 2008 but I can't find information on where the catalogs get physically created and if putting them on a separate disk is still a best practice.

How can I set the catalog location in SQL Server 2008/2012? Or is the best practice of storing them on separate disks no longer necessary to follow?

share|improve this question
    
You should probably be aware that there is no such thing as SQL Server 2011. –  mrdenny Apr 23 '11 at 20:48
    
Excuse me...SQL Denali. –  Keith May 3 '11 at 17:05
    
Just making sure, I've heard SQL Server 2008 R2 called some funny things before. –  mrdenny Jun 30 '11 at 21:08
add comment

2 Answers

up vote 1 down vote accepted
  1. Create a ft catalog
  2. Create a filegroup for your ft index
  3. Create a file in the filegroup you created above on the drive you want the ft index on
  4. Right-click on the table you want to create the ft index for
  5. Select Full-text Index -> Define Full-text index...
  6. On the first page of the wizard, click Next
  7. Select the unique index to use, click Next
  8. Select the columns to index, click Next
  9. Select the preferred change tracking, click Next
  10. Select the ft catalog you created
  11. Use the Select index filegroup pulldown to select the ft filegroup you created
  12. Click Next
  13. Create a schedule if you wish, click Next
  14. Click Finish, click Close
  15. Richt-click the table, select Full-text Index -> Properties
  16. Verify the correct filegroup is indicated
share|improve this answer
1  
As per the other answer, is this still needed? –  gbn Feb 23 '12 at 7:57
    
This may be the answer I'm looking for but I need some clarification. My question was about the location of the full text catalog. These steps set the location of the full text index. Is the effect the same? –  Keith Feb 24 '12 at 16:37
    
I verified that this is the ideal approach for SQL Server 2008 and 2012. The files' physical locations are now determined by the full text index, not at the catalog-level anymore. Using this setting I can achieve the performance boost I was looking for by storing the full text indexes on a separate drive from other db files. –  Keith Feb 26 '12 at 15:57
add comment

Keith,

Full-text catalog had a complete architecture change in 2008 & above and that's why this option is removed and NOT necessary. Definitely NOT the answer you were looking for :-)

Ref: http://msdn.microsoft.com/en-us/library/ms189520(v=SQL.100).aspx

Important Beginning with SQL Server 2008, a full-text catalog is a virtual object and does not belong to any filegroup. A full-text catalog is a logical concept that refers to a group of full-text indexes.

ON FILEGROUP filegroup Beginning with SQL Server 2008, this clause has no effect. IN PATH 'rootpath' Note This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Beginning with SQL Server 2008, this clause has no effect.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.