[SQL]2014 Cloud Hero 資料庫應用上機考研究 ( 中 )

2014-05-14

1944
0
認證考試
2020-02-20

2 HA

A.「Log Shipping」: 算是比較容易實作的題目，基本上只要注意 Windows 目錄安全性的設定，就沒有太大的問題了

先啟用之後，才可以按下「備份設定」

這裡有兩個重要的目錄設定，因此我在這台主機上建立一個 C:\SQLHero\Logshipping 的目錄，必且設定這個目錄分享出來，因此重點在這個目錄 SQL Server 要能將備份檔案存放在這個目錄下，並且讓別台電腦來存取這個分享的目錄。

目錄的分享權限

大部分人員會失敗的點都會在這個部分，因為預設的分享目錄下 SQL Server 是沒有權限寫入的，因此這裡要特別注意要在「安全性」下把「NT Service\MSSQLServer」的帳號給加入，並且完成控制該目錄。那大致就沒有甚麼問題了。

接下來就要把目的主機一台一台給加入了，主要有三個頁籤的部分要設定

在「複製檔案」這個頁籤有個重要的地方要注意，在之前我們設定要來源主機備份的目錄，要特別把 SQL Server 服務的帳號加入到安全性內，而在這裡要注意一下，我們並需要在目的主機上也要提供一個目錄，讓 SQL Agnet 可以完全的掌控，因此我在兩台目的主機上都建立一個 C:\SQLHero\Copy 的目錄。

「還原交易紀錄」這裡就看考試的時候要選擇甚麼了，這裡我先改成「待命模式」，這樣比較容易清楚是否有正常把變更的狀況傳送過來。

目的主機上的目錄要注意安全性的設定

完成相關設定

B. 「AlwaysOn」

這個部分可能就要花點時間，不確定考試的環境下是否已經預先安裝好「容錯移轉叢集」，如果沒有的話，那麼就要先手動透過「新增角色及功能精靈」來做。

接下來透過「容錯移轉叢集管理員」，選擇「新增叢集」

把三台 SQL Server 主機都加入

賦予一個叢集專用的名稱和 IP

做到這裡應該就先把環境給預備好了，接下來開啟「SQL Server 2014 組態管理員」，選擇「SQL Server 服務」下的 SQL Server 執行個體，選取「啟用 AlwaysOn 可用性群組」

接下來就可以開啟 SSMS，選擇「AlwaysOn 高可用性」按下滑鼠右鍵選擇「新增可用性群組精靈」

給一個可用性群組的名稱

選擇目的資料庫

這裡可能就要看當時題目來加入不同的執行個體和設定相關參數

這裡要指定一個 Share 的目錄，跟前面注意的事項一樣，要注意安全性和分享的權限

看起來似乎大功告成，但在這裡如果你按下完成的話，絕大部分的時候你可能會等待非常久，然後收到一個錯誤訊息，表示在其他副本上的資料庫無法加入到可用性群組。此時如果你檢查一下 SQL Server 的「Error Log」，應該會看到有類似這樣的錯誤訊息 ( Database Mirroring login attempt by user 'MYLAB\2014DB2$.' failed with error: 'Connection handshake failed. The login 'MYLAB\2014DB2$' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: 192.168.1.61] )，為什麼會有這樣的訊息呢 ? 主要是因為安裝 SQL Server 的時候是採用預設安裝的話，那麼 SQL Server 會採用服務的帳號來做啟動，此時你可以用兩種方式，一種是更改 SQL Server 服務的啟動帳號，一種是在安全性加入其他副本的電腦，並且賦予端點連線的權限。

這裡我選擇後者，因此如果要更改啟動的服務帳號，那麼你一開始就要改了，否則做到這裡才改，那麼前面的 Log Shipping 的相關設定又要調整，因此這裡可能要考一下大家對指令的熟悉度。記得在每一台都要設定，否則到時候容錯移轉之後，就會有不能連接的狀況了。

		-- 建立 Windows Login
		
		CREATE LOGIN [MYLAB\2014DB1$] FROM WINDOWS 
		
		CREATE LOGIN [MYLAB\2014DB2$] FROM WINDOWS 
		
		CREATE LOGIN [MYLAB\2014DB3$] FROM WINDOWS 
		
		-- 賦予端點連線的權限
		
		GRANT Connect on EndPoint::[Hadr_endpoint] To [MYLAB\2014DB1$]
		
		GRANT Connect on EndPoint::[Hadr_endpoint] To [MYLAB\2014DB2$]
		
		GRANT Connect on EndPoint::[Hadr_endpoint] To [MYLAB\2014DB2$]

設定好之後應該就大功告成了。

因為從考試範圍看不出來是否要建立 Listen，如果需要的話在建立一下，這個應該就沒有甚麼難度了。

3. Security

A.「權限設定」: 此部分我覺得題目有些地方有問題，我個人覺得應該修改一下，將原本的《建立 login l 對應到 Windows group g》更改為《建立 login l ~~對應到 Windows group g~~》，因為 SQL Server 的 Login 帳號應該是沒有辦法對應到 Windows 的群組，如果說指的是 Windows 帳號，那麼在 SQL Server 內也不能 rename，因此這個部分可能要看一下實際考試所寫的題目。

至於建立伺服器角色，如果要搶時間就用 Script，但是就算用 GUI，應該也差不多一分鐘才對。

		-- 建立登入帳號 l
		
		CREATE LOGIN [l] WITH PASSWORD='P@ssw0rd123'
		
		-- 建立自訂伺服器角色 sr
		
		CREATE SERVER ROLE [sr]
		
		-- 將　sr 加入到 bulkadmin 伺服器角色內
		
		ALTER SERVER ROLE [bulkadmin] ADD MEMBER [sr]
		
		-- 將　l 加入到 sr
		
		ALTER SERVER ROLE [sr] ADD MEMBER [l]

至於設定權限的部分，語法也算是單純

		-- 切換資料庫到 tempdb
		
		use [tempdb]
		
		-- 建立使用者 u
		
		CREATE USER [u] FOR LOGIN [l]
		
		-- 建立資料庫角色
		
		CREATE ROLE [r]
		
		-- 把使用者加入到角色內
		
		ALTER ROLE [r] ADD MEMBER [u]
		
		GO
		
		-- 建立 SCHEMA
		
		CREATE SCHEMA [s]
		
		GO
		
		-- 更改使用者預設 SCHEMA
		
		ALTER USER [u] WITH DEFAULT_SCHEMA = [s]
		
		-- 建立範例 TABLE t
		
		CREATE TABLE s.t(t1 int )
		
		INSERT s.t VALUES (1),(2),(3)
		
		GO
		
		-- 建立範例預存程序 sp
		
		CREATE PROC s.sp AS SELECT t1 from t
		
		GO
		
		-- 測試 SP 並且設定權限
		
		EXEC s.sp
		
		GRANT EXECUTE ON SCHEMA::[s] TO [u]
		
		DENY SELECT ON SCHEMA::[s] TO [u]