Measuring Your DBA Skills

Over the last 9 weeks I took you on a journey of skills and career topics related to being a SQL Server DBA. We looked at the Production DBA. We saw skills and career topics from the beginning to mid-career to Senior DBA. Then we looked at the Development DBA and their skills and career development needs. Finally there was a wrap up post.

To make it easier for everyone to get to these posts, I decided to bring them all together on a single page.

measuring your skills

 Production DBA Skills Years 0-2

Production DBA Skills Years 2-4

Production DBA Skills Years 4-7

Senior Production DBA Skills Years 7+

Development DBA Skills Years 0-2

Development DBA Skills Years 2-4

Development DBA Skills Years 4-7

Senior Development DBA Skills Years 7+

Series wrap-up

If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

Insufficient System Memory – Failed Allocate Pages

 

Unable to Start SQL Server

In my own local SQL Server I ran across a problem starting the SQL instance. I went to SQL Server configuration Manager and manually started the SQL Server instance. The UI showed the instance had started. I opened SSMS and tried to connect. And I waited, waited and waited some more until it didn’t connect and threw an error.

How To Locate the SQL Server Error Log

I knew I could look at the SQL Server Error Log to get more information on what had gone wrong.  I browsed to the directory where the log file is. For any system that is found by going to the drive where the binaries have been installed and go to \Program Files\Microsoft SQL Server\SQLServerVersionNumber.InstanceName\MSSQL\Log. In that location you will look for files like Errorlog, errorlog.1 through errorlog.6 The file with no number at the end is the most recent Error Log. Double click it and open in your favorite viewer. As I read through the file, I came across the below.

Insufficient System Memory – Failed Allocate Pages

 

Failed Allocate Pages - Insufficient System Memory

Also, you can read the SQL Server Error Log using the extended stored procedure xp_readerrorlog. Here is an example that shows the memory related messages from the SQL Server Error log. Of course, you can only do that once your SQL Server is running.

xp_readerrorlog 0, 1, "memory"

Then the next thing was, how do I investigate this since I can’t start the SQL Server?

Startup Parameters for SQL Server

SQL Server has startup parameters that can be added to the SQL Server Configuration Manager to control what happens when SQL Server starts. I was looking for something that would help me get SQL Server started and let me poke around. At first I tried -m for single user mode but SQL Server still wouldn’t start. Then I found the -f parameter and it sounded like just what I needed.

“Starts an instance of SQL Server with minimal configuration. This is useful if the setting of a configuration value (for example, over-committing memory) has prevented the server from starting. Starting SQL Server in minimal configuration mode places SQL Server in single-user mode. For more information, see the description for -m that follows.”

Now that I had that information I opened SQL Server Configuration Manager. I located the SQL Server instance I couldn’t start and right clicked it. I then chose properties and typed -f in the Startup Parameters tab.

I then clicked Add to the right of the startup parameter I just added. I clock Ok and I’m prompted that I need to restart the SQL Server service to make this change effective. So, I restart SQL Server and attempt my connection again, and I’m in!

SQL Server Configuration Manager Startup Parameters

 

Because this was an insufficient memory error, I right clicked the name of my SQL Server instance and selected Properties then Memory. I see I only have 1024 MB assigned to the SQL instance for Max Server Memory. I increased it to 3072 MB and clicked Ok.

I went back to SQL Server Configuration Manager and removed the -f startup parameter and restarted SQL Server. Now I am able to start up SQL Server and connect with no issues.

What To Do Next

  1. Go back to the link on SQL Server Startup Parameters and familiarize yourself with what is in there. You never know when you might need functionality from a startup parameter to get you past a problem.
  2. Do some research on Max Server Memory.
  3. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

 

How Do I Measure My DBA Skills Part 9

Production and Development DBA Skills and Things We Didn’t Touch On

We’ve covered a lot of ground for Production and Development DBAs in terms of the skills and applicable career topics. We’ve mentioned necessary skills with T-SQL, backup/restore, automation, PowerShell, troubleshooting, SSIS, SSRS, patching, Query Store, HA/DR, leadership and emotional intelligence. That’s quite a list! However, looking back at the series, there are still a number of things that weren’t mentioned.

Other skills and career topics not discussed along the way:

  1. OS skills – This once used to be just Window skills. However, with SQL Server on Linux, and depending on what is in use in your environment, you may need to learn the basics of the Linux platform as well as Windows.
  2. I also didn’t touch on the topic of SQL Server Wait Statistics. This is a tried and true method for diagnosing server level issues that can lead to further investigation about what ails your SQL Server.
  3. What about the cloud and virtualization? Azure, AWS, Google Cloud Platform, VMware, Hyper-V. These are all virtualization platforms that are in use these days, so you may encounter these and need to know, or might want to know, something about how they work.
  4. What about certifications? Are they worthwhile? Should you pursue one? If yes, which one?

Then there is also a third career title I didn’t even mention. What about the DBA whose focus or specialty is in BI? You are the person who takes care of the SQL Servers, but maybe you also write a lot of reports for the business using SSRS, Power BI, maybe SSAS, Tableau, Qlik or some other reporting platform. This may really just be a specialty within the Development DBA role, but it could be separate as well.

Beyond the Senior levels of the types of DBA careers discussed, you’re likely to end up moving into management. That will open up an entirely different set of needed skills and career topics that you will need to explore.

Next Steps To Take

