پیکربندی سیستم‌عامل

آیا تیم مدیریت Windows شما برای Windows Server 2019 یک «Gold Build» دارد؟ حتی اگر چنین Buildی وجود دارد، آیا برای SQL Server بهینه شده است؟ مگر آنکه تیم شما Build جداگانه‌ای مخصوص میزبانی SQL Server ساخته باشد، احتمالاً پاسخ منفی است. سفارشی‌سازی‌های دقیق موردنیاز به پیکربندی Build ویندوز، نیازمندی‌های محیط شما و نیازمندی‌های برنامه لایه داده‌ای که Server میزبانی می‌کند بستگی دارد. بخش‌های بعدی برخی تغییراتی را که معمولاً لازم هستند معرفی می‌کنند.

تنظیم Power Plan

مهم است Server را روی Power Plan از نوع High Performance قرار دهید. اگر Balanced Power Plan استفاده شود، CPU ممکن است در دوره‌های Inactivity با سرعت پایین‌تری کار کند. هنگامی که Activity دوباره افزایش می‌یابد، ممکن است مشکل Performance مشاهده شود.

می‌توانید از طریق GUI ویندوز و با بازکردن Power Options در Control Panel و انتخاب High Performance این تنظیم را اعمال کنید، یا از PowerShell/Command Line استفاده نمایید. Listing 1-2 با ارسال GUID مربوط به High Performance به پارامتر -setactive در executable به نام powercfg این کار را نشان می‌دهد.

Listing 1-2 — Set High Performance Power Plan with PowerShell

powercfg -setactive 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c

بهینه‌سازی برای Background Serviceها

بهتر است Server طوری پیکربندی شود که Background Serviceها را نسبت به Foreground Applicationها در اولویت قرار دهد. در عمل، Windows الگوریتم Context Switching خود را طوری سازگار می‌کند که Background Serviceها، از جمله Serviceهای SQL Server، زمان بیشتری از Processor دریافت کنند.

برای اطمینان از فعال بودن Optimize for Background Service، در Control Panel وارد System شوید، Advanced System Settings را انتخاب کنید و در پنجره System Properties، بخش Performance را باز کرده و Settings را انتخاب نمایید.

این تنظیم را با PowerShell نیز می‌توان اعمال کرد. Listing 1-3 نشان می‌دهد که چگونه فرمان Set-ItemProperty مقدار Registry Key مربوط به Win32PrioritySeparation را به‌روزرسانی می‌کند. Script باید با دسترسی Administrator اجرا شود.

Listing 1-3 — Setting Optimize for Background Services with PowerShell

Set-ItemProperty -path HKLM:\SYSTEM\CurrentControlSet\ControlPriorityControl -name Win32PrioritySeparation -Type DWORD -Value 24

اعطای User Rightها

بسته به Featureهایی که می‌خواهید در SQL Server استفاده کنید، شاید لازم باشد به Service Account اجرای SQL Server برخی User Rights Assignmentها را بدهید. این Assignmentها به Security Principal اجازه می‌دهند وظایفی را روی کامپیوتر انجام دهد. برای SQL Server Service Account، این Permissionها برخی قابلیت‌هایی را فعال می‌کنند که با سیستم‌عامل تعامل دارند. سه User Right رایج که هنگام نصب به‌طور خودکار به Service Account داده نمی‌شوند در صفحات بعد توضیح داده شده‌اند.

Instant File Initialization

به‌طور پیش‌فرض، هنگام ایجاد یا گسترش File، File با صفر پر می‌شود؛ فرایندی که «Zeroing Out» نام دارد و هر داده‌ای را که قبلاً همان فضای Disk را اشغال کرده بوده بازنویسی می‌کند. این فرایند، به‌ویژه برای Fileهای بزرگ، می‌تواند زمان‌بر باشد.

می‌توان این رفتار را Override کرد تا Fileها Zero نشوند. این کار یک ریسک امنیتی بسیار کوچک ایجاد می‌کند، زیرا از نظر نظری ممکن است داده قبلی موجود در همان محل Disk قابل کشف باشد؛ اما معمولاً مزیت Performance بسیار بیشتر از این ریسک ارزیابی می‌شود.

برای استفاده از Instant File Initialization، باید User Rights Assignment با نام Perform Volume Maintenance Tasks به Service Account اجرای SQL Server Database Engine داده شود. پس از اعطای این حق، SQL Server به‌صورت خودکار Instant File Initialization را استفاده می‌کند و پیکربندی دیگری لازم نیست.

