In an earlier post, I detailed a short list of things that tripped me up starting out as a DBA. Some of these are things that trip up developers, others are things that trip up DBAs. Whether I was the cause or the one that had to fix it, I was involved, somehow.
This continues that list. Again, there are no answers here; only questions.
Why are the pains of using NVarchar(MAX) not shoved down a developer’s throat by SSMS? Developers understand data structures; It’s easy enough to visualize what data can fit on an 8K page (or some representation thereof), so why doesn’t SSMS warn the developer the same way the compiler does? “Hey, using NVarChar(Max) means your pretty little indexes won’t be awesome.”
Why doesn’t NOLOCK generate a similar warning? It’s a bad idea in most cases and doesn’t do what you expect, so why not hit developers over the head with that detail?
Why don’t uses of Table Variables generate warnings about bad statistics?
Why aren’t execution plans and Statistics automatically generated for each query run in “developer” mode? Developers are used to seeing those sorts of metrics; and with a few simple default changes SSMS could get developers to start to pay attention to things that really matter.
Views. Why doesn’t SQL Server complain when you use nested views? Seems like something to tell a developer. I spent two days unraveling nested views just to find out the de-nested view generated a different query execution plan than the nested view.
Shouldn’t SELECT * generate a warning EVERY TIME A DEVELOPER USES IT? It shouldn’t matter if it’s in the query window or not — If we subscribe to the idea that developers need to know this stuff, why aren’t we beating them over the head with it?
Why does SQL Server let you set up a LINKED Server as SA without some sort of “You’re an Idiot” warning? In fact, why does SQL Server let you use SA for anything without some kind of signal that it’s a bad idea?
Why does changing the name of a SQL Server box (to the name of a box that is still in AD/DNS) result in dire delayed pain if you’re using Windows Auth? It works, it works, it works, and then all of a sudden: No one can log in using Windows Auth (I was bitten by this twice before I realized why the renaming did it).
Why doesn’t SQL Server use the Begin/End Transaction pattern by default for SSMS queries? Just put it in the template; don’t give people the option of choosing the implicit transaction settings. Some junior DBA may forget what is implicit and what isn’t, and end up filling the transaction log with uncommitted transactions.
Why does SQL Server throw the principle of least privilege out the window? Why doesn’t it make it hard to do something stupid security-wise? Good software helps its uses fall into the pit of success, not the pit of “We just Got Owned”.
Along the same lines, there should be warnings for unparameterized queries as well; make the DBA turn them off; don’t just let it happen.
For that matter; why doesn’t SQL Server have some generated tables/template scripts for various business needs? I recognize it’s a general purpose Database; but Microsoft doesn’t have some idea about Stored Procedures or tables for User Account Management, or half a dozen other business purposes. You can’t swing a query plan without hitting templates for most Microsoft products, but for SQL Server, they’re conspicuously absent.
Why are our options for importing data into SQL Server to either use SSIS (that you pay for), BCP, or some application code? Why is it so difficult to import a CSV file or XML file into SQL Server? (Unicode will get you every time).
Editor’s Note: Apparently there’s a way to get SSIS for free. It’s also available with the Express version; and the issues I talk about here are available in Express.
Why doesn’t Microsoft provide SQL Server as its own bundle with Windows Server set up to reasonable defaults (like Instant File Initialization; and read/write access to what SQL Server needs)? It’s not like there are people out there that think, “I’d love to use SQL Server, but I’d rather put it on Linux”. Microsoft knows what Windows Server needs to be configured as to use SQL Server; why not bundle it?
Why is it so painful to script SQL Server privileges for Read/Write access to the filesystem? Part of my DR work has been to automatically stand up new servers when needed, and I spent more time on getting privileges to work than any other part of the task (including writing the automated restore code to restore 200+ databases from backup to a single server when the system drive has lost all its data/MSDB is corrupt). I literally spent two days on that code (including unit tests) but a better part of a week trying (and failing) to get user access rights set up through scripting in AWS.
It’s 2015; if it weren’t for AWS, I’d have set up a few dozen more SQL Servers than I’ve had to — why does it take the cloud to make such a thing scriptable?
Why isn’t there a better API for Windows Server + SQL Server administration stuff? Do they still envision a DBA clicking on settings? Really? My options are T-SQL, or PowerShell that emits T-SQL.
Why aren’t there giant warnings on Triggers? Why are they so hard to debug? Sell a bundle; SQL Server + MSMQ or your favorite Queuing service. While you’re at it, give me a better API into SQL Server.
Why did it take until 2014 for Microsoft to have (nearly) sane RAM licenses?
High Availability, Disaster Recovery. As part of a startup, my options are: Write it myself, migrate to Postgres, or pay a lot of money for Microsoft to do it. We’ve punted on HA and focused on DR for now; but that’s a similar scene: Write my own or pay a lot of money.
Why doesn’t Microsoft give some sort of guidance about usage patterns for different databases? In a truly multi-tenant environment, it’s almost impossible to find out who the real users are. If it weren’t for the Brent Ozar scripts and an internet full of small utilities it’d be nearly impossible to find that out easily.
Why are all the important DMVs, procedures, and tables hidden from view? Shouldn’t those be part of the default view for a DBA?
Deleting a database is not always a Severity 16 error. Sometimes it’s a non-event, and sometimes it’s a world stopping event.
Pager? Really? It’s 2015; is that really still a thing?
Why doesn’t SQL Server emit a warning when a database in full recovery mode hasn’t had a log backup in a predetermined amount of time (an hour seems reasonable)?
Why aren’t orphaned users front and center when restoring a database? Why doesn’t SQL Server try to restore users if it finds a match?
Wait Stats. SQL Server knows which are bad and which aren’t; or which are indicators of something untoward; but out of the box there is no notification mechanism. Sane defaults, people. Sane Defaults.
If SQL Server fails to compile a query; why doesn’t it send the query to the error log and tell us exactly which query it couldn’t compile? It has the query text!
Why doesn’t SQL Server give an easy option for a user to go in and out of SA mode? Sometimes I want to drop to SA mode (think: sudo), but most of the time, I don’t want my Windows user to be a SysAdmin; and I defintely don’t want to have to execute a custom command to connect to SSMS to log in as a Windows User that has SA.
Why isn’t there the ability to generate an empty database? If you choose to put tables in Model, those tables get created for every Database you create. If you want an empty database, you’d better keep the backup of an empty database before you mess with Model. Don’t delete it, or you may find yourself setting up a new SQL Server instance just so you can have an empty database.
Author’s Note: These are a few more things that have tripped me up as a DBA. In the coming weeks, I’ll have answers for some of these, but I may not. This blog post is mostly a theraputic look at DBA work; I’m really not angry (even though I may come across as such in this series). DBA work means detective work. It means visualizing the problem with pieces of evidence scattered across a large surface area. It means having appropriate tools in place before something goes wrong; and above all, it means being able to take a complex system and making it work.
The only thing I can compare it to is to understand the basis of flight, and then be asked to pilot many different types of aircraft (including helicopters) by instrumentation alone, with all of those instruments hidden from view until you know exactly which one to ask for.