PowerShell for the DBA at SQLIntersection with @DBAduck

If you are wondering why you would want to use PowerShell as a DBA, read this first – Why Become a PowerShell DBA. Then go to SQLIntersection Registration to register and I will see you there. And if you register, please use the code MILLERB and you will get $50 off registration.

Click the links below to see the list of sessions, workshops and speakers
Sessions: http://bit.ly/SQL_Fall_2019_Sessions  
Workshops: http://bit.ly/SQL_Fall_2019_Workshops  
Speakers: http://bit.ly/SQL_Fall_2019_Speakers

Workshop

Leveling Up With PowerShell for the DBA

This workshop is a great one for anyone that wants to Level Up their PowerShell skills relating to DBA tasks. We are going to cover multiple things in this workshop. The tools we will be going over are: DBAtools module, SQLServer module, ImportExcel module. Each one of these has great power and can take a lot of clicking away. They also have the ability to allow you to export data to Excel or CSV and store for other purposes, or even take data from a query and put it directly in a table without having to write SQL or SSIS packages to put or import the data another way. Join me for a fun filled day that will have you coming back for more. It is not required to know PowerShell inside and out to attend. You will learn something new no matter your level of PowerShell expertise or years working with SQL and PowerShell. Join me in Vegas at the MGM for SQL Intersection, you won’t regret it.

An In-Depth Look at the SQL Server Provider in PowerShell

You probably already know that there is a SqlServer module on the PowerShell Gallery. This module contains many cmdlets that will help you with your DBA work. But something you may not know about is the SqlServer Provider that is in there too. This provider will be the subject of this presentation. Have you ever opened file explorer and started browsing through your filesystem? How about if you could have Object Explorer in SSMS as a navigable structure in PowerShell. Well you guessed it, it is. This is the SqlServer Provider. I will take you through the provider and show you how to navigate it, but that is not all. By the end of the session you will understand how you can use it in your PowerShell life to get more out of SqlServer by using Paths. Yes, you heard right, a path like structure that you can navigate and use to make your life simpler in retrieving objects and manipulating them in PowerShell. Join me for another chapter in the quest to become a PowerShell DBA. You won’t want to miss this one.

Synopsis: The SqlServer module is a powerful tool in PowerShell. It continues to receive updates and new cmdlets, which is fantastic. We will talk about these functions that allow you to do more with commands instead of TSQL. But the real value of this session is all about the SQLServer PowerShell Provider. This provider allows you to navigate SQL Server like a file system and that is not all, we will talk (and mostly show) what you can do with it and how you can leverage this provider to do almost anything that you would like to with objects and commands rather than clicking your way through. Join me for an in depth look at the provider and you will be converted to this tech, I am sure of it.

Getting Started with the DBAtools PowerShell Module

There has been a movement towards PowerShell in the SQL Server Community lately. With so much out there, how do you get ahead. I will take you through this massive module that has grown very quickly to over 300 functions for use in your daily DBA job. This module can single handedly migrate an entire server with one command, or you can choose the parts you want to do and just do those. Join me to find out how you can quickly leverage the power of this module. This is one tool you want in your PowerShell toolbox.

Synopsis: DBAtools has been around for 5 years and has finally reached release status. It is now in version 1.x which means it has stabilized the command names and parameter names to be consistent through all the commands. This remains very important because a lot of commands can be used to automate a lot of things. This gives you the ability to use splatting and parameter objects to allow you to reuse and get the most out of the module without typing a whole lot. Join me for a great look into some of the productivity tools in this module and you will be hungry for more.

Temporal Tables In-Depth

Have you ever wanted to know what the data looked like yesterday before a change was made? Have you cringed at the thought of creating triggers to get data changes logged for later analysis? Looking at a new feature in SQL 2016 called Temporal Tables gives you the ability to create a table that automatically keeps track of your data in time. Magic was never meant to be part of IT, but SQL Server has done a great job with this new feature. We will cover how to set it up, how it works, querying the tables including the history table and last but not least, how you can incorporate this feature into existing tables. Join me in a time travelling adventure to find out how you can leverage Temporal Tables in your world.

Synopsis: Temporal tables are not new, but there are still very many that have not used them yet. I will tell you there are many things that you can use them for, but recently we came across an update statement gone awry and had we had a temporal table in place, it would have been much simpler to just put the data back in place and not have to restore databases to do so. I will take you through the scenarios and show you how to put them in place, from a new table, an existing table, and a table that you want to customize your temporal tables indexes to allow you more ability to query the data and see the data at a given point in time. We will cover In Memory OLTP and new features in 2017 and 2019. Join me for a fun filled hour that may just make your head hurt.

Read More

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

PASS Summit 2014 – What to expect

