Day 15 Introduction to Powershell for MS Sql Server
So far we have discussed several features and information about Windows Powershell, how it works and how powerful it is. I am impressed the way it is handling the stuff… you must be feeling the same right? Okay, Now onwards I will be discussing Powershell with Microsoft Sql Server (I will refer Sql or Sql Server), as we are almost half our time reach of our A Day with Powershell series (April Month) discussing about windows – which was very important information and necessary to know Powershell.
As we discussed that with Powershell we can do one thing with different ways with the help of several components. With Powershell we can implement almost everything. Powershell is Shell +Scripting Language. Following are some of the components /members of Powershell.
- .Net Framework
I am as a Sql Server Developer/DBA and Powershell is very important for Sql server developer/DBA as well. Powershell is develop completely on .Net Framework. So everything of .net component can be used here. As a scripting with Powershell we can script something and implement the same at different location at any number of time without any error ,which is not easy with any GUI.
Sql server is a very powerful RDBMS these days. And used for enterprise platform and capable of handling big mission critical system. With sql server 2008 Powershell 2 is build it. No need to install it explicitly(same for Windows 2008 onwards). At sql server 2008 onwards we can run the Powershell script through Sql server Agent job. Which can be scheduled… cool.
Compatibility: Powershell is developed in late 2006, and used mostly for windows administration. It was so powerful that it can also incorporated in sql server 2008. But it can able to handle the instances of sql server 2000 onwards – with features they support. But as it is introduced in sql server 2008 you must have to use Sql server 2008 client version to access earlier version of sql server
Why Powershell for Sql Server:-
- Error Free implementation of task from one system to another (Migration).
- With scripting, easy to implement and light weighted (no GUI)
- Script can be use for multiple locations and can be use for future reference.
- Integrate Windows programming and Sql server Coding efficiently to achieve the any requirement(if any) – (Processes/Services/Registry/ADSI/Perfmon/Eventvwr)
- Future Centralized Language for Windows programming – includes Applications/Hardware.
- It’s build in with Sql server 2008(or Windows 2008) onwards- means it’s going play a very strong role in development –Mini shell.
- It’s very easy to learn and expandable (snapins) as per our requirement.
- Growing very rapidly with snapins from different big companies like (quest/Idera) including features like intellisence and ADSI plugins.
- Scripting has been a powerful tool for Unix administrators for a long time. Windows administrators have had fewer and less capable options for automating administrative processes until the introduction of PowerShell.
okay. Here you go. What do we do to open Powershell,
are we are done…. We can work with Powershell Script.
To work with Sql server there are several ways.
- 1. SQLPS – Mini Shell:
Can also open it using management studio, select any object and right click –will have an option to “Start Powershell” which will open the sqlps at directory location which you select.
No snapins are allowed
- 2. . Net Assembly.
To access the sql server load the .net Assembly
SMO and ADO.net
SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server. SQL Server Replication Management Objects (RMO) is a collection of objects that encapsulates SQL Server replication management.
Some of the smo’s are as follows
And once you registered the Assembly you can create an object to access sql server with that object.
Ps C:\>[system.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.sqlserver.smo’) |out-null
Ps C:\>$server=new-object (‘Microsoft.sqlserver.management.smo.server’) MSSqlSqlserver
Here Sqlserver is the default instance you can specify the sql instance could be named instance [hostname\instnacename]
You are done. You can use the $server object to retrieve the sql related command.
To get the member of the object $server
Snap is is like adding external dll /objects /cmdlets to programming to extent the existing functionality for easy to use.
4.Use SqlAgent job to run Powershell
We can use sql agent job’s steps to run the Powershell script.
$m = New-Object (‘Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer’)
Use WMI object to integrate sql server with wmi.
We will discuss more in detail on each of these in our next blogs.