SQL Server 記憶體使用量說明

         最近常常被問到，假設主機上記憶體總共有16G，並限制SQL Server最大可使用的記憶體只有8G，但是為何從 [工作管理員] 看到 SQL Server的服務 [sqlservr.exe] 總是會使用超過8G的限制，是否設定的限制無效，或是該如何設定與確認，所以我希望透過此篇來告訴大家如何進行確認。

相關文章：
SQL Server - x86與x64的架構下 - 最佳記憶體設定
http://caryhsu.blogspot.com/2011/11/sql-server-x86x64.html

可能的情況會有兩種情況，以下我再逐一說明：
1、第一種是您沒有正確的設定 [鎖定記憶體分頁] 權限，所以就算您有設定 SQL Server的記憶體限制，但此設定還是不會啟用，確認方式，您可以從 SQL Server Error Logs看到下列的訊息時，代表您沒有設定正確，詳細的設定方式，再請參考我另一篇文章 [SQL Server - x86與x64的架構下 - 最佳記憶體設定] 進行設定。

錯誤訊息： 
Cannot use Large Page Extensions:  lock memory privilege was not granted.

2、另外一個最重要的，由於 [max server memory] 主要是管理 SQL Server 中 Buffer Pool的區塊，當您的系統中可能有使用到如 Extended Stored Procedures、COM objects, non-shared DLLs, EXEs, 和MAPI等元件時，就有可能造成超過限制的情況發生，如要真正的確認目前SQL Server中 Buffer Pool的使用情況，您可以透過下列的方式進行確認。

開始 -> 系統管理工具 -> 效能監視器 -> 效能 -> 監視工具 -> 效能監視器 -> 點選 [ + ] 的按鈕。

選擇 [SQLServer：Memory Manger] -> [Target Server Memory (KB)] 與 [SQLServer：Memory Manger] -> [Total Server Memory (KB)]加入後點選 [確定]。

[SQLServer：Memory Manger] -> [Target Server Memory (KB)]：伺服器可用的動態記憶體總數。
 [SQLServer：Memory Manger] -> [Total Server Memory (KB)]：來自緩衝集區的認可記憶體 (以 KB 計)。

加入後，你就可以看到目前的使用量為多少。

官方說明：

You can establish upper and lower limits to the amount of memory (buffer pool) used by the SQL Server database engine with the min server memory and max server memory configuration options. Before you set the min server memory and max server memory options, review the references in the section titled "Memory" in the following Microsoft Knowledge Base article:

Note that the max server memory option only limits the size of the SQL Server buffer pool. The max server memory option does not limit a remaining unreserved memory area that SQL Server leaves for allocations of other components such as extended stored procedures, COM objects, non-shared DLLs, EXEs, and MAPI components. Because of the preceding allocations, it is normal for the SQL Server private bytes to exceed the max server memory configuration. For additional information about allocations from this unreserved memory area, click the article number below to view the article in the Microsoft Knowledge Base:

參考網址：
How to adjust memory usage by using configuration options in SQL Server
http://support.microsoft.com/kb/321363/en-us
Inside SQL Server 2000's Memory Management Facilities
http://msdn.microsoft.com/en-us/library/aa175282(v=sql.80).aspx
SQL Server 的 Memory Manager 物件
http://technet.microsoft.com/zh-tw/library/ms190924.aspx

關鍵字：SQL Server、Memory Manager、max server memory、buffer pool、Task Manager

SQL Server - 搬移資料庫時，如何自動比對帳號SID

        當您要進行資料庫的搬移時，方法有很多種，最簡單的方法，如果您的資料庫版本完全相同時，您可以將相關的資料庫，如 MSDB、Master等資料庫，透過 detach database之後，再將database attach 到新的系統上即可，但是如果是進行升級或是資料庫的版本不相同時，就需要手動方式進行轉移， 搬移的工作大致上，都可以透過手動產生 Script 之後，再貼到新的主機上執行即可，但是搬移後，會有SID不相同的問題需要手動進行修正，在SQL Server 2000的版本上，有 sp_SidMap 可以使用，但很可惜的，在 SQL Server 2005以後就沒有了，所以我手動的寫了下列的語法進行 Database User與 Login User的比對，希望對大家在移轉資料庫上有所幫助。