Planning - To Do List

  1. If you’ve read through this series and followed the instructions about building a training plan for skills you would like to develop, then you likely have a significant list of skills to work on. Don’t focus on how long the list is. Select one thing and work on it for however long it takes for you to feel at least semi-comfortable with it. Then, cross it off the list and pick something else. Repeat the process. Within a few weeks or months, you will see a lot of progress.
  2. Remember to keep your skills/career development plan somewhere that allows you to see the plan every day. This will help keep you on track and your To Do list prioritized.
  3. You can go to kand.io and find a variety of graded skills test. There is one for Database Administrators and one for Database Developers. Both were written by the fine people at SQLSKills.com.
  4. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

 

Introduction To PowerShell For The DBA Part 3

Hello dear reader! This is the third post in a series to help you get started automating things with PowerShell. You will be looking at how to start and stop SQL Server using PowerShell as well as setting the Max Server Memory setting, measuring free disk space and more.

This series is just an introduction to PowerShell and basic functionality you might be interested in as a SQL Server Database Administrator or as a System Administrator responsible for SQL Server. Because it is introductory I don’t have examples of long scripts that do a series of complex things as part of these posts. What the series does have are one or two line scripts that are still powerful and help you explore what is available to you in PowerShell.

With that in mind, let’s delve into a few more PowerShell commands that can help you with common tasks.

How To Start And Stop SQL Server Using PowerShell

Open PowerShell as an Administrator and run these commands. These are simple ways to search for commands related to the SQL Server services. Since we’re looking for information related to services, the first command searches for cmdlets containing the word “service”.  Since we know we want to start or stop the service, we’re looking for commands containing “start” or “stop” in those second and third commands.

GCM *service* -Module DBATools, SQLServer

GCM *start* -Module DBATools, SQLServer

GCM *stop* -Module DBATools, SQLServer

Service Related PowerShell Commands

PowerShell Commands with Start

Commands With Stop in PowerShell DBATools SQLServer

I’m showing you how to find interesting commands related to what you’re trying to accomplish, but I also want you to see how much else you can learn about the capabilities of the DBATools and SQLServer PowerShell modules. As you can see from the output, DBATools has Stop-DBAService, Start-DBAService, Restart-DBAService. The SQLServer module has Stop-SqlInstance and Start-SqlInstance.

I encourage you to look through the output of the screenshots, or in your own PowerShell session, to see what else you can do.  Particularly inside the DBATools these Get-Command outputs show many things are available. For example, you can start a SQL Agent job, start a trace, start an extended events session, you can stop an endpoint or process as well as do several other things. By the way, there is no Stop-DbaAgentJob because you can actually do that from the Start-DbaAgentJob cmdlet in DBATools using a switch.

So, now that you know the types of commands available how are you going to find out what they do? Remember your friend Get-Help or its alias Help?

Help Get-DBAService -Full
Help Restart-DbaService -Full

The output of these commands is too much to put into the post. So, I’ll just point out a few things about each one.

For Get-DBAService notice that you can pass it a computer name value to get the services for all SQL Server instances installed on a given computer.  This will be useful if you know your environment tends to do what’s called “instance stacking.” This is were 2 or more SQL instances are installed on the same computer. If this is done in your environment, you will want to know that information before you start sending commands to restart SQL Server services or you could end up restarting more SQL Server instances than you planned on. That sounds like unplanned down time and you want to avoid that!

<#Returns all SQL Server instances on a given computer. Default columns 
show things like the computerhname, instancename, service name and whether
the current status of the service.
#>
Get-DBAService -ComputerName MyComputerNameHere

Setting Max Server Memory With PowerShell

We’re going to look at Set-DBAMaxMemory from the DBATools module. If you would like more information about SQL Server memory settings. I have a blog post that describes the topic in more detail.

Here is what the DBATools website has to say about this cmdlet in their documentation.

