May 3, 2023 at 11:07 pm
Hello All,
I have four VMs setup while I am learning SQL Server Admin tasks and configurations. SVR01 is the DC, the other three are the servers to be used. I am attempting to setup Failover clustering, however I run into this error every single time, no matter what folder I use, what name I give it, what permissions it has...
Checking for compatibility of the database file locations on the server instance that hosts secondary replica SVR02\MSSQLSERVER02 (Microsoft.SqlServer.Management.HadrModel)
This pops for both secondary servers. I had a folder on the C drive on SVR01 that was shared, backed up the two DBs, and restored them on SVR02 and 03. I relocated the data file (not the log file, maybe I need to do that too?) to use the shared folder.
I even mapped that folder as a network drive, still didn't work.
Then I created a shared folder on the DC - I mean why not? No dice there either.
What am I missing?
Thanks,
Mike
May 4, 2023 at 8:32 am
What type of 'Always On' are you trying to deploy as the term 'Always On' encompasses both 'Always On Failover Cluster Instances (FCI)' and 'Always On Availability Groups (AOAG)'.
Both of these can and do have very different hardware/software setups, so depending on what you have already deployed VS what you are now trying to implement, you may have to destroy what you already have to make new and deploy something else.
For example, an FCI requires shared disks, which allow the actual hard drive to move between nodes.
This is NOT a shared folder / UNC drive which you normally access via \\someserver\someshare, the two are completely different .
Where as an AOAG requires that individual storage is presented to each replica, and not shared in any means.
If you can detail what you already have deployed, what you are trying to switch to, it will give a clearer picture of the steps which are needed to make the switches you are wanting to do.
May 5, 2023 at 1:00 am
I am taking some pre-recorded online classes and we went through the process of creating Failover Clusters, but now we are working on the AG. (I think I got the two FCI and AG mixed up in the OG post).
So, I created a cluster using the Failover Cluster Manager on SVR01. Added two additional servers. Enabled the Always On group on each node.
The instructor states that we need to have a shared folder. BTW - I just realized that I did not mention all machines are VMs on my workstation. The folder he uses is the default MSSQL\Backup\ folder on SVR01 - shared to all other servers. This is for the DB backup files only.
In the AG - added three servers, enabled automatic failover, synchronous mode, with readable secondary. Did not change Endpoints. Created Listener.
On the "Select Data Synchronization" page, the instructor uses the "Full" option, and for the shared location: \\Server1\Backup. His validation completes without issue, however I get the error mentioned above. A quick search reveals that each server is looking for the exact same path. And that's where I'm stuck.
Hope this helps you understand my situation.
Thanks
May 5, 2023 at 8:13 am
OK, so you have built the cluster, created the AG and all thats working, it's just the databases wont add into the group.
So with the FULL method, you need to ensure you have the same disk and folder structure on all the machines, if anything is slightly off it can throw all sorts of weird and wonderful errors. In all fairness even if not using the FULL method it's still best placed to have everything exactly the same.
The only two options in an AG setup I would generally ever advocate for are "Join Only" and "Skip Initial Data Sync", and that is because I like to be in control of performing the backups, doing the restores and making sure things go into the right places. Call me paranoid but thats part of being a DBA, you need the paranoia to make sure your not doing anything silly.
So make sure you have the same disks, same folders on all the machines and try the full option again, otherwise manually backup and restore the databases on the new machines and use the "Join Only" option.
May 5, 2023 at 4:56 pm
Ok, I got you.
I got it to work using the "Join Only" option, but I'm still wondering why I can't use a shared folder? If I were to create a virtual disk on my workstation, add that to the VM as a shared folder, do you think that would work? Maybe it won't really matter, though, since once it's setup, I will be automating the backups.
Thanks,
Mike
May 5, 2023 at 7:40 pm
Ok, I got you.
I got it to work using the "Join Only" option, but I'm still wondering why I can't use a shared folder? If I were to create a virtual disk on my workstation, add that to the VM as a shared folder, do you think that would work? Maybe it won't really matter, though, since once it's setup, I will be automating the backups.
Thanks,
Mike
I am a bit confused - you stated you relocated the mdf (data file) to the shared location. If that is the case - then I am not surprised it is failing. The data and log files need to be located in the same location on each server. That is, if you have DB1 and the data file is on D:\MSSQL\Data on SRV1 then it needs to reside on D:\MSSQL\Data on SRV2 and SRV3.
The other servers are replica's - and being a replica they need to mirror the primary exactly. If the primary needs to extend the data file or log file, that command is replicated to the secondary instances. If the files don't exist in the same location then SQL Server will not be able to extend those files on those secondary instances.
There is another option available - which is automatic seeding. This option allows you to add the database to the primary - and SQL Server performs the backup/restore in the background for you and then joins the database to the AG. You can add a specific trace flag that improves the performance (compresses the data for automatic seeding).
As long as the file layout and folders all exist on the secondary nodes - then automatic seeding works quite well. It can work well for very large databases too - but not something I would recommend doing. The join only option is the one I generally use for larger databases.
One final thought - when using the join method, the database on the secondary will be restored with the owner defined as the login performing the restore. You need to account for that if the owner needs to be changed - which you will not be able to change until you failover to that secondary. If you manually restore the database, change the owner (to 'sa' for example) - then restore the database again using REPLACE then the owner will be the current owner (sa in this case). This way you don't have to perform that extra failover to change database settings.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 5, 2023 at 8:34 pm
I am a bit confused - you stated you relocated the mdf (data file) to the shared location.
Well, I don't have a separate drive to locate the mdf and log files to, only the shared folder on SVR01. (\\SVR01\Backup) So... Let's say create a folder on SVR01, map it as a network drive, then backup and restore from that folder, relocating all mdf and log files to said folder... That should work, right? If so, I must have missed something in the configuration because it didn't work earlier. And if I understand correctly, using the default folders on each server won't work, correct? Just want to make sure I understand. Thanks for putting up with my lack of knowledge 🙂
I used the shared folder to relocate when I manually restored each DB to the replicas.
Regarding the DB owner, I did have to make sure the same acct had the same permissions on each server.
May 5, 2023 at 8:53 pm
You cannot relocate the mdf/ldf files to a shared folder - nor would you want to do that. The database files need to exist on non-shared storage for an availability group.
Each instance is a replica (an exact copy) of the primary. So each server would have a local drive - for example the C:\ drive and would host a copy of the database. So if you created the database on the primary and place the data files in the default location on the C:\ drive - then you would 'create' the database on the secondary in the exact same path.
The shared folder is only needed to host the backup file that is used to create the database on each secondary.
The point about the owner is that you can avoid having to failover to change the database owner (and other database properties) by restoring a copy with recovery, setting those options, then restoring with no recovery and joining to the AG. If using automatic seeding - that option isn't available because you have to delete the database on the secondary before adding it to the group, which then requires a failover to make those types of changes.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply