System Database

System Database

System Database 466 383 امین ظفرقندی

دیتابیس های سیستمی  ( System Databases ) چه دیتابیس هایی هستند؟ دیتابیس های سیستمی (System Databases) چه کاربردهایی دارند؟ تفاوت دیتابیس های معمولی با دیتابیس های سیستمی ( System Databases ) در چیست؟ اهمیت دسترسی به دیتابیس های سیستمی ( System Databases ) به چه میزان است؟ پاسخ تمامی این سوالات را در این مقاله خواهید یافت.

دیتابیس های سیستمی ( System Databases ) موارد زیر را شامل می شود:

master ذخیره اطلاعات در سطح سیستم
msdb برای زمانبندی job ها و alert ها توسط SQL Server Agent استفاده می شود
model خیلی از ویژگی های این دیتابیس بر روی دیتابیس هایی که بعد از آن ساخته می شوند، پیاده می گردد
tempdb برای نگهداری object های موقت یا نتایج میانی استفاده می شود

Master Database: تمامی اطلاعات مربوط به سیستم SQL در دیتابیس master نگهداری می شود، این اطلاعات شامل سطح وسیعی از metadata ها از جمله logon accounts، Endpoints، لینک سرور و تنظیمات ابتدایی سیستم است. همچنین موجودیت، محل قرارگیری فایل ها و اطلاعات اولیه باقی دیتابیس ها در دیتابیس Master نگهداری می شود. به همین دلیل، اگر دیتابیس Master در دسترس نباشد. سرور SQL  نمی تواند شروع به کار کند.

محدودیت ها: اجرای موارد زیر بر روی دیتابیس Master امکان پذیر نمی باشد:

  • اضافه کردن File یا Filegroup
  • تغییر Collation دیتابیس، پیش فرض collation سرور بر روی دیتابیس اعمال می شود
  • تغییر Owner دیتابیس، مالک دیتابیس “sa” است.
  • ساخت Full-Text Catalog یا  Full-Text Index
  • ساخت Trigger در جداول سیستمی (system tables) دیتابیس
  • حذف دیتابیس
  • حذف guest user از دیتابیس
  • فعال کردن Change Data Capture
  • شرکت در Database Mirroring
  • حذف primary filegroup، primary data file و log file
  • تغییر نام دیتابیس یا primary filegroup
  • Offline کردن دیتابیس
  • Read-Only کردن دیتابیس یا Primary Filegroup

> در رابطه با دیتابیس Master ، بهتر است به نکات زیر توجه کنیم:

  • همیشه آخرین بکآپ دیتابیس در دسترس باشد.
  • بعد از هر یک از سه عملیات زیر، بلافاصله از دیتابیس Master، بکآپ بگیریم:
  • 1-ساخت، اعمال تغییر و حذف هریک از دیتابیس های موجود
  • 2-تغییر در سطح سرور و تنظیمات دیتابیس
  • 3-ساخت اکانت جدید یا اعمال تغییر بر روی آنها
  • بهتر است که در دیتابیس، user object نسازیم، در این صورت بازه زمانی Backup گیری باید کمتر شود.
  • TRUSTWORTHY را بر روی دیتابیس فعال نکنیم.

اگر دیتابیس Master  غیر قابل استفاده شد، می توانیم هریک از موارد زیر را انجام دهیم:

  • آخرین بکآپ موجود را Restore کنیم. ( اگر توانسته باشیم سرور را start کنیم، امکان Restore کردن دیتابیس نیز وجود دارد.)
  • دیتابیس Master را کاملا Rebuild کنیم. (اگر آسیب در حدی بود که امکان start سرور SQL نبود، باید دیتابیس را دوباره بسازیم.)

نکته: با ساخت دوباره ی دیتابیس Master، باقی دیتابیس های سیستمی نیز ، دوباره از نو ساخته می شوند.