“Inspired by Jonathan Kehayias’s post about SQL Server Max memory (http://bit.ly/sqlmemcalc), this uses a formula to determine the default optimum RAM to use, then sets the SQL max value to that number.”

Here are some examples from the documentation from DBATools and from the Help commands available in PowerShell.

<#
If you have a Central Management Server for you SQL environment, consider using this command to loop through all the SQL Servers and set the Max Server Memory where it is set to something larger than the total amount of RAM assigned to the server.
#>
Get-DbaRegServer -SqlInstance sqlserver | Test-DbaMaxMemory | Where-Object { $_.MaxValue -gt $_.Total } | Set-DbaMaxMemory

<#
If you have a Central Management Server for you SQL environment, consider using this command to loop through all the SQL Servers and set the Max Server Memory to this accepted formula created by a SQL Server expert.
#>

Get-DbaRegServer -SqlInstance sqlserver | Test-DbaMaxMemory | Set-DbaMaxMemory

<#
If you don't have a registered server then just use the below
#>

Test-DbaMaxMemory -SQLinstance SQLServerInstanceNameHere | Set-DbaMaxMemory

Measuring Free Space with PowerShell

DBAs should have some way of knowing and tracking free space in the database files and for the drives of the SQL Server machines. The DBATools module has two commands for doing just that. Check out the below examples and be sure to use the Help cmdlet on these to look at other examples.

<#Gets just certain parts of the output of get-dbaDbspace. Can add | Out-File C:\DBATools\SpaceOutput.txt 
to the end of this to output this information to a file for review.
#>
Get-DbaDbSpace -SqlInstance SQLinstanceNameHere | SELECT Database, FileName, UsedSpace, FreeSpace, PercentUsed, AutoGrowth

#Resturns drive letters, total space and free space on drives on a computer.
Get-dbadiskspace -ComputerName MyComputerNameHere

Detecting IO Latency With PowerShell

One of the things that DBAs want to check for is IO performance of the storage as seen by SQL Server.  This is often done using sys.dm_io_virtual_file_stats. You can view this information in the DBATools cmdlet Get-DbaIoLatency.

The DBATools output from the Help command says that the output of this commands is based on two articles by Paul Randal of SQLSkills.com. Those articles are listed below. If you don’t know who Paul Randal is, you need to find out. Your career with SQL Server will be greatly enhanced by reading his stuff.

How to examine IO subsystem latencies from within SQL Server

Capturing IO latencies for a period of time

I also have a post on finding queries experiencing waits related to SQL Server IO latency.

So here are a couple of examples of how to run this PowerShell cmdlet.

#Outputs the IO latency information of two different SQL Servers

Get-DbaIoLatency -SqlInstance SQLServerInstance1, SQLServerInstance2

#Outputs the IO latency to a GridView UI for visual examination
$output = Get-DbaIoLatency SQLServerInstance1 | Select-Object * | Out-GridView -PassThru

#Writes the output to a file.
Get-DbaIoLatency SQLServerInstance1 | Select-Object * | Out-File C:\DBATools\IOLatency.txt

 

Next Steps To Take

  1. On a Dev environment, practice using the DBATools commands related to stopping and starting SQL Server. This gives you another option when things go south and you have to restart the SQL instance. If the machine suddenly has issues showing the MMC snap-ins then SQL Server Configuration Manager may not be available. And yes, this has happened to me!
  2. Use the Test-DBAMaxMemory command to see if your SQL Servers are misconfigured in terms of the RAM allocated to them. Once you’ve reviewed the output of the command and are comfortable with it, then use the PowerShell examples in here to make the changes.
  3. Consider how you can leverage the cmdlets about diskspace, database space and IO latency to examine your environment. Write some test scripts and try them out.
  4. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

 

How Do I Measure My DBA Skills Part 8

Senior Development DBA

You’ve now likely reached the last stop in your career as a Development DBA. You have arrived at the Senior level. What skills do you have or should you be working on as a Senior Development DBA? I’m glad you asked. Let’s find out.

Senior Development  DBA – 7+ years of experience

    1. Most competencies from the previous levels.
    2. Advanced analysis of SQL Server Performance issues using advanced techniques to increase performance and/or stability. This could involve server level monitoring and analysis to identify issues or concerns.
    3. Makes decisions regarding SQL Server architecture, development strategies and overarching performance considerations, often in tandem with Development team or even the Infrastructure team.
    4. Regularly participates in code reviews of mid-level to Senior Devs and other DBAs, offering best practices and guidance and may assist in implementation.
    5. Fosters and protects healthy development environment, including Source control, controlled or automated migrations, and advanced development/QA Data curation via data masking technologies and potentially other methods.
    6. Makes significant contributions to the design of migrations and upgrades for SQL Server and may provide assistance to other, more junior members of the team, when it comes to implementation.
    7. Leads knowledge sharing in four or more areas.
    8. May contain competencies from Production DBA levels up to level 2 or 3.
    9. Regularly demonstrates good self-awareness, self-management, social awareness and relationship management skills (emotional intelligence

Automating SQL Server Processes

 

automation example

Remember the washing machine example from an earlier post? You wouldn’t want to use a tub and washboard to clean your clothes. You use a washing machine right? It’s faster and more efficient than doing things manually. Yeah, you’re going to be building a lot of washing machines at this stage of your career.

You will be guiding and contributing to efforts to automate processes. This will certainly show up in work efforts for migrations and upgrades as well because the Senior Development DBA will be a person who automates as much as possible. You may be doing this exclusively through PowerShell but you may also be using a variety of DevOps software to automate processes as well.

At this level, you will also be guiding implementation of and answering questions related to things like Dynamic Data Masking, cell level encryption and row-level security. You may be doing advanced data analysis using temporal tables. You will certainly be deciding which of these technologies, and others, should be used and when.

There is also likely going to be some skill overlap with the production DBA. This Senior level person is likely going to need to know quite a few things about production support and troubleshooting. You’re going to need to be able to support SQL Server Agent jobs and be able to troubleshoot job failures. Using a third party monitoring software will likely be needed as well. Many of the skills discussed in earlier posts regarding production DBA skills up level 2 or 3 will be needed at this level.

As with the Senior Production DBA, knowledge sharing and emotional intelligence will be key at this point in your career. You have to be able to work with others, have patience and keep your cool when things don’t go well.

Next Steps to Take

  1. Check out DevOps software that helps you automate tasks.
  2. Check out migration tools like the Database Migration Assistant.
  3. Get comfortable sharing your knowledge with other people. You’re going to need presentation skills and the commensurate people skills that go along with that.
  4. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

How Do I Measure My DBA Skills Part 7

Lead Development DBA

Over the past few weeks I have been writing about DBA skills for various career levels, and for a couple of different DBA types – the Production DBA and the Development DBA. Today, we’re examining the Lead Development DBA. You’ll discover what this person needs to know.

DEV DBA III/Lead Development DBA – 5 to 7 Years

  1. All competencies from previous level.
  2. Manages most administrative aspects of non-prod environments without assistance from other DBAs.
  3. Participate in T-SQL development and database design of advanced complexity.
  4. Investigates potentially complex data issues within SQL Server.
  5. May have several years of progressive experience with SSIS, SSRS or SSAS.
  6. Administers source control systems for the environment.
  7. Regularly automates tasks. This should include the use of T-SQL, SQL Server Agent, and PowerShell.  May include the use of other languages like Python or C#.
  8. Creates and manage design documentation related to development work.
  9. Regularly explores, learns and implements new SQL Server development features. Examples would include new T-SQL enhancements and new feature sets like Temporal Tables for SQL Server 2016 and Data Virtualization for SQL Serer 2019..
  10. Advanced understanding of execution plans, indexes, and query tuning.
  11. Investigate complex data integrity/repair issues within SQL Server.
  12. Leads in knowledge sharing in two or more areas from levels I, II, or III.
  13. Assists with SQL Server migrations and upgrades in DEV/QA or production.
  14. Mentors Junior DBA’s and Developers.
  15. Participates in code reviews.
  16. May contain competencies from Production DBA levels up to level 1 or 2.
  17. Demonstrates emotional intelligence and may take an interest in leadership roles.

So let’s dive into a few of these points in the skill list.

Managing Non-Prod SQL Servers

First, as a Lead Development DBA, you are managing your company’s non-prod SQL Servers without the regular input of other DBAs.. That doesn’t mean you know everything about managing the environment. However, it does mean you are no longer asking any basic questions. You’re managing new SQL Server installations and post-install configuration, managing access to the SQL Servers, and SQL Server maintenance (backups/restores, index and statistics maintenance, CHECK DB, etc).

You Have T-SQL Skills

Second, you know how to use views for inserting, updating or deleting data. Techniques such as temporary tables, table variables or a derived tables are used appropriately. You also know when dynamic SQL is appropriate. By the way, the answer isn’t “never.” Stored procedures you write have error handling using TRY… CATCH and/or THROW. Inputs are also validated to address SQL injection. You know and avoid the problems associated with scalar user defined functions. You consume development material from people like Itzik Ben Gan and Gail Shaw without your eyes glazing over.

SQL Server Performance Tuning and Optimization

Third, performance tuning and optimization is your bread and butter. You’ve probably read the various editions of books on execution plans by Grant Fritchey. Brent Ozar and Hugo Kornelis are people whose blogs you frequent to learn about execution plans and making SQL Server go fast. The Query Store is something you know how to leverage to find poor performing queries using the reports and perhaps some custom T-SQL. MAXDOP and Cost Threshold of Parallelism are things you understand and know what to do with at the server level. additionally, you know how these settings affect queries running on the SQL Server. You also know that MAXDOP can be set at the database level in newer versions of SQL Server. If you didn’t know that, see this.

Working With Other SQL Server Components and Features

Fourth, depending on the needs of your employers, you are likely to be  comfortable with SSIS, SSRS or SSAS. You can develop solutions with those tools that go beyond basic things. SSRS Administration using the Report Manager and Report Server URLs is familiar to you, for example. Complex SSIS packages are something you’re not afraid of at this point.

Mentoring and Knowledge Sharing

Fifth, at this stage of your career you will be expected to be a leader in your group and share knowledge with others. You will be skilled enough to share your growing expertise in 2 or more areas. You may be the go to person for execution plan analysis, Query Store and T-SQL.  Share that knowledge with others. Make someone else’s professional life better by sharing your experience. This will not only benefit the other person, but you as well.  Nothing solidifies learning like trying to teach something to someone else!

As part of your leadership among other people, you should demonstrate emotional intelligence in most of your interactions. You are able to manage your own emotions and the emotions of others during your interactions. You’re also taking on more and ore the role of a leader at this phase of your career. People will be looking to you for guidance and decision making.

Next Steps To Take

  1. Look at the criteria in this post and make a training plan for the things you know you need to work on and put that plan somewhere that you will see on a regular basis.
  2. Contact me here for questions about this post or the skills listed in it. You can also reach out to me via Twitter using the handle @leemarkum.

 

Introduction To PowerShell For The DBA Part 2

In Part 1 of this series we began to look at how you can leverage PowerShell as a DBA and there was a brief discussion about automation. Today you will experiment with the DBATools command Copy-DbaLogin and learn about Out-Gridview and the -whatif switch.

Let’s suppose you have one, or more logins you need to get from one SQL instance to another.  Perhaps you are setting up a new test environment for SQL Server 2019 and you need  to move this login to that environment. Maybe this is one node of a SQL Server AlawaysOn Availability Group and you need to get this login, with its SID, on each node of the AG. Below you see I have a screenshot of a login called mysqltestlogin. It’s on a SQL Server 2017 instance.

 

How would we copy this with PowerShell? First, let’s use the Get-Help command to see what commands are available related to logins.

Get-Command *login* -module DBATools, sqlserver

From that output you see a command called Copy-DBAlogin in the DBATools module.

So now run the first command to see the syntax and run the second one to see all the help data, including some examples of how the command works:

Help Copy-DBALogin

Help Copy-DBALogin -Full

The most common way you’re going to use this command is with a Source and Destination SQL Server name and one or more login names. See the example below.

These logins can be SQL logins or Active Directory Logins. One thing to note about this command is that the SID is copied with the login as well as the associated permissions. If you need to, you can use the -Force switch to re-copy an existing login. You might need to do this to ensure that logins, SIDs and permissions are the same between all nodes involved in a group of servers. Those SQL Servers could stand alone instances that are all servicing a particular application or they could be nodes in a Failover Cluster Instance or an Availability Group.

Copy-DBALogin -Source MySourceSQLServerIntanceName -Destination MyDestinationSQLServerInstanceName -Login Login1, Login2, Login3

Maybe you want to copy all the logins except a certain small list. In that case, use the -ExcludeLogin parameter and pass in the logins to omit from the copy operation.

For my scenario from above, my command looks like this.

Copy-DbaLogin -Source MyPCName\Kronos2017 -Destination MyPCName\Romulus2014 -Login mytestsqllogin

And the output is this.

DBATools Copy-DBALogin Output

 

What Is Out-GridView?

You can also use a cmdlet called Out-GridView to pass the objects of the pipeline into a GUI where you can then select the items you want to have PowerShell do the action against.

Get-DBALogin -Sqlinstance MySourceSQLInstance | Out-GridView -PassThru | Copy-DBALogin -Destination MyDestinationSQLInstance

Here are the results of that Out-GridView. Click to enlarge.

Out-GridView Example

To use this for selecting only the rows of output that you want to do something with, left click the row you want to actually copy and then click the OK button in the bottom right. That will copy the selected row. You can use Shift + Click or Control + Click to do a multi-select. There is a Filter option at the top of the Out-GridView screen that also allows you to trim down the results you want to copy.

Of course, using Out-GridView re-introduces the human element to the process and so isn’t great when the goal is to completely automate a process. However, it is great if you want to see the output before you do something you don’t want to do. As a consequence, if you see results in Out-GridView that don’t match what you expect, then you can simply click on Cancel in the lower right hand corner and the PowerShell will do nothing.

Using the -WhatIf Switch In PowerShell

This brings me to another switch that helps in this scenario where you want to see what a command is going to do before you actually executing it. There is a switch called -WhatIf that will stream output showing you what a command would do if it were actually executed. This is a great tool for testing your commands. Often there will be errors or other unexpected results from a command. Using -WhatIf is a great way to catch this before you actually execute the command for real.

So far in this article, you have reviewed what you can do for a very specific object, logins. Let me suggest that you also look at what can be done at the wider scope of the SQL Server level by running the below. You may remember from the previous post that GCM is an alias for the Get-Command cmdlet.

GCM *instance* -Module DBATools, SQLServer

Here is my output for the above. Click to enlarge.

Get-Command Instance :evel Commands

As you see, there are a lot of things you can do with commands related to the SQL Server Instance.

If you want to consider automating the scripting out of your SQL Server instance objects without buying software to do it, then check out the Help for Export-DBAInstance, which is one of the commands that is in the above list.

Help Export-DbaInstance -Full

 

Next Steps To Take

  1. Try out Copy-DBALogin in a test environment. Use it with the -WhatIf switch, or pipe it to a file by using the Out-File command to see what the command does when it runs.
  2. Experiment with various commands and Out-GridView in your test SQL Server environment.
  3. Don’t have a test SQL Server environment? Consider learning what Export-DBAInstance does and using it as part of an “Easy button” for creating a test environment on a fresh install of SQL Server. Or, look up Start-DBAMigration for this task as well.
  4. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

Introduction To PowerShell for the DBA Part 1

Automating Tasks With PowerShell

What we’re really talking about in this series is automation, specifically by using PowerShell. Now, in simple terms, automation is about removing the human, manual element in doing tasks. This will produce time savings for you as a DBA in the long run and provide better consistency in your environment than doing things manually. Better consistency means reduced or easier troubleshooting because your SQL Server environment will be more consistent with automation. All of this will make your SQL Servers easier to manage.

Imagine being able to migrate an entire SQL Server instance with a single command! You can do this with PowerShell. You want to copy logins from one server to another without installing sp_help_revlogin and  working through the scripting process involved? You can do that with PowerShell.  You want to copy DatabaseMail settings from one server to other servers without writing out all the T-SQL? You can do that with PowerShell!

With PowerShell you can get information faster than from a GUI, especially when that task needs to be done on multiple computers. These tasks include things like like search event logs, search the SQL Server Error Log,  get the status of services, see sp_configure information and more.

You can also do things like copy SQL Server jobs from one server to another or export the results of sp_configure to a file. When doing an action like backing up a database, PowerShell also enables you to output the actions of the script itself and thereby create a self-documenting log that you can look at afterward to see exactly what the script did.  With a module called DBATools, you can migrate an entire instance to a new SQL Server with a single, short command.

Getting Set Up for Using PowerShell

PowerShell is already installed on Windows. Different OS versions have different versions of PowerShell. There is a process to use for updating your PowerShell version. I’ll not be showing that process in this series though.

Also, in order to follow along in the series. You will need to get the DBATools module for PowerShell. Instructions for doing that are located at DBATools.io. The simple answer is to open PowerShell as an Administrator and run the below command. There are some things to consider and so that’s why the link to DBATools instructions on installation.

Install-module DBAtools

 

You will want the SQLServer module in order for some of these commands to work properly, and you may want to explore the more “native” way to use PowerShell with SQL Server, without the aid of the DBATools module. I think DBATools simplifies things so immensely that it is indispensable. However, you may be curious about what is in the SQLServer module for PowerShell. If so, you can add the module to your PowerShell by opening  PowerShell as an administrator and running the following.

Install-Module -Name SqlServer

 

Once you have DBATools and the SQLServer module installed, you are ready to get started.

Finding Available PowerShell Commands

Get-Command: This cmdlet enables you to explore what is available to do in PowerShell.  For instance, you might run the below to see what commands are available in PowerShell related to backups. Notice you can use asterisks as wildcards as well as specify a PowerShell module to look in for commands. If you don’t have the DBATools module, I urge you to get it installed and start using it, if for no other reason than that you will need it if you’re going to follow along with the examples!

Get-Command *Backup* -Module DBATools, SqlServer

The above command will output a long list of things.  You can backup computer certificates, database certificates, find information about backup history and throughput, as well as a lot of other things beyond simply using PowerShell to make a database backup.

Maybe you’re interested in what is available for Logins or Database Mail. If so, try the below commands.  Notice that the second command says “gcm”.  What is that? Well, that is a shortened version, or alias, for Get-Command.  Most, maybe all commands, have an alias you can use.  Aliases provide a shorter way to write a command, but when someone else is looking at your code, then they have to know what your alias is for. Otherwise it will be harder to figure out what you’re code is doing.

Get-Command *Login*  -Module DBATools, SqlServer

gcm *mail*

Getting Help with PowerShell Commands

From the above example where you are looking for commands containing the word “Login”, you should see something similar to the below if you have DBATools and the SQLServer module installed.

 

Let’s start simple and review Get-DbaLogin.  In your PowerShell window run the command  below.

Get-Help Get-DBALogin -Detailed

From this code you will get an output that provides a summary of what the cmdlet does, the syntax options it can be used with and a text description of what those syntax options do.

Let’s say that you’re interested in finding information about the Logins on your SQL Servers. If you just pass this command to PowerShell you will get a list of all the logins on the SQL instance with some properties. Of course, you will want to substitute your own valid SQL Server instance name.

Get-DbaLogin -SqlInstance MySQLInstanceName

The output will list the computer name, SQL Server instance name, name of the login, the login type, create date, the last login timestamp and some other useful properties.

Let’s suppose we only cared about the logins that weren’t system logins so we want to exclude logins like NT Service\SQLWriter and the ones whose name starts with ##. How do we do that? Well, look at the help output again and notice the -ExcludeFilter option. Add that to the earlier command, like so:

Get-DbaLogin -SqlInstance MYSQLServerInsstane -ExcludeFilter '##*', 'NT*'

PowerShell Piping

Now let’s suppose that you are only interested in a couple of the properties that are output from this command.  You’re interested in the login name and the last time that login accessed this SQL Server.

To get only those properties we can do what is called “piping”.  This is where the “|” symbol is used to move PowerShell output from the left over to the right side of the script objects.  This is often done for filtering or to pass along PowerShell output for further processing by other commands.  PowerShell output looks like text, but it’s really .Net objects.

In the first example below, you’re passing all the objects left from Get-DbaLogin when the ones with ‘##*’ or ‘NT*’ are eliminated and then telling PowerShell to display only the Name and LastLogin objects.

In the second example, you will be sending the output of the command to a file.

Get-DbaLogin -SqlInstance MySQLServerInstance -ExcludeFilter '##*', 'NT*' | SELECT Name, LastLogin

Get-DbaLogin -SqlInstance Skolarlee-PC\KRONOS2017 -ExcludeFilter '##*', 'NT*' | SELECT Name, LastLogin | Out-File 'C:\DBATools\LoginsOutput.txt'

On my local PC this returns the sa account and my local Windows account along with the last time these accounts accessed the SQL Server. This sort of information is useful, probably in a number of scenarios, but let’s suppose that you’re doing an audit of SQL Server logins because you suspect you have a lot of left over, unused logins.  You might want to examine them to see if you can drop users and logins that are no longer being used.  This sort of thing makes your SQL Server easier to manage because you don’t have unused Logins and database users cluttering up your SSMS when you’re connected to a SQL Server.

Let’s suppose you don’t want or need to do something with this information right now, but you want to review it later, or provide it to someone else for review. What can you do? The simplest thing is to use the second command above to send the PowerShell objects to a text file by using the piping technique I mentioned earlier.  The directory in the Out-File command needs to exist already but the command will create the text file for you.  This information can be written to a share for your team to access later.  You can also do things like email this output to the team using a PowerShell command for email. If you run GCM *mail* in PowerShell, you will find a command that lets you do this.

How To Script Out SQL Server Logins With PowerShell

Another powerful thing that can be done with a one line PowerShell script is scripting out your logins and users.  This cmdlet from the DBATools module will create a script of your logins at the server level and their corresponding database users and all associated permissions. Look at the help for this command.

#Start by getting the help for the command
Help Export-DbaLogin -Detailed

Now that you have reviewed the help content, let’s try something.

Export-Dbalogin -SqlInstance MySQLServerInstanceNameHere -Path 'C:\DBATools\'

Now go to the C:\DBATools\ directory and double click the .sql file.  SQL Server Management Studio will launch and prompt you to connect to a SQL instance. Once you do that, the script will load. Review the output and take a look at the Help output from PowerShell to see what else you can do with this.  This command gives a DBA a great way to script out login and user information, and this can be helpful for migrations or for providing a history of changes to logins, users, and their permissions.

Next Steps To Take

  1. Explore more commands and the possibilities that they offer. Here is a place to start.
GCM *export* -Module DBATools

Help Export-DbaInstance -Detailed

Export-DbaInstance -SqlInstance MySQLServerInsanceNameHere -Path C:\DBATools\

2. Find a book or video series on PowerShell and consume that material.

3. Read some of the documentation and blogs over at dbatools.io .

4. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

How Do I Measure My DBA Skills Part 6

Development DBA II

With the last post, we began talking about the Development DBA and the skills needed at the beginning of this career path. Now it’s time for you to ponder what the next level looks like.

Development DBA II – 2 to 4 Years of Experience

  1. All competencies from level 1
  2. Develop basic to intermediately complex stored procedures, triggers, views and other database objects.
  3. Will have some experience with SSRS, SSIS, and/or SSAS development, deployment and possibly administration.
  4. May administer source control systems for the environment.
  5. Promotes SQL changes from Dev up through all environments, including production.
  6. Contributes to automation, particularly using native SQL Server methods, but could also leverage PowerShell or other languages for automating tasks (Python, C#, etc.)
  7. Designs pre-prod testing of SQL code prior to production upgrades or migrations. This includes performance and regression testing.
  8. Basic to mid-level understanding of execution plans, indexes, SQL Server statistics, and query tuning.
  9. Investigate basic to moderately complex data integrity/repair issues within SQL Server
  10. May begin leading in knowledge sharing in some capacity in one or more areas from level I or II.
  11. May take an active interest in leadership and in development of leadership skills, including emotional intelligence.

In years 2 through 4 the Dev DBA will work with more complex T-SQL such as recursive CTE’s, and error handling with TRY CATCH or the THROW syntax will likely be introduced at this stage. You may begin writing database views during this part of your career and leveraging them to make query writing a bit simpler for yourself and others. You are likely to be exposed to T-SQL Triggers and you’ll need to understand them and even maybe write a few triggers of your own.

You will likely start to get some sort of regular exposure to SSRS, SSIS or SSAS at this point. This will of course be based partly on your own interest in learning the tools of the trade and what is in use already at your employer.

Source control and the process of promoting changes from source control up into QA, and even production, may be something that begins at this level. After all, as a person who is writing a fair amount of code, you will have an interest in ways to manage code changes.

Automation

 

automation example

Let me ask you a question. Would you rather wash clothes manually with a washboard and tub or use a washing machine? It seems like a silly question. You would want to use a washing machine. You probably can’t imagine washing clothes without one.

In a similar thought process, there are things that you and others at your job will not want to do over and over in a manual way.  Accordingly, you will make more significant contributions to automation of tasks at this level in your career. SQL Agent will become your friend and learning to script out processes to make them easily repeatable, rather than doing everything in the SSMS GUI by hand, will occur more frequently. You may automate tasks with PowerShell for sure, especially using DBATools, but Python may also be an option. In case you didn’t know, Python does integrate with SQL Server via a specific driver.

Testing Code and Performance Tuning

An important part of the Dev DBA’s work is testing SQL code. You’re going to be doing more of that at this level in your career. You will need to verify query results and probably automate repeated testing of code as well so that you can test not only results but performance. Maybe the query returned the results you expected but it took too long to do it. This is where learning how to read execution plans will come in.  Execution plan operators will give you clues about what is happening with your T-SQL and those clues can help you with re-writes or with indexing.

Aaah yes. Indexes.  You’ll need to begin understanding indexes as well.  You’ll need to know what they are, the different types of indexes and when to use each type. Types of Indexes are as follows:

  1. Clustered indexes, which may be the result of creating a primary key. These may be clustered or non-clustered primary keys.
  2. Non-clustered row-store indexes.
  3. Non-clustered columnstore indexes.
  4. Clustered columnstore indexes.
  5. Filtered indexes

As a corollary, you’ll need to look into the concept of SQL Server statistics as well.

Next Steps to Take

  1. If you’ve read the other parts of this series, then you know I’m going to tell you to copy the skills list above to a Word doc and place an “X” next to the things you need to work on.
  2. Use those “X” marks to build a training plan. Put the training plan somewhere that you will see every day. One thing that should be on your training plan at this stage is learning more T-SQL.
  3. Work the plan. I have a post that will help you find resources for your learning plan.
  4. Contact me here for questions about this post or the skills listed in it. You can also reach out to me via Twitter using the handle @leemarkum.

How Do I Measure My DBA Skills Part 5

Development DBA

We’ve been on a journey recently in this series as we discuss measuring your DBA skills. Previous posts have been intriguing to you. You like where this is going and yet, your skills are different from what we’ve been discussing in this initial part of the series.

Maybe you are a Developer who works with databases and you find SQL Server very interesting. You build some tables and write some queries as part of your work and you really like doing it. You like trying to figure out how to make your initial draft query for your project run faster. You have conversations about this with other Developers or maybe even a DBA or two at your company. What you hear resonates with you and deepens the interest in SQL Server. You wonder, “Is there a place for me in the SQL Server world? What would I do if I did a career pivot to work with SQL Server almost exclusively?” Enter the Development Database Administrator.

 

What Does a Development DBA Do?

Before we jump into a discussion of skills, we need to try to answer the question, “What does a Development DBA do?” After all, if you think you’re interested in this, what are you signing up for?

A Development DBA will often be responsible for the management of the non-prod SQL Servers, SQL development work for applications and processes, task automation and perhaps source control and the change deployment process. Non-prod SQL environments need care and maintenance. The Dev DBA role can assume this responsibility. This isn’t because the Dev DBA is a “Junior” role, but because this non-prod environment plays the critical role of testing grounds for development work. This non-prod environment will be where the Dev DBA works and experiments with new SQL Server development such as stored procedures, SSIS work SQL Agent job development etc.

This work will often be done in tandem with Developers from other areas of the company to produce new products and applications. The Dev DBA spends most of their time in non-prod so there is familiarity and as a result it makes sense to have the Dev DBA manage the environment.

Related to the experimental nature of the work, and the need to promote that work to production, the Dev DBA will often participate in or wholly manage source control and change processes such as source control software and the design of the process of promoting code through the various developmental levels of the environment. Once deployments are tested, scripts are then often handed off to Production DBAs for deployment of new code in production, but even this could be automated by the Dev DBA with control over when the deployment happens being wielded by the Production DBA.

At higher levels, the Dev DBA role may begin to overlap some with the Production DBA role and skillset. Let’s dive into the skills of a Development DBA.

The Development DBA Role

DEV DBA – 0 to 2 Years Experience

  1. Design tables utilizing basic normalization techniques.
  2. Basic T-SQL development. SELECT, INSERT, UPDATE, DELETE. Stored procedure development with guidance from more experienced DBAs.
  3. Manages most administrative aspects of non-prod environments with assistance from more experienced DBAs, including the application of patches to pre-production.
  4. May participate in pre-prod testing of SQL code prior to production upgrades or migrations.
  5. May have some exposure to SSIS, SSAS or SSRS.
  6. Demonstrates understanding of basic backup/restore processes.
  7. Demonstrates values driven behaviors such as humility, integrity, teamwork and is teachable.

T-SQL Skills

Now that you’ve looked over the list, let’s talk about it. T-SQL skills and overall development skills are at the heart of this role. The Dev DBA will spending a lot of time manipulating data and the first and foundational way that will happen will be with the core skills of SELECT, INSERT, UPDATE and DELETE. Introductory and basic T-SQL skills will be learned, practiced and built upon as you spend time in this role. A corollary to that will be learning all about the relational database table. At this phase of the career the Dev DBA will be learning what a good relational table should look like. Even if you can’t quote them, the principles of first, second and third normal form will become familiar as you design more and more tables.

Stored procedure development will be learned in this part of your career. You’ll be exposed to what a stored procedure is, why it should be used, advantages to it, the basic form of a stored procedure and so on. Of course, you’ll begin to write some of your own stored procedures as well. After all, there’s no point in learning about them, but not actually writing them!

Database Security

In this phase of your career you maybe be asked to make some logins and users as part of your development work. You’ll need to learn what those database objects are, how they are different and how they work together to provide authentication and permissions. You will, I hope, be doing this with the help of other DBAs who have walked this path before you so that you’re not scratching and clawing to figure it out on your own. My point is, other people will be guiding your work at this phase.

You will be testing code, a lot. After all, in this role you are creating things in non-prod that have to be reliable. You will be making sure that result sets make sense.

Depending upon the technologies in use at your company, you may be exposed to things beyond the T-SQL and basic security that we’ve already mentioned. SQL Server Integration Services (SSIS) may be  important at your work, and as a result, you will need to have some basic understanding of it. Maybe your employer has invested heavily in SQL Server Reporting Services (SSRS). In which case, you will be exposed to report development and deployment.

What Happens When I Lose Some Data?!

At this point in your career, you probably aren’t taking backups or restoring backups. But you should begin learning backup and restore concepts. You’re going to need that understanding as your career grows. Trust me!

You are going to make a mistake in a non-prod environment and wipe out a small, or large, amount of data. Please accept that this is going to happen. You’re going to make a mistake. You’re going to freak out and think, “I’m going to be fired!” I hope that you won’t be fired over it, especially since you’re not working in production. If you introduce me to a person who says that they’ve never messed up or lost any data, I will have met either a liar or a person who hasn’t worked with data nearly long enough!

When this happens, and it will happen, relax, and ask for help. If the other people on the DBA team have done their first and most important job, you’ll be able to get the data back. If they haven’t done their first and most important job, then maybe they are the ones who should be worried!

Do I Really Need People Skills?

Just like the other posts in the series, the last thing on the list is about people skills. Yes, you’re going to need them.  You have to interact with other people on your team, people on other teams and maybe even external customers. You need to be teachable, develop good listening skills and be willing to help other people. All of these things will help you lean your craft. You need the other people around you because they know things and have experience that you need. Remember that example above where you’ve deleted data you need to get back? If you don’t work on your people skills and have good relationships with co-workers, then that conversation where you have to ask for help is going to be reeeaaallllly awkward!

Particularly at this level of your career, other people can help you avoid pitfalls in your code as well as help you get your next promotion. So, be humble and willing to ask questions. When someone answers your question, don’t argue. If you don’t like the answer or you think the person is wrong, ask more questions to get clarification. Say something like, “I’m not sure I follow. Can you try explaining that another way?” Or you might say, “I hear what you’re saying. Would there be anything wrong with doing it like ‘X’? Is there a reason that wouldn’t work?”

Next Steps To Take

  1. Copy/paste the skills list into a Word doc and place an “X” next to anything you need to work on.
  2. Pick two or three things to focus on and build yourself a training plan. Put that training plan somewhere that you will see it every day.
  3. Ask a person on your team how to do a task that is important in your environment, but that you don’t know anything about yet.
  4. Talk to me. Contact me here if you would like specific help with anything in this post or other things related to SQL Server. If there is an issue with the form, you can reach me at leem@leemarkum.com. I will be glad to help.