[轉載][SQL Server][PowerShell]dbatools - best practices and instance migration module

　　最近發現一個給SQL Server DBA用的Free PowerShell tools，目前有超過150個命令可用，尤其是有很多Migration Commands，真是非常方便啊

　　有興趣的自己試試，網址如下
dbatools - best practices and instance migration module

sp_whoisactive有新版本

原來Adam大神持續有在更新啊
Version 11.17 - October 18, 2016 (Box versions 2005-2016 only. NOT for Azure.)

http://whoisactive.com/

[SSIS][PostgreSQL]Error code:0x80040E21 Description: Multiple-step OLE DB operation generated errors.

        在SSIS Project裡，我打算從PostgreSQL匯入資料到SQL Server上，然後執行時出現如下錯誤，從錯誤訊息裡得知好像是欄位型態轉換的問題

[OLE DB Destination [29]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E21
Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

[OLE DB Destination [29]] Error: An error occurred while setting up a binding for the "name" column. The binding status was "DT_NTEXT".
The data flow column type is "DBBINDSTATUS_UNSUPPORTEDCONVERSION".
The conversion from the OLE DB type of "DBTYPE_IUNKNOWN" to the destination column type of "DBTYPE_WVARCHAR" might not be supported by this provider.

        PostgreSQL該欄位上是TEXT，而SQL端對應欄位是NVARCHAR，我中間沒有加入資料轉換元件，對於異質資料轉換，我習慣在來源端就先轉掉好了，比較不容易出問題，所以直接在PostgreSQL來源端用cast轉換就好，例子如下

select id,cast(name as varchar(32)) as name from accounts

         這樣就沒再出錯囉

[SQL Server]Linked Server到Oracle時，出現'To_Date' is not a recognized built-in function name的解法

       用連結伺服器連到Oracle要取資料，因為有日期時間的過濾條件，在Oracle需要用To_Date函數，但此函數無法通過SQL Server語法剖析的檢查，會出如下的錯誤

Select * From LK_ORA..HR.EMPLOYEES Where  Login_Date >= To_Date('2015-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss') And Login_Date < TO_DATE('2015-05-02 00:00:00','yyyy-mm-dd hh24:mi:ss');

       解法一：在Oracle建View，在SQL端直接讀View，但多了道建View的手續

       解法二：在SQL端改用Exec，比較簡單，範例如下

EXEC ('Select * From HR.EMPLOYEES Where  Login_Date >= To_Date(''2015-05-01 00:00:00'',''yyyy-mm-dd hh24:mi:ss'') And Login_Date < TO_DATE(''2015-05-02 00:00:00'',''yyyy-mm-dd hh24:mi:ss'')') at LK_ORA;

[推薦][SQL Server]sp_who加強版系列

        sp_who或sp_who2系列一直以來不斷的有高人加強它衍生出很多版本，這邊紀錄一下看到覺得不錯用的，給不知道的人參考

• Who is Active v11.11 這個我用最久了，很好用，但沒有對應SQL 2012之後的版本

• sp_AskBrent 這個含有whoisactive的功能，再加上job、 wait statistics及Perfmon counters的監控，也很不錯

• sp_whopro 這個最近看到的，對應到最新SQL 2014版本，但還沒試，感覺像whoisactive囉

[SQL Server][Oracle][MySQL][PostgreSQL] IN條件式寫法的差異

        一般找某個資料表的資料是否存在另一個資料表中，就語法上來說，可以用INNER JOIN，若條件式僅JOIN單一欄位，可以改成用IN或EXISTS；若是JOIN多個欄位，就無法用IN囉，這是在SQL Server的情況

        無意間看到在Oracle中，IN條件式的寫法裡竟然可以接受多個欄位呢，另外試了MySQL與PostgreSQL也都可以喔

[轉貼]免費資料庫學習影片

        有一些免費資料庫教學影片喔，請造訪51CTO學院吧，不只SQL Server還有Oracle與MySQL呢！

[T-SQL]PIVOT兩欄甚至多欄的方法

        最近遇到一個報表的特殊需求，要將兩個欄位的列轉成欄(PIVOT)，一般頂多處理一欄吧，這次要處理兩欄，而且轉置後，將會多出五十幾個欄位喔...@@

        基本上可以用CASE處理；也可以用PIVOT，雖然BOL上沒提到PIVOT可以多欄，實際上可以用多個PIVOT來做，但最後還要GROUP BY再SUM起來有點麻煩；也可以分別對兩個欄位各自PIVOT後，再JOIN起來，一樣可達到目的

        我在想說有沒有更好的方法，結果在網路上看到有人用PIVOT把多欄當一欄來做，超簡單的，我想都沒想到可以這樣用呢，就是先將多欄UNPIVOT成一欄，再PIVOT就OK啦

[DBA天團爭霸戰]快去參加

網址在此---> [DBA天團爭霸戰]

台灣微軟舉辦兩屆的SQL HERO後，終於又辦了SQL Server的競賽囉
你夠TOP的話可以一人參加競賽，也可以三人組團報名
這次獎品有證書耶，連藍袍級的都有
前兩屆的SQL HERO好像都沒有證書喔
趕快去報名吧
反正藍袍級的可以無限制挑戰
記得7/31前通過線上考試即可

[Documenting]結合Table Layout與Value

        現有個需求是製作Table Layout的文件，有個比較特別的是還需要列出一筆對應的欄位值，產生Table Layout很簡單，列出一筆資料也很簡單，結合的話可能得用到Execl，把那一筆資料轉置，我想把這幾個步驟自動化，之後如果要製作所有的資料表時就會方便很囉了

面對SQL Injection，DBA可以做甚麼呢！

        難得公司請廠商做滲透測試，模擬駭客攻擊，我很好奇廠商會怎麼測試，所以同時間我也監控網站與資料庫的LOG，看看能從LOG裡讓我學到甚麼，當然最基本的SQL Injection與XSS一定會測試，身為DBA我當然最在意SQL Injection囉，而針對SQL Injection，我原本就做了以下前三項的設定

1. 應用程式帳號權限最小化
2. 限制中繼資料的可見性
3. 稽核軌跡 
4. 資料表(行)命名的考量

原來定序也會影響轉換函數啊！

        CAST 和 CONVERT (Transact-SQL)裡面明明提到nvarchar可以轉varchar，但我不知為什中文字卻全轉成問號，後來再另外一台SQL Server上試，結果卻正常！

        後來比較了兩邊差異，原來是定序不同造成的，以下寫了個簡單例子供各位參考（用Cast也可）

declare @var nvarchar(32)

set @var = N'傳說中的6頭牛-牛牪犇'

select @var 'nvarchar',

   convert(varchar(32),@var) COLLATE SQL_Latin1_General_CP1_CI_AS '轉成varchar(Latin定序)',

   convert(varchar(32),@var) COLLATE Chinese_Taiwan_Stroke_CI_AS '轉成varchar(Chinese定序)'

        基本上，nvarchar轉varchar，英數字不會有問題，但中文裡若是有Unicode字元的會轉成？喔


        至於我為什會需要將nvarchar轉varchar呢！因為在作SQL與DB2的轉換，DB2那邊都是varchar的囉！

利用IPSec建立內部防火牆以提升資料庫安全3

        原本管的DB與AP Server都是Windows作業系統，要啟用IPSec還算容易，但是因為某個需求，得讓核心系統的AIX也能連SQL Server，問題是我要啟用IPSec啊！我原是使用交涉的方式，需要在Server端與Client端皆設定對應的IPSec才行，可是AIX上怎麼設！短時間要我們管AIX的同仁測試IPSec有點困難，只好想想有沒有只要在Server端設定IPSec的方法？後來發現除了用交涉外，還可以用允許，用允許就可以解決我的問題啦，允許只要在Server端設定即可，超方便

連結伺服器"linked server"的OLE DB 提供者"IBMDADB2.DB2COPY1" 報告了錯誤。拒絕存取。

        當我建立好DB2的連結伺服器後，我試著使用SELECT語法去查詢連結伺服器的資料，結果出現如下錯誤

        訊息7399，層級16，狀態1，行1
        連結伺服器"linked server"的OLE DB 提供者"IBMDADB2.DB2COPY1" 報告了錯誤。拒絕存取。
        訊息7350，層級16，狀態2，行1
        無法從連結伺服器"DB2TEST" 的OLE DB 提供者"IBMDADB2.DB2COPY1" 取得資料行資訊。

使用IBM DB2 ODBC DRIVER，來建立DB2的連結伺服器

        如果想利用SQL Server來連結DB2，強烈不建議使用Microsoft OLE DB Provider for DB2喔！

        因為光是搞HostCCSID與PC code page這兩個編碼屬性我就快瘋了，弄了兩天我還是搞不定，不是中文顯示問號，就是變亂碼，再不然就是出錯，從1.0試到3.0，中英文版都試了也一樣

         後來使用Microsoft OLE DB Provider for DB2的資料存取工具去試試看到底支援哪些編碼，明明有950的啊，但偏偏設了就會出錯

錯誤一
         無法連接至資料來源 'DB2TEST':
        發生內部網路程式庫錯誤。要求命令包含了目標系統無法辨識，或不支援的參數值。         SQLSTATE: HY000, SQLCODE: -385
錯誤二
        無法連接至資料來源 'DB2TEST':
        處理命令時，發生一或多個錯誤。

        就在我要放棄的時候，我改用IBM DB2 ODBC DRIVER看看，結果上面那兩個屬性我根本不用設就OK了，中文顯示都很正常喔，真是白白浪費我兩天時間啊

[T-SQL]分組排名(SUBSQERY、CTE及用TOP))

        如何從銷售訂單資訊[SalesOrderHeader]資料表中，找出每個顧客[CustomerID]其應付總額[TotalDue]前三高的訂單[SalesOrderID]，並顯示名次？呈現結果如下

        注意應付總額一樣的也要列喔

