SQL Server Upgrade Fails with Error 1712? Troubleshooting SQL 2017 Installation

Table of Contents

Upgrading SQL Server instances is a critical task for maintaining database performance, security, and access to new features. However, these operations can sometimes be fraught with unexpected errors that halt the entire process. This article provides a comprehensive guide to troubleshooting and resolving a specific issue where upgrading SQL Server 2014 or SQL Server 2016 to SQL Server 2017 fails with Error 1712 during the execution of database upgrade scripts. Understanding the nuances of this error and its underlying cause is paramount for a successful resolution and a stable SQL Server environment.

The upgrade process, while designed to be robust, involves intricate script executions that modify system databases and components. When these scripts encounter unexpected conditions, they can lead to severe operational disruptions, including the inability of the SQL Server instance to start. Our focus here will be on diagnosing the symptoms, dissecting the root cause, and providing a detailed, step-by-step resolution strategy to overcome this particular challenge.

Symptoms¶

When attempting to upgrade an existing SQL Server 2014 or 2016 instance to SQL Server 2017, the installation may terminate unexpectedly. The primary symptom of this specific issue is a failure during the execution of the ISServer_upgrade.sql upgrade script. The installer will typically display a generic message indicating a problem without much detail, often leading to confusion.

Upon closer inspection, the SQL Server setup logs or the main SQL Server Error Log will reveal a more specific error message. The most common indication of this problem is the following alert:

Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.

This message signifies that the database engine could not successfully complete its recovery process, which is a critical phase during startup and upgrade. When the recovery handle fails, it often points to a deeper issue preventing the core database services from initializing correctly.

Further examination of the SQL Server error log (located in the LOG subdirectory of your SQL Server instance’s installation path, e.g., C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\ERRORLOG) will provide a detailed sequence of events leading to the failure. You will typically observe entries similar to these, indicating the exact point of failure:

2020-10-26 10:08:09.94 spid6s      Database 'master' is upgrading script 'ISServer_upgrade.sql' from level 0 to level 500. 
2020-10-26 10:08:09.94 spid6s      --------------------------------------------- 
2020-10-26 10:08:09.94 spid6s      Starting execution of ISServer_upgrade.SQL 
2020-10-26 10:08:09.94 spid6s      --------------------------------------------- 
2020-10-26 10:08:09.94 spid6s        
2020-10-26 10:08:09.94 spid6s      Taking SSISDB to single user mode 
2020-10-26 10:08:09.94 spid6s      Setting database option SINGLE_USER to ON for database 'SSISDB'. 
2020-10-26 10:08:10.47 spid6s      Error: 1712, Severity: 16, State: 1. 
2020-10-26 10:08:10.47 spid6s      Online index operations can only be performed in Enterprise edition of SQL Server. 
2020-10-26 10:08:10.47 spid6s      Error: 917, Severity: 15, State: 1. 
2020-10-26 10:08:10.47 spid6s      An upgrade script batch failed to execute for database 'master' due to compilation error. Check the previous error message for the line which caused compilation to fail. 
2020-10-26 10:08:10.47 spid6s      Error: 912, Severity: 21, State: 2. 
2020-10-26 10:08:10.47 spid6s      Script level upgrade for database 'master' failed because upgrade step 'ISServer_upgrade.sql' encountered error 917, state 1, severity 15. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion. 
2020-10-26 10:08:10.48 spid6s      Error: 3417, Severity: 21, State: 3. 
2020-10-26 10:08:10.48 spid6s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online. 
2020-10-26 10:08:10.48 spid6s      SQL Server shutdown has been initiated 
2020-10-26 10:08:10.48 spid6s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required. 
2020-10-26 10:08:10.50 spid15s     The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/SAFHSQL01.SAFEHAVEN.com ] for the SQL Server service. 
2020-10-26 10:08:10.50 spid15s     The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/SAFHSQL01.SAFEHAVEN.com:1433 ] for the SQL Server service.

