وحید نصیری وحید نصیری
مطالب دوره‌ها
استفاده از Adobe iFilter برای جستجوی Full Text در فایل‌های PDF
در قسمت‌های قبل، نحوه‌ی کار با فیلترهای FTS آفیس را بررسی کردیم. شرکت Adobe نیز برای جستجوی Full-Text بر روی فایل‌های PDF، یک iFilter خاص را طراحی کرده‌است که نسخه‌ی آخر آن‌را از آدرس ذیل می‌توانید دریافت کنید:
یک تجربه‌ی مهم: نگارش 11 آن را با SQL Server X64 تست کردم کار نکرد. اما نگارش 9 کار می‌کند.
مستندات کامل


پس از نصب ابتدایی آن، مراحل ذیل را برای فعال سازی آن باید طی کرد:

1) تنظیم مسیر پوشه bin نصب فیلتر (مهم!) 
 Start > Control Panel > System > Advanced
Environment Variables -> System Variables -> find PATH
مسیر فوق را در تنظیمات ویندوز یافته و سپس به انتهای Path، آدرس پوشه bin فیلتر نصب شده را اضافه کنید:
 C:\Program Files\Adobe\Adobe PDF iFilter 9 for 64-bit platforms\bin\
دقت داشته باشید که این مسیر باید به \ ختم شود.
سپس کل سیستم را ری استارت کنید.


2) ثبت آن در وهله‌ی جاری SQL Server
برای این منظور ابتدا دستورات ذیل را جرا کنید:
 exec sys.sp_fulltext_service 'load_os_resources', 1;
EXEC sp_fulltext_service 'verify_signature', 0
EXEC sp_fulltext_service 'update_languages'; -- update language list
EXEC sp_fulltext_service 'restart_all_fdhosts'; -- restart daemon
reconfigure with override
گزینه‌ی verify_signature مربوط به فایل‌های iFilter ایی است که امضای دیجیتال ندارند.
سپس در management studio یکبار بر روی وهله‌ی جاری کلیک راست کرده و گزینه‌ی Restart را انتخاب کنید (مهم).

3) پس از ری‌استارت SQL Server، اطمینان حاصل کنید که این فیلتر جدید نصب شده‌است:
 exec sys.sp_help_fulltext_system_components 'filter';


و یا کوئری ذیل نیز برای این منظور مفید است:
 SELECT document_type, path from sys.fulltext_document_types where document_type = '.pdf'

4) در ادامه برای استفاده از آن آزمایش ذیل را ترتیب خواهیم داد.

ایجاد یک جدول جدید که فایل‌های باینری PDF را در خود ذخیره می‌کند:
 CREATE TABLE PdfDocuments
(
id INT IDENTITY(1,1) NOT NULL,
doctype NCHAR(4) NOT NULL,
doccontent VARBINARY(MAX) NOT NULL,
CONSTRAINT PK_PdfDocuments
PRIMARY KEY CLUSTERED(id)
);
ستون‌های doctype معرف نوع سند و doccontent ذخیره کننده‌ی محتوای کامل فایل‌های PDF خواهند بود.
سپس چند رکورد را در آن ثبت می‌کنیم. برای نمونه دو مقاله‌ی خروجی PDF سایت جاری را در این جدول ثبت خواهیم کرد:
 INSERT INTO PdfDocuments(doctype, doccontent)
SELECT
N'PDF',
bulkcolumn FROM OPENROWSET (BULK 'C:\Users\Vahid\Downloads\1732-DotNetTips.pdf', SINGLE_BLOB) AS doc;

INSERT INTO PdfDocuments(doctype, doccontent)
SELECT
N'PDF',
bulkcolumn FROM OPENROWSET (BULK 'C:\Users\Vahid\Downloads\1733-DotNetTips.pdf', SINGLE_BLOB) AS doc;
در ادامه علاقمندیم تا بر روی خواص و متادیتای فایل‌های PDF نیز بتوانیم جستجوی FTS انجام دهیم. به همین منظور search propery list متناظری را نیز تعریف خواهیم کرد. همانطور که در قسمت‌های قبل عنوان شد، نیاز است GUID هر خاصیت را برای تعریف از سازنده‌ی iFilter دریافت کرد. این اطلاعات در سند ذیل مستند شده‌اند:

 -- Search property list
CREATE SEARCH PROPERTY LIST PdfSearchPropertyList;
GO
ALTER SEARCH PROPERTY LIST PdfSearchPropertyList
ADD 'Author'
WITH (PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9',
PROPERTY_INT_ID = 4,
PROPERTY_DESCRIPTION = 'Author - author of a given item.');
GO

در اینجا اگر علاقمند بودید، stop list معرفی شده در قسمت‌های قبل را نیز می‌توان افزود.
 CREATE FULLTEXT STOPLIST SQLStopList;
GO
-- Add a stopwords
ALTER FULLTEXT STOPLIST SQLStopList ADD N'به' LANGUAGE 'English';
ALTER FULLTEXT STOPLIST SQLStopList ADD N'با' LANGUAGE 'English';
--.....

سپس یک کاتالوگ FTS و ایندکس Full-Text ایی را بر روی این جدول ایجاد می‌کنیم:
 -- Full-text catalog
CREATE FULLTEXT CATALOG PdfDocumentsFtCatalog;
GO

-- Full-text index
CREATE FULLTEXT INDEX ON PdfDocuments
(
  [doccontent] TYPE COLUMN [doctype]
  Language 1033
  STATISTICAL_SEMANTICS
)
KEY INDEX PK_PdfDocuments
ON PdfDocumentsFtCatalog
WITH STOPLIST = SQLStopList,
  SEARCH PROPERTY LIST = PdfSearchPropertyList,
  CHANGE_TRACKING AUTO;
GO

آیا کار می‌کند ؟ چیزی ایندکس شده‌است؟ 
 SELECT
 I.document_id,
 I.display_term,
 I.occurrence_count
FROM sys.dm_fts_index_keywords_by_document(DB_ID(DB_NAME()), OBJECT_ID(N'dbo.PdfDocuments')) AS I
INNER JOIN dbo.PdfDocuments D
ON D.id = I.document_id;


انجام دو کوئری بر روی آن. یکی برای یافتن متنی ساده و دیگری برای یافتن خواص

 SELECT *
FROM PdfDocuments
WHERE CONTAINS(doccontent, N'است')

SELECT
 I.document_id,
 I.display_term,
 I.property_id