列出所有Database User：
SELECT name, sid from sysusers
列出所有Login User：
SELECT sid, name FROM sys.syslogins
下列的語法會比對 Database User 與 Login User，當 Login User不存在時，會直接新增，如果存在，則會透過 sys.sp_change_users_login update_one 的語法直接進行 SID 的對應。
use test_db;
declare @sysusers_name varchar(200);
declare @sysusers_sid varbinary(85);
declare @ret_val int;
DECLARE check_user_list CURSOR
FOR
SELECT name, sid from sysusers
WHERE issqluser = 1
    AND   issqlrole = 0
    AND   isapprole = 0
    AND   uid > 4
ORDER BY name
OPEN check_user_list
FETCH NEXT FROM check_user_list INTO
@sysusers_name, @sysusers_sid
WHILE(@@FETCH_STATUS = 0)
 BEGIN
   IF NOT EXISTS (SELECT name FROM sys.syslogins WHERE name = @sysusers_name)
   BEGIN
  EXEC @ret_val = sp_addlogin @loginame = @sysusers_name, @sid = @sysusers_sid
  IF @ret_val <> 0
  BEGIN
    RAISERROR(15497,16,1,@sysusers_name)
    PRINT'MSG ****: Please add the Login : ' + @sysusers_name + ' using sp_addlogin.'
    CONTINUE
  END
   END
   EXEC sys.sp_change_users_login update_one, @sysusers_name, @sysusers_name
   FETCH NEXT FROM check_user_list INTO
   @sysusers_name, @sysusers_sid
 END
--關閉cursor
CLOSE check_user_list
DEALLOCATE check_user_list


 參考連結：

1. How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server
   http://support.microsoft.com/kb/224071/en-us   
2. 如何在 SQL Server 的執行個體之間傳送登入和密碼
   http://support.microsoft.com/kb/246133
3. How to resolve permission issues when you move a database between servers that are running SQL Server
   http://support.microsoft.com/kb/240872/en-us

關鍵字：SQL Server、sp_SidMap、Database User、 Login User、sys.sp_change_users_login update_one、sys.syslogins

Reporting Service - 如何建立資料驅動訂閱

        報表訂閱的部份，相信大家都已經相當的清楚了，就是將報表透過E-Mail或共享檔案的方式進行，主動的將報表傳送給使用者，但是如果每一個使用者的條件不同時，如A使用者想要直接收到 PDF 的格式，B使用者想要收到 Excel 的格式時，以往只能透過不同的訂閱群組進行，但是在管理上卻又非常的麻煩。

        當然這個問題有許多的方法可以解決，如自行透過程式設計的等方式進行，但是這些方法都需要額外的花費時間進行設計才可以完成，所以微軟提出 [資料驅動訂閱] 可以很簡單的解決這個問題，可以透過資料表的對應，來完成每一位使用者不同的需求，所以本篇就來介紹如何進行 Reporting Serv資料驅動訂閱。

        在此篇的範例上，我透過 Northwind (北風資料庫) 建立了一個查詢，然後將此查詢透過報表精靈設計成一張報表後，再將報表上傳到 Reporting Service 之中，最後再透過資料驅動訂閱的方式進行。

PS：資料驅動訂閱只有 Evaluation、Developer 和 Enterprise 三個版本有支援，其他的版本皆無法使用。

設定步驟：
1、啟動SQL Server Agent，由於報表的訂閱都是透過 SQL Server Agent來進行的，所以請記得先啟動此服務。

2、設計報表，並將報表上傳到 Reporting Service 之中。

報表查詢語法：這表報表在預覽時會讓前端的使用者選擇員工的名稱，所以在查詢語法中，透過 @emp_id 進行動態的設定。

SELECT Orders.OrderID, Orders.OrderDate, Employees.EmployeeName,
       Customers.CompanyName, Products.ProductName,
       OrderDetails.UnitPrice, OrderDetails.Quantity,
       OrderDetails.Discount
FROM Orders INNER JOIN
     Employees ON Orders.EmployeeID = Employees.EmployeeID
INNER JOIN
     Customers ON Orders.CustomerID = Customers.CustomerID
INNER JOIN
     OrderDetails ON Orders.OrderID = OrderDetails.OrderID
