DallasDBAs.com

Explaining SQL Server in plain english

Header Right

syndicated

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: Dismount-DbaDatabase and Mount-DbaDatabase

by Leave a Comment

We have a double feature for today’s dbatools blog post, as these two commands go hand-in-hand.

Todays commands: Dismount-DbaDatabase and Mount-DbaDatabase

Dismount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test'

Mount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test'

Detach-DbaDatabase and Attach-DbaDatabase can be used as aliases. For the purpose of this blog i’m using the official names, because reasons™

Note that the .mdf and .ldf files are still present, meaning detaching a database can keep you from deleting a database you were told wasn’t needed anymore, only to instantly have someone ask why they can’t get to the database they need. If the files are still there, you can attach it and it’s good as new.

Dismount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test' `
    -Force

If the database is part of a mirror or something, -Force can be used to detach it anyways.

Dismount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test' `
    -UpdateStatistics

This will update the statistics of the database before it’s detached.

$testAttach = New-Object System.Collections.Specialized.StringCollection
$testAttach.Add("C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\test.mdf")
$testAttach.Add("C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\test_log.ldf")

Mount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test' `
    -FileStructure $testAttach

The -FileStructure parameter can be used to point out where the files to attach are. If not specified, it’ll use backup history to figure out what you mean, if possible.

Mount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test' `
    -DatabaseOwner 'sa'

The -DatabaseOwner parameter lets you set the, well, database owner, when you attach the database. Defaults to sa.

Dismount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test', 'square[]brackets'

The -Database parameter can take multiple values.

That’s all i got for this one, not too complicated unless you’re doing something with credentials and stuff. Follow me (b|t) on twitter!

Previous Dbatools posts:

Backup-DbaDatabase
Restore-DbaDatabase
Get-DbaLastBackup
Get-DbaAgentJobHistory
Measure-DbaBackupThroughput
Get-DbaErrorLog

Future Dbatools posts:

None yet!

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: Measure-DbaBackupThroughput

by Leave a Comment

Today’s dbatools command: Measure-DbaBackupThroughput

This one’s a smaller command, but that doesn’t mean it’s without use. If you need information on backup speeds (throughput) or early warning of possible hardware issues, such as in a RAID 5 setup, this can help you.

Measure-DbaBackupThroughput `
    -SqlInstance PRECISION-M7520\SQL2016 `
    | Select-Object `
        SqlInstance, Database, AvgThroughput, `
        AvgSize, AvgDuration, MinThroughput, `
        MaxThroughput, MinBackupDate, `
        MaxBackupDate, BackupCount `
    | Out-Gridview

Basic command usage, then we pipe it through a select statement and into a gridview. Selecting only those specific columns to cut down on redundant information, and also to get it all in one picture easier. Only measures full backups by default.

 

Measure-DbaBackupThroughput `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database test `
    -Last `
    | Select-Object `
        SqlInstance, Database, AvgThroughput, `
        AvgSize, AvgDuration, MinThroughput, `
        MaxThroughput, MinBackupDate, `
        MaxBackupDate, BackupCount `
    | Out-Gridview

Maybe we don’t want all the backup history for every database. Maybe we just want the most recent history for one database. The -Database parameter lets us specify which databases to check, and the -Last parameter returns only the most recent backup of each type.



Measure-DbaBackupThroughput `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Since (Get-Date).AddDays(-28) `
    | Select-Object `
        SqlInstance, Database, AvgThroughput, `
        AvgSize, AvgDuration, MinThroughput, `
        MaxThroughput, MinBackupDate, `
        MaxBackupDate, BackupCount `
    | Out-Gridview




The -Since parameter combined with Get-Date lets us get all the information since a certain point in time relative to now. Presumably this can also be done with a hard-coded date, but that seems like an edge-case scenario to me.




 


Measure-DbaBackupThroughput `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database test `
    -Type Differential `
    | Select-Object `
        SqlInstance, Database, AvgThroughput, `
        AvgSize, AvgDuration, MinThroughput, `
        MaxThroughput, MinBackupDate, `
        MaxBackupDate, BackupCount `
    | Out-Gridview




Use  the -Type parameter to get information on different kinds of backups, accepts “Full”, “Log” or “Differential”.

Note: -Type parameter does not accept multiple values with commas, probably because the results would then have to have up to 3 times as many rows, one for each backup type.




 


Measure-DbaBackupThroughput `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -ExcludeDatabase msdb, master, model, tempdb `
    | Select-Object `
        SqlInstance, Database, AvgThroughput, `
        AvgSize, AvgDuration, MinThroughput, `
        MaxThroughput, MinBackupDate, `
        MaxBackupDate, BackupCount `
    | Out-Gridview




If we don’t want specific databases, we can use -ExcludeDatabase for that. Useful for only checking the user databases. You don’t have to specifically exclude tempdb, but i did it anyways, just for fun.




Speaking of just for fun, i also took backups of the test database onto 3 different drives, C and two external drives. The results are somewhat less useful than i’d like, though:




Speaking of external drives, go follow me (b|t) on twitter, where i mostly talk about two things:


    

  1. Things related to SQL Server.
    2. 

  2. Things that may or may not be related to SQL Server.
    3. 



Previous Dbatools posts:


Backup-DbaDatabase

Restore-DbaDatabase

Get-DbaLastBackup

Get-DbaAgentJobHistory


Future Dbatools posts:


None yet!