1 Reply Latest reply: Apr 16, 2015 1:31 PM by Jeffrey Adams RSS

    Powershell ISE Refresh Successful. Refresh via Powershell or Scheduled Task Failure

    Jeffrey Adams Wayfarer
    Visibility: Open to anyone

      Hi All.

       

      I am working on refreshing a test machine and I am running into problems.

      I am doing this in my script:

       

      1) Stopping SQL

      2) Connecting to Nimble

      3) Taking old test drives offline and deleting them

      4) Cloning the production snapshot and mounting them on test

      5) Restarting sql

      6) Running a few cleanup sql scripts

      7) Sending an email refresh is complete.

       

      I have used parts of 2 scripts referenced on this forum,  GroupMgmt.dll and the Nimble module.

      They have worked great for getting a refresh going through the ISE.  It works like a champ.

      There may be a cleaner way, but this is what I have been able to figure out so far.

       

      When I run it from regular powershell or a scheduled task it fails.

      From what I can tell the ISE allows the databases to come online without a problem.

      The databases stay '(in recovery)' thru Powershell and a scheduled task.

      This causes all of the cleanup scripts to fail...since the databases are stuck in recovery.

      Occasionally the scheduled task has difficulty stopping the sql server service too.

       

      Below is the script I am using.  I have replaced sensitive information/comments, etc.

       

      Any help would be appreciated.

       

      Thanks!

      Jeff

       

      ### CONFIG VARIABLES ###
      $NimblePath = "C:\Windows\System32\WindowsPowerShell\v1.0\Modules\Nimble"

      #Import Modules/Cmdlets
      #https://gallery.technet.microsoft.com/scriptcenter/Enhanced-Script-Logging-27615f85
      Import-Module PowerShellLogging
      Import-Module Nimble
      Import-Module 'sqlps' –DisableNameChecking
      . ($NimblePath + "\iSCSI_Cmdlets.ps1")

       

      $ArrayIP = "x.x.x.x"
      $ArrayUsername = "user"
      $ArrayPassword = "pw"

       

      # Build Nimble GroupMgmt Object and Array Management URL
      [Reflection.Assembly]::LoadFile($NimblePath + "\GroupMgmt.dll") | Out-Null
      $gm = New-Object GroupMgmt
      $gm.Url = "http://" + $ArrayIP + ":4210/soap"

      $InitiatorGroup = "server"
      $SQLInstance = "instance"
      $FullSQL = $InitiatorGroup + '\' + $SQLInstance

       

      $ProdData = "ProdSnapshotData"
      $ProdLog = "ProdSnapshotLog"
      $ProdIndex = "ProdSnapshotIndex"

       

      $SQLData = "RefreshData"
      $SQLLog = "RefreshLogs"
      $SQLIndex = "RefreshIndex"

       

      $SMTP = "email"
      $To = "to"
      $From = "from"

      $LogFile = "C:\nimble\Logs\$(get-date -f yyyy-MM-dd).log"
      $Log = Enable-LogFile -Path $LogFile


      Write-Host "Start Refresh"

      #Shutdown SQL Services
      $services = Get-Service | ? {$_.DisplayName -imatch "SQL Server.*$SQLInstance"}
      foreach($service in $services) {
      Write-Host "Stopping SQL service " $service.Name
      Stop-Service -Force -Name $service.Name
      }

      # Login
      $sid = [ref]""
      $return = $gm.login($ArrayUsername, $ArrayPassword, $sid)
      if ($return -eq "SMok") {
        Write-Host "Logged into array @ " $gm.Url
      }
      else {
        Write-Host "Couldn't login to " $gm.Url
      }

      # get vols
      $volList = New-Object Vol
      $return = $gm.getVolList($sid.Value, [ref]$volList)

      foreach ($vol in $volList) {
         if (($vol.name -eq $SQLData) -or ($vol.name -eq $SQLLog) -or ($vol.name -eq $SQLIndex)){
            $return = $gm.onlineVol($sid.Value, $vol.name, $false)
            if ($return -eq "SMok") {
             Write-Host "Set old volume " $vol.name " offline"
            }
            else {
             Write-Host "Could not set old volume " $vol.name " offline"
             #Exit
            }
       
            $return = $gm.deleteVol($sid.Value, $vol.name)
            if ($return -eq "SMok") {
             Write-Host "Deleted old volume " $vol.name
            }
            else {
             Write-Host "Could not delete old volume " $vol.name
             #Exit
            }
          }
      }

      #Start-Sleep -s 10

      # BEGIN handle getting cloning via Nimble plugin

      Connect-NSArray -SystemName $ArrayIP -UserName $ArrayUsername -Password $ArrayPassword

       

      Get-NSSnapShot $ProdData |sort-Object -Property Created -Descending |sort-Object -Property schedulename | Select -First 1 | New-NSClone -Name $SQLData
      Get-NSSnapShot $ProdIndex |sort-Object -Property Created -Descending |sort-Object -Property schedulename | Select -First 1 | New-NSClone -Name $SQLIndex
      Get-NSSnapShot $ProdLog |sort-Object -Property Created -Descending |sort-Object -Property schedulename | Select -First 1 | New-NSClone -Name $SQLLog
      $error.Clear()   #above lines produced a few divide by zero errors but appears to be working fine?

       

      Add-NSInitiatorGroupToVolume -InitiatorGroup $InitiatorGroup -Volume $SQLData -Access Volume
      Add-NSInitiatorGroupToVolume -InitiatorGroup $InitiatorGroup -Volume $SQLIndex -Access Volume
      Add-NSInitiatorGroupToVolume -InitiatorGroup $InitiatorGroup -Volume $SQLLog -Access Volume
      # END handle getting cloning via Nimble plugin


      # Refresh iSCSI Targets
      Write-Host "Refreshing to find new targets"
      Refresh-Targets

       

      # Get Available Nimble Targets
      $NimbleTargets = Get-AvailableTargets | ? {$_.Target -notmatch "com.nimblestorage:control-"} | ? {$_.Target -match "com.nimblestorage"} | Sort-Object -Unique Target
      Write-Host "Found " $NimbleTargets.Count " Nimble targets"

       

      # Loop through each Nimble target and host IP and validate connections. If any are missing, execute iscsicli to connect and make persistent
      Write-Host "Removing all old Nimble persistent iSCSI logins"
      foreach ($Login in Get-PersistentLogins | ? {$_.Target -match "com.nimblestorage"}) {
        if ($Login.Target -eq $null) {
          continue
        }
        iscsicli removepersistenttarget $Login.Initiator $Login.Target $Login.InitiatorPort $Login.TargetIP $Login.TargetPort | Out-Null
      }

       

      foreach ($Target in $NimbleTargets) {
        Write-Host "Connecting to " $Target.Target
        Connect-toIQN $Target.Target
      }

      # Wait 5 seconds for connections to finalize
      Write-Host "Rescanning for new drives"
      "rescan `n exit" | diskpart | out-null
      Start-Sleep -s 10

       

      # Get current set of Nimble sessions
      #$NimbleDisks = Get-EstablishedSessions | ? {$_.Target -match "com.nimblestorage"} | ? {$_.Target -match $env:COMPUTERNAME + "-" + $SQLInstance } | Select Target,TargetNice,Devices -Unique
      $NimbleDisks = Get-EstablishedSessions | Select Target,TargetNice,Devices -Unique

       

      # Loop Nimble Disks and make sure they are online and mount if not already mounted
      foreach ($NimbleDisk in $NimbleDisks) {
        Write-Host "Setting " $NimbleDisk.TargetNice " online"
        "select disk "+$NimbleDisk.Devices[0].DeviceNumber+"`n attributes disk clear readonly noerr`n online `n online disk" | diskpart | out-null
        $Disk = Get-WmiObject -Class Win32_DiskDrive | ? {$_.Caption -match "Nimble Server" -and $_.DeviceID -eq $NimbleDisk.Devices[0].LegacyName}
        $Partitions = Get-WmiObject -Query "ASSOCIATORS OF {$($Disk.Path.RelativePath)}" | ? {$_.__CLASS -eq "Win32_DiskPartition"}
        foreach ($Partition in $Partitions) {
      $Path = "c:\nimble\"+$NimbleDisk.TargetNice+"\"+$Partition.Index
      if(Test-Path -Path $Path -PathType Container) {
         mountvol $Path /d
      }
      else {
         #New-Item $Path -ItemType Directory | Out-Null
      }

        Write-Host "Mounting " $NimbleDisk.TargetNice " @ " $Path
      "select disk "+$NimbleDisk.Devices[0].DeviceNumber+"`n select partition "+$($Partition.Index + 1)+"`n attributes volume clear readonly noerr`n attributes volume clear hidden noerr`n attributes volume clear shadowcopy noerr`n assign mount="+$Path | diskpart
      "select disk "+$NimbleDisk.Devices[0].DeviceNumber+"`n select partition "+$($Partition.Index + 1)+"`n attributes volume clear readonly noerr`n attributes volume clear hidden noerr`n attributes volume clear shadowcopy noerr`n assign mount="+$Path | diskpart
      chkdsk /x $Path | out-null
        }
      }

       

      Start-Sleep -s 30

       

      # Restart SQL Services
      foreach($service in $services) {
      if((Get-WmiObject Win32_Service -filter ("Name='"+$service.Name+"'")).StartMode -eq "Auto") {
        Write-Host "Starting SQL service " $service.Name
        Start-Service -Name $service.Name
      }
      }


      Write-Host "sqlscript1"
      Invoke-SqlCmd -ServerInstance $FullSQL -InputFile "C:\Nimble\Scripts\sqlscript1.sql" -querytimeout 600
      Write-Host "sqlscript2"
      Invoke-SqlCmd -ServerInstance $FullSQL -InputFile "C:\Nimble\Scripts\sqlscript2.sql" -querytimeout 600
      Write-Host "sqlscript3"
      Invoke-SqlCmd -ServerInstance $FullSQL -InputFile "C:\Nimble\Scripts\sqlscript3.sql" -querytimeout 600

      Write-Host "Refresh Complete"

      $return = $gm.logout($sid.Value)
      $Log | Disable-LogFile

      if ($error.count -ne 0) {[string]$EmailBody = $error}
      if ($error.count -ne 0) {[string]$Subject = "Failure: $SQLInstance Refresh Error"}
      if ($error.count -eq 0) {[string]$EmailBody = (Get-Content -Path $LogFile -raw)}
      if ($error.count -eq 0) {[string]$Subject = "Success: $SQLInstance Refresh has been Applied"}

      $email = @{
      From = $From
      To = $To
      Subject = $Subject
      SMTPServer = $SMTP
      Body = $EmailBody
      }

      send-mailmessage @email

       

       

       

       

       

      The scheduled task is setup as an Administrator, Run with highest privileges:

      The program/script is C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

      I have tried variations on the arguments.  Here is the latest:   -ExecutionPolicy bypass -command "C:\Nimble\RefreshSQL.ps1"

      I have also tried -f instead of -command, different execution policies, etc.

      I do see the powershell window open as an administrator.  It just acts differently than the ISE.

        • Re: Powershell ISE Refresh Successful. Refresh via Powershell or Scheduled Task Failure
          Jeffrey Adams Wayfarer

          This issue has been resolved.

          The initial setup involved mirroring to newer SQL servers.  We failed over to the new servers and then turned off mirroring.

          When we would refresh other environments the databases thought they were part of mirroring still.

          Mirroring was turned off.

          Cloning the volumes resulted in databases that would be stuck in 'Pending Recovery'.

          After digging thru the error logs the databases were complaining about a mirroring endpoint.  I recreated the endpoint (used in the initial mirroring configuration) and the refresh ran normally.