-
Notifications
You must be signed in to change notification settings - Fork 9
/
ActiveDR Failover Test.ps1
144 lines (81 loc) · 5.05 KB
/
ActiveDR Failover Test.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
##############################################################################################################################
# ActiveDR - Non-Disruptive DR Test for SQL Server
#
# Scenario:
# Test failover only in DR. Does not impact Production.
#
# Single test database "ExampleDb" on two RDM volumes: data & log, on each SQL Server.
#
# Prerequisites:
# 1. DR Pod needs to be pre-created
# 2. Databases in DR Pod need to be "initialized" by being presented and attached to the DR SQL Server, then set offline
# 3. After Step 2 initialization, be sure to retrieve applicable DR disk serial numbers & substitute in code
# 4. On DR server, SQL Server service off. Service auto-start should be set to Manual as well.
#
# Usage Notes:
# This script is meant to be run in chunks. Note the Part X headers. DO NOT run everything at once!
#
# Disclaimer:
# This example script is provided AS-IS and meant to be a building block to be adapted to fit an individual
# organization's infrastructure.
##############################################################################################################################
#########################################
# PART 1: PROMOTE DR FAILOVER POD
#########################################
# Import PowerShell modules
Import-Module PureStoragePowerShellSDK2
Import-Module SqlServer
# Set Variables
$ArrayName = "flasharray1.example.com" # DR FlashArray
$PodName = "ActiveDrPod" # Pod name on the DR FlashArray
$DRSQLServer = "SqlServer1" # DR SQL Server
$DatabaseName = "ExampleDb" # Name of database
# Connect to DR SQL Server
$DRSQLServerSession = New-PSSession -ComputerName $DRSQLServer
# Connect to DR FlashArray
$Credential = Get-Credential
$FlashArray = Connect-Pfa2Array -Endpoint $ArrayName -Credential $Credential -IgnoreCertificateError
# Promote DR pod
Update-Pfa2Pod -Array $FlashArray -Name $PodName -RequestedPromotionState "promoted"
# Check status of pod - do not proceed until state is promoted - PromotionStatus : promoted
Get-Pfa2Pod -Array $FlashArray -Name $PodName
# Disks will be presented back to Windows but serial number may not be materialized.
# Because disk serial number can change between reboots, need to programmatically reference
# serial number to properly identify which disks to manipulate.
# Use Get-Disk to determine disk serial numbers
Invoke-Command -Session $DRSQLServerSession -ScriptBlock { Get-Disk | Format-Table }
# Set disk serial numbers to variables
$Disk1 = "6000c02022cb876dcd321example01b" # Serial Number of data disk
$Disk2 = "6000c02022cb876dcd321example02b" # Serial Number of log disk
# Online the windows disks
Invoke-Command -Session $DRSQLServerSession -ScriptBlock { Get-Disk | Where-Object { $_.SerialNumber -eq $using:Disk1 } | Set-Disk -IsOffline $False }
Invoke-Command -Session $DRSQLServerSession -ScriptBlock { Get-Disk | Where-Object { $_.SerialNumber -eq $using:Disk2 } | Set-Disk -IsOffline $False }
# Setting volumes to Read/Write
Invoke-Command -Session $DRSQLServerSession -ScriptBlock { Get-Disk | Where-Object { $_.SerialNumber -eq $using:Disk1 } | Set-Disk -IsReadOnly $False }
Invoke-Command -Session $DRSQLServerSession -ScriptBlock { Get-Disk | Where-Object { $_.SerialNumber -eq $using:Disk2 } | Set-Disk -IsReadOnly $False }
# Confirm disks are online
Invoke-Command -Session $DRSQLServerSession -ScriptBlock { Get-Disk | Format-Table }
# Online the database
$Query = "ALTER DATABASE [$DatabaseName] SET ONLINE WITH ROLLBACK IMMEDIATE"
Invoke-Sqlcmd -ServerInstance $DRSQLServer -Database master -Query $Query
# Confirm database online
$Query = "SELECT [name], [state_desc] FROM sys.databases WHERE [name] = '$DatabaseName'"
Invoke-Sqlcmd -ServerInstance $DRSQLServer -Database master -Query $Query
#########################################
# PART 2: DEMOTE DR TEST
#########################################
# Offline the database
$Query = "ALTER DATABASE [$DatabaseName] SET OFFLINE WITH ROLLBACK IMMEDIATE"
Invoke-Sqlcmd -ServerInstance $DRSQLServer -Database master -Query $Query
# Confirm database offline
$Query = "SELECT [name], [state_desc] FROM sys.databases WHERE [name] = '$DatabaseName'"
Invoke-Sqlcmd -ServerInstance $DRSQLServer -Database master -Query $Query
# Offline the volume
Invoke-Command -Session $DRSQLServerSession -ScriptBlock { Get-Disk | Where-Object { $_.SerialNumber -eq $using:Disk1 } | Set-Disk -IsOffline $True }
Invoke-Command -Session $DRSQLServerSession -ScriptBlock { Get-Disk | Where-Object { $_.SerialNumber -eq $using:Disk2 } | Set-Disk -IsOffline $True }
# Confirm disks are offline
Invoke-Command -Session $DRSQLServerSession -ScriptBlock { Get-Disk | Format-Table }
# Demote DR Pod
Update-Pfa2Pod -Array $FlashArray -Name $PodName -RequestedPromotionState "demoted"
# Confirm DR Pod status is demoted - PromotionStatus : demoted
Get-Pfa2Pod -Array $FlashArray -Name $PodName