Brad Chen's Developer Blog: 6月 2009

2009年6月30日星期二

SQL Server Memory pool(Buffer Pool) - Procedure Cache and Data Cache

-- SQL Server 7.0之前(SQL Server 6.5),Data Cache與Procedure Cache有獨立控制的memory pool
-- SQL Server 7.0與SQL Server 2000則是共用一個memory pool
--
-- 此memory pool即Buffer pool
-- The buffer pool is managed by a process called the lazywriter

--01.Procedure cache (execution plan cache)

select * from master.dbo.syscacheobjects

--DBCC PROCCACHE
--GO

--Free Porcedure Cache
-- Method 1: Free Porcedure Cache by database
--SELECT DB_ID('pubs')
--DBCC FLUSHPROCINDB (5)
--GO
--
-- Method 2 -Free All Porcedure Cache
-- DBCC FREEPROCCACHE
-- GO

-- 02.Data Buffer Cache
-- Get TOP 20 objects in the data cache
-- BUG: DBCC MEMUSAGE Is Not Supported in SQL Server 7.0
--The DBCC MEMUSAGE statement is not supported in SQL Server 7.0. Executing it on servers running heavy loads with large databases may cause the server to stop responding.
-- http://support.microsoft.com/default.aspx?scid=kb;en-us;196629

DBCC MEMUSAGE

-- Free Buffer Cache
-- DBCC DROPCLEANBUFFERS

-- Force all dirty pages to be written to disk
--CHECKPOINT
--GO

-- 03.Other Useful Command - DBCC MEMORYSTATUS
USE master
GO
DBCC MEMORYSTATUS
GO

-- 04.Other Useful Command - DBCC PINTABLE (@db_id,@object_id)
-- 微軟不建議使用以下功能,但仍然可以使用,建議在測試環境才使用
-- keep a table's data pages in memory
-- demo Database named pubs
USE pubs
GO
select DB_ID('pubs')
GO
-- return 5
select OBJECT_ID('dbo.jobs')
GO
-- return 277576027
DBCC PINTABLE (5,277576027)
GO

-- release a pinned table's data pages from memory.
USE pubs
GO
select DB_ID('pubs')
GO
-- return 5
select OBJECT_ID('dbo.jobs')
GO
-- return 277576027
DBCC UNPINTABLE (5,277576027)
GO

[reference]
Analyzing SQL Server 2000 Data Caching
How to Interact with SQL Server's Data and Procedure Cache

2009年6月21日星期日

找回Windows Vista與Windows 2008右鍵功能表的執行身分Run as different user功能

經驗老道的系統管理員為了安全性或測試,習慣在Windows 2003或Windows XP的環境按Shift+右鍵功能選單就會多出現一個[執行身分],以方便切換身分執行程式,但此功能在Windows Vista與Windows 2008的環境下此功能已找不到了

[Windows XP] 執行身分的功能

[Windows Vista] 在Vista已找不到

PS.若在 Vista找不到 Active Directory 使用者和電腦..等管理工具,
請看[Vista 的 Microsoft 遠端伺服器管理工具(RSAT) (取代Admin Packs)]

[執行身分的功能找回方法]
1.先到微軟Technet官方網站下載shellRunas程式
http://technet.microsoft.com/en-us/sysinternals/cc300361.aspx

2.下載後解壓縮到C:\Windows\System32目錄下

3.以系統管理員身分啟動[命令提示字元]

4.執行ShellRunad.exe /reg註冊Run as Different User功能

5.註冊成功

6.再次於任一程式或捷徑上按下滑鼠右鍵,
選單上就會出現Run as different user..了

7.輸入指定的user帳戶與密碼,則可以其他身分啟動此程式

2009年6月20日星期六

跨資料庫存取而不想在別的資料庫額外設定權限EXECUTE AS的用法

最近接到一個需求,需要跨資料庫存取,而不想在別的資料庫額外設定權限,
所以就開始了解一下EXECUTE AS的用法

[注意]執行以下Demo Code需要AdventureWorks資料庫

-- 建立測試資料庫TEST1
CREATE DATABASE [TEST1]
GO

-- 建立測試Login帳戶Brad
CREATE LOGIN [Brad]
WITH PASSWORD = N'P@ssw0rd',
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF,
DEFAULT_DATABASE = [TEST1],
DEFAULT_LANGUAGE = [繁體中文]
GO

-- 建立TEST1對應的User帳戶brad
USE [TEST1]
GO
CREATE USER [Brad]
GO

-- 建立PROCEDURE With EXECUTE AS SELF讀取別的資料庫的資料
CREATE PROCEDURE [usp_GetAdvWorkEmployee]
WITH EXECUTE AS SELF
AS
SELECT SUSER_NAME(), USER_NAME(), COUNT(*)
FROM [AdventureWorks].[HumanResources].[Employee]
GO

