升級 Database EngineUpgrade Database Engine

本主題提供了您在準備和了解升級程序時所需要的資訊,其中涵蓋:This topic provides the information that you will need to prepare for and understand the upgrade process; it covers:

  • 已知的升級問題。Known upgrade issues.

  • 升級前的工作和考量。Pre-upgrade tasks and considerations.

  • 升級 Database EngineDatabase Engine之程序主題的連結。Links to procedural topics for upgrading the Database EngineDatabase Engine.

  • 將資料庫移轉到 [SQL Server]SQL Server之程序主題的連結。Links to procedural topics for migrating databases to [SQL Server]SQL Server.

  • 容錯移轉叢集的考量。Considerations for failover clusters.

  • 升級後的工作和考量。Post-upgrade tasks and considerations.

已知的升級問題Known Upgrade issues

在升級 Database EngineDatabase Engine 之前,請檢閱 SQL Server Database Engine 回溯相容性Before upgrading the Database EngineDatabase Engine, review SQL Server Database Engine Backward Compatibility. 如需所支援升級狀況和升級已知問題的相關資訊,請參閱支援的版本與版本升級For information about supported upgrade scenarios and upgrade known issues, see Supported Version and Edition Upgrades. 如需其他 [SQL Server]SQL Server 元件的回溯相容性內容,請參閱回溯相容性For backward compatibility content for other [SQL Server]SQL Server components, see Backward Compatibility.

重要

[SQL Server]SQL Server 的一個版本升級到另一個版本之前,請確認您目前使用的功能在您想要升級後的版本中有受到支援。Before you upgrade from one edition of [SQL Server]SQL Server to another, verify that the functionality that you are currently using is supported in the edition to which you are upgrading.

注意

當您升級至SQL Server 2014SQL Server 2014從舊版的[SQL Server]SQL ServerEnterprise edition 中,選擇 Enterprise Edition:核心為基礎的授權和 Enterprise Edition。When you upgrade to SQL Server 2014SQL Server 2014 from a prior version of [SQL Server]SQL Server Enterprise edition, choose between Enterprise Edition: Core-based Licensing and Enterprise Edition. 這些 Enterprise Edition 只有在授權模式方面不同。These Enterprise editions differ only with respect to the licensing modes. 如需詳細資訊,請參閱 Compute Capacity Limits by Edition of SQL ServerFor more information, see Compute Capacity Limits by Edition of SQL Server.

升級前檢查清單Pre-Upgrade Checklist

[SQL Server]SQL Server 安裝程式支援從舊版升級 [SQL Server]SQL ServerUpgrading [SQL Server]SQL Server from an earlier version is supported by the [SQL Server]SQL Server Setup program. 您也可以從舊版的 [SQL Server]SQL Server 移轉資料庫。You can also migrate databases from previous [SQL Server]SQL Server versions. 您可以從一個 [SQL Server]SQL Server 執行個體移轉到同一部電腦的另一個執行個體,或是從另一部電腦的 [SQL Server]SQL Server 執行個體移轉。Migration can be from one [SQL Server]SQL Server instance to another on the same computer, or from a [SQL Server]SQL Server instance on another computer. 移轉選項包括使用複製資料庫精靈、備份與還原功能、使用 [SQL Server]SQL ServerIntegration ServicesIntegration Services 匯入與匯出精靈,以及大量匯出/大量匯入方法。Migration options include use of the Copy Database Wizard, Backup and restore functionality, use of the [SQL Server]SQL ServerIntegration ServicesIntegration Services Import and Export Wizard, and bulk export/bulk import methods.

在升級 Database EngineDatabase Engine之前,請檢閱下列文章:Before upgrading the Database EngineDatabase Engine, review the following:

在您升級 [SQL Server]SQL Server之前,請檢閱下列問題並進行變更:Review the following issues and make changes before you upgrade [SQL Server]SQL Server:

  • 升級 [SQL Server]SQL Server 的執行個體時,如果 [SQL Server]SQL Server Agent 編列在 MSX/TSX 關聯性中,請先升級目標伺服器,然後再升級主要伺服器。When upgrading instances of [SQL Server]SQL Server where [SQL Server]SQL Server Agent is enlisted in MSX/TSX relationships, upgrade target servers before you upgrade master servers. 如果您先升級主要伺服器,然後再升級目標伺服器,[SQL Server]SQL Server Agent 將無法連接至 [SQL Server]SQL Server 的主要執行個體。If you upgrade master servers before target servers, [SQL Server]SQL Server Agent will not be able to connect to master instances of [SQL Server]SQL Server.

  • 從 64 位元版本的 [SQL Server]SQL Server 升級成 64 位元版本的 SQL Server 2014SQL Server 2014 時,您必須先升級 Analysis ServicesAnalysis Services,再升級 Database EngineDatabase EngineWhen upgrading from a 64-bit edition of [SQL Server]SQL Server to a 64-bit edition of SQL Server 2014SQL Server 2014, you must upgrade Analysis ServicesAnalysis Services before you upgrade the Database EngineDatabase Engine.

  • 從要升級的執行個體備份所有 [SQL Server]SQL Server 資料庫檔案,好讓您可以在必要時還原它們。Back up all [SQL Server]SQL Server database files from the instance to be upgraded, so that you can restore them, if it is required.

  • 對要升級的資料庫執行適當的 Database Console Commands (DBCC),以確定它們處於一致狀態。Run the appropriate Database Console Commands (DBCC) on databases to be upgraded to ensure that they are in a consistent state.

  • 除了使用者資料庫以外,也要評估升級 [SQL Server]SQL Server 元件所需的磁碟空間。Estimate the disk space that is required to upgrade [SQL Server]SQL Server components, in addition to user databases. 如需 [SQL Server]SQL Server 元件所需的磁碟空間,請參閱安裝 SQL Server 2014 的硬體與軟體需求For disk space that is required by [SQL Server]SQL Server components, see Hardware and Software Requirements for Installing SQL Server 2014.

  • 確定現有的 [SQL Server]SQL Server 系統資料庫 master、model、msdb 和 tempdb 都設定為自動成長,並確定它們有足夠的硬碟空間。Ensure that existing [SQL Server]SQL Server system databases - master, model, msdb, and tempdb - are configured to autogrow, and ensure that they have sufficient hard disk space.

  • 確定所有資料庫伺服器在 master 資料庫中都有登入資訊。Ensure that all database servers have logon information in the master database. 這對於還原資料庫很重要,因為系統登入資訊是位於 master 中。This is important for restoring a database, as system logon information resides in master.

  • 停用所有啟動預存程序,因為升級程序將會在升級的 [SQL Server]SQL Server 執行個體上停止及啟動服務。Disable all startup stored procedures, as the upgrade process will stop and start services on the [SQL Server]SQL Server instance being upgraded. 在啟動時處理的預存程序可能會封鎖升級程序。Stored procedures processed at startup time might block the upgrade process.

  • 確認複寫為當前的複寫,然後將之停止。Make sure that Replication is current and then stop Replication.

  • 結束所有應用程式,包括具有 [SQL Server]SQL Server 相依性的所有服務。Quit all applications, including all services that have [SQL Server]SQL Server dependencies. 如果本機應用程式連接到要升級的執行個體,則升級可能會失敗。Upgrade might fail if local applications are connected to the instance being upgraded.

  • 如需詳細資訊,請參閱在升級伺服器執行個體時將鏡像資料庫的停機時間減至最少If you use Database Mirroring, see Minimize Downtime for Mirrored Databases When Upgrading Server Instances.

升級 Database EngineUpgrading the Database Engine

您可以用版本升級覆寫 SQL Server 2005SQL Server 2005 或更新版本的安裝。You can overwrite an installation of SQL Server 2005SQL Server 2005 or later with a version upgrade. 如果在執行 [SQL Server]SQL Server 安裝程式時偵測到舊版的 [SQL Server]SQL Server ,所有舊版的 [SQL Server]SQL Server 程式檔都會升級,但是會保留舊版 [SQL Server]SQL Server 執行個體中儲存的所有資料。If an earlier version of [SQL Server]SQL Server is detected when you run [SQL Server]SQL Server Setup, all previous [SQL Server]SQL Server program files are upgraded, and all data stored in the previous [SQL Server]SQL Server instance is preserved. 此外,舊版的《 [SQL Server]SQL Server 線上叢書》將原封不動地保留在電腦上。In addition, earlier versions of [SQL Server]SQL Server Books Online will remain intact on the computer.

警告

執行 SQL Server 2014 安裝程式時,SQL Server 執行個體會因為執行升級前檢查而停止並重新啟動。When running the SQL Server 2014 setup program, the SQL Server instance is stopped and restarted as part of running the pre-upgrade checks.

警告

