On the Eleventh Day of Christmas…2011
On the Eleventh day of Christmas I Give Some Tips to Thee…11 SQL Never Not Dos!
Ten Quick Hit Thumbscripts
A Nine Hour Work Day
I FORGOT WHAT EIGHT WAS FOR…
Seven Reports to Start My Day
Six Gb of RAM
Five Volumes Minimum…
Four Words to Live By…
Three Training Events…
Two Twitter Handles…
and Mirrored Disks with Parity
.
.
We Three Things
…a Database Administrator should never fail to do against a production SQL Server
Oh, you thought we were on eleven? Don’t you know your binary? Plus I couldn’t come up any ideas for 11.
DBAs are responsible for a myriad of tasks for any relational database system. These tasks result in hundreds, if not thousands, of decisions we need to make daily. There are three tasks that should be enacted daily though that should never be lost in the shuffle of getting through the day.
Backing Up Your Databases
Not just your user databases. I’ve seen many systems where the system databases (master, model, and msdb) are not being backed up. I’ve had software vendors tell me that they didn’t need to be backed up – it required too much overhead, they could be simply copied from another server… so many flat-out wrong information that companies without established and competent DBAs would simply take for true. The only databases I exclude from daily scheduled backups are any databases being used for reporting or some other process where the database is refreshed regularly from another version of the database. In the case of these databases I make sure they’re in Simple recovery to protect against any unwarranted or unexpected transaction log file growth that may result from a database in Full recovery without associated transaction log backups.
Monitor Database and Server Space
In conjunction with a warning against placing all your files (data, log, index, backups) on the same volume as your operating system you should monitor all volumes for your SQL Servers for disk space availability and monitor the data and log files likewise to manage unexpected auto-growth cycles (if auto growth is enabled). When a transaction log file is extended through auto growth processes or manually the log is unavailable for writes which means your users are not going to be able to perform any inserts, updates, or deletes until the process completes. Likewise if the volume hosting your log file fills due to unmanaged log file bloat the same issue occurs until space is allocated or reclaimed. A good DBA monitors space consumption. A great DBA maintains a system that allows for monitoring of space over time so (s)he can make forecasts for future space needs based upon past growth/use and proactively ensure that space exists and is able to be granted should the need arise. I do this through collecting file size information from sys.database_files as well as various msdb tables relating to backups for each of my instances through SQL Server Integration Services. I then build reports that are generated each morning that provide me with information on those databases exceeding thresholds for file consumption as well as volumes where space is also violating those thresholds. I can then drill down and display graphically the file size and consumed bytes since file creation to gauge usage over time and determine how much to grow the file when the need arises.
Protect Your Data
Only grant the rights needed to only those who need it. It’s not the easy way – it’s the right way. A solid understanding of what database roles actually allow users assigned to those roles to do is very important. More times than not I’m requested to grant “database owner” rights to a single user for a database. I then spend hours and countless emails explaining why this is not the right approach. Sure, it works. The users will be able to do whatever they need to do in the database (and more so). I then have to outline all those things that constitute the “and more so”. When I finally get that laid out. I’m usually confronted with the “but we limit what the users can do through the application”. This then results in my explaining how easy it is for anyone to download SQL Server Management Studio from Microsoft (they’re practically giving it away). At that point the user can do all that “and more so”.
No, the proper approach is to determine what objects the user(s) need rights to and what those rights are (select, insert, update, delete, execute, modify and so forth) and create multiple roles with those rights granted accordingly. I always approach this from an angle of using Active Directory authentication. The Security Team (or whatever team in your organization) should create an AD Group and assign individual logins to that group. As the DBA I then create a login in SQL from that AD Group and then create the appropriate users from that login inside the database. The user is then assigned to a database role that matches their needs. As users transition in and out of their roles or come and go in the company the DBA is typically not made aware of such activities. With using the AD Group and associated memberships we don’t find ourselves with a graveyard of old logins on our SQL Servers from employees past, or rights granted to users that now have no reason to have access to the data should they still work for the company but perhaps they’ve been promoted or demoted to another role. It also prevents the already-busy DBA from the constant inundation of creating/revoking rights.
You’ll notice I’ve made no mention to those extremely important tasks relating to performance – DBCC CHECKDB, index fragmentation, statistics updates and so forth. Those are vital to performance, but data is still recoverable, stable, and secure if none of those tasks occur.