FROM sys.dm_fts_index_keywords_by_property(DB_ID(DB_NAME()), OBJECT_ID(N'dbo.PdfDocuments')) AS I
INNER JOIN dbo.PdfDocuments D
ON D.id = I.document_id;


 
‫۱۰ سال و ۸ ماه قبل، دوشنبه ۱۹ اسفند ۱۳۹۲، ساعت ۱۴:۲۵
محمد رجبی محمد رجبی
مطالب
Resource Governor در 2008 SQL Server
مقدمه
Resource  Governor، اجازه می‌دهد تا انواع مختلف Session را بر روی Server طبقه بندی کنید که به نوبه خود چگونگی کنترل تخصیص منابع سرور به فعالیت داده شده را به شما اعطا می‌کند. این قابلیت کمک می‌کند که ادامه فرآیند‌های OLTP تضمین شود و یک عملکرد قابل پیش بینی فراهم می‌کند تا توسط فرآیند‌های غیر قابل پیش بینی، تحت تاثیر منفی قرار نگیرد. با استفاده از Resource  Governor، قادر خواهید بود نحوه دستیابی به Session را به منظور محدود کردن منابع خاص برای SQL Server مشخص کنید. به عنوان مثال می‌توانید مشخص کنید که بیش از 20 درصد از پردازنده یا منابع حافظه به گزارش‌های در حال اجرا اختصاص داده نشود. هنگامیکه این ویژگی فعال باشد، مهم نیست چه تعداد گزارش در حال اجرا است، آنها هرگز نمی‌توانند از تخصیص منابع تعیین شده تجاوز کنند. البته این موضوع عملکرد گزارش گیری را کاهش می‌دهد ولی عملکرد فرآیند‌های OLTP حداقل توسط گزارش ها، دیگر تحت تاثیر منفی قرار نمی‌گیرد.

1- بررسی اجمالی Resource Governor:
Resource Governor، با کنترل تخصیص منابع بر حسب Workload کار می‌کند. هنگامی که یک درخواست اتصال به موتور بانک اطلاعاتی ارسال می‌شود درخواست براساس یک تابع رده بندی (Classification function) طبقه بندی می‌شود. تابع رده بندی یک تابع اسکالر است که از طریق T-SQL تعریف می‌شود. تابع رده بندی، اطلاعات را درباره یک اتصال (برای مثال  login ID، application name، hostname، server role ) ارزیابی می‌کند، به منظور تشخیص اینکه چگونه آنها را دسته بندی کند. پس از دسته بندی درخواست اتصال، آنها به گروه‌های حجم کاری (Workload Group) که برای رده بندی تعریف شده اند، شکسته می‌شوند. هر Workload Group مرتبط با یک مخزن منابع (Resource Pool) است.
یک Resource Pool، منابع فیزیکی SQL Server را نمایش می‌دهد (در حال حاضر در SQL Server 2008، تنها منابع فیزیکی موجود برای پیکربندی پردازنده و حافظه است) و مقدار حداکثر پردازنده و یا منابع حافظه را که به نوع خاصی از Workload اختصاص داده می‌شود، تعیین می‌کند. هنگامی که یک اتصال طبقه بندی شده و در Workload Group صحیح خود قرار می‌گیرد به این اتصال، پردازنده و منابع حافظه به اندازه نسبت داده شده به آن تخصیص داده می‌شود و سپس Query به Query Optimizer برای اجرا داده می‌شود. 

2-  اجزای Resource Governor:
Resource Governor، از سه قسمت اصلی تشکیل شده است: Classification، Workload Groups و Resource Pools. درک این سه قسمت و چگونگی تعامل آنها به درک و استفاده از Resource Governor کمک می‌کند.

2-1- Classification:
Classification، فرآیند ارزیابی اتصالات ورودی کاربر و اختصاص آن به یک Workload Group است که توسط منطق موجود در یک تابع تعریف شده توسط کاربر (user-defined function) انجام می‌شود. تابع نام یک Workload Group را برمی گرداند که Resource Governor از آن برای مسیر دهی Session به Workload Group مناسب استفاده می‌کند.
هنگامی که Resource Governor پیکربندی می‌شود فرآیند ورود به سیستم برای یک Session شامل گام‌های زیر است:
• Login authentication
• LOGON trigger execution
• Classification
2-2- Workload Groups:
Workload Groups، ظروفی برای اتصالات مشابه هستند که با توجه به معیارهای طبقه بندی برای هر اتصال گروه بندی می‌شوند. Workload Groups همچنین مکانیسمی برای تجمیع نظارت بر روی منابع مصرفی فراهم می‌کند.
Resource Governor دو Workload Group از پیش تعریف شده دارد: یک گروه داخلی (internal group) و یک گروه پیش فرض (default group).
 Internal Workload Group، تنها توسط فرآیندهای داخلی موتور بانک اطلاعاتی استفاده می‌شود. معیارهای طبقه بندی را برای گروه‌های داخلی نمی‌توانید تغییر دهید و همچنین هیچ یک از درخواست‌های کاربران را برای انتقال به گروه داخلی نمی‌توانید رده بندی کنید، با این حال بر گروه  داخلی می‌توانید نظارت کنید.
درخواست‌های اتصال به طور خودکار هنگامی که شرایط زیر وجود دارد، به  Default Workload Group رده بندی می‌شوند:
• معیاری برای طبقه بندی درخواست وجود ندارد.
• کوششی برای رده بندی درخواستی به گروهی که وجود ندارد.
• خرابی کلی Classification
Resource Governor، در مجموع 20 عدد Workload Group را پشتیبانی می‌کند. از آنجائی که دو عدد از آنها برای Workload Group‌های داخلی و پیش فرض ذخیره شده اند در مجموع 18 عدد Workload Group تعریف شده توسط کاربر (user-defined) می‌توان تعریف نمود.

