Access 適合小型應用,但隨著資料量增長 (超過 100-200 MB)、併發用戶增加 (50+)、安全性需求提升,**遷移至 SQL Server 成為必然**。本文提供從 Access 2003 升級格式、使用升遷精靈、生成腳本建立架構、資料轉移服務 (DTS) 匯入資料的完整步驟。
前置準備
- 安裝 **Microsoft Office Access 2003** 與 **SQL Server 2000/2005/2008+**。
- **完整備份** 原 .mdb 檔案。
步驟 1:轉換為 Access 2002-2003 格式
- 用 Access 2003 開啟 .mdb (忽略安全性警告)。
- 工具 → 資料庫實用程式 → 轉換資料庫 → **轉換為 2002-2003 格式**,指定新檔名 (如
MyApp_2003.mdb)。
步驟 2:升遷精靈建立 SQL Server 資料庫架構
- 用 Access 2003 開啟轉換後的 .mdb。
- 工具 → 資料庫實用程式 → **升遷精靈** → 下一步。
- 選擇 **「新建資料庫」** → 輸入 SQL Server 登入資訊 (伺服器、驗證模式、SA/帳號、密碼) → 指定新資料庫名稱 (如
MyApp_SQL)。
- 下一步 → 全選物件 (資料表、查詢、表單、報表、巨集、模組) → 下一步。
- 選擇 **「不對應用程式作任何改動」** (僅遷移資料結構與資料) → 完成。
- 精靈會在 SQL Server 建立資料庫、資料表、索引、關聯、預設值等架構。
步驟 3:生成 SQL 腳本並修正資料類型相容性
- 企業管理員 / SSMS → 右鍵資料庫
MyApp_SQL → 所有工作 → **產生 SQL 腳本**。
- 「一般」頁籤 → 全部顯示 → 「格式」頁籤 → 勾選 **「產生所有物件腳本」** → 確定,儲存為
MyApp_Schema.sql。
- 用記事本/編輯器開啟腳本檔,**全域取代** 解決 Access→SQL Server 型別不相容:
datetime → smalldatetime (Access Date/Time 精度較低,smalldatetime 足夠且省 4 bytes)
nvarchar → varchar (若無 Unicode 需求,節省一半空間)
text/ntext → varchar(max)/nvarchar(max) (SQL 2005+)
memo → nvarchar(max)
yes/no → bit
- 儲存修改後的腳本。
步驟 4:執行腳本建立乾淨架構
- SSMS → 新增查詢 → 開啟
MyApp_Schema.sql → 執行 (F5)。
- 確認無錯誤,資料庫架構已就緒 (無資料)。
步驟 5:DTS/SSIS 匯入資料
- 企業管理員 / SSMS → 右鍵資料庫
MyApp_SQL → 所有工作 → **匯入資料** (啟動 DTS/SSIS 精靈)。
- 資料來源:Microsoft Access → 選取原始 .mdb 檔。
- 目的地:Microsoft OLE DB Provider for SQL Server → 目標伺服器/資料庫。
- 選擇 **「從來源資料表/檢視表複製」** → 下一步。
- 全選資料表 → 可針對個別表對應欄位、轉換 → 下一步。
- **立即執行** → 完成。
步驟 6:遷移後驗證清單
| 檢查項目 | 驗證方式 |
| 資料列數一致 | SELECT COUNT(*) FROM AccessTable vs SELECT COUNT(*) FROM SqlTable |
| 主鍵/身分識別種子 | DBCC CHECKIDENT ('Table', NORESEED) 確認種子值正確 |
| 外鍵關聯啟用 | SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS |
| 索引/統計資料 | EXEC sp_updatestats 重建統計 |
| 應用程式連線字串 | 修改為 SQL Server (SQLOLEDB/SQLNCLI),測試所有功能 |
| 權限模型 | 建立資料庫角色、授予最小權限、移除 SA 直連 |
常見遷移坑點與對策
- AutoNumber → Identity:Access AutoNumber 遷移後為 Identity,但種子值可能不連續,插入新列時可能衝突 → 遷移後執行
DBCC CHECKIDENT ('Table', RESEED, max_id)。
- 日期時間精度:Access Date/Time 精度至秒,SQL
datetime 至 3.33ms → 使用 smalldatetime (精度至分鐘) 或 datetime2(0) 避免比較問題。
- 查詢/檢視表語法差異:Access SQL (Jet/ACE) 與 T-SQL 差異大 (如
IIF vs CASE, NZ vs ISNULL/COALESCE, TOP n PERCENT 語法不同) → **必須人工重寫** 所有查詢/檢視表/預存程序。
- 表單/報表/模組:升遷精靈**不遷移** UI 與 VBA 程式碼,需重寫前端 (建議改 .NET/Web) 或保留 Access 前端連結 SQL 後端 (分離架構)。
現代化替代方案 (SQL Server 2008+ / Azure SQL)
- SQL Server Migration Assistant (SSMA) for Access:微軟官方免費工具,自動評估、轉換結構/資料/查詢、產生報告,**強烈推薦取代手動升遷精靈**。
- Azure SQL Database:SSMA 直接遷移至雲端,享受 PaaS 優勢。
遷移非一日之功,建議採用 **雙寫/同步驗證** 策略:新舊系統並行運行一段時間,比對資料一致性再全面切換。