3

We are new to using clustered shared volumes and SQL Server 2016 Enterprise Edition.

When restoring SQL Server 2005 databases with full text search catalogs onto our new 2016 instance, we get the following SQL Server error:

Msg 7627, Level 16, State 1, Line 2
Cannot create the full-text catalog in the directory C:\ClusterStorage\Volume2\SQLData\LogBook for the clustered server. Only directories on a disk in the cluster group of the server can be used.

We have restored this same database to a SQL Server 2016 without issue using:

USE [master];
RESTORE DATABASE [MYDATABASE]
FROM DISK = N'\\BACKUPSERVER\BACKUPS\DATABASE\Database_backup_201805070600.bak'
WITH FILE = 1,
     MOVE N'DatabaseData'
     TO N'C:\ClusterStorage\Volume2\SQLData\Database.mdf',
     MOVE N'DatabaseLog'
     TO N'C:\ClusterStorage\Volume1\SQLTranLogs\Database_log.ldf',
     MOVE N'sysft_LogBook'
     TO N'C:\ClusterStorage\Volume2\SQLData\LogBook',
     NOUNLOAD,
     REPLACE,
     STATS = 5;

Have any of you ran into this using clustered shared volume instead of just a plain volume?

Here are some shots of the setup on the cluster using CSVs.

enter image description here

enter image description here

enter image description here

enter image description here

Using the old method on shared volumes our dependencies looked like this example:

enter image description here

Now using clustered shared volumes which are using SMB they look like this:

enter image description here

  • Have you checked this - support.microsoft.com/is-is/help/295732/… ? – Kin Shah May 9 '18 at 15:03
  • I came across that and it was a dead end. This article explains why. edwinmsarmiento.com/csvs-with-sqlfci The CSVs do not have dependencies. – JFlorida May 9 '18 at 15:09
  • This is restoring a database to SQL 2016 Enterprise that uses CSVs. All databases restore perfectly except ones with FTS. – JFlorida May 9 '18 at 17:42
  • When you speak of "cluster group" do you mean role? Under the role I see the SQL Server application as a resource. – JFlorida May 9 '18 at 17:49
  • yes, 'Role' is the correct terminology on 2016. – Max Vernon May 10 '18 at 17:57
3

In Windows Failover Cluster 2016, you need to ensure all storage (except for node-local-tempdb) is configured as a dependent resource in the SQL Server cluster role.

In the example below, you can see this cluster has two disks, Disk_F and Disk_G; those are the only places where SQL Server will allow us to create data and log files for SQL Server.

enter image description here

When restoring a SQL Server 2005 database that has a full text catalog, you likely need to specify the location of the full-text catalog files, otherwise SQL Server will attempt to put them in the default location, which may not be on a cluster shared volume. Use RESTORE FILELISTONLY FROM DISK = '\path\to\backup'; to determine the files that exist in the backup. Ensure your restore statement has a MOVE clause for each file in the backup.

As a workaround, you might try restoring the SQL Server 2005 full-text-search-enabled database onto a non-clustered SQL Server 2016 instance, then take a backup of the database from the 2016 instance to restore onto the clustered 2016 instance. I'm thinking it's possible there is a bug in the restore process for old full-text-enabled databases onto modern clustered instances.

  • I updated my original post with some screenshots of the configuration on the volumes and dependencies. – JFlorida May 10 '18 at 17:53
  • @JFlorida - I updated my answer to show a possible workaround – Max Vernon May 10 '18 at 17:57
  • That is a good idea. I was just trying to make the migration a bit smoother using less down time. :-) – JFlorida May 10 '18 at 18:05
  • just try it in your dev environment first to see if that's a viable path – Max Vernon May 10 '18 at 18:09
  • 1
    Will do. Thank you and I will let you know. – JFlorida May 10 '18 at 18:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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