0

I inherit a setup of two nodes (primary & secondary replica) Availability Group.

On my secondary replica node, the SQL server instance default files are stored at:

enter image description here

My default primary replica node files are as follow:

enter image description here

When I look at the synchronizing database's properties on my secondary replica, I see its database files as follow

enter image description here

They are primary replica node directories. Is this how it works in Availability Group?

That is when you look at the file group of your secondary replica database, they show the paths of the primary replica database even though the data file and log file are stored at a different local directory?

Improve this question
4
  • 1
    Those are default locations, they may not be the actual file locations, I would look at each databases file properties to see where the files actually are.
    – James Jenkins
    Jun 17, 2020 at 15:12
  • 1
    And read the recommendation here: learn.microsoft.com/en-us/sql/database-engine/…
    – David Browne - Microsoft
    Jun 17, 2020 at 15:17
  • Thanks James & David. If I have my choice I'd make both nodes having the same disk drive and that's what I'm trying to figure out with this question. In the secondary node when I look at the database file under the properties it points to G: and H: but those drive do not exist on my secondary node. That is why I assume it just show what is on the primary replica and not the actual path of the database.
    – Fylix
    Jun 17, 2020 at 15:19
  • 1
    You or your server support team should be able to rename the drives. You will want to practice on a non-prod system. If your using virutual servers, it might be easier to build two new servers correctly, add them to the AG, and then fail over to the new better servers and retire the old ones.
    – James Jenkins
    Jun 17, 2020 at 18:12

1 Answer 1

Reset to default
2

If you try and open the database properties window on a secondary replica, SSMS will throw you an error, so my guess is that you're not actually connected to the secondary replia when you're checking these paths.

Run the following on both nodes:

select db_name(database_id), name, physical_name
from sys.master_files

This will show you the physical paths that SQL Server is using to access each file, and I would expect will show you different paths on each replica.

Improve this answer
1
  • Thank you, you helped me verify my assumption in my question. When I ran the script on the secondary replica database, it shows all my data file and log files on L: and K. However, when I right click on the actual database under the object explorer, it shows the files are at G: and H which aren't the file path of the secondary replica node but instead the primary replica node.
    – Fylix
    Jun 18, 2020 at 19:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.