fbpx

DallasDBAs.com

Explaining SQL Server in plain english

Header Right

syndicated

2020 Year In Review

by Leave a Comment

TL;DR – It was great and not great

I think we can all pretty much agree that in some regards 2020 was horrible, globally.

So lets move on…

Good stuff:

COVID-19 Vaccines are hitting the market and being distributed!

Boeing 737 MAX is cleared to fly again (Boeing is a Dow 30 component and LOTS of people are employed there or by Boeing suppliers)

US Presidential election appears to be settled (if you want to go all political on this, see you on Twitter!)

We moved the Dallas DBAs Global HQ in Q3 2020. (I moved…we all WFH, there is no physical presence)

Dallas DBAs specific stuff:

  • New customers added in 2020: 29 (+52%)
  • Sales: +28% over 2019
    • Analysis – COVID forced us to take smaller projects, but more of them
  • Pocket DBA customers: +100%
  • Website clicks (blog posts and other pages): +83%
    • Some of this from more advertising/marketing
    • MUCH of it from Jeff (b|t) and Liz (t) adding to the blogs
  • Website clicks over to Tim Mitchell’s (b|t) page: 618 (+38%, number 1 overall site visited from ours)
  • Blog posts: 42 ( down 30%…writing is hard, lol)
  • YouTube:
    • Views + 78%
    • Hours watched: +82%
    • Subscribers: +124%
    • 4 new videos – videos are hard
      • Feel free to suggest a video topic in the comments (something I can cover in 10-20 minutes, beginner DBA stuff)
  • Kevin started the Data Bits podcast and needs to get back on it!
  • Number of employees: 3 – no new hires and NO LAYOFFS!
  • Kevin released a followup Pluralsight video
    • Look for a new one in 2021 as well. Maybe 2 if I get energetic and time permits

Summary

It was a good year for the company. We were able to pivot quickly and offer services that matched the newly shrunken/frozen budgets of our clients and market. We only had one that had to stop a project and I anticipate them picking it back up in 2021.

Personally – we are all healthy here, and that’s a good thing. 2 folks in my extended family contracted COVID but recovered (mostly). I was able to pick up the cycling miles and have a home gym installed in the new home’s garage for throwing some weights around from time to time…gotta keep healthy!

Thanks for reading!

Kevin3NF

Top 3 Upgrade & Migration Mistakes

by Leave a Comment

This is a guest post by another friend of Dallas DBAs – Brendan Mason (L|T)

Upgrading and migrating databases can be a daunting exercise if it’s not something you practice regularly. As a result, upgrades routinely get dismissed as “not necessary right now” or otherwise put off until there is not really another option. Maybe the physical hardware the instance was running on had a motherboard melt down, or perhaps the vendor of your ERP software is forcing your hand by no longer supporting that near decade old version of SQL Server. Or maybe you’re running a current version but find yourself along for the ride when the application team decides they’re moving from on-premise to the cloud.

Everyone wants upgrades and migrations to go smoothly, particularly the folks on the business side of the house who control budgets for outside help. As consultants, we get called in to help with these projects a lot. I have personally been involved in hundreds of database migrations as both an employee and a consultant. It has exposed me to a lot of different scenarios, good and bad. In this post, I’m going to talk about the top 3 mistakes I see companies make when it comes to SQL Server upgrades and migrations.

#1 – Deciding on the solution before analyzing the goals

It seems that everyone thinks they need AlwaysOn Availability Groups these days, whether they know what it is they do or what the alternatives are. Most often, a kick-off meeting for a migration project starts with, “We need you to set up our new Availability Group so that we have multiple copies of the database and never have another outage. We have VMs built and ready for you to install SQL Server on.” This constitutes being proactive to a fault. Sometimes the entire architecture needs to be redone when we tell them that Availability Groups won’t solve their particular use case or give them what they were led to believe, or that their budget simply won’t accommodate Enterprise Edition. Getting too far ahead of yourself can also cost you extra money. I have seen companies go out and buy physical hardware for their new environment, only to discover during our conversation that the processors they paid extra for have more cores than the SQL Server Standard Edition instance they want to use will be able to utilize. It adds insult to injury when I tell them that Microsoft will expect them to license all those cores for SQL Server even when the lone instance won’t be using them all.