The sequence of errors is crucial here:
* Error 1712: This immediate error is clear: “Online index operations can only be performed in Enterprise edition of SQL Server.” This indicates that a script attempted an online index operation, a feature restricted to Enterprise and Developer editions.
* Error 917: Following 1712, this error states “An upgrade script batch failed to execute for database ‘master’ due to compilation error.” This signifies that the preceding error (1712) caused the upgrade script for the master database to fail compilation.
* Error 912: This is a critical system error: “Script level upgrade for database ‘master’ failed because upgrade step ‘ISServer_upgrade.sql’ encountered error 917, state 1, severity 15. This is a serious error condition which might interfere with regular operation and the database will be taken offline.” This error highlights that a fundamental upgrade step for the master database failed, deeming the SQL Server instance non-operational and forcing it offline.
* Error 3417: The final, most severe error in this chain: “Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it.” This error signifies a catastrophic failure in the master database recovery, rendering the entire SQL Server instance unable to start. The only way forward at this point is significant recovery effort.

The presence of these specific errors, particularly in the context of the ISServer_upgrade.sql script and references to SSISDB and master database recovery, strongly points to the underlying cause discussed in the next section.

Cause¶

The root cause of Error 1712 during a SQL Server 2017 upgrade is a known issue within the initial release to manufacture (RTM) upgrade scripts. Specifically, the SQL Server 2017 RTM upgrade script includes a dynamic link library (DLL) or embedded Transact-SQL code that attempts to execute online index operations.

Online index operations are an advanced feature in SQL Server that allows indexes to be rebuilt or reorganized while the underlying table remains available for data modification operations. This capability is highly beneficial for high-availability environments as it minimizes downtime during maintenance. However, this feature is explicitly restricted to the Enterprise and Developer editions of SQL Server. Standard, Web, and Express editions do not support online index operations.

The issue arises because the ISServer_upgrade.sql script, responsible for upgrading the SQL Server Integration Services Catalog database (SSISDB), was designed or compiled in a way that did not adequately check the SQL Server edition before attempting an online index operation. During the upgrade process, the master database, which orchestrates the overall upgrade, calls these component-specific scripts. When ISServer_upgrade.sql attempts to perform an online index operation on a non-Enterprise or non-Developer edition of SQL Server, the database engine immediately throws Error 1712.

This error then cascades, causing the master database’s upgrade script batch to fail (Error 917), preventing the overall script-level upgrade for master (Error 912), and ultimately making the master database unrecoverable (Error 3417). The server cannot start without a functional master database, thus leading to the complete failure of the SQL Server 2017 upgrade. This particular flaw in the RTM release meant that any upgrade to SQL Server 2017 on editions other than Enterprise or Developer was vulnerable to this specific failure if the SSISDB was present and being upgraded. Subsequent Cumulative Updates (CUs) for SQL Server 2017 addressed this oversight by modifying the upgrade scripts to either remove the online index operation or implement conditional logic to bypass it on unsupported editions.

Resolution¶

Resolving Error 1712 during a SQL Server 2017 upgrade primarily involves applying the appropriate patches that contain the fix for the faulty RTM upgrade script. However, since the instance has likely failed to start, the resolution process requires specific steps to bring the SQL Server instance to a state where patches can be applied.

Pre-Upgrade Best Practices (Prevention)¶

Before attempting any SQL Server upgrade, consider these critical steps to prevent such issues:

1. Full Backups: Always perform a complete backup of all system databases (master, msdb, model) and all user databases. Store these backups securely on separate media. This is your primary recovery mechanism.
2. Test Environment: Whenever possible, perform a dry run of the upgrade in a non-production environment that mirrors your production setup. This helps identify potential issues, like Error 1712, without impacting live systems.
3. SQL Server Upgrade Advisor: Utilize the SQL Server Upgrade Advisor tool (or equivalent tools in newer versions) to scan your existing SQL Server instances for potential upgrade blockers and deprecated features.
4. Review Release Notes and Known Issues: Always consult the official Microsoft documentation, release notes, and known issues for the target SQL Server version (SQL Server 2017 in this case) and any specific Cumulative Updates you plan to apply.
5. Apply Latest Cumulative Update (CU) during Installation: Instead of installing the RTM version of SQL Server 2017 and then patching it, it is highly recommended to integrate the latest Cumulative Update directly into the installation process. This ensures that known RTM bugs, like the one causing Error 1712, are addressed from the outset. Many SQL Server setup wizards allow you to specify a CU path or download and integrate updates during the installation.

