Steps I Took To Handle Burn Out

I want to put a disclaimer at the beginning. I am not a mental health professional. Do not construe anything I say to be professional mental health advice. I am simply telling my story.

I’ve always wanted to be a high impact person. I want my life to count for something. I think most people want that. In the United States, where the idea of “living the American dream” is prominent, this desire to make a difference and to live the dream often leads to people living life by going as fast as possible. By late 2020 I found myself doing that. I found myself going in too many directions.

  1. Trying to start an IT business.
  2. Work a full time  IT job.
  3. Study for an IT certification.
  4. Write 2 blog posts a week.
  5. Began answering questions on technical forums.
  6. Dealing with the stress of a global pandemic.
  7. I moved out of the house I raised my kids in for 17 years just one day after my birthday.

That’s a tough combination. I was excited and full steam ahead on all of those things for several months in 2020. In December I took vacation time for the last two or so weeks of the year. The plan was to throw myself even more into launching the business, studying for the 70-764 and blogging.  However, what I discovered on those first few days of vacation was that I just couldn’t make myself do any of it. I realized I was exhausted.

You’ve heard the phrase about burning the candle at both ends to describe people who are really working hard and staying busy in life. I felt like the latter half of 2020 I hadn’t just been burning the candle at both ends, I’d been trying to melt the whole thing with a blow torch! I was burned out – mentally spent. I also began to wonder if I was experiencing symptoms of depression as well.

So, what did I do?

Making Time for Down Time

 

I rested. That “working vacation” in December 2020 turned into a couple of weeks of doing a whole lot of nothing. I actually took a vacation from my problems and my stress. No writing blog posts or studying for certifications, and I didn’t really work on developing the business either.

I also eventually decided to take a consistent day off. For a few weeks in 2020 I was getting up at 7:30 AM on Saturday and Sunday to start work on blogging and business strategy. I would work for 4 or so hours every Saturday and Sunday morning, then I would take a break for awhile and go do something else. During that time I would frequently return to work on blogging that evening. Even when I decided not to get up early on Saturday and Sunday, I was still dedicating the first few hours of both days to blogging and technical work. Essentially, I was working 7 days a week.

Many years ago I worked two jobs and had only 2 days a month off. That’s right – 2 days a month!  I did that for 5 years and was elated when I no longer had to do that. I remember how dropping down to one job changed me quite rapidly, and for the better. Going back to that was not something I wanted to repeat, but I still knew that I would need to put in some effort on my own time to accomplish any of the goals I had.

As a result, I’ve started taking off all day on Saturdays. For probably 2-3 months, I haven’t done anything technical on Saturdays. On Sundays, I sleep in like a person would normally do on their day off and then I do probably 2-4 hours of technical work. This has made a big difference in my mental energy levels. Our bodies and minds were not designed to be going, going, going. We need down time. We need recreation.

Re-focused My Certification Efforts

I had been working hard on the 70-764 SQL Server certification. As I worked through that material, I realized I was learning, or re-learning, a lot of things that I probably wasn’t  going to use any time soon. I had previously been studying for the AZ-900 and switched to the 70-764 because I knew the deadline for that certification was coming. With this new realization, I switched back to the AZ-900 some time in early to mid-January because I could see more potential for using what I was learning. I’m happy to say that in early March I passed the AZ-900! Three weeks later I passed the DP-900 and I’m working on the DP-300.

De-prioritizing My Business

This seems like a bad idea for my business, but it is the choice I made. Remember what I said earlier? I was doing too many things at once. Something had to give.

From March 2020 until early December 2020 I had been working hard on business plans and talking to friends in and out of the tech industry to get input. In October 2020 I incorporated the business, currently just named Lee Markum LLC. It is a tech company focusing on helping people with their SQL Server performance issues. I paid a local web developer a small amount of money to make some changes to the website because those changes desperately needed to be made and I wanted to focus on other aspects of the business.

I contacted a company in Canada who works with SQL Server professionals to update websites. The price was a bit of a steep investment so I wanted to wait until I had more cash on hand. I also made some software purchases around the time of the Black Friday sales in November 2020.