當您升級 [SQL Server]SQL Server 時,會覆寫先前的 [SQL Server]SQL Server 執行個體,所以它不再存在於電腦上。When you upgrade [SQL Server]SQL Server, the previous [SQL Server]SQL Server instance will be overwritten and will no longer exist on your computer. 升級之前,請備份 [SQL Server]SQL Server 資料庫以及與先前的 [SQL Server]SQL Server 執行個體相關聯的其他物件。Before upgrading, back up [SQL Server]SQL Server databases and other objects associated with the previous [SQL Server]SQL Server instance.

您可以使用 Database EngineDatabase Engine 安裝精靈來升級 [SQL Server]SQL ServerYou can upgrade the Database EngineDatabase Engine by using the [SQL Server]SQL Server Installation Wizard.

升級後的資料庫相容性層級Database Compatibility Level After Upgrade

相容性層級tempdbmodelmsdb資源資料庫升級之後,會設定為 120。The compatibility levels of the tempdb, model, msdb and Resource databases are set to 120 after upgrade. master 系統資料庫會繼續保有升級前的相容性層級。The master system database retains the compatibility level it had before upgrade.

如果使用者資料庫的相容性層級在升級前為 100 或更高層級,則在升級後仍會保持相同。If the compatibility level of a user database was 100 or higher before the upgrade, it remains the same after upgrade. 如果升級前的相容性層級為 90,則在升級後的資料庫中,相容性層級會設定為 100 (這是 SQL Server 2014SQL Server 2014所支援的最低相容性層級)。If the compatibility level was 90 before upgrade, in the upgraded database, the compatibility level is set to 100, which is the lowest supported compatibility level in SQL Server 2014SQL Server 2014.

注意

新的使用者資料庫會繼承 model 資料庫的相容性層級。New user databases will inherit the compatibility level of the model database.

移轉資料庫Migrating Databases

您可以使用 [SQL Server]SQL Server 中的備份和還原或是卸離和附加功能,將使用者資料庫移到 [SQL Server]SQL Server執行個體。You can move user databases to an instance of [SQL Server]SQL Server using backup and restore or detach and attach functionalities in [SQL Server]SQL Server. 如需詳細資訊,請參閱使用備份與還原複製資料庫資料庫卸離與附加 (SQL Server)For more information, see Copy Databases with Backup and Restore or Database Detach and Attach (SQL Server).

重要

來源和目的地伺服器上具有相同名稱的資料庫將無法移動或複製。A database that has the identical name on both source and destination servers cannot be moved or copied. 在此情況下,會將它標示為「已存在」。In this case, it will be noted as "Already exists."

如需詳細資訊,請參閱 Use the Copy Database WizardFor more information, see Use the Copy Database Wizard.

升級 Database Engine 之後After Upgrading the Database Engine