برای اعطای این Assignment از طریق GUI ویندوز، Local Security Policy را از مسیر Control Panel ➜ System and Security ➜ Administrative Tools باز کنید و سپس Local Policies ➜ User Rights Assignment را دنبال کنید. فهرست کامل Assignmentها نمایش داده می‌شود؛ گزینه Perform Volume Maintenance Tasks را پیدا کنید. شکل 1-5 این وضعیت را نشان می‌دهد.

با Right-click روی Assignment و ورود به Properties می‌توانید Service Account خود را اضافه کنید.

Locking Pages in Memory

اگر Windows با Memory Pressure مواجه شود، تلاش می‌کند Data را از RAM به Virtual Memory روی Disk منتقل کند. این موضوع می‌تواند در SQL Server مشکل ایجاد کند. برای ارائه Performance قابل قبول، SQL Server Data Pageهای اخیراً استفاده‌شده را در Buffer Cache نگه می‌دارد؛ Buffer Cache ناحیه‌ای از Memory است که Database Engine رزرو می‌کند.

در واقع همه Data Pageها از Buffer Cache خوانده می‌شوند، حتی اگر ابتدا لازم باشد از Disk به Memory آورده شوند. اگر Windows تصمیم بگیرد Pageهای Buffer Cache را به Disk منتقل کند، Performance Instance به‌شدت افت می‌کند.

برای جلوگیری از این اتفاق می‌توان Pageهای Buffer Cache را در Memory قفل کرد، به شرط آنکه از Enterprise یا Standard Edition در SQL Server 2019 استفاده شود. برای این کار کافی است Assignment با نام Lock Pages in Memory را با همان روشی که برای Perform Volume Maintenance Tasks گفته شد به Service Account اجرای Database Engine بدهید.

ارسال SQL Audit به Event Log

اگر قصد دارید از SQL Audit برای Capture کردن Activity داخل Instance استفاده کنید، می‌توانید Eventهای ایجادشده را در File، Security Log یا Application Log ذخیره کنید. اگر Enterprise شما نیازمندی امنیتی بالایی دارد، Security Log مناسب‌ترین محل خواهد بود.

برای اینکه Eventهای تولیدشده در Security Log نوشته شوند، Service Account اجرای Database Engine باید User Rights Assignment با نام Generate Security Audits را داشته باشد. این کار از طریق Local Security Policy قابل انجام است.

همچنین باید تنظیم Audit Application Generated پیکربندی شود. این گزینه در Local Security Policy از مسیر Advanced Audit Policy Configuration ➜ System Audit Policies ➜ Object Access قرار دارد. سپس Properties مربوط به Audit Application Generated مطابق شکل 1-6 قابل تغییر است.

انتخاب Featureها

هنگام نصب SQL Server ممکن است وسوسه شوید همه Featureها را نصب کنید تا شاید روزی به آن‌ها نیاز پیدا کنید؛ اما برای Performance، Manageability و Security محیط باید همواره از اصل YAGNI یا «You Aren't Going to Need It» پیروی کنید. این اصل از Extreme Programming می‌آید، اما برای Platform نیز کاربرد دارد: ساده‌ترین چیزی را که کار می‌کند انجام دهید. این رویکرد مشکلات ناشی از Complexity را کاهش می‌دهد. به یاد داشته باشید Featureهای اضافی را می‌توان بعداً نصب کرد. بخش‌های بعد نمای کلی Featureهای اصلی قابل انتخاب هنگام نصب SQL Server 2019 Enterprise Edition را ارائه می‌کنند.

Database Engine Service

Database Engine سرویس اصلی در مجموعه SQL Server است. این سرویس شامل SQLOS — بخشی از Database Engine که مسئول کارهایی مانند Memory Management، Scheduling و مدیریت Lock و Deadlock است — همچنین Storage Engine و Relational Engine می‌شود؛ همان‌طور که در شکل 1-7 نشان داده شده است. Database Engine مسئول Secure کردن، پردازش و بهینه‌سازی دسترسی به Relational Data است.

