[SQL]2014 Cloud Hero 資料庫應用上機考研究 ( 下 )
4 開發
A.「DDL Trigger」: 要限制不允許 SSMS 的 IntelliSense 不允許登入
基本上 DDL Trigger 這個題目算是很中規中矩的題目，但最大的困難會有兩個部分，一個是要能寫出完整的語法 ( 我想考試時間應該沒有辦法使用 Google 或者是 MSDN 吧 )；另外一個是怎麼拼出 IntelliSense 完整的程式名稱。前者似乎只能自己靠自己去熟悉了，後者是可以偷偷作弊一下。
DBA 的好幫手「SQL Profiler」，開啟錄製之後在 SSMS 查詢內輸入一些 SQL 語法，讓 IntelliSense 有運作一下，就可以抓到他的 Application Name，知道名稱之後那就容易了。
剩下就是寫一段語法當登入的時候把他強制給 ROLLBACK 囉
CREATE TRIGGER Server_Disable_IntelliSense
ON ALL SERVER FOR LOGON
AS
BEGIN
IF ( APP_NAME() = 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense' )
BEGIN
ROLLBACK ;
END
END
B.「建立預存程序」
要注意的就是要回傳的參數要加上 OUTPUT，另外就是「RECOMPILE」和「ENCRYPION」
CREATE PROC sp_GetOrdersCustomCompany( @Begin DATETIME, @End DATETIME, @Total INT OUTPUT )
WITH RECOMPILE, ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
select distinct CompanyName from Orders o
inner join Customers c ON o.CustomerID = c.CustomerID
where OrderDate between @Begin and @End
SET @Total = @@ROWCOUNT
END
GO
如果真的背不起來語法結構的話，不妨試試看 SSMS「範本」的功能，可以讓你稍微知道大概的語法結構，剩下的就是組合的功力囉。
5 其他
A.「Peer to Peer Transaction Replication」: 這個就應該比較少有人練習過的題目了
基本上這個題目主要是要考你一個基本觀念，就是在點對點交易複寫的時候，就是每個 Host 自己既是發行者，也是散發者。在舊的版本中，設定稍微麻煩一點，在新的版本中都已經有方便的精靈一步一步造著做，應該就沒有太大的問題。但也就那麼湊巧，一個地雷又不小心的被我踩到了。基本上 Replcation 是透過 SQL Server Agent 來定時處理的，在擬透過「設定散發」的時候，如果判斷 SQL Server Agent 服務的啟動方式不是自動的話，那 SQL Server 會問你是否要把設定為自動，但很不幸的的如果你是用預設安裝 SQL Server 的啟動帳號的話，那麼那個帳號會沒有權限來更改服務的設定，因此可能過程中會有失敗，基本上這個不是太大的問題，那就自己先透過組態管理員，更改啟動模式就好了。
首先，我們要先在每個要參予複寫的 SQL Server 執行個體設定自己為散發的主機，因此以下的步驟必須要在三台進行相互複寫的環境上進行。首先我們在 SSMS 上選擇「複寫」，按下滑鼠右鍵選擇「設定散發」，接著幾乎都是選下一步就可以完成了，應該沒有太大的困難。
在這個步驟本來想說產生指令碼，然後套用到其他機器上執行 Script，但後來發覺整個選擇下來不到 15s，產生 Script 之後還要開啟檔案，修改主機名稱，然後把這個 Script 套用到其他執行個案上，似乎也沒有節省到幾秒鐘的時候，因此在其他執行個體上，我也都還是直接用精靈的方式來做設定了。
接著我在三台電腦上都先建立好要測試用的資料庫和資料表
-- 建立測試資料庫
CREATE DATABASE P2PSample
GO
-- 建立測試資料表
USE [P2PSample]
CREATE TABLE T( T1 INT NOT NULL PRIMARY KEY, T2 VARCHAR(10) )
GO
接下來我們就可以正式進入到重頭戲設定點對點交易複寫了，首先透過 SSMS 選擇「複寫」→「本機發行集」，按下滑鼠右鍵選擇「新增發行集」
選擇我們測試的資料庫
選擇「點對點發行集」
這裡要選擇「安全性設定」來進行設定，這裡偷懶一點就直接設定網域管理者的帳號
到這裡看起來似乎就做完了，但可別高興得太早，這裡只是先設定好要發行的資料庫和資料表，我們還沒有設定要跟那些節點做資料複寫喔。
完成上述步驟後，我們在「複寫」→「本機發行集」下面就可以看到前面所設定好的發行集，此時我們選擇該發行集，按下滑鼠右鍵選擇「設定點對點拓樸」
接下來這一步就是關鍵了，在新的版本中可以透過精靈，你可以把其他要複寫的節給給加上去，因此在頁面上按下滑鼠右鍵，選擇「加入新的對等節點」，把其他主機給加入，並且把節點之間的關聯給設定好
接著設定 Log reader Agent 的安全性，這裡也都直接用網域管理者的帳號來連接，基本上設定第一個之後，然後勾選下方選項的將所有節點都使用相同的設定。
散發代理也是比照剛剛同樣的方式來做設定
那我們要怎麼來驗證是否有成功呢 ? 此時可以用一開始所設定的中央管理伺服器來新增查詢，這樣同樣的指令就可以一次下到所有關聯的主機上，因此
找其中一個節點新增資料
就可以看到資料有散發到其他電腦上了 ( 沒有 2014db1 的原因是因為中央管理伺服器不能註冊自己上去 )
B.「SQL Server Agent Proxy」: 這是比較容易實作的題目，但要怎麼來驗證是否真的有切換使用者執行呢 ?
因為 SQL Server 2014 目前沒有隨 SQL Server 提供 SSDT ，因此我先在我的電腦上建立一個 SSIS 的封裝，這裡面只有做一件事情，我讓他去執行一段 SQL 指令
利用 SUSER_NAME 來取得登入者的名稱，把這個紀錄成到一個 LOG 資料表
INSERT X.dbo.LOG( session_id, username ) VALUES ( @@SPID, SUSER_NAME() )
LOG 資料表的建立語法如下
-- 建立資料庫 X
CREATE DATABASE X
GO
-- 建立存放紀錄的 LOG 資料表
CREATE TABLE [dbo].[LOG](
[session_id] [int] NOT NULL,
[username] [nvarchar](50) NULL,
[logintime] [datetime] NULL DEFAULT GETDATE()
) ON [PRIMARY]
GO
材料預備好之後，那接下來就容易了，首先在安全性中加入 Windows 帳號 a，並指定 a 可以在 X 資料庫有權限 ( 主要是為了能存資料 )
接著跟在做 Log Shipping 的類似，在「安全性」→「認證」下新增一個認證對應到 Windows 帳號 a
在 「SQL Server Agent」→「Proxy」→「SSIS 封裝執行」下新增一個 Proxy 帳號，使用前一個步驟所建立之憑證。
接下來就可以新增一個 JOB 來測試了，先指定一個 JOB 名稱
接著在步驟這裡要更改類型為「SQL Server Integration Services 封裝」，並且更改執行身分為前一個步驟所建立的 Proxy 帳號，指定要執行的封裝檔案
在排程指定一個特定時間去執行
接下來我們在排程執行之後，就可以從 LOG 檔案中看到在所指定的時間有執行排程，而且有順利切換我們所指定的 User 身分去執行了。
Ｃ.「擴充事件」: Deadlock 偵測和分析
在 SQL Server 2008 的時候需要自己去寫，但在 2012 之後有 GUI 可以設定，否則考試的時候要寫出來還真的有點小困難。首先在 SSMS 下透過「管理」→「擴充事件」→「工作階段」下，使用滑鼠右鍵選擇「新增工作階段精靈」
有精靈那就容易了，基本上都是下一步
設定一個名稱
因為要偵測死結，因此這裡我就設定「不使用範本」
這裡我先打入 deadlock，找出相關的事件，這裡我就挑選「xml_deadlock_report」
指定要擷取的欄位，這裡我加入 sql_text，讓發生的時後知道是甚麼樣的 SQL 所造成
這裡如果是自己一般使用，我會選擇上面，把資料檔留著等一定時間再來觀察和處理，至於考試這種短時間要看的，那用下者就可以了。
Ａ
設定完成之後，當啟動之後，我們就可以故意製造一些 Deadlock 的狀況，那馬上就可以看到狀況了。
基本上只要主機還算 OK 的狀況下，應該不大會出甚麼樣的狀況，比起考 Hyper-V 來說，那就真的要看人品了，要是 XX 牌的 Storage 出狀況，任你怎麼努力也沒有辦法完成。只是我個人覺得這次的考試應該可以再增加一些，像是弄個 SSIS 配合專案部屬模式來轉不同的資料庫進行資料交換，或者是用 SSRS 來寫一張損益表或者是做一張統計表，要不然弄個 PowerPivot、PowerQuery和 Power View，否則感覺題目都比較單純在 SQL Server 管理上，其他的一些部分就比較沒有涉獵到了。