利用IPSec建立內部防火牆以提升資料庫安全2

        上一篇介紹IPSECCMD的使用範例，本篇介紹可以用在Windows Server 2008上的Netsh IPsec命令吧，詳細使用方法請參見Netsh Commands for Internet Protocol Security (IPsec)
 
        Netsh IPsec比起IPSECCMD更好的優點有以下兩點（缺點是指令比較多行）：

1. IPSECCMD對篩選器清單的篩選器只會整個取代，而Netsh IPsec對篩選器清單的篩選器是可以一筆一筆附加的
2. IPSECCMD對原則的每次變更都要先停用再啟用，變更的部分才會生效，而Netsh IPsec只要是對已經指派的原則所作的變更都是立刻生效 

       對了，忘了提使用IPsec不僅可當內部防火牆，還可順便對網路連線作加密！像你如果用Wireshark這類的封包分析軟體來擷取網路封包，未加密前是可以從封包中查出使用的T-SQL指令，但如果用IPsec加密後是看不出來的喔！

如何取出某段期間內的每個周日？

         MSDN上有網友在問，已經有高手利用CTE去解囉，小弟剛好在練習水平思考的技巧，在此提供另一種解法，就是利用SPT_VALUES提取列表去解

        小弟認為使用SPT_VALUES比CTE更直覺的去解這個問題，效能也許第一次不比CTE快，但第二次將SPT_VALUES載入記憶體後，就不輸CTE囉，而且重點是此方法從SQL 2000到SQL 2012 (RC0)都適用

