Public/New-AGMLibMSSQLMigrate.ps1
|
# Copyright 2022 Google Inc. All Rights Reserved. # # Licensed under the Apache License, Version 2.0 (the "License"); # you may not use this file except in compliance with the License. # You may obtain a copy of the License at # # http://www.apache.org/licenses/LICENSE-2.0 # # Unless required by applicable law or agreed to in writing, software # distributed under the License is distributed on an "AS IS" BASIS, # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. # See the License for the specific language governing permissions and # limitations under the License. Function New-AGMLibMSSQLMigrate ([string]$imagename,[string]$imageid,[int]$copythreadcount,[int]$frequency,[switch]$jsonprint,[switch]$dontrenamedatabasefiles,[switch]$volumes,[switch]$files,[string]$restorelist,[switch][alias("g")]$guided,[switch]$usesourcelocation) { <# .SYNOPSIS Creates a migration for a mounted MS SQL Image .EXAMPLE New-AGMLibMSSQLMigrate You will be prompted for ImageID .EXAMPLE New-AGMLibMSSQLMigrate -imageid 56072427 Starts a migrate with default copy thread of 4 and default frequency set to 24 hours for Image ID 56072427 Files will be renamed to match the new database name and files will be copied to the same drive/path as they were on the source server .EXAMPLE New-AGMLibMSSQLMigrate -imagename Image_10557067 Starts a migrate with default copy thread of 4 and default frequency set to 24 hours for ImageName Image_10557067 Files will be renamed to match the new database name and files will be copied to the same drive/path as they were on the source server .EXAMPLE New-AGMLibMSSQLMigrate -imageid 56072427 -copythreadcount 2 -frequency 2 Starts a migrate with copy thread count set to 2 and frequency set to 2 hours for Image ID 56072427 Files will be renamed to match the new database name and files will be copied to the same drive/path as they were on the source server .EXAMPLE New-AGMLibMSSQLMigrate -imagename Image_10557067 -copythreadcount 7 Starts a migrate with copy thread count set to 7 and frequency left for the default of 24 hours for ImageName Image_10557067 Files will be renamed to match the new database name and files will be copied to the same drive/path as they were on the source server .EXAMPLE New-AGMLibMSSQLMigrate -imageid 6859821 -files -restorelist "SQL_smalldb.mdf,D:\Data,d:\avtest1;SQL_smalldb_log.ldf,E:\Logs,e:\avtest1" Starts a migrate with default copy thread of 4 and default frequency set to 24 hours for ImageID 6859821 Files will be renamed to match the new database name. Because "-files" was specified, the -restorelist must contain the file name, the source location and the targetlocation. Each file is separated by a semicolon, the three fields for each file are comma separated. In this example, the file SQL_smalldb.mdf found in D:\Data will be migrated to d:\avtest1 In this example, the file SQL_smalldb_log found in E:\Logs will be migrated to e:\avtest1 The order of the fields must be "filename,sourcefolder,targetfolder" so for two files "filename1,source1,target1;filename2,source2,target2" .EXAMPLE New-AGMLibMSSQLMigrate -imageid 6860452 -copythreadcount 3 -frequency 2 -volumes -restorelist "D:\,K:\;E:\,M:\" Starts a migrate with copy thread of 3 and frequency set to 24hours for ImageID 6860452 Files will be renamed to match the new database name. Because "-volumes" was specified, the -restorelist must contain the source drive letter and the target drive letter. Each drive is separated by a semicolon, the two fields for each drive are comma separated. In this example the D:\ files will be migrated to the K:\ In this example the E:\ files will be migrated to the M:\ The order of the fields must be "sourcedrive,targetdrive" so for two drives "sourcedrive1,targetdrive1;sourcedrive2,targetdrive2" .DESCRIPTION A function to create migration for an MS SQL Image The following defaults all apply: If frequency is not set with "-frequency XX" it will default to 24 hours If copythreadcount is not set with "-copythreadcount YY" it will default to 4 If dontrenamedatabasefiles is not set with "-dontrenamedatabasefiles" then files will be renamed to match the new database name If a restore list is not specified, then the default will be to copy files to the same drive/path as they were on the source server #> # its pointless proceeding without a connection. if ( (!($AGMSESSIONID)) -or (!($AGMIP)) ) { Get-AGMErrorMessage -messagetoprint "Not logged in or session expired. Please login using Connect-AGM" return } $sessiontest = Get-AGMVersion if ($sessiontest.errormessage) { $sessiontest return } if (($imagename) -and (!($imageid))) { $imageid = (Get-AGMImage -filtervalue backupname=$imagename).id } if ( (!($imagename)) -and (!($imageid)) ) { $guided = $TRUE $backup = Get-AGMImage -filtervalue "characteristic=MOUNT&apptype=SqlInstance&apptype=SqlServerWriter" -sort "hostname:asc,appname:asc" if ($backup.id) { $AGMArray = @() $i = 1 Foreach ($id in $backup) { if ( $id.flags_text -notcontains "JOBFLAGS_MIGRATING") { $id | Add-Member -NotePropertyName appliancename -NotePropertyValue $id.cluster.name $id | Add-Member -NotePropertyName hostname -NotePropertyValue $id.host.hostname $id | Add-Member -NotePropertyName appid -NotePropertyValue $id.application.id $id | Add-Member -NotePropertyName mountedhostname -NotePropertyValue $id.mountedhost.hostname $id | Add-Member -NotePropertyName childappname -NotePropertyValue $id.childapp.appname $AGMArray += [pscustomobject]@{ select = $i imageid = $id.id imagename = $id.backupname apptype = $id.apptype appliancename = $id.appliancename hostname = $id.hostname appname = $id.appname mountedhost = $id.mountedhostname childappname = $id.childappname label = $id.label files = $id.restorableobjects.fileinfo volumes = $id.restorableobjects.volumeinfo.logicalname } $i++ } } if ($AGMArray.imageid.count -eq 0) { Get-AGMErrorMessage -messagetoprint "All mounted SQL Apps are already migrating" return } Clear-Host Write-Host "Image list. Choose your image." $AGMArray | select-object select,apptype,hostname,appname,mountedhost,childappname,imageid,imagename | Format-Table * While ($true) { Write-host "" $listmax = $AGMArray.Length [int]$imageselection = Read-Host "Please select an image (1-$listmax)" if ($imageselection -lt 1 -or $imageselection -gt $listmax) { Write-Host -Object "Invalid selection. Please enter a number in range [1-$listmax]" } else { break } } $imageid = $AGMArray[($imageselection - 1)].imageid $srcid = $AGMArray[($imageselection - 1)].application.srcid $imagegrab = Get-AGMImage -id $imageid $vollist = $imagegrab.restorableobjects.volumeinfo.logicalname | sort-object -unique $filelist = $imagegrab.restorableobjects.fileinfo | sort-object filepath,filename if ((!($frequency)) -and (!($copythreadcount))) { if (!($frequency)) { [int]$frequency = Read-Host "Frequency (hit enter for default of 24 hours)" } if (!($frequency)) { [int]$frequency = 24 } if (!($copythreadcount)) { [int]$copythreadcount = Read-Host "Copy thread count (hit enter for default of 4 threads)" } if (!($copythreadcount)) { [int]$copythreadcount = 4 } } Clear-Host Write-Host "Rename files to match new database name" Write-Host "1`: Rename files to match new database name (default)" Write-Host "2`: Don't rename files" [int]$userselection = Read-Host "Please select from this list (1-2)" if ($userselection -eq "") { $userselection = 1 } if ($userselection -eq 1) { $dontrenamedatabasefiles = $FALSE } if ($userselection -eq 2) { $dontrenamedatabasefiles = $TRUE } Write-Host "" Write-Host "Select file destination for migrated files" Write-Host "1`: Copy files to the same drive/path as they were on the source (default)" Write-Host "2`: Choose new file locations at the volume level" Write-Host "3`: Choose new locations at the file level" Write-Host "" [int]$userselection = Read-Host "Please select from this list (1-3)" if ($userselection -eq "") { $userselection = 1 } if ($userselection -eq 1) { $usesourcelocation = $TRUE } if ($userselection -eq 2) { Write-host "`n For each volume please specify a new volume" $restorelist = "" write-host "" foreach ($vol in $vollist) { $targetlocation = "" $targetlocation = read-host "Source: $($vol) Target" if ($targetlocation -eq "") { $targetlocation = $vol } $restorelist = $restorelist + ";" + $vol + "," + $targetlocation } $restorelist = $restorelist.Substring(1) $volumes = $TRUE } if ($userselection -eq 3) { $restorelist = "" Write-host "`n For each file please specify a new location:" write-host "" foreach ($file in $filelist) { $targetlocation = "" $targetlocation = read-host "File: $($file.filename) Source: $($file.filepath) Target Path" if ($targetlocation -eq "") { $targetlocation = $file.filepath } $restorelist = $restorelist + ";" + $file.filename + "," + $file.filepath + "," + $targetlocation } $restorelist = $restorelist.Substring(1) $files = $TRUE } } else { Get-AGMErrorMessage -messagetoprint "There are no mounted SQL App Type apps to list" return } } if (!($frequency)) { [int]$frequency = 24 } if (!($copythreadcount)) { [int]$copythreadcount = 4 } if (!($restorelist)) { $usesourcelocation = $TRUE } if (!($srcid)) { if (!($imagegrab.id)) { $imagegrab = get-agmimage -id $imageid if (!($imagegrab)) { Get-AGMErrorMessage -messagetoprint "Could not find image $imageid" return } } $srcid = $imagegrab.application.srcid } if ($guided) { Write-Host "Guided selection is complete. The values entered would result in the following command:" Write-Host "" Write-Host -nonewline "New-AGMLibMSSQLMigrate -imageid $imageid -copythreadcount $copythreadcount -frequency $frequency" if ($volumes) { Write-Host -nonewline " -volumes " } if ($files) { Write-Host -nonewline " -files " } if ($restorelist) { Write-Host -nonewline " -restorelist `"$restorelist`"" } Write-Host "" Write-Host "1`: Run the command now (default)" Write-Host "2`: Show the JSON used to run this command, but don't run it" Write-Host "3`: Exit without running the command" $userchoice = Read-Host "Please select from this list (1-3)" if ($userchoice -eq 2) { $jsonprint = $TRUE } if ($userchoice -eq 3) { return } } $body = [ordered]@{} $mapping = @() $provisioningoptions = @() $restorelocation = [ordered]@{} if ($dontrenamedatabasefiles) { $provisioningoptions += @( [ordered]@{ name = 'renamedatabasefiles'; value = "false" } ) } else { $provisioningoptions += @( [ordered]@{ name = 'renamedatabasefiles'; value = "true" } ) } $provisioningoptions += @( [ordered]@{ name = 'copythreadcount'; value = $copythreadcount } ) $body += @{ provisioningoptions = $provisioningoptions } if ($usesourcelocation) { $body += @{ restorelocation = @{ type = "usesourcelocation" } } } if ($volumes) { foreach ($volume in $restorelist.split(";")) { $mapping += @( [ordered]@{ name = $volume.split(",")[0] ; source = $volume.split(",")[0] ; target = $volume.split(",")[1] } ) } $restorelocation += @{type = "volumes"} $restorelocation += @{mapping = $mapping} $body += @{ restorelocation = $restorelocation } } if ($files) { foreach ($file in $restorelist.split(";")) { $mapping += @( [ordered]@{ name = $file.split(",")[0] ; source = $file.split(",")[1] ; target = $file.split(",")[2] } ) } $restorelocation += @{ type = "files" } $restorelocation += @{ mapping = $mapping } $body += @{ restorelocation = $restorelocation } } $application = @{ srcid = $srcid } $body += @{ application = $application } $body += @{ frequency = [int]$frequency } $json = $body | ConvertTo-Json -depth 4 if ($jsonprint) { $compressedjson = $body | ConvertTo-Json -compress -depth 4 Write-host "This is the final command:" Write-host "" Write-host "Post-AGMAPIData -endpoint /backup/$imageid/configmountmigrate -body `'$compressedjson`'" return } Post-AGMAPIData -endpoint /backup/$imageid/configmountmigrate -body $json } |