[SQL Server][Upgrade]SQL Server 2005移轉資料庫檔案到SQL Server 2017

這次不用升級SQL Instance版本(就地升級),但要移轉幾個User Database到新機器上新版本的SQL Instance。在學校曾上過SQL 2000的課,幾年前重新接觸SQL Server,已經是SQL 2008R2 ,幾年下來慢慢也從SQL 2008移轉到2012/2014/2016,好在SQL資料庫的相容性,每次都很順利,不過這次是自己沒使用過的SQL 2005,試試SQL2005到SQL2017,一個12生肖的距離。

 

SQL 2017的相容性層級可以選擇140(2017), 130(2016), 120(2014), 110(2012), 100(2008),微軟官方Docs(下圖)也提到SQL Instance的升級可以是以下版本,但最早也只到SQL 2008,來試試SQL2005的資料庫檔案(user database)的移轉。

 

下載SQL 2005資料庫

從微軟資料庫的練習範本找SQL 2005的資料庫檔案,雖然2016 年 4 月 微軟終止了SQL2005的延伸支援,但幸好連結還在

1.下載好安裝檔案後,點擊安裝檔兩下

2.我同意,下一步

3.修改產生資料庫檔案的路徑,然後下一步

4.取得AdventureWorks 資料庫mdf、ldf檔案

5.下載好了SQL2005的資料庫檔案後,把他們複製到SQL2017的機器上。

 


SQL2017附加舊版資料庫檔案

1.先確認機器上的SQL Instance版本

SELECT @@VERSION

版本SQL 2017

2.附加(ATTACH)資料庫

USE [master]
GO
CREATE DATABASE AdventureWorks2005 ON 
( FILENAME = N'F:\Data\SQL2005\AdventureWorks_Data.mdf' ),
( FILENAME = N'F:\Data\SQL2005\AdventureWorks_Log.ldf' )
FOR ATTACH
GO

順利成功!

訊息中,可以發現SQL正在轉換內部版本從611到869

Converting database 'AdventureWorks2005' from version 611 to the current version 869.

附加資料庫成功!資料庫已經可以使用了。

 


SQL 資料庫內部版本(Internal version)

進一步觀察資料庫內的版本,查詢資料庫的內部版本號:

USE [AdventureWorks2005]
DBCC TRACEON (3604)
DBCC DBINFO
DBCC TRACEOFF (3604)
GO

可以從db info得知,2006年建立資料庫時,內部版本號還在611(當時在SQL2005);目前則Upgrade到869(SQL2017),相容性層級則在100(SQL 2008)。

不過內部版本一旦升級到SQL2017,就像變了心的女朋友,回不來了~

相較有彈性的是相容性層級:

查看資料庫屬性的頁面,在選項下有一個相容性層級: 

這次的移轉,我們的資料庫原本是2005(90)也升級到SQL 2017最低可支援的SQL Server 2008(100)了

SQL 2017還能向下相容其他4的版本

 


相容性層級(compatibility level)及內部版本(Internal version)

  • 相容性層級控制SQL語法元素的層級、函數版本、執行計畫的基數估計演算法及保留字等,如下表,每一個版本的SQL Instance都可以支援多種相同性層級的資料庫
  • 內部版本則是SQL資料庫的內部處理版本,舊資料庫升級到新的SQL Instance後會自動更新與Instance相符的內部版本,也沒辦法修改。最常碰到的問題是不能降級,舉例來說,用SQL2017開新資料庫,就不能把新資料庫檔案在SQL2017以前的版本做資料庫還原或資料庫附加。另一個問題就是太舊的也不能直接跨世代升級,像SQL 2000,一種威爾史密斯之我是傳奇,就沒辦法升到SQL2012以後的版本。

 

Marketing Name

Compatibility Level

Supported Compatibility Level

InternalVersion

UpgradeDb file

UpgradeDb file

SQL 2017

140

140, 130, 120, 110, 100

869

 

SQL 2016

130

130, 120, 110, 100

852

 

SQL 2014

120

120, 110, 100

782

 

SQL 2012

110

110, 100, 90

706

 

SQL 2008R2

100

100, 90, 80

661

SQL 2008

100

100, 90, 80

655

SQL 2005

90

90, 80

611

SQL 2000

80

80

539

 

 

如果單看相容層級及Instance升級說明,原以為SQL2017最早只能吃到SQL 2008的資料庫,實驗結果是可以吃到SQL 2005資料庫檔案。不過Docs也有說明可以升: 

Docs的說明: 

 


更早的版本(SQL 2000:539)

看來得先從SQL 2005或2008這兩個過渡版本洗一遍再升級,晚一點有空再繼續測。

 

 


小結:

  • 除了資料庫卸離附加可以升級,其實用備份還原也行,就看我們移動資料庫檔案 vs 備份檔移動 + 備份還原的時間比較,兩者升級都會有相同的upgrade step作業。
  • 記得連線字串、SQL login要照顧一下。
  • 微軟網站: 透過卸離與附加升級SQL 2005到SQL2014影片
  • 相容性層級晚一點再升到SQL2016/2017。
  • 注意各版本已被取代、已停用及重大變更。
  • 瀕臨絕種 vs 化石生物
  • 甲客戶今年安裝SQL 2016,延伸支援到2026年7月。9年
  • 乙客戶今年安裝SQL 2014,延伸支援到2024年7月。7年

 

SQL 2005

Microsoft SQL Server 2005 的支援已在2016年結束。在支援結束後,Microsoft 將不再提供安全性更新與 Hotfix

 


參考:

升級 SQL Server 2005 的十個重要理由

Northwind and pubs Sample Databases for SQL Server 2000下載

SQL Server 2005 SP2a 下載

northwind database backup-Sql Server 2005 and 2008下載

Microsoft SQL Server Community Projects & Sample

Attach SQL Server 2000 database file to SQL server 2014

ALTER DATABASE (Transact-SQL) Compatibility Level

Supported Version and Edition Upgrades for SQL Server 2017

升級SQL2014 Express技術指南

升級SQL2014技術指南

SQL Server 2017 中已取代的資料庫引擎功能

SQL Server 2016 中已停止的 Database Engine 功能