I’ve recently become a Dad again to a lovely little boy,  and of course the one thing everyone knows about being a parent to a newborn is that you’ll have plenty of sleepless nights to look forward to. But whilst “sleepless nights” is a bit of an over-exaggeration, I have found myself sat up wide awake at 1AM with young Ben in my arms sound asleep. Occasionally I might listen to music (headphones of course!) or have the TV on (sound down, brightness down, subtitles on), but on more than one occasion my mind has wandered to SQL Server. I don’t get to use the time to blog, but this time has afforded me time to think over a subject, and so I’m blogging about a few of them today.
Creating Objects in System Databases
The other night I thought to myself “When is it OK to create objects in system databases?” And I’m not talking about those times that a script is unwittingly executed and objects are created in master when it was meant for a user database. I mean objects thant are intentionally created and are there for the duration.
Lets start with the official line from Microsoft: what does Books Online say about it? I have searched around and the only advice I can find is on the master database, which recommends that you “do not create user objects in master. If you do, master must be backed up more frequently.” Well, you should always be taking regular backups of the master database anyway…
On dba stackexchange, someone asked this very same question, and there was one answer that seemed to reflect the common sense approach, and suggested that questions need to be answered before a decision can be made.
- What happens if, somehow, Microsoft creates an object with the exact same name in a new version?
- If databases are migrated to a new server, how will anyone know that there’s something in master to migrate?
- What happens if your objects corrupt the master database?
- What adverse affects would having extra data in master have on the instance?
- What would happen if an in-place upgrade was performed on this instance? (ie, upgrading 2008 to 2008 R2)
- How would restoring the entire instance from backups be affected?
It’s not just whether you should create system objects, but what type of system objects we are contemplating creating is the system database: Consider also that you probably have the system databases on a separate drive from your user databases, and this disk is likely to be small, database and log files are likely to be on this drive together, and that that the recovery model of the system databases are generally simple. So any thoughts that storing data in system tables can definitely be filed under “bad ideas”.
I’ve created user objects in system databases for the sake of simplicity: is it really necessary to create a whole new database for a stored procedure? Probably not. But is it necessary when designing a schema of tables? Definitely. Ultimately there’s nothing stopping you from creating objects other than your own discretion. And I guess that is where the trouble starts…
There’s no Such Thing as an “Accidental” DBA
Despite what careers officers around the world make you think, very few people at 14 know what it is they want to do for a living. And I can guarantee you that the same people at twice or three times that age still don’t really know. Most people fall into something, and if you’re lucky, you’ll end up doing something you like and that you do not suck at.
I never aspired to be a DBA. When I graduated from a decent university with a degree in Computer Science, I knew I wanted to work in IT, but I equally knew that I did not want to work as a developer. But some years ago, the company I worked for hit a hard time, and with the team in need I took on more roles and found that I enjoyed learning about databases and database engines, and that has led me to today where I blog about SQL and am even going to be presenting at the SQL Supper User Group in January. But at no point when I was covering as a DBA did I ever use the phrase “accidental DBA.” It just sounds daft. At what point do you stop being a DBA by accident? To me the phrase suggests a sort of abrogation of responsibilities, like if something goes wrong it’s not your fault because you’re only the DBA “by accident”. If you really need a title that suggests that you’re only the DBA because there’s no one else to do it, I’d prefer the term “de-facto DBA”. And if you’re the DBA under duress, and you don’t like it, talk to your boss, and if that gets you nowhere, talk to hiring managers!
My Number One SQL Server Feature Request
If you were to ask me what new feature I would like to see most in SQL Server, I would immediately say “logless tables”, and maybe after a slight hesitation I’d say “logless databases”. By “logless” I mean that data written to the tables is not logged. I’ve worked with data warehouses for a number of years, and there are legitimate scenarios where logless tables would make sense. For example when using staging tables for partition switching, if the loading of the staging tables fails chances are they’d be dropped and recreated, so there’s no point in logging something that is essentially a oneshot data load. Sure, bulk logged will help you in minimally logging to the log file, but it still doesn’t make log backups any smaller as the pages affected will be included in the backup file. And you’d be surprised how many people think that the simple recovery model means that no logging takes place at all, when the reality is that the simple mode still logs, it just flushes the log file upon the transaction being committed. Marking tables for logless activity would mean faster performance. Logless databases…..well, I can see a use for them, but the idea is far riskier than logless tables.
At any rate, if the feature was enabled, I’d want the keyword not to be LOGLESS, or UNLOGGED, I’d want the keyword to be a keyword already used in isolation levels in SSIS: CHAOS. I think this use of keyword would actually really explicitly state just how risky it is to implement logless activity (as an aside the worst keyword in the whole SQL Server engine is NOLOCK; it’s such a misnomer and the cause of many an argument.) Using the CHAOS keyword would also be great for interviews: when interviewing for a role you can ask if they use the CHAOS keyword anywhere, and why. This would really help out in determining whether it’s the kind of place you’d want to work at or not!
Well, that about wraps it up for me, what feature would you like to see in the next version of SQL Server?
We named him Benjamin Frederick Bixente Lee, or "Ben" for short. The first two names are after the grandfathers, but "Bixente" requires a bit of an explanation. It's an old French/Basque name that I've always liked for years, and whilst it may be a little unfair to give him such a bizarre name, you can always lose a middle name as you grow up (just ask my wife, she deliberately omits hers from any document as she's got older.) I guess Ben should always be grateful that I didn't have it all my way and have his first name as "Bixente", as unusual first names can be irritating (again just ask my wife.)