如何建立PowerView的報表

        在前面的幾篇已有完整的介紹到如何進行PowerView的環境建置與在Excel中進行報表設計的方式，所以本篇再來介紹如何透過PowerPivot連線到SQL Server進行PowerView報表的設計。

參考連結：
PowerView環境安裝與建置
http://caryhsu.blogspot.tw/2012/09/powerview.html
如何在Excel 2013中使用PowerView
http://caryhsu.blogspot.tw/2012/12/excel-2013powerview.html


PowerView環境安裝與建置

先決條件：
1、如果您的Excel的版本為2013以下時，需請您先行安裝下列的檔案。
        PowerPivot for Excel 安裝 (2010)
        http://msdn.microsoft.com/zh-tw/library/ee210599.aspx
2、範例資料庫 - Northwind
        SQL Server 2000 Sample Databases
        http://archive.msdn.microsoft.com/northwind/Release/ProjectReleases.aspx?ReleaseId=1401


Demo說明：
由於PowerView目前只能透過Excel與直接連接到Analysis Service的Tabular Mode，所以本Demo我將介紹如何透過Excel連線到SQL Server，然後設計一張客戶國家別的分佈統計圖。


設定流程：
1、建立Excel檔案連結至SQL Server
1-1 選擇 [資料] -> [從其他來源] -> [從SQL Server]

1-2 輸入您的伺服器名稱與登入的資訊

1-3 選擇 [Northwind] 資料庫，並先只選擇 [Customers] 的資料表即可。

1-4 確認連線資訊

1-5 匯入資料的部份，請選擇放至在 [新工作表] 即可。

1-6 確認資料已匯入至Excel中。

2、建立PowerPivot連線
2-1 選擇 [PowerPivot] -> [建立連結資料表]

2-2 此時他會將你目前選擇的工作表建立PowerPivot的連線

3、建立PowerView報表
3-1 請先將剛剛建立的Excel上傳至SharePoint的網站上

3-2 選擇Excel的檔案

3-3 確認是否更換excel的顯示名稱

3-4 上傳完成後，請點選 [文件] -> [新增文件] -> [BI語意模型連接]

3-5 請輸入此連接的名稱，而活頁簿URL，請輸入剛剛上傳的Excel名稱。

PS:預設路徑為：http://server-name/share%20Documents/powerview_demo.xlsx


3-6 建立完成後，請選擇 [建立 Power View報表]

4、設計PowerView報表
4-1 開啟PowerView的報表後，右半邊即會載入您在Excel中有設定PowerPivot連線的資料表

4-2 請勾選 [Country]與[CustomerID]，然後在下方欄位的部份，請將 [CustomerID]變更為 [計數 (不是空白)]

4-3 點選 [設計] -> [線條]

4-4 此時即可將剛剛的資訊透過線條圖的方式呈現。

關鍵字：SQL Server、Reporting Service、PowerView

如何結合Database Mirroring與Log Shipping

         在我前面多篇的文章中已有介紹到多種HA(High Availability Solutions)的架設方式，而其實最簡單也是最多人入門使用的，就是Log Shipping與Database Mirroring，但由於Database Mirroring不像AlwaysOn可以擁有多個複本，只能有一個鏡射節點，所以這時候我們就可以透過Database Mirroring與Log Shipping的整合，藉以達到多個複本，也可以達到同地與異地的備援方案。

前導文章：

如何建立 Log Shipping
http://caryhsu.blogspot.com/2012/02/log-shipping.html
SQL Server - 如何建立 Database Mirroring
http://caryhsu.blogspot.com/2011/12/sql-server-database-mirroring.html


相關的架設方式，其實可以參考MSDN上的說明，但可惜的是官網上說的太過於簡單，所以我才整理這篇文章來加以說明，希望大家可以更清楚的了解到如何整合Database Mirroring與Log Shipping.


架構說明：
1、Database Mirroring：
1-1 建議使用高可用性的架構(High-Safety Mode)。
1-2 建議有一台監控伺服器(Witness Server)

2、Lop Shipping：
2-1 不一定要有監控伺服器(Witness Server)，可以透過手動進行Log Shipping的Failover。
2-2 備份的檔案必須放在第三台上面，也就是檔案放在一台獨立的主機上。
2-3 還原交易記錄請選擇不復原模式。

設定方式：
1、請先參考之前的文章進行架設，但整合上有些注意事項，詳細的部份，請一定要依照架構說明的部份進行。

SQL Server - 如何建立 Database Mirroring
http://caryhsu.blogspot.com/2011/12/sql-server-database-mirroring.html

2、設定Log Shipping。
2-1 指定備份的位址，此時我將備份的位置指定到備份主機上的目錄，再次提醒，此位置必須讓Database Mirroring與Log Shipping的主機都要能有讀寫的權限。

