SQL Server 利用交易紀錄拯救資料

今天出了一個大包,大概是從寫程式以來最大的問題了。

查詢備份檔案

要利用交易紀錄拯救資料,首先必須要有一個完整的備份 bak ,再來需要有從完整備份開始的不中斷交易紀錄,這樣就可以利用來拯救交易紀錄期間所有資料庫異動。

SELECT 
a.[database_name] as '資料庫名稱',
f.physical_device_name as '備份位置',
CASE a.[type]
WHEN 'D' THEN N'資料庫'
WHEN 'I' THEN N'差異資料庫'
WHEN 'L' THEN N'紀錄'
WHEN 'F' THEN N'檔案或檔案群組'
WHEN 'G' THEN N'差異檔案'
WHEN 'P' THEN N'部分'
WHEN 'Q' THEN N'差異部分'
ELSE N'NULL'
END as '備份類型',
a.[name] as '備份組的名稱',
a.[first_lsn] as '備份組中第一個或最舊的記錄序號',
a.[last_lsn] as '備份組之後下一個記錄的記錄序號',
a.[database_backup_lsn] as '最近的完整資料庫備份之記錄序號',
a.[differential_base_lsn] as '差異備份的基底 LSN',
a.[backup_finish_date] as '備份作業完成的日期和時間',
a.[backup_size] as '備份組的大小 (以位元組為單位)'

FROM
msdb.dbo.backupmediafamily f
INNER JOIN msdb..backupset a ON f.media_set_id = f.media_set_id
INNER JOIN master..sysdatabases b ON
a.database_name COLLATE DATABASE_DEFAULT =
b.name COLLATE DATABASE_DEFAULT
where
a.database_name = 'DatabaseName'
ORDER BY
a.database_name, a.backup_finish_date

在結果之中會顯示每一次備份的紀錄,還有記錄到的紀錄序號,紀錄序號一定要連續不中斷的,一中斷只能說事情大條了。

還原資料庫

接下來我們利用 bak 以及  trn 檔案來還原資料庫,先還原 bak 的完整備份

image

image

先別急著按下確定,這時候我們點下指令碼,把介面替你做掉的 T-SQL 撈出來看看,應該會類似下面這段。

RESTORE DATABASE [Test20120401] 
FROM DISK = N'C:2012_20120309.bak' WITH FILE = 1,
MOVE N'test2012' TO N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATATest20120401.mdf',
MOVE N'test2012_log' TO N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATATest20120401_1.ldf',
NORECOVERY, NOUNLOAD, STATS = 10
GO

接下來還原 trn 的部分
image
選擇至某個時間點一樣點下指令碼,接下來讓兩段指令碼合併

RESTORE DATABASE [Test2012] 
FROM DISK = N'C:2012_20120309.bak' WITH FILE = 1,
MOVE N'test2012' TO N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATATest20120401.mdf',
MOVE N'test2012_log' TO N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATATest20120401_1.ldf',
NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [Test2012]
FROM DISK = N'C:2012_20120309120405_190000_2012_TRN.trn' WITH FILE = 1,
NORECOVERY, NOUNLOAD, STATS = 10,
STOPAT = N'2012-04-03T22:40:00'
GO

藉由這種方式,我們可以更改變數還有還原時間點還原出不同時間點的資料庫

image

比較資料庫差異性

再來就是利用工具比較還原的資料庫,我們就可以找到每一段區間中間的變動,新增或是刪除的資料列。在這邊我用的工具是 Visual Studio 的 Data Compare

Data > Data Compare > New Data Comparison

image

在這邊的可以選擇來源資料庫跟比較資料庫,Only in Source 或是 Only in Target 也可以單純過濾說被刪除/被新增的資料,選擇完比較的兩邊之後就可以直接開始比較的動作。

image

可以看到我這邊指定了 Only in Source ,各有兩個 Table 少了 20 跟 42 筆資料,點到那一個 Table 的時候下方就會列出差異性的資料,或是選擇上方 Export To Editor 可以匯出將資料塞回 Table 的 T-SQL

image

總結

當然系統安安穩穩的不需要做還原最好,但是誰也不知道哪一天會出大災難,這次的事件真的是學了一次教訓。

EntityFramework SQL 2005 not support datatype ‘datetime2’

因為在測試環境都是用 SQL 2008 的版本在做測試開發,到了正式環境要發佈的時候卻發生 not support datatype ‘datetime2’ 的錯誤。

Server Error in '/' Application.
--------------------------------------------------------------------------------

