AG Fundamentals

/* the whole anatomy in four result sets */
SELECT name, is_distributed, automated_backup_preference_desc
FROM sys.availability_groups;                       -- the AG

SELECT ag.name AS ag, ar.replica_server_name, ar.availability_mode_desc,
       ar.failover_mode_desc, ar.seeding_mode_desc, ar.endpoint_url
FROM sys.availability_replicas ar
JOIN sys.availability_groups ag ON ar.group_id = ag.group_id;  -- replicas

SELECT name, state_desc, port = (SELECT port FROM sys.tcp_endpoints t
       WHERE t.endpoint_id = e.endpoint_id)
FROM sys.endpoints e WHERE type_desc = 'DATABASE_MIRRORING';   -- endpoint

SELECT l.dns_name, l.port, ip.ip_address, ip.state_desc
FROM sys.availability_group_listeners l
JOIN sys.availability_group_listener_ip_addresses ip
     ON l.listener_id = ip.listener_id;                        -- listener

SELECT ar.replica_server_name, DB_NAME(drs.database_id) AS db,
       drs.log_send_queue_size AS send_kb,      -- RPO exposure
       drs.log_send_rate      AS send_kb_s,
       drs.redo_queue_size    AS redo_kb,       -- RTO exposure
       drs.redo_rate          AS redo_kb_s,
       CASE WHEN drs.redo_rate > 0
            THEN drs.redo_queue_size / drs.redo_rate END AS redo_catchup_sec
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
WHERE drs.is_local = 0 OR drs.is_primary_replica = 1;

/* check current modes */
SELECT ag.name, ar.replica_server_name,
       ar.availability_mode_desc, ar.failover_mode_desc
FROM sys.availability_replicas ar
JOIN sys.availability_groups ag ON ar.group_id = ag.group_id;

/* switch DR replica to sync (before a planned no-loss failover) */
ALTER AVAILABILITY GROUP [YourAG]
  MODIFY REPLICA ON 'DRSERVER'
  WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
-- wait until sync state = SYNCHRONIZED, then fail over.

/* set it back to async after (protect app latency) */
ALTER AVAILABILITY GROUP [YourAG]
  MODIFY REPLICA ON 'DRSERVER'
  WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);

-- SQL's view: listener, port, IPs and which is online
SELECT l.dns_name, l.port, ip.ip_address, ip.state_desc,
       ip.network_subnet_ip, ip.network_subnet_prefix_length
FROM sys.availability_group_listeners l
JOIN sys.availability_group_listener_ip_addresses ip
     ON l.listener_id = ip.listener_id;

-- read-only routing (if you use readable secondaries)
SELECT ar.replica_server_name AS when_primary_is,
       ar2.replica_server_name AS route_reads_to,
       rl.routing_priority, ar2.read_only_routing_url
FROM sys.availability_read_only_routing_lists rl
JOIN sys.availability_replicas ar  ON rl.replica_id = ar.replica_id
JOIN sys.availability_replicas ar2 ON rl.read_only_replica_id = ar2.replica_id
ORDER BY when_primary_is, rl.routing_priority;

# PowerShell on a cluster node — the cluster's view
Get-ClusterResource |
  Where-Object ResourceType -in 'Network Name','IP Address' |
  Format-Table Name, State, OwnerGroup, OwnerNode

# the two DNS-behavior settings, per listener Network Name
Get-ClusterResource "YourAG_Listener" |
  Get-ClusterParameter RegisterAllProvidersIP, HostRecordTTL

# force DNS re-registration after fixing either setting
Get-ClusterResource "YourAG_Listener" | Update-ClusterNetworkNameResource

# what clients actually receive — run FROM AN APP SERVER
nslookup your-listener-name
Test-NetConnection your-listener-name -Port 1433

/* on each replica — once per other replica's service account */
CREATE LOGIN [DOMAIN\svc-sql-dr] FROM WINDOWS;
GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [DOMAIN\svc-sql-dr];

/* if the endpoint doesn't exist yet */
CREATE ENDPOINT Hadr_endpoint
  STATE = STARTED
  AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
  FOR DATABASE_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES);

/* VERIFY — who can connect to the endpoint on this replica */
SELECT e.name AS endpoint, sp.name AS grantee,
       p.permission_name, p.state_desc
FROM sys.server_permissions p
JOIN sys.endpoints e ON p.major_id = e.endpoint_id AND p.class = 105
JOIN sys.server_principals sp
     ON p.grantee_principal_id = sp.principal_id;

/* on the NEW SECONDARY (the replica being seeded) */
ALTER AVAILABILITY GROUP [YourAG] GRANT CREATE ANY DATABASE;

/* verify + watch it work */
SELECT ag.name, ds.database_name, ds.current_state,
       ds.failure_state_desc, ds.start_time
FROM sys.dm_hadr_automatic_seeding ds
JOIN sys.availability_groups ag ON ds.ag_id = ag.group_id
ORDER BY ds.start_time DESC;

/* required for AG operations — verify SYSTEM has these */
GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM];
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM];
GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM];

SELECT sp.name, p.permission_name, p.state_desc
FROM sys.server_permissions p
JOIN sys.server_principals sp ON p.grantee_principal_id = sp.principal_id
WHERE sp.name = N'NT AUTHORITY\SYSTEM';

