Showing posts with label Powershell. Show all posts
Showing posts with label Powershell. Show all posts

Tuesday, 7 June 2016

Navigating a SQL Server Instance with PowerShell


As well as navigating a folder structure with commands such as get-childitem, and set-location, PowerShell can also be used to navigate the SQL Server object hierarchy of an instance. You can connect PowerShell to the SQL Server database engine provider by using set-location to navigate to SQLSERVER:\SQL The information returned by get-childitem is dependent on the current location of the object hierarchy. The table below shows what information is returned from each level of the hierarchy.

Location
Information Returned
SQLSERVER:\SQL
The name of the local machine
SQLSERVER:\SQL\ComputerName  
The names of the database engine instances installed on the local machine
SQLSERVER:\SQL\ComputerName\InstanceName
Instance level object types
Lower levels
Object types or objects contained within the current location

Once you have navigated to the level of the hierarchy, in which you are interested, you are able to use PowerShell to perform basic operations against objects at that level. For example, the script below will navigate to the "tables" namespace within the AdventureWorks2016 database and rename the dbo.DatabaseLog table, to dbo.DatabaseLogPS. The dir commands will display the original name and new name of the table.


sl SQLSERVER:\SQL\ESASSMgmt1\MASTERSERVER\DATABASES\ADVENTUREWORKS2016\TABLES

dir | where{$_.name -like "*DatabaseLog*"}

rename-item -LiteralPath dbo.DatabaseLog -NewName DatabaseLogPS

dir | where{$_.name -like "*DatabaseLog*"}

More complex tasks that do not have a specific cmdlet, associated with them can be performed by using the invoke-sqlcmd cmdlet. 

You can also start PowerShell from within SSMS. This is done by selecting "Start PowerShell" from the context menu of an object folder, within Object Explorer. This will cause the PowerShell CLI to be invoked, with the initial location being set to the object folder that you used to invoke the CLI.

My new book, Expert Scripting and Automation for SQL Server DBAs contains extensive details and examples of how to use PowerShell to administer SQL Server. The book is available from Apress at www.apress.com/9781484219423?gtmf=s or from Amazon, at www.amazon.com/Expert-Scripting-Automation-Server-DBAs/dp/1484219422?ie=UTF8&keywords=expert%20scripting%20and%20automation&qid=1465300485&ref_=sr_1_1&sr=8-1

Monday, 6 June 2016

Expert Scripting and Automation for SQL Server DBAs

My new book "Expert Scripting and Automation for SQL Server DBAs" is now available to pre-order on Amazon and Apress.com




The link for Apress.com is www.apress.com/9781484219423?gtmf=s

It is worth noting that the expected release date on Amazon is not correct. Publication is actually expected in August.

Sunday, 19 December 2010

Managing policies with PowerShell

Whilst Powershell is undoubtedly an incredibly powerful tool, in SQL Server 2008, Microsoft did not seem to get a chance to finish off the help files, and although it is included to allow you to automate the management of policies, there seems to be very little written on the web about how to do this, so let me try and unravel a few mysteries for you…
Firstly, lets assume that we have three policies stored in the file system. They are in a folder named c:\policies and are called MyPolicy1.xml, MyPolicy2.xml and MyPolicy3.xml.
Now assume that you want to evaluate both of these policies against your sever, but you also want to enforce the policies at the same time. From within PowerShell, the first thing that we need to do is navigate to our folder in the file system…
sl "c:\policies\"
Now we are in the correct location, we need to evaluate them, but to enforce the policies, we will also set an ad-hoc evaluation mode…
 
invoke-policyevaluation -policy "name of pol.xml", "name of pol2.xml" -adhocpolicyevaluationmode "configure"
That is useful, but more than likely, or policies will not be stored in the file system, they will be stored in our SQL Server instance, and if you have many policy objects, you may also be taking advantage of policy categories. So lets navigate back to our Policy Store in SQL…
 
sl sqlserver:\sqlpolicy\ny-srv-01\default\policies
Now for the slightly more complex statement. Here, we are going to use | as a for loop, to loop through the policies stored in a category called MyCategory1. We are also going to use > to send the results out to a xml file, so that we have an audit, rather than having to view the results manually…
 
get-childitem | where-object {$_.policycategory -eq "new2" } | invoke-policyevaluation -targetserver "ny-srv-01" -outputxml > "c:\policies\policyeval.xml“
This is a simple, but classic example of combining PowerShell with SMO & Policies. If you want you can go much further than this, and on CodePlex, you will find a community project called the EPM Framework, which uses PowerShell in conjunction with Reporting Services to produce a fantastic compliance tool. http://epmframework.codeplex.com/

Find my book, Pro SQL Server Administration on Amazon -