PS：由於資料的限制，所以我先將檔案備份的位址放在AD的主機上，其實應該要在一台獨立的主機上，而不是放在AD上，以免影響AD主機的運作。


2-2 選擇加入Log Shipping的Secondary主機後，由於我在Secondary沒有這個資料庫的存在，所以我選擇第一項進行。

2-3 切換到 [複製檔案] 的頁次，輸入Log Shipping中Secondary上的分享路徑上。

2-4 切換到 [還原交易記錄] 的頁次後，此部份請選擇 [不復原模式] ，藉以避免在復原的情況下，其他使用者的存取，造成復原的失敗。

2-5 設定完成後，請將此設定輸出成指令檔，後續上會使用到。

2-6 建立完成後，請先確認各個Job執行上應該都能正常的執行。

2-6-1 Primary上的 Job清單
LSBackup_Northwind

2-6-2 Secondary上的Job清單
a. LSAlert_CARY-TEST
b. LSCopy_WIN-2008R2-1_Northwind
c. LSRestore_WIN2008R2-1_Northwind


3、整合Database Mirroring與Log Shipping
3-1 請在Database Mirroring上進行手動的Failover，將節點切換到Mirror的機器上。
3-2 請在原本是Mirror的機器上(目前為Principal)執行之前儲存的Script。

PS:
a. Script的部份分成兩大區塊，上半部是在(Primary)的主機上執行，而下半部是在Secondary的主機上執行，但請注意，我們在目前Principal(之前的Mirror)的機器上只需執行上半部即可。
b. 基本上你另存的語法不需作任何的修改，只需執行在Principal (之前的Mirror)的主機上即可。
c. 由於Log Shipping的GUI介面只能指定到一個Primary主機，所以一定要透過語法的方式進行。


語法內容 -- 只截取上半部
-- 在主要伺服器端執行下列陳述式，以設定記錄傳送
-- 針對資料庫 [WIN-2008R2-1].[Northwind]
-- 指令碼需要在主要伺服器端的 [msdb] 資料庫內容中執行。
-------------------------------------------------------------------------------------
-- 加入記錄傳送組態

-- ****** 開始: 要在主要伺服器端執行的指令碼: [WIN-2008R2-1] ******


DECLARE @LS_BackupJobId AS uniqueidentifier
DECLARE @LS_PrimaryId AS uniqueidentifier
DECLARE @SP_Add_RetCode As int


EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database
@database = N'Northwind'
,@backup_directory = N'\\win-cary-ad\log_backup'
,@backup_share = N'\\win-cary-ad\log_backup'
,@backup_job_name = N'LSBackup_Northwind'
,@backup_retention_period = 4320
,@backup_compression = 2
,@backup_threshold = 60
,@threshold_alert_enabled = 1
,@history_retention_period = 5760
,@backup_job_id = @LS_BackupJobId OUTPUT
,@primary_id = @LS_PrimaryId OUTPUT
,@overwrite = 1


IF (@@ERROR = 0 AND @SP_Add_RetCode = 0)
BEGIN

DECLARE @LS_BackUpScheduleUID As uniqueidentifier
DECLARE @LS_BackUpScheduleID AS int


EXEC msdb.dbo.sp_add_schedule
@schedule_name =N'LSBackupSchedule_WIN-2008R2-11'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 15
,@freq_recurrence_factor = 0
,@active_start_date = 20130312
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_BackUpScheduleUID OUTPUT
,@schedule_id = @LS_BackUpScheduleID OUTPUT

EXEC msdb.dbo.sp_attach_schedule
@job_id = @LS_BackupJobId
,@schedule_id = @LS_BackUpScheduleID

EXEC msdb.dbo.sp_update_job
@job_id = @LS_BackupJobId
,@enabled = 1


END


EXEC master.dbo.sp_add_log_shipping_alert_job

EXEC master.dbo.sp_add_log_shipping_primary_secondary
@primary_database = N'Northwind'
,@secondary_server = N'cary-test'
,@secondary_database = N'Northwind'
,@overwrite = 1

-- ****** 結束: 要在主要伺服器端執行的指令碼: [WIN-2008R2-1]  ******

3-3 此時請再執行各個Log Shipping上的Job，藉以確認是否正常運作，如此即完成此兩個功能的整合。


參考網址：
Database Mirroring and Log Shipping (SQL Server)
http://msdn.microsoft.com/en-us/library/ms187016.aspx


關鍵字：Database Mirroring、Log Shipping、Database Mirroring and Log Shipping Working Together、Setup database mirroring and log shipping together

資料庫使用者最小權限設定

        使用者權限的部份往往因為方便性，常常看到許多的管理者都直接使用SA的帳號進行連線，但萬一系統遭受到如SQL Injection等入侵攻擊時，連帶的直接就讓入侵者直接取得資料庫的最高權限，讓整個資料庫系統停止服務。

