Trace Flag 3625

SQL Server کنترل سخت‌گیرانه‌ای بر نمایش Metadata اعمال می‌کند. User فقط Metadata مربوط به Objectهایی را می‌بیند که مالک آن‌هاست یا صراحتاً Permission مشاهده Metadata را دارد. بااین‌حال یک مهاجم ماهر ممکن است با دست‌کاری ترتیب تقدم در Queryها Error Messageهایی ایجاد کند که اطلاعاتی از Metadata را فاش کنند.

برای کاهش این ریسک می‌توان Trace Flag 3625 را فعال کرد. این Flag مقدار Metadata قابل مشاهده در Error Message را محدود و بخشی از Data را با Asterisk Mask می‌کند. عیب آن این است که Error Messageها معنای کمتری دارند و Troubleshooting سخت‌تر می‌شود.

Portها و Firewallها

در Topologyهای سازمانی مدرن SQL Server معمولاً باید از دست‌کم دو Firewall عبور کند: Hardware Firewall و Windows Firewall یا Local Firewall. برای اینکه Instance بتواند با Applicationها یا Instanceهای دیگر در Network ارتباط برقرار کند و در عین حال Security Firewall حفظ شود، Portهای لازم باید باز شوند.

فرایند ارتباط

برای دانستن اینکه چه Portهایی باید باز شوند ابتدا باید شیوه ارتباط Client با SQL Server را درک کرد. Figure 5-7 Flow ارتباط TCP/IP را برای Instance‌ای که روی Port 1433 Listen می‌کند و Clientی با Windows Vista/Windows Server 2008 یا بالاتر نشان می‌دهد.

اگر Client به‌جای TCP/IP از Named Pipes استفاده کند، SQL Server روی Port 445 ارتباط برقرار می‌کند؛ همان Portی که File and Printer Sharing نیز استفاده می‌کند.

Figure 5-7 — جریان فرایند ارتباط.

Portهای موردنیاز SQL Server

در نصب Default Instance، Setup به‌طور خودکار Port 1433 را تعیین می‌کند؛ Port ثبت‌شده SQL Server در IANA. بسیاری از DBAها برای لایه‌ای از Obfuscation و کاهش حمله روی Well-known Port، آن را تغییر می‌دهند. در Estate کوچک این کار می‌تواند مفید باشد، اما در Enterprise بزرگ باید اثر آن بر Operational Supportability سنجیده شود؛ مثلاً اگر هر Instance Port متفاوت دارد باید Inventory دقیقی برای یافتن سریع Port در صورت خرابی Browser Service وجود داشته باشد.

Named Instance به‌طور پیش‌فرض Dynamic Port می‌گیرد. هر بار Instance Start می‌شود از Operating System Portی تصادفی در Dynamic Range درخواست می‌کند. در Windows Server 2008 و بالاتر این Range برابر 49152 تا 65535 است؛ در نسخه‌های قدیمی Windows از 1024 تا 5000 بود. تغییر Range از Windows Vista/Server 2008 برای هم‌خوانی با IANA انجام شد.

Dynamic Port پیکربندی Firewall را دشوار می‌کند. Windows Firewall می‌تواند Service مشخصی را روی هر Port مجاز کند، اما Hardware Firewall معمولاً چنین انعطافی ندارد و ناچار باید کل Dynamic Range به‌صورت Bidirectional باز بماند. بنابراین نویسنده توصیه می‌کند Instance از Port مشخص و Static استفاده کند.

SQL Server برای Featureهای مختلف Portهای دیگری نیز استفاده می‌کند. Portهای احتمالی موردنیاز Database Engine در جدول 5-5 آمده‌اند. Featureهای خارج از Database Engine مانند SSAS و SSRS و سرویس‌های مکمل مانند IPSec، MSDTC یا SCOM نیازهای دیگری دارند.

پیکربندی Portی که Instance روی آن Listen می‌کند

برای Named Instance معمولاً قبل از Firewall یک Static Port تعیین می‌شود. در SQL Server Configuration Manager از مسیر SQL Server Network Configuration → Protocols for INSTANCENAME وارد Properties مربوط به TCP/IP شوید. در تب Protocol گزینه Listen All به‌طور پیش‌فرض Yes است؛ اهمیت آن در ادامه روشن می‌شود.

