Collations, Compatibility Levels, Database Options, Recovery Models, and Baseline Settings

Learning outcomes

The ServiceHub application survives an engine upgrade, but one restored database sorts text differently, another remains at an older compatibility level, and a DBA changes recovery to FULL assuming that point-in-time recovery is now “on.” These are three different database-level contracts: collation affects comparison/sort semantics, compatibility level gates selected T-SQL/query-processing behavior, and recovery model controls transaction-log maintenance and restore possibilities. None of them is simply another name for the SQL Server executable version.

1. Collation is comparison/sort behavior with multiple scopes

A SQL Server collation defines rules used for textual comparison/sorting and associated code-page/case/accent behavior. The server has a default collation chosen during installation. A database has its own default collation, normally inherited from the server when not explicitly specified. Individual text columns can declare a different collation, and expressions can use COLLATE to resolve or intentionally select rules.

Changing a database’s default collation does not rewrite the collation of every existing user-defined text column. This is a common migration trap. Temp objects can also interact with tempdb collation, so cross-database/temp-table code needs explicit testing rather than assuming all collations match.

SELECT    SERVERPROPERTY('Collation') AS server_collation;GOSELECT    name,    collation_nameFROM sys.databasesWHERE name IN (N'master', N'tempdb', N'ServiceHubLab');GOUSE ServiceHubLab;GOSELECT    s.name AS schema_name,    t.name AS table_name,    c.name AS column_name,    TYPE_NAME(c.user_type_id) AS data_type,    c.collation_nameFROM sys.columns AS cJOIN sys.tables AS t ON t.object_id = c.object_idJOIN sys.schemas AS s ON s.schema_id = t.schema_idWHERE s.name = N'ops'  AND c.collation_name IS NOT NULLORDER BY t.name, c.column_id;GO

If a column reports a collation different from the database default, that is not automatically an error. It is a design difference that must be justified and tested for joins/comparisons/index behavior. Server collation is also operationally expensive to change because system databases are involved; it is not a cosmetic dropdown.

2. Engine version and compatibility level are intentionally separate

The Database Engine executable can be SQL Server 2025 (17.x) while an attached/restored database remains at compatibility level 160 or lower. Compatibility level lets organizations run a newer engine while controlling selected query-processing and T-SQL compatibility behavior during validation. It is not an “emulation mode” that turns SQL Server 2025 into SQL Server 2022, and it does not change the physical server build.

SQL Server 2025’s current native compatibility level is 170. New SQL Server 2025 databases normally use 170, but upgraded/restored databases can remain on their prior level. Many intelligent query processing features and newer syntax have compatibility prerequisites, which is why ALTER DATABASE ... SET COMPATIBILITY_LEVEL belongs in a tested upgrade plan rather than a cosmetic post-upgrade checkbox.

SELECT    SERVERPROPERTY('ProductVersion') AS engine_version,    SERVERPROPERTY('ProductUpdateLevel') AS update_level,    d.name,    d.compatibility_levelFROM sys.databases AS dWHERE d.name = N'ServiceHubLab';GO

3. Recovery model controls log maintenance and restore options—not “is data durable?”

SQL Server supports SIMPLE, FULL, and BULK_LOGGED recovery models. SIMPLE automatically reclaims reusable log space and does not support transaction-log backups or point-in-time recovery through a log-backup chain. FULL supports log backups and point-in-time recovery when the backup chain is correctly established and maintained. BULK_LOGGED is a variant intended for selected bulk-operation scenarios and has point-in-time limitations when minimally logged operations are involved.

Recovery model is not a promise that every committed transaction survives every possible failure. Durability also depends on storage, write/cache behavior, synchronous/asynchronous commit choices, delayed durability, HA state, and whether the backup/log chain can actually be restored. “FULL recovery” does not mean “full backup exists,” and switching SIMPLE → FULL does not by itself prove a usable log-backup chain.

SELECT    name,    recovery_model_desc,    log_reuse_wait_desc,    state_descFROM sys.databasesWHERE name = N'ServiceHubLab';GOSELECT TOP (10)    database_name,    type AS backup_type_code,    backup_start_date,    backup_finish_date,    is_copy_only,    has_backup_checksumsFROM msdb.dbo.backupsetWHERE database_name = N'ServiceHubLab'ORDER BY backup_finish_date DESC;GO

Chapter 01 created a COPY_ONLY full baseline backup where a server-visible backup path was available. That backup is useful as a lab baseline but deliberately does not establish a production log-backup strategy. Chapter 15 will create and restore a real chain.

4. Database options are a contract, not decoration

sys.databases exposes database properties such as AUTO_CREATE_STATISTICS, AUTO_UPDATE_STATISTICS, READ_COMMITTED_SNAPSHOT, snapshot-isolation state, PAGE_VERIFY, Query Store state, recovery model, collation, and compatibility level. Other behaviors live in sys.database_scoped_configurations. These settings can change concurrency, optimizer behavior, corruption-detection posture, logging, and plan behavior.