When thinking about attending PASS Summit each year I don’t spend much time deciding. I have not missed a Summit yet and hope I won’t miss one in the future. This post is meant to help those that may be deciding whether or not to go or for those that have decided to go but really don’t know what to expect.

At PASS Summit there are many things to do. The list below resonates in my mind every year:

  • Networking
  • Learning from others
  • Finding those you have not seen and catch up with them
  • Meet new people, your favorite MVP or MCM or just a great figure to finally put a face to the name
  • Learn new things

Some of my favorite Summit experiences include meeting people like (not a definitive list) Kalen Delaney, Paul Randall, Kimberly Tripp, Brent Ozar, and the list could go on. Meeting these people for me was a treat and has made my PASS trips great to reconnect and chat with them and so many others to name off. The PASS Attendee parties are also one of the highlights because it was a chance to talk with peers and some of the famous ones in a setting that was not about database. You learn things like Kalen loves air hockey and some DBAs are really funny, but more than that, you can chat with the leaders of the PASS organization and board members.  These are times I will remember for a very long time.

First Timers

For you first timers, you are in for a treat. This is the first Summit for you and hopefully won’t be the last. For you there is finding and meeting people and speakers that you have seen for many years, maybe, and now they are there to shake hands with and chat with you. They are not untouchable, they are real. Friendly SQL Server Professionals that will actually be willing to chat with you.  There is the Expo where you can see products you have heard about for a while and actually see how they work.  It is NOT all sales and hype, there are actually some very good demos there.  You will always get some sales but this is your chance to get to know the vendors better.  Here are a list of things I would do as a first timer having been through many Summits and I could actually be a great first timer now. 🙂

  • Find out who is presenting and mark the ones that you MUST see and then those that you would like to if the opportunity presents itself
  • BUY THE RECORDINGS, no matter how good of a time manager you are, you will inevitably get overwhelmed with the amount of stuff that is out there and the people who are there, this will give you the opportunity to watch the recorded sessions you missed because you are networking.
  • Did I already mention buying the recordings before the show?
  • Make time for the Community Lounges, SQLCAT Support Center and anything that you cannot get online or in a presentation. Get in front of people and BRING BUSINESS CARDS to give out so that people get to know who you are and they may give you their contact information. BUILD A NETWORK.
  • Go to the MUST see presentations since education is still a huge part of the Summit
  • Attend the Keynotes or watch them, sometimes they surprise you with announcements you won’t get anywhere else.
  • Get to know the Board of Directors of PASS. Hold them accountable as they lead the organization that you are a member of.
  • Investigate opportunities to volunteer for PASS or one day run for the Board.  Passionate people is what makes PASS great, remembering that passion begins with PASS. 🙂
  • Attend the online First Timers presentations that are out there.  I believe there are many but these are great events. I believe that there is even one now onsite at the PASS conference. Get to know what is there for that year.
  • Find the SQL MVPs, they are out there.  These volunteers in the SQL Community have passion towards SQL Server and are always willing to share.
  • Find out who the Microsoft Players are and their role and network with them.
  • Many more things that you can do to enjoy and always want to come back for another Summit.

Preparing for your next experience today

If you begin with the next year in mind, set up the relationships that you will renew each year at the Summit and you will find yourself refreshed and renewed each Summit you attend. Do things to keep in touch with people you meet, like getting their contact information, giving yours out to set up collaboration during the year you are away. I keep all the cards I receive in a place in my backpack and it is amazing how many I have at the end of Summit. Obviously this is not an opportunity for free consulting, but to get a quick opinion or a quick answer to a question, your network is your best friend. The next thing you should do to complete your experience is when you leave Summit, look for the email where registration opens for the next Summit and most often it is a very significant discount to register before the year ends than to wait until the next year. Save some money.  Finally, get to know the players and you will be playing for the rest of your career.

Valuable Lessons Learned

The most valuable lessons I have learned at my PASS Summits I have attended is that people at PASS Summits are real people. They have lives outside of their career and they have failings just like me. I don’t know everything and they don’t know everything, that is why we get together and chat with each other because they share what they have learned and experienced and I do the same, and we come away better for it. The people you meet at PASS and the leaders that lead PASS are just like you and me, they are not better than you or me and we are not better than them, we have just had different experiences and that is what makes a PASS Summit FUN. Swapping stories, learning new things and renewing the PASSion that you have gained for SQL Server and the technologies that surround it.  Chatting with the Microsoft folks and the MCM and MVP crowd can enlighten you in an environment that cannot be replicated at work, take advantage of this opportunity, you won’t regret the time you spend.  But most of all Unwind, HAVE FUN and take it all in, you will be exhausted when you get done, but it will be an experience second to none.

I hope to see you there and hope your experience will be well worth the time. Here’s to all the SQL Community or #SQLFamily out there that will be attending, can’t wait to see you there.

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
Menu