-- 給予EXECUTE權限給Brad
GRANT EXECUTE ON [usp_GetAdvWorkEmployee] to [Brad]
GO

-- 從AdventureWork資料庫產生一些資料到TEST1資料庫
SELECT * INTO dbo.Employee FROM AdventureWorks.HumanResources.Employee;
GO

-- 建立一個Procedure JOIN外部資料庫的資料表
CREATE PROCEDURE [usp_EmployeeDetail]
WITH EXECUTE AS SELF
AS
SELECT a.EmployeeID, a.Title, b.AddressID ,b.ModifiedDate
FROM dbo.Employee a LEFT JOIN AdventureWorks.HumanResources.EmployeeAddress b
ON a.EmployeeID=b.EmployeeID
GO

-- 給予EXECUTE權限給Brad
GRANT EXECUTE ON [usp_EmployeeDetail] to [Brad]
GO

-- Create function with EXECUTE AS SELF
USE [TEST1]
GO
CREATE FUNCTION ufn_GetAdvWorkEmployee()
RETURNS @retTable TABLE
(
EmployeeID int,
Title nvarchar(50)
)
WITH EXECUTE AS SELF
AS
BEGIN
INSERT INTO @retTable SELECT EmployeeID,Title FROM [AdventureWorks].[HumanResources].[Employee]
RETURN
END;
GO

-- 給予SELECT權限給Brad
GRANT SELECT ON ufn_GetAdvWorkEmployee to [Brad]
GO

-- 此時使用brad登入到SQL Server執行以下2個Procedure仍會出現錯誤訊息
USE [TEST1]
GO
EXECUTE usp_GetAdvWorkEmployee;
GO
EXECUTE usp_EmployeeDetail;
GO
-- Msg 916, Level 14, State 1, Procedure usp_GetAdvWorkEmployee, Line 4
-- 伺服器主體 "sa" 在目前的安全性內容下無法存取資料庫 "AdventureWorks"。

-- 將測試資料庫的TRUSTWORTHY改為ON
USE MASTER
GO
ALTER DATABASE [TEST1] SET TRUSTWORTHY ON
GO

-- 此時使用brad登入到SQL Server執行以下2個Procedure就可以執行成功
USE [TEST1]
GO
EXECUTE usp_GetAdvWorkEmployee;
GO
EXECUTE usp_EmployeeDetail;
GO

-- 刪除資料庫
USE MASTER
GO
DROP DATABASE [TEST1]
GO
-- 刪除Login登入帳戶
DROP LOGIN [Brad]
GO

2009年6月19日星期五

Windows 2008 Evaluation 企業評估版-試用 60 天(可延長至 240 天)

Windows 2008 Enterprise Evaluation 企業評估版-試用 60 天(可延長至 240 天)

Windows Server 2008：產品試用-
http://www.microsoft.com/taiwan/windowsserver2008/download/try-it.aspx
Windows Server 2008 Enterprise (x64) with Hyper-V Beta 可免費試用 60 天(可延長至 240 天)

SQL Server 2008 Enterprise Evaluation 180 Day trial 企業版180 天評估版

SQL Server 2008 企業版 180 天評估版
http://www.microsoft.com/taiwan/sql2008/download/trial-software.aspx

http://technet.microsoft.com/zh-tw/bb851664.aspx

Windows 7, Windows Vista, Windows XP版本對照表

[參考來源]
ZDNet - 名家專欄-Shirley 的 IT 筆記-微軟遲不肯公告Win 7價格的原因?

Windows 7 RC版官方下載可試用到2010年6月1日

Windows 7 RC版
[限制]
1.2009年7月之前可免費下載
2.試用期限到2010年6月1日，但從2010年3月1日開始，電腦每2小時會自動關機一次
3.目前提供英文、德文、日文、西班牙文、法文五國語言版本，若要中文介面需額外下載安裝繁體中文語言包

[Windows 7 RC 光碟取得方式]
1.連進Windows 7 官方下載首頁
2.選擇要下載的語言與版本
3.需使用Windows Live ID(即MSN帳戶)登入後取得序號
4.下載後將影像檔燒錄成光碟即可使用

[語言包取得方式]
在安裝好的Windows 7 RC版系統上,執行Windows Update,在optional位置選取繁體中文語言包

2009年6月18日星期四

SQL Server 7.0 以及 SQL Server 2000 的支援服務時間 (SQL Server Support Lifecycle)

SQL Server 7.0 以及 SQL Server 2000 的主流支援服務終止時間

SQL Server 7.0
於 2005 年 12 月 31 日終止主流技術支援服務，
到 2010 年 12 月底為產品延伸支援服務期間

SQL Server 2000
於 2008 年 4 月 8 日終止主流支援服務，
到 2008 年 4 月開始提供產品延伸支援服務