In a recent example of a pre-determined solution that was incompatible with the upgrade goals, a client wanted to perform an in-place upgrade of their SQL Server 2012 instance running on Windows Server 2012. They didn’t want to upgrade the operating system and had correctly noted that SQL Server 2019 was not supported on Windows Server 2012. Based on this, they had decided that 2017 Standard would work for them and they just wanted some outside expertise to help with the upgrade process. It wasn’t until we were days away from the upgrade that they shared that Transparent Data Encryption (TDE) was a must-have feature that they needed to enable post-upgrade. This is an easy misinterpretation for someone not well versed in SQL Server editions and features to make. TDE had been an Enterprise Edition feature until 2019 when it was finally included with Standard. The client had been looking at the edition comparison for 2019 and didn’t realize that TDE was not available in Standard edition for the earlier versions.

Another common one I see is the decision being made up front to use a database Platform-as-a-Service (PaaS) solution. I’m a big fan of PaaS in general, but there can be a lot of gotchas – especially when it involves migrating a legacy database.

Remember, we’re here to help guide you during the planning phases, not just the execution, so I recommend engaging early in the process. The more details you share about what problems you’re trying to solve and what you want to get out of your upgrade/migration project, the more likely it is that you’ll land with a cost-effective solution that does exactly what you need.

#2 – Not having an adequate testing plan

You can do everything right as far as sizing your new environment and choosing the right SQL Server edition to meet your requirements, but if you don’t actually test before migrating, you’re taking a huge risk. Inevitably, there is almost always a forgotten dependent application or an old stored procedure that uses an obscure piece of syntax that’s no longer supported. There are also changes made to the database engine with each new version that usually improve query performance, but occasionally cause regressions for certain queries and workloads. If the testing plan is to just “click around” and make sure the application still functions, you can easily miss issues that can escalate under full user load.

Part of testing should be collecting baseline performance metrics. You can’t effectively compare performance of the upgraded databases without a baseline from the source. You can and should also take this opportunity to perform some basic performance tuning prior to upgrading. There have been several cases where I have been able to recommend a reduction in the number of CPU cores and associated cost for the migration just by looking at accurate baseline performance metrics and/or making a few minor performance tweaks.

Another often overlooked part of testing is the usual myriad of connected services and applications. Database Mail, SSIS, SSRS, linked servers, etc. all need to be validated during testing to avoid surprises at go-live.

Note that a solid testing plan is not a replacement for an equally solid rollback plan. You don’t want to be trying to come up with one on the fly. Once a SQL Server database is upgraded to a newer version, you can’t back it up and restore it back to the older version. That means that if you discover a major issue post-upgrade, you need to either work through it, roll back to the database backup that was taken immediately prior to upgrading, or have a process in place to reconcile the deltas.

#3 – Not taking advantage of the new version

Every new version of SQL Server comes with improvements to performance, security, and functionality. You’re paying for these things, so you might as well use them, right? Unfortunately, clients can often be reluctant to take full advantage of these enhancements as part of an upgrade project. Those who wait to upgrade until the SQL Server version they’re running is about to go out of support tend to just want to get upgraded with as little change as possible. However, you’re missing out on a lot of benefits when you take that approach. Everyone will have a slightly different situation that dictates which new features warrant trying out, but there are a few general recommendations I have for getting more value from your upgraded SQL Server instance:

Query Store – When upgrading from a SQL Server version prior to 2016, I suggest enabling Query Store after the upgrade. It’s a valuable tuning tool (the so-called “flight recorder”) and you don’t want to wait until you really need it to turn it on.

