In DBA world, usually two types of DBA are defined depending on what a DBA is focusing on, i.e. development DBA or production DBA, As for the responsibility of the two types, here is a good summary article http://www.craigsmullins.com/dbta_023.htm
However I believe now we should add another type, Test DBA, and I define the TEST DBA as the following:
A test DBA is dedicated DBA who is responsible to design test cases to stress test any target system, and also is reponsible to diagnose any complex issues with a well designed test framework, and last but not the least is responsible to test any off-shelf (or open sources) database tools.
In more details, I outline the following primary tasks for a test DBA
1. Hardware/system pre-deployment test: the test DBA needs to set up and test the performance baseline for a new server, such as raid configuration impact to performance, (my recent experience tells me that I cannot take anything for granted, for example, RAID 10 is always better than RAID 5, in theory, YES, but in reality, esp. in my environment, on a same server, with same disks, RAID 5 outperformed RAID 10 in both read and write performance, and the system adminstrators are still checking why); a test DBA also need to check whether the new hardware can meet the expected future business requirements.
2. Hardware/system evaluation: when we need to retire our current production servers or other hardware such as SAN disks, we usually have to evaluate among various vendor products to find the best candidate in terms of benefit and cost. A test DBA needs to provide the valid data to facilitate the decision.
3. Application database component test: In a complex environment, it is worthwhile to test those critical stored procedures / views / queries from DBA perspective by tuning indexes, configure server options, adding hints, playing around with temp tables and table variables. A test DBA will be able to read the query plan and I/O statistics and make corresponding changes or recommend proper suggestions to developement team.
4. Off-shelf / open-source database admin tools trial and test: this is to facilitate management / production DBAs to get the most suitable tools to monitor and manage the production enviroments.
5. Test case library build up: test case library can be treated as an invalualbe company asset. For example, previous test cases may be used to check against the code performance after the application is revamped, also previous cases can be used to check against any new hardware environments. A well-built test case library can greatly improve the efficiency of the whole application ecosystems.
Skills a Test DBA needs:
A test DBA needs to master various test tools, for example, SQLIO, SQLIOSim and IOMeter, SQL BPA
A test DBA needs to have deep knowledge of sql server tuning (index, isolation level, server configuration options, DMVs etc) to be able to design qualified test cases / environments.
A test DBA is better to master some reporting tools, because you need to present what you have found. I think SQL reporting service is a good skill for a test DBA to learn.
Why do I come to this "Test DBA" idea?
In the last two days, I was asked to do a stress test on a SAN box of RAID 10 type in a new server which will be used as a production server a few months later, I use SQLIOSIM to do the various tests with different configurations, and the interesting thing is oringially we had RAID 5 in the server, and I happened to test RAID 5 before it was reconfigured to RAID 10, after I stress-tested RAID 10 (memory was even upgraded to 16 GB from 8 GB when RAID 5 was there), I did a comparison of the results of RAID 10 and RAID 5, and was surprised to find RAID 5 beat RAID 10 in almost all areas, such as IO response time, drive level read / write time etc. Due to the lack of documented information for SQLIOSIM, it is a pain to do all the tests and then analyze the results. I originally even thought to start performance counters while doing the stress tests and then corelate the performance counters with the performance results in diffeernt test cases. I finally gave up this idea due to time constraints (forget to mention I am currently a production DBA that needs to respond to any requests ASAP), and this leads me to think we need a DBA who can be dedicated to this type of test work, and then document each test case, test trails, test environment, test result to build a test case library for future use.
Of course, there are overlaps among different DBA types, but I believe DBA work scope is broad enough these days that demands dedicated people with specialized skills in different sectors of DBA work spectrum.