INNER JOIN
     Products ON OrderDetails.ProductID = Products.ProductID
WHERE Employees.EmployeeID = @emp_id
ORDER BY Orders.OrderDate


報表預覽

3、確認在 Reporting Services Configuration Manager 之中已有設定 E-Mail Settings，如果你此時沒有SMTP主機可以進行測試時，你可以參考我的另一篇文篇 [如何設定Reporting Service與本地SMTP服務的應用] 進行設定。

4、登入Reporting Service 的網頁管理介面，點選報表切換到管理 -> 訂閱 -> 新增資料導向訂閱 ，如果此時出現下列的錯誤時，主要是由於報表進行訂閱時，都需要自訂資料來源，無法透過共用資料來源來進行，所以請接續下一個動作。

5、選擇要訂閱的報表切換到管理 -> 資料來源 -> 點選自訂資料來源

5-1 在連接字串的部份，輸入下列的文字。
data source=server address; initial catalog=Northwind
5-2 在 [使用下列方式連接] 上，選擇 [安全地儲存在報表伺服器中的認證]，此時請輸入一組可以連接到資料庫的帳號與密碼，並且勾選 [連接到資料來源時作為Windows 認證]。
5-3 點選 [測試連接]，確認連線的帳號與密碼是正確的。
5-4 最後點選 [套用] 進行儲存。

6、選擇要訂閱的報表切換到管理 -> 訂閱 -> 新增資料導向訂閱，在 [描述] 的地方請輸入此訂閱的名稱，然後在 [指定通知收件者的方式] 選擇 [電子郵件]，在此選單中，如果沒有 [電子郵件] 可以選擇，代表你沒有設定第三個步驟，選完後，再點選 [下一步]。

7、此處需再次輸入訂閱的資訊，請比照第五步驟的資訊再次設定即可。

8、訂閱上由於我要顯示針對每一個員工有不同的寄件方式，所以我在 [Employees] 的資料表上增加了兩個欄位，一個是 [Email]，另一個是 [ExportFormat] 的欄位，分別記錄員工的E-Mail與匯出的格式，另外希望匯出的同時，也同時副件寄給主管。

查詢語法：
SELECT Employees.EmployeeID, Employees.ExportFormat,
       Employees.Email, Employees_1.Email AS mag_email
FROM Employees LEFT OUTER JOIN
     Employees AS Employees_1 ON
     Employees.ManagerID = Employees_1.EmployeeID


9、將上述的語法貼到訂閱的步驟3的地方，然後點選驗證進行確認。

10、步驟4主要就是最關鍵的部份，由於資料驅動訂閱主要透過這個步驟與你的查詢語法產生連結，根據我的需求，有幾個部份需要進行設定：

10-1 收件者 -> 請選擇 [從資料庫取值]，然後選擇 [Email] 欄位。
10-2 副本 -> 請選擇 [從資料庫取值]，然後選擇 [mag_email] 欄位。

10-3 轉譯格式 -> 請選擇 [從資料庫取值]，然後選擇 [ExportFormat] 欄位。
10-4 註解 -> 請選擇 [從資料庫取值]，然後選擇 [EmployeeID] 欄位。
10-5 點選下一步

11、在這個步驟主要針對你的報表中的參數要與查詢語法的那一個欄位進行結合，在這裡請選擇 [EmployeeID] 的欄位。

12、選擇訂閱的處理方式，在這裡由於我們需要在每天的固定時間傳送報表，所以請選擇 [在為此訂閱建立的排程上]，然後選擇 [下一步] 。

13、排程的時間可以自由設定，在這我是希望每天 01:00 進行訂閱的排程，設定如下。

14、完成設定後，在報表的訂閱就可以看到如下的訂閱項目。

15、當收到E-Mail時，你就可以看到如下的格式，紅色圈起來的部份代表著 10-4 選擇的註解，另外如果你希望將報表的內容呈現在 E-Mail之中時，在輸出格式要選擇 [MHTML] ，也就是在 10-3 步驟中的欄位。

16、另一個範例，你可以看到副本也會自動轉寄給主管，然後透過附件的方式進行輸出，但是在 E-Mail的內文中，你就不會看到詳細的內容，但是可以透過連結的方式直接連到 Reporting Service 的主機進行觀看。

