Sql-server – Can two SQL “Always on” nodes have different datafiles position on disk

sql serversql server 2014

I have two Always on instances SQL server 2014 configured on two servers (primary read/write, secondary only sync). First instance has 13 db and the second has only one. I need to move all the transaction log datafiles from a disk to a new one. All the transction logs reside in a disk (for example D: on both nodes).

I would like to stop the sync from the secondary, move the transaction log position (for example from D: to E:) and then resync all.

In this way I will have a primary node working with transaction log on old D: and secondary node working on a new E: file system.

Can someone help me to understand if SQL Always on can work with different datafiles paths on the two nodes?

Second question (if the first is feasible): all the services will continue to run on the first node. When I will try to resync the secondary node, how much confidence can I have to achieve the activity without reconstructing the secondary from a backup?

[UPDATE]

Try to answer the below question for more detail about my second question.

I have those steps in my mind (but I don't know if they are correct)

Preliminary status: SERVER1 primary node. SERVER2 secondary. Two instances running.
One DB at a time cycle those three steps:

  • Stop sync on SERVER2 (SERVER1 working).
  • Move the transaction log from the old position to the new one on SERVER2 (It should be something like detach-update config-move datafiles-attach)
  • Start Sync

After all do this two:

  • Failover both instances to SERVER2
  • Do the same on SERVER1

The above steps should guarantee that all the instances continue to work without service outage (in my mind).
I'm trying to figure out how much safer it can be to perform those steps instead of creating a new disk and adding new tranlog datafiles on it without moving the old one (only stopping autogrowth).

Thanks so much

Best Answer

Having different file paths for secondary databases in an Always On AG setup is possible but not recommended, as shown by below quote from the microsoft docs:

We recommend that, if possible, the file path (including the drive letter) of a given secondary database be identical to the path of the corresponding primary database

Source

If you want to do it anyway, there are restrictions and caveats as mentioned here:

If the file path (including the drive letter) of a secondary database differs from the path of the corresponding primary database, the following restrictions apply:

  • New Availability Group Wizard/Add Database to Availability Group Wizard: The Full option is not supported (on the Select Initial Data Synchronization Page page),
  • RESTORE WITH MOVE: To create the secondary databases, the database files must be RESTORED WITH MOVE on each instance of SQL Server that hosts a secondary replica.
  • Impact on add-file operations: A later add-file operation on the primary replica might fail on the secondary databases. This failure could cause the secondary databases to be suspended. This,in turn, causes the secondary replicas to enter the NOT SYNCHRONIZING state.
Related Question