To the untrained eye, Relational Databases are arcane, magical things. I remember my first realization (as a programmer, mind you) that Databases do follow normal data structures and algorithms that any other program follows.
Wonder and awe.
I wish I were being facetious.
I’m still fresh enough in my exposure to databases that I haven’t turned into a grizzled old guy yelling at the NoSQL kids with their hats on backwards and their ACID washed jeans bearing phrases on their rears like “BASE” or “fault tolerant” (HA), and I haven’t forgotten my own struggles as a DBA. If anything, the mistakes I’ve made are only amplified now; they’re still new enough that I remember them as if they were yesterday (in some cases, they were only yesterday).
This blog post is a list of things that tripped me up. I don’t provide any answers here, only questions.
What are service accounts? Are they the standard NT SERVICE\MSSQLSERVER account types? If not, are they domain accounts? How are they managed and created?
If Backups shouldn’t all run at the same time, and we should have our databases in FULL Recovery mode,; how do you set up a FULL, DIFF, and LOG database backup to not collide? Do you stagger the FULL/DIFF? The FULL starts at 12:00am and the DIFF at 12:05?
Why are some basic bad things (like the query processor running out of memory) not better catalogued by SQL Server? Why doesn’t it tell me, “Hey, this is the hash of the plan I failed on?” (or at the very least output the query to the error log)
Are transaction log backups supposed to randomly fail with weird messages? (That’s a SQL Backup error. I think I understand the cause, but I’d love to figure out how to stop it)
Why doesn’t SQL Server come with an automated backup/restore tool? Why must we keep reinventing the wheel ?
Why is RECOVERY for when you want your database up right now and NO RECOVERY for when you want to restore more backups? Shouldn’t that be the other way around?
Why do I need to use RESTORE FILELIST ONLY to see what the data and log logical names are for a database before I restore it from a backup? The .bak file obstensibly contains the name of the database I’m trying to restore. It knows. Does it want to know that I know?
And What crazy person decided that a database’s logical name could be totally different from its filename, and that you really do need both pieces of information to restore it? Why not just say ‘screw it’ and let me overwrite the original file name on restore if I don’t provide a logical file name?
Why does a transaction log backup failing for 1 database in 200 (on a single server) mean that finding out which database that failed take an act of congress? (Or really, a custom backup/restore checker) My options are to either spleunk through the log files, or hope the restore tool catches it before we need it.
Why are basic things like compressing the backup not available in Web edition? This isn’t an expensive feature (Red Gate charges a few thousand a year for the privilege for 4 servers), but you pay out the rear end if you want it (just check the licensing costs between Web edition and Standard Edition).
Why does using Buffer Pool Extensions blow up SQL Server if you put the BPE file on an ephemeral storage volume? TempDB can handle it, why can’t BPE?
Why, in 2014, is Microsoft Guidance to email a database if it’s small enough?
Why are system databases still stored on C:\ if other options are available? Why is moving them such a giant pain once the database is in production?
For that matter, why aren’t Log files automatically pruned out of the box for everything? Why aren’t there sensible defaults?
Why would you name two completely separate things (the log file, like the error log) and the transaction log file (sometimes just called the log file) named so similarly? Why are we lazy with our words?
Why aren’t important things for SQL server screaming at you? If my database is slow, why isn’t there a giant thing that says, “This is why it’s slow?” We all know you can’t trust the activity monitor.
Why are there Users and Logins? Of course, I’d love to create a user that can’t login to the database. Why that makes perfect sense (again, fledgling DBA). Why do users get orphaned from logins when you restore a database? The names match; in what world would that be two different people? What insanity is this?
Database Mail? Why isn’t that easily scriptable from one Database Server Instance to the next? Trips me up more than I care to admit.
Why is it when I kill a process, that process doesn’t show as killed? Sometimes it still shows as “runnable”, and other times it stays in the “rollback/killed” status for a long time (depending on whether I’m using sp_whoisactive or sp_who2).
Why does the backup and restore wizard perform so badly over a network? Why can’t I use the UNC Path syntax to access other drives?
Why aren’t necessary database metrics shown in their own view? The DBA should have a necessarily different view than a database developer. Why is SSMS so generally crappy? Why doesn’t it have better support (in 2014) for Intellisense, or command completion? (It seems like starting a command off with RESTORE Database should be enough to let SSMS know to start showing you the options)
Why is it infinitely configurable; yet common sense defaults still aren’t set? Why does High DPI support still suck so bad? With as much money as Microsoft makes from SQL Server, they can’t spend time to improve SSMS? Why s the difference between an operational server and a server in RECOVERY (again, NO RECOVERY?) mode a tiny little icon difference? Shouldn’t stuff like that be in giant bold letters? YOUR DATABASE IS DOWN.
Why doesn’t SQL Server tell you its ebb and flow for SQL Server agent jobs? It knows when it’s busy, it knows when it’s not; why doesn’t it warn you when you try to schedule a job during peak load?
Why are Reporting Services encrypted by default, yet the actual engine does not? Microsoft went all “Do whatever you want” for SQL Server, but for Reporting Services suddenly got cold feet?
Why doesn’t SQL Server support a TMUX like connection through SSMS? If it does, why isn’t that the default?
Does anyone really create solutions in SQL Server Management Studio? If we’re closing SQL_QUERY_1.sql -> SQL_QUERY_7.sql, do you really think we know which one is which? Why not make the default file name to show whatever the first 20 characters were of the query?
Why do I feel like an episode of Sherlock whenever something goes wrong in SQL Server? Server is slow… Well, there’s a hung SQL Backup process, could that be causing it? Let me kill it. Things got better! That must be it. Was it it? I HAVE NO IDEA. I know baseline, and I know what it’s like now, but this is like piloting a helicopter in a snowstorm. I only know which way is down because gravity and death. Everything else? No idea.
Come to think of it, I’d pay for some Star Trek TNG style alerts and dashboards for SQL server. Think of the Brent Ozar scripts (and stuff like SP_WhoIsActive) in a nice view; catalogued and kept track of, with baselines, charts, and warning klaxons. Yea, I’d buy that.
I’m new to this whole DBA thing, for sure. Maybe at some point it’ll be like looking at the Matrix; for now though it feels like there are grand opportunities to make things a bit less painful for DBAs. Maybe it’s just not profitable to do so?
I use the Database Mail generation script in a SQL Server Central forum response by Lowell. I should add that to my GitHub 🙂 Also, wow you know a lot for a “new” DBA.
For your last question about a dashboard, at my work we use a program called Solarwinds DPA (formerly Confio Ignite). It’s pretty handy, lots of warnings when things are trending the wrong way, query analysis, etc etc.
We only looked at that and a Dell product before buying DPA (time crunch), so I honestly have no idea if there are better products out there, but it is certainly working well for us.
Ah Microsoft – the same company that made Visual Basic!!! Edsger Dijkstra (one of the fathers of computer science) says “It is practically impossible to teach good programming to students that have had a prior exposure to BASIC: as potential programmers they are mentally mutilated beyond hope of regeneration.”
Now MS asks money for it.
Although MS SQL Server is a good product, my guess is that the results of some of their programmer’s bad habits are still in there. Like no BOOLEAN type in T-SQL even though its part of the SQL standard.
Smells like BASIC