以上是Reporting Service資料驅動訂閱的詳細建立步驟，程序上有點多，但是透過圖文並茂的方式，相信對大家在理解會有一定的幫助，也請希望大家多多利用這個功能。

Reporting Service 匯出格式參考(詳細說明請參考連結)：

1. XML
2. CSV
3. ATOM
4. PDF
5. RGDI
6. HTML4.0
7. MHTML
8. EXCEL
9. RPL
10. IMAGE
11. WORD

參考連結：
資料驅動訂閱
http://msdn.microsoft.com/zh-tw/library/ms159150(v=SQL.105).aspx
教學課程：建立資料驅動訂閱
http://msdn.microsoft.com/zh-tw/library/ms169673(v=SQL.105).aspx
Exporting Reports
http://msdn.microsoft.com/en-us/library/dd239307.aspx

關鍵字：Reporting Service、Data-Driver Subscription、資料導向訂閱

如何設定Reporting Service與本地SMTP服務的應用

        許多人在自已的環境中，由於可能對Mail Server不熟悉或是沒有測試的環境，所以在報表訂閱的部份都無法進行測試，雖然很多人都說可以透過 IIS 的 SMTP E-Mail進行，但是很可惜還是無法成功，後來我終於試出了一個方法，在此提供給大家，希望對大家在進行報表訂閱的部份可以有所幫助。

PS：下列的值由於透過 Reporting Service 的管理介面是無法修改，所以請透過 Reporting Service 的設定檔進行修改，下列以Reporting Service 2008R2為例。

1、打開 Reporting Service 的設定檔進行修改。
C:\Program Files\Microsoft SQL Server\MSRS10_50.InstanceName\Reporting Services\ReportServer\RSReportServer.config

2、清除 SMTPServer 標籤的值，但請注意此標籤請勿刪除。
<SMTPServer></SMTPServer>

3、設定郵件以存放檔案的方式進行，我將他指定到 D:\tmp\mail 中，另外請注意需確認您 Reporting Service 的啟動帳號擁有讀取與寫入的功能。
<SMTPServerPickupDirectory>D:\tmp\mail</SMTPServerPickupDirectory>

4、設定寄件者的E-Mail
<From>caryhsu0316@gmail.com</From>

5、設定傳送的方式，預設為2，請改為1使用本機端的SMTP。
<SendUsing>1</SendUsing>

6、重新啟動 Reporting Service。

PS：這裡也許有人會好奇的問，都沒有設定 SMTP 的 Service 這樣可以嗎？但由於此處只是透過 Reporting Service 進行E-Mail的產生，而且並未沒有寄送的動作，所以只要以上的步驟即可，另外當報表訂閱進行後，會將檔案放置在步驟三指定的目錄中，所以透過如 Outlook 開啟後即可。

如何：為電子郵件傳遞設定報表伺服器 (Reporting Services 組態)
http://msdn.microsoft.com/zh-tw/library/ms345234.aspx
設定報表伺服器的電子郵件傳遞
http://msdn.microsoft.com/zh-tw/library/ms159155.aspx
RSReportServer 組態檔
http://msdn.microsoft.com/zh-tw/library/ms157273.aspx

關鍵字：Reporting Service、Local SMTP、報表訂閱

泡麵式安裝法(下集) - 如何大量並快速的安裝 SQL Server

        根據上一集 [泡麵式安裝法(上集) - 如何大量並快速的安裝 SQL Server]  的介紹，本篇再來介紹如何透過映像進行 SQL Server 的快速安裝，再請大家參考下列的步驟進行。

1、Windows 設定

1-1 先將上集介紹並已安裝的映像複制一份出來，如果是Hyper-V的VHD檔，請另外建立一台Hyper-V機器後，透過此複製的VHD檔進行啟動，其他的方式 (如GHOST等) 請再自行設定，啟動後畫面如下，先進行 Windows 的設定，並選擇您的地區別與鍵盤配置等。

1-2 輸入您 Windows 的產品金鑰。

1-3 勾選接受授權合約。

1-4 第一次登入，請輸入您的 [Administrator] 的密碼。

1-5 登入後請記得更換您的電腦名稱與網域。

2、SQL Server 安裝

