Why is this SQL Server DBA learning PowerShell-SQL task?

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.

I started learning PowerShell because I was looking for a fast and efficient way to collect information about my SQL Servers and better manage my server workload. I thought that I was learning another new scripting language, which could help me achieve the ideas I mentioned above. In fact, I found that learning PowerShell not only provides a powerful means to implement many conventional and repetitive server tasks and health checks, but also a useful stepping stone to improve my other skills. For example, when I learned PowerShell, I found:

I have improved my understanding of. NET, so that I can better communicate with the application developers I support.

Learned how to use Server Management Objects (SMO) to automate the execution of database-related tasks.

I learned about Windows Management Instrumentation (WMI). It allows me to query one or more servers with only one piece of information.

Better adapted to OOP.

 

In this article, I describe some examples of using PowerShell, and hope these are useful to DBAs. My script will demonstrate how to execute SQL queries, WMI queries, and SMO code on one or more servers, and help you better manage multiple database servers. All scripts passed the test in SQL Server 2005.

The purpose of this article is not to write a PowerShell tutorial. I suppose you are familiar with the following content: Basic PowerShell syntax, how to use cmdlets for help, how the command line works, how to run scripts, what is the named pipeline, and what is the alias. If you do not know the content, you can find a lot of help in various online articles, news groups, and blogs (some resources are listed in the reference sections at the end of the article ). Some of the scripts in this article come from what I encountered when reading these resources.

Use PowerShellManage multiple servers

The core of managing multiple servers using PowerShell is a simple server list, which includes the name of the server on which you want to perform regular tasks and health checks.

In my example, I use a simple allservers.txt containing my server. The format is as follows:

Server1

Server2

Server3

......

In the example I will demonstrate, I use a Foreach loop to execute a task on each server listed in this list. This simple server list forms the cornerstone for completing repetitive tasks. My main job is in the Microsoft environment. I found that using PowerShell to execute repetitive tasks is faster than using Python. For example, Python needs to read multiple lines of statements, open and close a file, but the Get-Content cmdlet in PowerShell only uses one line of code to read a file.

# Read a file

get-content "C:\AllServers.txt"

If you want to input too many words, you can use its alias to call the Get-Content cmdlet.

gc "C:\AllServers.txt"

The best practice to define for ease of reading is to use aliases in the command line and complete the cmdlet in the script. You can use the Get-Alias cmdlet to list all aliases in PowerShell:

# List aliases, sort by name or definition

get-alias | sort name

get-alias | sort definition

PowerShell is an interactive command line and a script environment.I started to solve a problem by executing the command in the command line. When I have determined the correct order of commands, I save them as a script file with the. ps1 extension and execute it when necessary.

 

Automated repetitive tasks

PowerShell makes it easier to automate regular and repetitive tasks for all my servers, making it easier to use bit of information about the server) you can quickly and efficiently process seemingly endless ad hoc requests. The following sections only describe some scripts that I have written to automate repetitive tasks. The progress of these examples comes from: I found that the problems that have been solved by a lot of effort have become very simple to convert to Powershell for processing.

 SQLTask

The simplest task of converting Python to PowerShell is to execute a statement on multiple servers. The basic steps in these examples are as follows:

1. Read Database Server list for each server
2. Create a table to store results
3. Establish a connection with the server
4. Execute the query and format the query result.

 

Check SQLServer on multiple serversVersion:

Run the following script to check whether all servers are at the patch level specified by the company:

# SQLVer.ps1

# usage: ./SQLVer.ps1  

# Check SQL version

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

{

$con = "server=$svr;database=master;Integrated Security=sspi"

$cmd = "SELECT SERVERPROPERTY('ProductVersion') AS Version, SERVERPROPERTY('ProductLevel') as SP"

$da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)

$dt = new-object System.Data.DataTable

$da.fill($dt) | out-null

$svr

$dt | Format-Table -autosize

}        

 

The following script follows the standard template I used to execute SQL scripts on multiple servers. It uses foreach to read the Server LIST cyclically, connect to the server, and execute an SQL query that returns the user's database name. For this article, I have edited the format of the example. The annotation is in green, the PowerShell code is in blue, and the SQL is in red.

Check the actual database directory and internal database directory

Every month, I need to check the actual database directory and an internal developed database directory system that is referenced by other applications as resources.

# inv.ps1

# usage: ./inv.ps1

# Database inventory

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

{

$con = "server=$svr;database=master;Integrated Security=sspi"

$cmd = "SELECT name FROM master..sysdatabases WHERE dbid > 4 AND name NOT IN ('tracedb','UMRdb','Northwind','pubs','PerfAnalysis') ORDER BY name"

$da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)

$dt = new-object System.Data.DataTable

$da.fill($dt) | out-null

$svr

$dt | Format-Table -autosize

} 

This query is sorted by server and returns the name of a database not provided by Microsoft (Non-Microsoft supplied, I guess the author is an SqlServer System database. Then, I will compare it with a report generated by the database directory system.

 

From SysAdminRemove BULTIN \ Administrator from roleGroup

This script defines a function instead of a foreach loop, allowing me to remove the BULTIN \ Administrator group from the SysAdmin role on any server. Type only:

Rmba ServerName

This function accepts a parameter, establishes a connection with the server, and then executes the sp_dropsrvrolememeber system stored procedure.

# Remove BUILTIN\Administrators from sysadmin role

function rmba ($s)

{

$svr="$s"

$cn = new-object System.Data.SqlClient.SqlConnection"server=$svr;database=master;Integrated Security=sspi"

$cn.Open()

$sql = $cn.CreateCommand()

$svr

$sql.CommandText = "EXEC master..sp_dropsrvrolemember @loginame = N'BUILTIN\Administrators', @rolename = N'sysadmin';"

$rdr = $sql.ExecuteNonQuery();

}

This script saves me time because I do not need to jump to SSMS to complete this task. In the SMO section, you will find two other functions that I have created to list members of BULTIN \ Administrator and the server's local Administrator.

--------------------------------------------

If it is reproduced or referenced, keep the following content:
Joe's Blog: http://www.cnblogs.com/Joe-T/