在所有產品延伸支援服務期間，SQL Server 2000 產品不再接受保固支援及功能改變的要求，
但是安全性更新和付費的支援方式將會繼續提供。
目前微軟任何產品軟體上市後，前5年為主流支援服務期，後5年則為延伸技術支援服務期，產品超過10年後將無任何支援服務

如何識別SQL Server的版本

-- SQL Server 2005
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

發行	Sqlservr.exe
RTM	2005.90.1399
SQL Server 2005 Service Pack 1	2005.90.2047
SQL Server 2005 Service Pack 2	2005.90.3042

-- SQL Server 2000
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

發行	Sqlservr.exe
RTM	2000.80.194.0
SQL Server 2000 SP1	2000.80.384.0
SQL Server 2000 SP2	2000.80.534.0
SQL Server 2000 SP3	2000.80.760.0
SQL Server 2000 SP3a	2000.80.760.0
SQL Server 2000 SP4	2000.8.00.2039

-- SQL Server 7.0
SELECT @@VERSION

版本編號	Service Pack
7.00.1063	SQL Server 7.0 Service Pack 4 (SP4)
7.00.961	SQL Server 7.0 Service Pack 3 (SP3)
7.00.842	SQL Server 7.0 Service Pack 2 (SP2)
7.00.699	SQL Server 7.0 Service Pack 1 (SP1)
7.00.623	SQL Server 7.0 RTM (製造階段版本，Release to Manufacturing)

[參考]
http://support.microsoft.com/kb/321185

Windows Server 2003 R2 With SP2 (180天)評估版下載

Windows Server 2003 R2 with SP2: How to Get Trial Software
如何取得Windows Server 2003 R2 with SP2試用版(評估版)

需使用Windows Live ID(即MSN帳戶)登入申請才能取得序號,
可下載的版本請看參考網站:
繁體中文,英文...等語言,x86或x64也有,下載後為img檔,
直接燒成光碟即可使用

[參考]
http://technet.microsoft.com/en-us/windowsserver/bb430831.aspx

2009年6月16日星期二

Boot INI Options Reference(32位元(32bit)系統,實體記憶體超過4G以上需調整Boot.ini)

Boot INI Options Reference
http://technet.microsoft.com/zh-tw/sysinternals/bb963892(en-us).aspx

32位元(32bit)系統,實體記憶體超過4G以上,系統若要抓到4G以上則要使用/PAE

常用到或常看到的摘要如下:
/PAE
Causes Ntldr to load Ntkrnlpa.exe, which is the version of the x86 kernel that is able to take advantage of x86 PAEs. The PAE version of the kernel presents 64-bit physical addresses to device drivers, so this switch is helpful for testing device driver support for large memory systems.

/FASTDETECT
Default boot option for Windows. Replaces the Windows NT 4 switch /NOSERIALMICE. The reason the qualifier exists (vs. just having NTDETECT perform this operation by default) is so that NTDETECT can support booting Windows NT 4. Windows Plug and Play device drivers perform detection of parallel and serial devices, but Windows NT 4 expects NTDETECT to perform the detection. Thus, specifying /FASTDETECT causes NTDETECT to skip parallel and serial device enumeration (actions that are not required when booting Windows), whereas omitting the switch causes NTDETECT to perform this enumeration (which is required for booting Windows NT 4).

/NOEXECUTE
This option is only available on 32-bit versions of Windows when running on processors supporting no-execute protection. It enables no-execute protection (also known as Data Execution Protection - DEP), which results in the Memory Manager marking pages containing data as no-execute so that they cannot be executed as code. This can be useful for preventing malicious code from exploiting buffer overflow bugs with unexpected program input in order to execute arbitrary code. No-execute protection is always enabled on 64-bit versions of Windows on processors that support no-execute protection. There are several options you can specify with this switch:

/NOEXECUTE=OPTIN Enables DEP for core system images and those specified in the DEP configuration dialog.
/NOEXECUTE=OPTOUT Enables DEP for all images except those specified in the DEP configuration dialog.
/NOEXECUTE=ALWAYSON Enables DEP on all images.
/NOEXECUTE=ALWAYSOFF Disables DEP.

/USERVA=
This switch is only supported on Windows XP and Windows Server 2003. Like the /3GB switch, this switch gives applications a larger address space. Specify the amount in MB between 2048 and 3072. This switch has the same application requirements as the /3GB switch and requires that the /3GB switch be present. Applies to 32-bit systems only.

/3GB
Increases the size of the user process address space from 2 GB to 3 GB (and therefore reduces the size of system space from 2 GB to 1 GB). Giving virtual-memory- intensive applications such as database servers a larger address space can improve their performance. For an application to take advantage of this feature, however, two additional conditions must be met: the system must be running Windows XP, Windows Server 2003, Windows NT 4 Enterprise Edition, Windows 2000 Advanced Server or Datacenter Server and the application .exe must be flagged as a 3-GB-aware application. Applies to 32-bit systems only.