2-1 選擇繼續安裝 SQL Server，此時有兩種方法可以進行，再請自行選擇其一。

2-1-1 透過 Microsoft SQL Server 2008 R2 -> 組態工具 -> 完整的 SQL Server 2008 R2 Installator。

2-1-2 透過安裝光碟的 [進階] -> [完成備妥的 SQL Server 獨立執行個體的映像]。

2-2 確認程式的支援規則，點選確定。

2-3 此步驟會檢查需安裝的檔案，選擇安裝直接進行。

2-4 此步驟會檢查您目前的環境是否支援 SQL Server 的安裝。

2-5 選擇您要安裝的版本或選擇輸入產品金鑰。

2-6 授權條款確認。

2-7 選擇要安裝的個體，這裡主要選擇之前您已準備的 SQL Server SysPrep。

2-8 確認您目前需要安裝的功能，請注意，此處為您之前有選擇的功能，如果有需要增加 SQL Server Management Studio 等功能時，在第3大項的流程中會再告訴大家如何進行。

2-9 選擇您的執行個體是要預設或是以具名的方式。

2-10 設定 SQL Server 各服務的啟動帳號與預設的啟動類型。

2-11設定 SQL Server 的驗證模式。

2-12 如果您有選擇安裝 Reporting Service 時，在此處可以選擇 Reporting Service 的組態。

2-13 選擇發生錯誤時，是否要進行回報。

2-14 再次確認映像的規則。

2-15 準備開始進行安裝，這裡會列出您之前的選項，您可以在此再加以確認。

2-16 安裝完成的畫面，在這裡通常如果按照一般的安裝方式，都需要一段較長的時間，但是在我的機器中只需三分鐘而已，其實主要的檔案都已經安裝好了，在這裡只是重新配置而已，所以非常的快速。

2-17 安裝完成後，由於沒有 [SQL Server Management Studio]，所以我們透過內建的 SQLCMD來進行測試。

2-17-1 開啟一個 DOS 視窗
2-17-2 輸入 sqlcmd -h localhost
2-17-3 輸入 select @@version; go
2-17-4 上述完成後，即可秀出目前安裝的版本。

2-18 為了確認 SQL Server SysPrep真的有進行重新配置，所以我們透過下列的指令進行主機名稱的確認。

2-18-1 開啟一個 DOS 視窗
2-18-2 輸入 select @@servername; go
2-18-3 輸入 select convert(varchar(255), SERVERPROPERTY('servername'));
2-18-4 確認兩個指令輸出的電腦名稱是否相同

以上的步驟就算已經完成整個 SQL Server 的安裝，但是因為很多人習慣使用 [SQL Server Management Studio] 等 Client Tools ，所以下面的章節，我就來介紹如果進行安裝。

3、SQL Server Client Tools 安裝

3-1 放入安裝光碟，選擇 [安裝] -> [新的安裝或新功能加入到現有安裝]。

3-2 此步驟會檢查需安裝的檔案，選擇安裝直接進行。

3-3 確認程式的支援規則，點選確定。

3-4 安裝類型請選擇 [新安裝或加入共用功能]。

3-5 再次選擇您要安裝的版本或選擇輸入產品金鑰。

3-6 授權條款確認。

3-7 安裝程式角色，請選擇 [SQL Server 功能安裝]。

3-8 選擇您要的 [共用功能] ，此部份可以依照您的需求進行選擇。

3-9 系統檢查安裝規則。

3-10 磁碟空間需求確認，點選下一步。

3-11 選擇伺服器的組態，在此處由於我又另外安裝了 SQL Server Integration Service，所以需要再另外指定啟動帳號與啟動類型。

3-12 選擇發生錯誤時，是否要進行回報。

3-13 安裝組態規則檢查，點選下一步。

3-14 準備安裝前，這裡會列出您之前的選項，您可以在此再加以確認。

安裝完成後，您就可以正常的使用 SQL Server Client Tools ，相信這個方法應該可以節省大家在大量步署上的許多的時間，也請大家多多利用。

相關連結：

• 泡麵式安裝法(上集) - 如何大量並快速的安裝 SQL Server

關鍵字：SQL Server、Hyper-V、SysPrep、SQL Server Setup、SQL Server SysPrep