PowerShell: Wait for SQL Server is ready for client connections

The useful PowerShell script waiting SQL server is ready.
I found that waiting for starting service

while((get-service MSSQL`$INSTANCENAME).Status -ne 'Running'){Sleep 3}

is not enough.
Better to check the event log entry EventID = 17126: SQL Server is now ready for client connections. This is an informational message; no user action is required.

$ew = new-object system.management.ManagementEventWatcher                                                         
$ew.query = "Select * From __InstanceCreationEvent Where TargetInstance ISA 'Win32_NTLogEvent'"                   
while(!(get-eventlog -logname 'Application' -Source 'MSSQL$INSTANCENAME' | ? {$_.EventId -eq 17126})){
	$ew.WaitForNextEvent()
}
Advertisements

2 thoughts on “PowerShell: Wait for SQL Server is ready for client connections

  1. Actually, I didn’t invent that quote. I have heard Chuck Thacker use it. But long before that, Ivan Sutherland.

    I think the actual phase was “An engineer can make for a dime what any fool can make for a dollar”

    Cheers,

    Alan

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s