[SQL SERVER][Denali]SQL Server 2012 – Upgrading to SQL Server 2012
SQL Server 2012 好文比賽得獎公布
文章參考來源:SQL SERVER 好文比賽
主題:SQL Server 2012 – Upgrading to SQL Server 2012 (SQL 151)
升級前準備
升級前應該要有一個完整計劃，以減少升級風險和變數，這樣才可以讓升級過程順暢點，
升級成功後也一定要經過應用程式和相容性測試作業..等，
同時也需擬定一個失敗應急計畫，如果不幸直接升級失敗也可以馬上恢復資料庫上線。
準備升級前應該先收集可能影響升級的因素和風險資訊，
如果忽略這一個步驟，那麼升級過程中可能會增加失敗風險程度。
不管升級環境簡單或複雜，分析升級需求是相當重要的步驟，
而這通常包含SQL Server軟硬體需求，版本、相容性、封鎖...等相關問題。
升級方式
1.直接升級(In-Place Upgrade)
直接透過安裝程式作業升級，在相同平台上升級舊有SQL2005、SQL2008執行個體為SQL2012，
過程中不需要手動複製資料和相關物件(缺少資料庫控制權)，
升級處理快速簡單也不需要預先準備另一套硬體和作業系統，
但升級成功後，舊有SQL2005、SQL2008執行個體將被移除。
note:如果你只想升級某一資料庫，那應該使用平行移轉的方法。
2.平行移轉(Side by Side Upgrade)
需要事先安裝一個新的SQL2012執行個體，
然後手動複製SQL2005、SQL2008相關物件和資料平行移轉到新的SQL2012上，
這方法可讓新舊執行個體同時存在，
不過需要有較多的手工作業，需要較有經驗DBA來執行，
但可以更有效掌握升級過程主動權並減少停機時間，
因為舊有執行個體未被移除，當升級成功後進行應用程式測試作業時，
更可以大大提高正確性和有效性。
Exercise 1: Detecting SQL Server Upgrade issues
這次情境中，我們已經有一個新的SQL2012執行個體，
我們目標是要將SQL2005平行移轉到SQL2012，
並享受SQL2012所帶來的新特性和效能，
過程中你將會使用Upgrade Advisor工具來規劃升級過程，
Upgrade Advisor包含分析精靈和分析結果報表，
透過分析精靈幫我們發現SQL2005、2008、2008R2升級有那些問題影響，
然後透過分析報表所提供資訊來解決相關問題，
下面我們就來實際操作練習吧。
Task 1: Identifying upgrade issues, prior to upgrading
1.所有程式中點選SQL Server 2012 Upgrade Advisor 並執行
2.Server Name輸入 myupgrade，這裡只關注SQL Server(資料庫)升級。
勾選 SQL Server元件。
3.確認連線屬性
這裡請選擇SQL2005。
4.選擇資料庫分析(預設會選擇SQL2005相容層級資料庫)
取消 ReportServer$2005 and ReportServer$2005TempDB 資料庫。
如果你嘗試勾選Northwind70將會出現以下錯誤訊息
因為該資料庫相容層級低於90，Upgrade Advisor無法支援分析，所以請勿勾選Northwind70資料庫。
5.準備執行資料庫分析
這裡可以再次確認所選擇的資料庫清單。
6.執行分析成功後開啟分析報表
7.確認相關 issue 何時修正、如何修正以及受影響的物件
點選 Show affected objects 查看物件
這裡我們就知道資料庫LABDB2005的HierarchyID需要刪除。
如果你不知道該如何修正的話，
可以點選on Tell me more about this issue and how to resolve it，
你將得到更多訊息並瞭解如何解決問題，
接下來我們緊接者第2任務，修正升級前相關問題。
Task 2: Fixing upgrade issues, prior to upgrading.
1.移除使用者定義類型 ORDPATH
開啟SSMS2012連接SQL2005執行個體
展開LABDB2005資料庫的User-Defined Data Types
刪除dbo.HierarchyID。
因為有其他物件參考使用，所以無法正常刪除。
點擊Show Dependencies 查看相依物件
可以看到資料表 tbl_person 參考使用中
為了要順利刪除dbo.HierarchyID，
我們先將建立資料表Script到新查詢視窗
建立該資料表指令碼(Script)。
修改資料表名稱(tbl_person_new)和parentid資料型別為 int。
執行上面Script建立資料表後，在執行下面語法複製資料。
SET IDENTITY_INSERT tbl_person_new ON
INSERT INTO tbl_person_NEW (personID, Personname, ParentID)
SELECT personID, Personname, ParentID from tbl_person
資料複製成功後，刪除原有資料表(tbl_person)，並把資料表(tbl_person_new)重新命名為tbl_person
DROP TABLE tbl_person
EXEC sp_rename ‘dbo.tbl_person_NEW’,'dbo.tbl_person'
到這裡你已經可以順利刪除dbo.HierarchyID，並且也修正了升級前的問題，
其餘的問題你可以利用相同的方式來處理，直到所有升級前問題都修正完畢。
Exercise 2: Performing Database Upgrade
當你使用平行移轉方法來升級資料庫時，
你有幾個方法移轉舊執行個體資料庫到新的執行個體上
-
Backup / Restore
-
Data Tier Applications
-
Copy Database Wizard
-
Detach/Attach
本次練習中你將使用 Backup/ Restore 方法，以及資料庫複製精靈將舊資料庫移轉到新的執行個體上。
Task 1: Transferring a Database using CDW (Copy Database Wizard)
連線到SQLONE執行個體，展開管理資料夾並選擇複製資料庫
來源伺服器輸入 myupgrade\sql2005
目的伺服器輸入 SQLONE
選擇使用附加和卸載方法
選擇LABDB2005資料庫進行複製
這裡我修改目地資料庫名稱為LABDB2012
我們準備將LADB2005資料庫複製到SQLUPGRADE主機上，
所以我們要先設定分享SQLUPGRADE主機上的SQL Server資料檔案目錄存取權限。
登入SQLUPGRADE主機設定共享資料夾權限(密碼 pass@word1)
點選進階設定。
新增LABUSER
給予完全控制權限
輸入檔案分享路徑
勾選 save transfer logs，選擇windows event log
如果移轉有相關錯誤，我們可以查看windows event log。
CDW方法移轉成功
連線到SQLONE執行個體並展開資料庫資料夾
可以看到LABDB2012資料庫移轉成功。
Task 2: Upgrading Databases using BACKUP/RESTORE
針對AdventureWorks 資料庫執行備份
路徑選擇之前所設定的資料分享路徑
備份成功
連線SQLONE執行個體並執行還原資料庫
選擇備份裝置時，請輸入之前所備份的路徑和檔名
勾選Relocate all files to folder
還原成功
Task 3: Post Upgrade Operations
最後一個步驟即是要把兩個移轉成功的資料庫相容性層級改為SQL2012
設定LADB2012資料庫屬性
相容性層級:SQL Server 2012(110)
自動更新統計資料:True
自動非同步更新統計資料:True
設定AdventureWorks資料庫屬性
復原模式:Full
相容性層級:SQL Server 2012(110)
自動更新統計資料:True
自動非同步更新統計資料:True
確認兩個資料庫層級
升級過後大致上有幾個重要步驟要執行，這裡補充一下
1.執行 DBCC UPDATEUSAGE
更正任何無效的資料列或頁面計數(DBCC UPDATEUSAGE('database_name') WITH COUNT_ROWS)
2.sp_updatestats
更新統計值以便取得最佳執行計畫(Update Statistics your table with FULLSCAN)
3.如果有使用全文檢索請記得重建
4.recompile all sp(sp_recompile 'procedureName')
我個人習慣用匯出指令碼先刪除後建立方式
5.Refresh your views SP_REFRESHVIEW view_name
如果有使用indexed view
6.如果環境複雜，有DB Mirror、Log Shipping..等高可用性功能，也都必須重新設定測試。
真實世界中如果你的資料庫相當龐大(資料和物件繁多)，你可能得好好評估那個方法耗費時間少，
而且升級後的測試相當重要，你得確保各種服務都得正常執行(不然老闆可能會請你走路了...XD)，
個人實務上會採取保守的方法來處理資料庫升級作業，
我會預先測試整個升級過程可能遭遇的情況(採平行移轉)，並且評估測試大概花了多少時間，
同時盡量維持測試環境和正式環境相同(版本、語系、物件..等)，以減少可能影響升級的風險和未知問題，
至於升級後要不要更改資料庫相容性層級，
則是需要取決使用者需求和相容性結果來決定。