Home / Databases
SQL Server, PostgreSQL, Oracle, MySQL, MongoDB and Redis performance and recovery. 228 posts.
Read the user@host in the error, check which mysql.user row matches, and fix wrong passwords, host patterns, anonymous users, plugins, TLS rules and locked accounts.
First-day MySQL administration. Connect safely with option files, understand user@host accounts, create application and read-only users with least privilege, use roles, and inspect sessions and basic health from the client.
Change big MySQL tables without downtime. Know which ALTERs are INSTANT, INPLACE or COPY, protect against metadata lock pile-ups, and use gh-ost or pt-online-schema-change with throttling, replica lag checks and safe cut-over.
Connect to Oracle with SQL*Plus or SQLcl, understand the container database and pluggable databases, find the right service name, create a user with only the privileges it needs, and check basic health.
Use SQL plan baselines to stop plan regressions after statistics changes, patches and upgrades. Load known-good plans from the cursor cache or AWR, fix one plan in an emergency, let the evolve task accept better plans, and manage baselines over time.
A practical first session in psql. Connect locally and remotely, use the backslash commands you need daily, understand databases vs schemas, create an application role with least privilege, and fix the most common connection errors.
What an index does, how to create one without locking a busy table, how to read a simple EXPLAIN plan, multi-column index order, and the common reasons PostgreSQL ignores an index you expected it to use.
Find integer keys close to 2,147,483,647, buy time when inserts already fail, and convert the column to bigint with either a rewrite or an online add-backfill-swap.
When EXPLAIN shows estimated rows far from actual rows, PostgreSQL picks bad joins and scans. Find misestimates, raise per-column statistics targets, add extended statistics for correlated columns and functional dependencies, and know what the planner still cannot see.
How keys map to hash slots and shards, how to add or remove shards and move slots safely with redis-cli, why multi-key commands fail across slots, and how to find hot keys and big keys that make one shard the bottleneck.
DROP TABLE or TRUNCATE ran on production. Find the exact time from logs, restore a copy of the cluster to just before it with PITR (pgBackRest, base backup plus WAL, or managed-service restore), then dump the table from the copy and load it into production.
The AG failed over and the new primary is healthy, but apps time out or reach the old node. Check listener resource state, DNS records and TTL, MultiSubnetFailover, cluster permissions and cloud load balancer probes, then restore connections quickly.
Secondaries fell behind longer than the oplog window and now report RS102 "too stale to catch up", leaving the primary without redundancy. Grow the oplog, resync the stale members by initial sync or from a snapshot, and protect the window from heavy writes.
After a failover (manual or by Orchestrator, MHA or a script), the old primary came back writable and some clients kept writing to it. Fence the old primary, measure the divergence with GTIDs, reconcile the errant transactions and rebuild it as a replica.
New connections and parses fail with ORA-04031 in the shared pool or large pool. Buy time safely, find what is consuming the pool (literal SQL, high version counts, a leak, large pool users), resize SGA components and fix the application pattern behind it.
Understand SIMPLE vs FULL recovery, take full, differential and transaction log backups with T-SQL and PowerShell, check that backups are usable, and practise a restore to a new database name before you ever need one.
Recognise parameter-sensitive plans, prove it with Query Store and plan cache data, and pick the right fix. Parameter Sensitive Plan optimization in SQL Server 2022, OPTIMIZE FOR, RECOMPILE, plan forcing and Query Store hints, with the trade-offs of each.
One shard holds most of the data or takes most of the writes. Read balancer status, find jumbo chunks and monotonic shard keys, then fix with balancer windows, clearing jumbo flags, refining or resharding the key.
A schema change hangs and every query on that table queues behind it. Find the transaction holding the metadata lock with performance_schema, clear the pile-up, and run future DDL with lock timeouts, INSTANT/INPLACE or online schema change tools.
CPU jumps and sessions pile up on library cache and cursor mutex waits. Find the SQL with thousands of child cursors, read the reasons in V$SQL_SHARED_CURSOR, and fix bind handling, hard parsing and hot objects.
Throughput collapses on the primary or a hot standby with LWLock waits on subtransaction SLRU buffers. Find the code that creates subtransactions, the long transaction that turns them into a problem, and fix both.
An AG secondary lags minutes or hours behind, reports on the readable secondary show old data, and failover RTO is at risk. Separate send queue from redo queue, find a blocked redo thread and fix I/O, workload and redo settings.
New connections time out and the server looks hung while CPU is low. Confirm worker thread exhaustion, connect through the Dedicated Admin Connection, find the blocking chain or parallelism storm, and fix it without blindly raising max worker threads.
One repeatable order of checks for a slow MySQL or PostgreSQL database - host, running sessions, waits and locks, top queries, plans, engine health and connections - with the commands for both engines side by side.
Since the September 2026 SQL Server updates, querying sys.dm_exec_requests while a database is recovering (after a restart or failover) can raise an access violation and write a dump. Monitoring tools trigger it most. Choose between trace flag 4696, pausing those queries during recovery, or removing the update.
After the September and October 2026 SQL Server updates, linked servers that use MSDASQL with a provider string can fail with Msg 7416 - access denied because no login mapping exists. A stricter connection validation check is the cause. Check the login mappings and provider string, and look for the fix in the later builds.
Long reports or exports fail with ORA-01555. Read the alert log, check V$UNDOSTAT, size UNDO_RETENTION and the undo tablespace, and fix fetch-across-commit and LOB cases.
Decode PostgreSQL connection errors, check pg_hba.conf rules with pg_hba_file_rules, fix SSL, md5 and SCRAM mismatches, and reload safely without a restart.
PostgreSQL 14 gets its last release in November 2026. After that, no security or bug fixes. Find every v14 instance, pick a target (17 or 18 for most), check extensions and application compatibility, and choose between pg_upgrade, logical replication and the cloud provider's major version upgrade.
The redis-cli commands you reach for every week - connecting, health checks, finding keys safely, memory, clients, slow commands, replication, persistence, ACLs and cluster.
Route ApplicationIntent=ReadOnly connections through the AG listener to readable secondaries, load-balance them, test with sqlcmd and fix the usual reasons reads still land on the primary.
Microsoft's 13 October 2026 updates for SQL Server 2017-2025 fix an elevation of privilege flaw (CVE-2026-96956) among 22 CVEs in the SQL Server 2025 update. Two known issues carried over from September affect MSDASQL linked servers and sys.dm_exec_requests during recovery. Check them, pick the right package, and patch availability groups in the right order.
Choose between mongodump (logical, small data), filesystem/volume snapshots (large data) and managed backups. Take consistent backups from a replica set, restore them, replay the oplog for point-in-time recovery and test regularly.
The mongosh commands a MongoDB DBA uses every week - server health, running operations, replica sets, sizes, indexes, profiler, users, sharding and mongodump/mongorestore.
Error 1213 means InnoDB found a deadlock and rolled back one transaction. Log every deadlock, read which statements and locks were involved, then fix it with consistent ordering, better indexes, shorter transactions and application retries.
When Oracle sessions hang on enq TX row lock contention, find the root blocker with v$session, identify the locked object and row, see what the blocker is doing, then fix it at the application rather than killing sessions by habit.
Copy-ready SQL for common PostgreSQL access patterns - owner, read-write and read-only roles, default privileges for future tables, checking who has what, and fixing the usual permission denied errors.
Redis is usually sub-millisecond. When p99 jumps, use SLOWLOG, the latency monitor and INFO to find the blocking command, fork, fsync or host problem, then fix it.
After restoring a database to another server, users can't log in because database users point to login SIDs that don't exist there. Find orphaned users, re-map them with ALTER USER, and copy logins with their original SIDs to prevent it.
Which msdb table holds what for SQL Server Agent, how they join, and ready-to-use queries for the questions you'll ask most. Jobs with steps, schedules per job, running jobs, last outcome per step, operators and alerts.
Build a job health report in Power BI on top of a central Agent history store. Covers the data model, DAX measures for success rate and duration change, the pages worth building, refresh through a gateway and the read-only permissions it needs.
Create SQL Server Agent jobs from a single re-runnable T-SQL script with variables for name, category, owner, steps, schedule and notification. It's safe to run again, works well in source control, and gives you the same standards on every server.
SQL Server security drifts when each instance is configured by hand. Define a baseline covering authentication, least-privilege roles, encryption in transit and at rest, auditing, surface area and patching, apply it with scripts or policy, and check every instance against it regularly.
Managed MySQL services differ in storage design, failover speed, replica scaling, version policy and analytics features. Check your versions, plugins and replication needs, compare services on those points within your cloud, and test failover and major upgrades before moving production.
Managed PostgreSQL services look similar but differ in storage architecture, HA behaviour, extensions, version support and limits on superuser access. List what your applications need (extensions, versions, replication, scale), compare services on those points within your cloud, and test failover, upgrades and performance before committing.
The shard key decides whether a sharded cluster spreads load evenly or piles it onto one shard. Evaluate candidates on cardinality, frequency and monotonic growth, make sure common queries include the key, prefer compound or hashed keys where needed, and know your resharding options before you commit.
Redis deployments range from a single node to sharded clusters across zones. Decide by data size, throughput, availability needs and client support, understand what each topology changes for applications (multi-key commands, failover handling), and prefer managed services unless you have the skills to run Redis HA yourself.
MySQL offers several ways to survive a server failure, with different data-loss guarantees, failover speed and operational effort. Compare asynchronous and semi-synchronous replication with orchestrated failover, InnoDB Cluster/Group Replication, Galera-based clusters and managed Multi-AZ services, and decide by RPO, RTO and team skills.
Backup schedules are often copied from a template instead of from what the business can lose and how long it can wait. Agree RPO and RTO per database tier, derive full, differential and log schedules, store copies off-server and immutable, protect against ransomware, and prove restores regularly.
RMAN supports many backup styles, and the right one depends on database size, RPO/RTO and where backups are stored. Choose between full plus incrementals and incrementally updated image copies, set a recovery window retention, use block change tracking, send copies off-site or to object storage with immutability, and test restores.
MongoDB schemas should follow how the application reads and writes data, not a normalised relational model. Embed data read together, reference data that grows without bound or is shared, use patterns such as subset, bucket and computed fields, and enforce structure with schema validation.
SaaS-style applications must keep tenants isolated without making operations impossible. Compare database-per-tenant, schema-per-tenant and shared tables with a tenant key, enforce isolation with row-level security where tables are shared, and plan for noisy neighbours, per-tenant restores and growth.
Logical dumps and physical backups solve different problems. Use physical backups with WAL archiving for point-in-time recovery of whole clusters, logical dumps for portability and single-object restores, compare pgBackRest, Barman and WAL-G for self-managed servers, and rely on managed PITR plus extra copies on cloud services.
A cache helps only if it stays correct and survives failures gracefully. Choose cache-aside, read-through or write-through per data type, set TTLs and invalidation rules, protect the database from stampedes when hot keys expire, and design the application to keep working when Redis is slow or down.
Enterprise edition costs much more than Standard, and many workloads don't need it. List the features and scale limits your workload depends on, compare them with Standard's capabilities in your version, consider alternatives such as basic availability groups or Azure SQL, and confirm with licensing specialists before deciding.
SQL Server performance on Azure VMs often comes down to storage choices: disk types, host caching, striping, and the VM's own throughput limits. Size from a measured baseline, separate data, log and tempdb, choose Premium SSD v2 or Premium SSD with correct caching, use local SSD for tempdb, and check VM-level caps.
Oracle databases can move to several cloud targets with very different feature sets, licensing rules and operating models. Compare OCI database services, Oracle Database@Azure and @AWS, Amazon RDS for Oracle and self-managed VMs on the features you use (RAC, Exadata, options), licensing, latency to applications and who operates them.
The basics of running WinSCP's command-line client from a SQL Server Agent CmdExec step without it hanging, prompting or failing silently. Pin the host key, use key authentication, write a session log and let WinSCP's exit code fail the step.
Walk through the New Job dialog in SQL Server Management Studio page by page, with the choices that matter for production. Owner, category, step flow, retries, output, schedule and notifications. Then script the job out so it can be rebuilt.
Test emails from Database Mail arrive, but job failure and alert notifications never do. The usual reason is that SQL Server Agent isn't set to use Database Mail, or wasn't restarted after it was. Fix it in SSMS or T-SQL, then verify the whole path.
A dependable file-transfer job. A PowerShell step uses the WinSCP .NET assembly to download new files, verify them, archive them on the remote side, record each file in a control table and fail cleanly with a clear message when anything goes wrong.
Collect jobs, owners, schedules, last outcomes and notification settings from all your SQL Server instances with dbatools, export to CSV or a central table, and flag common problems. Includes handling of unreachable servers and a scheduled version.
Jobs that call sp_start_job create dependencies nobody sees in SSMS. Find them by searching step commands, alerts that run jobs and procedures that start jobs, then draw the chain before you disable, move or reschedule anything.
Copy SQL Server Agent history from msdb into a central reporting database with an incremental, watermark-based load before Agent purges it. Covers a simple star schema, the load procedure, collecting from many servers and the queries it makes possible.
Find out what is filling msdb, from job history, backup history, Database Mail logs or job step logs, and set up one clean-up job that purges each of them by age in batches. Also covers the indexes that keep purging fast.
Find jobs with no failure notification and fix them all with one script. Also covers keeping new jobs covered automatically, writing failures to the event log for monitoring tools, and making sure the emails actually leave the server.
Temporary jobs, such as an overnight index rebuild, a one-time data fix or a migration step, pile up and confuse people for years. Make them delete themselves after running, give recurring temporary jobs an end date, and sweep up the ones that slipped through.
Slow backups overrun maintenance windows, and slow restores stretch outages. Measure throughput, turn on compression, stripe to several files or URLs, tune BUFFERCOUNT and MAXTRANSFERSIZE, speed up restores with instant file initialization, and test restore time regularly against the RTO.
MongoDB performs well while the working set and indexes fit in memory. Measure WiredTiger cache usage and eviction pressure, check index sizes and usage, remove unused indexes, design compound indexes with the ESR rule, and right-size instances or shard before the working set outgrows RAM.
The MySQL sys schema shows which indexes are never used, which duplicate others, and which statements scan full tables. Use it to drop dead weight safely with invisible indexes, add the indexes that remove full scans, and verify the result with statement digests.
Large, growing tables slow queries, backups and purges. Partition by date or another access key so queries prune and old data drops instantly, compress cold partitions, move history to cheaper tablespaces, and convert online where possible. Check licensing for Partitioning and Advanced Compression before using them.
Huge time-series and event tables make vacuum slow, purges painful and indexes bloated. Partition by time with declarative partitioning, automate partition creation, drop or detach old partitions instead of deleting rows, and migrate existing tables with minimal downtime.
PostgreSQL's defaults suit a tiny server. Size shared_buffers relative to RAM, set effective_cache_size so the planner knows about the OS cache, raise work_mem carefully because it multiplies per sort and connection, give maintenance work enough memory, and verify with buffer hit ratios and temp file statistics.
pg_stat_statements shows which queries consume the most time overall, which is where tuning pays off. Rank queries by total time, fix the top ones with EXPLAIN and targeted indexes, remove unused and duplicate indexes that slow writes and vacuum, and track results over time.
Redis keeps everything in RAM, so memory use is cost. Find what uses memory, store small objects in hashes with compact encodings, set TTLs on cache keys, choose the right eviction policy, watch fragmentation, and right-size the instance or cluster once usage is under control.
Oracle memory is often sized once at install and never revisited. Use the SGA, buffer cache and PGA target advisors with AWR history to see the effect of more or less memory, choose between automatic shared memory and manual settings, enable HugePages on Linux, and change in small, measured steps.
Most MySQL performance comes from a well-sized buffer pool, enough redo log capacity and I/O settings that match the storage. Measure the buffer pool hit rate and working set, size redo capacity for peak write rates, set I/O capacity for SSD/cloud disks, and change settings online where MySQL allows.
Row and page compression shrink data on disk and in memory, and columnstore indexes make large analytical scans far faster. Estimate savings first, choose page or row compression by update pattern, use columnstore for large fact and history tables, and watch CPU and maintenance impact.
Nightly rebuilds of every fragmented index burn I/O, bloat the log and slow AG replicas, often for little gain on modern storage. Focus on statistics freshness, rebuild only where fragmentation actually hurts scans, use resumable and online options, and measure the maintenance window before and after.
Too few indexes slow reads; too many slow every write and waste memory. Review missing-index suggestions critically, find indexes that are written but never read, merge duplicates and overlapping indexes, and validate each change against Query Store before and after.
Default parallelism settings let small queries go parallel and big queries take every core, causing CXPACKET/CXCONSUMER waits and unpredictable performance. Set MAXDOP from the NUMA layout, raise cost threshold for parallelism from the default, use database-scoped and query-level overrides where needed, and confirm with wait stats.
Wait statistics show what SQL Server spends time waiting on, which points you to storage, CPU, memory, locking or log throughput. Snapshot waits over a busy period, filter out benign waits, map the top waits to causes and fixes, and re-measure after each change.
Three ways to stop a SQL Server Agent job from running, and what each one does to manual runs, shared schedules and other jobs. Includes T-SQL for each, a check for jobs left disabled by accident, and a habit that stops them being forgotten.
Agent has no built-in dependency between jobs, so people chain them with sp_start_job or guess at schedule gaps. Instead, keep dependencies in a table and add a gate step that checks predecessors succeeded for today's batch. Each job can then wait, skip or fail, and the whole chain is visible in one query.
Set up an SSIS job step that runs under a dedicated domain account instead of the Agent service account. Create the credential and proxy, grant the SSIS subsystem, give the right people use of the proxy, configure the step and troubleshoot the usual failures.
Schedule a Python script with SQL Server Agent the right way. Use a dedicated virtual environment, call it from a CmdExec or PowerShell step, run it under a proxy, pass parameters with Agent tokens and make failures fail the step.
Job history cuts step messages short, often just before the useful part. Compare the output options SQL Server Agent offers per step (output file, log to table, include in history) and set them in SSMS or T-SQL so failures can be diagnosed the first time.
Applications get NotWritablePrimary or "no primary found", and rs.status() shows only SECONDARY members. Work out whether a voting majority is reachable, fix network or member problems, and use a forced reconfiguration only when the majority is truly gone.
The replica SQL thread stopped on a duplicate key (1062) or a missing row (1032). Find the failing transaction, decide whether skipping is safe, skip it the GTID way, check for errant transactions that will break the next failover, and verify data with checksums.
mysqld crashes during start-up with InnoDB assertion or corruption errors. Save the data directory first, read the error log, raise innodb_force_recovery one level at a time until it starts, dump the data, and rebuild a clean instance. Or restore from backup if that's faster.
Every insert and update hangs, the error log says MySQL is waiting for disk space, and the data or binlog volume is at 100%. Free space safely by purging only binary logs that all replicas and CDC tools have read, then fix retention so it doesn't happen again.
Applications fail with "no listener", "listener does not currently know of service" or "SID not known". Check the listener is running and listening on the right address, and that the database has registered its services. Then fix tnsnames, LOCAL_LISTENER and firewall problems, with commands for each step.
The only sysadmin login was removed, sa is disabled and its password lost, or the DBA group was deleted from AD. A local Windows administrator can start SQL Server in single-user mode, connect as sysadmin and add a proper admin back. Here are the steps, the traps and the clean-up.
The instance crashes or won't mount because a controlfile copy or an online redo log member is missing or corrupt. What you do depends on which file is gone and whether a good copy exists. The options run from a simple copy to RMAN autobackup restore, CLEAR LOGFILE, or incomplete recovery.
The database mounts but won't open because a datafile needs recovery, is missing or can't be locked. Find the affected file, decide between recover, restore-and-recover or taking the file offline, and use RMAN to bring the database back with no data loss where possible.
patronictl list shows no Leader, every node is a replica or stopped, and applications can't write. The usual causes are a lost etcd quorum, no candidate passing the failover rules, or a paused cluster. Diagnose each one and get a leader back without risking split-brain.
The primary server is down, without Patroni or another manager to fail over. Check the standby's lag, fence the old primary so you don't get two primaries, promote with pg_promote, repoint applications, and rebuild the old primary as a standby with pg_rewind.
PostgreSQL warns that a database must be vacuumed within N transactions, or stops accepting writes altogether. Find what is holding back freezing, such as long transactions, abandoned replication slots or orphaned prepared transactions, remove it, and run a targeted VACUUM to get write access back.
The Redis master is unreachable, clients get connection errors or READONLY, and Sentinel hasn't promoted a replica. Check Sentinel quorum and majority, replica eligibility and announced addresses, then force a failover and fix clients that bypass Sentinel.
A TDE-encrypted database won't restore or attach on another server (error 33111), or the original server was rebuilt. Find the certificate backup, recreate the master key and certificate, restore, and set up certificate backups so this never depends on luck again.
A database shows RECOVERY_PENDING or SUSPECT after a restart or storage problem. Work out which it is and why from the error log. Then bring it online by fixing files and permissions, restoring from backup, or, only as a last resort, using emergency mode repair.
The SQL Server service starts and stops at once, or won't start at all. Find the real error in ERRORLOG, then fix the common causes. Covers a missing tempdb path, damaged or missing master files, account and permission problems, port conflicts and bad startup parameters, using minimal-configuration and single-user starts.
A first tour of SQL Server Agent for people new to SQL Server operations. What each building block does, how they fit together, and a first useful automated task built end to end.
Short, practical answers to the SQL Server Agent questions that come up again and again. Where jobs are stored, who they run as, why a job didn't run, time zones, Express edition, Azure options, and more.
SQL Server Agent fails to start with error 5 "Access is denied" or stops straight after starting. Work through the service account, folder and registry permissions, the sysadmin login, and the Configuration Manager change that fixes most cases.
sp_start_job returns at once, so an SSIS package that calls it carries on before the job has done anything. Build a reusable procedure that starts a job, waits for it with a timeout, and returns its outcome, then call it from an Execute SQL Task. Covers permissions too.
Before patching, a migration cut-over or a DR test you often need all jobs stopped. Save the current enabled state, disable everything, and afterwards restore exactly that state, so jobs that were already disabled stay disabled.
SQL Server Agent notifies per job, not per step, and if a step is set to continue on failure, the job reports success and nobody hears about it. Three patterns for alerting on individual step failures while the rest of the job keeps running.
Agent only alerts on failure, so a job stuck for six hours looks fine until morning. Build a check that compares each running job with its own history and emails when it runs well beyond its normal duration, with sensible thresholds and an exclusion list.
A step-by-step check for SQL Server Agent jobs that didn't start when expected. Covers enabled flags, next run times, overlapping runs, daylight saving, Agent downtime, schedule end dates and frequency settings that don't mean what people think.
Create Agent alerts for high-severity errors and the I/O errors that warn of corruption, with sensible delays so you aren't flooded. One script sets them all up, then you can test them safely and know what to do when one fires.
Oracle 19c is the last release that supports the non-CDB architecture, so every database moving to 23ai must become a pluggable database. Check compatibility, describe the non-CDB, plug it into a CDB with NOCOPY or COPY, run noncdb_to_pdb.sql, and fix services and backups afterwards.
Data Pump is too slow for multi-terabyte databases moving from big-endian UNIX to Linux. Cross-platform transportable tablespaces with incremental backups keep the target in step while the source stays online, so the outage is a short final incremental plus a metadata import.
Move self-managed MySQL to Azure Database for MySQL Flexible Server. Check compatibility, create a right-sized server, do the initial load with mydumper/myloader or Azure DMS, keep it in sync with data-in replication, and cut over in minutes.
Consolidating on PostgreSQL? pgloader converts a MySQL schema and copies the data in one command. Here are the casting rules that matter, the SQL differences to fix in the application, how to validate, and how to cut over with a short freeze or CDC.
Move a large self-managed or RDS MySQL database to Aurora MySQL with minimal downtime. From RDS, use an Aurora read replica. From EC2 or on-premises, restore a Percona XtraBackup from S3 and then replicate binlogs until cut-over.
Move an Oracle database to RDS for Oracle when you want a managed service but must stay on Oracle. Check what RDS doesn't allow, size and license the instance, load with Data Pump through S3, catch up with DMS CDC, then cut over.
Move an Oracle schema and its data to PostgreSQL (self-managed, Azure Flexible Server or RDS/Aurora). Estimate effort with the Ora2Pg report, convert schema and PL/SQL, load data, and handle the differences that cause most bugs. Then run a parallel validation before switching.
pg_upgrade needs an outage and an in-place change. Instead, build a new cluster on the new major version, replicate into it with logical replication, test it under real data, then switch over in minutes with an easy way back.
Move a self-managed PostgreSQL database to Aurora PostgreSQL. Check extensions and settings, choose dump/restore, native logical replication or AWS DMS, run the cut-over, then adjust for how Aurora storage, replicas and failover behave.
Hundreds or thousands of short-lived application connections waste PostgreSQL memory and CPU. Add PgBouncer in transaction pooling mode, size the pools, find the session features that break, and roll it out without an outage.
Move MongoDB replica sets off self-managed VMs. Choose between Atlas (MongoDB's own service) and Cosmos DB for MongoDB vCore (MongoDB-compatible on Azure), check feature compatibility, migrate online or with dump and restore, and switch connection strings.
Move PostgreSQL from VMs or on-premises to Flexible Server. Check extensions and superuser dependencies, size compute, storage and networking, then migrate offline with pg_dump or online with the built-in migration service. Finish with the cut-over and post-migration tuning.
Stop patching Redis VMs and Sentinel. Decide whether the data even needs migrating, pick the managed tier and clustering mode, move data with RDB import or dual-write where it matters, and change clients to handle TLS and cluster mode.
MySQL 8.0 reached end of life in April 2026, and 8.4 is the current long-term support release. Run the MySQL Shell upgrade checker, fix the changes that bite (authentication plugin defaults, removed replication syntax, changed defaults), and upgrade replicas first, then the source.
Use AutoUpgrade to analyse, fix up and upgrade 19c databases to 23ai, including converting non-CDBs into PDBs. Covers the config file, the analyze, fixups and deploy modes, restore points for fallback, and the checks to run after the upgrade.
sysjobhistory stores run_date as 20261025, run_time as 23015 and run_duration as 13542 (1 hour 35 minutes 42 seconds). Here are reliable T-SQL conversions to datetime, seconds and hh:mm:ss, plus end times and a reusable view.
Live SQL Server Agent status without opening Job Activity Monitor. Which jobs are running, which step each is on, what its session is waiting for, what runs next, and whether Agent itself is up. Also how to give these views to people who aren't sysadmin.
Build a readable inventory of every SQL Server Agent job on an instance from msdb. Owner, category, enabled state, schedules, step count, subsystems, notification settings and last outcome, in one result set you can paste into documentation.
SQL Server Agent records that a job changed but not who changed it or what changed. Use a database audit specification on msdb's job procedures to capture who did it, keep script snapshots to capture what changed, and know the limits of triggers on msdb tables.
The pg_wal directory keeps growing until the volume fills. Find what is holding WAL back, usually a stale replication slot or a failing archive_command, release it safely and cap it.
The client always sees state 1. Pull the real state from the SQL Server error log, map it to the cause (bad password, disabled login, missing database, Windows-only mode) and fix it.
A step-by-step runbook for failed SQL Server Agent jobs. Find every failure and the step that broke, read the full error from history, output files and the Agent log, match it to the common causes (owner, permissions, proxies, timeouts, blocking, disk, Agent itself), fix and rerun safely, and stop it recurring.
Ready-to-run msdb queries and procedure calls for everyday SQL Server Agent work. Inventory every job with its schedule and last outcome, enable or disable jobs in bulk, change owners and categories, script jobs out for source control, copy them between servers and remove the ones nobody needs.
What to check in the first days after moving SQL Server to Azure or AWS. Covers data validation, statistics and index maintenance, raising compatibility level safely with Query Store, common performance gaps in the cloud, and monitoring set-up.
If SQL Server Agent can't reach the operator on an alert, the notification is lost unless a fail-safe operator is set. This guide sets up Database Mail, enables it for Agent, creates team operators and the fail-safe operator, and tests the whole chain end to end.
Move self-managed SQL Server on EC2 to Amazon RDS to hand over patching, backups and Multi-AZ HA. Covers the readiness check against RDS limits, sizing from CloudWatch data, migration by native backup or DMS, replacing instance-level features, and decommissioning.
A FinOps playbook for SQL estates already in Azure. Covers finding idle and oversized databases, moving spiky databases to serverless, consolidating into elastic pools or instance pools, reservations, Azure Hybrid Benefit hygiene, and dev/test savings.
The decisions and settings that separate a well-run Azure SQL Database from a fragile one. Covers choosing the service tier, networking and identity, encryption and auditing, Query Store and automatic tuning, retry logic, backups and geo-DR, monitoring, maintenance, and cost control, with az CLI and T-SQL for each item.
Move a growing Azure SQL Database from General Purpose or Business Critical to Hyperscale for up to 128 TB, fast backups and restores, and read scale-out. Covers fit checks, the online conversion, reverse migration window, replicas, and cost.
The second step after a lift-and-shift. Move databases from SQL Server on Azure VMs to Azure SQL MI to stop patching, clustering and backup work. Covers the business case, readiness checks, picking MI link or LRS, network design inside Azure, and decommissioning the VMs.
Job categories are a free, built-in way to group SQL Server Agent jobs by team, application or purpose. Create a simple category scheme, assign jobs in bulk, use categories in reports, bulk enable and disable, and notifications, and avoid the built-in categories that other features depend on.
A practical map of the on-premises SQL Server features that block or complicate moving to Azure SQL Managed Instance or Azure SQL Database, with the replacement for each and example code.
Move databases from Amazon RDS for SQL Server to Azure SQL MI, for example after a consolidation decision or an acquisition. Covers native backups from RDS to S3, copying to Azure Blob Storage with azcopy, restoring with Log Replay Service, TDE and identity considerations, and cutover.
SQL Server licences are often the biggest line in an AWS database bill. Covers downgrading Enterprise to Standard where safe, Optimize CPUs on EC2, memory-rich instance types, consolidating instances, Savings Plans and RIs, and when refactoring to Babelfish pays off.
A routine for keeping SQL Server Agent healthy. Daily checks for failures, long runs and disabled jobs, a weekly review of owners, notifications and history, and monthly housekeeping and testing. Each check comes with the query or dbatools command, and links to the deep-dive posts in this series.
Start SQL Agent jobs from SSMS, T-SQL, another job, PowerShell, sqlcmd, schedules, Agent alerts or an application. Includes how to start at a specific step, wait for a job to finish (sp_start_job doesn't wait), and give non-admins permission to run only the jobs they need.
Replace ETL jobs and reporting replicas with Fabric mirroring, which continuously copies SQL tables into OneLake as Delta tables. Covers the supported sources, prerequisites, setup, what it costs, what isn't replicated, and how reporting changes.
Most monitoring platforms already read the Windows Application log. Configure every SQL Server Agent job to write an event on failure, write your own custom events from job steps, find the events with PowerShell, and forward them to Azure Monitor, SCOM or a SIEM.
Use an Always On availability group to move Tier 1 SQL Server workloads to Azure with seconds of downtime. Covers network and cluster design across sites, adding an Azure VM replica (or a distributed AG), using it for DR first, and the planned failover that completes the migration.
Plan the two areas that delay most SQL Server moves to Azure. Covers mapping Windows logins and groups to Microsoft Entra ID, service accounts to managed identities, MI Windows authentication with Kerberos, private endpoints vs VNet-injected MI, DNS and firewall design.
Many SQL Server apps rely on Windows logins. Design Active Directory for SQL Server on AWS with AWS Managed Microsoft AD and a trust to on-premises AD, join RDS for SQL Server and EC2 to it, create Windows logins, and test Kerberos.
Unexpected restarts and failovers often go unnoticed until users complain. Set up an Agent job that emails on every service start with the node, start time and reason clues; add availability group role-change alerts; and check cluster and Windows events to find out why it happened.
CmdExec, PowerShell and SSIS job steps run as the SQL Agent service account unless you give them a proxy. Create a credential and a proxy, grant it to the right subsystems and logins, use it in a step, and troubleshoot the access errors you'll meet. Includes an audit query for every proxy on the server.
Rehost SQL Server on EC2 with full control. Covers instance and EBS design, three move methods (AWS Application Migration Service for whole servers, backup/restore via S3, and an AG replica over Direct Connect), licensing choices, and HA after the move.
Rehost SQL Server on Azure Virtual Machines with full compatibility. Covers building the VM correctly, the three common data move methods (backup to URL, log shipping, adding an AG replica), registering with the SQL IaaS Agent extension, and the cutover.
SQL Server Agent publishes its own Windows performance counters for jobs, steps and alerts. Find them, collect them with Get-Counter and logman, and alert on failed jobs and success rate. Then go further and measure the CPU, reads and duration each running job step uses through DMVs.
Move databases to Azure SQL MI from native backups in Blob Storage using the Log Replay Service. Covers backup requirements, uploading with azcopy, continuous vs autocomplete mode, monitoring, the final cutover and common errors.
Use AWS Database Migration Service to copy SQL Server data to RDS or EC2 and keep it in sync with change data capture until cutover. Covers source prerequisites (MS-CDC or replication), schema-first approach, task settings for LOBs, validation, monitoring latency and cutover.
Lift existing SSIS packages into Azure Data Factory with the Azure-SSIS Integration Runtime. Covers SSISDB on Azure SQL MI or Database, package deployment, connecting to on-premises sources, custom components, scheduling and cost control, plus when to rebuild instead.
Decide where SQL Server Integration Services and Reporting Services run after an AWS migration. Covers SSIS and SSRS as RDS options, their limits, running them on EC2 instead, migrating the SSIS catalog and report server content, and modern alternatives.
The MySQL commands and queries you use every week. Covers the mysql client and mysqladmin, server status and variables, sessions and locks, slow queries with the sys schema, InnoDB health, sizes, indexes, users, binlogs, replication with GTIDs, and backups with mysqldump, MySQL Shell and XtraBackup.
Move databases to Amazon RDS for SQL Server using .bak files in S3. Covers the option group and IAM role, uploading backups, full + differential + log restores with NORECOVERY for small cutover windows, task monitoring and post-restore steps.
Move a database into Azure SQL Database. Covers fixing compatibility blockers first, choosing between Azure Database Migration Service (offline), BACPAC and transactional replication (minimal downtime), and the post-move settings that matter.
Use the Managed Instance link, which is built on distributed availability groups, to replicate on-premises databases to Azure SQL MI in near real time, cut over with a planned failover in seconds, and keep the option to fail back with SQL Server 2022.
The Oracle Database commands DBAs reach for every week. Covers SQL*Plus and startup/shutdown, CDB and PDB navigation, sessions, locks and waits, top SQL, tablespaces and FRA, redo and archivelogs, RMAN, Data Guard broker, users and privileges, alert log and listener.
The PostgreSQL commands and queries DBAs use every week. Covers psql shortcuts, server and database info, active sessions and locks, slow queries with pg_stat_statements, sizes and bloat, vacuum and autovacuum, indexes, replication, backups with pg_dump and pgBackRest, and roles.
Use pg_blocking_pids and pg_locks to find the session at the head of a lock queue, clear it safely, and stop DDL and idle transactions from freezing the app again.
How msdb stores job and step history, and the queries that answer real questions. What failed overnight and why, how long each job really takes, which jobs are getting slower, which step is the bottleneck, and what is running right now.
Use Babelfish for Aurora PostgreSQL to move SQL Server applications off SQL Server licences with fewer code changes. Covers Babelfish Compass assessment, migration modes, creating the cluster, moving schema and data, connecting apps over TDS, and testing gaps.
How to schedule SSIS packages deployed to the SSIS catalog (SSISDB) with SQL Server Agent. Covers the SSIS job step and its options, environments for per-server settings, running under a proxy, 32-bit runtime, starting executions from T-SQL, and the SSISDB queries that show why a package failed.
Size Azure SQL, RDS and cloud VMs from real data rather than server specs. Covers which counters to collect and for how long, how to read CPU, memory, IOPS, throughput and log rate, and how to map them to service tiers and instance families.
The starting point for a SQL Server move to the cloud. Covers the 6R options for each database, every Azure and AWS landing target compared side by side, a decision flow, and how to group an estate into migration waves.
Confirm the damage with DBCC CHECKDB, find out what is corrupt, and recover with an index rebuild or a page restore before you ever reach for REPAIR_ALLOW_DATA_LOSS.
One page of tested T-SQL and PowerShell for daily SQL Server work. Covers instance and database info, sessions and blocking, waits and top queries, indexes and statistics, space and tempdb, backups and restores, Always On, Agent jobs, security and dbatools one-liners.
Use Azure Arc as the first step of a SQL Server transformation. Connect on-premises and other-cloud instances to Azure for inventory, best-practices assessment, Defender, Entra ID authentication, pay-as-you-go licensing and ESUs, and migration assessment.
Build a complete inventory of SQL Server instances, databases, features and dependencies, then assess compatibility with each target. Covers the tools for Azure and AWS, the T-SQL queries to fill the gaps, and how to turn the results into migration waves.
How SQL Server licences carry over to Azure and AWS. Covers Azure Hybrid Benefit and the 180-day dual-use right, pay-as-you-go versus reserved capacity, AWS license-included versus BYOL with License Mobility or Dedicated Hosts, and edition and core optimisation.
A target-neutral cutover runbook for moving a SQL Server database to Azure or AWS. Covers the T-minus checklist, freezing writes, final sync, switching connections, validation, go/no-go points and a tested rollback path.
SQL Server Agent has three fixed database roles in msdb that let people create, view or operate jobs without sysadmin. What each role can and can't do, how to grant them to groups, how they interact with job ownership and proxies, and how to review who has Agent access.
Job ownership decides whether a job can run at all and under which security context its steps execute. Why jobs owned by personal logins fail when people leave, which owner to choose, how ownership affects T-SQL steps versus proxies, and scripts to find and fix risky owners across the estate.
Rebuild a shared-storage SQL Server Failover Cluster Instance on EC2 using Amazon FSx for Windows File Server (SMB) or FSx for NetApp ONTAP (iSCSI) across two Availability Zones. Covers when FCI beats an AG, cluster networking on AWS, storage choice, and migration steps.
A practical operating standard for SQL Server Agent. Covers job naming and categories, ownership and security, step design with retries and logging, notifications and alerts, history retention, source control and change management, so that jobs stay understandable and fail loudly instead of silently.
SQL Agent stores schedules as bitmasks and packed integers. This post decodes freq_type, freq_interval, relative intervals and sub-day settings into plain English, and builds a report of every job, its schedules and next run time. Use it for on-call run books, overlap checks and maintenance-window planning.
RDS Custom gives a managed SQL Server with OS and sysadmin access for apps that can't run on standard RDS. Covers when to choose it, prerequisites, the shared responsibility model, migrating with native backups, automation pause for customisations, and HA.
A disabled job fails silently, because it never runs. Three ways to catch enable and disable changes on SQL Server Agent jobs, with complete scripts. A low-risk polling job that compares a snapshot, an msdb trigger for instant email, and SQL Server Audit to record exactly who made the change.
SQL Server Agent keeps only 1,000 history rows by default, 100 per job, so busy servers lose history within days. Three ways to keep what you need. Raise the row limits, switch to time-based purging, or archive history to a table, with scripts and msdb sizing advice.
Check replica and database state with DMVs, interpret send and redo queues, and resume or reseed a database that is NOT SYNCHRONIZING or SUSPENDED.
Turn RPO and RTO targets into backup schedules for each engine. Covers retention rules, the 3-2-1-1-0 approach with immutable copies, and a restore testing routine that proves the backups work.
An engine-neutral runbook for database DR drills. Covers scope and success criteria, pre-checks, measuring the actual RPO and RTO, application validation, failback and the evidence auditors ask for.
A common monitoring baseline for SQL Server, PostgreSQL, Oracle and MySQL. Covers availability, capacity, performance, replication and backup alerts with thresholds, plus the dashboards and query-level telemetry worth collecting.
Set a patching policy for database engines and their operating systems. Covers cadence by severity, test rings, rolling patching for HA clusters, pre-checks, rollback plans and compliance reporting.
A one-page standard for building databases consistently across SQL Server, PostgreSQL, Oracle and MySQL. Covers tiers, naming, versions, storage layout, configuration baselines, ownership and the hand-over checklist.
A security baseline for any production database. Covers named and federated identities, least privilege, network isolation, TLS, encryption at rest, auditing, secret rotation and regular access reviews.
Turn on the slow query log, summarise it with mysqldumpslow or pt-query-digest, use sys schema views for live analysis, then read EXPLAIN and add the right index.
Generate AWR and ASH reports for the right window, then read DB time, top wait events, load profile and top SQL to find the real bottleneck instead of guessing.
Design a MongoDB replica set that survives a node or zone failure. Covers member layout, priorities, arbiters (and why to avoid them), majority write concern, read preferences, rolling maintenance and monitoring.
Find slow operations with the profiler or logs, read explain('executionStats') for COLLSCAN and in-memory sorts, and build compound indexes with the Equality-Sort-Range rule.
Get in when every slot is taken, see which users and hosts hold connections, kill sleepers and long queries, and right-size max_connections and pools.
Set up a MySQL replica in a DR region using GTID-based asynchronous replication. Covers seeding with the clone plugin or XtraBackup, securing the channel, monitoring lag, promoting the replica, and rebuilding replication for failback.
Build a three-node MySQL InnoDB Cluster with MySQL Shell, put MySQL Router in front of the applications, understand single-primary failover and quorum, and run status checks, switchovers and recovery.
Read SHOW REPLICA STATUS correctly, then fix the usual causes of lag, from single-threaded appliers and missing primary keys to large transactions.
Configure a single MySQL 8.4 LTS server for production. Covers the InnoDB buffer pool and redo log, durability settings, binlogs for point-in-time recovery, connection limits, slow query logging, secure users and XtraBackup.
Confirm the fast recovery area or archive destination is full, free space safely with RMAN after backing up archive logs, and keep it from happening again.
Unlock Oracle accounts, find which host keeps locking them with failed logins, and use profiles so application accounts don't expire in production.
Run Oracle Data Guard role changes safely with DGMGRL. Covers health checks, protection modes, a planned switchover, an emergency failover, reinstating the old primary with Flashback Database, and Fast-Start Failover.
Install Oracle Database 19c or 23ai as a single instance on Oracle Linux. Covers the preinstall RPM, storage layout, silent install, creating a CDB/PDB, memory settings, archivelog mode, RMAN backups and basic security.
Get real HA out of Oracle RAC. Covers database services with preferred and available instances, SCAN listeners, client failover (FAN, TAC and Application Continuity), rolling maintenance, health checks and common failure scenarios.
Use RMAN's Data Recovery Advisor and validate commands, restore a single lost datafile online, and perform full and point-in-time recovery of a database.
Check real usage against MAXSIZE, add or resize datafiles safely, deal with TEMP and UNDO errors, and find the segments that grew.
Find who holds the connections, clean up idle-in-transaction sessions, and fix the cause with connection pooling instead of raising max_connections.
Protect PostgreSQL against site or region loss with a streaming standby in the DR region and a pgBackRest repository there too. Covers a Patroni standby cluster, promoting the standby, PITR from object storage, and failback.
Build a three-node PostgreSQL cluster with automatic failover using Patroni and etcd, route clients with HAProxy or a VIP, choose synchronous mode settings, and run switchovers and health checks.
Prepare, check, run and validate a PostgreSQL major version upgrade with pg_upgrade, including extensions, replicas, statistics and a rollback plan.
Configure a single PostgreSQL server for production. Covers OS settings, memory parameters, WAL and checkpoint tuning, autovacuum, connection pooling, logging, pg_hba security and pgBackRest backups.
Measure write, flush and replay lag from the primary, tell network and disk bottlenecks from replay conflicts, and handle canceled queries on hot standbys.
Use Query Store to find a query whose plan changed, compare the old and new plans, force the good plan as an immediate fix, and address the root cause.
Run EXPLAIN with the right options, spot misestimates, sequential scans, sorts spilling to disk and nested loops gone wrong, and know which fix each one needs.
Read INFO memory, choose the right maxmemory-policy, find big keys and missing TTLs, and handle fragmentation on self-managed and managed Redis.
Run a single Redis or Valkey node safely. Covers when standalone is enough, RDB vs AOF persistence, maxmemory and eviction policies, kernel settings, ACLs and TLS, backups, and the signs that you need replication.
Restore full, differential and log backups side by side to the moment before a bad DELETE or UPDATE, then copy the lost rows back, without overwriting the production database.
Choose and build a SQL Server DR option for a second site or region. Covers an asynchronous AG replica, distributed availability groups, and log shipping, with RPO/RTO, network needs and a forced-failover runbook.
Use log_reuse_wait_desc to see why the log can't be truncated, fix the specific cause (missing log backups, long transactions, replication, AG), and shrink only when it's safe.
Choose between an FCI and an AG for SQL Server high availability. Compares what each protects, storage, licensing and failover behaviour, then walks through building a two-replica synchronous AG with a listener and checking its health.
Build a production standalone SQL Server instance properly. Covers volume layout, service accounts, setup options, tempdb, memory, MAXDOP, cost threshold, instant file initialisation, maintenance jobs and backups.
Identify which sessions and objects are filling tempdb, recognise PAGELATCH allocation contention, and configure tempdb files correctly.
Find tables with dead tuples, the long transactions that block vacuum, and transaction ID wraparound risk, then tune autovacuum for each table.
Find the head blocker in seconds, pull deadlock graphs from the system_health session, and pick the right long-term fix, from indexes to RCSI.
No posts in this category yet.