همچنین اجزای Replication، In-database Machine Learning Services، Full-text و Semantic Extraction برای Search، سرویس Query در PolyBase و Featureهای DQS Server را در خود دارد که می‌توانند به‌صورت اختیاری انتخاب شوند. Replication مجموعه‌ای از ابزارها برای توزیع داده است. In-database Machine Learning Services یکپارچگی با Python و R را فراهم می‌کند؛ Semantic Extraction به شما اجازه می‌دهد در Full Text به‌جای صرفاً Keywordها، بر اساس معنای واژه‌ها جست‌وجو کنید. PolyBase Query Service امکان اجرای T-SQL روی Hadoop Data Sourceها را می‌دهد. DQS Server ابزاری برای یافتن و پاک‌سازی Dataهای ناسازگار است. تمرکز اصلی این کتاب بر قابلیت‌های Core Database Engine است.

Analysis Services

SSAS یا SQL Server Analysis Services مجموعه‌ای از ابزارها برای Analytical Processing و Data Mining است و در یکی از سه Mode قابل نصب است:

• Multidimensional and Data Mining
• Tabular
• PowerPivot for SharePoint

Mode چندبعدی و Data Mining امکان میزبانی Multidimensional Cubeها را فراهم می‌کند. Cubeها می‌توانند داده Aggregated که Measure نامیده می‌شود ذخیره کنند و آن را در Dimensionهای متعدد Slice و Dice کنند؛ این ساختار زیربنای Report و Pivot Tableهای Responsive، Intuitive و Complex است. Developerها Cube را با زبان MDX یا Multidimensional Expressions Query می‌کنند.

Tabular Mode به Userها اجازه می‌دهد داده را در BI Semantic Model مایکروسافت میزبانی کنند. این Model از xVelocity برای In-memory Analytics استفاده می‌کند، Integration میان Relational و Nonrelational Data Sourceها را فراهم می‌کند و KPI، Calculation و Multi-level Hierarchy ارائه می‌دهد. در Tabular Model به‌جای Dimension و Measure از Table، Column و Relationship استفاده می‌شود.

PowerPivot یک Extension برای Excel است که مانند Tabular Model از xVelocity برای In-memory Analytics استفاده می‌کند و می‌تواند با Data Setهایی تا 2GB کار کند. نصب PowerPivot for SharePoint با اجرای Analysis Services در SharePoint Mode این قابلیت را گسترش می‌دهد و هم Server-side Processing و هم تعامل Browser-based با PowerPivot Workbookها را ارائه می‌کند؛ همچنین Power View Reportها و Excel Workbookها را از طریق SharePoint Excel Services پشتیبانی می‌کند.

Machine Learning Server

Machine Learning Server سرویسی است که از زبان‌های R و Python پشتیبانی می‌کند. این سرویس مجموعه‌ای از R Packageها، Python Packageها، Interpreterها و Infrastructure را فراهم می‌کند تا بتوان Data Science و Machine Learning Solution ایجاد کرد. این Solutionها می‌توانند Data Setهای ناهمگون را Import، Explore و Analyze کنند.

Data Quality Client

همان‌طور که پیش‌تر گفته شد، Data Quality Server به‌عنوان Component اختیاری Database Engine نصب می‌شود؛ اما Data Quality Client را می‌توان به‌عنوان Shared Feature نصب کرد. Shared Feature فقط یک بار روی Server نصب می‌شود و بین تمام Instanceهای SQL Server روی آن Machine مشترک است. Client یک GUI است که امکان Administer کردن DQS و انجام Data Matching و Data Cleansing را فراهم می‌کند.

Client Connectivity Tools

Client Connectivity Tools مجموعه‌ای از Componentهای ارتباط Client/Server است و Network Libraryهای OLEDB، ODBC، ADODB و OLAP را در بر می‌گیرد.

Integration Services

Integration Services یک ابزار گرافیکی و بسیار قدرتمند ETL یا Extract, Transform, Load در SQL Server است. از SQL Server 2012 به بعد Integration Services در Database Engine ادغام شده است؛ بااین‌حال Option مربوط به Integration Services همچنان باید نصب شود تا Functionality درست کار کند، زیرا Binaryهای موردنیاز را در خود دارد.

Packageهای Integration Services شامل Control Flow هستند که وظیفه مدیریت و Flow Operationهایی مانند Bulk Insert، Loop و Transaction را بر عهده دارد. Control Flow همچنین صفر یا چند Data Flow دارد. Data Flow مجموعه‌ای از Data Source، Transformation و Destination است که Framework قدرتمندی برای Merge، Disperse و Transform کردن Data فراهم می‌کند.

Integration Services را می‌توان به‌صورت Horizontal در چند Server Scale Out کرد؛ با یک Master و n Worker. بنابراین در نسخه‌های جدید SQL Server می‌توانید Integration Services کلاسیک Stand-alone، یا Scale-out Master، یا Scale-out Worker را روی Server نصب کنید.

