SQL Server数据库迁移：设置兼容级别100的可行性及风险问询

Great question—this is a super common scenario when upgrading SQL Server while clinging to legacy apps that haven’t been updated. Let’s break this down clearly:

Absolutely. Compatibility level is a database-level setting, so after migrating your database (via backup/restore, detach/attach, or the Data Migration Assistant), you can set it to 100 (which maps to SQL Server 2008R2) with this T-SQL command:

ALTER DATABASE [YourApplicationDB] SET COMPATIBILITY_LEVEL = 100;

Just make sure you have the necessary permissions (ALTER ANY DATABASE or ALTER on the target database) to run this.

No—they’re similar, but not identical. Here’s the breakdown:

• What stays consistent:
  • T-SQL syntax behavior for most legacy constructs (e.g., CONVERT style defaults, DATEFIRST handling, and deprecated function behavior).
  • The legacy cardinality estimator (CE) is used for query optimization, which matches how 2008R2 plans queries.
  • Newer SQL Server 2014/2016 features (like In-Memory OLTP, Columnstore Indexes v2) won’t be enabled by default, so your app won’t accidentally use them.
• What changes (even at level 100):
  • Underlying engine improvements: The storage engine, transaction log system, and memory management have all been updated in 2014+. These changes are transparent to most apps, but can affect edge cases (e.g., lock behavior, backup performance).
  • Security defaults: 2014+ has stricter default security settings (e.g., sa account is disabled by default, stronger password policies, and changes to CONTROL SERVER permissions).
  • System catalog differences: Some sys views have new columns or slight behavior changes. If your app or custom scripts query system views directly, this could cause issues.
  • Version detection: Apps that check @@VERSION or SERVERPROPERTY('ProductVersion') will see the 2014/2016 version string, not 2008R2. Some legacy apps hardcode version checks and will refuse to connect because of this.

• Security permission mismatches: If your app uses SQL logins or Windows groups, you may need to reconfigure permissions. 2014+ removes some legacy permissions and adds new ones, so test login access thoroughly.
• Query performance surprises: Even with the legacy CE, the underlying engine’s optimizations can change query execution plans. Run performance tests on critical queries to catch any slowdowns or deadlocks.
• Deprecated feature dependencies: Some 2008R2 features (like sp_dboption or certain trace flags) are deprecated in 2014+. While they may still work at level 100, they could break in future updates. Audit your app’s code for deprecated calls.
• One-way migration: Once you restore a 2008R2 database to 2014/2016, you can’t restore it back to 2008R2 without using a data export tool (like SSIS or bcp). Plan for a rollback strategy before migrating production.
• Third-party app quirks: Some apps have hidden checks for the SQL Server version (not just compatibility level). Test the full app workflow in a staging environment to confirm it works—don’t assume compatibility level alone will fix everything.

Pro tip: Always test this setup in a staging environment that mirrors production exactly before touching live data. If possible, reach out to the third-party app vendor to ask if they officially support running their app on SQL Server 2014/2016 with compatibility level 100—many do, but some have specific caveats.

内容的提问来源于stack exchange，提问作者Kevin