Post-Failure Recovery Steps (If Error 1712 Occurred)¶

If your SQL Server 2017 upgrade has already failed with Error 1712, follow these steps to recover and complete the upgrade:

Step 1: Start SQL Server in Single-User Mode¶

Since the master database is corrupted or in an unrecoverable state, the SQL Server instance will not start normally. You need to start it in single-user mode to perform administrative tasks.

1. Stop SQL Server Services: Open SQL Server Configuration Manager or Windows Services and stop the SQL Server (MSSQLSERVER) service for the affected instance.
2. Start in Single-User Mode:
   • Via Command Prompt (Recommended for reliability):
     Open Command Prompt as an administrator.
     Navigate to the SQL Server Binn directory (e.g., cd "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn").
     Execute the command: sqlservr.exe -m -T4022 -T3659 -s"INSTANCENAME"
     Replace "INSTANCENAME" with your actual SQL Server instance name (e.g., MSSQLSERVER for a default instance).
     • -m: Starts SQL Server in single-user mode.
     • -T4022: Bypasses auto-start of stored procedures.
     • -T3659: Ensures all upgrade scripts run. This is crucial for completing the pending upgrade steps.
   • Via SQL Server Configuration Manager:
     Open SQL Server Configuration Manager.
     Select “SQL Server Services.”
     Right-click your SQL Server instance and select “Properties.”
     Go to the “Startup Parameters” tab.
     In the “Specify a startup parameter” box, add -m; (and potentially -T4022; and -T3659;). Click “Add,” then “Apply.”
     Start the SQL Server service. Remember to remove these parameters after successfully applying the CU and restarting normally.

Once SQL Server is running in single-user mode, you can connect to it using SQLCMD or SQL Server Management Studio (SSMS). Only one connection is allowed.

Step 2: Apply the Latest SQL Server 2017 Cumulative Update (CU)¶

The fix for Error 1712 was included in early Cumulative Updates for SQL Server 2017. Applying a recent CU will provide the corrected ISServer_upgrade.sql script.

1. Download the CU: Go to the Microsoft Update Catalog or the official SQL Server support page to download the latest Cumulative Update for SQL Server 2017. It’s always best to use the most recent one available.
2. Execute the CU Package: Run the downloaded CU executable. The CU installer will detect the partially upgraded SQL Server 2017 instance.
3. Follow the Installer Prompts: The CU installer will guide you through applying the update. It will replace the problematic RTM upgrade scripts with the fixed versions.
4. Monitor the Installation: Pay close attention to the installation logs. If the CU applies successfully, it will usually trigger a restart of the SQL Server service.

Step 3: Verify and Restart¶

After the Cumulative Update has been applied:

1. Remove Single-User Startup Parameters: If you started SQL Server using Configuration Manager with -m (and -T4022, -T3659), remove these startup parameters before attempting a normal restart.
2. Restart SQL Server Normally: Attempt to start the SQL Server service through SQL Server Configuration Manager or Windows Services.
3. Check Error Logs: Monitor the SQL Server Error Log again to ensure there are no new errors and that the instance starts cleanly. Look for messages indicating successful database recovery and that all upgrade scripts have completed.
4. Verify Version: Once the server is online, connect via SSMS and run SELECT @@VERSION; to confirm that SQL Server 2017 and the applied CU version are now correctly reported.

Alternative: Rebuilding System Databases (Last Resort)¶

If the above steps fail, or if you do not have a pre-upgrade backup of the master database, rebuilding system databases might be necessary. This is a drastic measure and should only be considered as a last resort, as it deletes all user databases and login information.