The one thing I hadn’t wanted to do was to try to start this business on my own, but that’s what ended up happening. I invited some friends to launch it with me, and for a few months there was interest. However, what I realized was that while I was full steam ahead on this because the idea of this business had been brewing in my head and heart for about 10 years, the timing just wasn’t right for my friends to join me in the adventure. I have no hard feelings toward my friends. As I said, the timing just wasn’t right for them to be involved in the ways I envisioned.

So, the business went on hold for a bit. I needed to get a detail about my Doing Business As name straightened out anyway. Thanks to the slow grinding wheels of state government, that issues wasn’t resolved until sometime in March 2021.

No Blogging or Technical Forums

I didn’t intentionally decide to take a break from blogging and answering questions on forums. This just sort of happened, but I think it was for the best. Blogging takes a fair amount of time and energy. I took the time off in December, but in January I still found that I only had the mental capacity to do my full time technical job as a SQL Server DBA. Compiling blog ideas and  creating drafts still occurred, but that’s as far as I made it and so that is why I have had only 3 posts this year. I plan to get a queue of posts written so I can post at least once a week. I spent my time in Feb/March focused on the two Azure tests I took so I could get them out of the way.

So that’s a big part of what has been going on with me and my various projects. The previous year has been hard on all of us. I am certain most everyone would like to forget that 2020 happened, and we’re all coping the best way we know how.

Next Steps to Take

  1. Assess your well-being. Think deeply about how you’re truly doing right now.
  2. Don’t be afraid to seek professional help.
  3. Watch this presentation from Tracy Boggiano on “Mental Health in IT” presented at PASS this last Fall.
  4. Check out this post from SQL Server guru Kendra Little where she gives advice to her 20 year old self.
  5. Also read here where Kendra talks about experiencing burn out for herself and what she did.

Top 3 Most Popular Posts of 2020

So, I realize it’s now almost April of 2021 when I’m posting this. Normally this sort of post would come out in January for the previous year. However, I have a reason for this being late and I’ll actually post about that separately.

Showing My Blog Some Love

In mid-2020 I decided to give my blog more attention. there were a number of reasons for this. First, I’d already spent a fair amount of money over the years on hosting and related things so I didn’t want to waste that. Second, I really wanted to try to start giving back to the SQL Server community. I have been the recipient of a lot of learning and help through a number of regular, and occasional, bloggers. I have also purchased and mostly read my fair share of SQL Server books, which was another way I had been helped by the community. Third, I wanted a place to record my own troubleshooting and learning so when I needed to do something again, I would have a record of what to do. By the way, I have seen this repeatedly listed as a reason that someone blogs.

Blog Posts by Month

So, here is the break down of the number of posts by month for 2020:

  • April – 1
  • July – 1 This was the last week of the month and marks my decision to start blogging more consistently.
  • Aug – 3
  • Sept – 4
  • Oct – 4
  • Nov – 7 – I had built a good backlog of posts that were ready to publish and nearly reached my goal of posting twice a week for the entire month
  • Dec – 4

To get the list of the most popular blog posts I looked at Google Analytics PageView data for Jan 1, 2020 – Dec 31, 2020.

Popular Post # 1

The most viewed page based on this data had 354 views and was part 1 of a series on PowerShell for the DBA. The series was meant to show some “getting started” type techniques so that someone could open PowerShell and start exploring what the tool could do for them as a DBA. I explored how to find commands that might be of interest and how to use the help system in PowerShell to figure out how to use the command. I also provided some practical examples of PowerShell to use in every day scenarios.

https://leemarkum.com/archive/2020/11/introduction-to-powershell-for-the-dba-part-1/ 

Popular Post #2

The second most viewed page had 332 views and covered 5 different ways to make a SQL Server backup. Backups are so important, especially in the world of data. They give you and your business a way to recover data that has been lost or damaged in some way. As a Database Administrator, you need to be able to recover and that all starts with taking backups. That seems obvious but I still read stories of businesses that aren’t even backing up their databases. If you do have backups, you also need also to test whether you can use those backups to actually restore data.

