4

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

\\DEVMC\MSSQLSERVER\v1\TESTDB\dbo\coreFileStore\fileData\525F2031-8D8C-45FF-8386-E1DD5F11C960

SQL Server 2012

\\DEVMC\EXPRESS2012\v1\TESTDB12\dbo\coreFileStore\fileData\525F2031-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);
                }
            }
        }
    }
}
  • It might be helpful to show us the actual exception text. – Max Vernon Oct 21 '15 at 14:00
  • Also I checked the .Net Framework source and it was failing at the call to NtCreateFile method with the status code 0xC000000D, which according to MSDN is an STATUS_INVALID_PARAMETER. msdn.microsoft.com/en-au/library/cc704588.aspx – Kumar Oct 27 '15 at 2:17
  • since there is no response. I am going to assume that its not possible. – Kumar Dec 7 '15 at 23:01
  • This sounds like a good question to ask the Microsoft SQL Server Support Team. If you get an answer, you may want to answer your own question here so future visitors can also benefit from your experience. – Max Vernon Dec 8 '15 at 19:33
  • Good suggestion Max vernon. – Kumar Dec 21 '15 at 6:14
1

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

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.