Online learning

​fREE

SQL DBA SCHOOL

Founder Abubeker Refaw

The Expanding Role of a DBA The role of the database administrator has been changing slowly over the past few versions of the SQL Server product. Beginning with SQL Server 2005, this slow transition of the DBA role has been accelerated immensely. Traditionally, a DBA would fit into one of two roles: development or administration. It’s much tougher to draw a line now between DBA roles in SQL Server 2008. In addition, the new role of Business Intelligence DBA is on the rise. As lines blur and morph, DBAs have to quickly prepare themselves to take on different roles. If you don’t position yourself to be more versatile, you may be destined for a career of watching SQL Server alerts and backups.


Production DBA


Production DBAs fall into the traditional role of a DBA. They are a company’s insurance policy that the production database won’t go down. If the database does go down, the company cashes in its insurance policy in exchange for a recovered database. The Production DBA also ensures that the server is performing optimally, and he or she promotes database changes from development to quality assurance (QA) to production. Other tasks performed by a Production DBA include the following:


❑ Install SQL Server instances and service packs.

❑ Monitor performance problems.

❑ Install scripts fromdevelopment.

❑ Create baselines of performance metrics.

❑ Configure the SQL Server optimally.

❑ Configure/implement high availability plans.

❑ Create\implement disaster recovery and scalability plans.

❑ Ensure that backups have been run.


Since the release of SQL Server 2000, there has been a trend away from full-time Production DBAs, and the role has merged with that of the Development DBA. The trend may have slowed, though, with laws such as Sarbanes-Oxley, which require a separation of power between the person developing the change and thepersonimplementing thechange. In a large organization,a Production DBA mayfall into the operations department, which would consist of the network administrators and Windows-support administrators. Placing a Production DBA in a development group removes the separation of power that may be needed for some regulatory reasons. It may create an environment where ‘‘rush’’ changes are immediately put into production, without proper inspection and auditing.


Development DBA


Development DBAs also play a very traditional role in an organization. They wear more of a developer’s hat and are the developmentstaff’sdatabase expertsandrepresentatives.This administrator ensures that all stored procedures are optimally written and that the database is modeled correctly, both physically and logically. He or she also may be the person who writes the migration processes to upgrade the database from one release to the next. The Development DBA typically does not receive calls at 2:00 a.m. Other Development DBA tasks may be as follows:


❑ Model an application database.

❑ Create stored procedures.

❑ Develop the change scripts that go to the Production DBA.

❑ Performance-tune queries and storedprocedures.

❑ Create data migration plans and scripts.

❑ Serve as an escalation point for the Production DBA.


The Development DBA typically would report to the development group. He or she would receive requests from a business analyst or another developer. In a traditional sense, Development DBAs should never have modification access to a production database. They should, however, have read-only access to the production database to debug in a time of escalation.


Business Intelligence DBA

The Business Intelligence (BI) DBA is a new role that hasevolved due to the increased capabilities of SQL Server. In SQL Server 2005, BI grew to be an incredibly important feature set that many businesses could not live without. The BI DBA is an expert at these features.


BI DBAs may have specializations, just like normal SQL DBAs. A Production BI DBA will perform the same functions as the Production DBA: installs, service packs, deployments, high availability, performance tuning, and backups. The only difference is that the Production BI DBA will be paying closer attention to SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), and perhaps Proclarity, Business Scorecard Manager, and Performance Point Servers.
Development BI DBAs specialize in the best practices, optimization, and use of the BI toolset. In a small organization, heorshemaycreateyourSSIS packagestoperformExtractTransformandLoad(ETL)processes or reports for users. In a large organization, developers create the SSIS packages and SSRS reports. The Development BI DBA is consulted regarding the physical implementation of the SSIS packages, and Analysis Services (SSAS) cubes. Development BI DBAs may be responsible for the following types of functions:


❑ Model\consult regardingAnalysis Services cubes and solutions.

❑ Create reportsusing ReportingServices.

❑ Create\consult around ETL using IntegrationServices.

❑ Develop deployment packages that will be sent to the Production DBA.
Organizationally, the BI DBA most often reports to the development group. In some cases, Analysis Services experts may report to the analyst group or the project management office. In some small organizations, the BI DBA may report directly to an executive such as a CFO.


Hybrid DBA

The most exciting role for a DBA is a hybrid of all the roles just mentioned. This Hybrid DBA is very typical with smaller organizations but is becoming popular with larger organizations as well. An organization with high turnover may want to spread its investment over many Hybrid DBAs instead of relying on specialized roles.


Organizationally, you may see Hybrid DBAs reporting directly to the product organization or to a specialized DBA group. No matter where these DBAs report, each typically has a slate of products that he or she supports, performing every DBA function for that product. Organizations that rely on Hybrid DBAs should have adequate backup personnel to reduce the organization’s risk if a Hybrid DBA leaves the company. Also, this DBA should never install his or her own changes into production.


Ideally, for regulatory reasons and for stability, the DBA’s backup DBA should install the change into production. That way, you can ensure that the DBA who installed the script didn’t make ad hoc changes in order to make the change work. We cover much more about this change-management process.


The only role of a Hybrid DBA that’s questionable is development of stored procedures. In most organizations where we see this role, the Hybrid DBA does not develop stored procedures. Instead, he or she creates difficult stored procedures or tunes the ones causing issues. The developer working on the application develops his or her own stored procedures and then provides them to the Hybrid DBA to package and proof. The main reason for this is that the DBA is too taxed for time, working on other functions of the database.


SQL Server 2008 Architecture