1. Locate Setup Files: Ensure you have the original SQL Server 2017 RTM setup media or files.
2. Run Setup with Rebuild Option: Open Command Prompt as Administrator and navigate to the setup files’ x64\Setup directory.
   Execute: Setup.exe /q /ACTION=REBUILDDATABASE /INSTANCENAME=YourInstanceName /SQLSYSADMINACCOUNTS=YourDomain\YourAdminAccount /SAPWD=YourStrongPassword
   • This command rebuilds the master, model, and msdb databases to their initial state.
   • WARNING: This will delete all user databases and server-level objects (logins, linked servers, jobs, etc.). You will need to restore them from backups after rebuilding.
3. Install the Latest CU: After rebuilding, immediately install the latest SQL Server 2017 Cumulative Update to ensure the ISServer_upgrade.sql script is fixed.
4. Restore Databases: Restore your master, msdb, model, and all user databases from your pre-upgrade backups. This process is complex and requires careful execution.

mermaid graph TD A[Start SQL Server 2017 Upgrade] --> B{Existing SQL Edition?} B -- Enterprise/Developer --> C[Upgrade Successful] B -- Standard/Web/Express --> D{ISServer_upgrade.sql runs?} D -- Yes --> E[Attempt Online Index Operation] E -- Fails --> F[Error 1712: Online index ops restricted] F --> G[Error 917: Upgrade script batch failed] G --> H[Error 912: Master DB script upgrade failed] H --> I[Error 3417: Cannot recover master DB] I --> J[SQL Server instance fails to start] J --> K[Action: Start SQL Server in Single-User Mode] K --> L[Action: Apply Latest SQL Server 2017 Cumulative Update] L --> M[Action: Remove Single-User Params & Restart SQL Service] M --> N{SQL Server Starts?} N -- Yes --> C N -- No --> O[Action: Rebuild System Databases (Last Resort)] O --> L

Understanding the ISServer_upgrade.sql Script¶

The ISServer_upgrade.sql script is fundamental for maintaining the integrity and functionality of the Integration Services Catalog database (SSISDB) during a SQL Server upgrade. This script is responsible for adapting the SSISDB schema and its objects to the requirements of the new SQL Server version. It handles tasks like:
* Updating internal tables and views.
* Modifying stored procedures and functions.
* Creating or altering indexes to improve performance or comply with new standards.
* Ensuring backward compatibility or transitioning to new features.

In the case of SQL Server 2017 RTM, the script’s inclusion of an online index operation without an edition check was a critical oversight. A typical online index command might look like ALTER INDEX [IX_ObjectName] ON [Schema].[TableName] REBUILD WITH (ONLINE = ON);. When this command executes on an unsupported edition, the server correctly throws Error 1712, but this halts the entire upgrade flow managed by the master database. Subsequent CUs addressed this by either modifying the ALTER INDEX statement to remove WITH (ONLINE = ON) or wrapping it in conditional logic using @@VERSION or SERVERPROPERTY('Edition') to execute it only on supported editions.

For a general understanding of SQL Server upgrade troubleshooting, you might find this conceptual video helpful:
SQL Server Upgrade Troubleshooting Guide - Identifying and Resolving Common Errors
Please note: This is a placeholder for an illustrative video; actual YouTube search results would be needed for a real link.

Best Practices for SQL Server Upgrades¶

Beyond resolving specific errors, a structured approach to SQL Server upgrades significantly reduces risks and downtime.

Comprehensive Pre-Upgrade Checklist¶

A thorough checklist ensures all prerequisites are met and potential issues are identified early.
* Hardware and Software Requirements: Verify that the server meets the minimum requirements for SQL Server 2017. This includes CPU, RAM, disk space, and operating system compatibility.
* Service Accounts: Document current service accounts and ensure they have appropriate permissions for the upgrade process.
* Disk Space: Ensure ample free disk space for the new binaries, temporary files, and potential database growth.
* Network Configuration: Confirm network connectivity and firewall rules allow communication between SQL Server and other applications.
* Application Compatibility: Test critical applications and services for compatibility with SQL Server 2017. Vendor certification or internal testing is crucial.
* Login and Permissions Audit: Document all SQL Server logins, server roles, and database user permissions. These are critical for post-upgrade validation.
* Database Health Check: Run DBCC CHECKDB on all databases to ensure no corruption exists before the upgrade. Corrupt databases can lead to unpredictable upgrade failures.
* Disable Scheduled Jobs: Temporarily disable SQL Server Agent jobs and any external applications that connect to SQL Server to prevent interference during the upgrade.