2-3- Resource pools:
Resource Pool (مخزن منابع)، نشان دهنده تخصیص منابع فیزیکی به SQL Server است. یک Resource Pool از دو بخش تشکیل شده است:
• در بخش نخستین حداقل رزرو منابع را مشخص می‌کنیم، این بخش از مخزن منابع با مخازن دیگر همپوشانی نمی‌کند.
• در بخش دیگر حداکثر ممکن رزرو منابع را برای مخزن مشخص می‌کنیم، تخصیص منابع با مخازن دیگر مشترک است.
در 2008 SQL Server مخزن منابع با تعیین حداقل و حداکثر تخصیص CPU و حداقل و حداکثر تخصیص حافظه تنظیم می‌گردد. با تنظیم حداقل، در دسترس بودن منبع از مخزن تضمین می‌شود. از آنجائی که در هر رزرو حداقل منابع تداخلی نمی‌تواند وجود داشته باشد، مجموع مقادیر حداقل در تمام مخازن از 100% کل منابع Server نمی‌تواند تجاوز کند.
مقدار حداکثر در محدوده بین حداقل و شامل 100% مقدار می‌تواند تنظیم گردد. تنظیم حداکثر نشان دهنده مقدار حداکثری است که یک Session می‌تواند مصرف کند، مادامی که منابع در دسترس باشند و توسط مخزن دیگر که با حداقل مقدار غیر صفر پیکربندی شده، استفاده نشود. هنگامی که یک مخزن با حداقل مقدار غیر صفر تعریف شده، مقدار حداکثر موثر از مخزن‌های دیگر دوباره تنظیم می‌شوند، در صورت لزوم حداکثر مقدار موجود از جمع کل حداقل منابع مخازن دیگر کسر می‌گردد.
برای مثال، دو مخزن تعریف شده توسط کاربر (user-defined) را در نظر بگیرید. مخزن اول Pool1 با مقدار حداقل 20% و مقدار حداکثر 100% تعریف شده، مخزن دیگری Pool2 با مقدار حداقل 50% و مقدار حداکثر 70% تعریف شده است. حداکثر مقدار موثر برایPool1 برابر 50% است (100% منهای مقدار حداقل 50% مخزن Pool2) و حداکثر مقدار موثر برای Pool2، 70% است زیرا حداکثر مقداری است که پیکربندی شده است، گر چه 80% باقی می‌ماند.
بخش مشترکی از مخزن (مقدارش بین مقدار حداقل و مقدار حداکثر موثر است) که برای تعیین مقدار منابع مورد استفاده است، توسط مخزن می‌تواند مصرف شود اگر منابعی موجود باشد و توسط مخازن دیگر مصرف نشده باشد. هنگامی که منابعی توسط یک مخزن مصرف می‌شوند، آنها به یک مخزن مشخص نسبت داده می‌شوند، به بیان دیگر اشتراکی نیستند تا زمانی که فرآیند در آن مخزن به اتمام برسد.
برای توضیح بیشتر یک سناریو که در آن سه مخزن تعریف شده توسط کاربر (user-defined) وجود دارد، را در نظر بگیرید:
PoolA با حداقل مقدار 10% و حداکثر مقدار 100% تعریف می‌شود.
PoolB با حداقل مقدار 35% و حداکثر مقدار 90% تعریف می‌شود.
PoolC با حداقل مقدار 30% و حداکثر مقدار 80% تعریف می‌شود.
مقدار موثر PoolA و مجموع در صد منابع به اشتراک گذاشته PoolA به شرح زیر محاسبه خواهد شد:
( حداکثر مقدار PoolA ) -  ( حداقل مقدار PoolB ) -  ( حداقل مقدار PoolC ) =  ( حداکثر مقدار موثر PoolA )
(حداکثر مقدار موثر PoolA ) –  ( حداقل مقدار PoolA ) = ( اشتراک PoolA )
جدول زیر مقدار حداکثر موثر و اشتراکی را برای هر مخزن در این پیکربندی نمایش می‌دهد:



  Internal Pool، منابع مصرف شده توسط فرآیندهای داخلی موتور بانک اطلاعاتی را نشان می‌دهد. این مخزن تنها شامل گروه‌های داخلی است و به هیچ وجه قابل تغییر نیست. مخزن داخلی مقدار ثابت حداقل صفر و حداکثر 100% را دارد و مصرف منابع توسط مخزن داخلی، از طریق تنظیمات در هر مخزن دیگر محدود یا کاسته نمی‌شود.
به عبارت دیگر حداکثر مقدار موثر مخزن داخلی همیشه 100% است. هر workloads در مخزن داخلی برای عملکرد Server حیاتی در نظر گرفته می‌شود و Resource Governor در صورت لزوم اجازه می‌دهد تا مخازن داخلی 100% منابع موجود را مصرف کند حتی اگر به معنی نقض نیازمندیهای منابع از سایر مخازن باشد.
Default Pool، اولین مخزن تعریف شده کاربر است. قبل از هرگونه پیکربندی، Default Pool تنها حاوی Default group است. Default Pool نمی‌تواند ایجاد یا حذف شود اما می‌تواند تغییر کند. Default Pool علاوه بر Default group می‌تواند شامل گروه‌های تعریف شده توسط کاربر (user-defined) نیز باشد.

3- پیکر بندی Resource Governor :
پیکربندی Resource Governor شامل مراحل زیر است:
- فعال کردن Resource Governor
- ایجاد مخازن منابع (Resource Pools) تعریف شده توسط کاربر (user-defined)
- تعریف Workload Groups و نسبت دادن آن به مخازن
- ایجاد Classification function
- ثبت Classification function به Resource Governor
3-1-  فعال کردن Resource Governor
پیش از اینکه بتوانید یک Resource Pool را ایجاد کنید، نیاز است تا نخست Resource Governor را فعال کنید.

3-2-  تعریف Resource Pool
ویژگی‌های موجود برای یک Resource Pool عبارتند از:
Name، Minimum CPU %، Maximum CPU%، Min Memory%، Max Memory%

3-3-  تعریف Workload Group 
پس از اینکه Resource Pool را تعریف کردید، گام بعدی ایجاد یک Workload Group و اختصاص آن به Resource Pool مناسب است. چندین workgroup را می‌توان به مخزن (Pool) یکسان نسبت داد اما یک workgroup را به چندین Resource Pool نمی‌توان نسبت داد. خواص انتخابی موجود برای Workload Groups به شما اجازه می‌دهد سطح بهتری از کنترل را روی اجرای دستورات یک Workload Group تنظیم کنید. انتخاب‌های موجود عبارتند از:
3-3-1- Importance :
اهمیت نسبی (کم، متوسط یا بالا) Workload Group درون Resource Pool را تعیین می‌کند. اگر چندین Workload Group را در یک Resource Pool تعریف کنید این تنظیمات تعیین می‌کند که درخواست‌ها در عرض یک Workload Group در اولویت بالاتر یا پائین‌تری از Workload Group‌های دیگر درون همان Resource Pool اجرا شوند، مقدار متوسط تنظیم پیش فرض است. در حال حاضر فاکتورهای وزنی برای هر تنظیم کم برابر 1، متوسط برابر3 و زیاد برابر 9 است. به این معنی که زمانبند به اجرای Session‌های درون workgroup هائی با اهمیت بالا، سه برابر بیشتر از workgroup‌های با اهمیت متوسط و نه برابر بیشتر از workgroup‌های کم اهمیت، مبادرت خواهد کرد.
3-3-2- Maximum Request  :
حداکثر تعداد درخواست‌های همزمان که اجازه دارند در یک Workload Group اجرا شوند را مشخص می‌کند. تنظیم پیش فرض، صفر، تعداد نامحدود دستور را اجازه می‌دهد.
3-3-3-  CPU Time :
حداکثر مقدار زمان پردازنده در ثانیه را مشخص می‌کند که یک درخواست درون Workload Group می‌تواند استفاده کند. تنظیم پیش فرض، صفر، به معنی نامحدود است.
3-3-4- Memory Grant %:
به صورت در صد، حداکثر مقدار اعطا حافظه برای اجرا (Execution grant memory)، که یک تک دستور از Resource Pool می‌تواند اخذ کند را مشخص می‌کند. این درصد نسبی است از مقدار حافظه ای که به Resource Pool نسبت داده می‌شود. محدوده مجاز مقادیر از 0 تا 100 است. تنظیم پیش فرض 25 است.
 Execution grant memory، مقدار حافظه ای است که برای اجرای query استفاده می‌شود (نه برای Buffer کردن یا cache کردن) که می‌تواند صرفه نظر از Resource Pool یا Workload Group توسط تعدادی از Session‌ها به اشتراک گذاشته شود. توجه شود که تنظیم این مقدار به صفر از اجرای عملیات Hash Join و دستورات مرتب سازی در Workload Group‌های تعریف شده توسط کاربر (user-defined)جلوگیری می‌کند. همچنین این مقدار توصیه نمی‌شود بیشتر از 70 باشد زیرا ممکن است Server قادر نباشد، اگر Query‌های همزمان در حال اجرا باشند، حافظه آزاد کافی اختصاص دهد.