The version of SQL Server in use does not support datatype 'datetime2'.
Description: An unhandled exception occurred during the execution of the current web request.
Please review the stack trace for more information about
the error and where it originated in the code.

Exception Details: System.ArgumentException:
The version of SQL Server in use does not support datatype 'datetime2'.

  • 開發測試環境是 SQL 2008
  • 正式環境 SQL 2005
  • EntityFramework 4.3
  • Database First

問題發生原因

這邊有 datetime 跟 datetime2 的比較
在 SQL 2008 之後版本,微軟將資料庫的 datetime 欄位改為 datetime2,而當使用 EntityFramework Database First 的時候,就會根據資料庫的版本去產生 edmx。再利用 edmx 對應去產生物件做操作。所以如果使用的是 SQL 2008 產生出來的物件,在 Datetime 要存進資料庫的時候就會幫你轉成 SQL Datetime2 格式。當然這樣在 SQL 2008 下是沒有問題的,可是如果要拿同一份程式到 SQL 2005 執行就會造成這個錯誤。

解決方法

其實解決方法很簡單,只要將 edmx 用 XML Editor 開啟之後。找到資料庫對應版本的設定。

<!-- SSDL content -->
<edmx:StorageModels>
<Schema Namespace="Test.Store"
Alias="Self"
Provider="System.Data.SqlClient"
ProviderManifestToken="2008"
xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator"
xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
<EntityContainer Name="LiteonAward2012ModelStoreContainer">

將上面的第 6 行 2008 改為 2005 之後將檔案存檔,並且重新產生 POCO 或是 DBContext 物件編譯過後就可以了。


Using SQL Server 2008 and SQL Server 2005 and date time

SQL Server 利用產生指令碼功能複製資料庫

以往要複製還原都是用 SQL 資料庫內建的備份還原功能,將檔案備份到 .bak 檔案之中,還原的時候也可以選擇日期版本,可以說是相當的便利。

但是在某些狀況下並沒有辦法使用內建的備份還原功能

  1. 內建的備份還原必須是針對本機的檔案,如果要針對遠端的資料庫就必須先把備份檔案複製到資料庫的機器上面。
  2. 必須具備資料庫備份還原的權限

常常在不同環境下並沒有那麼完整的權限,但是又必須複製資料庫的時候就可以用到。

資料庫>右鍵>產生指令碼

image

image

再轉出的時候有一個進階選項可以進入做更完整的設定

image

如果需要複製資料庫資料,就需要在這邊做設定的動作。我一開始雖然知道這個功能,可是不知道的是還有複製資料的部分,都是先產生 Table Schema 再慢慢轉入資料,現在想想真的太傻了。

image

最後只要把產出的 script 拿到新資料庫上面執行就好了,不過速度上會慢非常多喔,跟使用 bak 備份還原比較起來。

SQL Server 2008 R2 效能調教課程筆記 #3 識別效能瓶頸 – CPU

在效能調教的過程中,利用工具找出最影響效能的癥結點,進而去改善效能問題,效能調教是沒有辦法說已經調教到最好,只能說在這個狀況下是不是能夠比之前發揮出更多硬體的效能,而當調教花費的成本比改善硬體更高的時候,就會選擇改善硬體規格。

怎麼辨識CPU效能瓶頸

  • Processor:% Processor Time 每一個CPU持續高達 75~80% 以上
    • 並非當 Processor 使用率很高的狀況下就是 CPU 瓶頸,使用率很高其實也是發揮出硬體原有的效能,只是平均高達 80% 而且長時間持續突破的情況下可以考慮由這方向查證。
  • Process: % Processor Time for sqlservr Process 數值高(除以CPU個數)
    • 當 CPU 使用率很高,而且大部分的 CPU 效能都是被 SQL SERVER 使用,如果 CPU 使用很高,但是 SQL SERVER 使用的很少,或許要檢查 Server 上面是不是有掛載其他程式使用掉了,固定的執行排程,又抑或是防毒軟體。
  • System: Processor queue length > 2
    • 執行的等待時間持續大於 2 ,幾乎可以斷定是 CPU 瓶頸,每個執行都必須等待兩個以上。
  • Wait Type 中的 CXPACKET 數值非常高
  • DMV 語法中的 runnable_tasks_count 數值持續不為0

造成CPU使用率高的原因

  • 大量並同時的批次作業造成系統無法負荷
    • 在半夜執行的批次作業可以錯開時間

