Udostępnij za pośrednictwem

Who’s the DBA Anyway? It depends.

  • Artykuł

Every now and then I get questions from DBA’s if I can tell why application Y needs sysadmin permissions. Or why it needs to be local admin on the SQL box, or why the database has to be named XYZ, or why no other databases are allowed on the instance, or why SQL 2000 is the only supported database.

These questions are all related to installation most of the time. In some cases you can remove sysadmin privileges after installation, you can add other databases and you can move the database to SQL 2008 R2. Sometimes I get questions that are different. More like; why does application Y want to do it’s own index maintenance or according to the manual we should have 50% free space in the database. Here is a list of these questions and my view on it.

  • The application needs sysadmin (most of the times combined with securityadmin, backupadmin and other admin privileges) : That’s a no brainer. And that means you too! As a DBA you don’t need to be sysadmin. CONTROL SERVER will give you pretty much the same powers and you can always elevate to a sysadmin account if needed. And if even you are not sysadmin why should anyone else be!
  • The application need local administrator privileges: I’ve seen this happen where an application wanted to attach a database on the remote SQL box during installation. To do this it would first copy the files to the SQL directory. In order to do this it used the administrative shares (c$ etc.). Administrative shares can only be accessed by.. yes, Administrators. Every DBA should be able to attach a database. Use that!
  • No other databases are allowed on the instance: There is no excuse for that. I’m a big fan of consolidation. By consolidating your SQL databases you have less instances to manage and less licenses. Why on earth do application check on this?
  • The database has to be named XYZ: Ok. I’m not a real developer but how hard can it be to put the database name in a configuration file? I’ve seen applications with hardcoded database names or applications that would resolve the machine name to the IP-address. Just making sure the application works when DNS doesn’t. Until you change the IP-address of course.
  • SQL 200x is the only supported version: Of course it is. Because you put it in your code: “If db-version not equal to 2000 FAIL application.”. The only purpose is too make sure you upgrade to the next version. Ok, that depends, but most of the times there is no need to stay on older versions.
  • There is no need to defrag the indexes, the application is taking care of it: That’s a tricky one. I don’t like applications that check if indexes are dropped or created and change that back to their defaults. The need for an index can change over time or depending on the actual workload. For defragmentation of indexes it can be true that the application knows best when to do that. Rebuilding or reorganizing indexes will cause use resources. This can be locks, be it short or long, but it can be. Maybe there are tables that cannot be locked or have a severe performance impact when locked. This would be a situation where the application knows best. On the other hand, if the application is not performing and you can blame the fragmented indexes I’m sure the other admins will let you defrag the indexes. But always try to find out why decisions are made, don’t just follow the manual.
  • Databases should not be set to Autogrow: There are manuals that just state that. No context whatsoever. And agreed, as a best practice you should never let your databases autogrow. You as a DBA should identify trends and grow database files ahead of time during off hours.But Autogrow can also be a last resort. What if a database is growing faster than expected? At least make sure that you are aware of auto growths and correct these if needed. Especially for database logfiles. Ideally these should never grow.
  • The databases files need to have 50% free space: Ok. Why? 50% can be anything, 50MB, 50GB or even 50TB. If you haven an operation that incidentally needs more space, just calculate the space needed or show the calculation. Keeping 50GB free space is not efficient and will keep most SAN admins screaming everybody is taking up space and never using it. There is no unlimited disk space and it should be used wisely.

I’m not an Exchange person, so I will not tell an Exchange administrator how to configure Exchange. And if SQL would require some setting on Exchange I would validate this with an Exchange administrator. And if he’s not comfortable with the setting, neither am I.

Most of the times the DBA knows best. And a DBA has take care of more databases than just one. And they all have to be manageable with standardized procedures. A SharePoint database best practice or a System Center database best practice are not always a DBA best practice. It should be an open discussions where everyone should have a clear point of view of what is needed and why. “Because the manual says so” is not a valid reason!

The answer; “It depends.”. As always with SQL Server there is no right or wrong (unless it’s shrinking your database files, that’s wrong). There are best practices and these should be followed as much as possible. But if you know what you are doing you can take the road less travelled.