در تب IP Addresses تنظیمات چند IP دیده می‌شود، اما وقتی Listen All برابر Yes باشد SQL Server همه آن‌ها را نادیده می‌گیرد و فقط Settingهای IP All در پایین Dialog را استفاده می‌کند. فیلد TCP Dynamic Ports Port تصادفی اختصاص‌یافته توسط OS را نشان می‌دهد و TCP Port خالی است. برای Static Port باید TCP Dynamic Ports را خالی و TCP Port را با Port انتخابی، در این مثال 1433، پر کرد. برای اعمال تغییر SQL Server Service باید Restart شود.

Figure 5-8 — تب Protocol.

همین کار با PowerShell نیز ممکن است. Listing 5-9 دو Variable برای Instance Name و Port دارد و در صورت نیاز می‌توان آن‌ها را Parameterize کرد. Script Assembly مربوط به SMO WMI را Load می‌کند، Object جدید SMO می‌سازد، Dynamic Port را غیرفعال و Static Port را تنظیم می‌کند. Script باید با Permission Administrator اجرا شود.

Listing 5-9 — اختصاص Static Port

# Initialize variables
$Instance = "PROSQLADMIN"
$Port = "1433"

Figure 5-9 — تب IP Addresses.

ادامه Listing 5-9

# Load SMO Wmi.ManagedComputer assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
# Create a new smo object
$m = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer')
#Disable dynamic ports
$m.ServerInstances[$Instance].ServerProtocols['Tcp'].IPAddresses['IPAll'].IPAddressProperties['TcpDynamicPorts'].Value = ""
# Set static port
$m.ServerInstances[$Instance].ServerProtocols['Tcp'].IPAddresses['IPAll'].IPAddressProperties['TcpPort'].Value = "$Port"
# Reconfigure TCP
$m.ServerInstances[$Instance].ServerProtocols['Tcp'].Alter()

System Databaseها

SQL Server پنج System Database دارد که هرکدام برای کارکرد صحیح Instance حیاتی‌اند. در ادامه نقش و ملاحظات Configuration آن‌ها بررسی می‌شود.

mssqlsystemresource (Resource)

نام کامل Resource Database برابر mssqlsystemresource است و Repository فیزیکی System Objectهایی است که در Schema برابر sys هر Database دیده می‌شوند. Read-only است و جز با راهنمایی Microsoft نباید تغییر کند. در Management Studio دیده نمی‌شود و اتصال مستقیم از Query Window، مگر در Single-user Mode، شکست می‌خورد. ملاحظه Configuration خاصی برای Resource وجود ندارد.

MSDB

MSDB Repository مربوط به Metadata بسیاری از Featureهای SQL Server از جمله Server Agent، Backup/Restore، Database Mail، Log Shipping، Policyها و موارد دیگر است. Configuration خاصی ندارد، اما در Instance بسیار بزرگ با Databaseهای زیاد و Log Backupهای مکرر می‌تواند بسیار بزرگ شود. در این حالت باید Data قدیمی Purge و گاهی Indexing Strategy بررسی شود. History مربوط به Backup با sp_deletebackuphistory یا History Cleanup Task در Maintenance Plan قابل پاک‌سازی است.

Master

Master Metadata مربوط به Objectهای Instance-level مانند Loginها، Linked Serverها، TCP Endpointها و Master Key/Certificateهای Encryption را نگهداری می‌کند. مهم‌ترین ملاحظه برای Master، Backup Policy است. لازم نیست به دفعات User Database Backup شود، اما باید Backup فعلی داشته باشید. حداقل پس از ایجاد یا تغییر Login، Linked Server، System Configuration، Key/Certificate یا پس از ایجاد/حذف User Database از Master Backup بگیرید. بسیاری Weekly Full Backup انتخاب می‌کنند، ولی Frequency باید با نیاز عملیاتی هماهنگ شود.

از نظر فنی می‌توان User Object در Master ساخت، اما Bad Practice است؛ زیرا Storage مربوط به User Objectها را پراکنده می‌کند و Frequency Backup Master را بالا می‌برد.

