Vyoms OneStopTesting.com - Testing EBooks, Tutorials, Articles, Jobs, Training Institutes etc.
OneStopGate.com - Gate EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopMBA.com - MBA EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopIAS.com - IAS EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopSAP.com - SAP EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopGRE.com - of GRE EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
Bookmark and Share Rss Feeds

Why This SQL Server DBA is Learning Powershell | Articles | Recent Articles | News Article | Interesting Articles | Technology Articles | Articles On Education | Articles On Corporate | Company Articles | College Articles | Articles on Recession
Sponsored Ads
Hot Jobs
Fresher Jobs
Experienced Jobs
Government Jobs
Walkin Jobs
Placement Section
Company Profiles
Interview Questions
Placement Papers
Resources @ VYOMS
Companies In India
Consultants In India
Colleges In India
Exams In India
Latest Results
Notifications In India
Call Centers In India
Training Institutes In India
Job Communities In India
Courses In India
Jobs by Keyskills
Jobs by Functional Areas
Learn @ VYOMS
GATE Preparation
GRE Preparation
GMAT Preparation
IAS Preparation
SAP Preparation
Testing Preparation
MBA Preparation
News @ VYOMS
Freshers News
Job Articles
Latest News
India News Network
Interview Ebook
Get 30,000+ Interview Questions & Answers in an eBook.
Interview Success Kit - Get Success in Job Interviews
  • 30,000+ Interview Questions
  • Most Questions Answered
  • 5 FREE Bonuses
  • Free Upgrades


Wipro Technologies
Tata Consultancy Services
Cognizant Technologies

Home » Articles » Why This SQL Server DBA is Learning Powershell

Why This SQL Server DBA is Learning Powershell

Article Posted On Date : Friday, May 22, 2009

Why This SQL Server DBA is Learning Powershell

HTML clipboard

Why This SQL Server DBA is Learning Powershell

I started studying PowerShell because I was looking for a quicker, more efficient way to gather information regarding my SQL Servers, and to manage my server workload better. I thought I was just going to learn yet another new scripting language that would help me do this. In fact, alongside providing a powerful means to automate many of my common, repetitive server tasks and heath checks, I've found that learning PowerShell has also proved a useful springboard to improving my skills with other, related technologies. For example, while learning PowerShell, I've found that I've:

* Improved my knowledge of .NET, so that I can talk more intelligently to the Application Developers I support.
* Learned how to use Server Management Objects (SMO) to automate my database-related tasks.
* Learned about Windows Management Instrumentation (WMI), allowing me to query one or more servers for a piece of information.
* Become more comfortable with object-oriented programming.

In this article, I will describe several examples of using PowerShell that I would hope a DBA would find useful. My scripts will demonstrate how to run SQL queries, WMI queries or SMO code on one or more machines, and help you to better manage multiple database servers. All of the scripts have been tested on SQL Server 2005.

This article is not intended to be a PowerShell tutorial. I assume that you are familiar with the basic PowerShell terminology, how to get help with the cmdlets, how the command line works, how to run a script, what a pipeline is, what aliases are, and so on. If not, plenty of help can be found in various online articles, newsgroups and blogs (a reference section, at the end of the article, lists some of these sources). Some of the scripts I include with this article were derived from examples I encountered during my readings.

Managing Multiple Servers using PowerShell
At the heart of multiple server management with PowerShell is a simple list of the servers on which you wish to run your routine tasks and health checks.

In my examples, I use a AllServers.txt file that simply contains a list of my servers, in the following format:

As I will demonstrate in the examples, I use this list to run a task against each listed server, using a foreach loop. This simple server list forms the cornerstone for completing repetitive tasks.

I work primarily in a Microsoft environment and I'm finding it quicker to perform repetitive tasks with Powershell than I previously did with Python. For instance, whereas Python needs multiple lines to open, read, and close a file, the get-content cmdlet in PowerShell reads a file with one line of code:

# Read a file
get-content "C:AllServers.txt"
If even that feels like too much typing, you can invoke the get-content cmdlet via its alias:
gc "C:AllServers.txt"
The defined best practice is to use the alias at the command line and the complete cmdlet in scripts, for readability. You can list all the PowerShell aliases with the get-alias cmdlet:
# List aliases, sort by name or definition
get-alias | sort name
get-alias | sort definition
PowerShell is both an interactive command line and scripting environment.

I start solving a problem by executing commands at the command line. When I have established the correct sequence of commands, I save them to a script file with the .ps1 extension and execute it as needed.

Automating Repetitive Tasks
PowerShell makes it easier for me to automate common, repetitive tasks across all my servers, and to deal quickly and efficiently with the seemingly-endless stream of ad-hoc requests for some bit of information about each of them.

The following sections describe just some of the PowerShell scripts I've created to automate these repetitive tasks. The examples progress from those I found the easiest to convert to PowerShell to those that took more effort to solve.

SQL Tasks
The easiest tasks to convert from Python to PowerShell were those that executed a SQL query against multiple machines. In these examples, the basic procedure followed in each script is as follows:
1. Read a list of database servers and for each server
2. Create a data table to store the results
3. Establish a connection to the server
4. Run the query and format its output.