Compatibility level – In order to take advantage of all the latest enhancements and Query Optimizer fixes, your databases should be at the latest compatibility level. Leaving them at their legacy setting post-upgrade is the “good enough” approach, but if you’re doing proper testing, you might as well test changing the compatibility level while you’re at it. There are tools that will analyze database code and queries and identify incompatibilities, so there’s not a good excuse to not at least test this. Unlike upgrading the database itself, changing the compatibility level is not a one-way operation and it’s a trivial change to go back, if necessary. Bonus points if you enable Query Store prior to changing the compatibility level – it can help identify performance regressions and target individual queries that might need attention. Just always be sure to follow vendor requirements if the database is used by third-party software.

Keep in mind that I’m not advocating that you do these things on go-live night as part of the actual upgrade. I’ve simply witnessed too many upgrades that ended with the database online and passing all the smoke tests, but with no further effort devoted to maximizing value gained from the upgrade.

Wrapup

There you have it – Those are the top 3 mistakes I see during upgrades and migrations. This by no means comprehensive and I have purposely left out some especially cringeworthy experiences to protect the guilty. Upgrading versions or moving to new hardware or platforms doesn’t have to be painful. In fact, with good processes and procedures it should be so predictable and straightforward that it becomes a regular part of keeping your systems up to date.

 

ED: Please feel free to leave some comments in the ummm…comments section!

Virtual Log Files

by Leave a Comment

Today’s post is a guest article from a friend of Dallas DBAs, writer, and fantastic DBA Jules Behrens (B|L)

One common performance issue that is not well known that should still be on your radar as a DBA is a high number of VLFs.

Virtual Log Files are the files SQL Server uses to do the actual work in a SQL log file (MyDatabase_log.LDF). It allocates new VLFs every time the log file grows. Perhaps you’ve already spotted the problem – if the log file is set to grow by a tiny increment, then if your the file ever grows very large, you may end up with thousands of tiny little VLFs, and this can slow down your performance at the database level. Think of it like a room (the log file) filled with boxes (the VLFs). If you just have a few boxes, it is more efficient to figure out where something (a piece of data in the log file) is, than if you have thousands of tiny boxes. (Analogy courtesy of @SQLDork)

It is especially evident there is an issue with VLFs when SQL Server takes a long time to recover from a restart. Other symptoms may be slowness with autogrowth, log shipping, replication, and general transactional slowness. Anything that touches the log file, in other words.

The best solution is prevention – set your log file to be big enough to handle its transaction load to begin with, and set it to have a sensible growth rate in proportion to its size, and you’ll never see this come up. But sometimes we inherit issues where best practices were not followed, and a high number of VLFs is certainly something to check when doing a health assessment on an unfamiliar environment.

Find your VLF counts

The built-in DMV sys.dm_db_log_info is specifically for finding information about the log file, and command DBCC LOGINFO (deprecated) will return a lot of useful information about VLFs as well. There is an excellent script for pulling the count of VLFs that uses DBCC LOGINFO from Kev Riley, on Microsoft Tech Net:

https://gallery.technet.microsoft.com/scriptcenter/SQL-Script-to-list-VLF-e6315249

There is also a great script by Steve Rezhener on SQLSolutionsGroup.com that utilizes the view:

https://sqlsolutionsgroup.com/capture-sql-server-vlf-information-using-a-dmv/

Either one of these will tell you what you ultimately need to know – if your VLFs are an issue. How many VLFs are too many? There isn’t an industry standard, but for the sake of a starting point, let’s say a tiny log file has 500 VLFs. That is high. A 5GB log file with 200 VLFs, on the other hand, is perfectly acceptable. You’ll likely know a VLF problem when you find it; you’ll run a count on the VLFs and it will return something atrocious like 20,000. (ed – someone at Microsoft support told me about one with 1,000,000 VLFs)

Resolution