https://leemarkum.com/archive/2020/09/5-ways-to-make-sql-server-backups/ 

Popular Post #3

The third most viewed post had 273 views and was about using a new feature in SQL Server 2019. This feature is based on the polybase technology that arrived in SQL Server 2016. However, the feature has now been extended to allow SQL Server to have external tables to ole db and odbc sources, such as SQL Server, MS Access, and, yes, even things like Oracle and IBM iSeries/AS400. The external, or virtual table, uses mostly built-in drivers. With this feature yo can run SELECT statements against remote data sources without a linked server. The environment I’m in has an AS400/IBM iSeries so I wanted to see if I could get the feature to work with that data source.

https://leemarkum.com/archive/2020/04/querying-an-as400-using-sql-server-2019-data-virtualization/

There you have it. I hope you enjoyed these posts in 2020, and if you haven’t seen them, give them a click and read through the information. I hope you learn something from the posts and if you have questions or comments reach out to me here, or on Twitter, and I’ll be happy to talk to you.

My Availability Group Database Isn’t Synchronizing

I currently manage 5 AlwaysOn Availability Groups. Two are on SQL Server 2014 and overdue for an upgrade, while three of them are on SQL Server 2017.  From time to time I have run into a couple of different situations that I needed to troubleshoot and I want to tell you where to look and what to check on in these scenarios. I can’t possibly tell you about everything that could go wrong, but I can tell you about my experience with AlwaysOn Availability Groups and let you decide if that experience helps you or not.

A Single Database Is Not Synchronizing

If a single Availability Group database is in the “Not Synchronizing” state, what would you do?

There are several things to check. First you could look in SQL Server Management Studio to see if data movement has been suspended. If it has then you will see an icon similar to two pipes || over the database icon.  You will also see messages in the Error Log about data movement being suspended. If this is the case, try to resume data movement. This can be done in SSMS by right clicking the database in the Availability Databases folder and choose “Resume Data Movement”. Alternatively, you could execute the below T-SQL.

ALTER DATABASE [YourDatabase] SET HADR RESUME

Beyond that you can also use PowerShell to resume data movement for the database.

<#
gcm is the alias for Get-Command. Below I'm using wildcards to find any commands with the wor "resume" in it
and specifying that I want PowerShell to look in the DBATools module.
#>
gcm *resume* -Module DBATools

<#
Once I have found a command I think might help me, then I run the alias Help for Get-Help
to find out how to use the command

#>

help Resume-DbaAgDbDataMovement -Detailed

#Resumes data movement on db1 and db2 to ag1 on sql2017a. Prompts for confirmation.

Resume-DbaAgDbDataMovement -SqlInstance sql2017a -AvailabilityGroup ag1 -Database db1, db2
    

 

Second, check the SQL Server Error Log for errors related to the AG and check the  AlwaysOn_health xEvent that is running by default for AGs. To check the default health session for AlwaysOn Availability Groups, open SSMS and navigate to Management > Extended Events > Sessions > AlwaysOn_health. Then, click the plus sign next to it in order to drill down to see the file. Right click the file and choose “View Target Data”. After doing that, you can filter and search the file for relevant entries.

Extended Events AlwaysOn Health

If none of these things gives you enough information to resolve the problem, you could also restart the SQL Server service on the secondary that is showing the database that isn’t synchronizing. Again, there are many ways to do this, and I’m going to encourage you to look at the DBATools module in PowerShell to find an automated way to handle this. As a last resort, you could remove the database from the AG and then re-add it to the AG.

None of the Availability Group Databases Are Synchronizing

What if all of the databases are in the “not synchronizing” state, what would you do?

This is a little different because in this case every database on at least one secondary is in the “Not Synchronizing” state. The prior scenario was only about a single database with a synchronization issue.