3-3-5- Grant Time-out :
حداکثر زمان، به ثانیه، که یک query برای یک منبع منتظر می‌ماند تا در دسترس شود را مشخص می‌کند. اگر منبع در دسترس نباشد، فرآیند ممکن است با یک خطای time-out مواجه شود. تنظیم پیش فرض، صفر، به معنی این است که سرور time-out را با استفاده از محاسبات داخلی بر مبنای هزینه پرس و جو ( query cost ) با تعیین حداکثر زمان برآورد می‌کند.
3-3-6- Degree of Parallelism  :
حداکثر درجه موازی سازی (DOP) را برای پرس و جو‌های موازی تعیین می‌کند. محدوده مجاز مقادیر از 0 تا 64 است. تنظیم پیش فرض، صفر، به معنی این است که فرآیند‌ها از تنظیمات عمومی استفاده می‌کنند.

3-4- ایجاد یک Classification function
پس از تعریف Resource Pool و Workload Group، به یک Classification function نیاز است که شامل منطق ارزیابی اتصالات و نسبت دادن آنها به Workload Group مناسب است. Classification function برای هر اتصال Session جدید به SQL Server بکار می‌رود. هر Session در Workload Group نسبت داده شده به آن باقی می‌ماند تا زمانی که به پایان برسد، مگر اینکه صراحتاً به یک گروه متفاوت دوباره نسبت داده شود. فقط یک Classification function فعال در هر زمان می‌تواند وجود داشته باشد. در صورت عدم تعریف شدن یا عدم فعال بودن Classification function همه اتصالات به Workload Group Default نسبت داده می‌شوند. Classification function یک نام workgroup که نوع آن SYSNAME است (که یک نام مستعار برای دیتا تایپ nvarchar 128 است.) برمی گرداند. اگر تابع تعریف شده مقدار 'NULL ،'Default یا نام گروهی که وجود ندارد را برگرداند، Session به Workload Group Default نسبت داده می‌شود. همچنین اگر به هر دلیلی تابع با موفقیت خاتمه نیابد Session به Workload Group Default نسبت داده می‌شود.
منطق Classification function معمولاً مبتنی بر ویژگی‌های اتصال است و اغلب از طریق مقدار بازگشتی توابع سیستمی از قبیل:
 ()SUSER_NAME() ،SUSER_SNAME() ،IS_MEMBER() ،IS_SERVERROLEMEMBER() ،HOST_NAME و یا ()APP_NAME، نام Workload Group اتصال مشخص می‌شود. علاوه بر این توابع می‌توانید از ویژگی‌های توابع دیگر برای ساخت منطق رده بندی  استفاده کنید. تابع ()LOGINPROPERTY شامل دو  ویژگی (DefaultDatabase و DefaultLanguage) می‌باشد  که می‌تواند برای Classification function استفاده شود. بعلاوه تابع ()CONNECTIONPROPERTY پروتکل‌ها و دسترسی به نقل و انتقالات در شبکه، همچنین جزئیات طرح احراز هویت،  Local IP address و  TCP Port و Client’s IP Address را برای استفاده اتصالات فراهم می‌کند. برای مثال می‌توانید برای یک اتصال، یک Workload Group نسبت دهید، مبتنی بر اینکه subnet یک اتصال ازکجا می‌آید.
نکته: اگر قصد دارید از هر یک از توابع ()HOST_NAME و یا ()APP_NAME در تابع رده بندی تان استفاده کنید، توجه داشته باشید این امکان وجود دارد مقادیر بازگردانده شده توسط این توابع توسط کاربران تغییر داده شوند، گر چه به طور کلی گرایش به استفاده از تابع ()APP_NAME برای رده بندی اتصالات بیشتر است.
 
4- بررسی نمونه ای از پیکربندی Resource Governor
برای سادگی، در این قسمت مثالی ارائه می‌شود که از تابع ()SUSER_NAME استفاده می‌کند: در گام نخست، دو Resource Pool ایجاد می‌شود (  ReportPool و OLTPPool ) 

  

در گام بعدی، دو Workload Group ایجاد می‌شود ( ReportWG1 و OLTPWG1 ) 

سپس دو Login ایجاد می‌شود ( report_user و oltp_user ) که در تابع رده بندی استفاده خواهند شد برای مشخص کردن این که اتصالات Seesion به کدام  Workload Group نسبت داده شوند. پس از اضافه کردن Login‌ها به عنوان User‌ها به Database مورد نظر مان، در بانک اطلاعاتی Master تابع رده بندی (Classification function ) را ایجاد می‌کنیم:  

می توان تابع ()WorkgroupClassifier را در محیط SSMS با اجرای دستور زیر برای Login‌های متفاوت تست نمود: 

در  ادامه دستور زیر برای پیکربندی تابع رده بندی به Resource Governor استفاده می‌شود: 


5- اصلاح پیکربندی Resource Governor:
می‌توانید درمحیط SSMS تنظیمات Resource Pool و Workload Group را تغییر دهید ( برای مثال حداکثر استفاده CPU برای یک Resource Pool و یا درجه اهمیت یک Workload Group). متناوباً می‌توان از دستورات T-SQL استفاده نمود.
نکته: پس از اجرای دستورات ALTER RESOURCE POOL یا ALTER WORKLOAD GROUP، برای اعمال کردن تغییرات اجرای دستور ALTER RESOURCE GOVERNOR RECONFIGURE نیاز می‌باشد.
5-1-  حذف Workload Group :
یک Workload Group را اگر هر نوع Session فعال نسبت داده شده به آن وجود داشته باشد، نمی‌توان حذف نمود. اگر یک Workload Group شامل Sessionهای فعال باشد، حذف Workload Groupو یا جابجائی آن به یک Resource Pool متفاوت، هنگامی که دستور ALTER RESOURCE GOVERNOR RECONFIGURE برای اعمال نمودن تغییرات فراخوانی می‌شود، با خطا مواجه خواهد شد.
5-2-  حذف Resource Pools:
یک Resource Pool را اگر هر نوع Workload Group نسبت داده شده به آن وجود داشته باشد، نمی‌توان حذف نمود. نخست نیاز دارید Workload Group حذف شود و یا به Resource Pool دیگری جابجا گردد.
5-3-  اصلاح Classification function:
اگر نیاز دارید تغییراتی در تابع رده بندی ایجاد نمائید، مهم است توجه داشته باشید که تابع رده بندی تا زمانی که مشخص شده (marked) برای Resource Governor است، نمی‌توان آنرا حذف و یا تغییر داد. پیش از اینکه بتوان تابع رده بندی را اصلاح و یا حذف نمود نخست نیاز دارید Resource Governor را غیر فعال نمائید. متناوباً می‌توان تابع رده بندی را جایگزین کرد با اجرای دستور ALTER RESOURCE GOVERNOR و فرستادن (passing) یک اسم متفاوت برای CLASSIFIER_FUNCTION،همچنین می‌توان با اجرای دستور زیر تابع رده بندی جاری را غیر فعال نمود:

تابع رده بندی می‌توان تعریف کرد که نام Workload Group را از جداول یک بانک اطلاعاتی جستجو کند به جای اینکه نام Workload Group به صورت hard-coding و مطابق با ضوابط درون تابع باشد. عملکرد،  در موقع دسترسی به جدول برای جستجو کردن نام Workload Group، نباید تا حد زیادی تحت تاثیر قرار گیرد. 

6- نظارت بر Resource Governor
با استفاده از Performance Monitor، events و (Dynamic Management View (DMV  می‌توان Workload Group و Resource Pool را نظارت (Monitor) کرد. دو شی Performance برای این کار موجود است: SQL Server:Workload Group Stats و SQL Server:Resource Pool Stats
شکل زیر مربوط به پیکر بندی مثال مورد نظرمان می‌باشد:
 

7- نتیجه گیری
Resource Governor چندین مزیت بالقوه ارائه می‌دهد، در درجه اول قابلیت اولویت بندی منابع Server برای کاربران و برنامه‌های کاربردی (applications) بحرانی، جلوگیری از “runaway” یا درخواست‌های غیر منتظره ای که به شدت و بطور قابل توجهی روی کارائی Server تاثیر منفی می‌گذارند.
ضمناً Resource Governor چندین مشکل بالقوه نیز عرضه می‌کند، برای مثال پیکربندی اشتباه Resource Governor تنها به عملکرد کلی Server آسیب نمی‌رساند بلکه به طور بالقوه روی سرور قفل (Lock) می‌تواند ایجاد کند و نیاز به استفاده از اتصال اختصاصی Administrator برای متصل شدن به SQL Server به منظور اشکال یابی و رفع مشکل  می‌باشد. بنابراین توصیه شده است که تنها در صورتی که DBA با تجربه ای هستید و درک خوب و آشنائی خوبی با Workload هائی که روی بانک اطلاعاتی اجرا می‌شوند دارید، Resource Governor را بکار برید. حتی در این صورت، ضروری است که پیکربندی تان را روی یک Server تستی پیش از اینکه روی محیط تولیدی بگسترانید، تست نمائید.
Resource Governor به عنوان یک ویژگی با نام تجاری جدید در SQL Server 2008، با تعدادی محدودیت همراه است که احتمالاً در نسخه‌های بعدی SQL Server حذف خواهد شد، از محدودیت های  بارز :
- محدودیت منابع (Resource)، که به CPU و حافظه محدود می‌شوند. I/O Disk و منابع شبکه را در  SQL Server 2008 نمی‌توان محدود کرد.
- استفاده از منابع برای Reporting Service، Analysis Service و Integeration Service را نمی‌توان محدود کرد . در این نسخه محدودیت‌های منابع تنها روی هسته موتور بانک اطلاعاتی بکار برده می‌شود.
- محدودیت‌های Resource Governor روی یک SQL Instance تعریف و بکار برده می‌شود.

‫۱۰ سال و ۶ ماه قبل، سه‌شنبه ۲ اردیبهشت ۱۳۹۳، ساعت ۲۲:۲۵
وحید نصیری وحید نصیری
اشتراک‌ها
SQL Server® 2014 Service Pack 1 منتشر شد

A few of the customer-requested updates in Microsoft SQL Server 2014 SP1 are:

  • Column store performance is improved when batch mode operators spill over to disk. A new XEvent provides insights into column store inserts.
  • Several issues in buffer pool extension SSD configuration are addressed, including the ability to do instant initialization of the buffer pool extension file.
  • Certain queries compile faster when using new cardinality estimator, and other cardinality estimation query plans are more efficient when using TF-4199. 
  • The scalability benefits of two trace flags (TF-1236, TF-9024) are applied automatically.
  • Backup header information retrieval works better with encrypted backups.
SQL Server® 2014 Service Pack 1 منتشر شد
‫۹ سال و ۶ ماه قبل، پنجشنبه ۲۷ فروردین ۱۳۹۴، ساعت ۱۴:۴۵
دادگر دادگر
مطالب
تفاوت‌های Stored Procedure و Function ها در SQL Server
در این مقاله سعی شده است به تفاوت‌های Stored Procedure و Function‌ها در SQL Server به صورت مختصر و مفید بپردازیم:
  1. Function‌ها حتما باید مقدار بازگشتی داشته باشند ولی مقدار بازگشتی در *SP‌ها به صورت اختیاری است. یک SP می‌تواند از صفر تا n خروجی داشته باشد.
  2. Function‌ها تنها می‌توانند پارامترهای ورودی داشته باشند ولی SP‌ها علاوه بر پارامترهای ورودی، می‌توانند پارامتر خروجی نیز داشته باشند.
  3. Function‌ها می‌توانند از داخل SP‌ها صدا زده شوند ولی SP‌ها قابل فراخوانی از داخل Function‌ها نیستند.
  4. Function‌ها تنها می‌توانند دستورات Select را اجرا کنند ولی SP‌ها می‌توانند تمامی دستورات را از قبیل Select, Delete, Insert و... اجرا نمایند.
  5. Function‌ها قابل فراخوانی از طریق دستورات Select هستند ولی SP‌ها این قابلیت را ندارند.
  6. Function‌ها قابلیت استفاده به صورت مستقیم را در دستوراتی مانند Having، دارند ولی SP‌ها این گونه نیستند.
  7. Function‌ها در هربار استفاده کامپایل می‌شوند لیکن SP‌ها می‌توانند یکبار کامپایل شده، execution plan آنها برای استفاده آتی نگهداری شوند. این مهمترین تفاوت این دو می‌باشد.
  8. استثناء و خطاها در SP‌ها قابل دریافت توسط Try...Catch می‌باشند ولی در Function‌ها این قابلیت وجود ندارد.
  9. در SP‌ها می‌توان از Transaction‌ها استفاده کرده و آنها را مدیریت نمایید؛ لیکن Function‌ها این چنین امکاناتی را ندارند.
در کل به خاطر داشته باشیم اگر تفاوت امکانات متفاوت یک محصول را بهتر درک کنیم، می‌توانیم از آنها بهتر استفاده نماییم.
*SP: Stored Procedure 
‫۹ سال و ۸ ماه قبل، سه‌شنبه ۱۹ اسفند ۱۳۹۳، ساعت ۰۲:۴۵
وحید نصیری وحید نصیری
مطالب
تهیه گزارش از منسوخ شده‌های مورد استفاده در SQL Server 2008

مطلب "منسوخ شده‌ها در نگارش‌های جدید SQL server" را احتمالا به خاطر دارید. جهت تکمیل آن، کوئری زیر را هم می‌توان ذکر کرد:

SELECT instance_name,
      cntr_value
FROM   sys.dm_os_performance_counters
WHERE  OBJECT_NAME = 'SQLServer:Deprecated Features'
      AND cntr_value > 0
ORDER BY
      cntr_value DESC

توسط این کوئری گزارشی از منسوخ شده‌های مورد استفاده‌ در دیتابیس‌های شما ارائه می‌شود. برای مثال چندبار از text و ntext استفاده کرده‌اید، آیا هنوز compatibility level دیتابیس‌های خود را تغییر نداده‌اید و مثال‌هایی از این دست.

برای مثال جهت یافتن سریع فیلدهای منسوخ شده text و image دیتابیس جاری از کوئری زیر می‌توان کمک گرفت:
SELECT O.Name,
      col.name AS ColName,
      systypes.name
FROM   syscolumns col
      INNER JOIN sysobjects O
           ON  col.id = O.id
      INNER JOIN systypes
           ON  col.xtype = systypes.xtype
WHERE  O.Type = 'U'
      AND OBJECTPROPERTY(o.ID, N'IsMSShipped') = 0
      AND systypes.name IN ('text', 'ntext', 'image')
ORDER BY
      O.Name,
      Col.Name

‫۱۴ سال و ۱۰ ماه قبل، دوشنبه ۲۸ دی ۱۳۸۸، ساعت ۰۱:۱۱
وحید نصیری وحید نصیری
مطالب
عدم کاهش حجم لاگ فایل SQL Server

در مورد روش‌های کاهش حجم لاگ فایل‌های SQL Server در این مطلب بحث شد.
اما یکی از دیتابیس‌های قدیمی shrink نمی‌شد و پیغام خطای زیر را صادر می‌کرد:

Cannot shrink log file 2 because of minimum log space required.

یکی از علت‌هایی که اگر مطابق روش ذکر شده در مقاله یاده شده رفتار شود، سبب کاهش حجم لاگ فایل یک دیتابیس نمی‌شود، وجود تراکنش‌های کامل نشده است. جهت مشاهده‌ی وضعیت تراکنش‌های یک دیتابیس می‌توان دستور زیر را صادر کرد:

DBCC OPENTRAN
که نتیجه به صورت زیر بود:

Replicated Transaction Information:
       Oldest distributed LSN     : (0:0:0)
       Oldest non-distributed LSN : (5291:25:1)

وجود سطر مربوط به Oldest non-distributed LSN به این معنا است که هنوز یک replication نا تمام بر روی این دیتابیس موجود است. البته چون این دیتابیس از یک سرور دیگر به اینجا منتقل شده بود و هیچ نوع replication ایی هم در این سرور بر روی آن تنظیم نشده بود؛ بنابراین ابتدا این replication حذف شد:
exec sp_removedbreplication 'dbName', 'both';

سپس مجددا دستور زیر جهت مشاهده‌ی وضعیت تراکنش‌های ناتمام صادر شد:
DBCC OPENTRAN

که این‌بار دیگر هیچ خروجی نداشت.
اکنون با استفاده از روش ذکر شده، لاگ فایل 70 گیگابایتی این دیتابیس به سادگی به چند مگابایت shrink شد.

‫۱۴ سال و ۱۱ ماه قبل، پنجشنبه ۲۶ آذر ۱۳۸۸، ساعت ۱۷:۱۲
وحید نصیری وحید نصیری
مطالب
روش‌هایی برای حذف رکوردهای تکراری

هر برنامه نویسی در طول عمر کاری خود حداقل یکبار با این مساله مواجه خواهد شد: "چگونه یک سری رکورد تکراری موجود را باید حذف کرد؟"

ابتدا جدول موقتی زیر را که در آن یک سری رکورد تکراری ثبت شده است در نظر بگیرید:

CREATE TABLE #Employee
(
ID          INT,
FIRST_NAME  NVARCHAR(100),
LAST_NAME   NVARCHAR(300)
)

INSERT INTO #Employee VALUES ( 1, 'Vahid', 'Nasiri' );
INSERT INTO #Employee VALUES ( 2, 'name1', 'lname1' );
INSERT INTO #Employee VALUES ( 3, 'name2', 'lname2' );
INSERT INTO #Employee VALUES ( 2, 'name1', 'lname1' );
INSERT INTO #Employee VALUES ( 3, 'name2', 'lname2' );
INSERT INTO #Employee VALUES ( 4, 'name3', 'lname3' );

روش‌های حذف رکوردهای تکراری جدول موقتی Employee

الف) استفاده از یک جدول موقتی دیگر بر اساس تمامی فیلدهای موجود

SELECT DISTINCT *
FROM   #Employee 
   
SELECT * INTO #DuplicateEmployee
FROM   #Employee

INSERT #DuplicateEmployee
SELECT DISTINCT *
FROM   #Employee

BEGIN TRAN 
DELETE #Employee
INSERT #Employee
SELECT *
FROM   #DuplicateEmployee

   COMMIT TRAN 
    
  DROP TABLE #DuplicateEmployee 
   
SELECT DISTINCT *
FROM   #Employee

ب) استفاده از یک جدول موقتی دیگر بر اساس یک مجموعه از فیلدهای موجود

