2012年1月30日 星期一

Gartner2012年CIO十大科技要務前三名:BI、行動、雲端

Gartner針對全球2,335名CIO所做的年度IT主管大調查結果顯示,分析與商業智慧將是今年CIO在業務和科技方面最重視的優先任務,調查中第二重要的是行動科技(mobile),此項調查是在2011年第4季所進行。

本調查還發現全球IT預算基本上持平,但各區域差異甚大。

北美地區IT預算較2011年下跌0.6%,而拉丁美洲則成長12.7%;亞太區今年預計將增加3.4%,同期歐洲則減少0.7%。根據Gartner報告,大型企業多已縮減預算。

根據Gartner調查結果,CIO重大科技要務和去年相較略有變動,以下是今年和去年的名次比較:

1.分析和商業智慧(去年名次:5)
2.行動科技(去年名次:3)
3.雲端運算,包括SaaS(去年名次:1)
4.協同/工作流程技術(去年名次:8)
5.舊資產的現代化(Legacy modernization)
6.IT管理(去年名次:4)
7.CRM
8.ERP應用
9.安全
10.虛擬化(去年名次:2)

本調查也探討了業務優先順序及其排名;去年和今年的前三名沒有變化:第一是促進企業成長、第二是吸引新客戶與留住他們,最後是降低成本。

「今年的景氣將迫使CIO的IT策略回到撙節成本,但高層主管卻希望科技,包括IT,能強化企業策略與營運措施,藉此解決公司的重大挑戰。」Gartner分析師Mark McDonald說。

此外,Gartner的調查也發現CIO的IT掌控權已漸漸旁落到CFO(財務長)手中。

2012年1月4日 星期三

2008 SQL Server 瘦身


-- 查詢資料庫使用空間
sp_spaceused

-- 查詢 Data file 各個使用狀況
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

-- SQL SERVER 2008 後採用的命令
-- Set to SIMPLE mode
ALTER DATABASE [DATA_BASE_NAME] SET RECOVERY SIMPLE;

-- Shrink the db to 10 MB
DBCC SHRINKFILE ('DATA_FILE', 10);

-- Set back to FULL (optional depending on backup method used)
ALTER DATABASE  [DATA_BASE_NAME] SET RECOVERY FULL;

2012年1月1日 星期日

Use OpenQuery and Linked Server to query AD data entries


Problem

My boss is asking for a list of email addresses and phone numbers for all users in the company. I know this data exists in Active Directory, so how can I access this data from SQL Server?  In this tip we walk through how you can query Active Directory from within SQL Server Management Studio.

Solution

In this tip I’ll show you how to query Active Directory using linked servers and the OPENQUERY command.

Create Linked Server

First thing we’ll do is create our linked server, Active Directory Service Interface also known as ASDI, to Active Directory using the code below:

USE [master]
GO 
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'DOMAIN\USER',@rmtpassword='*********'
GO 
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible',  @optvalue=N'false'
GO 
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO 
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO 
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO 
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO 
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO 
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO 
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO 
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
GO 
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation',  @optvalue=N'false'
GO 
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO 
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation',  @optvalue=N'true'
GO 
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
Make sure you change the @rmtuser and @rmtpassword variables to a login and password that has access to your Active Directory.

Querying Active Directory

Once the linked server is created we can now setup our query to return the information we need.

First, you’ll need to ask your Network/Systems Administrator for your LDAP info then we can continue to the query.

Here is how the LDAP connection is broken down:

For our example it looks like this: LDAP://DOMAIN.com/OU=Players,DC=DOMAIN,DC=com
LDAP://Domain.com - is the name of a domain controller
/OU=Players - this is the Organization Unit, in our case (Players)
,DC - this is the Domain Name broken up by domain and extension name
So....LDAP://DomainControllerName.com/OU=OrganizationalUnit,DC=DOMAIN,DC=NAME
According to the problem, this user needs to return the companies email addresses and phone numbers. To do this we can use the code below:

(note - you will need to change your domain information for this to work)

