Adding file to filegroup containing filestreams does not work in SQL Server 2022 RC1 Express Edition

Question

Adding file to filegroup containing filestreams does not work in SQL Server 2022 RC1 Express Edition

David Wimmer 6

Hi,

just tested SQL Server 2022 RC1 Express Edition and I am getting an undocumented error when I am adding a file to a filegroup containing filestreams.
This only fails in the Express Edition adding the file/filegroup to an existing database.
Does not fail in Evaluation Edition or Developer Edition.
Does not fail when creating the file/filegroup with CREATE DATABASE.

Steps to reproduce:

Install SQL Server 2022 RC 1 Express Edition with Filestream Feature
Run the following script in SQL Server Management Studio
CREATE DATABASE [FS_Test]
ON PRIMARY
(NAME = N'FS_Test', FILENAME = N'C:\databases\FS_Test.mdf')
LOG ON
(NAME = N'FS_Test_log', FILENAME = N'C:\databases\FS_Test_log.ldf')
GO ALTER DATABASE FS_Test ADD FILEGROUP fsGroup CONTAINS FILESTREAM
GO ALTER DATABASE FS_Test ADD FILE (NAME = 'FS_Test_fs', FILENAME = 'C:\databases\FS_Test_fs') TO FILEGROUP fsGroup;
GO
After ALTER DATABASE ... ADD FILE the following undocumented error occurs.

Msg 35221, Level 16, State 1, Line 13
Could not process the operation. Always On Availability Groups replica manager is disabled on this instance of SQL Server. Enable Always On Availability Groups, by using the SQL Server Configuration Manager. Then, restart the SQL Server service, and retry the currently operation. For information about how to enable and disable Always On Availability Groups, see SQL Server Books Online.

The error is not listed database-engine-events-and-errors documentation.

The SQL Server installation ConfigurationFile.ini

----------

System Info:
Microsoft SQL Server 2022 (RC1) - 16.0.950.9 (X64) Sep 13 2022 13:09:32 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows Server 2022 Standard 10.0 <X64> (Build 20348: ) (Hypervisor)

Erland Sommarskog 128.7K Reputation points MVP Volunteer Moderator

2022-10-19T21:36:51.117+00:00

I have run into the same error. But in my case I tried to add a second log file to a database.

And this was on Evaluation Edition. What I noticed was that it works on my default instance, but on a named instance on the same machine.

I tried your script on my instances, and result was the same: works on the default instance, fails on the named instance.
David Wimmer 6 Reputation points

2022-10-20T06:30:17.05+00:00

Good hint. It actually only fails on named instances. Doesn't matter which edition you install.
If I install the Express Edition as the default instance, the script also works.

7 answers

Your answer

Erland Sommarskog 128.7K Reputation points MVP Volunteer Moderator

2022-10-19T21:36:51.117+00:00

I have run into the same error. But in my case I tried to add a second log file to a database.

And this was on Evaluation Edition. What I noticed was that it works on my default instance, but on a named instance on the same machine.

I tried your script on my instances, and result was the same: works on the default instance, fails on the named instance.
David Wimmer 6 Reputation points

2022-10-20T06:30:17.05+00:00

Good hint. It actually only fails on named instances. Doesn't matter which edition you install.
If I install the Express Edition as the default instance, the script also works.

Answer 1

Erland Sommarskog 128.7K MVP Volunteer Moderator

For what it's worth, I have reported this on
https://feedback.azure.com/d365community/idea/212a7e0e-bc50-ed11-a81b-000d3adfeb99.

PandaPan-MSFT 1,931 Reputation points

2022-10-21T01:53:59.317+00:00

I checked your sharing advice thanks so much for your contribution to the Microsoft !

Answer 2

David Wimmer 6

Got an update from technical support.

There is a workaround for this issue with enabling the trace flag 12324.

DBCC TRACEON(12324, -1)

A server restart will reset the trace flags activated with DBCC TRACEON, so you have to add a startup parameter for permanent activation.

The support engineer can not guarantee this will be fixed with a cumulative update, hopefully the will fix it.

Clive 6 Reputation points

2022-12-02T04:18:12.977+00:00

Hi @David Wimmer ,

I have tried it and it works! Thank you so much!!!
Moa'yad Raja 6 Reputation points Microsoft Employee

2022-12-08T05:28:45.873+00:00

The issue will be fixed in CU1, documentation below :