SELECT DISTINCT * FROM #Employee

SELECT * INTO #DuplicateEmployee FROM #Employee

INSERT #DuplicateEmployee
SELECT ID,
    FIRST_NAME,
    LAST_NAME
FROM   #Employee
GROUP BY
    ID,
    FIRST_NAME,
    LAST_NAME
HAVING COUNT(*) > 1

BEGIN TRAN
DELETE #Employee
FROM   #DuplicateEmployee
WHERE  #Employee.ID = #DuplicateEmployee.ID
   AND #Employee.FIRST_NAME = #DuplicateEmployee.FIRST_NAME
   AND #Employee.LAST_NAME = #DuplicateEmployee.LAST_NAME

INSERT #Employee
SELECT *
FROM   #DuplicateEmployee

COMMIT TRAN
DROP TABLE #DuplicateEmployee

SELECT DISTINCT * FROM   #Employee

ج) استفاده از rowcount بر اساس یک مجموعه از فیلدهای موجود

SELECT DISTINCT *
FROM   #Employee

SET ROWCOUNT 1
SELECT 1
WHILE @@rowcount > 0
   DELETE #Employee
   WHERE  1 < (
              SELECT COUNT(*)
              FROM   #Employee a2
              WHERE  #Employee.ID = a2.ID
                     AND #Employee.FIRST_NAME = a2.FIRST_NAME
                     AND #Employee.LAST_NAME = a2.LAST_NAME
          )

SET ROWCOUNT 0

SELECT DISTINCT *
FROM   #Employee

د) استفاده از Analytical Functions بر اساس یک مجموعه از فیلدهای موجود

SELECT DISTINCT *
FROM   #Employee;
 
WITH #DeleteEmployee AS (
                         SELECT ROW_NUMBER()
                                OVER(PARTITION BY ID, First_Name, Last_Name ORDER BY ID) AS
                                RNUM
                         FROM   #Employee
                     )

