functions/Test-DbaServerName.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
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
Function Test-DbaServerName
{
<#
.SYNOPSIS
Tests to see if it's possible to easily rename the server at the SQL Server instance level, or if it even needs to be changed.
 
.DESCRIPTION
When a SQL Server's host OS is renamed, the SQL Server should be as well. This helps with Availability Groups and Kerberos.
 
This command helps determine if your OS and SQL Server names match, and thus, if a rename is required.
 
It then checks conditions that would prevent a rename like database mirroring and replication.
 
https://www.mssqltips.com/sqlservertip/2525/steps-to-change-the-server-name-for-a-sql-server-machine/
 
.PARAMETER SqlServer
The SQL Server that you're connecting to.
 
.PARAMETER Credential
Credential object used to connect to the SQL Server as a different user.
 
.PARAMETER Detailed
Specifies if the servername is updatable. If updatable -eq $false, it will return the reasons why.
 
.PARAMETER NoWarning
This is an internal parameter used by Repair-DbaServerName which produces warnings of its own.
 
.NOTES
Tags: SPN
dbatools PowerShell module (https://dbatools.io, clemaire@gmail.com)
Copyright (C) 2016 Chrissy LeMaire
 
This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
 
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
 
You should have received a copy of the GNU General Public License along with this program. If not, see <http://www.gnu.org/licenses/>.
 
.LINK
https://dbatools.io/Test-DbaServerName
 
.EXAMPLE
Test-DbaServerName -SqlServer sqlserver2014a
 
Returns ServerInstanceName, SqlServerName, IsEqual and RenameRequired for sqlserver2014a.
 
.EXAMPLE
Test-DbaServerName -SqlServer sqlserver2014a, sql2016
 
Returns ServerInstanceName, SqlServerName, IsEqual and RenameRequired for sqlserver2014a and sql2016.
 
.EXAMPLE
Test-DbaServerName -SqlServer sqlserver2014a, sql2016 -Detailed
 
Returns ServerInstanceName, SqlServerName, IsEqual and RenameRequired for sqlserver2014a and sql2016.
 
If a Rename is required, it will also show Updatable, and Reasons if the servername is not updatable.
 
#>

[CmdletBinding()]
[OutputType([System.Collections.ArrayList])]
Param (
[parameter(Mandatory = $true, ValueFromPipeline = $true)]
[Alias("ServerInstance", "SqlInstance")]
[string[]]$SqlServer,
[PsCredential]$Credential,
[switch]$Detailed,
[switch]$NoWarning
)

BEGIN
{
$collection = New-Object System.Collections.ArrayList
}

PROCESS
{
$servercount++

foreach ($servername in $SqlServer)
{
try
{
$server = Connect-SqlServer -SqlServer $servername -SqlCredential $Credential
}
catch
{
if ($servercount -eq 1 -and $SqlServer.count -eq 1) # This helps with handling servernames being passed via commandline or via pipeline
{
throw $_
}
else
{
Write-Warning "Can't connect to $servername. Moving on."
Continue
}
}

if ($server.isClustered)
{
Write-Warning "$servername is a cluster. Renaming clusters is not supported by Microsoft."
}

if ($server.VersionMajor -eq 8)
{
if ($servercount -eq 1 -and $SqlServer.count -eq 1)
{
throw "SQL Server 2000 not supported."
}
else
{
Write-Warning "SQL Server 2000 not supported. Skipping $servername."
Continue
}
}

$sqlservername = $server.ConnectionContext.ExecuteScalar("select @@servername")
$instance = $server.InstanceName

if ($instance.length -eq 0)
{
$serverinstancename = $server.NetName
$instance = "MSSQLSERVER"
}
else
{
$netname = $server.NetName
$serverinstancename = "$netname\$instance"
}

$serverinfo = [PSCustomObject]@{
ServerInstanceName = $serverinstancename
SqlServerName = $sqlservername
IsEqual = $serverinstancename -eq $sqlservername
RenameRequired = $serverinstancename -ne $sqlservername
}

if ($Detailed)
{
$reasons = @()
$servicename = "SQL Server Reporting Services ($instance)"
$netbiosname = $server.ComputerNamePhysicalNetBIOS
Write-Verbose "Checking for $servicename on $netbiosname"
$rs = $null

try
{
 $rs = Get-Service -ComputerName $netbiosname -DisplayName $servicename -ErrorAction SilentlyContinue
}
catch
{
if ($NoWarnings -eq $false)
{
Write-Warning "Can't contact $netbiosname using Get-Service. This means the script will not be able to automatically restart SQL services."
}
}

if ($rs.length -gt 0)
{
if ($rs.Status -eq 'Running')
{
$rstext = "Reporting Services must be stopped and updated."
}
else
{
$rstext = "Reporting Services exists. When it is started again, it must be updated."
}
$serverinfo | Add-Member -NotePropertyName Warnings -NotePropertyValue $rstext
}

# check for mirroring
$mirroreddb = $server.Databases | Where-Object { $_.IsMirroringEnabled -eq $true }

Write-Debug "Found the following mirrored dbs: $($mirroreddb.name)"

if ($mirroreddb.length -gt 0)
{
$dbs = $mirroreddb.name -join ", "
$reasons += "Databases are being mirrored: $dbs"
}

# check for replication
$sql = "select name from sys.databases where is_published = 1 or is_subscribed =1 or is_distributor = 1"
Write-Debug $sql
$replicatedb = $server.ConnectionContext.ExecuteWithResults($sql).Tables

if ($replicatedb.name.length -gt 0)
{
$dbs = $replicatedb.name -join ", "
$reasons += "Databases are involved in replication: $dbs"
}

# check for even more replication
$sql = "select srl.remote_name as RemoteLoginName from sys.remote_logins srl join sys.sysservers sss on srl.server_id = sss.srvid"
Write-Debug $sql
$results = $server.ConnectionContext.ExecuteWithResults($sql).Tables

if ($results.RemoteLoginName.length -gt 0)
{
$remotelogins = $results.RemoteLoginName -join ", "
$reasons += "Remote logins still exist: $remotelogins"
}

if ($reasons.length -gt 0)
{
$serverinfo | Add-Member -NotePropertyName Updatable -NotePropertyValue $false
$serverinfo | Add-Member -NotePropertyName Blockers -NotePropertyValue $reasons
}
else
{
$serverinfo | Add-Member -NotePropertyName Updatable -NotePropertyValue $true
}
}

$null = $collection.Add($serverinfo)
}
}

END
{
return $collection
}
}