Multiple SQL Server instances with FileStream enabled

Posted on

Question :

Is it possible to have two SQL Server instances, one 2008 R2 and another 2012, to run on the same machine with both having filestream enabled?

I tried to setup this in local dev machine and I can get the services running. But when I try to read a filestream from the 2008 R2 instance, I get a ArgumentException.

The path in both instance looks alright.

SQL Server 2008 R2

\DEVMCMSSQLSERVERv1TESTDBdbocoreFileStorefileData525F2031-8D8C-45FF-8386-E1DD5F11C960

SQL Server 2012

\DEVMCEXPRESS2012v1TESTDB12dbocoreFileStorefileData525F2031-8D8C-45FF-8386-E1DD5F11C960

Is there any magical setting which could make it work in both instances or is just simply not do-able?

Here is the exception stacktrace when trying to access the file using C# test code.

System.ArgumentException : An invalid parameter was passed to the function.
   at System.Data.SqlTypes.SqlFileStream.OpenSqlFileStream(String path, Byte[] transactionContext, FileAccess access, FileOptions options, Int64 allocationSize)
   at System.Data.SqlTypes.SqlFileStream..ctor(String path, Byte[] transactionContext, FileAccess access, FileOptions options, Int64 allocationSize)
   at System.Data.SqlTypes.SqlFileStream..ctor(String path, Byte[] transactionContext, FileAccess access)
   at SimpleTests.FileStreamTest.ReadFilestream(String connectionstring, String fileid) in FileStreamTest.cs: line 47

And here is the NUnit test method I used to verify the issue. FileStore access to TESTDB fails, but TESTDB12 works fine.

[TestCase(new object[] { "Data Source=localhost;Database=TESTDB;Integrated Security=True;Connect Timeout=120;", "525F2031-8D8C-45FF-8386-E1DD5F11C960" })]
[TestCase(new object[] { "Data Source=localhost\express2012;Database=TESTDB12;Integrated Security=True;Connect Timeout=120;", "9AC28DC0-B927-4075-8D31-00E343E15663" })]
public void ReadFilestream(string connectionstring, string fileid)
{
    const string _selectSql =
        @"SELECT fileData.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT()
  FROM coreFileStore
  WHERE fileStoreId = @FileStoreId";

    using (SqlConnection connection = new SqlConnection(connectionstring))
    {
        connection.Open();
        using (SqlTransaction transaction = connection.BeginTransaction())
        {
            string filepath;
            byte[] txnInfo;
            using (SqlCommand command = connection.CreateCommand())
            {
                command.CommandText = _selectSql;
                command.Parameters.AddWithValue("FileStoreId", new Guid(fileid));
                command.Transaction = transaction;
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    reader.Read();
                    filepath = reader.GetString(0);
                    txnInfo = reader.GetValue(1) as byte[];
                }
            }

            Console.WriteLine(filepath);

            using (SqlFileStream sqlFileStream = new SqlFileStream(filepath, txnInfo, FileAccess.Read))
            {
                using (StreamReader reader = new StreamReader(sqlFileStream))
                {
                    string x = reader.ReadToEnd();
                    Console.WriteLine(x);
                }
            }
        }
    }
}

Answer :

Did found the answer in SQL Server support site.

https://connect.microsoft.com/SQLServer/feedback/details/793017/filestream-problem-when-installing-sql-server-2012-sp1-side-by-side-with-sql-server-2008-r2

It’s a bug in SQL Server 2012. Hear is the copy of workaround suggested.

A workaround is to put the FILESTREAM containers of either a SQL Server 2012 instance or a prior version instance in a different volume by relocating or recreating them.

This will make the SQL Server 2012 RsFx driver (RsFx020x) attached to a different volume, so it won’t interfere with the requests to the driver of a prior version.

For more info on relocating filestream dirs, please see http://msdn.microsoft.com/en-us/library/ms345483.aspx.

After relocation or recreation (it might need to restart the SQL Server), “fltmc instances” command will indicate if the RsFx drivers are attached to different volumes. For example, the result should look like this (excluding unrelated lines):

Filter Volume Name Altitude Instance Name

RsFx0201 C: 41002.01 RsFx0201 MiniFilter Instance 0

RsFx0105 D: 41001.05 RsFx0105 MiniFilter Instance 0

Leave a Reply

Your email address will not be published.