Hi! I have not worked with AG groups very much, and I have a disk containing all my .mdf files on my secondary replica that is 90% full, and not expandable. I need to either move them or create .ndf files. What would you do and why? What would have the least amount of impact? What other questions do I need to be asking? If I move the .mdf files, the drive letters need to be the same between primary and secondary - is that correct? Talk me through it.
February 12, 2024 at 3:27 pm
Definitely build a test environment to check/test your solution first.
Please read this web site information:
https://www.sqlshack.com/move-sql-database-files-mdf-ldf-another-location/
DBASupport
Just curious - why would you have a secondary replica that is not configured the same as the primary replica? If you failover to that secondary and it isn't the same then you are almost certainly going to encounter issues.
And if the drives on the primary are extended past the point where the drives on the secondary can be extended - once your data and/or log file exceeds that size on the primary then you end up impacting the primary because the secondary is waiting to extend the files.
I would have a new volume created and presented that matched the size of the volume presented to the primary. I would then shut down SQL Server on the secondary and copy/move the files from the original drive to the new drive. Relabel the old drive to an unused drive letter - relabel the new to the old drive letter.
Note: to copy/move use something like Robocopy with the parameter(s) to copy permissions. If not - then you have to manually set the permissions on every folder which can be a pain.
Another option: have your SAN team do the work on the SAN itself. At a set point in time - shut down SQL Server - have the storage team finish replication and swap in the new volume for the old volume. Set permissions and restart SQL Server...
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
February 12, 2024 at 6:56 pm
I inherited a turd environment. Many, many years of neglect. All to say that I have no idea why things are set up the way they are. I'm just trying to keep the lights on. I appreciate the help more than you know. I don't have anyone to ask or mentor me.
February 13, 2024 at 6:33 pm
I inherited a turd environment. Many, many years of neglect. All to say that I have no idea why things are set up the way they are. I'm just trying to keep the lights on. I appreciate the help more than you know. I don't have anyone to ask or mentor me.
I am assuming the environment is an FCI cluster for HA - with AG setup for DR. Which is actually not a bad configuration because it eliminates a lot of issues you find with synchronous replicas. For example, with a synchronous replica any activity on the primary has to be hardened on that replica before it can be committed on the primary - and any large processing will cause a backup of the send and redo queues which forces other processes to wait.
In other words - in a fully AG cluster with synchronous replica's - rebuilding indexes becomes almost impossible without significant impact to the workflow, regardless of ONLINE vs OFFLINE mode. Batch processing on the primary has an increasingly negative impact on performance - which often leads to cursor based operations to perform row by row processing to avoid that impact.
So I wouldn't necessarily say it was a turd environment.
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply