February 26, 2018 at 2:56 pm
I have a process that needs to occasionally copy a file to a local drive on the active node of an AlwaysOn environment (SQL Server 2014 running on Windows Server 2012 R2 servers). This environment has a primary node and 2 secondary nodes. I tried using the listener name, but obviously that doesn't work.
This is an old process that is being overhauled now, but for the short term, I really need to find a way to do this. Ideally, I'd like to do this in a DOS batch file, and if not possible, then the next alternative would be via a PowerShell script.
I've done a lot of searches on this topic, but have hit a brick wall. Any suggestions would be greatly appreciated.
February 26, 2018 at 4:05 pm
If you are looking for code to identify the primary - this would work:
Select ags.primary_replica
From sys.dm_hadr_availability_replica_states ars
Inner Join sys.dm_hadr_availability_group_states ags On ags.group_id = ars.group_id
Where ars.role_desc = 'PRIMARY'
Once you have this - then it should be simple to create the file path that can be used with copy-item or robocopy to copy the file to the destination.
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 27, 2018 at 3:07 pm
Sweet! Thank you Jeffrey. That's exactly what I needed. I did have to add the UNIQUE keyword to the select, since it was returning 5 rows with the same value. This also spurred me to investigate all those dm_hadr tables. Lots of good information there for future use.
Now its on to building a PowerShell script to call that SQL statement, get the result and build a path for the file copy.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply