DallasDBAs.com

Explaining SQL Server in plain english

Header Right

SQL

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

by Leave a Comment

Welcome to part 2 of my series reviewing my first year as a DBA. If you missed the first post, you can read it here.

Moar Training!

Reflecting back to the earliest days (via re-reading my Dallas DBAs Slack channels from the beginning), it’s very easy to see just how far I’ve come in the last 12+ months. It’s already getting hard to remember the days when I was a Mac-only guy and trying to relearn my way around the Windows OS. I still get tripped up from time to time, but I’m a confident-enough Windows user that now my keyboard shortcut muscle memory defaults to Windows commands, which causes momentary confusion when I try to operate my iMac.

I ran my VM through it’s paces in those days, taking a bare install and getting SQL Server Developer Edition on there as fast as I could. I ran through installs, patching, instance creation, database creation, and a whole lot more. Doing all of this over and over again really helped solidify the basics and give me confidence to move on to more critical issues such as backups, restores, and integrity checks.

Those days were also something of a juggling act since, while the design business was steadily waning, there was often enough to keep me busy for several hours throughout a week. Also, when I wasn’t working on graphics or learning more about SQL Server, I was learning my way around Power BI. I’ve since pretty much dropped Power BI off my radar for now, but will likely take it back up again in the future when it makes sense.

One of the biggest frustrations I’ve had in self-guided learning is discovering that there’s a ton of simply erroneous information out there related to SQL Server. I can’t tell you how many times I’ve read a blog post or article to eventually find it was just wrong. I don’t know if these authors are just newbies like me, or if they wrote one-off posts from an “it worked on my machine” standpoint. Either way, winnowing out faulty sources was frustrating as an apprentice.

And don’t get me started on technical information that’s just grossly out of date…

Stay tuned for the last post in this new series where I’ll share my favorite and recommended SQL Server training resources.

Working in Prod

It was mid-July of 2019 that I got my first real taste of client production servers. We started out small, with me observing daily checks being performed. From there I began to RDP into client servers from home while Kevin watched over my shoulder virtually using Zoom screen sharing. (We were using Zoom before it was quarantine cool!) After about a week of this per client, Kevin felt secure in letting me fly solo, and before the end of the month I had two clients for whom I performed daily checks.

Not bad for an apprentice DBA with less than two months of experience.

Now granted, there have been very few critical discoveries during my time on these clients, and no major emergencies that I can recall. Most days it’s pretty basic and somewhat tedious stuff. My daily reports often look something like this:

Manage Engine:
No issues found.

DPA:
No significant waits or blocks.

Jobs:
No new job failures.

Backups:
All backups are current.

Errorlogs
Login failures for 'sa' are still ongoing (known issue).
4 deadlocks in the last four days.
No new errors found.

And while such reports seem boring, they’re a product of something very important. Most of the time, Kevin has whipped a client’s servers into shape before handing them over to me for daily checks. That means we have clients with SQL Server instances that are by-and-large humming along without any problems. This means I’m now on the front lines in detecting issues before they become major problems.

Keeping a daily record of simple things like hard drive space, waits and blocks, job failures, and error log output helps us know when things just aren’t running like they should. Like a canary in the coal mine, we can often give our clients a heads up long before an issue with their servers becomes painful.

Honestly, serving our clients and helping ensure their data is safe so that they can concentrate on operating their business successfully is extremely gratifying.

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



SQL Server Express Size Limit

by Leave a Comment

My Pluralsight course for new SQL Server DBAs

 

(Needs to upgrade to a bigger bowl)

Common scenario:

Company is using SQL Server Express to save on licensing costs for a small database. Over time this database grows and is getting close to the hard 10GB limit.

I just got off the phone with a client and their client is in this situation. During the conversation, she mentioned that the DB has information for four “regions.”

They already have a purge process in place, and compression is not an Express Edition feature.

Options I gave:

  • Decrease the retention (her client already said no to this)
  • Split the data into 4 databases – 1 for each region
    • Might require some re-work of applications and connection strings
  • Install a second Express instance and split the data between them
    • Easier on their app
  • Convince the end customer to buy SQL Standard (1% chance this is going to happen)
  • Buy Standard Edition for them and eat/split the cost to keep a huge customer happy
  • Move to a different platform (nope…all SQL Server shop)
  • I saw a blog post that you can bypass 10GB by using the Master database instead
    • That’s the worst idea I’ve ever read. If you do that, never call me. 🙂

If you have other legitimate things I should add to this list that Microsoft will support, please share in the comments.

Conclusion:

If your data is important, buy the proper version for your needs.  Maybe that’s Standard, maybe an Azure VM and spread out the SQL licensing over many months. But don’t do crazy things to try to get around the limits.

Thanks for reading!

Kevin3NF

| |

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

by Leave a Comment

Whoo! It’s been six months since my last post. There are reasons for this, none of which are probably valid, but it’s the way it is. I’ve been a full-time DBA for just over a year now, and Kevin thought it would be a good time to review how far I’ve come thus far. Looking back, it’s been quite an experience.

Ozar Number = 3

Back in May of 2019, after several years of working from home as a graphic designer and WordPress developer, my family was more than three months into almost no income. Due to various factors, we just weren’t getting many leads and many of our clients had moved on to lower-cost options. Our emergency fund was empty, and I began sending out emails to friends and acquaintances asking them to keep an ear to the ground for any potential job leads. I started looking at everything from working in the warehouse at IKEA, driving a UPS truck, and even become an insurance adjuster.

My long-time closest friend Kevin—for whom I had done some Dallas DBAs-related graphics work over the years—responded by basically saying, “I think I can help. Let’s go for a bike ride and talk.” Before the end of the week, Kevin had me set up with a free PluralSight account and I began learning the basics of what makes SQL Server tick. It all began with Kevin’s own course, Getting Started with Your First SQL Server Instance.

The funny thing is, Kevin had been offering free one-day classes throughout the DFW area for several years, teaching introductory basics of SQL Server for accidental DBAs and those curious about database administration as a career. I had attended his classes more than once to give him an honest evaluation from the viewpoint of someone who knew almost nothing about SQL Server. Looking back now, I can see that Kevin was already working on me to consider changing careers, knowing I would probably be a good fit.

Coming back around to May 2019, we agreed for me to start with some self-paced investigation into the world of SQL Server, with the goal of me attending SQL Saturday Dallas on June 1, 2019. If I was still interested in pursuing DBA as a career, that day would be my first official day on the payroll at Dallas DBAs.

Funny enough, I had already attended SQL Saturday Dallas the year before as a volunteer photographer. Being an actual attendee was quite a different experience. I had already volunteered to be the photographer for a second year long before considering becoming a DBA, so I split my time between helping Kevin staff his company sponsor’s table, taking photographs, and attending a handful of sessions. Honestly, most of the sessions were way over my head, but it was worth getting to see some various approaches to troubleshooting. I ended the day attending Brent Ozar’s (b|t) class on What’s New in SQL Server 2019 and had a fantastic time despite being completely lost for most of the session. The term parameter sniffing has been indelibly etched in my brain since that day.

Oh, and what’s with the Ozar number? At some point in the past, my co-worker SQL Dork (b|t) came up with an idea that basically goes like this: If you’ve done online training with Brent then add 1; if you’ve met Brent in person then add 1; if you’ve taken an in-person class with Brent then add 1. My first day on the job at SQL Saturday, since I’d already done one YouTube “class”, my Ozar number went from 1 to 3. I’ve spent many hours training with Brent online since that day.

The Apprentice

From that day forward, and for the next several months, I was the newly-christened apprentice at Dallas DBAs—earning SQL Dork a promotion to Junior. I had already spent some time working in a VM installing instances, updating instances to the latest SPs and CUs, learning about the different file types involved in a SQL Server environment, identifying the differences in the different recovery models, and getting started with understanding backups and restores. Now my training began in earnest.

In the interest of keeping this post from growing into a novel, below are the links to a series of posts I made in the first four months of training. These cover a lot of the initial concepts I learned on my way to starting work on production servers and becoming a junior DBA. Don’t worry, there’s more to come in my story as I try to fill in the gaps between those posts and what’s gone on in the last few months.

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

The Ironic DBA Files



TIL: Get-DbaErrorLog

by Leave a Comment

Ed: SQLDork is a Junior DBA on the team here, learning the DBATools in addition to normal DBA stuff™

Today’s dbatools command is Get-DbaErrorLog, which is very similar to a script we frequently run on client servers to check for errors that need fixing.

Get-DbaErrorLog `
    -SqlInstance PRECISION-M7520\SQL2016 `
    | Select-Object `
        SqlInstance, Source, LogDate, Text `
    | Out-Gridview

Basic command usage, using Select-Object to filter out the columns i don’t really care about, then piping the results to Out-Gridview.

Get-DbaErrorLog `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -LogNumber 1, 0 `
    | Select-Object `
        SqlInstance, Source, LogDate, Text `
    | Out-Gridview

Adding the -LogNumber Parameter to check the archived errorlogs, defaults to current log only.
NOTE: If you want the results ordered by date, the values passed into -LogNumber must be in descending order.

Get-DbaErrorLog `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Source spid5s `
    | Select-Object `
        SqlInstance, Source, LogDate, Text `
    | Out-Gridview

The -Source parameter lets us filter out some stuff™. I can’t think of a better way to describe that. The documentation uses -Source Logon as an example, but my sample data didn’t have that so i used a spid.

(Ed: The Source can be a spid, “logon”, “Server”, or anything you see in the errorlog Source column in the SSMS Log Viewer)

Get-DbaErrorLog `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -LogNumber 5, 4, 3, 2, 1, 0 `
    -Text "instance" `
    | Select-Object `
        SqlInstance, Source, LogDate, Text `
    | Out-Gridview

Using -Text as a rudimentary where clause, but this one only supports 1 string. We can do better.

Get-DbaErrorLog `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -LogNumber 5, 4, 3, 2, 1, 0 `
    | Select-Object `
        SqlInstance, Source, LogDate, Text `
    | Where { `
                ( `
                $_.Text -Like "*CheckDB*" `
            -or $_.Text -Like "*Error*" `
            -or $_.Text -Like "*UTC*" `
                ) `
           -and ( `
                $_.Text -NotLike "*found 0 errors*" `
           -and $_.Text -NotLike "*finished without errors*" `
           -and $_.Text -NotLike "*informational message*" `
                ) `
            } `
| Out-GridView

This formatting feels very strange to me, but it does a very similar thing to that errorlog query we use that i mentioned earlier.

(Ed: The Where-Object or Where is PowerShell, not DBATools, click here)

Shoutout to Kevin (b|t) for helping me figure out the syntax on this, and also for pointing out that he changed the -SqlInstance parameter to his own machine name when he was testing this. Saved me a bunch of time troubleshooting connection errors.

Speaking of connection errors, follow me (b|t) on twitter, even though i don’t really tweet that much. Unless i retweet pictures of dogs, that’s always a possibility.

Pluralsight course for new SQL Server DBAs

TIL: Restore-DbaDatabase

by Leave a Comment

(This post comes from one of our Junior DBAs – @SQLDork)

If you haven’t read it already, i recently wrote a blogpost where i go over Backup-DbaDatabase, you can read that post here.

Or here, it’s your choice which link you want to click to go there.

Today’s command: Restore-DbaDatabase

Same setup as last time, backups, ISE, SQL Server instances, all that good stuff.

Restore-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016

This kinda works, but asks to specify a bunch of paths if you run it. Best to specify those in the parameters ahead of time, i’d imagine.

 

Restore-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Path 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup'`
-DestinationDataDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationLogDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Log'

This seems to work, but won’t do anything because the databases already exist.
Warning message suggests using -WithReplace to overwrite the existing databases, but i think there’s another option…

 

Restore-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -DatabaseName 'test_restored'`
-Path 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\test_202004241133.bak'`
-DestinationDataDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationLogDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Log'

Moved some parameters around for readabilities’ sake.
Warning message implies we need to specify the names for the .mdf and .ldf, which makes sense.

 

Restore-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -DatabaseName 'test_restored'`
-Path 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\test_202004241133.bak'`
-DestinationDataDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationLogDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Log'`
-DestinationFilePrefix 'restored_'

Success! Database name is “test_restored”, and file names are “restored_test.mdf” and “restored_test.ldf”
Let’s dork around with the parameters a bit to get the names to be consistent:

 

Restore-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 `
-Path 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\test_202004241133.bak'`
-DestinationDataDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationLogDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationFilePrefix 'restored_' -RestoredDatabaseNamePrefix 'restored_'

Moved the log files to go in the DATA folder, because apparently the Log folder is for different stuff. (Ed: yep…that’s for errorlogs, crash dumps, default trace files, etc.)
No longer specifies the database name via -DatabaseName.
Instead uses -RestoredDatabaseNamePrefix to put ‘restored_’ at the beginning of the name.
This would be useful if we were restoring multiple databases at once.

NOTE: There is a -DestinationFileSuffix, but there is no -RestoredDatabaseNameSuffix parameter. I’ve asked twitter for clarification on this.

There doesn’t seem to be a parameter for renaming the logical names of the
files in Restore-DbaDatabase either, but there is one in Rename-DbaDatabase.

 

Restore-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 `
-Path 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\test_202004241133.bak', 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\square[]brackets_202004241133.bak'`
-DestinationDataDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationLogDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationFilePrefix 'restored_' -RestoredDatabaseNamePrefix 'restored_'

Restore multiple databases at once, there’s probably a better way to do this without having to specify the obnoxiously long path both times.

Yes, i have a database named square[]brackets.
Yes, i have gone crazy.
No, you shouldn’t make a database with square brackets in the name.

 

Restore-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 `
-Path 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\test_202004241133.bak'`
-DestinationDataDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationLogDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationFilePrefix 'restored_' -RestoredDatabaseNamePrefix 'restored_' -OutputScriptOnly

Using -OutputScriptOnly dumps a T-SQL script into the command line, which you can edit or run to your heart’s content.

 

Summary

Parameters i found to be useful, some of which i didn’t test
because i’d have to insert lines into the database and i’m lazy™:

-SqlInstance: Required, tells powershell what instance to restore to.
Can use commas to point to multiple instances, like if you wanted a fresh copy of prod data on your 7 dev boxi.

-Path: Tells powershell where the backups to restore are, enclose in single-quotes if there’s a space. (Looking at you, Program Files)
Can point to a folder to restore all the backups in that folder, or to a .bak file to restore just that file.
Use commas to specify multiple paths.

-DestinationDataDirectory: Where to put the physical data files, enclose in single-quotes if there’s a space.
-DestinationLogDirectory: Where to put the physical log files, enclose in single-quotes if there’s a space.

NOTE: All paths need to be specified relative to the instance specified with -SqlInstance.

-DestinationFilePrefix: Adds a string to the beginning of the names of the restored files.
-RestoredDatabaseNamePrefix: Adds a string to the beginning of the restored database name.

-DestinationFileSuffix: Adds a string to the end of the names of the restored files.
-RestoredDatabaseNameSuffix: Does not exist, i wish it did, but it doesn’t.

-OutputScriptOnly: Paranoid? This will give you the script for the thing you just did, just in case.

-NoRecovery: Restores the files with NoRecovery, for if you have more files to restore.
-Recover: Does a With Recovery, nothing fancy.

-RestoreTime: Point in time recovery, stop restoring before the bad thing™ happened.

-WithReplace: Overwrites the existing database if there is one with the same name.

-MaintenanceSolutionBackup: Tell dbatools that you’re using OlaHallengren’s (b) maintenance solution.
Makes things faster since it now knows what to look for, rather than checking every file in the folder.

That’s all for today, if you want to bombard me (b|t) with questions i don’t know how to answer, you can do so on twitter!