SELECT    name,    state_desc,    recovery_model_desc,    compatibility_level,    collation_name,    page_verify_option_desc,    is_auto_create_stats_on,    is_auto_update_stats_on,    snapshot_isolation_state_desc,    is_read_committed_snapshot_on,    is_query_store_on,    is_auto_close_on,    is_auto_shrink_onFROM sys.databasesWHERE name = N'ServiceHubLab';GOUSE ServiceHubLab;GOSELECT name, value, value_for_secondary, is_value_defaultFROM sys.database_scoped_configurationsORDER BY name;GO

A sensible baseline is explicit about correctness and operational intent. For example, AUTO_SHRINK is not a general capacity-management strategy. Query Store and statistics settings belong to a performance observability/tuning plan. Row-versioning options belong to a concurrency design and should not be toggled blindly because application transaction behavior can change.

5. Deliberately wrong approach A: change database collation and assume every column changed

ALTER DATABASE ... COLLATE changes the database default used by new objects and system metadata behavior, but existing user-defined text columns can retain their existing collation. A migration that claims success after only checking sys.databases.collation_name can therefore leave mixed-collation columns behind.

6. Deliberately wrong approach B: switch to FULL and stop thinking about backups

An operator executes ALTER DATABASE ServiceHubLab SET RECOVERY FULL and tells management that point-in-time recovery is enabled. No data backup starts the new log chain, no log-backup schedule exists, and the transaction log eventually grows. The setting changed, but the recovery capability was never operationalized.

7. Hands-on lab: create an explicit settings baseline

Create a disposable database whose important baseline choices are visible in T-SQL. We do not alter ServiceHubLab, because later chapters depend on its established starter baseline.

USE master;GOIF DB_ID(N'ServiceHubSettingsProbe') IS NOT NULLBEGIN    ALTER DATABASE ServiceHubSettingsProbe SET SINGLE_USER WITH ROLLBACK IMMEDIATE;    DROP DATABASE ServiceHubSettingsProbe;END;GOCREATE DATABASE ServiceHubSettingsProbe;GOALTER DATABASE ServiceHubSettingsProbe SET COMPATIBILITY_LEVEL = 170;GOALTER DATABASE ServiceHubSettingsProbe SET RECOVERY SIMPLE;GOALTER DATABASE ServiceHubSettingsProbe SET PAGE_VERIFY CHECKSUM;GOALTER DATABASE ServiceHubSettingsProbe SET AUTO_CREATE_STATISTICS ON;GOALTER DATABASE ServiceHubSettingsProbe SET AUTO_UPDATE_STATISTICS ON;GOALTER DATABASE ServiceHubSettingsProbe SET AUTO_SHRINK OFF;GOALTER DATABASE ServiceHubSettingsProbe SET AUTO_CLOSE OFF;GOSELECT    SERVERPROPERTY('ProductVersion') AS engine_version,    SERVERPROPERTY('Collation') AS server_collation,    d.name,    d.compatibility_level,    d.collation_name,    d.recovery_model_desc,    d.page_verify_option_desc,    d.is_auto_create_stats_on,    d.is_auto_update_stats_on,    d.is_auto_shrink_on,    d.is_auto_close_onFROM sys.databases AS dWHERE d.name = N'ServiceHubSettingsProbe';GO

If your lab is not actually SQL Server 2025, do not force compatibility level 170; use the level documented for that engine and record the difference. This course’s declared baseline is SQL Server 2025, so an older engine means you are running an alternate lab and should label it.

USE master;GOIF DB_ID(N'ServiceHubSettingsProbe') IS NOT NULLBEGIN    ALTER DATABASE ServiceHubSettingsProbe SET SINGLE_USER WITH ROLLBACK IMMEDIATE;    DROP DATABASE ServiceHubSettingsProbe;END;GO

Verification checklist

• You record server and database collation separately.
• You can identify the collations of existing ServiceHub text columns.
• You record engine build and compatibility level as separate values.
• You can explain why SIMPLE/FULL/BULK_LOGGED affect the backup/log strategy.
• You can state why FULL recovery alone does not create point-in-time restore capability.
• You leave the reusable ServiceHubLab baseline unchanged.

8. Production judgment: baseline settings are part of application compatibility

Put database settings under change management just like schema. A production manifest should capture engine version/CU, database compatibility level, collation, recovery model, Query Store/statistics/concurrency options, database-scoped configuration, backup chain state, and any deliberate deviations from defaults. When migrating or upgrading, compare both the old and new manifests before blaming the engine for changed behavior.

With Chapter 02 complete, you can now separate the service/instance connection path, system-database responsibilities, physical files/filegroups, configuration scopes, and database-level settings. Chapter 03 moves into T-SQL itself: batches, GO as a client separator, variables, expressions, NULL, data types, conversions, and error-tolerant parsing.

Authoritative references

• Set or change the database collation — database default collation and existing-column caveats
• Set or change server collation — instance collation scope and operational implications
• View or change compatibility level — database compatibility versus engine version
• Recovery models — SIMPLE, FULL, BULK_LOGGED and restore/log-maintenance implications
• View or change recovery model — safe transitions and log-chain recommendations
• ALTER DATABASE — database options, compatibility, recovery and file configuration syntax