https://learn.microsoft.com/en-us/sql/sql-server/sql-server-2022-release-notes?view=sql-server-ver16#availability-group-replica-manager
Clive 6 Reputation points

2022-12-09T08:40:22.29+00:00

Many Thanks! Great to hear that!

BTW, the document mentioned:

> To work around this issue, you can use Trace Flag 12342 as either as startup trace flag, or at the session level (using DBCC TRACEON).

Should it be Trace Flag "12324"?
Erland Sommarskog 128.7K Reputation points MVP Volunteer Moderator

2022-12-09T23:10:28.407+00:00

Indeed it should! Thanks for pointing out the error. I have edited the topic and created a pull request. Hopefully, the Docs people get it out soon.
Clive 6 Reputation points

2022-12-12T09:04:23.567+00:00

@Erland Sommarskog Thanks a lot!
Erland Sommarskog 128.7K Reputation points MVP Volunteer Moderator

2022-12-12T21:36:54.72+00:00

The page has now been corrected. Again, big thanks for helping the community by pointing out this error!

Answer 3

Salah fazai 1

Installer ou ajouter une table ou unficher

Answer 4

PandaPan-MSFT 1,931

Hi @David Wimmer ,
I think it might be the version problem, cuz the express version does not support some funtions like the following picture shows:

You can check this official link, I hope this can be helpful.
https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-editions-and-components-2022?view=sql-server-ver16

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

David Wimmer 6 Reputation points

2022-10-20T06:38:59.98+00:00

This issue should have nothing to do with Always On Availability Groups. In my opinion the error message is misleading.

As @Erland Sommarskog mentioned the error occurs on named instances in all editions (Developer, Evaluation and Express).
If I install the Express Edition as default instance (unnamed) this script also works.
PandaPan-MSFT 1,931 Reputation points

2022-10-20T07:28:39.167+00:00

Hi @David Wimmer
Then I think the next soultion is clear that you just need to use SSCM to change the TCP/IP port into 1433.
David Wimmer 6 Reputation points

2022-10-20T08:27:19.117+00:00

I changed the port from dynamic to 1433 in SSCM and the same error occurs.
PandaPan-MSFT 1,931 Reputation points

2022-10-20T09:05:19.227+00:00

Hi @David Wimmer ,
May I ask did you also change the set of firewall. Pls try this code in CMD: telnet 127.0.0.1 XXXX(port number). So far that's the soultion I can come up with.........If it still doesn't work, I will continue trying other soultions.
David Wimmer 6 Reputation points

2022-10-20T09:48:20.687+00:00

The telnet command works. The error in SQL Server still occurs.
Erland Sommarskog 128.7K Reputation points MVP Volunteer Moderator

2022-10-20T21:08:39.18+00:00

Panda, before you come with all sorts of funny suggestions, why don't you test the ideas on your own first?

This is a plain bug in SQL Server 2022, which preferably should be fixed before RTM.
PandaPan-MSFT 1,931 Reputation points

2022-10-21T01:14:30.62+00:00

My dear Er @Erland Sommarskog my test tool is a VM, and there are also many limitaions about downloading the software. Besides some kind of problems I can't reproduce on my environment. My job is to provide possbile ideas and advice to help SQL Server user. And I don't know why you define the suggestion as funnny ？ I feel confused that try helping others with the definition of funny or not funny. :)
Erland Sommarskog 128.7K Reputation points MVP Volunteer Moderator

2022-10-21T21:22:38.1+00:00

I can't speak for David, but if I go to a forum and ask about a problem, and I get a reply from someone who appears to represent the vendor in question, I expect a reply that is at least somewhat on the mark. I don't expect wild guesses or suggestions that have no relevance to the problem. Such replies only serve to give the vendor a bad reputation.

(To be fair: this has occurred to me with other vendors than Microsoft as well. But this is still not how you give support.)

What I would expect of you in this particular case is that you reproduce the problem yourself, and then alert the Product Group about this issue, as this is a potential blocker for the release of SQL 2022.

Answer 5

David Wimmer 6

I already posted it there yesterday in the hope that someone there would take care of it.

https://feedback.azure.com/d365community/idea/b35e9417-5e50-ed11-a81b-000d3a7c50ce

Share via

Adding file to filegroup containing filestreams does not work in SQL Server 2022 RC1 Express Edition

7 answers

Your answer