MISTECH 技術手抄本

MSSQL Server 2012 對於 table  中的欄位作加密已有支援，作業如下:

-- Create EmployeeReviews table and grant permissions
USE AdventureWorks

IF EXISTS (SELECT * FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.schema_id
           WHERE t.[Name] = 'EmployeeReview' AND s.[Name] = 'HumanResources')
 DROP TABLE HumanResources.EmployeeReview

CREATE TABLE HumanResources.EmployeeReview
(EmployeeID int NOT NULL,
 ReviewDate datetime DEFAULT GETDATE() NOT NULL,
 Comments varbinary(2000) NOT NULL)

-- Create database master key
IF NOT EXISTS(SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Pa$$w0rd'

-- Create certificate
CREATE CERTIFICATE HRAppCert
WITH SUBJECT = 'HR certificate'
GO

-- Create symmetric key
CREATE SYMMETRIC KEY HRKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE HRAppCert
GO

-- Insert encrypted data
    OPEN SYMMETRIC KEY HRKey DECRYPTION BY CERTIFICATE HRAppCert

    INSERT INTO HumanResources.EmployeeReview
    VALUES
    (1, DEFAULT, EncryptByKey(Key_GUID('HRKey'),'Increasing salary to $35,000'))

    CLOSE ALL SYMMETRIC KEYS


-- Read the decrypted data as HRApp
    OPEN SYMMETRIC KEY HRKey DECRYPTION BY CERTIFICATE HRAppCert
    SELECT CONVERT(varchar,DecryptByKey(Comments)) AS Comments FROM HumanResources.EmployeeReview
    CLOSE ALL SYMMETRIC KEYS

MSSQL Server 2012 在帳號認證可以採用 Windows Authentication 或是 SQL Server and Windows Authentication 混合模式兩種，認證模式若有修改則需重啟 SQL Server 才會生效。

若您只要使用 MSSQL Server 內鍵資料庫的帳號，但又要使用OS系統的 Security Policy 的原則，則除了將 MSSQL Server 的 Security 改為SQL Server and Windows Authentication 混合模式外，在建立資料庫帳號前先設定好 Local Security Policy，例如：密碼長度至少要 4 碼。

然後建立 MSSQL Server 帳號時，除了選擇 Login name 是以 SQL Server authentication 外，Enforce password policy 也要打勾才行。

這樣就可以使用 MSSQL Server DB 內鍵帳號登入了，個人是認為以此種方式較為單純，而不需用純 Windows Authentication ，因為管資料庫的並不一定會管OS系統對吧!最後要提醒一下，此時的帳號只是能登入 MSSQL Server ，若要它能使用特定的資料庫，還得在 User Mapping 中設定對那一個資料庫有權限，權限的等級為何，這樣這個帳號才有辦法存取特定的資料庫內容。

MSSQL Server 2000  建立一個資料庫時, 會有兩個檔案 MDF 檔(資料檔) 及 LDF 檔(交易檔), 有時因為某種原因(如當機, 備份時間不一致...等), 致使這兩個檔的 LSN 不一致. 或者只備份 MDF 檔, 因此在附加資料庫時, 會發生 LSN 的錯誤. 以致於資料庫無法附加


環境:
OS: Windows Server 2k3 SP2
MSSQL: 2000 版本8.0
資料庫: ATest
資料庫檔案: D:\DB\ATest_Data.MDF (遺失 ATest_Log.LDF 或兩者不同步, 導致附加資料庫時出現 LSN 錯誤)

要救回資料庫的方法如下:

Step 01: 新建立一個相同的資料庫, 建立好後 shutdown MSSQL 2k


Step 02: 移除新建立資料庫的檔案 D:\DB\ATest_Data.MDF & ATest_Log.LDF , 將原來的 ATest_Data.MDF 放回 D:\DB

Step 03: 在 SQL Query Analyzer 中執行以下语句，把该数据库设置为紧急模式
Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go



Step 04: 接著執行 下列指令
update sysdatabases set status = 32768 where name = 'ATest'

Step 05: 重启SQL Server服务, 會發生此資料庫進入緊急模式


Step 06: 執行 sp_dboption 指令，把資料庫设为Single User模式；
sp_dboption 'ATest', 'single user', 'true'

Step 07: 執行 DBCC CHECKDB
DBCC CHECKDB('ATest')


Step 08: 執行 DBCC REBUILD_LOG , 重建立 LDF 檔, 此時可以看到資料庫內的資料表了
DBCC REBUILD_LOG('ATest','D:\DB\ATest_Log.LDF')




Step 09: 執行下列指令, 将資料庫回覆正常狀態
update sysdatabases set status=0 where name='ATest'
或者
sp_resetstatus "ATest"
go
sp_configure 'allow updates', 0
reconfigure with override


Step 10: 執行 sp_dboption 指令, 將資料庫設回多人模式:
sp_dboption 'ATest','dbo use only','false'

Step 11: 重啟 MSSQL Server 2000 即可

MSSQL Server 2012 Restore 備份還原概念：

MSSQL Server 2012 Restore 在還原的過程中，有三個階段 (Phase)：

1. Data Copy：建立實體檔案，與回存資料

2. Redo：從 LOG File 回存所有已經確認 ( commit ) 的 Transaction

3. Undo：回覆 ( Roll Back ) 在還原時間點時未確認的 Transaction (交易)

至於 MSSQL Server 2012 Restore 還原概念，大致是以下列 4 個步驟，不過這 4 個步驟應該也是大部份資料庫系統採用的方法，像 Oracle DB 的 RMAN 也是如此。

• 1. 先嚐試執行 Tail-log 備份
• 2. 回存最近一次全備份
• 3. 回存最近一次的差異備份
• 4. 回存所有備份 LOG Fiile 裡的 Transaction ，再回存第一步驟所備份 tail-log 中的 Transaction

備份還原實作

首先建立一個目錄 C:\backups 作為備份的目錄，接下來的步驟則是模擬資料庫 Northwind 的檔案毁損時，資料庫重建的完整過程

--1.建立備份裝置 mybackup(backup device)
指令：
EXEC master.dbo.sp_addumpdevice  @devtype = N'disk', @logicalname = N'mybackup', @physicalname = N'C:\backups\mybackup.bak'

--2.檢查 Northwind資料庫是否為 Full Recovery mode
 

--3.執行完整資料庫備份

指令：
BACKUP DATABASE [Northwind] TO  [mybackup] WITH FORMAT, INIT,  NAME = N'Northwind-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

--4.建立資料表(log中有此資料表)

指令：
use Northwind
go
create table table1 (a int , b int)
go

--5.backup log(log備份且 table1被清除)
指令：
BACKUP LOG [Northwind] TO  [mybackup] WITH NOFORMAT, NOINIT,  NAME = N'Northwind-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

--6.新增資料(log中有 1 1 )
指令：
insert table1 values (1,1)

--7.backup log(log備份且 1 1被清除)
指令：
BACKUP LOG [Northwind] TO  [mybackup] WITH NOFORMAT, NOINIT,  NAME = N'Northwind-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

--8.新增資料(log中有 2 2 )
指令：
insert table1 values (2,2)

--破壞資料庫

1.stop sql service
2.rename C:\TSQL2K8DB\Northwind.mdf --> C:\TSQL2K8DB\Northwind.xxx
3.copy C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA 到上層目錄
4.start sql service
5.sql log查詢壞掉的檔案
6.檢查資料庫狀態，這時資料庫的狀態會變成 Recovery Pending

--7.tail log backup
a.device右鍵-->database-->
Database:Northwind
backup type:log
option-->backup the tail of ....

因為資料庫：Northwind 已被破壞，所以只能從 Backup Devices：mybackup 作 Tail-log 備份，這也是在 MSSQL Server 2012 Backup 備份 中建議用 Backup Devices 的好處之一。

指令：
BACKUP LOG [Northwind] TO  [mybackup] WITH  NO_TRUNCATE , NOFORMAT, NOINIT,  NAME = N'Northwind-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 10

整個備份完的記錄可以 Device 的 Media Contents 中查詢

 
b.檢查資料庫狀態，因為資料庫：Northwind 的資料檔名已被修改成 Northwind.mdf --> Northwind.xxx，重啟 MSSQL Server 伺服器後，MSSQL Server 檢查有問題，會將 Northwind 的狀態改為 Recovery Pending

 

c.選 Northwind,右鍵-->task-->restore-->database
指令：
RESTORE DATABASE [Northwind] FROM  DISK = N'C:\backups\mybackup.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE LOG [Northwind] FROM  DISK = N'C:\backups\mybackup.bak' WITH  FILE = 2,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE LOG [Northwind] FROM  DISK = N'C:\backups\mybackup.bak' WITH  FILE = 3,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE LOG [Northwind] FROM  DISK = N'C:\backups\mybackup.bak' WITH  FILE = 4,  NOUNLOAD,  STATS = 10
GO

若是用 SQL Server Management Studio 回存也是很方便操作
 

MSSQL Server 2012 Transaction Concept 概念：

MSSQL Server 2012 在資料寫的的運作上，是秉持著 WAL(Write Ahead Logging) 的機制，Transaction 存取磁碟的作業，主要是依照下列 4 個步驟來作業：

• 1. 將 Transaction 寫入 LOG File
• 2. 將 Transaction 的異動修改 Buffer (記憶體) 裡的資料
• 3. 交易完成 Transaction Commit
• 4. 將 Buffer (記憶體) 裡的資料寫入 Disk ( Data Files )裡

Recovery Model：

在說明 MSSQL Server 2012 的備份模式前，要先瞭解它的 Recovery Model，MSSQL Server 2012 的 Recovery Model 有三種模式：

1. Simple：當 Transaction Commit 交易確認後，此 Transaction Log 就會從 LOG File 中清除(Truncate)，所以 LOG File 並不會長大，LOG File 的檔案大小永遠保持最小。

2. Full：所有的 Transaction 交易都會保留在 LOG File 中，所以 LOG File 會一直長大，除了作備份作業後，LOG File 裡的 Transaction 內容才會被清除(Truncate)，當然 LOG File 檔案要縮小，還得手動執行 Shrink Log 才行，如下圖示

3. Bulk Logged：當有 Bulk Insert 的 SQL 語法時，這些 批次 的 Transaction 不會記錄在 LOG File 中。

Backup Type：

在 MSSQL Server 2012 的備份模式有七種模式，

• 1. Full：所有的 Data files 及 Transaction Log 都備份
• 2. Differential：只備份差異部份
• 3. Partial：主要的 Primary Filegroup，與指定的 Filegroup
• 4. Transaction Log：只備份 Log File 中的 Transaction，回存時需搭配 Full 備份的檔案
• 5. Tail-log Backup：只備份尚存在 LOG File 中的 Transaction，如 15:00 執行了 Transaction Log 備份，下一次的備份時間在 16:00 ，但 SQL Server 在 15:30 掛了，此時尚存在 LOG File 中的 Transaction 就是 Tail-log。
• 6. File/File Group：指定的 File or File Group 備份
• 7. Copy Only：這個模式主要是將現行運行的資料庫線上烤貝 Copy 一份到異地端存放用的，這個作業並不會影響 LSN 的序列號。

備份實作：

一般簡單全備的的指令為

BACKUP DATABASE { database_name | @database_name_var }   TO <backup_device> [ ,...n ]   [ <MIRROR TO clause> ] [ next-mirror-to ]   [ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ] [;]

如：
BACKUP DATABASE Northwind
TO DISK ='C:\SQLDB_DATA\back\Northwind.bck'

 但這樣的作法，並不是很好的方式，建議應該建立一個 Backup Device 來作備份的目標物，在 Backup Device 再設定實際的磁碟或磁帶，這樣在未來儲存目標物 (destination) 有變更時，只要修改此 Backup Device 的儲存目標物 (destination) 即可。

建立備份裝置 mybackup(backup device)，指令如：

EXEC master.dbo.sp_addumpdevice  @devtype = N'disk', @logicalname = N'mybackup', @physicalname = N'C:\backups\mybackup.bak'

也可以用 SQL Server Management Studio 來建立

當電腦主機名變更時，如：SA01 變更為 VS21，MSSQL Server 2012 也要作相對應的更名作業，不然在執行某些作業時會有問題，變更的操作如下：

Step 01：change to SQLCMD MODE

Step 02: Drop 現行的 Instance：SA01

先執行 select @@SERVERNAME 查詢現在的 Instance 是什麼，此時應為 SA01；確定後，再執行下列指令

sp_dropserver @@servername
go

Step 03：增加 VS21 的 Instance

sp_addserver 'vs21',local

Step03：重啟 SQL Server 服務

!!net stop mssqlserver
!!net start mssqlserver

要注意的事：MSSQL Server 2012 預設的 Default Instance Name 為 mssqlserver，若您有第二個以上的 Instance，如 I2 ，則應為主機名\I2，以此範例而言，應是VS21\I2。

Struts2 的類型轉換方法有分成區域性及全域性兩種，以實例來說明這兩種的差別與設定方法。

實作的過程大致是這樣的：客戶端建立一個 X,Y 字串的輸入input.jsp ，然後經過 struts2 的類型轉換後，再顯示於客戶端 pointConverterResult.jsp

Step 01：建立 struts.xml 檔，內容為

Step 02：建立客戶端的輸入劃面 input.jsp & 處理後的的顥示劃面 pointConverterResult.jsp

input.jsp

pointConverterResult.jsp

Step 03：建立 class：Point 於 package: com.test 下，這個 class 很簡單，只是為了設定/取得 X,Y 軸的值

Step 04：建立一個自定類型轉換的 class: PointConverter 於 package: com.test 下，因為 struts2 的 JAR 檔內有一個 ognl-x.x.x.jar，此 JAR 包含了一個 class：TypeConverter，這個 class 預設有一個 DefaultTypeConverter method，所以可以利用這個 method 來改寫我們自已想要的類型轉換，文檔可以參考：http://commons.apache.org/proper/commons-ognl/apidocs/index.html

Step 05： 依 struts.xml 裡指示，建立一個 Action class：PointConverterAction

Step 06：在類型轉換有分成區域性及全域性，若是區域性：則在 Action class：PointConverterAction 相同的 package: com.test 下需建立一個檔案 PointConverterAction-conversion.properties (-conversion.properties 是固定的，此檔用來指示 struts2 那一個欄位，[ 如:input.jsp 中的 point 欄位] 在作 setPoint()/getPoint() 前，要先使用此自定義類型 [如: com.test.PointConverter ] 作轉換 )；若是全域性：則在 struts.xml 檔案相同目錄下需建立一個檔案：xwork-conversion.properties 來指示 struts2 那一種類型 [如：com.test.Point] 均使用那一個 class [如：com.test.PointConverter ]來作類型轉換。

PointConverterAction-conversion.properties 的內容如下：

xwork-conversion.properties 的內容如下：

Struts 2 的開發環境設定，所需最低的要求為：

1. Servlet API 2.4
2. JSP API 2.0
3. Java 5

可以在 http://struts.apache.org/downloads.html 此下載所需的 Struts 2 所有開發套件，在此使用 Struts 2.3.15.2 ("best available")。

在此建議的 IDE 介面為 Eclipse IDE for Java and Report Developers，下載後解壓於 D:\Progs\eclipse ； WEB Server 可以使用 Apache Tomcat v7.0.42 ，下載後解壓於C:\AppServ\apache-tomcat-7.0.42 ； Java JDK 1.6 或 1.7 版，下載後執行安裝。接下來就直接執行 D:\Progs\eclipse\eclipse.exe 即可啟動 Eclipse IDE 開發環境。

Step 1: 首先要在 Eclipse IDE 開發環境建立一個專案 struts2_test：

整個專案的程式擺放配置如下圖：

Step 2: 在此專案的 WEB-INF 下建立一個 web.xml 來定義此專案起動時的執行方式

Step 3: 於Java Source 下建立 Struts 2 Action 的規則檔: struts.xml

注意，可以使用Struts 2.3.15.2 中的 struts2-blank 的專案來作初始設定。這裡建立一個 test 的 package 測試，所以要將 test.xml 檔案含括進來。test.xml 的檔案內容如下：

 Step 3：將所需 Struts2 的 JAR 檔放在 WEB-INF/lib 目錄下：

Step 4: 先建立一個顯示 "Hi Hello Struts 2!!"  的 Hello.jsp 頁面，其中"Hello Struts 2!!"為要取得的message。

  Step 5: Hello.jsp 頁面裡要取得 message 的變數，在 test.xml 控制檔中已宣告對應的 class="com.test.Hello" ，所以要建立取得此 message 的方法：getMessage()，檔案為 Hello.java ，package 為 com.test

注意：struts2 預設回應的方法為 execute() ，只要填入 return "success" 即可在 Apache Tomcate 中執行 http://localhost:8080/struts2_test/test/Hello 。

建立一個使用者登入作業範例：

step 1：建立 userLogin2.jsp

還記得在 test.xml 中我們有一個設定<result name="input">/test/userLogin2.jsp</result>  => input 是 struts2 的預設值，即是 http://localhost:8080/struts2_test/test/UserLogin_input 會指向 /test/userLogin2.jsp 這個檔案，內容如下：

 Step 2：建立對應的 ActionForm class="com.test.UserLoginAction"

 Step 3：對輸入欄位加入判斷

在 Struts2 要對欄位加入偵錯判斷的方法有二個，一個是覆寫 validate() 的方法，在此方法裡加入  this.addFieldError("loginName", "Login Name was required"); ，第二種方法為建立一個檔案為 程式名-validation.xml 的檔案，如： UserLoginAction-validation.xml，內容如下：

Step 4：建立多國語言判斷

Struts2 是可以支援多國語言的，但您必需使用 struts-tags 且在輸入欄位的 From 裡使用 key 值，如  <s:textfield key="loginName" /> ，多國語言的檔名為 package.properties ，預設是 US 。內容如下：

 PS: Eclipse 的多國語言的編輯器可以從 http://sourceforge.net/projects/eclipse-rbe/ 下載 ResourceBundleEditor_v0.8.0.zip ，解壓後放在 D:\Progs\eclipse\plugins 目錄下，重新起動 Eclipse 就可以了。

 參考：

http://struts.apache.org/release/2.2.x/docs/message-resource-files.html

用 PL/SQL 撰寫 HELLO PL/SQL 的方法有下列幾種:

1. Anonymous Blocks
2. Function
3. Procedure
4. Package

註：所有建立的 Table、Index、Sequence、Function、Procedure、Package...等，都可以在 VIEW：USER_OBJECTS 中的 status 欄位查詢得到它的狀態。

Anonymous Blocks 的程式範例：

SET SERVEROUTPUT ON -- 資料輸出顯示開啟
-- Anonymous Blocks
DECLARE
  V_HELLO VARCHAR2(20);
BEGIN
  V_HELLO := 'HELLO PL/SQL';
  DBMS_OUTPUT.PUT_LINE(V_HELLO);
END ;
-- 執行
/ 

顯示出的結果

Function 的程式範例：

CREATE OR REPLACE FUNCTION FUNC_HELLO( F_NAME VARCHAR2:=NULL )
RETURN VARCHAR2
IS
  V_TEMP VARCHAR2(20);
BEGIN
  V_TEMP := 'HELLO ';
  RETURN V_TEMP || F_NAME;
END;
-- 執行 Compile
/ 

執行 Function 帶參數

SELECT FUNC_HELLO('PL/SQL') FROM DUAL;
-- 執行
/ 

結果

執行 Function 不帶參數

SELECT FUNC_HELLO FROM DUAL;
-- 執行
/ 

結果

Procedure 的程式範例：

CREATE OR REPLACE PROCEDURE PROC_HELLO IS
  V_HELLO VARCHAR2(20);
BEGIN
  V_HELLO := 'HELLO PL/SQL';
  DBMS_OUTPUT.PUT_LINE(V_HELLO);
END ;
-- 執行 Compile
/ 

執行 Procedure

SET SERVEROUTPUT ON
EXECUTE PROC_HELLO
-- 執行
/ 

結果

 Package 的程式範例：

-- 建立 Package 規格
CREATE OR REPLACE PACKAGE PACK_HELLO AS
  PROCEDURE PROC_HELLO(P_NAME VARCHAR2:=NULL) ;
  FUNCTION FUNC_HELLO (F_NAME VARCHAR2:=NULL) RETURN VARCHAR2;
END;
/

-- 建立 Package 主體
CREATE OR REPLACE PACKAGE BODY PACK_HELLO AS
  PROCEDURE PROC_HELLO(P_NAME VARCHAR2:=NULL) IS
  BEGIN
   DBMS_OUTPUT.PUT_LINE('HELLO '||P_NAME );    
  END ;
  
  FUNCTION FUNC_HELLO ( F_NAME VARCHAR2:=NULL) RETURN VARCHAR2 IS
    V_HELLO VARCHAR2(20);
  BEGIN
    V_HELLO:='HELLO ';
    RETURN V_HELLO || F_NAME; 
  END ;
END;
/

執行 Package.Procedure:PACK_HELLO.PROC_HELLO

結果

執行 Package.Function:PACK_HELLO.FUNC_HELLO

結果

彈性欄位分成兩種：Key flexfields 與 Descriptive flexfield；Key flexfield 出現在 Form 的一般文字類型的輸入型態

Key flexfield 出現在 Form 的一般文字輸入型態欄位，Descriptive flexfield 則會在 Form 上出現兩個字元寬的方形標示 [ ] 來代表，由下列的圖示可以得知此兩種 Flexfield 的不同。當點擊此 文字欄位 或 [ ] 時，系統會彈出一個視窗，內含一個 From ，藉此來讓使用者輸入資料。Key flexfield 使用的時機，一般是用來儲存其它模組要的值 Code ，例如：組織代碼： Org ID，會計科目： Accounting Code，這些 Code 若有多個截段( Segment ) ，可以組合在一起，就成為一串連續的資料，可稱為 combination。如下圖中 Part 欄位的值：COM-876-LTN。

Descriptive flexfield 使用的時機，則是在既有的 Form 上擴展欄位空間(expansion space) ，它可以依照 Form 上欄位內的值，彈出相對應符合的資料格式。例如：在填寫固定資產時，若類別欄位的值為：電腦類，彈出的 Form 可以是CPU，RAM，HD相關的欄位；若為辦公室桌子類，彈出的 Form 則可以為：Style，Size，Type。

　　至於 Segment，Value，Value Description，Value Set 與 Flexfields 的關係，可以從下圖中瞭解。