If the database is in Simple recovery model and doesn’t see much traffic, this is easy enough to fix. Manually shrink the log file as small as it will go, verify the autogrow is appropriate, and grow it back to its normal size. If the database is in Full recovery model and is in high use, it’s a little more complex. Follow these steps (you may have to do it more than once):

  • Take a transaction log backup .
  • Issue a CHECKPOINT manually.
  • Check the empty space in the transaction log to make sure you have room to shrink it.
  • Shrink the log file as small as it will go.
  • Grow the file back to its normal size.
  • Lather, Rinse, Repeat as needed

Now check your VLF counts again, and make sure you are down to a nice low number. Done!

If you need help, contact us

Thanks for reading!

Kevin3NF

The Ironic DBA—My First Year as a DBA [Part 4]

by Leave a Comment

My Favorite and Recommended Training Resources

As I’ve been in intense, focused SQL Server training for the last year, I thought it would be a great time to share links to the resources I’ve gathered over the preceding months. To be clear, I’ve not yet had the chance to use all of these resources, but have saved them for future use. So, I can’t guarantee that all of these resources are of equal value or quality. Some types of presentations will appeal to you in various degrees based on your learning style.

I’m not going to list many specific blogs or non-dedicated training resources, but there’s a wealth of information out there if you search for it. One of the best things you can do is jump on Twitter and follow the #sqlhelp and #sqlfamily hashtags to find some of the best minds in the SQL community. You should also join the SQL Community Slack channel and get involved.

Basics for Beginners and Accidental DBAs

Kevin’s Getting Started with Your First SQL Server Instance on Pluralsight.

Though it’s not out yet—but should be very soon—Kevin has a new class on Getting Started with SQL Server Maintenance coming out on Pluralsight that is the perfect next step once you’ve completed his first class.

You should also check out Kevin’s YouTube channel for an array of quick videos on many basic SQL Server concepts you should know.

And to finish tooting our own horn here at Dallas DBAs, you should read all the posts in the Accidental DBA category here on the blog.

For the first of many links to Brent Ozar materials, I suggest starting with his DBA Training Plan series of blog posts to get grounded.

I highly recommend the Accidental DBA Series at SQL Skills. Prepare to go a lot deeper and take your time with these posts. I should probably go back and re-read all of these myself.

SQL Server Tutorial has a nice collection of beginner-level tutorials to help you get a grasp on concepts.

You should definitely subscribe to PASS’s DBA Fundamentals Virtual Group. You can peruse the meeting archive to find past webinars on a variety of topics.

You can learn about SQL Server, Azure, and more at Microsoft Learn.

Check out Kendra Little’s Training Plan for Junior DBAs Learning SQL Server, as well as her Dear SQL DBA series.

Topical Overviews

The SQL Server Central Stairways series covers everything from Azure to U-SQL and more. There’s no particular order to climb these stairways, but some will require more previous knowledge than others.

Microsoft has a collection of SQL Server and Azure Labs and Workshops available for free.

Online Classes, Webinars, and Streams

Get a grasp on the basic of indexes and how they work with Brent’s How to Think Like the SQL Server Engine.

Speaking of Brent Ozar, we got in on his Black Friday sale last year for his Recorded Class Season Pass, and it has been a fantastic experience. Not only is Brent a top-notch presenter and teacher, he constantly updates the classes with his latest live presentation, which means you can re-watch the class every few months and learn about different aspects of the topic at hand based on class participant questions.

You can see all the content from past SQL Bits conferences. There’s over 900 videos available spread across dozens of SQL and data-related topics.

Here’s a handful of SQL Server related channels you should definitely get subscribed to today:

The Ironic DBA—My First Year as a DBA [Part 3]

by Leave a Comment

T-SQL Tuesday is a monthly blog party for the SQL Server community. It is the brainchild of Adam Machanic (b|t) and this month’s edition is hosted by Kerry Tyler (b|t), who  has asked us to write about “Learning From Others“.

I thought I’d join the party this month and throw my latest post into the mix since my entire journey over the last year has been learning from others in the SQL community.

Junior DBA

On September 17, 2019, I was given a promotion from Apprentice to Junior DBA. By this point, I had three daily check clients and one weekly check client in my portfolio. Despite the promotion, I was (and remain to this day) very much in training mode. The primary reason for the change in status was the fact that I was starting to interact directly with some of my clients.

