PowerShell

dbatools 1.0 is here and why you should care!

I have been using dbatools for a long time now. I have even contributed a few commands and fixed some bugs.  I present on this topic and help many people with PowerShell and being a DBA.  I am so excited for the launch of 1.0 for dbatools, that I cannot stop pinching myself to make sure that it is true.

Chrissy Lemaire ( b | t ) and company have done a great job keeping this project going as well as supporting the sqlcommunity.slack.com slack environment. Many people are helped in there and it is a great sense of community being fostered there.  I take my hat off to Chrissy for spearheading this project and for inspiring all of us to contribute and use the module in our daily lives.

Experience

I wanted to also share that in my presentations, I show the things that dbatools can do, and I help people get started and hope that they get something out of it.  I recently had a presentation on Complete Database Migration with dbatools and it went across so well, I was inspired again.  Then that same week I was helping a client to get things set up on a new server and it was late at night and I needed to recreate a credential and Operator and Proxy for a job that would run as the proxy.  I scoured emails and looked and looked.  I could not find the password and assumed that the password was sent another way other than email.  Then the light bulb came on.  “I can use dbatools, I thought”.  So I downloaded the latest version to ensure that I was using the best, and proceeded to use Copy-DbaCredential and Copy-DbaAgentOperator and Copy-DbaAgentProxy as well as Copy-DbaDbMail.  They all worked and I could configure everything. Then I used Copy-DbaAgentJob to cap it all off.  I cannot tell you how nice it was to use the tools I had just shown an audience not 2 days earlier.

I hope that everyone is able to get something out of this module and that your day is much better than days before because you have found dbatools.  I again tip my hat to all those that work behind the scenes on building, testing and publishing this module for all of us to use.

Happy Launch Day!!!

Read More

Get SQL Server Service Accounts with WMI

Today I was tasked with retrieving the SQL Server Service accounts to validate them in Active Directory.  I wanted a simple way to get them and show the account names for each SQL Server service.  I knew that you could get the service in PowerShell with Get-Service.  I also new that PowerShell could get the service with Get-WmiObject –class Win32_Service. But neither one had the ServiceAccount property.

So I turned to WMI under SMO with PowerShell.  The script below has the details of a script I named GetSqlServices_WMI with a parameter of $sqlserver.

param (
	[string]$sqlserver
)

# Call this with .filename.ps1 "servername"

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")
$wmi = New-Object ("Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer") $sqlserver
$wmi.Services | Select DisplayName, Type, StartMode, ServiceState, ServiceAccount | ft -auto

The call is in the code.  The results will have DisplayName, Type, StartMode, ServiceState and ServiceAccount listed in a table with column headers.

Great way to interrogate the Services from SQL Server.

Available Properties to you are as follows:

  • Parent
  • AcceptsPause
  • AcceptsStop
  • Description
  • DisplayName
  • ErrorControl
  • ExitCode
  • PathName
  • ProcessId
  • ServiceAccount
  • StartMode
  • Type
  • IsHadrEnabled (because I am using 11.0 SMO)
  • StartupParameters
  • Dependencies
  • AdvancedProperties
  • Urn
  • Name
  • Properties
  • UserData
  • State

With Methods

  • Alter
  • ChangeHadrServiceSetting (because I am using 11.0 SMO)
  • ChangePassword
  • Equals
  • GetHashCode
  • GetType
  • Initialize
  • Pause
  • Refresh
  • REsume
  • SetServiceAccount (handy little guy)
  • Start
  • STop
  • ToString
  • Validate

The link to the documentation for this object is here (Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer).

Enjoy and happy scripting.

@DBAduck

Read More

SMO and Index Fragmentation

When working with SMO and Indexes there are times that you want to get the fragmentation of this particular index.  In SMO there is a method on the Index called EnumFragmentation.  When you call this method, there is a bug in SMO out on Connect (SMO Index Enum Fragmentation…) that you should vote for.  This is a very impactful bug as this is the only way in SMO to get the Fragmenation information on an Index.

The bug is this.  When you call EnumFragmentation() you get the entire Database fragmentation by index instead of for the Index that you are calling it from.  Normally what you would expect is to see

select *
from sys.dm_db_index_physical_stats(9, 39399393, NULL, NULL, 'SAMPLED')

Instead you see

select *
from sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'LIMITED')

This is crazy as you only get a limited set, which I know you can change with the method above by specifying the type of Detail (‘DETAILED’, ‘SAMPLED’, ‘LIMITED’) but it defaults to LIMITED.

Vote up the bug if this is something that affects you and your work.

Read More
Menu