Upgrade Method Selection¶

Consider whether to perform an in-place upgrade or a side-by-side upgrade.
* In-Place Upgrade: Upgrading the existing SQL Server instance directly on the same hardware. This is generally simpler but offers less rollback flexibility and requires more downtime if issues arise.
* Side-by-Side Upgrade: Installing a new SQL Server 2017 instance on a separate server (or the same server with a different instance name), then migrating databases and objects. This offers maximum flexibility, minimal downtime for production, and an easy rollback path by simply switching back to the old server if needed. It is often the preferred method for critical production systems.

Monitoring During Upgrade¶

Even with careful planning, issues can arise. Active monitoring during the upgrade process is essential.
* Setup Logs: Continuously monitor the SQL Server setup logs (summary.txt, detail.txt) and the SQL Server Error Log. These logs provide real-time feedback on the upgrade’s progress and any encountered errors.
* System Event Logs: Check Windows Event Viewer (Application and System logs) for any related operating system errors or warnings.
* Resource Utilization: Monitor CPU, memory, and disk I/O to ensure the server isn’t bottlenecked, which could cause timeouts or performance degradation during the upgrade scripts.

Post-Upgrade Validation¶

After a successful upgrade, a thorough validation process confirms the new environment’s health and functionality.
* Version Verification: Confirm that all SQL Server components (Database Engine, SSIS, SSAS, SSRS, etc.) are at the expected SQL Server 2017 version and Cumulative Update level.
* Database Connectivity: Test connections from all critical applications and services to the upgraded SQL Server instance.
* Data Integrity: Run sample queries and reports to ensure data integrity and accuracy.
* Performance Baseline: Compare performance metrics (query execution times, index usage, resource utilization) against pre-upgrade baselines to identify any regressions.
* Security Audit: Verify that all logins, users, and permissions are correctly migrated and functional.
* Job and Agent Verification: Ensure all SQL Server Agent jobs are running as expected, and scheduled tasks are completing successfully.
* SSIS Packages, SSAS Cubes, SSRS Reports: Test all Integration Services packages, Analysis Services cubes, and Reporting Services reports to confirm their functionality in the new environment.

Performance Tuning Post-Upgrade¶

SQL Server 2017 introduced new features and internal changes.
* Compatibility Level: Consider upgrading database compatibility levels to 140 (for SQL Server 2017) to leverage new query optimizer features. Test thoroughly after changing compatibility levels.
* Query Store: Enable and configure Query Store to monitor and tune workload performance.
* Adaptive Query Processing: Explore and enable features like Adaptive Query Processing to automatically improve query performance.
* Index Maintenance: Review and update index maintenance strategies to align with new optimizer capabilities.

Related Errors and Considerations¶

While Error 1712 is specific to online index operations, other errors can also halt SQL Server upgrades:
* Permission Issues: Lack of appropriate permissions for the setup user or SQL Server service accounts can lead to access denied errors.
* Database Corruption: Pre-existing corruption in system or user databases can prevent upgrade scripts from executing successfully.
* Insufficient Resources: Running out of disk space, memory, or CPU during the upgrade can cause processes to fail or time out.
* Antivirus Interference: Aggressive antivirus software can sometimes lock files or interfere with the setup process.
* Missing Prerequisites: Failure to install necessary operating system updates or components before SQL Server can lead to installation failures.

Each of these requires a distinct troubleshooting approach, emphasizing the importance of detailed log analysis. The SQL Server Error Log and setup logs are your primary tools for diagnosing any upgrade failure.

Upgrading SQL Server is a complex process, but by understanding common pitfalls like Error 1712, implementing robust pre-upgrade strategies, and knowing how to recover from failures, database administrators can significantly improve their success rates. Always prioritize backups and testing, and stay informed about the latest cumulative updates for your SQL Server version.

Do you have any experiences with SQL Server upgrade failures? Share your insights or ask questions in the comments below! Your experiences can help others navigating similar challenges.