DELETE
FROM   #DeleteEmployee
WHERE  RNUM > 1

SELECT DISTINCT *
FROM   #Employee

ه) استفاده از یک فیلد identity جدید بر اساس یک مجموعه از فیلدهای موجود

SELECT DISTINCT *
FROM   #Employee;
 
ALTER TABLE #Employee ADD UNIQ_ID INT IDENTITY(1, 1)

DELETE
FROM   #Employee
WHERE  UNIQ_ID < (
        SELECT MAX(UNIQ_ID)
        FROM   #Employee a2
        WHERE  #Employee.ID = a2.ID
               AND #Employee.FIRST_NAME = a2.FIRST_NAME
               AND #Employee.LAST_NAME = a2.LAST_NAME
    )

ALTER TABLE #Employee DROP COLUMN UNIQ_ID

SELECT DISTINCT *
FROM   #Employee

و در آخر

DROP TABLE #Employee

‫۱۵ سال و ۳ ماه قبل، چهارشنبه ۲۸ مرداد ۱۳۸۸، ساعت ۰۲:۵۵
وحید نصیری وحید نصیری
مطالب
امن سازی برنامه‌های ASP.NET Core توسط IdentityServer 4x - قسمت اول - نیاز به تامین کننده‌ی هویت مرکزی
عصر Thick Clients

امن سازی برنامه‌های وب همواره چالش برانگیز بوده‌است؛ خصوصا این روزها که نیاز است برنامه‌ها، خارج از دیوارهای یک شرکت نیز در دسترس باشند و توسط انواع و اقسام وسایل ارتباطی مورد استفاده قرار گیرند. در سال‌های قبل، عموما برنامه‌های thick clients مانند WPF و WinForms برای شرکت‌ها توسعه داده می‌شدند و یا برنامه‌های وب مانند ASP.NET Web Forms که مبتنی بر سرویس‌ها نبودند. در برنامه‌های ویندوزی، پس از لاگین شخص به شبکه و دومین داخلی شرکت، عموما از روش Windows Authentication برای مشخص سازی سطوح دسترسی کاربران استفاده می‌شود. در برنامه‌های Web Forms نیز بیشتر روش Forms Authentication برای اعتبارسنجی کاربران مرسوم است. امن سازی این نوع برنامه‌ها ساده‌است. عموما بر روی یک دومین ارائه می‌شوند و کوکی‌های اعتبارسنجی کاربران برای ارائه‌ی مباحثی مانند single sign-on (داشتن تنها یک صفحه‌ی لاگین برای دسترسی به تمام برنامه‌های شرکت)، میسر است.


عصر شروع به‌کارگیری سرویس‌های وب

در سال‌های اخیر این شیوه‌ی کاری تغییر کرده و بیشتر بر اساس بکارگیری برنامه‌های مبتنی بر سرویس‌ها شده‌است. برای مثال برای این مورد استاندارد WS-Security مربوط به WCF ارائه شده‌است که باز هم مرتبط است به برنامه‌های یک دومین و یا یک Application pool. اگر تعداد دومین‌ها بیشتر شوند و نیاز به ارتباط امن بین آن‌ها باشد، استاندارد SAML 2.0 مورد استفاده قرار می‌گرفت که هدف از آن، انتقال امن اعتبارسنجی و سطوح دسترسی کاربران بین دومین‌های مختلف است. همانطور که ملاحظه می‌کنید تمام این برنامه‌ها و استانداردها، داخل دیوارهای یک شرکت و یک دومین زندگی می‌کنند.


عصر شروع به‌کارگیری Restful-API's

پس از آن باز هم شیوه‌ی طراحی برنامه‌های تغییر کرد و شروع به ایجاد Restful-API's و HTTP API's کردیم. این‌ها دیگر الزاما داخل یک دومین ارائه نمی‌شوند و گاهی از اوقات حتی تحت کنترل ما هم نیستند. همچنین برنامه‌های ارائه شده نیز دیگر thick clients نیستند و ممکن است برنامه‌های سمت کلاینت Angular و یا حتی موبایل باشند که مستقیما با سرویس‌های API برنامه‌ها کار می‌کنند. حتی ممکن است یک API را طراحی کنیم که با یک API دیگر کار می‌کند.


در این حالت دیگر نمی‌توان این APIها را با نگهداری آن‌ها داخل دیوارهای یک شرکت محافظت کرد. اگر قرار است با یک HTTP API کار کنیم، این API باید عمومی باشد و در اینجا دیگر نمی‌توان از روش Forms Authentication استفاده کرد. زیرا این روش اعتبارسنجی مختص برنامه‌های سمت سرور قرار گرفته‌ی در یک دومین طراحی شده‌است و آنچنان سازگاری با برنامه‌های سمت کلاینت و موبایل خارج از دیوارهای آن ندارد. همچنین ارسال نام کاربری و کلمه‌ی عبور به ازای هر درخواست نیز روشی بسیار بدوی و نا امن است. اینجا است که عصر امن سازی برنامه‌ها به کمک توکن‌ها شروع می‌شود. با استفاده‌ی از توکن‌ها، بجای هر بار ارسال نام کاربری و کلمه‌ی عبور به ازای هر درخواست از API، صرفا لیست سطوح دسترسی امضا شده‌ی به امکاناتی خاص، ارسال می‌شوند.


عصر شروع به‌کارگیری Security Tokens

بنابراین در اینجا نیاز به برنامه‌ای برای تولید توکن‌ها و ارسال آن‌ها به کلاینت‌ها داریم. روش متداول پیاده سازی آن، ساخت یک برنامه‌ی ابتدایی، برای دریافت نام کاربری و کلمه‌ی عبور از کاربران و سپس بازگشت یک JSON Web Token به آن‌ها است که بیانگر سطوح دسترسی آن‌ها به قسمت‌های مختلف برنامه است و کاربران باید این توکن را به ازای هر درخواستی به سمت سرور (بجای نام کاربری و کلمه‌ی عبور و خود) ارسال کنند.
مشکل این روش در اینجا است که آن برنامه‌ی خاص، باید از نام کاربری و کلمه‌ی عبور کاربران مطلع باشد تا بتواند توکن مناسبی را برای آن کاربر خاص تولید کند. هر چند این روش برای یک تک برنامه‌ی خاص بسیار مناسب به نظر می‌رسد، اما در یک شرکت، ده‌ها برنامه مشغول به کارند و به اشتراک گذاری نام کاربری و کلمه‌ی عبور کاربران، با تک تک آن‌ها ایده‌ی مناسبی نیست و پس از مدتی از کنترل خارج خواهد شد. برای مثال کاربری در یک برنامه، کلمه‌ی عبور خود را تغییر می‌دهد اما در برنامه‌ای دیگر خیر و همین مساله‌ی عدم هماهنگی بین برنامه‌ها و همچنین بخش‌های مختلف یک شرکت، مدیریت یک دست برنامه‌ها را تقریبا غیر ممکن می‌کند. همچنین در اینجا برنامه‌های ثالث را نیز باید در نظر داشت که آیا ضروری است آن‌ها به ریز اطلاعات کاربران شرکت دسترسی پیدا کنند؟
به علاوه مشکل دیگر توسعه‌ی این نوع برنامه‌های صدور توکن خانگی، اختراع مجدد چرخ است. در اینجا برای بهبود امنیت برنامه باید منقضی شدن، تمدید، امضای دیجیتال و اعتبارسنجی توکن‌ها را خودمان پیاده سازی کنیم. توسعه‌ی یک چنین سیستمی اگر غیرممکن نباشد، بسیار سخت و پیچیده است و همچنین باید باگ‌های امنیتی ممکن را نیز مدنظر داشت.