دیتابیس Model: به عنوان یک قالب و نمونه برای تمام دیتابیس های موجود در SQL Server مورد استفاده قرار می گیرد. از اونجا که هر موقع سرور SQL دوباره start شود، دیتابیس Tempdb  از نو ساخته می شود، دیتابیس Model باید همیشه در دسترس باشد. چون تمامی محتویات دیتابیس Model، از جمله تنظیمات آن، بر روی دیتابیس های جدید کپی می شود. در زمان restart سرور و ساخت مجدد Tempdb نیز این اتفاق می افتد.

به طور مثال برای Recovery Model تمام دیتابیس های جدید، همان Recovery Model دیتابیس Model انتخاب خواهد شد.

> وقتی دستور “CREATE DATABASE” اجرا می شود، بخش اول دیتابیس با کپی کردن محتویات دیتابیس Model شکل می گیرد، باقی آن با Page های خالی پر می شود.

> هر تغییری روی دیتابیس Model اعمال شود، بر روی تمامی دیتابیس هایی که بعد از آن ساخته می شوند نیز اعمال خواهد شد. البته مشخصات فایل (File Properties) استثتا هستند و به غیر از سایز اولیه datafile، باقی مشخصات نادیده گرفته می شوند. سایز اولیه پیش فرض برای Logfile و Datafile دیتابیس 8MB است.

محدودیت ها: اجرای موارد زیر بر روی دیتابیس Model امکان پذیر نمی باشد:

  • اضافه کردن File یا Filegroup
  • تغییر Collation دیتابیس، پیش فرض collation سرور بر روی دیتابیس اعمال می شود
  • تغییر Owner دیتابیس، مالک دیتابیس “sa” است
  • حذف دیتابیس
  • حذف guest user از دیتابیس
  • فعال کردن Change Data Capture
  • شرکت در Database Mirroring
  • حذف primary filegroup، primary data file و log file
  • تغییر نام دیتابیس یا primary filegroup
  • Offline کردن دیتابیس
  • Read-Only کردن دیتابیس یا Primary Filegroup
  • ایجاد procedure، view یا Trigger با استفاده از قابلیت رمزنگاری (Encryption)

> وقتی در یک دیتابیس، objectی با استفاده از قابلیت رمزنگاری ایجاد می شود، Enctyption Key به همان دیتابیس متصل خواهد بود، بنابراین object های رمزنگاری شده ای هم که در دیتابیس Model ساخته شده اند فقط در همین دیتابیس قابل استفاده هستند.

دیتابیس MSDB: دیتابیس کاربردی برای SQL Server Agent در جهت زمانبندی Job ها و Alert ها و همچنین برای Feature های دیگر SQL از جمله DB Mail، Service Broker و Management Studio می باشد.

> به طور مثال تمام تاریخچه Backup و Restore، در جداول دیتابیس msdb نگهداری می شود. این اطلاعات شامل اسم کاربری که Backup را اجرا کرده، زمان اجرا و مکان یا فایل ذخیره شده  Backup می باشد. SQL Server Management Studio از این اطلاعات در جهت ارائه plan برای Restore کردن دیتابیس و apply کردن Transaction Log Backup ها استفاده می کند. رویداد ها (Backup events)  برای تمامی دیتابیس ها ثبت می شود حتی اگر توسط برنامه ها یا application های ThirdParty ایجاد شده باشد. به طور مثال اگر از طریق Visual Basic Application یک object در sql  جهت اجرای Backup ی ، Call شود. این اتفاق در جداول سیستمی msdb، Windows Application log و SQL Server error log ثبت می شود.  برای محافظت از اطلاعات موجود در msdb، توصیه می شود Log Transaction های دیتابیس در storage مقاومی ذخیره شود.

> به طور پیش فرض، Recovery Model دیتابیس simple است  امّا اگر از جداول Backup and Restore History استفاده می کنیم، توصیه می شود که Recovery Model  را Full انتخاب کنیم.

