Microsoft SQL Server is a mature and broad technology platform that supports a diverse set of careers - this blog post is an attempt to provide technical detail to my personal theory on careers in the Microsoft SQL Server world.

A mid-tier SQL Server professional who may carry the title "Database Administrator" could find career traction with high-level skill in only three or four of these categories. It would be a rare accomplishment to find someone with honest expertise in all nine of these buckets, and most mid-tier SQL Server professionals have significant experience with no more than six of these roles.

It is also important for any IT professional to be aware of his/her limitations. We should all "know what we don't know," so this blog post is an effort to quantify these items. It is also very likely that the modern "DBA" possesses skill sets in .NET and other surrounding technologies which I do not aim to include here.

This list is NOT intended to be an exhaustive list of all possible database topics, SQL Server features or Microsoft products, though detail is provided when necessary.

Database Administrator

• Typical responsibility areas include but not limited to
• SQL Server backups and disaster recovery, backup schedule planning with SQL Server features or third-party enterprise backup software, point-in-time recovery
• Backup and storage administration, off-siting of backups and documentation of RPO/RTO, including the remote backups functionality of AlwaysOn Availability Groups, Transaction Log Shipping, Database snapshots
• Built-in and custom-written maintenance plans for backups, index maintenance, historical log retention
• Database Replication, including performance optimization, handling DDL changes and security
• Database security administration, configuring services and service accounts in SQL Server Configuration Manager, working with developers to implement column encryption, working with network admins to implement Kerberos authentication if needed
• SQL Instance metadata monitoring with third party tools, xevents, perfmon, DMV’s, looking at wait statistics, authentication errors, autogrowth events, deadlocks
• Configuring database mail, operations, log collection, and coordinating response to SQL Agent Alerts for severe database errors
• Windows Server configuration optimization, page file placement, power options, guest VM optimization, local server security, firewall, antivirus configuration
• Filegroup and file management and drive allocation design, including TempDB file decisions
• Enterprise features like Transparent Data Encryption, SQL Audit
• Multi-instance SQL servers, port configuration, network protocols, connection methods
• Policy-based management, using SQL Server Central Management Server
• SQL Server administration and task automation with PowerShell

Database Deployment Administrator

• Production change management, change management system processing, and “clicking the button” for someone else’s deployment code, enforcing preparation for and performing rollbacks if necessary
• Enforces IT Operations procedures and governance, production-level security 
• Database solutions, knowledge of TFS or similar source control for working in a team environment
• Code Review and approval for development teams 

High Availability Administrator

• Database or instance failover technologies including Log Shipping manual interventions, Database Mirroring, AlwaysOn Availability Groups
• Windows server clustering and storage configuration, quorum settings, optimizing the network stack 
• Manual failovers for maintenance and migrations, design synchronous/asynchronous and automatic failover strategy 
• Work with developers and network administrators for connection string maintenance for migrations, failovers, federation of data, consolidating servers into one instance 
• Work with SAN administrators and network administrators to optimize the physical IO subsystem and SQL Server storage.
• Assist with load balancing efforts with application/web server including Reporting Services scaleouts
• Work with virtualization server admins for high-availability options at the VM level, guest VM optimization for SQL Server
• SQL Server administration and task automation with PowerShell, especially for failovers

Database Developer 

• Performance tuning of TSQL queries using knowledge of internals of SQL server, working with developers to improve code-first applications, writing stored procedures and functions with a high level of awareness for performance, transactions, and error handling
• Complex TSQL query writing including PIVOT/UNPIVOT, windowing functions, distinct/duplicate record detection, data scrubbing
• Isolation levels, including snapshot isolation, with an eye towards long-term scalability, analysis of execution plans and plan cache 
• Proper alignment of clustered indexes and other index types including nonclustered and columnstore indexes as well as specialty xml, spatial and fulltext indexes
• Maintenance of indexes, custom index maintenance strategies
• Database solutions, knowledge of TFS or similar source control for working in a team environment
• Database performance profiling with traces and xevents sessions, DTA, perfmon, DMV’s, including the passive metrics for index usage, blocking, missing indexes, wait statistics.
• Optimal data type usage, proper alignment of uniqueness and constraints including foreign keys
• Apply appropriate data compression when useful
• Code Review and approval for development teams
• Working with developers to implement column encryption, password hashing, proper SQL Server security implementation
• FileStream and FileTable implementation where appropriate

Relational Database Designer

• Logical and Physical design of relational databases to serve business purposes, academic level knowledge of fundamental principles of normalized database design
• Optimal data type usage, proper alignment of uniqueness and constraints including foreign keys, identity and sequence values
• Proper alignment of clustered indexes
• Strategies for duplication of data for offloading reporting workloads, replication, mirroring, AlwaysOn Availability Groups, snapshots, sharding or federation of databases via various methods in and outside of SQL server 

Data Integration Developer

• At home in SSIS and capable of complex multi-server, multi-environment deployments for heterogeneous data integration, using the SSIS expression language for a variety of purposes, mostly Extract, Transform and Load (ETL) packages.
• Database solutions, knowledge of TFS or similar source control for working in a team enviornment
• Data scrubbing and quality (perhaps with Data Quality Services) using TSQL
• Change Tracking/Changed Data Capture custom development for new/updated record detecting in data warehousing 
• SQL Server Agent proxies and proper SSIS security

Business Intelligence Developer

• At home in SSRS for visually pleasing reporting, dashboarding, KPIs, sparklines, complicated subreports, paging, SSRS expression language for formatting and conditional visibility, etc. 
• Complex TSQL query writing including PIVOT/UNPIVOT, windowing functions, distinct/duplicate record detection, data scrubbing
• Works well with the business users and power users for discovery, design and report prototyping. Communicates effectively about data quality without getting tied up in cosmetics, and assists the transformation of existing manual data processes (such as Excel copy/paste schemes) into automated data consumption and reporting.
• Knowledge of SSRS scheduling, caching, subscriptions, data source configuration and deployment from a reporting solution. Should be able to administer security in SSRS or pass on instructions for security to administrators.
• Excel Pivot Tables, DAX in PowerPivot, PowerView and other third-party reporting tools using SQL Server as a data source
• Business Intelligence capabilities of SharePoint, PowerPivot for SharePoint, SSRS in SharePoint Integrated mode. (PerformancePoint is plus, but a different animal)
• Development in SSAS Tabular, the Tabular BI Semantic Model, and DAX 

Data Warehouse Designer

• Development of Fact and Dimension tables (using the Kimball method star schema, for example), identification of slowly-changing dimension types of data, logical and physical implementation of dimensional modeling
• Excellent communicator with business users and power users for discovery, design and report prototyping. Communicates effectively about data quality without getting tied up in cosmetics, and leads the transformation of existing manual data processes (such as Excel copy/paste schemes) into automated data consumption and reporting.
• Close development effort with business subject matter experts (SME's) for data sourcing identification, common key transformation and integration effort, report prototyping
• Creates and maintains end-to-end data mapping of data sources through all transformations necessary for report output. For example, tracing the origin of data on a report back to its native source in external systems.

SSAS Multidimensional Developer 

• Start-to-finish development of SSAS in multidimensional mode with custom MDX development for custom measures, hierarchies and dimensional data modeling. SSAS Tabular is a logical extension of this skill set, though not a primary function.
• Partitioned Cube processing, proactive caching, MOLAP/ROLAP/HOLAP strategies
• Developing report prototypes in Excel, SSRS, or other reporting tools
• Backups and security of the Analysis Services engine, including the cluster-aware SSAS service, typically fall under the direction of the SSAS developer, because of unfamiliarity with typical backup administrators in the IT department.