Tuesday, September 02, 2014

The Nine SQL Server Careers

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.

      It's unlikely you can make a career out of just one of the nine careers. That leads to a final section of DBA archetypes in real life:

      The Report Writer would possess varying levels of expertise in the Database Developer and Business Intelligence Developer, with a skill set that focuses on SSRS and Excel and TSQL querying, perhaps very advanced. Complimentary skill sets in the business' institutional knowledge, process analysis, data scrubbing, and statistical analysis are common. I have met folks in this category who have made 15+ year careers out of just this area, and they may even be titled "Database Administrator" without any contribution to the business' disaster recovery or high availability capability.

      The Business Intelligence Technical Lead would possess experience as an ETL Developer, Business Intelligence Developer and Data Warehouse Designer, with also capability as a SSAS Multidimensional Developer. Complimentary to this role would be experience with the report development life cycle management, business analysis, report project organization, and source control.

      The Business Intelligence Director would use their experience as a Data Warehouse Developer and Data Warehouse Designer, and likely experience with SSAS Multidimensional, to take on a departmental leadership role with guidance for Business Intelligence-related spend. Governance, systems integration, and platforms guidance would be complimentary roles to serve.

      The SQL Server Performance Consultant would be bring strong skill sets as a Database Administrator, Database Developer and Relational Database Designer to clients, and perhaps some experience with custom application development. When it comes to performance optimization, this consultant could deliver significant, positive results to client applications. With excellent communication skills, they would provide developers with a greater understanding of application impact on the database.

      The Data Center Database Operator would be strong as a Database Administrator and High Availability Administrator, perhaps in a multi-tenant environment or multi-datacenter enterprise where SQL Server up time and maintenance are key. Data center engineering, power distribution and network layout would be complimentary skill sets. Cool nerves, excellent written and verbal communication skills and a skill set in PowerShell for automating regularly scheduled disaster recovery exercises will be important, as you secure your internal or external clients against downtime. 

      Question: What mixes of these nine careers have you seen in the wild?


      Anonymous said...

      What mixes of these careers have you seen? All of them. As the sole "DBA" at my company, I am in charge of everything you just listed. I would guess I am more the norm than the exception these days. As a funny side note, my manager told me the other day that I was the only one on the team (the networking team) that has only one job (he was referring to "only doing SQL Server"). All I could do was stare...

      Anonymous said...

      Aren't we going crazy with this stuff? Things are becoming too scattered. If this trend continues, soon for a moderate SQL Server environment, companies will need to employ hundreds of people. I'll rant that the future possible "career" may include:

      - Join Coordinator, who has strong knowledge of physical and logical join operators in SQL Server, and works with developers to optimize join strategies,
      - Index Manager, who is responsible for all indexes inside relational engine, and manages index fragmentation levels
      - Columnstore Index Manager, who has unique knowledge of columnstore indexes and batch processing, and manages delta stores
      - Mirroring Witness Lead, that works with all DBAs to make sure the witness instance is fully implementing corporate witnessing policies
      - SQLOS Scheduler Administrator, who turns on and off schedulers when required, makes reports and recommendations for DBA, designs and implements long-term scheduling strategy
      - Plan Cache Administrator, who manages all query plans on all SQL Server instances, and is able to tell if the plan should actually be in the cache or not,
      - Statistics Operator, who has the fine grained knowledge about last update time for all statistics in all tables, and work with developers to make sure they leverage statistics,
      - Lock Consultant, who has strong abilities to tell if locks are obtained and released in a timely manner
      - Service Broker Technical Director that decides if the solution can use SB or not, and designs queues and activation strategies

      And so on, and so on. I repeat: aren't we going crazy?