DBA Roles

One of the questions I see in newsgroups quite often is, what exactly does a DBA do? You hear answers that range from “walk on water” to “look at the ceiling tiles counting the number of divots in a given tile.” Of course, the first quote was from a DBA in the field and the second one was from someone who was very annoyed with their DBA.

So what exactly does a DBA do? Many organizations that don’t have a DBA don’t realize how much a DBA actually does do (or can do). Many DBAs are very under-utilized, which further strengthens the complaint that DBAs don’t do anything. Really, it’s hard to classify a DBA in one category. In this section, I’ll show you some of the types of DBAs and their job descriptions I often see in the field. Keep in mind that there are tons of other roles/responsibilities that may not be mentioned here.

Production DBA

The production DBA spends most of his day trying to work himself out of a job. This is because no DBA who wants to keep his sanity would run a backup manually every day. Instead, he would automate it and schedule it using SQL Server Agent or a third-party product. A true production DBA usually focuses on the following base tasks:

  • Installing SQL Server Uses the corporate standard to install SQL Server. May also develop an unattended method of installing SQL Server, including service pack deployments.

  • Installing and deploying databases Uses premade scripts to upgrade and install databases.

  • Creating and implementing disaster recovery plans Based on the business needs of the company (how valuable your data is), finds the best solution for the budget and creates a backup schedule around the plan. Also raises the red flag if there are any vulnerabilities in his company.

  • Testing disaster recovery scenarios Performs regular random drills to test the backup plan and the integrity of the company’s backups. Needless to say, a backup is only as good as your ability to recovery from it.

  • Managing security Works with the security department, the development DBA, and the product planners to determine how much access the application will need to the database. The production DBA is typically the gatekeeper of this access.

  • Sizing out machines Uses the benchmarking evidence or Profiler traces to find out what type of machine will be needed for deployment.

  • Receiving projects from development DBAs Works with the development DBA for a clean handover of a product.

  • Performance tuning Tunes the physical layer of the database. May choose to do this by tuning the file structure and organization, scheduling index rebuilds, and any hardware-type decisions.

One of the key tasks that I purposely did not mention above is data control. A pure production DBA does not control the data. He would only control the physical implementation of the data, not the model or the data inside the tables. He would also not be responsible for writing queries. Typically, if a query is needed to pull data out of the database, an explicit request is sent to the DBA to run since he may not have the knowledge to abstract the data from the database himself. I know very few pure production DBAs anymore. With SQL Server 2000, most have migrated into more of a hybrid role, which I will discuss momentarily.

Development DBA

Development DBAs are tightly integrated with the developers of a project. It’s generally a good idea to have a ratio of three developers to each development DBA if the application is going to have an active back-end database. Some projects that I’ve seen need a one DBA to two developers ratio due to the amount of involvement in the DBA. This type of ratio is typical in projects that use Analysis Services, where the DBA has to create the cubes and ETL process. Here are a few of the typical tasks that the development DBA does:

  • Data modeling Creates the physical data model and modifies the model on an as-need basis. This usually means working closely with the data analyst and product planners. Some development DBAs find themselves also working on a closer level with the analyst to explore what the logical model must look like. This can be very time consuming and in most companies, it is preferred that the analyst create the logical and conceptual models since they know the business better than the DBA.

  • Creating DTS packages This is a new task for a DBA in SQL Server 7.0 and greater. This is where the DBA must create processes to load and scrub data.

  • Creating the installation scripts This of course includes the base installation scripts to create the tables, indexes, stored procedures, and seed data. This also includes any upgrades. Controlling the database builds is the most frustrating and time-consuming part of a DBA’s time. For each installation script that is created, a rollback script must also be created in case of failure and proper auditing must be enabled.

  • Stored procedure writing This is the most controversial item in the job description. A pure development DBA writes, tunes, and modifies the stored procedures. Most DBAs only assist in the complex queries, leaving the simple ones up to the developers. My personal feeling on this topic is that I can help tune ten select stored procedures in the time it takes to write one. At a minimum, though, a development DBA should approve all stored procedures that enter the next environments, such as production.

  • Performance tuning The development DBA handles the performance tuning at the query and index level. He can do this by running Profiler to determine where poorly running queries are and provide any assistance in rewriting the query. He must also look at execution plans to determine where indexes can be built to improve performance.

Hybrid DBA

Since SQL Server 7.0, I’ve noticed a new type of DBA evolving, the hybrid DBA. As time passes, the line is blurring between the two main types of DBAs to where almost no DBA is a pure development or production DBA anymore. This is how the hybrid DBA was born. This type of DBA is ideal because he can walk a project from its infancy to deployment, essentially performing a combination of all the tasks mentioned in the two previous types of DBAs’ portfolio.

This does make for a hefty workload at times and a compromise has to be reached with the development groups to lessen his load. For example, it is nearly impossible to manage the production environment for a larger project and write each and every stored procedure. You should have time to at least approve the stored procedures.

With every generation of SQL Server, Microsoft makes the management of SQL Server easier and easier, fooling many managers into thinking they don’t need a DBA. In the next release of SQL Server (code named “Yukon”), you can expect much of the same. Gord Mangione, vice president of SQL Server at Microsoft, was recently quoted in SQL Server magazine saying, “Our goal with ease of use is to let our DBAs concentrate more on working with the software developers—to really become guardians of data…”

