A question came across my inbox this week which I decided to investigate: what happens if you have read only filegroups as part of your database, and you want to use Always On Availability Groups? Let’s find out.

First, I have a database with two filegroups: one read write (PRIMARY) and one read only (ROFG).

Figure 1. Filegroups

I also have a table (ROTable) created on ROFG.

Figure 2. Table on a read only filegroup

I then created an AG named ROFGAG. I selected automatic seeding (more on that in a second) to initialize my secondary replica. According to the Wizard, all is good … or is it?

Figure 3. Successful AG creation

I checked the status of the AG and it wasn’t synchronizing. Seeing as this is not my first rodeo, I started looking and lo and behold, the database was not joined to the AG even though the Wizard said everything was OK at the end of creation.

Figure 4. DB not joined to the AG

So there appears to be a bug in the AG creation where if you have a read only filegroup and select automatic seeding, it didn’t initiate the seeding for some reason. Looking in the SQL Server log on ALEX,it gave me the telltale permissions issue.

Figure 5. Seeding permission not granted

Granted, this is SQL Server 2016 where there’s the 3 minute “issue”, but that generally worked OK through the Wizard. I manually issued the ALTER AVAILABILITY GROUP ROFGAG GRANT CREATE ANY DATABASE and lo and behold …

Figure 6. Seeding initiated

You can also manually back up the database, copy it to the other server, and restore it to the instance that is the secondary replica. Either way, once the database was on the secondary replica, with seeding , it joined to the AG just fine. If you manually restore the database with NORECOVERY, the database has to be joined to the AG.

Figure 7. Join the DB to the AG

I inserted a few rows into a table on the read write filegroup, and everything is synchronized as expected. Keep in mind that on the secondary replica you have the same files as the primary and both filegroups. So having a read only filegroup works just fine with an AG.

Figure 8. AG status

I now deviated from the plan. What if you just want the read write filegroup on the secondary replica, and not everything? Does that work? To do this, I removed ALEX as a secondary replica and then deleted ROFGTestDB from that instance. On ALEX, I then restored ROFGTestDB, but just the read write filegroup. That means I wouldn’t get ROFG or its corresponding .ndf file.

Figure 9. Restoring just the read write filegroup

Since I restored the database, in the Wizard I just selected the Join only option for initial data synchronization. SQL Server thinks all is fine … or is it?

Figure 10. Adding the replica with no read only filegroup

Unfortunately, the AG is not synchronizing even though it joined supposedly with no issues. The AlwaysOn_health extended events trace shows the join, but no other information indicating a problem. The same holds true for the SQL Server log. I was inserting rows into a table, and that DDL executed fine, so the issue is apparently not on the primary.

Figure 11. All is not well

At this point, I removed the secondary replica from the AG. That completed successfully, but after doing that, ROFGTestDB on ALEX (where the restore without the read only filegroup happened) is showing as Suspect.

Figure 12. ROFGTestDB is suspect

The only difference is that before when things worked, all of the filegroups were restored. I deleted ROFGTestDB from ALEX, and then restored it from a backup WITH NORECOVERY with both the read write and read only filegroups.

Figure 13. ROFGTestDB restored

I then joined ALEX to ROFGAG, inserted a few rows, and everything works as expected.

Figure 14. AG working

It looks like AGs work with DBs that have read only filegroups, but you have to make sure you have all files and filegroups. If you restore the database with only the read write filegroup(s), it appears the AG will not synchronize. I plan on following up with Microsoft to see if this is expected behavior, but it is definitely interesting because it reports success when it really is failing. That also means that at least the way things work today, you can’t have a smaller footprint on a secondary replica – it’s all or nothing.

Hope this helps some of you!