توجه داشته باشید هر موقع که SQL Server را نصب یا Upgrade کنیم یا هر زمان که System Database ها را Rebuild  کنیم. Recovery Model  دیتابیس دوباره simple می شود.

توصیه می شود بعد از هر عملیاتی که منجر به update دیتابیس msdb شود مثل Backup و Restore هر یک از دیتابیس ها و … ، از دیتابیس backup گرفته شود.

محدودیت ها: اجرای موارد زیر بر روی دیتابیس MSDB امکان پذیر نمی باشد:

  • تغییر Collation دیتابیس، پیش فرض collation سرور بر روی دیتابیس اعمال می شود
  • حذف دیتابیس
  • حذف guest user از دیتابیس
  • فعال کردن Change Data Capture
  • شرکت در Database Mirroring
  • حذف primary filegroup، primary data file و log file
  • تغییر نام دیتابیس یا primary filegroup
  • Offline کردن دیتابیس
  • Read-Only کردن دیتابیس یا Primary Filegroup

دیتابیس Tempdb:  دیتابیس سیستمی Tempdb یک منبع   Global برای تمامی کاربران متصل به SQL Server است و برای نگهداری موارد زیر استفاده می شود:

  • User object های موقتی که مستقیما ساخته می شوند از جمله: جداول و index های local یا global، stored procedure ها ، جداول variable ها، Table-Valued Function ها و …
  • Object های داخلی (Internal) که توسط Database Engine ساخته می شوند که شامل موارد زیر هستند:
  • Work Table هایی که جهت ذخیره سازی نتایج میانی برای Cursor ها ، spool ها، sort کردن و حافظه LOB استفاده می شود.
  • Work File هایی که برای عملیات hash join یا hash aggregate استفاده می شوند.
  • نتایج میانی یا واسط sort که برای یکسری عملیات از قبیل rebuild یا ساخت index ها و همچنین در  query هایی که شامل Union و Order BY و Group BY هستند استفاده می شود.

      نکته: هر Internal Object، حداقل 9 Page را درگیر می کند. (page: کوچکترین واحد ذخیره سازی data در SQL)

  • Version Stores متشکل از مجموعه ای از data page ها هستند. این page  ها خود محل ذخیره ی ردیف هایی از داده ها هستند که در جهت پشتیبانی از Row Versioning استفاده می شوند. 2 نوع Version Store وجود دارد: Common Version Store و Online-index-build version store

 Version Store ها شامل:

  • Row Version هایی که توسطTransaction های تغییر داده  (Data modification)  در یکسری از دیتابیس ها ساخته می شوند که در این دیتابیس ها عملیات خواندن از طریق row versioning isolation  یا snapshot isolation انجام می شود.
  • Row Version هایی که توسطTransaction های تغییر داده (Data modification) برای یکسری از feature ها از قبیل online index،Multiple Active Results Sets (MARS)  و AFTER Trigger ها ساخته می شود.

> هر زمان که سرور SQL دوباره start شود، دیتابیس Tempdb از نو ساخته می شود. در صورت قطعی، جداول و stored procedure های موقت drop می شوند و وقتی سیستم خاموش شود، هیچ ارتباطی active نخواهد بود. بنابراین در دیتابیس tempdb، هیچ وقت هیچ چیزی برای save شدن از یک session به session دیگر وجود ندارد. عملیات Backup  و Restore بر روی دیتابیس Tempdb امکان پذیر نمی باشد.

تعداد فایل های دیتابیس بستگی به تعداد پردازنده های منطقی (Logical Processor) ماشین دارد. به عنوان یک قانون کلی، اگر تعداد processor های منطقی کمتر یا برابر با 8 باشد، همان تعداد را برای datafile ها در نظر می گیریم. اگر تعداد processor ها بیشتر از این مقدار باشد، 8 تا datafile خواهیم داشت.  اگر تداخل  و درگیری ادامه داشت، تعداد  datafile ها رو به صورت مضربی از 4 افزایش می دهیم  تا زمانی که درگیری تا حد قابل قبولی کم شود یا تغییری در workload ها ایجاد شود.

