How do you automate SQL Server across many instances?

    Last updated · DB24 Team

    Automate the work that must happen the same way on every instance: inventory, versions, backup checks, integrity checks, index and statistics maintenance, alerts, change tracking and reporting. The usual ways are scripts built on free tools such as dbatools and Ola Hallengren's Maintenance Solution, SQL Server Agent's multiserver administration, monitoring tools and automation platforms, and many teams combine them. Whichever you choose, someone has to make sure new instances are included and own the alerts.

    Key takeaways

    • As instances multiply, the risk is drift: one instance without integrity checks, one new database without a backup, one alert going to someone who has left. Every instance needs the same treatment, including the newest.
    • SQL Server has built-in parts. In multiserver administration, jobs are defined once on a master server and downloaded by target servers (Microsoft). A Central Management Server runs queries and policies against a group of servers at once.
    • Free tools cover much of the routine. dbatools is a free, open-source PowerShell module, and Ola Hallengren's Maintenance Solution runs backups, integrity checks and index and statistics maintenance. The rollout, the results and the overview are yours to build.
    • When you compare tools or platforms, ask what each one runs and what it only reports, how new instances get included, where alerts go, and what it costs over a year for the same scope.

    What should be automated when the number of instances grows?

    The list does not change as the estate grows. What changes is that nobody can check each instance by hand.

    • Inventory: every instance and database, with its owner and the business system it supports. SERVERPROPERTY returns the version, edition and update level of each instance. The hard part is the instances nobody listed.
    • Versions and updates: each build compared with Microsoft's latest updates and the support dates.
    • Backup checks: per database and backup type, not per job. What to check is in SQL Server backup monitoring.
    • Integrity checks: DBCC CHECKDB on every database, with someone who acts on errors.
    • Index and statistics maintenance: Microsoft says the benefit of index maintenance ranges "from vitally important to minuscule", and that updating statistics can often give the same gain "at a much lower resource cost" (Microsoft). Decide per workload, then apply it the same way everywhere.
    • Alerts: SQL Server Agent can fire alerts on events and performance conditions. The rules and recipients should match on every instance, with alerts to a shared address.
    • Change tracking: who changed logins, roles, permissions, configuration and schema, and when. What to watch is in How do you monitor SQL Server security continuously?
    • Reporting: one dated status for the estate that management, auditors and, under NIS2, supervisors can read.

    What are the usual ways to automate many SQL Server instances?

    • Scripts with free tools. Ola Hallengren's SQL Server Maintenance Solution is a free set of scripts for backups, integrity checks and index and statistics maintenance on all editions of SQL Server 2017, 2019, 2022 and 2025, with a last supported version for older releases. dbatools describes itself as "A free, open-source PowerShell module with 700+ commands" to "Manage one instance or thousands with simple, consistent commands." You set the schedules, roll them out to new instances, collect the results and keep the code current.
    • SQL Server Agent multiserver administration. Microsoft calls automating administration across multiple instances "multiserver administration" (Microsoft). Jobs are defined on a master server (MSX); target servers (TSX) download them, run them and report the status back. Each target server reports to only one master server, and Microsoft advises using the most recent version as the master. The feature is not supported on Azure SQL Managed Instance (Microsoft).
    • Central Management Server and Policy-Based Management. A Central Management Server keeps a list of instances in groups and runs Transact-SQL and policies "on multiple servers at the same time". Policy-Based Management checks settings against policies on demand, on change, or on a schedule through a SQL Server Agent job.
    • Monitoring tools collect metrics, show them and alert. When one reports a failed backup or a full disk, a person acts.
    • Automation platforms run the routines on every instance on a schedule, log each run and alert on deviations. Someone still sets thresholds, receives the alerts and decides.

    How these options compare with a hired DBA, a consultant or a managed service is covered in Alternatives to a full-time SQL Server DBA.

    How do you compare SQL Server automation tools and platforms?

    Compare the work each option takes off your hands, not the dashboards. Ask a free tool you would run yourself the same questions as a vendor.

    Criteria for choosing a SQL Server automation tool or platform: what to compare and what to ask
    CriterionWhat you compareQuestions to ask
    CoverageWhich instances, versions and editions are includedDoes it find instances, or does someone add them? What happens when a new instance or database appears? Does it cover Express, which has no SQL Server Agent?
    Inventory and versionsVersion, build and support status per instanceWhere does the list of current updates come from, and how often is it refreshed? Does it show end-of-support dates?
    Runs or reportsWhich routines it executes and which it only showsDoes it run backups, DBCC CHECKDB and index and statistics maintenance itself? Inside maintenance windows? Is every run logged?
    Backup checksBackup state per database, also when another tool takes the backupsDoes it alert per database when a backup fails or falls behind? Does it read SQL Server's backup history or only its own jobs?
    AlertsThresholds, recipients and noiseCan thresholds differ per instance or group? Can alerts go to a shared address? Can objects be excluded without hiding real problems?
    Security and changesLogins, roles, permissions and configurationDoes it record who changed what, and when? Does it alert on a new sysadmin member?
    StandardisationThe same settings on every instanceCan configuration be set once for a group of instances? How much work is it to add one more instance?
    Footprint and accessWhat is installed, which rights it needs, what it collects and where that is keptDoes it need sysadmin permanently or only during installation? Does any data leave your network? Does it read the contents of your databases?
    History and reportingHow long history is kept and who can read itCan you show an auditor what happened last quarter? Can reports be exported? Is there a view for management?
    People dependencyWho keeps it runningIf the person who set it up leaves, who maintains the scripts, jobs or configuration?
    Cost over a yearHow the cost arises for the same scopePer instance, per server, per core, or your own time? What is included and what costs extra?

    When are SQL Server's own tools and free scripts enough?

    With a few instances and someone who knows SQL Server and has the time, maintenance plans or Ola Hallengren's scripts, perhaps with multiserver jobs, can cover the routine. Maintenance plans run as SQL Server Agent jobs (Microsoft), and the Agent service is disabled at installation "unless the user explicitly chooses to autostart the service" (Microsoft).

    As the estate grows, more of the work sits around the jobs: including new instances, reading job history across servers, routing alerts and showing an auditor the result. The tasks that still need an owner are listed in How to run SQL Server without a DBA.

    How DB24 helps

    DB24 collects the same facts from every instance where it is installed, can run the same routines on each, and shows the results in one place, so whoever owns SQL Server sees the whole estate on one page:

    • One setup per instance, one overview: a DB24 Runtime on each SQL Server runs the jobs and collects data, the Datastore gathers it from all managed instances, and the Portal is where you configure and view it. Configuration is pushed from the Portal to each Runtime (overview, Runtimes).
    • Maintenance: DB24 can run integrity checks with DBCC CHECKDB, index maintenance, statistics updates and file growth on each instance, inside your maintenance windows, checking disk space first and logging every operation (maintenance).
    • Backups: backup state per database across the estate, also when another tool takes the backups, and an alert when a backup has failed or is outdated, with warning and critical thresholds (DB24 Backup, notifications). With verification on, DB24 runs RESTORE VERIFYONLY after each backup it takes; no data is restored.
    • Versions and patch status: version, build and patch status per instance, in lifecycle and patching reports kept current against Microsoft's releases.
    • Changes and security: change tracking of logins, server roles and schema, with an alert when a new login appears or server role membership changes.
    • Groups: tags and key/value pairs on instances and databases for filtering and reporting across the estate (release notes 2026.2).
    • One score and a record: an AiQ score from 0 to 10, re-evaluated nightly, with recommendations ranked by impact, plus daily and monthly briefs that can be viewed and exported in the portal (release notes 2026.3).
    • Your own reports: read-only, version-controlled views for integrations (integration views).

    DB24 collects only metadata and performance data, never the contents of your databases.

    DB24 is priced per SQL Server instance. Contact us for a quote.

    Installing updates, restore tests and decisions about risk stay with your team; DB24 shows where they are needed and keeps the record.

    To see it on your own instances, contact us.

    Frequently asked questions

    Can SQL Server Agent run the same job on many servers?

    Yes, through multiserver administration. You define the job once on a master server and enlist the other instances as target servers, which download the job and report its status. Each target server has one master server. It does not work on Express edition, which has no SQL Server Agent, or on Azure SQL Managed Instance.

    How do we find SQL Server instances nobody listed?

    Scan for them. dbatools' Find-DbaInstance looks for instances through Active Directory lookups, the same instance enumeration SSMS uses and IP range scans, among other methods. Compare the result with your list and give every instance an owner.

    What about SQL Server Express?

    Express edition does not include SQL Server Agent (Microsoft), so maintenance plans and multiserver jobs cannot run there. The work has to be scheduled from outside the instance, and Express instances belong in the inventory like any other.

    How much does DB24 cost?

    DB24 is priced per SQL Server instance. Contact us for a quote.

    Sources

    1. Microsoft Learn, Automated administration across an enterprise
    2. Microsoft Learn, Create a multiserver environment
    3. Microsoft Learn, Create a Central Management Server and server group
    4. Microsoft Learn, Administer servers by using Policy-Based Management
    5. Microsoft Learn, SQL Server Agent
    6. Microsoft Learn, Alerts
    7. Microsoft Learn, Maintenance plans
    8. Microsoft Learn, Editions and supported features of SQL Server 2022
    9. Microsoft Learn, SERVERPROPERTY
    10. Microsoft Learn, DBCC CHECKDB
    11. Microsoft Learn, Maintain indexes optimally
    12. Microsoft Support, Latest updates and version history for SQL Server (KB321185)
    13. Microsoft Lifecycle, SQL Server
    14. dbatools
    15. dbatools, Find-DbaInstance
    16. Ola Hallengren, SQL Server Maintenance Solution
    17. DB24 Docs, Overview
    18. DB24 Docs, Runtimes
    19. DB24 Docs, Maintenance overview
    20. DB24 Docs, DB24 Backup
    21. DB24 Docs, Notifications