As each release of SQL Server passes, a DBA’s mundane administrative job becomes more obsolete, while the need for a new hybrid DBA grows. The hybrid DBA’s job first came about in SQL Server 7.0 with the introduction of Data Transformation Services (DTS) and has grown even more so with the introduction of XML integration in SQL Server 2000. In Yukon, you will be able to write stored procedures in almost any programming language under the sun. So are you a developer at that point or a DBA?

Shortly after the release of SQL Server 2000, I began to receive call after call from the development staff I support asking how to use certain SQL Server features, such as XML. This made me feel like a dinosaur quickly since I hadn’t learned XML yet, nor was it even on my radar. More and more of these types of tasks are being expected of former production DBAs. The second you take one of them on, you lower the risk of becoming obsolete, and you’re now a hybrid.

The economy isn’t helping the production DBA either. As the economy worsens, companies can’t afford to have traditional development and production DBAs separated. Instead, many companies are combining the two roles into one. I personally welcome this merging. I was accustomed to not having any input in a project in the beginning and then I was handed the project in disarray when it was ready to enter the production room. Nothing is more embarrassing than trying to explain to a new employee that you didn’t create a database that doesn’t have any relationships in it or nvarchar(1) fields. You had to just grit your teeth and create a backup plan and wait for the next ridiculous database.

Now you have the opportunity to get your hands dirty early, walk a database up to production, and then carry it over the threshold. Now that you’re a hybrid, you have a vested interest in the project from the beginning. If you create a crummy database, you’re stuck with it. You also may be in charge of training the staff to use SQL Server’s XML features, DTS, and in the future, the new stored procedure functionality.

Here are a few tips (in order of importance) on becoming a hybrid:

  • Learn XML If there is one technology that a DBA must know in the next few years, it is XML. SQL Server and other products of all kinds are integrating XML into their system. Not being able to speak this language would be equivalent of not understanding SQL in the next few years.

  • Learn VBScript A hybrid DBA will be expected to know at least one other scripting language. These scripting languages will be tightly woven into stored procedures in Yukon, and much of SQL Server will go untapped if you’re not up on these.

  • Learn DTS The minute you cut a two-week project into two hours by creating a DTS package, your stock rises. Developers will think of you as a peer, not a roadblock.

  • Load test data As soon as the physical database model is ready and developers are ready to develop, have a plan to generate test data either through a third-party tool or through your own scripts.

I’ll never forget teaching a DTS class a few months ago to a group of mainframe junkies. A few hours into the class, a mainframer changed his Windows theme to represent a 3270 green screen. I moaned then, but that mainframer was one of my best students as the class went on. He saw then that his technology was slowly becoming less and less used and was seeking new knowledge. I’m certain there will always be a place for mainframers and production DBAs, but what fun will that be?

Organization

With the tasks laid out, you may be wondering to whom would these DBAs report? I’ve seen tons of varying organization charts for each type of DBA. I prefer a central DBA group that supports, develops, and analyzes databases. I’ve also seen where a production DBA would report to the support group (typically the same people supporting the Windows server machines). I don’t like this model because it creates a divide between production and development DBAs. This divide could lead to the production DBA asking the development DBA, “How in the world could you hand me this?”

I see even more controversy when it comes to where the development DBA reports. This type of DBA usually reports to the development organization, which again creates an even wider divide between support and development. These two groups of DBAs should have a tight bond and constantly be sending each other feedback on what each other has in the pike and what the production DBA is seeing in the server room.

Tip 

The best reason to have a central DBA group is that the DBAs can be leveraged across multiple projects, protecting their jobs from individual projects getting cut and making for a more productive environment (fewer overall dedicated DBAs needed). It also becomes quite boring to work on a single project for too long as a DBA.

DBA Scheduled Tasks

As I mentioned before, the monotonous tasks of production DBA work will eventually force a DBA to automate himself out of a job. The following sections cover a core list of tasks that DBAs must automate or do manually. This list is pretty generic, but generally holds true for most environments. Many of these tasks can be automated by third-party monitoring tools.

Daily Tasks

The following tasks should be completed on a daily basis:

  • Check to make sure the SQL Server is still online and that you still have connectivity.

  • Check the NT and SQL Server logs for any errors or problems.

  • Ensure that no SQL Server job has failed.

  • Resolve any problem tickets.

  • Close any outstanding change tickets.

  • Perform the necessary backups, whether transactional or complete.

  • Check the general health of the server (space, CPU utilization, memory) to confirm there are no issues.

  • Track locking issues, including deadlocks, blocking, and lock timeouts.

Weekly Tasks

The following tasks should be completed on a weekly basis:

  • Perform necessary database backups.

  • Remove any unneeded space from the transaction log and data files.

  • Perform any necessary index tuning, including defragmenting the indexes.

  • Execute UPDATE STATISTICS if auto-update statistics has been turned off.

Monthly Tasks

The following tasks should be completed on a monthly basis:

  • Perform necessary database backups (including a complete backup of the OS and supporting third-party application files).

  • Apply any patches or service packs for SQL Server.

  • Run System Monitor to confirm that your server is operating close to its baseline. Update your baseline documentation to reflect this month’s numbers.

  • Perform a complete system restore of the server’s database onto a new server from a random day. Check the health of the restored database afterward by running DBCC CHECKDB.

  • Run sqldiag.exe on your server and document the results into a central repository.

  • Test your alerts to confirm that they still work.




SQL Server 2000 for Experienced DBAs
ppk on JavaScript, 1/e
ISBN: 72227885
EAN: 2147483647
Year: 2005
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net