Year: 2019

PowerShell at #SQLSatOregon with @DBAduck

Mastery Session – Leveling Up with PowerShell for the DBA

I know that most of you have used or heard about using PowerShell, but I am sure that you have things you could learn about using PowerShell in your DBA career. This mastery session is meant to get you closer to mastery of PowerShell for the DBA. I will cover 3 modules: DBAtools module, SQLServer module and the ImportExcel module. In these modules you have a wealth of commands and tools that will help you get more out of your day, using less clicks. The trick is not knowing that these things exist, but understanding the possiblities and patterns that exist in each module. You will learn about the commonalities in the DBAtools module that make it very simple to get the most out of this one. You will learn about some hidden gems in the SQLServer module and why you should care to learn everything you can about it. The ImportExcel module does not have a lot of commands but the commands available are amazing and will allow you to do things very simply without requiring Excel. There will also be a mix of deployment options using PowerShell as well. SSIS project deployment will also be covered. Join me at SQL Saturday Oregon and learn what your next steps are with PowerShell. You will absolutely love it.

Tips and Tricks for the PowerShell DBA

With environments growing and people hiring is lessening, you have to get some tools in your toolbelt to get you ahead of the game. These tips will cover both PowerShell and SQLServer PowerShell tricks. Some of the tips are directly related to performance and others about using features of SMO/SQLServer to get things done faster and more efficiently. Join me for a great session on getting things done.

Register Now for SQL Saturday Oregon

Read More

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

Connecting iSCSI LUNs with PowerShell

When clustering SQL on Windows Server Core, I had the need to configure the ISCSI LUNs to the server without UI.  I know that there is a UI component called iscsicpl.exe that you can run and it will pop up the simple Windows UI to allow you to click through all the configuration, but I wanted to go purist on this one and learn the nuances of configuration via PowerShell the whole way through.

First you would have a SAN LUN configured for iSCSI which would have a IQN like iqn.1991-05.com.microsoft:winsan-winsql1-target. This is a unique identifier for the iSCSI resource and this is what you would connect to for the drive.  To make this connection you would use the iSCSI module that has the commands you will see.  Below is the example of the connection.


# On Client Computer
$TargetPortal1 = "10.1.1.1"
$TargetPortal2 = "10.1.2.1"
$ISCSIIP1 = "10.1.1.2"
$ISCSIIP2 = "10.1.2.2"

New-IscsiTargetPortal -TargetPortalAddress $TargetPortal1 -InitiatorPortalAddress $ISCSIIP1 
New-IscsiTargetPortal -TargetPortalAddress $TargetPortal2 -InitiatorPortalAddress $ISCSIIP2

ClientISCSI_1

Figure 1.1 – The same as New Discovery Portal in iscsicpl

Start-Sleep -seconds 2

Connect-IscsiTarget -NodeAddress "iqn.1991-05.com.microsoft:winsan-winsql1-target" `
	-IsPersistent $true -IsMultipathEnabled $true -TargetPortalAddress $TargetPortal1 `
	-InitiatorPortalAddress $ISCSIIP1

Connect-IscsiTarget -NodeAddress "iqn.1991-05.com.microsoft:winsan-winsql1-target" `
	-IsPersistent $true -IsMultipathEnabled $true -TargetPortalAddress $TargetPortal2 `
	-InitiatorPortalAddress $ISCSIIP2

ClientISCSI_2

Figure 1.2 – Connecting with MultiPath IO using 2 different Targets and Initiators

Now you have a drive attached, at least in iSCSI.  This drive will be a raw drive and using Get-Disk  will show them. This cmdlet is in the Storage  module in PowerShell.

ClientISCSI_Get-Disk

Figure 1.3 – Get-Disk in action. Showing the newly attached drive

The next task is to online and configure the new drive so that you can use it. You need to initialize the disk, then create a partition of type GPT and finally format the volume with 64KB blocks for use with SQL Server.


Initialize-Disk -Number 1 -PartitionStyle GPT # Data

New-Partition -DiskNumber 1 -UseMaximumSize -DriveLetter E 

ClientISCSI_Initialize-Disk_New-Paritition

Figure 1.4 – Initialize-Disk and New-Partition to make the drive accessible


