This article first appeared in the SQLServerGeeks Magazine.
Author: Ben Miller
Subscribe to get your copy.
Initial Thoughts
Working as a DBA you are most often faced with multiple instances and multiple databases. Your job is to ensure that the database servers stay running and the databases are accessible. The tools that are in the trade of DBA consist of SQL Server Management Studio (SSMS), Azure Data Studio (ADS) or if you are a command line person, you could use sqlcmd at times. If you are new to being a DBA, PowerShell is becoming a standard tool as well. Some of us old ones are embracing PowerShell as a key tool in our toolbelts. Now is the time to ensure that you have the right tools and know how to use them. This article will illustrate some of the reasons and ways to use PowerShell as a DBA to make life just a little more enjoyable and your work a little more scalable.
PowerShell Introduction
When you are thinking of PowerShell, what is the first thing you think of? Windows Administration. Another language you need to learn, or are you thinking, I am glad this tool can help me be a DBA? I can tell you if the first 2 are how you think, it may be a challenge to get out of that mindset. But if you are thinking of the 3rd item, you may have a chance to get things moving in the right direction. PowerShell is a tool that Microsoft created for Windows Administration first, and we can use it as DBAs because it is built upon the .NET Framework which means that anything in .NET, we can use in PowerShell. In fact, SMO (Shared Management Objects) are built using .NET so the library and all her objects can be used in PowerShell to help administer the databases and their servers. Think of all the things you can do with SMO that will help you get more for your time. Think of the projects out there that leverage this set of libraries to do work for us, to give us a command instead of writing lots of code when we want to do something. DBAtools, an opensource module, is one of those tools we use as DBAs, as well as the SqlServer module from Microsoft. Both have a foundation in SMO and give you a command-based toolset that will let you get information as well as change objects in the databases and servers.
Database Administration Concepts
There are concepts as a DBA that are fundamental to our daily work. Some of them include:
• Growing Data or Log Files
• Changing Settings on Databases
These are only a few that are core to our work, but there are many more as well. Each will be addressed and some others may be introduced as well. Take a look at each section below to see how these concepts are addressed using PowerShell as the tool. They may be combined with DBAtools to underscore the point.
Some Background First on SMO
In PowerShell you get some default objects that are loaded when PowerShell starts. SMO is not one of them. The trick today to loading SMO is to use a Module to do it. It used to be that when you installed SSMS it installed SMO in the GAC (Global Assembly Cache c:\windows\assembly), but now it does not, it is all embedded in the install and gets put in the folder that SSMS uses, therefore is not necessarily as easy as
[System.Assembly.Reflection]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)
You now have to have the assembly where you can reference it. So you can either reference the assemblies in the SSMS space (C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE) and do:
PS:>Add-Type -Path “C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Microsoft.SqlServer.Smo.dll” PS:>Add-Type -Path “C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Microsoft.SqlServer.Smo.Extended.dll” PS:>Add-Type -Path “C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Microsoft.SqlServer.SqlEnum.dll”
and any others in that directory that you may need.
The other way is to use the modern modules DBAtools or SqlServer. If you don’t have these installed and you have PowerShell 5.1 installed (or are on Windows 10, because 5.1 is native there) you can do the following:
PS:>Install-Module SqlServer -AllowClobber PS:>Install-Module dbatools
Note: The reason you would specify -AllowClobber is if you have SQL Server installed on that server because it has SQLPS module that it installs for compatibility with SQLAgent for now.
Or if you already have them installed and want to update them (if you have both installed, otherwise just specify the one you have installed):
PS:>Update-Module SqlServer, dbatools
These modules have the SMO libraries embedded and will side load them when the module is imported using Import-Module dbatools. Or the same thing with the SqlServer module
Growing Data or Log Files
A database is made up of 1 or more Data Files and at least 1 log file and hopefully only 1 log file. There is a setting on a database to allow your files to auto grow, but we really don’t want that to happen if we can help it. This is how it is done a couple of different ways. The first way will be in SMO and the second will be via DBAtools where there are commands available to accomplish it.
Scenario 1
I have a database named Database1 on SqlServer1 and it has 2 files, 1 is a data file with the internal name being “Database1_Data” and a log file with the internal name “Database1_log”. I need to grow the Data File by 1GB and I need to grow the Log file by 512MB.
SMO Solution
First you will need to ensure that you follow the Background on SMO first. Then you will proceed with creating objects. You will see that using a module like dbatools or SqlServer will be of great help, since they can return SMO objects to you that save you some steps. But for now I will show you the SMO way (each command should be on its own line.
PS:> $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList SqlServer1 PS:> $db = $server.Databases[“Database1”] PS:> $file = $db.Filegroups[“PRIMARY”].Files[“Database1_Data”] PS:> $file.Size += (1MB)
PowerShell Module Solution
We are going to use the dbatools module to accomplish the same thing. One thing to note is that dbatools does not have a command to expand a data file, just a log file, but we will use a combination to expand a data file. (Look closely and you will see it is pretty close to the SMO version)
PS:> Import-Module dbatools PS:> $db = Get-DbaDatabase -SqlInstance SqlServer1 -Database Database1 PS:> $file = $db.Filegroups[“PRIMARY”].Files[“Database1_Data”] PS:> $file.Size += (1MB)
And the Log File gets better, except it is harder in this case. The Expand-DbaDbLogFile expects a TargetLogSize instead of just an increment that you want to add to it, so you have to go through more steps.
PS:> Import-Module dbatools PS:> $db = Get-DbaDatabase -SqlInstance SqlServer1 -Database Database1 PS:> Expand-DbaDbLogFile -SqlInstance SqlServer1 -Database Database1 -TargetLogSize ($db.LogFiles[0].Size+(512KB)) -Confirm:$false
Changing Database Settings
Setting some database properties can be done simply as well. The two properties are Recovery Model and the Compatibility Level. There are many more properties, but I will focus on these two. Recovery Model will change to “FULL” and the Compatibility Level will change to 140 (SQL 2017).
SMO Solution
This will look familiar in SMO as these types of changes basically stay the same.
PS:> $db = Get-DbaDatabase -SqlInstance SqlServer1 -Database Database1 PS:> $db.CompatibilityLevel = “Version140” PS:> $db.Alter()
PowerShell Module Solution
I will use the dbatools module for this solution as well
PS:> Import-Module dbatools PS:> Set-DbaDbRecoveryModel -SqlInstance SqlServer1 -Database Database1 -Compatibility Version140
SMO Solution
Let us change the Recovery Model to FULL now for the database.
PS: > $db = Get-DbaDatabase -SqlInstance SqlServer1 -Database Database1 PS: > $db.RecoveryModel = “FULL” PS: > $db.Alter()
PowerShell Module Solution
This solution will use the dbatools module.
PS: > Set-DbaDbRecoveryModel -SqlInstance SqlServer1-Database Database1 -RecoveryModel FULL
Summary
Performing your DBA duties can be done a few ways, even using SSMS, but the point of this article is to show that you can use PowerShell to change database settings or even using modules to make them simpler. PowerShell can do much more than you see here, but this should be a good taste of what you can do. Join me in the quest to become a PowerShell DBA.
This article first appeared in the SQLServerGeeks Magazine.
Author: Ben Miller
Subscribe to get your copy.
