Competencies Checklist for MS SQL Administrator

Installing and Configuring SQL Server
- Install and configure SQL Server instances and related services.
- Configure SQL Server instances and services.
- Configure additional SQL Server components.
- Implement database mail.

Maintaining SQL Server Instances
- Manage SQL Server Agent jobs, alerts and operators
- Implement the declarative management frame

Managing SQL Server Security
- Manage logins and server roles.
- Manage users and database roles.
- Manage SQL Server instance permissions.
- Manage database permissions.
- Manage schema permissions and object permissions.
- Audit SQL Server instances.
- Manage transparent data encryption.

Maintaining a SQL Server Database
- Back up databases.
- Restore databases.
- Manage and configure files, file groups, and related options; database options; recovery model; attach/detach data
- Manage database snapshots.
- Maintain database integrity.

Performing Data Management Tasks
- Import and export data.
- Manage data partitions.
- Implement data compression.
- Maintain indexes.

Monitoring and Troubleshooting SQL Server
- Identify SQL Server service problems.
- Identify concurrency problems.
- Identify SQL Agent job execution problems.
- Locate error information.

Optimizing SQL Server Performance
- Implement Resource Governor.
- Use the Database Engine Tuning Advisor.
- Collect trace data by using SQL Server Profiler.
- Collect performance data by using Dynamic Management Views (DMVs).
- Collect performance data by using System Monitor.
- Use Performance Studio.

Implementing High Availability
- Implement and configure database mirroring and failover
- Implement a SQL Server clustered instance.
- Implement log shipping.Implement replication.

Competencies Checklist for MS SQL Programmer

Implementing Tables and Views
- Create and alter tables.
- Create and alter views.
- Create and alter indexes.
- Create and modify constraints.
- Implement data types.
- Implement partitioning solutions.

Implementing Programming Objects
- Create and alter stored procedures.
- Create and alter user-defined functions (UDFs).
- Create and alter DML triggers.
- Create and alter DDL triggers.
- Create and deploy CLR-based objects.
- Implement error handling.
- Manage transactions.

Working with Query Fundamentals
- Query data by using SELECT statements.
- Modify data by using INSERT, UPDATE, and DELETE statements.
- Return data by using the OUTPUT clause.
- Modify data by using MERGE statements.
- Implement aggregate queries.
- Combine datasets.
- Apply built-in scalar functions.

Applying Additional Query Techniques
- Implement subqueries.
- Implement CTE (common table expression) queries.
- Apply ranking functions.
- Control execution plans.
- Manage international considerations.

Working with Additional SQL Server Components
- Integrate Database Mail.
- Implement full-text search.
- Implement scripts by using Windows PowerShell and SQL Server Management Objects(SMOs).
- Implement Service Broker solutions.
- Track data changes.

Working with XML Data
- Retrieve relational data as XML.
- Transform XML data into relational data.
- Query XML data.
- Manage XML data.

Gathering Performance Information
- Capture execution plans.
- Gather trace information by using the SQL Server Profiler.
- Collect output from the Database Engine Tuning Advisor. Collect information from system metadata.

Competencies Checklist for MS SQL BI developer

Implementing an SSIS Solution
- Implement control flow which include but is not limited to: checkpoints; debug control flow; transactions; implement the appropriate control flow task to solve a problem; data profiling and quality
- Implement data flow may include but is not limited to: debug data flow; implement the appropriate data flow components
- Implement dynamic package behavior by using property expressions.
- Implement package logic by using variables system variables and user variables
- Implement package configurations.
- Implement auditing, logging, and event handling.
- Extend SSIS packages by using .NET code.

Configuring, Deploying, and Maintaining SSIS
- Install and maintain SSIS components.
- Deploy an SSIS solution by using common utility
- Manage SSIS package execution.
- Configure SSIS security settings.
- Identify and resolve issues related to SSIS solution deployment.

Implementing an SSAS Solution
- Implement dimensions in a cube.
- Implement measures in a cube.
- Implement a data source view.
- Configure dimension usage in a cube.
- Implement custom logic in a cube by using MDX.
- Implement data mining.
- Implement storage design in a cube.

Configuring, Deploying, and Maintaining SSAS
- Configure permissions and roles in SSAS.
- Deploy SSAS databases and objects.
- Install and maintain an SSAS instance.
- Diagnose and resolve performance issues.
- Implement processing options.

Implementing an SSRS Solution
- Implement report data sources and datasets.
- Implement a report layout.
- Extend an SSRS solution by using code.
- Create an SSRS report by using an SSAS data source.
- Implement report parameters.
- Implement interactivity in a report.
- Implement report items.
- Embed SSRS reports in custom applications.

Configuring, Deploying, and Maintaining SSRS
- Configure report execution and delivery.
- Install and configure SSRS instances.
- Configure authentication and authorization for a reporting solution.
- Deploy an SSRS solution.
- Configure SSRS availability.