Get-Volume -DriveLetter E | 
         Format-Volume -FileSystemLabel DATA -AllocationUnitSize (64KB) -FileSystem NTFS `
                -ShortFileNameSupport $false

ClientISCSI_Get-Volume_Format-Volume

Figure 1.5 – Get-Volume and Format-Volume to finish setting up the drive

This concludes this part of the creation of a drive from the iSCSI side of the house.  The entire piece of code is available here.

Happy PowerShelling. Onward in the quest to become a SQL PowerShell DBA.

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

Why Become a PowerShell DBA

Powershell, SMO

If you find this article valuable and you decide that you want to get your learn on with PowerShell and SQL Server, there is a SQLSkills class that I am teaching this year, please join me to get a great start in how to get your toolset growing by adding PowerShell to your toolbelt. Early Bird discount still available until March 10, 2017.

I have heard many times from many people that it is not worth learning PowerShell as a DBA.  I have been using PowerShell since v1.0 and have found it to be amazingly rewarding. I will illustrate why this is the case in this entry and hopefully some of it will resonate with you and you will find some worth in learning at least a little PowerShell for your DBA job.

First, PowerShell was built to manage Windows Servers and soon manage some part of Linux servers as DotNet Core comes into play.  Back in the day, if you were a DBA and wanted to manage SQL Servers, you used a lot of SMO (Shared Management Objects) and that meant programming in C# or pseudo C# in PowerShell. Creating objects, managing properties, etc.. That was not for everyone I know. I have a developer background and an IT background so managing servers and Active Directory and building programs to do things was part of my world a long time ago.  But what I found was that there were not a lot of tools in the marketplace for doing things against SQL Server with PowerShell then so it was not widely adopted by DBAs.

In the SQL Server 2008 days, Microsoft wrote some PowerShell Snapins that would give us access to some cmdlets (Commands) in PowerShell to manage some things and introduced a PowerShell Provider for SQL Server that turned your SQL Server into a series of Paths (SQLSERVER:\sql\servername\instancename) and that was pretty powerful, at least to a guy like me, looking to automate many repetitive tasks. With those cmdlets and the provider there began to be a way to now use PowerShell to get at SQL Server without writing .NET code. It certainly did not cover the breadth of the product like Exchange had done with their cmdlets, but it was a start of some love. This is where the dawn of becoming a PowerShell DBA began.

Now Why Investigate

For a DBA there are many times you want to know information about your server, and there is plenty of TSQL to get that information. You need to know the DMVs or the system tables to get at that information and for a fairly new DBA, that can be a real challenge. Here is where the paradigm begins. In SMO there are objects (methods, properties) that encapsulate the properties of the objects in SQL Server into a class (Object Oriented term). These classes represent the objects by having the appropriate methods and properties to act on those objects.  Let me illustrate.

We all know that there is a Database object in SQL Server and there are many TSQL commands that we can use to find out information such as…

SELECT * FROM sys.databases

SELECT * FROM sys.database_files

and many more….

It is a common practice to create a set of TSQL scripts and take them with you as you go. That is one of the values of longevity in the industry is what you have created and built up.  There is no argument from me for this mentality. This is where you parallel with the PowerShell mentality in that you can reuse the scripts later on and not have to remember or rebuild them on the fly. But is there a better way? Maybe, but at least there is “another” way.

Enter PowerShell

PowerShell in and of itself is a mechanism to do many things. I advocate DBAs learn it because of the way you can change your job by using Automation, Scripting and Information Gathering that is much different than running TSQL Scripts and copy-pasting them into documents or tables. (or even Excel)  I will take you through a couple of scenarios to give you a flavor of an additional tool (not advocating replacing SSMS or any other tool with PowerShell, only making you more efficient) with examples.

Say I want to have a list of databases with their name and recovery model.

TSQL

SELECT name, recovery_model FROM sys.databases;

PowerShell

Get-SqlDatabase –ServerInstance servername  | Select Name, RecoveryModel | Format-Table * –AutoSize

We won’t really go into how you get to be able to run these commands, just to compare the commands. They are not much different than each other, one is run in SSMS or sqlcmd and one is run in PowerShell console or ISE or another tool that will execute PowerShell.  The results are pretty similar as well.

sys_databases

PowerShell-Get-SqlDatabase

Now that doesn’t look too scary. You get the same results.  Here is how you would get it with the provider.

PowerShell-Get-SqlDatabase-Provider

Notice that I used a Path-Like structure to get to the Databases in the Provider.  I used the –Force parameter to ensure that I got the System Databases. Now that seems the same, other than the tool. So you ask the question, why do I do it?  Why do I learn PowerShell to be able to get the same information?  Here is the answer.

Objects are the answer

In TSQL you get results in a Grid or Text, you copy and paste or you just look at it and read it. You just get some text back either in PowerShell or TSQL or so it seems.  In PowerShell everything is an object and they all have (potentially) methods and properties.  Let me illustrate something that is an interesting part of PowerShell and objects.

Say I want to change the recovery model to Full on my DBA database.  in TSQL I could do it in a couple of ways and it would look something like this.

ALTER DATABASE databasename SET RECOVERY FULL

Let’s take a look at how you do it with PowerShell and why it would be valuable to get this tool in your toolbelt.

PowerShell-AlterDatabase

What you see is the retrieving of the Database object in an SMO object (the provider always returns an SMO object) and there is a Property called RecoveryModel that tells me which recovery mode it is. To set it, the property is read/write so I can change it with the words “FULL”, “SIMPLE” or “BULKLOGGED” and then I use the method Alter() as above.  The methods have the double parenthesis () after the method name and I could have inserted ($true) in the parenthesis to do a WITH ROLLBACK IMMEDIATE like I would do if I were changing a property that required full access.  Now that looks like it may not be very advantageous, but imagine the next scenario.

PowerShell-Function

I create a function to change my recovery model.  I can use parameters for my SQL Server instance and the database and the RecoveryModel.  Now I have a tool that does not require SSMS to be installed and allows me to call a function to change my recovery model.  Once this is written and saved (and even put in a module for easy loading), and loaded into my environment, I can use it pretty easily for any database on any server.

Like I said before, this is contrived and works because I have SMO loaded, which is not hard to do, but this entry is all about why, not how to get things loaded.  I have a SQL PowerShell Stairway at SqlServerCentral.com that you can go and read to help you get that part worked out.  Not hard but not the reason for this post.

Join me in the quest to become a PowerShell DBA. Stay tuned for more on this site and join me in a class soon.

Read More
Menu