串接授權與拒絕

簡單範例:

use tempdb
create table t(c1 int)
create user u without login
create user u2 without login
grant select on t to u with grant option
exec(‘grant select on t to u2 as u’) as user=’u’
exec(‘select * from t’) as user=’u2′
deny select on t to u cascade
/*
Msg 229, Level 14, State 5, Line 18
結構描述 ‘dbo’,資料庫 ‘tempdb’,物件 ‘t’ 沒有 SELECT 權限。
*/
exec(‘select * from t’) as user=’u2′

drop table t
drop user u
drop user u2

透過 SSMS 可以看到授予者

image

不到一個星期,IT 團隊能提供什麼保證

收到我個人任職顧問的公司同仁夜裡發的訊息:"服務使用商將有活動,要我們保證系統品質"。但什麼是品質,又如何保證?

要保證品質,需要及早準備,不同的品質定義,準備的成本不同,這包含購置軟/硬體與人力,且需要訂合約時載明,如何量化目標與罰則,否則只是口語而已。

而我們提供的服務部分委外,委外商擁有 Domain know how,但對技術掌控能力有限,維護系統品質的意願也不高,這個從客戶端的品質規範要合約化地貫徹到委外商,更需要多方折衝。

然而,整體品質上的驗證有一定的困難度,因為這是多個服務商上中下游地整合服務,就當下的情境,有四個主要系統團隊各屬不同公司完成一個及時商業流程,還不包括數個 IDC 機房的 IT 團隊以及非即時的線下服務團隊,涵蓋 7 個以上的大小公司。

我們最常做的壓力測試在當下只能分段進行,但因為分段,很難有高的涵蓋率,且要偕同 IDC 的朋友一起完成,自主性受限。而我們僅能了解在自己這一段的壓力,模擬來源端並隔絕對目的下游服務商的呼叫,未能從最上游的來源需求一次壓到最下游的服務供應者。只有靠日常監控分析。

在整個流程的監控也有問題,我們很難跨越到對方去監控,基本上也不可能。因為一但幫別人監控,那責任就落到自己身上,跨團隊、部門都有障礙,何況跨公司。但監控與應變能力就在供應鏈內受損,尤其服務供應商力有未逮時,重傷整個供應鏈。換句話說,即時線上服務對供應鏈的挑戰更甚以往。

 

 

現今,不到一個星期,除了增加設備,排人力到時全天監控多個效能瓶頸點外,能做的不多。然而,突然的活動之特性我們不清楚,尖離峰時間不明,要 7*24 緊繃待命並非當下團隊的人力規劃。其實,很難談未量化與質化的口頭保證…

透過 PowerShell 讀 MSMQ,以及在資料庫內,跨資料表與欄位找尋是否包含某個字串

朋友的需求:

$q= new-object System.Messaging.MessageQueue ‘機器名稱\private$\erptowf’
$msg=$q.Peek()

$s=new-object System.IO.StreamReader $msg.BodyStream

$s.ReadToEnd()

 

declare cur cursor
for select schema_name(o.schema_id) schName, object_name(c.object_id) tbName,c.name from sys.columns c
join sys.objects o on c.object_id=o.object_id
where TYPE_NAME(user_type_id) in (‘varchar’,’nvarchar’,’char’,’nchar’) and o.type=’u’ and o.is_ms_shipped=0