有鑑於此本章我們就來介紹如何設定一個最小權限的使用者，藉以提供前端的程式進行連線，一來可以提供更佳的防護，二來更可以社絕資料庫間相互的影嚮所造成的損失。


操作流程：
1、開啟Management Studio。
2、選擇 [Security] -> [Logins] -> [New Login]

3、輸入使用者資訊

3-1 帳號的部份建議使用  Windows Authentication。
3-2 Default Database(預設資料庫)請不要使用預設的 master ，請選擇一個特定的資料庫，因為通常前端的程式如果有存取master，代表這些可能都是可疑的行為，所以指定使用者到一個特定的資料庫，藉以避免監控程式的誤判。

4、Server Roles的部份請不需勾選，尤其是sysadmin，請不要勾選。

5、此部份請勾選使用者特定的資料庫。

5-1 Database Role請先取消 [db_owner] 的勾選。
5-2 因為一般應用程式會針對資料庫進行Insert、Update、Delete，所以請勾選 [db_datareader]、[db_datawriter]即可。

6、按下確定後，會出現下列的錯誤訊息，其實這訊息主要是因為在1-5的步驟中取消 [db_owner]的部份，所以才會出現這個訊息，你可以忽略他。

針對這個問題你可以將操作的語法輸出後，你就可以看到語法的最後因為要針對 db_onwer進行成員的設定，所以才會有下列的錯誤訊息了，點選 ok 後，帳號即建立完成。

透過上面的方式建立出來的帳號即會只使用最小的權限存取資料庫，如此一來就算真的因為前端的程式設計不佳造成SQL Injection的問題被入侵，因為權限最小化的關係，入侵者也頂多只能針對此資料庫進行存取，無法進一步的進行其他資料庫或系統的破壞，藉以將傷害降到最低。


錯誤訊息：
Drop member failed for DatabaseRole 'db_owner'. (Microsoft.SqlServer.Smo)

An Exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)

An object or column name is missing or empty. For SELECT INTO statements, verify each column
has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not
allowed. Change the alias to a valid name. (Microsoft SQL Server, Error:1038)



問題原因：
SE [AdventureWorks2012]
GO
ALTER ROLE [db_owner] DROP MEMBER []
GO

PS：因為取消勾選時，工具的介面在產生語法上的錯誤，所以造成這種情況。

關鍵字：
最小權限設定、minimum permission、Minimum SQL login permissions

SQL Server 容錯叢集 vs Oracle RAC

        我想許多在評估SQL Server與Oracle的使用者，大多都會問為何SQL Server無法像Oracle一樣推出一個像RAC機制的Database Service，藉以達到Load Balance，其實這個在我之前的文章也有解釋過，但因為還是有許多的客戶與使用者有問到，所以我特別在這篇中再重新整理與說明，並希望給大家多一些參考。

PS：由於本人並未完全熟悉Oracle的部份，所以就技術底層的比較，本篇並不打算真正比較，以免成為眾所之的，也請大家如有認為有不妥的地方，再請提出討論，謝謝。


先前文章：
SQL Server 負載平衡架構介紹(Load Balancing)
http://caryhsu.blogspot.tw/2011/03/sql-server-load-balancing.html


1、成本比較：
其實在Oracle RAC的部份，由於我接觸的不多，所以不方便多進行著墨，但在Oracle官方宣稱RAC可以降低總成本，這點是我認為比較有值得探討的部份，因為光是初期的建置成本就已非常的高，這也是讓使用人望之退步的部份，想當初在前公司的時候，當時公司的資訊長找我與幾位Oracle的顧問進行開會討論，當實際討論到我方預算時(包含軟硬體)，對方表情的變化真的是讓我印像深刻(京劇變臉)，坦白說我們之前也有作功課，所以不可能亂開價格，但想不到我們的預算還是與Oracle RAC的費用有所差距，後來對方就直接建議我們建置單機的Oralce環境比較好，所以後來我們就多方考量後，選擇了SQL Server，但坦白說SQL Server也真的沒有讓我們失望。


1-1 維護合約費用：
Oracle的維護合約必須每年都要購買，如果有一年沒有買的話，就必須補齊之前未買的年份，假設你的主機在第三年的時候發生問題，需要請原廠協助時，你就必須也同時補買第一、二年的合約，此時才可以進行協助。

1-2 授權費用：
下列是分別兩家的售價表，可以看出兩者之間價格的差異。
Oracle的報價：

SQL Server報價：

總體簡易報價比較：

Component	SQL Server Solution 2012	Oracle RAC Solution 11g
Core Database	USD$115K	USD$380K
HA option	Included	USD$184K
Total	USD$115K	USD$564K