Model

Model Template همه Databaseهای جدید Instance است. پیکربندی درست آن می‌تواند زمان را کم و Human Error را کاهش دهد. مثلاً اگر Recovery Model در Model برابر Full باشد، User Databaseهای جدید نیز به‌طور پیش‌فرض Full می‌شوند؛ البته در CREATE DATABASE قابل Override است. Objectهایی مانند Maintenance Stored Procedure یا Database Role که باید در همه Databaseهای جدید باشند، اگر در Model ساخته شوند خودکار در Databaseهای جدید ایجاد می‌شوند. Model همچنین برای ساخت TempDB در هر Startup استفاده می‌شود؛ بنابراین Objectهای Model بعد از Restart در TempDB نیز ظاهر می‌شوند.

TempDB

TempDB Workspace ساخت Objectهای موقت SQL Server است. علاوه بر Temporary Tableهای User، Table Variableها نیز Objectی در TempDB ایجاد می‌کنند و Data آن‌ها در صورت عبور از Size Threshold روی Disk Spool می‌شود. بسیاری از عملیات داخلی نیز به Temp Object نیاز دارند، از جمله Sort و Spool Data، Hashing برای Join و Aggregate Grouping.

• Online Index Operation
• Index Operationهایی که Result را در TempDB Sort می‌کنند
• Triggerها
• DBCC Commandها
• OUTPUT Clause در DML
• Row Versioning برای Snapshot Isolation، Read Committed Snapshot Isolation، MARS و موارد مشابه

به دلیل حجم بالای وظایف، TempDB در Instanceهای پرتراکنش Throughput بسیار زیادی دارد و از System Databaseهایی است که بیشترین توجه Configuration را می‌طلبد.

اولین موضوع Size TempDB است. برای Instanceهای بزرگ یا Highly Transactional بهتر است Capacity Planning انجام شود. یک رویکرد عملی این است که روی Test Server، User Databaseها تا اندازه پیش‌بینی‌شده رشد داده شوند، Representative Workload اجرا و مصرف TempDB Monitor شود. Taskهای Administrative مانند Rebuild Index نیز باید روی Databaseهای بزرگ‌شده اجرا شوند تا Profile مصرف TempDB در این فعالیت‌ها سنجیده شود. DMVهای مفید برای این کار در جدول 5-6 آمده‌اند.

بهینه‌سازی TempDB

علاوه بر Size، تعداد Fileهای TempDB نیز بسیار مهم است. File خیلی کم می‌تواند به دلیل Create/Drop سریع Objectها Contention روی GAM و SGAM ایجاد کند. File خیلی زیاد نیز Synchronization Overhead را بالا می‌برد، زیرا SQL Server برای Proportional Fill باید Allocation Weight هر File را مدیریت کند.

توصیه عمومی فعلی این است که به ازای هر Core قابل استفاده برای Instance یک TempDB Data File، با حداقل 2 و حداکثر 8 File، داشته باشید. فقط زمانی بیش از 8 File اضافه کنید که واقعاً GAM/SGAM Contention را به شکل PAGELATCH Wait روی TempDB مشاهده کرده باشید.

SQL Server 2019 بهینه‌سازی جدیدی به نام Memory-Optimized TempDB Metadata معرفی می‌کند. System Tableهای مدیریت Metadata TempDB در Non-durable Memory-optimized Tableها نگهداری می‌شوند.

این Feature با حذف Bottleneck مربوط به System Pageهای TempDB Scalability را افزایش می‌دهد، اما هزینه و محدودیت دارد: یک Transaction نمی‌تواند هم‌زمان به Memory-optimized Tableهای چند Database دسترسی داشته باشد. بنابراین Scriptهای Monitoring سفارشی ممکن است دچار مشکل شوند.

Listing 5-10 نمونه‌ای می‌سازد که Database به نام Chapter5، یک Memory-optimized Filegroup و Tableای به نام TempTableCount دارد. Procedure برابر CaptureTempTableCount تعداد Temp Tableها را در آن Table ثبت می‌کند؛ فرض کنید SQL Server Agent آن را هر دقیقه اجرا کند و Result برای Capacity Planning استفاده شود.