declare @sch sysname,@tb sysname,@col sysname
create table t(schemaname sysname,tablename sysname,columnname sysname)
open cur
fetch cur into @sch,@tb,@col
declare @sql nvarchar(max), @search nvarchar(100)=’58.’
while @@FETCH_STATUS =0
begin
    set @sql=’if exists(select * from [‘ + @sch + ‘].[‘ + @tb + ‘] where [‘ + @col + ‘] like “%’ + @search + ‘%") insert t values(“‘ +@sch + “‘,"‘ + @tb + “‘,"‘ + @col + “‘)’
    –print @sql
    exec(@sql)
    fetch cur into @sch,@tb,@col
end
close cur
deallocate cur
select * from t
drop table t

SQL Server 2016 的交易記錄傳送可能需要手動安裝 .NET Framework 3.5

交易記錄傳送實際上是透過各SQL Server執行個體上的作業呼叫「sqllogship.exe」應用程式,需要小心的是在安裝SQL Server 2016時並未要求一定需要.NET Framework 3.5,而sqllogship.exe工具程式需要.NET Framework 3.5,導致執行失敗卻未將失敗原因寫入作業記錄。

透過命令提示列呼叫sqllogship.exe才會出現缺少.NET Framework 3.5的錯誤訊息。

今日推出的 SQL Server Data Tools 16.3,對 Tabular Model 的 Explorer 終於讓人可以一目了然

image

定義 AD Domain 是單一字母的,不能在 Windows Server 2012 R2 Server 建 iSCSI target

天啊,我自己在家裡的 AD Domain 叫 i,竟然導致奇怪的 Bug,會有如下的錯誤訊息:

Unable to create iSCSI virtual disk

要修 bug 都還不一定裝得上 hotfix…

https://support.microsoft.com/en-us/kb/2953557

最後是建立一台不加入 Domain 的 Windows Server 2012 R2 當作 iSCSI target…

指定應用程式只用某幾顆 CPU

今天 SharePoint 莫名掛掉,然後發現是更新時多 CPU 彼此造成更新錯誤,照以下的說明

https://blogs.msdn.microsoft.com/spses/2015/05/12/sharepoint-2010-2013-an-exception-of-type-microsoft-sharepoint-administration-spupdatedconcurrencyexception-was-thrown-while-installing-an-update/

透過 PowerShell 語法解掉

$cmd="start "+""""" /affinity 1 "+"""C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\BIN\Psconfig.exe""" + " -cmd" +  " upgrade" + " -inplace" + " b2b" + " -wait"
cmd.exe /c $cmd

但意外發現 start 可以指定 cpu affinity,這比先前的問題還有趣,可以參照以下的 blog

https://blogs.msdn.microsoft.com/santhoshonline/2011/11/24/how-to-launch-a-process-with-cpu-affinity-set/

image

可以透過 Process Explorer

image

看到 CPU Affinity 真的是 2^0 + 2^4 = 11 (16進位)

image

先用 SSMS 2016 英文版為 SQL Server 2016 中文版設定 AG

到目前為止 SSMS 2016(2016 年 7 或 8 月號,網路 download 寫的是 7 月號,但安裝完程式 show 的是 8 月號)中文版建置 AG 的精靈有 Bug,但可以用英文版 SSMS 幫中文版 SQL Server 建置與設定。

查詢某個服務聽的 Port

get-process | where name -like msmd* | select name,id |ft –AutoSize

netstat -ao -p tcp | where {$_ -like ‘*上述的 id*’}

當過濾欄位是 char/varchar 時,透過 SqlCommand 搭配 SqlParameter 時,大量存取會有效能考量

最近幫朋友壓測時,發現的問題。

依據 SQL Server 的 Data Type Precedence https://msdn.microsoft.com/en-us/library/ms190309.aspx

  1. user-defined data types (highest)

  2. sql_varian t

  3. xml

  4. datetimeoffset

  5. datetime2

  6. datetime

  7. smalldatetime

  8. date

  9. time

  10. float

  11. real

  12. decimal

  13. money

  14. smallmoney

  15. bigint

  16. int

  17. smallint

  18. tinyint

  19. bit

  20. ntext

  21. text

  22. image

  23. timestamp

  24. uniqueidentifier

  25. nvarchar (including nvarchar(max) )

  26. nchar

  27. varchar (including varchar(max) )

  28. char

  29. varbinary (including varbinary(max) )

  30. binary (lowest)

代表輸入的條件參數若是 unicode,而目標欄位是 char/varchr/text,將會是欄位內所有的紀錄值先轉型成 unicode,再跟輸入條件比較。

範例程式碼:

use tempdb

drop table if exists t1

create table t1(c1 int identity primary key,c2 char(6),c3 char(10),c4 varchar(50),c5 datetime2(7) default(sysdatetime()),
c6 uniqueidentifier default(newsequentialid()),c7 bigint default(0))

set nocount on

declare @i int=0
while @i<100000
begin
    insert t1(c2,c3,c4) values(right(‘000000’+convert(varchar(6),@i),6),
    right(‘0000000000’+convert(varchar(6),@i),10),rand())
    set @i+=1
end

select * from t1

create index idx on t1(c2,c3,c4)
go
create proc sp @c2 char(6),@c3 char(10),@c4 varchar(50)
as
    select c1 from t1 where c2=@c2 and c3=@c3 and c4=@c4
go

簡單利用 console 程式測試

using System;
using System.Data.SqlClient;

namespace ConsoleApplication4
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SqlConnection cnn = new SqlConnection("Data Source=.;Initial Catalog=tempdb;integrated security=sspi"))
            {
                SqlCommand cmd = new SqlCommand("select c1 from t1 where c2=@c2 and c3=@c3 and c4=@c4");
                cmd.Parameters.Add(new SqlParameter("c2", "000000"));
                cmd.Parameters.Add(new SqlParameter("c3", "0000000000"));
                cmd.Parameters.Add(new SqlParameter("c4", "0.0558658"));
                cmd.Connection = cnn;

                SqlCommand cmd2 = new SqlCommand("select c1 from t1 where c2=@c2 and c3=@c3 and c4=@c4");
                cmd2.Parameters.Add(new SqlParameter("c2", System.Data.SqlDbType.Char,6));
                cmd2.Parameters.Add(new SqlParameter("c3", System.Data.SqlDbType.Char, 10));
                cmd2.Parameters.Add(new SqlParameter("c4", System.Data.SqlDbType.VarChar, 50));
                cmd2.Parameters[0].Value = "000000";
                cmd2.Parameters[1].Value = "0000000000";
                cmd2.Parameters[2].Value = "0.0558658";
                cmd2.Connection = cnn;

                SqlCommand cmd3 = new SqlCommand("sp");
                cmd3.CommandType = System.Data.CommandType.StoredProcedure;
                cmd3.Parameters.Add(new SqlParameter("c2", "000000"));
                cmd3.Parameters.Add(new SqlParameter("c3", "0000000000"));
                cmd3.Parameters.Add(new SqlParameter("c4", "0.0558658"));
                cmd3.Connection = cnn;

                cnn.Open();
                long l = DateTime.Now.Ticks;
                int times = 100000;
                for(int i=0;i<times;i++)
                {
                    //exec sp_executesql N’select c1 from t1 where c2=@c2 and c3=@c3 and c4=@c4′,N’@c2 nvarchar(6),@c3 nvarchar(10),@c4 nvarchar(9)’,@c2=N’000000′,@c3=N’0000000000′,@c4=N’0.0558658′
                    SqlDataReader dr = cmd.ExecuteReader();
                    dr.Close();
                }
                Console.WriteLine((DateTime.Now.Ticks – l).ToString());

                System.Threading.Thread.Sleep(3000);

                l = DateTime.Now.Ticks;
                for (int i = 0; i <times; i++)
                {
                    //exec sp_executesql N’select c1 from t1 where c2=@c2 and c3=@c3 and c4=@c4′,N’@c2 char(6),@c3 char(10),@c4 varchar(50)’,@c2=’000000′,@c3=’0000000000′,@c4=’0.0558658′
                    SqlDataReader dr = cmd2.ExecuteReader();
                    dr.Close();
                }
                Console.WriteLine((DateTime.Now.Ticks – l).ToString());
                System.Threading.Thread.Sleep(3000);

                l = DateTime.Now.Ticks;
                for (int i = 0; i < times; i++)
                {
                    //exec sp @c2=N’000000′,@c3=N’0000000000′,@c4=N’0.0558658′
                    SqlDataReader dr = cmd3.ExecuteReader();
                    dr.Close();
                }
                Console.WriteLine((DateTime.Now.Ticks – l).ToString());
            }
        }
    }
}

 

若 .NET 未指定 type,會自動賦予 nvarchar,導致欄位為 char 的要轉成 nvarchar 再行比較,以此例而言,也就是每次比較就需要轉 10 萬筆,3 欄位的紀錄值:

image

就單次查詢時,SQL Server 的執行計畫判讀不覺得有效能差,所以彼此占比相同。

但重複執行 10 萬次後,時間上,可以看得出來,因為指定正確的欄位型態,或是 Stored Procedure 只需要輸入參數時轉對參數型態,實際語法比較時,不需逐筆轉換,所以效率較佳。

image

就總 CPU 耗用而言,因為無法平行運算,轉資料型態導致 CPU 要跑得比較久,但只會操死一顆 CPU

image