如何建立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