Listing 5-10 — استفاده از Memory-Optimized Table همراه TempDB

--Create The Chapter5 Database
CREATE DATABASE Chapter5
GO
USE Chapter5
GO
--Add a memory-optimized filegroup
ALTER DATABASE Chapter5 ADD FILEGROUP memopt
    CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE Chapter5 ADD FILE (
    name='memopt1', filename='c:\data\memopt1'
) TO FILEGROUP memopt ;
ALTER DATABASE Chapter5
    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON ;
GO
CREATE TABLE TempTableCount (
   ID                INT                IDENTITY(1,1)    NOT NULL
   PRIMARY KEY NONCLUSTERED,
   TableCount   INT               NOT NULL,
   DateTime     DateTime2         NOT NULL
) WITH(MEMORY_OPTIMIZED=ON) ;
GO

ادامه Listing 5-10

CREATE PROCEDURE CaptureTempTableCount
AS
BEGIN
    BEGIN TRANSACTION
          INSERT INTO TempTableCount (TableCount, DateTime)
          SELECT COUNT(*) As TableCount, SYSDATETIME() AS DateTime
          FROM tempdb.sys.tables t
          WHERE type = 'U'
    COMMIT
END
GO

Procedure در حالت عادی درست کار می‌کند. حال Memory-Optimized TempDB Metadata را با Listing 5-11 فعال کنید.

Listing 5-11 — فعال‌کردن Memory-Optimized TempDB Metadata

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON ;

بعد از فعال‌سازی، Procedure شکست می‌خورد چون Transaction تلاش می‌کند به Memory-optimized Table در چند Database دسترسی داشته باشد. راه‌حل این است که برای Table مقصد از Disk-based Table استفاده شود.

Buffer Pool Extension

Buffer Pool ناحیه Memory برای Cache کردن Pageها قبل از نوشتن روی Disk و بعد از خواندن از Disk است. دو نوع Page در Buffer Cache داریم: Clean و Dirty. Clean Page هنوز Modify نشده و معمولاً به دلیل Read وارد Cache شده است. DML می‌تواند آن را تغییر دهد و Marker آن را Dirty کند.

Dirty Page توسط INSERT/UPDATE/DELETE یا عملیات دیگر Modify شده است. ابتدا Log Record مربوط باید روی Disk نوشته شود و سپس Dirty Page به Disk Flush می‌شود تا دوباره Clean محسوب شود. نوشتن Log قبل از Data به نام WAL یا Write-Ahead Logging شناخته می‌شود.

Dirty Page تا زمان Flush در Cache می‌ماند. Clean Page تا حد ممکن در Cache نگهداری می‌شود، اما وقتی فضا برای Page جدید لازم باشد طبق Least Recently Used Policy Evict می‌شود. Workloadهای Read-intensive در صورت کوچک‌بودن Buffer Cache می‌توانند سریعاً Memory Pressure ایجاد کنند.

RAM نسبت به Storage گران است و همیشه نمی‌توان مشکل را با Memory بیشتر حل کرد. Microsoft در SQL Server 2014 فناوری Buffer Pool Extension را معرفی کرد. این Extension برای SSD بسیار سریع و معمولاً Local Attached طراحی شده و Secondary Cache فقط برای Clean Pageهاست. وقتی Clean Page از Buffer Cache Evict می‌شود به Extension منتقل می‌شود تا بازیابی آن از Main IO Subsystem سریع‌تر باشد.

این Feature مفید است اما Magic Bullet نیست. هیچ Extensionی Performance برابر Buffer Cache با RAM کافی نمی‌دهد و فایده آن کاملاً Workload-specific است. OLTP Read-intensive احتمالاً سود زیادی می‌برد، اما Write-intensive چون Dirty Page وارد Extension نمی‌شود فایده کمی دارد. Data Warehouse بسیار بزرگ نیز معمولاً سود چشمگیری نمی‌برد، زیرا Full Table Scan می‌تواند Cache و Extension را پر و Data قبلی را Evict کند.