Until this point I had been performing my daily server checks and sending my reports to Kevin. From there, Kevin would let the clients know of problems critical enough to warrant their attention. Once I became a Junior, I began to send my own reports to one client and interject in emails to others.

Honestly, this was a bit nerve-wracking at first. While I’m no stranger to emailing clients for work or business, sending emails of a highly-technical nature—especially within a field where I am still quite green—was completely outside my wheelhouse. Kevin has corrected me privately or clarified terms for clients when something I’ve written isn’t completely accurate. I’m okay with this because it not only aids our clients in getting the most accurate information, but it also helps solidify terms and concepts in my mind.

Since promotion day, I’ve added three more daily clients to my routine. Each new client brings another level of complexity and new experiences—including one client using replication, and another using Availability Groups.

Impostor Syndrome

Being so new and inexperienced in a job is something I haven’t had to deal with for a long time. To be honest, it’s a very strange feeling to be a middle-aged man about a year into a third career.

I was just getting settled into feeling comfortable calling myself a graphic designer after about five years of experience. When our design clients started drying up and I began learning about photography in the hopes of starting a photography business, I ventured onto shaky ground again. Thankfully, I discovered I was a natural at photography and became rather good at it in a short period of time. Despite all that, the business never got of the ground because I underestimated just how saturated the local market was, so it was a non-starter as a business (and why I don’t count it as yet another career).

Now that I’m a little over a year into the SQL Server world, I’m starting to find balance again. For a long while, whenever I met someone who asked me what I do for a living, I had felt like an impostor saying, “I’m a DBA.” I would sort of waffle and respond with something like, “Well, I used to be…and then I…but now I’m learning to be a DBA.” It’s only recently that I’ve felt confident enough to just flat out declare, “I’m a DBA.”

Comparison is a losing game, especially when you’re comparing yourself to those who’ve been in the game for 20+ years. In my months of dedicated training, I’ve spent a lot of virtual time around DBAs who have been in the business for a long time. It’s easy to fall into the trap of comparing myself to them, but I constantly remind myself that I’m still new and haven’t discovered my area of specialization yet. The people I’m learning from typically have both experience and specialization in their toolbox.

Certification?

The biggest challenge over the last few months has been preparing for the 70-764 Administering a SQL Database Infrastructure exam. About the time Kevin laid this challenge out for me, we discovered that Microsoft was retiring most of the role-based certifications in favor of a new framework.

To be fair, Kevin never required me to take the certification exam. He only wanted me to study and learn the material as the next step in my DBA training. Knowing myself, however, I asked him to set me a goal for taking the exam, which helps me keep my focus and move forward toward a specific goal. In the wake of the COVID-19 pandemic, Microsoft has decided to keep the pre-existing certifications and exams through January of 2021. Our goal is for me to take the 70-764 exam by the end of September 2020.

I’ve been studying long enough now that I’m starting to find and take some free online practice tests for the exam. I have access to the official practice exam which I’ll probably attempt in August 2020 to find my weak areas before scheduling the official exam. I’ve also found several flash card decks on the Quizlet website that were created by previous exam-takers. I’m spending some time each day reviewing and quizzing myself using these decks.

I fully expect to not pass the exam, and I’m okay with that fact—I’ll still give it my best effort. Again, Kevin’s goal has been for me to learn and grow as a DBA through both study and experience, not collect certifications. As I work through the practice tests I’ve found so far, my biggest takeaway is that there’s no replacement for experience. Several times I’ve been completely stumped by an exam question that would likely not cause a problem for a DBA with several more years of experience. But each time I’m stumped by a question, I go research it and learn the answer, thereby increasing my own knowledge.

Maybe, just maybe, I’ll have enough knowledge internalized by exam time to pass, but I’m not going to be terribly disappointed if I don’t. This is just another step on the journey to becoming a better DBA.

Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.