محدودیت ها: اجرای موارد زیر بر روی دیتابیس Tempdb امکان پذیر نمی باشد:

  • اضافه کردن FileGroup
  • Backup و Restore
  • تغییر Collation دیتابیس، پیش فرض collation سرور بر روی دیتابیس اعمال می شود
  • تغییر Owner دیتابیس، مالک دیتابیس “sa” است
  • ایجاد یک snapshot از دیتابیس
  • حذف دیتابیس
  • حذف guest user از دیتابیس
  • فعال کردن Change Data Capture
  • شرکت در Database Mirroring
  • حذف primary filegroup، primary data file و log file
  • تغییر نام دیتابیس یا primary filegroup
  • اجرای دستور DBCC CHECKALLOC
  • اجرای دستور DBCC CHECKCATALOG
  • Offline کردن دیتابیس
  • Read-Only کردن دیتابیس یا Primary Filegroup

 

دسترسی

هر کاربری می تواند object موقت در دیتابیس ایجاد کند. کاربرها فقط می توانند به object های خودشون دسترسی داشته باشند، مگر اینکه permission یا امتیازی گرفته باشند.  این امکان وجود دارد که جلوی دسترسی و ارتباط کاربر با Tempdb گرفته شود. اما این کار اصلا توصیه نمی شود، چون خیلی از عملیات معمول از این دیتابیس استفاده می کنند.

بهینه سازی عملکرد دیتابیس Tempdb

> سایز و مکان فیزیکی دیتابیس می تواند بر عملکرد سیستم تاثیر بگذارد. به طور مثال اگر سایز دیتابیس خیلی کوچک باشد، هر زمان که سرور SQL ، start می شود،  بخشی از system-processing با قابلیت auto growing دیتابیس گرفته می شود تا میزان بار (workload) موجود را پشتیبانی کند.

> بهتره از ابتدا حجمی تقریبا زیاد و کافی برای دیتابیس تخصیص داده شود که بتواند workload معمول دیتابیس را در خود جای دهد.  از پیش تعیین کردن سایز مناسب از گسترش مداوم فایل های دیتابیس که روی عملکرد سیستم تاثیر منفی دارد جلوگیری می کند. البته Tempdb باید روی autogrowth، set شود اما در مواردی غیر منتظره و از پیش برنامه ریزی نشده ای که سایز دیسک هم افزایش داشته باشد.

> از آنجا که SQL از الگوریتم Proportional-fill استفاده می کند، Datafile های یک Filegroup باید سایز برابر داشته باشند و اینجوری فضای آزاد بیشتری در فایل ها خواهیم داشت. تقسیم Tempdb به چند datafile با سایز برابر ، سطح بالا و موثری از اجرای موازی (Parallel) operation ها را به همراه خواهد داشت.

> برای file growth (میزان رشد فایل) بهتره یک مقدار منطقی رو در نظر بگیریم تا از رشد دیتابیس با سایز کم و فاصله های زمانی کوتاه جلوگیری شود. اگر این مقدار خیلی کم باشد، آن را با میزان داده ی ورودی دیتابیس مقایسه کنید.  دیتابیسی که مجبور باشد دائما فایل های خود را گسترش بدهد، روی عملکرد سیستم تاثیر می گذارد.

> دیتابیس  را در جایی با I/O پرسرعت قرار دهید. اگر دیسک های زیادی داریم که مستقیما attach شده اند، بهتره که از Disk Striping استفاده کنیم. لزومی ندارد که فایل های Tempdb روی دیسک های مختلفی قرار داده شود. اما بهتره که دیتابیس را در دیسکی مجزا از دیتابیس های کاربران قرار دهیم.

 

منبع: https://docs.microsoft.com

ترجمه مقاله : مهندس الهه علی پور