بهتر است SSD Volume مقاوم مانند RAID 10 باشد. اگر Volume بدون Resilience Fail کند Performance ناگهان افت می‌کند. اگر Drive حاوی Extension Fail شود، SQL Server خودکار Extension را Disable می‌کند و می‌توان آن را دستی یا با Restart دوباره Enable کرد.

برای Performance مناسب، نویسنده اندازه Extension را بین 4 تا 8 برابر Max Server Memory توصیه می‌کند. حداکثر اندازه مجاز 32 برابر Max Server Memory است. Listing 5-12 فرض می‌کند SSD روی Drive برابر S: قرار دارد، Max Server Memory برابر 32GB است و Extension برابر 128GB یعنی چهار برابر تنظیم می‌شود.

Listing 5-12 — فعال‌کردن Buffer Pool Extension

ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME = 'S:\SSDCache.BPE', SIZE = 128 GB )

در صورت نیاز Extension با Listing 5-13 Disable می‌شود، اما حذف آن احتمالاً افت ناگهانی Performance ایجاد می‌کند.

Listing 5-13 — غیرفعال‌کردن Buffer Pool Extension

ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF

Hybrid Buffer Pool

SQL Server 2019 با Hybrid Buffer Pool از PMEM یا Persistent Memory که SCM نیز نامیده می‌شود پشتیبانی می‌کند. PMEM روی Memory Bus قرار می‌گیرد، Solid-state و Byte-addressable است، از Flash سریع‌تر و از DRAM ارزان‌تر است و Data آن بعد از خاموش‌شدن Server باقی می‌ماند.

در Windows Server 2016 یا بالاتر، هنگام Format کردن PMEM برای Hybrid Buffer Pool باید DirectAccess فعال باشد و در Windows Server 2019 Allocation Unit Size برابر 2MB یا در سایر نسخه‌ها بزرگ‌ترین Size موجود انتخاب شود. پس از ایجاد Drive می‌توان SQL Server Transaction Log را روی Device قرار داد. SQL Server هنگام خواندن Clean Page از Buffer Pool از Memory-mapped IO یا Enlightenment استفاده می‌کند و نیاز به Copy کردن Page به DRAM را کاهش می‌دهد؛ در نتیجه IO Latency کمتر می‌شود.

Enlightenment فقط برای Clean Page قابل استفاده است. اگر Page Dirty شود ابتدا به DRAM نوشته و سپس به PMEM Flush می‌شود. برای فعال‌کردن PMEM Support در SQL Server روی Windows، Trace Flag 809 و روی Linux، Trace Flag 3979 باید در SQL Server Service فعال باشد.

جمع‌بندی

پیکربندی Processor و Memory باید متناسب با Workload انجام شود. Processor Affinity می‌تواند برای چند Instance یا کاهش Context Switching مفید باشد و MAXDOP نیز باید متناسب با محیط تنظیم شود.

در Memory باید Min/Max Server Memory، امکان برابرکردن آن‌ها و استفاده احتمالی از Buffer Pool Extension بررسی شود. اگر Extension استفاده می‌شود، Max Server Memory مبنای Size Cache گرم است.

Trace Flagها Behavior را Toggle می‌کنند و قرار دادن آن‌ها در Startup Parameter تضمین می‌کند Configuration پس از Restart باقی بماند. Flag 3226 نمونه‌ای با کاربرد عمومی برای Suppress کردن Messageهای Backup موفق است.

برای ارتباط Network باید Instance Port و Local Firewall درست تنظیم شوند. استفاده از Dynamic Port برای SQL Server Connection عموماً Bad Practice است و بهتر است TCP Port مشخص داشته باشید.

هر پنج System Database برای کارکرد Instance حیاتی‌اند، اما از نظر Configuration بیشترین توجه معمولاً به TempDB لازم است. TempDB توسط Featureهای بسیاری با Throughput بالا استفاده می‌شود و باید تعداد File و Size آن مناسب باشد.

Uninstall کردن Instance یا حذف Featureها از Control Panel یا Command Line امکان‌پذیر است. پس از Uninstall ممکن است Residueهایی در File System و Registry باقی بماند.