Why does this SQL Server DBA learn PowerShell-SMO tasks and miscellaneous?

Why does the SQL Server DBA learn PowerShell?

From: http://www.simple-talk.com/ SQL /database-administration/why-this-sql-server-dba-is-learning-powershell/

Joe. TJ is used only for the purpose of disseminating information.

SMO is an object set that allows you to automate any Microsoft SQL Server-related management tasks. Similarly, for DBAs who are not familiar with object-oriented programming, the biggest obstacle is the use of more daunting object models. Similarly, like WMI, you need to know how to check an object to determine its available attributes and methods.

In the SMO example, you will view the Foreach loop used to execute the SMO code again. All examples start by setting an SMO Assembly reference. Once you create this reference, the script can instantiate the new object derived from this Assembly class.

Browse SMOClass

Although the SMO class has records in online books, it is useful if you learn to obtain the list of Object Attributes and methods. To browse the SMO class, you need to set a reference and then use it. The Get-Member (gm) cmdlet displays the attributes and methods of the object.

# To examine the SMO Server object in PowerShell:

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

$svr = new-object ("Microsoft.SqlServer.Management.Smo.Server") "ServerName"

$svr | get-member

To study different objects, change the second and third rows in the above script accordingly.

 

List SysAdmin serversRole Member

Based on your previous experience, it may be tricky to understand how the SMO object model works. I understand basic object-oriented programming knowledge, but I don't fully understand it until I execute a script to list SysAdmin role members on the server. At first, I tried to use the following script and received an error message. (During the test, I did not encounter the error message mentioned by the author. I only output $ svrole as a string variable and did not get the expected result of the role Member. Therefore, the author's diagram is directly referenced here .)

# Before I understood the concept of objects completely, I tried…

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

$svr="ServerName"

$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"

$svrole = 'sysadmin'

$svrole 

At this point, I had a small epiphany. Finally, I understood the concept of object-oriented programming and "Everything is an object" in PowerShell ". I have successfully created an instance of a server object and want to process the server role object for the SysAdmin role in my own way. Therefore, I set a variable $ svrole and assigned the value 'sysadmin '.

Then I tried to call the method of this string object and thought that I was calling the method of the server role object. In this case, the variable $ svrole only contains string objects rather than references to server role objects. Therefore, the above error will occur.

The following script encapsulates the PowerShell Code required to list SysAdmin members on the server into a function.

# create sa function to list sysadmin members

# usage: sa ServerName

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

function sa ($s)

{

$svr="$s"

$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"

$svrole = $srv.Roles | where {$_.Name -eq 'sysadmin'}

$svr

$svrole.EnumServerRoleMembers()

}

List local administrators on servers

I use the following script (and the previous one) to maintain the least Admin permissions on the Server and SQL Server. This example is written by Microsoft MVP Ying Li and posted on his blog. It demonstrates how to list local administrators on the server. This function receives a server name, connects to the specified server, and lists the members of the local administrator group on it.

# create ListAdmins function to list local Administrators on a server.

# usage: ListAdmins ServerName

function ListAdmins ($svr)

{

$domain = [ADSI]""

$strComputer = $svr

$computer = [ADSI]("WinNT://" + $strComputer + ",computer")

$computer.name;

$Group = $computer.psbase.children.find("administrators")

$Group.name

$members= $Group.psbase.invoke("Members") | %{$_.GetType().InvokeMember("Name", 'GetProperty', $null, $_, $null)}

$members

} 

Query logon or AD on multiple serversGroup

One of my first SMO examples was inspired by my supervisor who asked me to find out which database servers the Data creation module can access. She hopes that only the Development Server can be accessed by this group.

The following example ends with 5 to 7 lines of code (depending on your orchestration), but no matter how many servers are on your server list, this will identify the logon/group.

# Find a login or AD group on multiple servers

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

foreach ($svr in get-content "C:\AllServers.txt")

{

$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"

trap {"Oops! $_"; continue } $srv.Logins | where {$_.Name -eq 'DOMAIN\ITS_DATA_ADMIN'} | select Parent, Name

}

The Trap statement in the script handles errors that occur when connecting to the server. In this example, if an error occurs when connecting to the server, the server name and error message are returned. Occasionally, I will see "Oops!" in the output! Failed to connect to the server ServerName ".

 

Check failed SQL statements on multiple serversProxy job

Every morning, I run the following script to check for any failed SQL proxy jobs on my server:

# Check for failed SQL jobs on multiple servers

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

foreach ($svr in get-content "C:\AllServers.txt")

{

write-host $svr

$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"

$srv.jobserver.jobs | where-object {$_.lastrunoutcome -eq "Failed" -and $_.isenabled -eq $TRUE} | format-table name,lastrunoutcome,lastrundate -autosize

} 

Miscellaneous tasks

The following PowerShell examples answer some questions that DBA may have.

 Check the installed hotfix

  # List all installed hotfixes on a server

get-wmiobject Win32_QuickFixEngineering

# Check if a specific hotfix is installed on a server

get-wmiobject Win32_QuickFixEngineering | findstr KB928388

Search for port numbers