Checking Versions of SQL Server on Multiple Servers
I run the following script to determine if my servers are at the approved patch level for our company:
# SQLVer.ps1
# usage: ./SQLVer.ps1
# Check SQL version
foreach ($svr in get-content "C:dataAllServers.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
$dt | Format-Table -autosize

The script follows the standard template I use for executing all of my SQL scripts against multiple servers. It uses a foreach loop to read through a list of servers, connect to the server and execute a query that returns the names of the user databases on each server. For this article, I have formatted the examples with comments in Green, PowerShell code in Blue and SQL in Red.

Reconciling Actual Database Inventory with Internal Database Inventory
On a monthly basis, I must reconcile my real database inventory with an internally-developed database inventory system that is used as a resource by other applications.
# inv.ps1
# usage: ./inv.ps1
# Database inventory
foreach ($svr in get-content "C:dataAllServers.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
$dt | Format-Table -autosize

The query returns the database names for all non-Microsoft supplied databases, sorted by server, and I then compare this to a report generated by the database inventory system.

Remove BUILTINAdministrators Group from the Sysadmin Role

This script, instead of a foreach loop, defines a function that allows me to remove the BUILTINAdmin group from the sysadmin role on any server, simply by typing:
rmba ServerName

The function accepts one parameter, establishes the connection to the server and executes the sp_dropsrvrolemember system stored procedure.
# Remove BUILTINAdministrators from sysadmin role
function rmba ($s)
$cn = new-object System.Data.SqlClient.SqlConnection "server=$svr;database=master;Integrated Security=sspi"
$sql = $cn.CreateCommand()
$sql.CommandText = "EXEC master..sp_dropsrvrolemember @loginame = N'BUILTINAdministrators', @rolename = N'sysadmin';"
$rdr = $sql.ExecuteNonQuery();

This script saves me time since I don't have to jump into Management Studio to complete the task. In the SMO section you'll find two other functions that I created to list the members of the sysadmin group and a server's local administrators.

Windows Management Instrumentation (WMI) Tasks
My next task was to get a quick view of free space on all my servers. In order to do this, I had to dip my toes into the world of WMI, which provides an object model that exposes data about the services or applications running on your machines.

The first obstacle here is figuring out what WMI has to offer. As soon as you start looking you'll realize that the object model for WMI (and SMO) is vast and you'll need to invest a little time in browsing through it and working out what does what.

MSDN is the best place to go for documentation on the WMI classes.
Browsing the Win32 WMI Classes
The WMI classes that will be the most useful to a DBA are the Win32 classes and you can use the following one-liner to get the list of all the available Win32 classes:
# Browse Win32 WMI classes
get-wmiobject -list | where {$_.name -like "win32*"} | Sort-Object

The classes I found interesting initially were:
* Win32_LogicalDisk - gives you stats on your disk drives
* Win32_QuickFixEngineering - enumerates all the patches that have been installed on a computer.

The following examples will highlight the use of these and other classes of interest.
Checking Disk Space
The simplest way to check disk space is with the Win32_LogicalDisk class. DriveType=3 is all local disks. Win32_LogicalDisk will not display mountpoint information.

# Check disk space on local disks on local server
Get-WmiObject -Class Win32_LogicalDisk -Filter "DriveType=3"

# Check disk space on a remote server.
Get-WmiObject -Class Win32_LogicalDisk -Filter "DriveType=3" -ComputerName ServerName

After a little digging around I found that, rather than try to calculate free space percentages from the output of the Win32_LogicalDisk class, it was easier to use the Win32_PerfFormattedData_PerfDisk_LogicalDisk class and its property PercentFreeSpace. In this example, I'm checking for drives with less than 20% free space. The other reason to use this class for checking disk space is that it will display mountpoint information.

# Checking disk space
foreach ($svr in get-content "C:AllServers.txt")
$svr; Get-WmiObject Win32_PerfFormattedData_PerfDisk_LogicalDisk -ComputerName $svr | where{$_.Name -ne "_Total" -and $_.PercentFreeSpace -lt 20} | select-object Name, PercentFreeSpace | format-list

# Local computer example
Get-WmiObject -class Win32_PerfFormattedData_PerfOS_Processor -Property Name,PercentProcessorTime | where{$_.Name -eq "_Total"} Checking what Services are Running on a Server

A quick way to find out which services are running on a remote server is to use the get-wmiobject, specifying the class, Win32_Service, and then the properties you want to list, and finally the computer that you wish to query. In this example, I'm just retrieving the name of the service:

# Checking what services are running on a computer.
get-wmiobject win32_service �computername COMPUTER | select name
Is IIS Running on your SQL Server? Oh, No!

If you want to see if a specific service is running on a remote machine use the filter parameter, �f, and specify the name of the service.
# Is IIS running on your server?
get-wmiobject win32_Service -computername COMPUTER -f "name='IISADMIN'"
gwmi win32_Service �co COMPUTER -f "name='IISADMIN'"

Sponsored Ads

Interview Questions
HR Interview Questions
Testing Interview Questions
SAP Interview Questions
Business Intelligence Interview Questions
Call Center Interview Questions


Clipper Interview Questions
DBA Interview Questions
Firebird Interview Questions
Hierarchical Interview Questions
Informix Interview Questions
Microsoft Access Interview Questions
MS SqlServer Interview Questions
MYSQL Interview Questions
Network Interview Questions
Object Relational Interview Questions
PL/SQL Interview Questions
PostgreSQL Interview Questions
Progress Interview Questions
Relational Interview Questions
SQL Interview Questions
SQL Server Interview Questions
Stored Procedures Interview Questions
Sybase Interview Questions
Teradata Interview Questions

Microsof Technologies

.Net Database Interview Questions
.Net Deployement Interview Questions
ADO.NET Interview Questions
ADO.NET 2.0 Interview Questions
Architecture Interview Questions
ASP Interview Questions
ASP.NET Interview Questions
ASP.NET 2.0 Interview Questions
C# Interview Questions
Csharp Interview Questions
DataGrid Interview Questions
DotNet Interview Questions
Microsoft Basics Interview Questions
Microsoft.NET Interview Questions
Microsoft.NET 2.0 Interview Questions
Share Point Interview Questions
Silverlight Interview Questions
VB.NET Interview Questions
VC++ Interview Questions
Visual Basic Interview Questions

Java / J2EE

Applet Interview Questions
Core Java Interview Questions
Eclipse Interview Questions
EJB Interview Questions
Hibernate Interview Questions
J2ME Interview Questions
J2SE Interview Questions
Java Interview Questions
Java Beans Interview Questions
Java Patterns Interview Questions
Java Security Interview Questions
Java Swing Interview Questions
JBOSS Interview Questions
JDBC Interview Questions
JMS Interview Questions
JSF Interview Questions
JSP Interview Questions
RMI Interview Questions
Servlet Interview Questions
Socket Programming Interview Questions
Springs Interview Questions
Struts Interview Questions
Web Sphere Interview Questions

Programming Languages

C Interview Questions
C++ Interview Questions
CGI Interview Questions
Delphi Interview Questions
Fortran Interview Questions
ILU Interview Questions
LISP Interview Questions
Pascal Interview Questions
Perl Interview Questions
PHP Interview Questions
Ruby Interview Questions
Signature Interview Questions
UML Interview Questions
VBA Interview Questions
Windows Interview Questions
Mainframe Interview Questions

Copyright © 2001-2023 Vyoms.com. All Rights Reserved. Home | About Us | Advertise With Vyoms.com | Jobs | Contact Us | Feedback | Link to Us | Privacy Policy | Terms & Conditions
Placement Papers | Get Your Free Website | IAS Preparation | C++ Interview Questions | C Interview Questions | Report a Bug | Romantic Shayari | CAT 2023

Fresher Jobs | Experienced Jobs | Government Jobs | Walkin Jobs | Company Profiles | Interview Questions | Placement Papers | Companies In India | Consultants In India | Colleges In India | Exams In India | Latest Results | Notifications In India | Call Centers In India | Training Institutes In India | Job Communities In India | Courses In India | Jobs by Keyskills | Jobs by Functional Areas

Testing Articles | Testing Books | Testing Certifications | Testing FAQs | Testing Downloads | Testing Interview Questions | Testing Jobs | Testing Training Institutes

Gate Articles | Gate Books | Gate Colleges | Gate Downloads | Gate Faqs | Gate Jobs | Gate News | Gate Sample Papers | Gate Training Institutes

MBA Articles | MBA Books | MBA Case Studies | MBA Business Schools | MBA Current Affairs | MBA Downloads | MBA Events | MBA Notifications | MBA FAQs | MBA Jobs
MBA Job Consultants | MBA News | MBA Results | MBA Courses | MBA Sample Papers | MBA Interview Questions | MBA Training Institutes

GRE Articles | GRE Books | GRE Colleges | GRE Downloads | GRE Events | GRE FAQs | GRE News | GRE Training Institutes | GRE Sample Papers

IAS Articles | IAS Books | IAS Current Affairs | IAS Downloads | IAS Events | IAS FAQs | IAS News | IAS Notifications | IAS UPSC Jobs | IAS Previous Question Papers
IAS Results | IAS Sample Papers | IAS Interview Questions | IAS Training Institutes | IAS Toppers Interview

SAP Articles | SAP Books | SAP Certifications | SAP Companies | SAP Study Materials | SAP Events | SAP FAQs | SAP Jobs | SAP Job Consultants
SAP Links | SAP News | SAP Sample Papers | SAP Interview Questions | SAP Training Institutes |

Copyright ©2001-2023 Vyoms.com, All Rights Reserved.
Disclaimer: VYOMS.com has taken all reasonable steps to ensure that information on this site is authentic. Applicants are advised to research bonafides of advertisers independently. VYOMS.com shall not have any responsibility in this regard.