SQL Server Experts: Part II - Stress, Pet Peeves, Roles and Responsibilities of a SQL Server DBA/Developer
3. What is the most stressful part of your job when working with SQL Server?
Cristian: For me nothing about SQL Server is stressful except people that call me to help them cause their hard drive just failed and they have no backup.
Andrew: Implementation from dev/test to live. There are so many variables to worry about and there are usually security issues that don't always come out in test.
Namwar: Firstly, using SSIS (previously known as DTS). It is still not mature enough. You can have memory errors, package corruptions etc. Secondly, managing multiple versions of object (SP,view, trigger, UDF) code.
Brent: The most stressful part of the job is always, always, always being on call. When someone's application goes bump in the night, they're going to want the DBA on the phone just to make them feel better. Until proven otherwise, developers seem to suspect the database is at fault, and it's the DBA's job to prove otherwise.
Denny: I would have to say that the most stressful part of my job as a Database Administrator is dealing with angry managers when there is a system problem or system crash. I have a tendency to get a little cranky when my systems don't work correctly, and so far all my managers have learned early on that when something is broken asking me for status updates every few minutes is less than productive.
Brian: Dealing with service pack deployments.
Chris: Working with non-technical Managers when I am working through a Crisis.
Pinal: Working on production environment during project release day. Lots of things are usually going on when release is taking place and debugging any issue on production server is always challenging.
4. Production DBA, Development DBA and SQL Server Developer ? Are these roles inter-changeable or do you think that each role is specialized, that there is little chance of crossover.
Cristian: You cannot master SQL Server if you are not familiar with each of the three roles. I would also add programming and networking knowledge.
Andrew: I think you should be able to do 60-80% of the other roles no matter which one you're in. Dev should closely mirror production and actually in today's world, dev and test should be virtual machine based on production. Development for me has been in business intelligence so this is specialised but a BI developer has to have a very good understanding of teh DBA role to deal with scale and get performance.
Namwar: I would say neither these roles are completely same nor they are completely separate. Mostly all these roles require knowledge of database tuning, management, availability etc. On the other hand, they have slightly different responsibilities:
Production DBA: They are more focused for keeping the system running at optimum. Since sometimes they just got already running systems and cannot suggest fundamental changes in database design therefore, they have to live with it and needs to make sure whatever implementation they have it should run smoothly.
Development DBA: They are more involved in designing the databases and can impact the changes in database structure. They have a good chance to optimize the system at first place instead of trying to optimize it after having issues in production.
SQL Server Developer: They are more towards writing TSQL code and should have good knowledge of efficient data manipulation techniques. Most of the time they do not know much about physical aspects of databases and servers.
Brent: These roles are interchangeable at small shops (less than, say, 25 SQL Server instances) but beyond that, the roles quickly diverge and have very little overlap. Today's production DBA at a large, 100-server shop writes very few stored procedures from scratch, and today's development DBA at that same shop does very little disaster recovery planning. When you're managing 100 database servers, tasks like disaster recovery, backup & restore, storage planning, SOX compliance, and so on are a very full time job for the production DBA.
These roles diverge even faster with each new release of SQL Server: Microsoft adds more features to new technologies like SSIS and SSRS, and it's tough for a single DBA to master all of those technologies plus do their regular production server maintenance. With the release of SQL Server 2005, I decided to focus on just one technology (the database engine itself) and get better at that one aspect. I recommend that same path to other DBAs - get great at SQL Server *or* SSIS *or* SSRS, but don't try to take on the entire portfolio. You'll spend your whole week learning, not delivering.
Denny: I would have to say that each of the three database roles; Production DBA, Development DBA and Database Developer are all specialized, but to fill any of these rolls well, you should have at least a passing knowledge of what the others do. As a DBA you really can not effectively tune a stored procedure for a developer without knowing the T/SQL which is being run, what it's suppose to do, and why it needs to do it. As a Database Developer you can't write effective queries without knowing how the indexes are laid out, and how the index settings are going to impact the insert and update performance of the queries.
Brian: Production and development DBAs have similar skill sets, but in larger organizations they should be separate roles. SQL Server developers can transition through the development DBA ranks to be a production DBA but while someone could do both of these roles, it's hard to do them well in a complex configuration.
Chris:There is some cross over, A P-DBA should know how to performance tune, and write SQL to accomplish Administrative tasks. However, I think other then that there really is fewer tasks that cross over.
Pinal: I just think they all are same. Everybody can take over each others job very easily and under the hood they all have same skills.
5. Name 2 exciting, new SQL Server features either in SQL Server 2008 or is upcoming
Cristian: Resource Governor and Extended Events.
Andrew: Policy Management to control and monitor the whole estate, and filestream to store large chunks of unstructured data so that they can be rapidly streamed and still be part of the database. I am working with Mercedes McLaren on using this technology to store car telemetry from Formula 1 races.
Namwar: 1. Data compression feature: If used properly it can great increase the data manipulation.
2. New Data Types: HierarchyID, DATE (only date), TIME (only TIME) and best of all Spatial Data Types (this will be really a big addition and will open up the stream of new type of geographical applications)
Brent: First, SQL 2008's data compression rocks. Sure, disk space is cheap, but disk performance is not. Compressing the data means that we can read less data off the disk, thereby reducing the I/O subsystem loads. CPU cores, on the other hand, are still getting cheaper every year, and it's easy to justify spending some of our newfound cores doing compression & decompression.
Second, and maybe not as sexy, there's the better auditing & compliance tools. Sarbanes-Oxley compliance requirements were tossed at database administrators, making our jobs tougher without much more budget money or man-hours. Microsoft's making compliance easier for us, and while it's hard to get excited about it, anything that makes it easier for me to take vacation is a win in my book.
Denny: Two of the features which I think are among the most exciting features of SQL Server 2008 are the Resource Governor and the filtered indexes. The Resource Governor allows the DBA to decided just how much CPU and memory a pool of users can use on the system in order to ensure that there is always enough CPU and memory available for business processes; preventing reporting and other batch operations from taking all the load of the SQL Server.
The filtered indexes is an amazing new feature. It allows you to create your indexes against a table, but only include a subset of the records from the table. As an example, say that you have a table where the column you want to index is 60% NULL values, and when ever you are going to query the table against that column you will always be giving it a value. You can now tell the index to only index the records in the table which have a value in the column, leaving out the 60% of the rows which have a NULL value in the column. This makes your index ~60% smaller meaning that you can load it from disk faster, and it takes less space in memory to keep the index in cache.
Brian: Transparent data encryption and policy-based management.
Chris: Data Compression - I am not sure how exciting this is. I am curious to see how this impacts a production database and the processors. Policy Based Management
Pinal: PBM (Policy Based Management ) would make DBA's life easier. Data compression
The concluding part of our discussion will be posted tomorrow. (Its now available)
OdinJobs Releases Job Market Statistics For 3rd Quarter Of 2008