SELECT * FROM OpenQuery ( 
  ADSI,  
  'SELECT displayName, telephoneNumber, mail, mobile, facsimileTelephoneNumber 
  FROM  ''LDAP://DOMAIN.com/OU=Players,DC=DOMAIN,DC=com'' 
  WHERE objectClass =  ''User'' 
  ') AS tblADSI
ORDORDER BY displayname
As you can see this query will return Active Directory’s Display Name, Telephone Number, Email Address, Mobile Number, and Fax Number. Also note, that when you query Active Directory it actually creates the SELECT statement backwards. I started the SELECT statement with SELECT displayname… but in the results pane it displayed displayName last as shown below.


If you wanted to view more columns for each user we can use the below code to display fields such as: FirstName, Office, Department, Fax, Mobile, Email, Login, Telephone, Display Name, Title, Company, Pager, Street Address, and more.

SELECT * FROM OpenQuery
  ( 
  ADSI,  
  'SELECT streetaddress, pager, company, title, displayName, telephoneNumber, sAMAccountName, 
  mail, mobile, facsimileTelephoneNumber, department, physicalDeliveryOfficeName, givenname 
  FROM  ''LDAP://DOMAIN.com/OU=Players,DC=DOMAIN,DC=com''
  WHERE objectClass =  ''User'' 
  ') AS tblADSI
ORDER BY displayname


You can also filter out columns using a WHERE clause. In this example I only want to return results where users have a fax number.

SELECT * FROM OpenQuery
  ( 
  ADSI,  
   'SELECT streetaddress, pager, company, title, displayName, telephoneNumber, sAMAccountName, mail,  
  mobile, facsimileTelephoneNumber, department, physicalDeliveryOfficeName, givenname
  FROM  ''LDAP://DOMAIN.com/OU=Players,DC=DOMAIN,DC=com''   
  WHERE objectClass =  ''User'' 
  ') AS tblADSI
WHERE facsimileTelephoneNumber IS NOT NULL
ORDER BY displayname


2011年12月28日 星期三

Telerik Grid Item 的客制化文件

http://www.telerik.com/help/aspnet/grid/grdinsertingvaluesusercontrolformtemplate.html

2011年12月17日 星期六

生活哲學


好好活,慢慢拖,一年還有幾萬多;
不要攀,不要比,不要自己氣自己;
少吃鹽,多吃醋,少打麻將多散步;
按時睡,按時起,跑步跳舞健身體;
父是天,母是地,盡孝父母要牢記;
夫妻愛,子女孝,家和比啥都重要;
行點善,積點德,多做善事多積德;
只要能吃飯,錢就不會斷;
不怕賺錢少,就怕走得早;
官再大,錢再多,閻王照樣土裡拖;

2011年12月14日 星期三

利用 Trigger 來限制登錄 SQL Server


網路找到的文章

不從特定 IP 不准某帳號登入 SQL Server

CREATE TRIGGER [connection_limit_trigger]
ON ALL SERVER
WITH EXECUTE AS 'I7Administrator'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'sa' AND
not exists  (SELECT * FROM sys.dm_exec_connections
            WHERE client_net_address = ’127.0.0.1′
            and session_id=@@SPID)
    ROLLBACK;
END;

–ENABLE TRIGGER [connection_limit_trigger] ON ALL SERVER

其中
ORIGINAL_LOGIN() -- 系統函數, 目前登錄的 帳號
sYS.DM_EXEC_CONNECTIONS -- 目前連線的動態表格
@@SPID -- 目前登錄的 Session ID

2011年12月13日 星期二

Web Performance Tips


Useful page from WE

While developing any web site, one should keep some points in mind.

1) Set debug=false under compilation as follows:
<compilation default Language="c#" debug="false">
2) Use Server.Transfer instead of Response.Redirect.
3) Always check Page.IsValid when using Validator Controls
4) Use Foreach loop instead of For loop for String Iteration.
5) Use Client-Side Validation. (but not all the time you have to validate even on the server side)
6) Check “Page.IsPostBack”. To avoid repetition code execution.
7) GIF and PNG are similar, but PNG typically produces a lower file size. (True, but some browsers not supporting PNG format)
8) Use the AppOffline.htm when updating binaries
9) Turn off Tracing unless until required. (by default it's off, use on the pages where it's required)
<trace enabled="false" requestLimit="10" pageOutput="false" traceMode="SortByTime" localOnly="true"/>
10) Precompiled pages and disable AutoEventWireup; setting the AutoEventWireup attribute to false in the Machine.config file.
11) Turn off Session State, if not required.
<sessionstate timeout="20" cookieless="false" mode="Off" stateconnectionstring="tcpip=127.0.0.1:42424" sqlconnectionstring="data source=127.0.0.1;Trusted_Connection=no">
12) Select the Release mode before making the final Build for your application.
This option is available in the Top Frame just under the Window Menu option. By default, the Mode is Debug
13) Disable ViewState when not required.
EnableViewState="false"
14) Avoid frequent round trips to the Database.
15) Use Caching to improve the performance of your application.
16) Validate all Input received from the Users.
17) Use Finally Method to kill resources. (But not in the case of using)
18) The String and Stringbuilder Magic.
It is nice to use Stringbuilder instead of String when string are Amended. Strings occupy different memory location in every time of amended where stringbuilder use single memory location
19) Never use object value directly; first get object value in local variable and then use. It takes more time then variable reading.
20) Avoid Exceptions: Use If condition (if it is check proper condition)
21) Code optimization:  Avoid using code like x = x +1; it is always better to use x+=1.
22) Data Access Techniques: DataReaders provide a fast and efficient method of data retrieval. DataReader is much faster than DataSets as far as performance is concerned
23) Before doing a bulky ASP code processing, you can check to make sure Response.IsClientConnected.
24) As always, avoid session variables because each ASP page runs in a different thread and session calls will be serialized one by one. So, this will slow down the application. Instead of session variables you can use the QueryString collection or hidden variables in the form which holds the values.
25) Enabling buffering will improve the performance, like
<% response.buffer=true %>
Then use:
<% response.flush=true %> 
26) Use Repeater control instead of DataGrid , DataList, Because It is efficient, customizable, and programmable.
27) Data listing is more time consume when large data are retrieve from database.
Paging will display only particular data but take load of all data.
Fetch only data that is needed for current page.
28) Avoid Inline JavaScript and CSS
29) Use single css file instead of multiple css file.
Try your best to combine all your CSS based classes into a single .css file as lot of .css files will cause a large amount of requests, regardless of the file sizes.
.css files are normally cached by browsers, so a single and heavy .css file doesn’t cause a long wait on each page request.
Inline .css classes could make HTML heavy, so again: go ahead with a single.css file.
30) Reduce cookie size
31) Compress CSS, JavaScript and Images
Online compressors are available; to compress file please refers following web and Replace your file content with optimize code.
32 .Use Cache appropriately
i. Page output caching:
<%@ OutputCache Duration="3600" VaryByParam="none" %>
ii. Page fragment caching:
Write a Page output caching code into each User Control
iii. Data caching:
<script language="C#" runat="server">
Protected void Page_Load (Object src, EventArgs e) {
DataView dv = (DataView) Cache. Get ("EmployeesDataView");
If (dv == null) { // wasn't thereSqlConnection conn =
new SqlConnection ("server=localhost;uid=sa;pwd=;database=Test");
SqlDataAdapter da =new SqlDataAdapter ("select * from Employees", conn);
Dataset ds = new DataSet();da.Fill(ds, "Employees");
dv = ds.Tables["Employees"].DefaultView;
Cache.Insert ("EmployeesDataView", dv);conn.Close();}
Else
Response.Write ("<h2>Loaded employees from data cache! </h2>");
lb1.DataSource = dv;
lb1.DataTextField = "Name";
lb1.DataValueField = "Age";
DataBind () ;}
</script>
33) Use server side compression software such as Port80shttp://www.port80software.com/products/httpzip/  
34) Usage of "using" and I don't know why it's not yet published.
35) Don't make the member variables public or proteted, try to keep private and use public/protected as properties.
36) Use strString=string.Empty instead of strString="" . [And perhaps instead of strString=null also (?)]
37) Make your page files as light as possible. That is try to avoid unnecessary markups, e.g. use div elements instead of tables.
38) Write static messages in div and make it visible when necessary. This is faster than letting server set Text property of your label or div.
39) Retrieve data from database at once, if possible. Don't add up to database trip as far as possible. For this, combine the datafields from different tables and select them.
40) Use short ID name for WebControl.