April 9, 2024 at 10:34 am
Hello,
I am testing the setup of contained availability groups. I am getting some strange errors while creating contained availability groups both through wizzard and with TSQL.
I started with creating a contained availability group with the wizzard on MSSMS. Setup:
On both primary and secondary replicas I selected Automatic failover and synchronous commit.
Endpoints were correctly set automatically on port 5022:
I opened TCP port 5022 on firewall on BOTH machines.
Backup was set to be prefered on secondary.
static IP was set and it was pingable, port was set and opened on firewall on both machines.
The errors in Availability group dashboard:
The errors in SQL Server log:
Directory lookup for the file 'E:\data\MSSQL99.sql1\MSSQL\DATA\groupAG_master.mdf' failed with the operating system error 3(The system cannot find the path specified.).
Here is the confirmation that it really is trying to restore to primary node path (sql1) instead of SQL2. So my question is how can I affect where the .mdf and .ldf path of master and msdb database will be if I the wizzard is restoring the databases.
I tried using TSQL aswell, however the problem was the same. Because when you set the command you still don't have the power to change where SQL instance will restore the files to.
Then I thought to double check if there is possibly anything wrong with my server configuration (endpoint for instance) or ports or anything like that. So I created a NORMAL (non-contained AG). It worked as expected. The databases were seeded automatically and the databses were in Synchronized state.
After that I went back to documentation. I found this (https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/contained-availability-groups-overview?view=sql-server-ver16#restore-a-contained-system-database):
Restore a contained system database
You can restore a contained system database using one of two different ways.
Restore a contained database using a secondary replica:
Restore a contained database by dropping the contained AG:
I followed proposed solution, I was paying close attention to really use the Restore with NORECOVERY command while restoring master and msdb, however the end result was that primary node had everything setup as it should. But the secondary node had databases in a weird state master database was Initializing / In recovery, but the others were in status Recovering...
Does anyone have any idea, how to deal with this. It would be great to be able to use contained availability groups over availability groups, because it would mean I wouldn't need to transfer Jobs and Users/Logins manually.
Best regards!
April 10, 2024 at 11:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
April 10, 2024 at 9:31 pm
Maybe I am a bit out to lunch here, but shouldn't your primary and secondary instances be the same name and have the same path to the system files but different physical volumes? E: is going to be a different physical disk on server A and server B, but the path should be the same between both of the systems, right?
If the primary fails over to the secondary, you don't want to have to update your clients to the new name. that defeats the purpose of the failover. When a failover occurs, it should be 100% transparent to the end users.
What I mean is if your instance on server A is called "sql1", then on server B it should be called "sql1" as well and have the same file paths.
Is there a reason that you set it up so that they have different paths?
If you NEED the system to have a different name/path on disk, can you just create the path it is expecting and move the system files to that location during the restore so that the file paths work how SQL is expecting?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 15, 2024 at 12:14 pm
Hi,
well I don't necessary need my file path to be at E:\data\MSSQL99.sql1 OR E:\data\MSSQL99.sql2, however this is the path that is automatically assigned in the creation of the instance (sql1 and sql2 are both instances on different servers).
I guess the problem while creating the CONTAINED AG's is that it creates system databases (master and msdb). The instance creates them on their own respectable path. When I create them on server sql1 it creates .mdf file on path E:\data\MSSQL99.sql1. Then when I run the "Create availability group" wizzard it can't proccess a restore because it wants to restore the database under that same path of instance sql1.
This is the workaround I used:
It works, however it doesn't feel the best possible way to do it. So my question is since master and msdb databases gets created under System database directory path is it possible to change that in any way? Should both instances that are part of the AG have the same path to the System database directory?
Edit FIX: You were correct. All I had to do was create an instance on both server which has the same name. That fixed the path of the instance files, which was now the same for both.
April 17, 2024 at 9:43 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply