Overview
11 Database administration and development
This chapter shows how AI can act as a force multiplier for both Administrative and Development DBAs. It highlights how chat-driven assistance narrows the gap between roles by generating accurate SQL, guiding administrative workflows, and accelerating everything from basic query writing to complex operational tasks. The authors emphasize a pragmatic approach—use AI to speed up routine work, reinforce recoverability and performance, and lean on automation and data collection to scale DBA impact across diverse environments.
Core use cases include rapid query and object creation, context-aware code generation, and execution plan analysis that surfaces bottlenecks and proposes targeted fixes, index strategies, and code reviews. AI also streamlines documentation by producing clear commentary, intent, and usage guidance for SQL. In heterogeneous environments, it serves as a tutor and translator—explaining unfamiliar concepts with analogies, mapping data types, converting DDL or syntax across platforms, and helping DBAs stay current on features and licensing. Throughout, prompt engineering patterns—personas, staged inputs, file uploads, explicit output formats, and concise instructions—consistently improve accuracy and usefulness.
For maintenance and advanced administration, the chapter focuses on recoverability first, then expands into integrity checks, statistics, configuration auditing, and selective automation with community tools like IndexOptimize and PowerShell modules such as dbatools. Examples show AI composing scripts to inventory permissions, centralize results, and analyze configurations (for example, memory utilization), as well as crafting advanced routines for snapshots, moving data files, and right-sizing auto-grow settings. The guidance stresses validation and testing to avoid errors or hallucinations, plus awareness of permissions and security trade-offs. The authors close by reaffirming the enduring value of human judgment—especially in data modeling, compliance, and ethics—framing AI as a powerful assistant rather than a replacement.
Prompts used in this chapter
- I need an example of a prompt that can be written to create a useful function in SQL Server. Do you have any ideas?
- Help me verbalize the difference between Administrative and Development DBAs, as there is often a lot of confusion.
- Create an optimized prompt that would have gotten the above results with less effort.
- Is there an asciidoc highlighting syntax for PowerShell? If not, I’ll just use "shell"
FAQ
What’s the difference between Administrative and Development DBAs, and how does AI help bridge the gap?
Administrative DBAs focus on secure, reliable operations, backups, and maintenance, while Development DBAs design schema and tune application performance. AI helps both sides by generating SQL/DDL, guiding administrative steps, and offering optimization advice, making it easier for each role to handle tasks outside their traditional scope.How can AI speed up query and object creation?
AI can produce tailored SQL and DDL from short prompts—everything from basic SELECTs to CREATE TABLE, stored procedures, triggers, and functions. It also clarifies tricky syntax (for example, INSERT INTO vs SELECT INTO), suggests secure designs (like using PasswordHash), and can convert units (microseconds to milliseconds) to improve readability.How do I use AI to find the worst-performing stored procedures in SQL Server?
Ask AI to write a query against sys.dm_exec_procedure_stats that ranks procedures by average elapsed time or CPU. It can include database and object names, execution counts, and averages per execution. Remember results reflect cached procedures only and require VIEW SERVER STATE permission.What’s the best way to prompt AI for query optimization help?
Provide the SQL statement and, separately, the execution plan; tell the model to wait until it has both. Ask for bottlenecks, index suggestions, anti-pattern fixes (unnecessary joins, non-SARGable functions, excessive sorts), and an optimized rewrite—then validate the result for correctness and performance.How should I give large inputs (like execution plans) to AI?
If supported, upload files to keep prompts organized and avoid character limits; token limits still apply. If file uploads aren’t available, instruct the model to wait until all inputs are provided before responding to avoid premature or irrelevant answers.Can AI help me work across heterogeneous databases (e.g., SQL Server and Oracle)?
Yes—use AI as a tutor and translator to map concepts (instances vs databases, tablespaces vs files), compare data types, convert DDL and query syntax, and explain licensing or new features. It can also generate PolyBase external table definitions from Oracle DDL and convert collations/types as needed.How can AI improve maintenance and recoverability planning?
AI can outline maintenance checklists (backups, integrity checks, index/statistics upkeep), generate platform-specific commands (sp_updatestats, DBMS_STATS.GATHER_SCHEMA_STATS), and leverage community tools like Ola Hallengren’s IndexOptimize. Always test outputs and confirm they align with your standards and recovery objectives.How do I combine AI with PowerShell and dbatools for automation?
Ask AI to generate dbatools-based scripts—for example, reading Registered Servers, finding sysadmin logins, emailing HTML reports, or writing results to a table with Write-DbaDataTable. Centralizing collected data enables reporting, drift detection, and scheduled automation across your estate.What’s a practical example of AI assisting with configuration analysis?
Provide a table or sample output of Oracle memory settings; ask AI to normalize units (M/G to MB), apply conditional totals (memory_max_target vs sga_max_size + pga_aggregate_target), and filter instances exceeding a utilization threshold (e.g., 75%). AI often uses a CTE to simplify conversions and calculations.What responsibilities still require a human DBA in the AI era?
Data modeling, stakeholder communication, and nuanced governance/compliance decisions require human judgment. DBAs must validate AI outputs, prevent security risks (e.g., with xp_cmdshell), and ensure ethical handling of sensitive data—AI is an accelerator, not a substitute for expertise.
AI for Everyday IT ebook for free
