這次不用升級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也有說明可以升:
更早的版本(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年
Microsoft SQL Server 2005 的支援已在2016年結束。在支援結束後,Microsoft 將不再提供安全性更新與 Hotfix
參考:
Northwind and pubs Sample Databases for SQL Server 2000下載
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
SQL Server 2016 中已停止的 Database Engine 功能