Compare the configuration of two SQL Server instances

Two servers that should behave the same rarely stay the same. One replica of an availability group got a different max degree of parallelism, the new server misses a trace flag, a database on the standby has another recovery model. You notice when a query is slow on one server only, or after a failover.

Comparing the instances side by side finds this configuration drift before it hurts. This article shows how to do it by hand and with the server comparison of SQLTreeo Compare.

By hand

The instance settings live in sys.configurations. Run this on both servers and compare the output:

SELECT name, value, value_in_use
FROM sys.configurations
ORDER BY name;

With a linked server you can let SQL Server show the differences:

SELECT l.name, l.value_in_use AS this_server, r.value_in_use AS other_server
FROM sys.configurations l
JOIN [OTHERSERVER].master.sys.configurations r ON r.configuration_id = l.configuration_id
WHERE l.value_in_use <> r.value_in_use;

Trace flags that are enabled globally:

DBCC TRACESTATUS(-1);

That covers two of the places where servers differ. The others each need their own queries on both sides: logins and server roles, linked servers, database options and files, SQL Server Agent jobs, operators and alerts in msdb, Database Mail, Resource Governor, audits and Extended Events sessions.

With SQLTreeo Compare

1. Choose Compare server configuration on the start page.
2. Click the source and target tiles and pick the two instances. No database is needed.
3. Press Compare.

The result lists every setting and object that exists on one server only or differs between the two, grouped and filterable in the same way as a schema comparison. Select an item to see its properties on both sides.

What is compared

Area	Items
Instance	Server properties, sp_configure settings, trace flags, endpoints, server triggers, backup devices, linked servers
Server security	Logins, server roles and their members, server permissions, credentials, audits and audit specifications
Databases	Options such as recovery model, compatibility level, collation, owner, snapshot isolation and Query Store, the database files, and database scoped configurations
SQL Server Agent	Agent properties, jobs with their steps and schedules, alerts, operators and proxies
Database Mail	Accounts, profiles and settings
Resource Governor	Configuration, resource pools and workload groups
Monitoring	Extended Events sessions

A section that cannot be read, because the login lacks permission or the feature does not exist on that platform, is reported as a warning. The rest of the comparison continues.

Options

Servers differ on purpose as well. These options keep the expected differences out of the result:

Option	Use it when
Ignore CPU, memory and host properties	The servers have different hardware
Ignore product version and patch level	One server is patched ahead of the other
Ignore file system paths, Ignore database file paths	Drive letters and folders differ
Ignore database file sizes	On by default; switch it off to compare sizes
Ignore fixed server role membership	Membership of roles such as sysadmin differs by design
Compare configured values only (not running values)	A setting is waiting for a restart
Include system databases	You also want master, model, msdb and tempdb
Include databases, Exclude databases	Only some databases matter; * is a wildcard

Under Object types to compare you can narrow the comparison, for example to the sp_configure settings and trace flags only.

Script the differences

All differences are ticked; untick what you do not want. Script… opens the server configuration script for the ticked items. It applies the settings of the source to the target:

• sp_configure values with RECONFIGURE, and a note when a setting needs a restart.
• DBCC TRACEON for trace flags, with a reminder to add the flag to the startup parameters.
• Logins, server roles, role membership and server permissions.
• Agent jobs, operators, alerts and proxies, Database Mail accounts and profiles, Resource Governor pools and groups.

Secrets cannot be read from a server. Passwords of SQL logins, credential secrets, linked server passwords and SMTP passwords are written as placeholders, and the script starts with a MANUAL STEPS list of everything you have to fill in or do yourself. Databases are never created or dropped by this script.

Clear Include statements that remove objects and settings that only exist on the target to get a script that leaves those in place. Items that differ, such as jobs, alerts, proxies and linked servers, are still dropped and re-created with the definition of the source.

Then save the script, copy it, or run it on the target. A configuration script does not run in a transaction: when a statement fails, the statements before it stay applied. Review every statement before you run it on a production server.

Reading the script is free. Copying, saving or running it works during the 30-day trial or with a license, see Trial and licensing.

From the command line

sqlcompare server --source "Server=prod1;Integrated Security=true" --target "Server=prod2;Integrated Security=true" --ignore-hardware --ignore-paths

Only the instance settings and trace flags, as an HTML report:

sqlcompare server --source "Server=prod1;Integrated Security=true" --target "Server=prod2;Integrated Security=true" --include Configurations,TraceFlags --format html --output settings.html

--script configure-prod2.sql --no-drops writes the configuration script without the statements that remove what only exists on the target. The exit code is 1 when the servers differ, so a scheduled run can alert you when two servers drift apart.

Related

• Compare SQL Agent jobs and logins between two servers
• Command line reference