بنابراین تا اینجا به این نتیجه رسیده‌ایم که دیگر نمی‌خواهیم مدیریت نام کاربری و کلمه‌ی عبور کاربران را در سطح هیچکدام از برنامه‌های خود انجام دهیم و هیچکدام از آن‌ها قرار نیست دریافت کننده‌ی این اطلاعات باشند. قرار است این کار، به یک تک برنامه‌ی مرکزی مخصوص اینکار منتقل شود و برای اینکار نیاز به پروتکلی امن است که بتوان این توکن‌های تولیدی را ارسال و پردازش کرد.


حرکت به سمت «تامین کننده‌ی هویت مرکزی»


در گذشته، هر تک برنامه‌ای دارای صفحه‌ی لاگین و امکانات مدیریت کاربران آن، تغییر کلمه‌ی عبور، تنظیم مجدد آن و این‌گونه عملیات بود. این‌روزها دیگر چنین کاری مرسوم نیست. این وظیفه‌ی برنامه‌ی شما نیست که بررسی کند کاربر وارد شده‌ی به سیستم کیست و آیا ادعای او صحیح است یا خیر؟ این نوع عملیات وظیفه‌ی یک Identity provider و یا به اختصار IDP است. کار IDP اعتبارسنجی کاربر و در صورت نیاز، ارائه‌ی اثبات هویت کاربر، به برنامه‌ی درخواست کننده‌است.
در یک IDP عملیاتی مانند ثبت کاربران و مدیریت آن‌ها انجام می‌شود. اینجا است که مفاهیمی مانند قفل کردن اکانت و یا تغییر کلمه‌ی عبور و امثال آن انجام می‌شود و نه اینکه به ازای هر برنامه‌ی تهیه شده‌ی برای یک شرکت، آن برنامه راسا اقدام به انجام چنین عملیاتی کند. به این ترتیب می‌توان به امکان استفاده‌ی مجدد از اطلاعات هویت کاربران و سطوح دسترسی آن‌ها در بین تمام برنامه‌های موجود رسید.
همچنین با داشتن یک برنامه‌ی IDP خوب پیاده سازی شده، از توزیع باگ‌های امنیتی مختلف در بین برنامه‌های گوناگون تهیه شده که هر کدام سیستم امنیتی خاص خودشان را دارند، جلوگیری خواهد شد. برای مثال فرض کنید می‌خواهید الگوریتم هش کردن پسوردهای سیستم را که امروز نا امن اعلام شده‌است، تغییر دهید. با داشتن یک IDP، دیگر نیازی نیست تا تمام برنامه‌های خود را برای رفع یک چنین باگ‌هایی، تک تک تغییر دهید.


به علاوه این روزها روش استفاده‌ی از نام کاربری و کلمه‌ی عبور، تنها راه ورود به یک سیستم نیست و استفاده از کلیدهای دیجیتال و یا روش‌های ویژه‌ی ابزارهای موبایل نیز به این لیست اضافه شده‌اند.


حرکت به سمت استاندارد OAuth 2

OAuth 2.0 پروتکلی است استاندارد، برای Authorization امن کاربران، توسط برنامه‌های وب، موبایل و دسکتاپ. به این ترتیب می‌توان امکان دسترسی یک برنامه را به یک API، به نحوی استاندارد و امن میسر ساخت. OAuth 2.0 یک توکن دسترسی (Access token) را تولید می‌کند و در اختیار کلاینت قرار می‌دهد. سپس آن کلاینت با ارسال این توکن به API مدنظر، امکان دسترسی به امکانات مختلف آن‌را خواهد یافت. به علاوه چون ماهیت برنامه‌های کلاینت وب و غیر وب متفاوت است، این استاندارد نحوه‌ی دریافت چنین توکنی را برای برنامه‌های مختلف نیز تعریف می‌کند. به این ترتیب موارد مشترکی مانند تولید و نحوه‌ی انتقال توکن‌ها به کلاینت‌های مختلف توسط این پروتکل استاندارد بیان می‌شود. در این حالت راه‌حل‌های خانگی ما تبدیل به راه‌حل‌هایی می‌شوند که استاندارد OAuth 2.0 را پیاده سازی کرده باشند. بنابراین IDP ما باید بر مبنای این استاندارد تهیه شده باشد. برای مثال IdentityServer که در این سری بررسی خواهد شد و یا Azure Active Directory، نمونه‌ای از IDPهایی هستند که این استاندارد را کاملا پیاده سازی کرده‌اند.
البته باید دقت داشت که این توکن‌های دسترسی، تنها سطوح دسترسی به منابع API را مشخص می‌کنند و یا به عبارتی عملیات Authorization توسط آن‌ها میسر می‌شود. عملیات ورود به سیستم اصطلاحا Authentication نام دارد و توسط استاندارد دیگری به نام OpenID Connect مدیریت می‌شود.


حرکت به سمت استاندارد OpenID Connect


OpenID Connect یک لایه‌ی امنیتی بر فراز پروتکل OAuth 2.0 است که به اختصار به آن OIDC نیز گفته می‌شود. توسط آن یک کلاینت می‌تواند یک Identity token را علاوه بر Access token درخواست کند. از این Identity token برای ورود به برنامه‌ی کلاینت استفاده می‌شود (Authentication) و پس از آن، برنامه‌ی کلاینت بر اساس سطوح دسترسی تعریف شده‌ی در Access token، امکان دسترسی به امکانات مختلف یک API را خواهد یافت (Authorization). همچنین OpenID Connect امکان دسترسی به اطلاعات بیشتری از یک کاربر را نیز میسر می‌کند.
بنابراین OpenID Connect پروتکلی است که در عمل استفاده می‌شود و توسعه دهنده و جایگزین کننده‌ی پروتکل OAuth 2.0 می‌باشد. هرچند ممکن است در بسیاری از منابع صرفا به OAuth 2.0 بپردازند، اما در پشت صحنه با همان OpenID Connect کار می‌کنند.
مزیت دیگر کار با OpenID Connect، عدم الزام به استفاده‌ی از API، در برنامه‌ای خاص و یا قدیمی است. اگر برنامه‌ی وب شما با هیچ نوع API ایی کار نمی‌کند، باز هم می‌توانید از امکانات OpenID Connect بهره‌مند شوید.
‫۶ سال و ۱ ماه قبل، شنبه ۱۰ شهریور ۱۳۹۷، ساعت ۱۸:۳۰