• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 94
  • Last Modified:

powershell timeout when restore vldb database

Hello,

The following script works with little database but failed with vldb, I use ConnectionContext.StatementTimeout 0 but it doesn't work if the size is higher 200GB, why ?
 
 function Add-CustomAvailabilityDB{
 <# .SYNOPSIS     
        Adds an existing Database to an existing Availabilty Group on all replicas provided
	.EXAMPLE
		Add-CustomAvailabilityDB -Servers SQLPRD1,REPL3001 -SqlAgName AG-CRMPRD -Databases CRM,Clients  -verbose 
 #>
   [CmdletBinding()]
      Param(
      [Parameter(Mandatory=$true)]
      [string[]]$Servers,
      [Parameter(Mandatory=$true)]
      [string]$SqlAgName,
      [string[]]$Databases,
      [string]$BackupDirectory,
      [int]$Timeout=0
    )

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") |Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
    Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Done Loading SMO Assemblies ...");
    $SqlServerPrim = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Servers[0]
    $SqlServerPrim.ConnectionContext.StatementTimeout = $Timeout

    #Loop through servers Backing up Database/Tranlog and Restore on each Secondary with NoRecovery
    $loopCnt = 0
    foreach ($Server in $Servers){
        $SqlConn = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Server
        $SqlConn.ConnectionContext.StatementTimeout = 0
        If($loopCnt -eq 0) #First Server is Primary Back up Full and TranLogs
        {
                         
            foreach ($db in $Databases){
            Try{
 
                    $DBcheck = $sqlconn.Databases | Where-Object {$_.name -eq $db}
        
                    If(!$DBcheck){
                        Throw "$db doesn't exist on $Server please verify Primary Server is first in the list"
                        exit
                    }

                    If($DBcheck.AvailabilityGroupName){
                        $DBAGCheck = $DBcheck.AvailabilityGroupName
                        Throw "$db exists in $DBAGCheck availability group already."
                        exit
                    } 
                    if ($SQLconn.Databases[$db].RecoveryModel -ne "FULL")
                    {
                        $sqlconn.Databases[$db].RecoveryModel = "Full"
                        $sqlconn.Databases[$db].Alter();
                        Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) +": Database is not in Full Recovery Mode Setting to Full...");
                    }
                }
            Catch{
                   Throw "Failed to set $db to Full Recovery."
                 exit
                }
			
		
			If ($BackupDirectory){        
				Try{
						$DbBackup = New-Object -typename Microsoft.SqlServer.Management.Smo.Backup
						$DbBackup.Database = $db
						$DbBackup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
						$DbBackup.Initialize = $true
						$DbBackup.Devices.AddDevice("$BackupDirectory\$($db)_AgSetup_full.bak",      [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
						$DbBackup.SqlBackup($SqlConn)
						Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": DbBackup Full for $db ...");
						$DbBackup = New-Object -typename Microsoft.SqlServer.Management.Smo.Backup
						$DbBackup.Database = $db
						$DbBackup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Log
						$DbBackup.Initialize = $true
						$DbBackup.Devices.AddDevice("$BackupDirectory\$($db)_AgSetup_log.trn", [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
						$DbBackup.SqlBackup($SqlConn)
						Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": DbBackup TranLog for $db ...");           
					}
				Catch{
						Throw "Failed to backup $db"
						Exit
					}
			}
            
            Try{
                $AvailabilityGroup = $SqlConn.AvailabilityGroups | Where-Object {$_.Name -eq $SqlAgName}
                $AvailabilityDb = New-Object -typename Microsoft.SQLServer.Management.Smo.AvailabilityDatabase -ArgumentList $AvailabilityGroup,$db
				$AvailabilityGroup.AvailabilityDatabases.add($AvailabilityDb);
                $AvailabilityDb.create();
                $AvailabilityGroup.alter();
                Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Primary db $db added to AG...");  
            }

            Catch{
                Throw "Failed  to Add $db on $SqlAgName..."
                Exit
            }
            
            }#For Each DB
        }
        else
        {
             foreach ($db in $Databases){

		If ($BackupDirectory){    
	            try{
                    $DbRestore = New-Object -typename Microsoft.SqlServer.Management.Smo.Restore    
                    $DbRestore.Database = $db
                    $DbRestore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Database
                    $DbRestore.Devices.AddDevice("$BackupDirectory\$($db)_AgSetup_full.bak",
                    [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
                    $DbRestore.NoRecovery = $true
  		            $SqlConn.ConnectionContext.StatementTimeout = 0
                    $DbRestore.SqlRestore($SqlConn)
                    Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Restore Full for $db on $Server...");
                    $DbRestore = New-Object -typename Microsoft.SqlServer.Management.Smo.Restore
                    $DbRestore.Database = $db
					$Wait = 900;
                    $DbRestore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Log
                    $DbRestore.Devices.AddDevice("$BackupDirectory\$($db)_AgSetup_log.trn",
                    [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
                    $DbRestore.NoRecovery = $true
			
                    $DbRestore.SqlRestore($SqlConn)
                    Write-Verbose -Message  ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Restore Tran Log for $db on $Server...");      
                    }
                
                catch{
                        Throw "Failed  to Restore $db on $Server..."
                        Exit
                    } 
				}
				Try{
                While($true){

                    $AvailabilityGroup = $SqlConn.AvailabilityGroups | Where-Object {$_.Name -eq $SqlAgName}
                    $AvailabilityDb = $AvailabilityGroup.AvailabilityDatabases | Where-Object {$_.Name -eq $db}
                    $AvailabilityGroup = $SqlConn.AvailabilityGroups | Where-Object {$_.Name -eq $SqlAgName}
                    $AvailabilityGroup.AvailabilityDatabases.Refresh()
                    $AvailabilityDb=$AvailabilityGroup.AvailabilityDatabases | Where-Object {$_.Name -eq $db}
                    if ($AvailabilityDb)
                    {break}
                    Start-Sleep -Seconds 15
                    }

                    $AvailabilityDb.JoinAvailablityGroup();
                    Write-Verbose -Message  ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": $db Joined to $SqlAgName on $Server...");   
                }
                catch{
                      Throw "Failed  to Join $db on to $SQLAgName on  $Server..."
                      Exit
                }

            }#For each DB
        }#Else Secondary Servers
        $loopcnt=$loopCnt+1

    }#End For Each Server


    If ($BackupDirectory){ 
	 Try{
        $DBBackupFile = "$BackupDirectory\$($db)_AgSetup_full.bak"
        $DBTRNFile = "$BackupDirectory\$($db)_AgSetup_log.trn"
        If (Test-Path -Path $DBBackupFile){Remove-Item -Path $DBBackupFile}
        If (Test-Path -Path $DBTRNFile){Remove-Item -Path $DBTRNFile}
        }    
        Catch{
            Throw "Failed  to Cleanup Backup Files..."
            Exit
		} 
		Write-Verbose -Message  ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Done Cleaning Up Backup Files...");
	}
}#Function End


Add-CustomAvailabilityDB -Servers test -SqlAgName test -Databases Clients -BackupDirectory \\test\sql -verbose 

Open in new window


Thanks

Regards
0
bibi92
Asked:
bibi92
  • 5
  • 5
  • 2
  • +3
1 Solution
 
Dustin SaundersDirector of OperationsCommented:
Which part is it failing at?  Backup or restore?  Any errors in the shell window?

I don't have a set up to test run this, but you're creating new SMO objects each loop, so your setting isn't applied to your backups or restores.

Seems like you might some extra lines in your code that aren't doing anything?
(22 - 23)
$SqlServerPrim = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Servers[0]
    $SqlServerPrim.ConnectionContext.StatementTimeout = $Timeout

Open in new window

(117)
$Wait = 900;

Open in new window

0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
I would have to know what the error message you get. The StatementTimeout of 0 is for unlimited time, so there must be something else going on instead of a timeout.  You should go down that route. Check the exception and inner exception. You can put this in your try { } catch { }. $_.Message and $_.InnerException.Message to see what the errors are.
0
 
CoralonCommented:
This is unrelated to the stated problem, but just an observation..

The AddWithPartialName construct is up for deprecation..  You can replace it with Add-Type -AssemblyName <name>

You can also use $null = <statements> rather than using | out-null.  It is *substantially* faster.

Coralon
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
DBAduck - Ben MillerPrincipal ConsultantCommented:
@Coralon there is another way to do the $null = <statements> you can also do [void]<statements> and it is faster than the $null =.

Just wanted to keep the question going.
Thanks.
0
 
bibi92Author Commented:
Hello,

Restore VLDB failed.

Thanks

Best regards
0
 
CoralonCommented:
I had read an article showing $null = was faster than [void] and both were *substantially* faster than | out-null.  I also found this stackoverflow article showing that $null is a *tiny* bit faster..
Measure-Command {$(1..1000) | Out-Null}

TotalMilliseconds : 76.211

Measure-Command {[Void]$(1..1000)}

TotalMilliseconds : 0.217

Measure-Command {$(1..1000) > $null}

TotalMilliseconds : 0.2478

Measure-Command {$null = $(1..1000)}

TotalMilliseconds : 0.2122

## Control, times vary from 0.21 to 0.24
Measure-Command {$(1..1000)}

TotalMilliseconds : 0.2141

Open in new window

https://stackoverflow.com/questions/5260125/whats-the-better-cleaner-way-to-ignore-output-in-powershell

Coralon
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
Good call. I have tested it in the newest version of PowerShell and it is still the case by 1 millisecond.
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
@bibi92 the message must be coming from the catch { } block.

What I am looking for is the actual exception.

If you could do something like the below in the catch { } block on the Restore command.
Write-Output $_.Exception.Message
Write-Output $_.Exception.InnerException.Message

This will give us both error levels that are getting thrown.  The inner exception will be what we really want to know.

Ben Miler
0
 
bibi92Author Commented:
Thanks a lot regards
0
 
bibi92Author Commented:
I have done :
Write-Output $_.Exception.Message
Write-Output $_.Exception.InnerException.Message

I modify the script :

Use SqlServer module instead of SqlPs

-          Download the SqlServer module from a Windows 10 workstation by running in a PowerShell Windows:

o   Save-Module -Name SqlServer -Path C:\Temp\SqlServer

-          Then copy the folder SqlServer  in C:\Program Files\WindowsPowerShell\Modules

 This is because the SqlPs module is “outdated” and has a specific bug with SQL 2014 AG from SQL 2016+. It is strongly advised by Microsoft to use SqlServer PowerShell module instead, which is frequently updated and had this issue fixed in late August 2017.
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
I tested this last night to ensure that all the messages from SQL are passing through the exceptions.  So in the catch { } block I put the following code and it showed me all the messages from SQL in the inner exceptions. Post the error message you get from this catch block and we can go from there.

$err = $_.Exception
  			$errmsg = $err.Message
  			while( $err.InnerException ) {
  				$err = $err.InnerException
  				$errmsg = $errmsg + "|" + $err.Message
  			}
			$errmsg

Open in new window

0
 
bibi92Author Commented:
Hello,

Thank you I have modified the script for using SqlServer module recommended.

Best regards
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
bibi, how did you get at that info? I guess it has been on own research, and hence your own comment #a42437118
should be the accepted one - valuable information, though the relation to your issue is not obvious (at first glance).
0
 
PberSolutions ArchitectCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: bibi92 (https:#a42437118)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

Pber
Experts-Exchange Cleanup Volunteer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 5
  • 5
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now