I am often asked by developers about the Instance name port number. Use a short named pipe to combine two cmdlet: Get-Content and Select-String. You can find the port number in the error log through a program. This is much faster than manually searching for error logs or executing a piece of SQL code.

I tried to search for error logs only using Select-String, but for some reason, Get-String cannot read the error logs of the activity unless used in conjunction with Get-Content. In the example below, I find the word "Listening" in the error log.

# Find a port number

gc \\ServerName\ShareName\MSSQL2005\MSSQL.2\MSSQL\LOG\ERRORLOG | select-string "listening"

 

Keep in mind that if you have cyclic error logs on the server, the lines you need to search for may not be in the current error log. You will need to adjust the following commands to search for error log archives by appending 1, 2, 3, and so on after ERRROLOG.

If you search for error logs on the named instance of SQL Server 2000, you need to use reverse quotation marks to escape $ in the file path. As follows:

get-content \\ServerName\ShareName\MSSQL2000\MSSQL`$SQL100\LOG\ERRORLOG | select-string "listening"

 

Generate random password

If you need to generate a random password for SQL logon, you can use the. NET class as follows:

# generate a random password

[Reflection.Assembly]::LoadWithPartialName(”System.Web” ) | out-null

[System.Web.Security.Membership]::GeneratePassword(10,2) # 10 bytes long

[System.Web.Security.Membership]::GeneratePassword(8,2) # 8 bytes long

 

Check the current backup on multiple servers

In my environment, I have two database configurations and the backups are not always placed in a standard location. Therefore, I used the "Brute Force" solution to check backups.

# Checking backups are current

write-host ''

write-host 'ServerName'

get-childitem \\ServerName\ShareName\dump_data\*.bak | where-object { $_.LastWriteTime -ge (Get-Date).AddDays(-1) } | Select name, LastWriteTime

If multiple drives on the server need to be checked, I will repeat the Get-ChildItem cmdlet for the additional drive. Here is a snippet of my ChkBkups. ps1 script:

# checking three dump locations on a default instance.

write-host ''

write-host 'Server1'

get-childitem \\Server1\e$\dump_data\ServerName\*.bak | where-object { $_.LastWriteTime -ge (Get-Date).AddDays(-1) } | Select name, LastWriteTime

get-childitem \\Server1\g$\dump_data\ServerName\*.bak | where-object { $_.LastWriteTime -ge (Get-Date).AddDays(-1) } | Select name, LastWriteTime

get-childitem \\Server1\i$\dump_data\ ServerName \*.bak | where-object { $_.LastWriteTime -ge (Get-Date).AddDays(-1) } | Select name, LastWriteTime

# checking one dump location on a named instance.

write-host ''

write-host ' Server2'

get-childitem \\Server2\ShareName\dump_data\ServerName\Instance\db_dump\*.bak | where-object { $_.LastWriteTime -ge (Get-Date).AddDays(-1) } | Select name, LastWriteTime

I run this script every morning. We have a set of automated routines that run every night to process standard DBA tasks, such as backup, integrity check, and index maintenance. Each server maintenance process sends an email to report their status. This script saves me time to view multiple emails.

 

Summary:

I think using PowerShell will make me a better DBA, Because I consciously automate common tasks and collect server-related information more quickly, and better management of my server workload. I also found that using PowerShell will extend my knowledge to areas that I usually don't involve (this is only a good thing ).

How can we do so many things with just a few lines of PowerhShell code? This is amazing.

In my opinion, it is useful to spend time learning PowerShell.

 

Reference:

ADO. Net

• Books24x7.com-sub‑required "> Microsoft ADO. NET 2.0 Step by Rebecca M. Riordan

SMO

• SQL Server Books Online:
  Http://msdn.microsoft.com/en-us/library/ms162169.aspx
• Blogs:
  Http://blogs.msdn.com/mwories/archive/tags/SMO+Samples/default.aspx

WMI

• Http://msdn.microsoft.com/en-us/library/aa394582.aspx
• The http://msdn.microsoft.com/en-us/library/aa394572 (VS.85). aspx

PowerShell

• "Getting Started Guide"
  The http://msdn.microsoft.com/en-us/library/aa973757 (VS.85). aspx
• Books.
• "Windows PowerShell In Action" by Bruce Payette
• Windows PowerShell: TFM "by Don Jones and Jeffery Hicks
• Newsgroups
• Microsoft. public. windows. powershell
• Blogs and articles:
• Http://www.mssqlengineering.com/
• Http://www.simple-talk.com/ SQL /database-administration/managing-sql-server-using-powersmo/
• Http://myitforum.com/cs2/blogs/yli628/archive/2007/08/30/powershell-script-to-add-remove-a-domain-user-to-the-local-administrators-group-on-a-remote-machine.aspx
• Http://sqlblog.com/blogs/allen_white/archive/2008/01/25/using-powershell-and-sql-server-together.aspx
• Http://msmvps.com/blogs/robfarley/archive/2007/01/05/powershell-and-sql.aspx
• Http://myitforum.com/cs2/blogs/dhite/archive/2007/06/03/powershell-mini-sql-query-analyzer.aspx
• Http://blogs.msdn.com/mwories/archive/2008/06/14/SQL2008_5F00_Powershell.aspx