Client Tools Backward Compatibility

Client Tools Backward Compatibility پشتیبانی Featureهای Discontinued در SQL Server را فراهم می‌کند. نصب این Feature، SQL Distributed Management Objects و Decision Support Objects را نصب می‌کند.

Client Tools SDK

نصب Client Tools SDK، Assemblyهای SMO یا Server Management Objects را فراهم می‌کند و به شما اجازه می‌دهد SQL Server و Integration Services را به‌صورت Programmatic از داخل برنامه‌های .NET کنترل کنید.

Distributed Replay Controller

Distributed Replay Featureای است که اجازه می‌دهد یک Trace را Capture و سپس روی Server دیگری Replay کنید. این قابلیت برای آزمودن اثر Performance Tuning یا Software Upgrade مناسب است. با Profiler هم‌پوشانی دارد، اما دو مزیت مهم ارائه می‌کند:

1. Distributed Replay تأثیر کمتری بر Resourceها نسبت به Profiler دارد؛ بنابراین Serverهایی که Trace می‌شوند کمتر در معرض Performance Issue قرار می‌گیرند.
2. Distributed Replay می‌تواند Workload را از چند Server یا Client Capture و روی یک Host واحد Replay کند.

در یک Distributed Replay Topology باید یک Server به‌عنوان Controller پیکربندی شود. Controller کار میان Clientها و Target Server را Orchestrate می‌کند.

Distributed Replay Client

همان‌طور که توضیح داده شد، چند Client Server می‌توانند با هم Workloadی بسازند که روی Target Server Replay شود. Distributed Replay Client باید روی هر Serverی که می‌خواهید با Distributed Replay از آن Trace Capture کنید نصب شود.

SQL Client Connectivity SDK

Client Connectivity SDK یک SDK برای SQL Native Client جهت پشتیبانی Application Development فراهم می‌کند. همچنین Interfaceهای دیگری مانند پشتیبانی از Stack Tracing در Client Applicationها ارائه می‌دهد.

Master Data Services

Master Data Services ابزاری برای مدیریت Master Data در Enterprise است. اجازه می‌دهد Data Domainهایی را Model کنید که به Business Entityها نگاشت می‌شوند و آن‌ها را با Hierarchy، Business Rule و Data Versioning مدیریت کنید. با انتخاب این Feature چند Component نصب می‌شود:

• Web Console برای قابلیت‌های Administrative
• Configuration Tool برای پیکربندی MDM Databaseها و Web Console
• Web Service برای Extensibility توسط Developerها
• Excel Add-in برای ایجاد Entity و Attribute جدید

جمع‌بندی

برنامه‌ریزی Deployment می‌تواند فرایندی پیچیده باشد که نیازمند گفت‌وگو با ذی‌نفعان Business و Technical است تا Platform نیازمندی‌های Application و در نهایت Business را برآورده کند. عوامل زیادی باید در نظر گرفته شوند.

Edition مناسب SQL Server و ملاحظات Licensing مرتبط با آن را بررسی کنید. هنگام تصمیم‌گیری، Supportability کل Estate را ببینید و صرفاً نیازهای یک Application خاص را در نظر نگیرید. همچنین بررسی کنید آیا Azure Hosting برای Application مناسب است یا حتی Hybrid Approach شامل On-premise و Cloud گزینه بهتری است.

هنگام برنامه‌ریزی Deployment، Capacity Planning کامل انجام دهید. Hardware Requirementهای Application را نیز بررسی کنید. مقدار RAM و تعداد Processor Coreها مهم‌اند، اما شاید مهم‌ترین ملاحظه Storage باشد. SQL Server معمولاً IO-bound است و Storage اغلب Bottleneck می‌شود.

نیازمندی‌های سیستم‌عامل را نیز در نظر بگیرید. موضوع فقط انتخاب مناسب‌ترین نسخه Windows نیست، بلکه Configuration سیستم‌عامل نیز اهمیت دارد. صرف وجود یک Windows Gold Build به این معنا نیست که آن Build برای نصب SQL Server شما بهینه است.

در نهایت Featureهایی را که باید نصب شوند با دقت انتخاب کنید. بیشتر Applicationها فقط به Subset کوچکی از Featureها نیاز دارند؛ با انتخاب دقیق Featureها می‌توانید Security Footprint نصب و همچنین Management Overhead را کاهش دهید.