The primary thing I check in this scenario is whether the cluster service is running on the secondary. If it isn’t, then none of the databases will synchronize. I have seen this happen several times, particularly after secondary servers have been rebooted. You could also have a network disconnect between this replica and the primary. Again though, the SQL Server Error log or the AlwaysOn_health extended events session will show you this. Additionally, you can look in the Cluster Events section of the Failover Cluster Manager for one of the nodes in the AG. In there you may find that you have lost quorum for some reason.

If for some reason you need to get down into the fine details of what is happening with the failover cluster, then check out the following path in the Windows Event Viewer.

Event Viewer > Applications and Services Log > Microsoft > Windows > FailoverClustering. In that location are three logs called Diagnostic, DiagnosticVerbose and Operational.

Next Steps To Take

  1. Go to YouTube and find videos on Availability Groups.
  2. Consider taking a class from the fine folks at SQLSkills about Failover Clustering and Availability Groups.
  3. Reach out to somebody if you have questions. 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.

 

SQL Server Query Store Overview

Query Store came out in SQL Server 2016. It was the next evolution of Microsoft’s attempts to provide valuable performance data. In this post you’re going to learn a bit about the history of native monitoring in SQL Server, why you should be using the Query Store, as well as how to view the information made available in SQL Server Query Store. This post is just an overview meant to get you to jump into using the feature. This feature is rich enough that someone can present on it easily for an entire day.

A Very Brief History of Native Monitoring in SQL Server

Query Store came out in SQL Server 2016. It was the next evolution of Microsoft’s attempts to provide valuable performance data. With SQL Server 2005, the Dynamic Management View and Dynamic Management Functions were a giant leap forward in terms of providing data about what was going on inside SQL Server. Performance tuning with those objects opened up a whole new world of insight. The “Waits and Queues” methodology for performance tuning rapidly sprung up.

The Data Collection and Management Data Warehouse feature, new in SQL Server 2008, collects both server and database level performance metrics. Deployment of this feature is possible on both a single instance of SQL Server and across multiple instances. That data could then be centralized and viewed through a series of reports.

Extended Events also came out in SQL Server 2008. Despite being a lighter weight replacement for SQL Server Profiler, Extended Events was a bit unwieldy and there was no UI. Adoption was slow.

In SQL Server 2012, Microsoft built a UI for Extended Events, and that helped improve adoption.  Extended Events opened up a wealth of information that Profiler cannot offer.

Then in SQL Server 2016, the Query Store came on the scene. Now that we’ve arrived at this feature, let’s talk about it!

Why Use SQL Server Query Store?

 

  1. Query Store solves the challenge of persisting performance data over time.

The feature collects and persists information about query performance by gathering the queries that are ran in a particular time interval, their query plans and aggregated runtime performance data for those queries. To do this natively, you would need to run extended events continuously and capture a lot of data. Then that data would need to be persisted and done so in a way that allows for easy analysis later. Capturing query text, the plans and other data about query performance isn’t free, even with something like Extended Events. Also, query plans are pushed out of memory as time goes on and,  of course, every time you restart the SQL Server valuable performance data is lost. Query Store solves this problem.

2. Persisted data in Query Store allows for detecting issues related to query plan changes.

The feature allows the DBA to track queries across time and displays all the different query plans associated with the query, up to the default of 200 execution plans per query. When a plan changes, the Query Store reports bubble up this information. The information also includes aggregated runtime data associated with each plan. Detecting this sort of issue without Query Store is much more challenging.

3. With Query Store you can see query performance across time and from a variety of perspectives.

Microsoft added query wait statistics to the feature in SQL Server 2017. Bar graphs in the Query Wait Statistics report show categories of wait types. Queries that contribute to that wait type, such as CPU, Memory, Lock etc. ,   are visible in the report. The DBA then has valuable insight into waits experienced by queries thanks to Query Store. This report can be based on Total Wait Time, like below, or Avg, Min, Max, and Standard Deviation. Left clicking on the wait category shows the queries contributing to that wait type.

 

Example report for Query store Wait Statistics report

With the built-in Query Store reports, the DBA can see expensive, poor performing queries with the highest CPU, duration, logical reads and more. This data can also be based on totals, averages min/max and standard deviation. By doing so, Query Store offers insight into the most expensive, painful queries that are hurting performance during any particular time slice and across days of history retained in the Query Store.