/* 1 · who IS the service account on this box? */
SELECT servicename, service_account, status_desc
FROM sys.dm_server_services;

/* 2 · where do new files want to go? */
SELECT SERVERPROPERTY('InstanceDefaultDataPath') AS default_data,
       SERVERPROPERTY('InstanceDefaultLogPath')  AS default_log;

/* 3 · probe a path AS THE SERVICE ACCOUNT
   (these run under the engine's identity — the honest test) */
EXEC master.sys.xp_dirtree  'D:\SQLData', 1, 1;
EXEC master.sys.xp_dirtree  '\\dr-share\backups', 1, 1;
EXEC master.sys.xp_fileexist '\\dr-share\backups\seed.bak';
-- empty result / 0s = the SERVICE ACCOUNT can't see it,
-- even if it opens fine in YOUR Explorer window

# PowerShell side — confirm and fix NTFS/share
Get-CimInstance Win32_Service -Filter "Name LIKE 'MSSQL%'" |
  Select-Object Name, StartName, State

icacls D:\SQLData              # who has what on the data dir
icacls D:\SQLData /grant "DOMAIN\svc-sql-dr:(OI)(CI)F"   # grant if missing

# share-level ACL matters too, not just NTFS:
Get-SmbShareAccess -Name backups   # on the file server

/* 1 · document logical names + physical paths — run on EVERY node.
   Paths live per-instance in master; never assume they match. */
SELECT @@SERVERNAME AS node, DB_NAME(database_id) AS db,
       name AS logical_name, type_desc, physical_name,
       size*8/1024 AS size_mb
FROM sys.master_files
WHERE database_id = DB_ID(N'<db>');

# 2 · target path exists on ALL nodes + NTFS for the service account
$nodes = 'SQL-1-01','SQL-1-02','SQL-1-03','SQL-1-04'
$paths = 'E:\SQLData','E:\SQLLog'
foreach ($n in $nodes) {
  Invoke-Command -ComputerName $n -ScriptBlock {
    param($ps)
    foreach ($p in $ps) {
      if (-not (Test-Path $p)) { New-Item -ItemType Directory -Path $p | Out-Null }
      "== $env:COMPUTERNAME $p =="; icacls $p
    }
  } -ArgumentList (,$paths)
}
# grant where missing:
# icacls E:\SQLData /grant "DOMAIN\svc-sql:(OI)(CI)F"

/* 3 · verify AS THE ENGINE, on each node (§6 — the honest test) */
EXEC master.sys.xp_dirtree N'E:\SQLData', 1, 1;
EXEC master.sys.xp_dirtree N'E:\SQLLog', 1, 1;

/* 4 · note failover modes, then set MANUAL for the migration window
   (no surprise auto-failover onto a node mid-move) */
SELECT ar.replica_server_name, ar.availability_mode_desc,
       ar.failover_mode_desc
FROM sys.availability_replicas ar;
ALTER AVAILABILITY GROUP [<ag>]
  MODIFY REPLICA ON 'SQL-1-01' WITH (FAILOVER_MODE = MANUAL);
-- repeat for every replica currently AUTOMATIC

/* on the SECONDARY being migrated */
ALTER DATABASE [<db>] SET HADR SUSPEND;

ALTER DATABASE [<db>] MODIFY FILE
  (NAME = N'<logical_data>', FILENAME = N'E:\SQLData\<db>.mdf');
ALTER DATABASE [<db>] MODIFY FILE
  (NAME = N'<logical_log>',  FILENAME = N'E:\SQLLog\<db>_log.ldf');
-- repeat per file from your pre-flight inventory

# still on that node - stop SQL (it's a secondary; apps don't notice)
Stop-Service MSSQLSERVER
robocopy D:\SQLData E:\SQLData <db>.mdf     /copyall /j
robocopy D:\SQLLog  E:\SQLLog  <db>_log.ldf /copyall /j
Start-Service MSSQLSERVER

/* verify the paths took, resume, wait for green */
SELECT name, physical_name FROM sys.master_files
WHERE database_id = DB_ID(N'<db>');
ALTER DATABASE [<db>] SET HADR RESUME;
-- wait for SYNCHRONIZED (AG health query) before the next node

-- after ALL nodes verified on E:\ and SYNCHRONIZED:
--   delete the old D:\ copies (they are stale, not backups)
-- restore failover modes:
ALTER AVAILABILITY GROUP [<ag>]
  MODIFY REPLICA ON 'SQL-1-01' WITH (FAILOVER_MODE = AUTOMATIC);
-- re-run: AG health query, backup job check, post-failover smoke test

/* file operations replay on EVERY replica - the E:\ path must
   already exist on ALL nodes or that secondary SUSPENDS (why
   pre-flight #2 and #3 matter for this method too) */
ALTER DATABASE [<db>] ADD FILE
  (NAME = N'<db>_data2', FILENAME = N'E:\SQLData\<db>_2.ndf',
   SIZE = <current_used_mb> MB) TO FILEGROUP [PRIMARY];

DBCC SHRINKFILE (N'<old_secondary_file>', EMPTYFILE);  -- IO-heavy, online
ALTER DATABASE [<db>] REMOVE FILE [<old_secondary_file>];