Oracle Price List：http://www.oracle.com/us/corporate/pricing/technology-price-list-070617.pdf
Microsoft License Advisor：http://mla.microsoft.com/default.aspx


2、技術服務：
Oracle與SQL Server的技術服務在台灣都有當地的服務，但在遇到台灣無法解決的問題需開發部或其他進一步協助時，SQL Server還是有亞太中心的服務，在語系與時間上比較能配合，但如果是Oracle的話，你就只能配合國外的工程師，另外你也要有好的英文溝通能力才可以。


3、平台比較
Oracle可以配置在許多不同的作業系統上，如Linux、Windows與Unix上，但我認為這個部份取決你使用的平台，如果在Linux與Unix上，當然是Oracle最好了，但是如果是Windows上，雖然Oracle也可以支援，但我認為在Windows上，Oracle一定不會跑的比SQL Server好。


4、效能比較
Oracle RAC的效能主要是提升CPU與機器的使用率，藉以發揮機器效用的最大化，但坦白說大部份的資料庫效能皆在於Memory與Disk，CPU很少使用到滿載的情況，而在Oracle RAC中也是使用到同一個SAN Storage，這個也是與SQL Server的情況相同。

另外SQL Server最常被問題的就是在Cluster的架構下，只能是A/P Mode，也就是只有一台可以進行服務，而另一台則是在 Standby的狀況下，這樣對許多企業主來說，另外一台實在非常的可惜，其實這時候可以考慮透過 A/A Mode的架構，藉以讓兩台SQL Server都可以同時進行服務，詳細的部份，可以參考我先前的文章。

參考文章：
SQL Server - 雙主動模式叢集環境架設
http://caryhsu.blogspot.tw/2012/01/sql-server.html


5、高可用性復原模式比較

在SQL Server 2012上由於多加了AlwaysOn，所以可以只針對單一的資料庫進行HA，而且在硬體的選擇上也更多元化，建議有興趣的使用者可以參考我的AlwaysOn的相關文章。

High Availability Recovery Time：

Technology	Recovery Time (approx.)
Oracle RAC	30 – 60 seconds*
SQL Server 2012 AlwaysOn Availability Groups	<45 seconds
SQL Server 2012 Failover Cluster	Seconds to minutes

參考文章：
隱藏在Oracle 真實應用集群 (RAC) 市場訊息背後的真實
http://www.microsoft.com/china/sql/prodinfo/compare/oracle/oracle-rac.mspx
Real World Oracle Real Application Cluster challenges
http://www.linxcel.co.uk/Whitepapers/Real-World%20Challenges%20for%20Oracle%20RAC%20Implementation-Issue%201.pdf
Why Oracle RAC Might Not Be Suitable For You A Competitive Review of Real Application Clusters (RAC)

http://download.microsoft.com/download/C/D/C/CDCA80B2-FD63-49C5-AC66-8E3A179E31F7/SQL_Server_2012_Why_Oracle_RAC_Might_Not_Be_Suitable_For_You_Apr2012.pdf


關鍵字：SQL Server、Oracle、RAC、Oracle Real Application Clusters、Cost、容錯叢集、Cluster

SQL Server - 如何在資料庫鏡像上啟用快照集隔離

       在前面的文章中有介紹到如何透過快照集隔離 (Snapshot Isolation Level) 來降低鎖定的問題，但如果要在有設定Database Mirroring的資料庫上進行設定時，就會遇到下列的錯誤訊息。


先前文章：
如何使用快照集隔離來降低鎖定問題的發生
http://caryhsu.blogspot.tw/2012/01/blog-post_31.html


錯誤訊息：
訊息 1468，層級 16，狀態 4，行 4
無法在資料庫 "Northwind" 上執行作業，因為它牽涉到資料庫鏡像工作階段。
訊息 5069，層級 16，狀態 1，行 4
ALTER DATABASE 陳述式失敗。

這個問題主要是由於Principal與Mirror之間需要進行同步確認，所以會保持連線，但是進行快照集隔離時需要變更資料庫的設定，所以不能有其他的使用者連線。

其實這個問題，解決方法上很簡單，只能透過 [移除鏡像] 的方式進行即可，但是這時候大家可能會有一個疑問，那就是一定要移除嗎？可否透過暫停Database Mirroring的方式，但可惜的是，這個方法是不行的，只能透過移除的方式，當設定好快照集隔離後再重新設定Database Mirroring即可。

參考連結：
How to Enable RCSI for a Database with Database Mirroring
http://blogs.msdn.com/b/sqlcat/archive/2010/03/16/how-to-enable-rcsi-for-a-database-with-database-mirroring.aspx


關鍵字：SQL Server、Database Mirroring、Snapshot Isolation Level、ALTER DATABASE