在升級 Database EngineDatabase Engine 之後,請完成下列工作:After upgrading the Database EngineDatabase Engine, complete the following tasks:

  • 重新註冊伺服器。Re-register your servers. 如需註冊伺服器的詳細資訊,請參閱註冊伺服器For more information about registering servers, see Register Servers.

  • 為確保查詢結果中語意的一致性,必須重新擴展全文檢索目錄。Re-populate full-text catalogs to ensure semantic consistency in query results.

    SQL Server 2014SQL Server 2014 會安裝新的斷詞工具,供全文索引與語意搜尋之用。installs new word breakers for use by Full-Text and Semantic Search. 編製索引及查詢時皆可使用斷詞工具。The word breakers are used both at indexing time and at query time. 如不重建全文索引目錄,可能會造成搜尋結果不一致。If you do not rebuild the full-text catalogs, your search results may be inconsistent. 當您發出全文索引查詢,尋找在舊版 [SQL Server]SQL Server 斷詞工具中與目前之斷詞工具中斷詞方式相異的片語,可能會無法擷取含有該片語的文件或資料列。If you issue a full-text query that looks for a phrase that is broken differently by the word breaker in a previous version of [SQL Server]SQL Server and the current word breaker, a document or row containing the phrase might not be retrieved. 這是索引片語所使用的分解邏輯與查詢所用者不相同所致。This is because the indexed phrases were broken using different logic than the query is using. 此方案會使用新的斷詞工具重新擴展 (重建) 全文索引目錄,讓索引與查詢時的行為一致。The solution is to repopulate (rebuild) the full-text catalogs with the new word breakers so that index time and query time behavior are identical.

    如需詳細資訊,請參閱 sp_fulltext_catalog (Transact-SQL)For more information, see sp_fulltext_catalog (Transact-SQL).

  • 設定 [SQL Server]SQL Server 安裝。Configure the [SQL Server]SQL Server installation. 為了減少系統的可攻擊介面區, [SQL Server]SQL Server 可以選擇性地安裝和啟用主要服務和功能。To reduce the attackable surface area of a system, [SQL Server]SQL Server selectively installs and enables key services and features.

  • 驗證或移除 SQL Server 2005SQL Server 2005 所產生及套用至分割區資料表和索引上之查詢的 USE PLAN 提示。Validate or remove USE PLAN hints that are generated by SQL Server 2005SQL Server 2005 and applied to queries on partitioned tables and indexes.

    [SQL Server]SQL Server 改變了在分割區資料表和索引上處理查詢的方式。changes the way queries on partitioned tables and indexes are processed. 在分割區物件上,針對 SQL Server 2005SQL Server 2005 所產生之計畫使用 USE PLAN 提示的查詢包含了無法在 SQL Server 2014SQL Server 2014中使用的計畫。Queries on partitioned objects that use the USE PLAN hint for a plan that is generated by SQL Server 2005SQL Server 2005 might contain a plan that is not usable in SQL Server 2014SQL Server 2014. 我們建議您在升級到 SQL Server 2014SQL Server 2014之後,使用以下程序。We recommend the following procedures after you upgrade to SQL Server 2014SQL Server 2014.

    當在查詢中直接指定 USE PLAN 提示:When the USE PLAN hint is specified directly in a query:

    1. 從查詢中移除 USE PLAN 提示。Remove the USE PLAN hint from the query.

    2. 測試查詢。Test the query.

    3. 如果最佳化工具未選取適當的計畫,請微調查詢,然後考慮使用所要的查詢計劃指定 USE PLAN 提示。If the optimizer does not select an appropriate plan, tune the query, and then consider specifying the USE PLAN hint with the desired query plan.

    當計畫指南中指定 USE PLAN 提示:When the USE PLAN hint is specified in a plan guide:

    1. 使用 sys.fn_validate_plan_guide 函數檢查計畫指南是否有效。Use the sys.fn_validate_plan_guide function to check the validity of the plan guide. 或者,您也可以使用 SQL Server ProfilerSQL Server Profiler中的 Plan Guide Unsuccessful 事件來檢查是否有無效的計畫。Alternatively, you can check for invalid plans by using the Plan Guide Unsuccessful event in SQL Server ProfilerSQL Server Profiler.

    2. 如果此計畫指南無效,請捨棄此計畫指南。If the plan guide is not valid, drop the plan guide. 如果最佳化工具未選取適當的計畫,請微調查詢,然後考慮使用所要的查詢計劃指定 USE PLAN 提示。If the optimizer does not select an appropriate plan, tune the query, and then consider specifying the USE PLAN hint with the query plan that you want.

    當計畫指南中指定了 USE PLAN 提示時,無效的計畫將不會造成查詢失敗。A plan that is not valid will not cause the query to fail when the USE PLAN hint is specified in a plan guide. 而是會編譯此查詢,而不使用 USE PLAN 提示。Instead, the query is compiled without using the USE PLAN hint.

在升級之前標示為已啟用或已停用全文檢索的任何資料庫,都會在升級之後維持該狀態。Any databases that were marked full-text enabled or disabled before the upgrade will maintain that status after upgrade. 升級之後,會針對所有已啟用全文檢索的資料庫自動重建及擴展全文檢索目錄。After the upgrade, the full-text catalogs will be rebuilt and populated automatically for all full-text enabled databases. 這是一項耗費時間和資源的作業。This is a time-consuming and resource-consuming operation. 您可以執行下列陳述式來暫停全文檢索索引作業:You can pause the full-text indexing operation temporarily by running the following statement:

EXEC sp_fulltext_service 'pause_indexing', 1;  

若要繼續全文檢索索引母體擴展,請執行下列陳述式:To resume full-text index population, run the following statement:

EXEC sp_fulltext_service 'pause_indexing', 0;  

另請參閱See Also

支援的版本與版本升級 Supported Version and Edition Upgrades
使用多個版本和 SQL Server 執行個體 Work with Multiple Versions and Instances of SQL Server
回溯相容性 Backward Compatibility
升級複寫的資料庫Upgrade Replicated Databases