[查詢優化]影響執行計畫的因素4-Selectivity(選擇性)

        選擇性是一種獨特性的衡量，大多用來描述述詞，如果要計算的話就是符合資料列/總資料列的比率

        假設有個員工資料表，總共100位員工，男女各半，資料表上有ID(身分證或護照)及性別欄位，如果述詞為" ID= '某個員工ID' "，可以預期只會回傳一個員工，選擇性為1/100=0.01，表示有較高的獨特性，所以ID欄位有高選擇性，如果述詞為" 性別 ＝ '男生' "，可以預期將會回傳50位員工，選擇性為50/100=0.5，有較低的獨特性，所以性別欄位的是低選擇性

        大資料裡找小資料，使用索引是很有效率的，所以通常述詞裡有高選擇性欄位，就很適合拿來做選索引欄位囉，像前述所提的ID欄位，很適合拿來做索引欄位，性別欄位就較不適合囉

利用IPSec建立內部防火牆以提升資料庫安全

　　一般公司都會有外部防火牆來防止駭客直接入侵，那內部防火牆要防誰呢？當然是來防內賊的，一般無內部防火牆的情況下，只要知道資料庫的ＩＰ、帳號及密碼，在內網就能夠去取得資料庫的資料，如果使用IPSec來建立內部防火牆，就能設定IP白名單，僅允許白名單上的伺服器可連接資料庫，這樣就算內部無權的人取得資料庫的帳號密碼也沒法取得資料，除非他有辦法透過那幾台在白名單裡的伺服器去連囉，這樣不就可以提升資料庫的安全性嗎！最近個資法的施行細則也快公布了，有免錢可以提升安全性的做法，是不是該考慮一下

　　IPSec的說明、安裝及使用方式我就不介紹了，網路上一堆參考資料，自己去搜尋吧！

　　我直接給設定指令IPSECCMD的範例，IPSECCMD是給XP及Win server2003用的，Win Server2000則是用IPSECPOL囉，參數基本上差不多，都需要另外安裝