image

  • 查詢的執行計畫(Execution plan)沒有效率
    • 可能是因為索引的關係原本少數資料的 Index Seek 必須要由 Index Scan 來執行
  • 過度的編譯(Compilations)及重新編譯(Recompilations)發生
    • 高比率的(SQL Recompilations/sec)/(Batch Requests/sec)指出重新編譯是導致CPU使用率高的原因,利用參數式查詢,避免每次動態語法重新編譯。
  • 不正確使用平行處理計畫(Intra-query parallelism)
    • 在多 CPU 的 Server 上,SQL Server 在預設行為下遇到需要大量 CPU 的語法執行時候,會自動執行平行計算使用所有的 CPU ,因此可能讓某一項需要長時間運算影響到整體 Server 。OPTION (MAXDOP  1) 可以設定使用單一 CPU 處理單一語法,避免因為一個怪獸級的動作消耗掉全部的效能。
  • 不正確使用伺服器端游標(Server side cursors),影響SQL Server效能

SQL Server 2008 (R2) 效能調教及工具應用專班!!

SQL Server 2008 R2 效能調教課程筆記 #2 基本工具 SQL Profiler

相對於 Performance Monitor 來說,SQL Profiler 是我比較熟悉的工具,如果有使用 ORM 工具也會用這種方式來看真正執行的 T-SQL。

image

指在資料庫階層工具>SQL Server Profiler,這邊需要有資料庫權限才有辦法開啟。

選擇相關的追蹤資料,上課的講師有建議
• Stored Procedures
• RPC:Completed(已經完成遠端程序呼叫)
• SP:stmtCompleted(已完成預存程序內的 T-SQL 陳述式)
• TSQL
• SQL:BatchCompleted(已完成 Transact-SQL 批次)
• SQL:StmtCompleted(已完成 Transact-SQL 陳述式)
• Errors and Warnings
• Audit login / logou

image

如果有選擇儲存至檔案,紀錄完畢會得到一個 trc 檔案,重新開啟 trc 檔案可以看到匯入效能資料。

image

利用這種方式可以將 SQL Provider 資料與 Performance Monitor 資料合併顯示。

image

選取某一段 CPU 很高的時間點也會顯示同時間執行的 SQL 語法,要匯入的資料必須是時間點上有重疊的部分,不然會沒有辦法匯入,如果相對應時間點沒有另一個紀錄的資料也會無法顯示。


SQL Server 2008 (R2) 效能調教及工具應用專班!!

SQL Server 2008 R2 效能調教課程筆記 #1 基本工具 Performance Monitor

上個禮拜有幸去參加了微軟講師 Ray 的課程,真的是有醍醐灌頂的感覺。平常上網爬文跟現場的教學真的還是有差距,不過代價也是差了很多就是,如果是個人自費的話應該也花不下手吧。趕快趁著上完課記憶還在的時候能記多少就記多少,不然平常程式開發也比較少上線後效能調教的經驗。

要調教效能的情況下通常是因為客戶覺得反應速度太慢,反應速度慢的情況又有很多種,有可能是因為使用人數或資料隨著成長造成的,也有可能是因為程式邏輯太過複雜,或是硬體真的發生了問題,太多太多種可能的狀況,所以還是需要一些工具來輔助。

Performance Monitor

這是 windows 內建的功能,直接開始>執行>perfmon 就可以開啟 Performance Monitor

image

也可以利用新增功能增加要監視的系統數值

image

除了即時監控的功能,也可以設定將記錄的資料儲存下來,再利用分析工具來分析效能不佳的原因

image

設定需要紀錄的項目,有些項目是比較不需要的,全部紀錄的話資料量太多也會影響分析的速度,在使用 Performance Monitor 的時候也會影響一些系統效能。但是如果要調教的話還是需要這些資料來做分析的動作。

image

影響效能這邊在紀錄的間隔時間可以來做一個設定

  • 總紀錄時間兩小時-每四秒一次
  • 總紀錄時間一天-每30秒一次
  • 總紀錄時間五天-每180秒一次

當然間隔時間愈長,為了紀錄所影響的效能愈少,但是為了取得需要的資料量,就需要比較長的紀錄時間。不過最重要的是在記錄時間內確實有發生效能問題,不然拿系統運作良好的紀錄要分析出效能低落的原因就比較困難了。

image

設定完了之後就會出現自定義的項目,在這邊的 右鍵>內容也可以進一步設定排程開始結束相關設定,如果問題發生是固定半夜兩點就可以設定早十分鐘先啟動紀錄,記錄問題發生的時候系統的狀況。

另外如果有大量的 server 需要紀錄的話,不太可能一台一台電腦設定,Performance Monitor 也有支援 command 語法設定紀錄或排程。


SQL Server 2008 (R2) 效能調教及工具應用專班!!