4. You can view missing index recommendations for expensive queries  using Query Store. Now, you shouldn’t go out and blindly apply the recommendations, but this does offer you the chance to analyze existing queries and the missing index recommendation to see if index cleanup and consolidation needs to happen. This process can benefit not only the query with the missing index recommendation, but other queries in the workload as well.

How To Enable the SQL Server Query Store

Enabling the Query Store is simple enough with two lines of T-SQL, but there are some defaults and options that you should understand before you enable the feature. Not all defaults are going to be desirable and you’ll learn which ones you should adjust as we progress. I will be showing you this in SQL Server 2017.

Below is what the query store UI looks like when initially opened and Query Store is not enabled. To get to this, open SSMS and connect to a SQL Server that is at least version 2016. then drill down into the databases folder and right click a database name and left click the “Properties” option. Then left click Query Store on the left in the “Select a Page” area.

Initial Query Store SSMS
Initial Query Store Screen Before Enabling

Now, when you left click on the drop down menu for Operation Mode(Requested) you get options for Read Only and Read write. To enable the query store and have it capture performance data, select the Read write option. Now you have a screen that looks like the below. Please note the two options I have bracketed in red. You will want to change these options and we will discuss that as I explain what these options do.

Query Store Defaults
Defaults for the Query Store

Query Store does not write collected data synchronously, but asynchronously. The “Data Flush Interval (Minutes)” value controls how long collected query data sits in memory before being written to disk. There is a trace flag that controls whether the data is written to disk prior to a shutdown as well as one that controls whether the data in Query Store is loaded first before a database comes online.

The “Statistics Collection Interval” also known as Interval_Length_Minutes, determines the time period over which aggregations of the data occur. Valid values are (1, 5, 10, 15, 30, 60, 1440). The smaller the value, the more granular your data will be and the more space that will be used by the feature.

The defaults for the following options should be changed. “Max Size (MB)” controls how large the Query Store can be and works in conjunction with a couple of different settings. The default size is 100 MB, and that is simply too small to store data on SQL Servers with much of a workload. The recommended setting for this is 2048 MB from Erin Stellato of SQLSkills.com. That is a starting point and size can be monitored from there.

The setting “Query Store Capture Mode” in SQL Server 2017 defaults to “All”. This captures even queries with virtually no impact on performance metrics like CPU, duration, logical reads, etc. It is unlikely that you need to capture every single query ran. It is probably better to set this to “Auto”.

The amount of space used by this feature is also influenced by the Size Based Cleanup Mode setting. The default is auto and it should be left that way. When the storage size for Query Store gets close to the maximum set in Max Storage Size (MB), then a clean up process kicks in, removing older data.

The space used is also influenced by the “Stale Query Threshold (Days) ”  setting. The default is 30 days and this controls how much query performance history is persisted. The default is a good starting point and depending on needs, you may want to keep more so you can see changes over a longer period of time. You might choose to do this if you really have no Dev or QA environment to speak of at your employer so most development is done in production. You know you shouldn’t be doing that, but I also know that many DBAs don’t work in the ideal, perfect environment.

As mentioned earlier, the “Wait Statistics Capture Mode” is available and on by default. If you leave it on, which is recommended, you get the benefits discussed earlier. You also will have a larger footprint for your Query Store so this is another option that influences size.

Once you have all of these options configured, simply press OK in the SSMS dialog.

If you prefer to use T-SQL, below is a sample script to enable this feature and change the two settings discussed above that are not great defaults.

USE [master]
GO
ALTER DATABASE [MyDB] SET QUERY_STORE = ON
GO
ALTER DATABASE [MyDB] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, MAX_STORAGE_SIZE_MB = 2048, 
QUERY_CAPTURE_MODE = AUTO)
GO

Next Steps to Take

  1. Consider your current monitoring solutions and consider making Query Store part of your approach to performance tuning.
  2. If you like reading books, Tracy Boggiano and Grant Fritchey have a great book on Query Store.
  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.

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.