CompTIA DataSys+

Administrators read and write SQL to inspect, repair and secure data. You work with the statement families, joins, views and stored routines, and read an execution plan to see what the engine actually did.

• Data definition, manipulation and control statements
• Joins, subqueries and set operations
• Views and materialized views
• Stored procedures, functions and triggers
• Transactions, commit and rollback
• Reading an execution plan

You build A script that creates a schema, loads sample data and explains the plan for three queries.

Good design prevents problems that tuning cannot fix. You move from requirements to an entity relationship model, normalize it, and decide where denormalizing, partitioning or constraints are justified.

• Gathering data requirements
• Entity relationship diagrams
• Normalization through the third normal form
• When and how to denormalize
• Constraints, defaults and data integrity
• Partitioning and index design

You build A normalized schema with diagram and constraints for a real business process.

Where a database runs shapes its cost, speed and risk. You compare on-premises, hosted, managed cloud and hybrid designs, size the hardware and storage, and account for licensing and support.

• On-premises, infrastructure-hosted and managed services
• Hybrid architectures and data gravity
• Sizing processor, memory, storage and network
• Storage layout for data, logs and temporary files
• Licensing models and their consequences
• Single instance, cluster and replica designs

You build A deployment recommendation for one workload, comparing three options on cost and risk.

A repeatable install is the base of everything later. You install a database engine, set the parameters that matter, create users and storage, and script the deployment so a second instance matches the first.

• Installing an engine and checking prerequisites
• Instance and database configuration parameters
• Memory, connection and logging settings
• Creating databases, users and schemas
• Scripted and container-based deployment
• Verifying a deployment

You build A scripted install that produces a configured, verified database instance.

Data has to arrive intact. You import and export in common formats, plan schema changes, migrate between engines or environments, and validate row counts and values before anyone relies on the result.

• Bulk import and export tools
• Schema change management
• Migration approaches and cut-over planning
• Moving between engines and environments
• Validating counts, keys and values
• Rolling back a failed migration

You build A migration of a sample database between two environments, with a validation report.

Slow databases cost everyone time. You find bottlenecks with waits and plans, design indexes that help, resolve locking and deadlocks, and tune queries and settings with measurements rather than hunches.

• Baselines and performance counters
• Wait analysis and finding bottlenecks
• Index strategy and statistics
• Query tuning from execution plans
• Locks, blocking and deadlocks
• Caching, partitioning and resource limits

You build A tuning report that takes a slow query set and shows measured gains from each change.

Maintenance keeps a database healthy between incidents. You monitor key measures, schedule jobs for integrity checks and index upkeep, apply patches safely and plan capacity from growth trends.

• Monitoring tools, metrics and alerts
• Integrity checks and statistics updates
• Index maintenance and fragmentation
• Patching and version upgrades
• Log and storage management
• Capacity planning from growth data

You build A monthly maintenance plan with scheduled jobs, alerts and a capacity forecast.

Most data exposure comes from access that was never reviewed. You set up authentication, roles and permissions on the principle of least privilege, separate duties, and audit who did what.

• Authentication methods and directory integration
• Roles, grants and least privilege
• Separation of duties and privileged accounts
• Row and column level security
• Auditing and activity logs
• Periodic access reviews

You build A role model and permission scripts for one database, with an audit trail you can query.

Data needs protection at every point it sits or moves. You encrypt storage and connections, mask sensitive values, defend against injection, and classify data so protection matches its sensitivity.

• Encryption of data files, backups and connections
• Key storage and rotation
• Masking, tokenization and anonymization
• SQL injection and defenses
• Data classification and compliance duties
• Database firewalls and network placement

You build A protection plan for a database holding personal data, with encryption and masking applied.

A backup is proven by restoring it. You combine full, differential and log backups, recover to a point in time, store copies safely and rehearse restoring a database under pressure.

• Full, differential and transaction log backups
• Point-in-time recovery
• Backup storage, retention and encryption
• Restoring a single table or object
• Backup verification and restore tests
• Recovery runbooks

You build A backup schedule with a timed point-in-time restore you carry out and record.

Some databases cannot be down. You build replication, clustering and failover, set recovery objectives with the business, and plan for the loss of a site, not just a server.

• Replication: synchronous and asynchronous
• Clusters, failover and witnesses
• Recovery time and recovery point objectives
• Disaster recovery sites and regions
• Business impact analysis
• Testing failover

You build A replicated pair of databases with a documented and tested failover.

Databases are rarely alone. You connect them to applications, move data between systems on a schedule or as events, and manage connections and interfaces without overloading the server.

• Connection strings, pooling and drivers
• Extract, transform and load jobs
• Change data capture and streaming
• APIs and data virtualization
• Linked servers and federation
• Supporting application teams

You build A data flow from an application database to a reporting store, with monitoring on each step.

One project joins every skill. You design, deploy, secure and tune a database for a business application, protect it with backup and failover, and then recover it from a deliberate failure.

• Turning requirements into a design
• Deploying and securing the database
• Tuning against a realistic workload
• Backup, failover and recovery drills
• Documenting the service for handover

You build A running, secured and recoverable database service with full documentation.