SQL Server Administration

[Reza_Yarahmadi]

به نام خدا

با توجه به اینکه تاپیک جامعی در زمینه SQL Server Administration در سایت وجود ندارد بر آن شدم که این تاپیک را ایجاد کنم و مطالبی در حد توانم را ارائه دهم تا مرجع تقریبا کاملی برای یادگیری این موضوع باشد.
قبل از شروع آموزش لازم است که چند نکته را یادآور شوم:


1) به دلیل اینکه اموزش به صورت متن می باشد، طبیعتا سرعت آن نسبت به کلاس های حضوری کمتر خواهد بود. چون مجبور خواهم بود که علاوه بر جهت جمع آوری مطالب، عکس های مربوطه را نیز مهیا کنم. پس خواهشا صبر به خرج دهید و انتظار تکمیل شدن این آموزش را در چندین روز نداشته باشید. انشاءالله بتوان یک منبع خوب فارسی در این زمینه داشته باشیم.

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

3) به دلیل برنامه ریزی هایی که انجام شده، مطالب به صورت سلسله وار و از مباحث مبتدی شروع میشه و به تدریج مباحث پیشترفته تدریس خواهند شد. چون این جا یک مکان عمومی است و هر برنامه نویسی با هر سطحی ممکن است، وجود داشته باشد. پس دوستانی که بر مباحث ابتدایی اشراف دارند، می توانند این تاپیک را در زمانی که به مباحث پیشترفته تر رسید، دنبال کنند.

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

5) سعی خواهد شد که پس از پایان هر قسمت، مطالب در قالب یک فایل pdf نیز در اختیار دوستان قرار بگیرد.

6) چون خیلی از دوستان (از جمله خودم) به هر دلیلی مجبور به استفاده از SQL Server 2005 هستند این آموزش بر پایه 2005 انجام می شود. انشاءالله پس از تکمیل این آموزش به سراغ ورژنهای بالاتر خواهم رفت و امکانات اضافه شده و یا تغییر کرده آنها نسبت به ورژنهای قبل را مورد بررسی قرار خواهم داد.

[Reza_Yarahmadi]

امكانات SQL Server 2005

پيش زمينه اي براي نصب SQL Server 2005

نصب SQL Server 2005

آشنايي با ساختار Database در SQL Server 2005

Database Partitioning

• Partition در SQL Server 2000
• Partition در SQL Server 2005
• روش ايجاد Partition
• روش Partition كردن دیتابیس

Transaction Log و معماري Log

• Write-Ahead Transaction Log
• معماری منطقی فایلهای Transaction Log
• Checkpoints

Transaction Log و معماري Log

• Truncating the Transaction Log
• مكانيزم Transaction

Attach & Detach & Shrink

• Detach و Attach کردن دیتابیس
• Shrink کردن دیتابیس و فایل
• معماری فیزیكی Transaction Log
• Shrinking

Backup and Restore

• شناخت فاكتورهاي مهم براي Backup گيري مناسب
• Recovery Model
• انواع Backup ها
• Full Backup - Complete Backup
• Deferential Backup
• Transaction Log Backup
• تعاريف پايه ايي براي Backup
• Backup Device

Backup and Restore

• تهيه Backup

Backup and Restore

• (Backup Mirroring (Backup Family
• روش Backup گيري با URL
• Partial Backup
• Backup گيري با دستور T-SQL

Backup and Restore

• Restore كردن دیتابیس
• Restore كردن دیتابیس با استفاده از T-SQL

Database Snapshot

• Database Snapshot
• ايجاد Snapshot Database
• خواندن اطلاعات از Snapshot Database
• بازيابي Databمحدوديت هاي Snapshot Database براي دیتابیس اصلي
• محدوديت هاي Snapshot Database
• ase ازروي Database Snapshot

Automate Administrative Tasks

• SQL Server Agent
• Job
• ايجاد Job
• Schedule كردن Job

SQL Server Tuning

• مفاهيم Tuning
• ابزارهاي Tuning
• معرفي Profiler
• نحوه كار برنامه Profiler

[Reza_Yarahmadi]

Table and Index Partitioning
توزيع Data هاي جداول و Index ها روي چندين فايل بصورتي كه در ساختار منطقي Database Objectها اختلالي پيش نمي آيد.

Attach Rebuild Log
اين امكان براي بازسازي Database ها بدون احتياج به Log File را مقدور مي سازد. براي استفاده از اين امكان بايد محدوديت هاي آنرا بدانيم .

Disabling Index
غير فعال كردن Index در زمان Administration بسيار مهم است. در بسياری از زمانها ممكن است بازسازي جداول به بازسازي Indexها منجر شود كه ممكن است باعث كندي در فرايند بازسازي شود .

Maintenance Plan
امكان ايجاد Work Flow و قابليت هاي جديد براي بهبود كارايي Database

View هاي جديد براي دسترسي به Meta Data ها
مجموعه ايي از Viewها مي باشند كه با .Sys شروع شده و Mete Data هاي يك Database را نشان مي دهند.

Profiler براي بررسي SQL Server
ابزاري براي بررسي رفتار SQL Server مي باشد. امكانات بيشتري براي Trace SQL Server در اختيار كاربر قرار مي دهد.

Script Generation and Documentation
با كمك اين ابزار مي توان از اشياي Database يك مجموعه Script كاربردي ايجاد كرد.

Security و مفهومي جديد به نام Schemas
لايه جديد مديريت Security SQL Server به نام Schema مي باشد . اين لايه بين Log in و User هاي يك Database ارتباط Security را برقرار مي كند.

ابزار جديد براي Replication Monitoring
• رفع خطاهاي نسخ قديمي Replication
• مشخص كردن ترتيبي براي Article ها در زمان انتشار اطلاعات
• مديريت صحيح در Delete شدن Record ها

Business Intelligence Development Studio
SQL Server Management Studio
SQL Server Configuration Manager
SQL Server Import and Export
Integration Services Designer


SQL Server 2005 روي Server هاي 64 بيتي و 32 بيتي
راه اندازي SQL Server 2005 روي Server ها با ساختار 46 بيتي و 32 بيتي و امكان Tuning براي اين دو ساختار وجود دارد.

Failover Clustering
امكان را ه اندازي MS SQL Server روي محيطهاي Clustered براي Windows هاي 32 بيتي و 46 بيتي در SQL Server 2005 وجود دارد.

Multi Instance Support
امكان استفاده چندين SQL Server روي يك Server و تركيبي از نسخه هاي 2000 , 2005

Dedicate Administrator Connection
براي Administrator در زمانهايي كه مي خواهد Maintenance كند و كاربران نبايد به Server متصل شوند ، يك Connection مستقل برقرار مي شود.

Backup and Restore Checksum
امكاني مفيد براي تهيه Backup مطمئن از Database و كنترل Backup از بابت صحت ساختار Database و Media ثبت Backup در زمان Backup گيري را شامل مي شود.

AWE Option
Address Windowing Extensions امكاني از Windows است كه قابليت مديريت حافظه را براي مقادير بيشتر از 4 گيگابايت را دارد و براي افزايش كارايي SQL Server مورد استفاده قرار مي گيرد.

Hot Add Memory
براي برخي از Serverها اين امكان وجود دارد كه در زماني كه Server روشن است Memory را كم یا زياد كنيم.

Database Snapshot
با اين امكان مي توان Databaseايي دقيقا با ساختار Database جاري ساخت. اين امكان براي تهيه گزارشات بسيار موثر بوده ولي كاملا Read Only مي باشد.

Online Restore
براي Database هاي Online مي توان File/File Group هاي Offline آن Database را Restore كرد.

Reporting Service
امكان جديد براي تهيه گزارشات از Database ها با كمك اين سرويس انجام مي شود . در اين سرويس كنسولي مجهز براي ايجاد رابط كاربر بصورت كاملا ساده و كاربردي وجود دارد تا گزارشات مانند يك Report Generator در اختيار كاربران قرار گيرد اين برنامه امكان ارائه گزارشات را بصورت Web- Enabled نيز دارد.

(SQL Server Integration Service (SSIS
اين سرويس ابزاري براي Integration Data ، استخراج Data و ايجاد Work Flow روي Data ها مي باشد. محيطي كاملا گرافيكي ، با قابليت برنامه ريزي و ابزاري مناسب براي Data Warehousing مي باشد.

[Reza_Yarahmadi]

اجزاي قابل نصب در SQL Server 2005

در زمان نصب MS SQL Server 2005 اجزاي زير نصب مي شود

SQL Server Database Engine
Analysis Services
Reporting Services
Notification Services
Integration Services
Management Tools
Documentation and Samples

MS SQL Server 2005 روي سخت افزار هاي 64بيتي راه اندازي مي شود اين امكان همانند راه اندازي روي 32بيتي ها مي باشد و تنها در مورد پردازش هاي SQL Server با نسخه هاي 32بيتي متفاوت است



نسخه هاي MS SQL Server 2005

براي سازماندهاي و اجراي يك نرم افزار روي MS SQL Server 2005 نسخه هاي زير قابل دسترس مي باشد

SQL Server 2005 Enterprise Edition - 32 , 64 bit
SQL Server 2005 Standard Edition - 32 , 64 bit
SQL Server 2005 Workgroup Edition - 32 , 64 bit

SQL Server 2005 Developer Edition - 32 , 64 bit
SQL Server 2005 Express Edition - 32 bit only

انتخاب نسخه اجرايي SQL Server 2005 دقيقا با نوع نرم افزار و ميزان كاربري Database مشخص مي شود . اين نسخ در مورد كاركردشان روي نسخه هاي مختلف متفاوت مي باشد از اين رو براي كار در محيطهاي كاربران بايستي نسخه مناسب انتخاب شود .

از طرفي اين نسخه هاي روي سيستم عامل هاي مختلف نصب مي شود و در مورد راه اندازي آنها روي سيستم عامل هاي مختلف محدوديتهايي وجود دارد . اين محدوديت ها در جدول هاي زيرين بيان خواهد شد



پيشنيازهاي سخت افزاري براي راه اندازي MS SQL Server 2005

• پيشنيازهاي شبكه ايي

همانند نسخه هاي 32 بيتي نسخه 64 بيتي نيز به زير ساخت شبكه نيازمند است. در اين مجموعه پروتكل هاي زير مورد استفاده قرار مي گيرد:

Shared memory
Named pipes
TCP/IP
VIA

• پيشنيازهاي نرم افزاري

براي راه اندازي MS SQL Server 2005 به نرم افزارهاي زير احتياج مي باشد

Microsoft Windows .NET Framework 2.0
Microsoft SQL Server Native Client
Microsoft SQL Server Setup support files

در نسخه SQL Server 2005 express Edition بايد قبل از نصب حتما .Net Framework 2 كاملا نصب شود و سپس SQL Server 2005 را نصب كنيم

• سخت افزار مورد نياز براي SQL Server 2005

پيشنيازهاي سخت افزاري براي راه اندازي MS SQL Server 2005

ضمیمه 67613

پيشنيازهاي Hard Disk براي راه اندازي MS SQL Server 2005

ضمیمه 67614

سيستم عامل هاي پشتيباني كننده هر نسخه از SQL Server 2005

ضمیمه 67615

پشتيباني Client ها (32 بيتي)
Client Component هاي MS SQL Server 2005 روي Windows 2000 يا نسخ جديدتر آن نصب مي شود.

پيشنيازهايي كه فقط براي SQL Server هاي 64 بيتي مورد نياز است.

ضمیمه 67616

سيستم عامل هاي مورد نياز براي محيط هاي 64 بيتي

ضمیمه 67617

نسخه Evaluation همانند نسخه Enterprise كار مي كند
پشتيباني Client ها ( 64 بيتي)
Client Component هاي SQL Server 64 bit فقط روي Windows 2003 64 bit نصب مي شود

[Reza_Yarahmadi]

نصب MS SQL Server 2005 نيز همانند ساير محصولات مايكروسافت از يك Wizard استفاده مي كند . MS SQL Server 2005 در 2 CD و يا 1DVD ارائه مي شود. براي نصب آن حدود 800 مگابايت براي برنامه ها و حدود 450 مگابايت براي Database هاي فضاي خالي احتياج است.

نصب پيش نيازها
براي نصب ابتدا بايستي پيش نيازهايي مانند .net framework 2.0 و Windows Installer 3.0 را نصب نمايد. پس از اجراي برنامه Setup پنجره License Agreement كه در واقع توافق هاي لازم در خصوص License و Upgrade شدن Server می باشد نمایش داده می شود. بالاجبار بايتسي آنرا تاييد نماييم. در پنجره بعد تمامي پيش نيازهاي لازم نصب مي شود.

كنترل نصب SQL Server
در پنجره زير تمامي نيازهاي لازم براي نصب را مورد ارزيابي قرار مي دهد و چنانچه اشكالي در آنها باشد آنرا مشخص مي كند
ضمیمه 67634

انتخاب Component ها
در اين پنجره Component هاي موجود را برای انتخاب لیست مي كند .
ضمیمه 67635

در پنجره فوق به ترتيب :
SQL Server Database Services:
براي نصب سرويس هاي SQL Server و در واقع هسته MS SQL Server 2005 مي باشد .
در پنجره زير همانند MS SQL Server 2000 امكان نصب MS SQL Server به عنوان Instance پيش فرض و يا به عنوان Instance دوم را ايجاد مي كند .محدوديت در مورد تعداد Instance ها همانند SQL Server 2000 مي باشد . در صورتي كه SQL Server روي محيط هاي Clustered نصب شود گزينه زيرين اين گزينه فعال خواهد شد.
Analysis Service:
براي نصب سرويس هاي OLAP مورد استفاده قرار مي گيرد.
Reporting Service:
براي نصب Reporting Service (ابزار گزارش سازي و برنامه ايي مستقل از MS SQL Server مي باشد) مورد استفاده قرار مي گيرد .
Notification Service:
سرويسي براي ارسال و دريافت Message و در تعريف كلي ارتباط با محيط MS SQL Server مي باشد . توسط اين گزينه اين سرويس نصب مي شود .
Integration Service :
يك سرويس جامع و كاربري براي حفظ يكپارچگي Database ها مي باشد . براي نصب اين سرويس اين گزينه را انتخاب مي نماييم.
گزينه آخر براي نصب ابزارها و برنامه هاي جانبي و كاربردي MS SQL Server 2005 مورد استفاده قرار مي گيرد.
(بهتر آن است که تمامي موارد را انتخاب نماييد.)

نصب SQL Server Instance
در پنجره زیر می توان Instance جدیدی با نام پیش فرض و یا نام انتخابی کاربر ایجاد کرد. و یا از بین Instanceهای نصب شده بر روی سیستم یکی را برای Upgrade شدن انتخاب نمود.
ضمیمه 67636

نكته:

1. SQL Server2005 را مي توان بصورت Instance نصب كرد. براي نسخه دوم بصورت Name Instance عمل مي كنيم
2. براي نصب MS SQL Server 2005 در كنار MS SQL Server 2000 مانعي وجود ندارد و فقط بايستي بصورت Instance مجزا نصب شود در غير اين صورت SQL قبلي را Upgrade مي نمايد .

مديريت امنيت سرويسها
در پنجره زير امكان انجام تنظيمات براي مديريت امنيت SQL Server Service ها وجود دارد.
ضمیمه 67637

مديريت امنيت SQL Server
در صفحه زير نحوه Authentication در MS SQL Server 2005 را مشخص مي نماييم
ضمیمه 67638

نكته:

1. MS SQL Server 2005 براي رمز گذاري كلمه عبور كاربران از Password Policy بطور پيش فرض استفاده مي كيند .

انتخاب زبان SQL Server
در پنجره بعدي زبان پيش فرض MS SQL Server را تعيين مي نماييم

در پنجره بعدي نتايج عمل Install كردن را Report ميكند و يا خطاهاي احتمالي حين نصب را گزارش داده مي شود .

در اين زمان SQL Server 2005 نصب شده در صورت دارا بودن Service pack میتوان آن را نصب نمود.

[Reza_Yarahmadi]

آشنايي با ساختار Database در MS SQL Server 2005
در اين قسمت با موارد زير آشنا مي شويم:

1. مفاهيم Page / Extent در Database
2. مفاهيم Data File ها
3. مفاهيم Transaction و Transaction Log File
4. تنظيمات Database
5. امكانات نگهداري Database

مفاهيم Page / Extent در Database
محل نگهداري Data در Database جايي بجز فايل هاي Database نمي باشد . اين فايلها قابل شناسايي SQL Server 2005 مي باشد و اطلاعات آن كاملا با ساختار فايل MS SQL Server 2005 ثبت مي شود . در واقع درهر Database فايلهاي Database مشخص و با معماري خاصي محل نگهداري اطلاعات را مشخص مي كند. هر Database در SQL Server 2005 شامل 3 گروه فايل مي باشد :

• MDF.*

به عنوان فايل اصلي Database مي باشد. هر Database شامل يك فايل از اين نوع مي باشد . اين فايل محل نگهداري اطلاعات مربوط به اطلاعات Database مي باشد. اين اطلاعات مانند اطلاعات فايل هاي Database و جداول و .... مي باشد. اين فايل در گروه Primary قرار دارد.

• NDF.*

به عنوان فايل كمكي هر MDF در گروه خود وظيفه نگهداري سربار اطلاعات مربوط به هر MDF را برعهده دارد.

• LDF.*

با توجه به معماري MS SQL Server 2005 در خصوص اجراي دستورات و حفظ امنيت اجراي دستورات فايلي به جهت مديريت تراكنش هاي ارسالي به Server ايجاد مي شود كه به آن Transaction Log File گفته مي شود و آنرا با پسوندLDF مي شناسيم.

در Database مفهومي به نام File Group وجود دارد . File Group ها يكي از اساسي ترين مفاهيم طراحي File هاي Database مي باشد. به عبارت ديگر تخصيص فايلهاي Database در گروه ها ، با نحوه ذخيره شدن اطلاعات در فايلها ارتباط مستقيم دارد .
در File Group مي توان بيش از يك فايل ايجاد كرد . اين بدين معني است كه در صورت محدود شدن يكي از فايلها سربار اطلاعات روي فايل ديگر نوشته شود . اين مورد از نظر بهبود كارايي و بهينگي در I/O موثر مي باشد . در اين حالت يك فايل با پسوند MDF به عنوان فايل اصلي گروه و مابقي با پسوند NDF به عنوان فايل هاي كمكي ايجاد مي شوند.
در حالتي هم كه فايلي در گروهي محدود نشده باشد . اطلاعات به نسبت حجم ابتدايي فايل ها روي آنها نوشته مي شود .اين مورد در زمانيكه اطلاعات روي چندين Disk سخت افزاري از هم مجزا قرار داشته باشد باعث افزايش كارايي I/O مي شود (مي توان بصورت موازي (Parallel) اطلاعات را از فايل خواند يا روي آن نوشت.)

نكات :

1. هر فايل از Database فقط و فقط مي تواند عضو يك File Group باشد.
2. هر File Group ميتواند شامل چندين فايل باشد.
3. Log file ها گروه بندي نمي شوند.

پيشنهادها:

1. براي بهبود كارايي Query ها ، جداول را در فايلهاي فيزيكي از هم جدا قرار دهيد.
2. Log File را در كنار Data File ها قرار ندهيد.
3. جداول سنگين و Index هاي از نوع Non Clustered را روي Disk هاي فيزيكي از هم جدا قرار دهيد.
4. از File Group ها استفاده نماييد تا با ايجاد اشيايي مانند جدول ثيت اطلاعات در چندين فايل قرار گيرد تا از امكانات I/O موازي استفاده نمايد.

يكي از اساسي ترين شرايط براي استفاده از امكان Partitioning در SQL Server 2005 ، ايجاد كردن File Group هايي بر مبني طراحي دقيق File Group مي باشد .براي اطلاعات بيشتر به قسمت Partitioning (پست بعد) مراجعه كنيد.
براي ديدن File Group ها Database Property را بگيريد ، پنجره زير نمایش داده خواهد شد:
ضمیمه 67651

در اين فرم مي توانيد يك گروه به گروههاي موجود اضافه نماييد.
در گروه فايلها چنانچه File Group اي داراي حداقل يك بايت اطلاعات و فايل باشد و يا شيي وابسته به آن گروه وجود داشته باشد ، امكان حذف آن File Group وجود ندارد.

Data File
براي مشاهده فايل هاي Database از دیتابیس Database Property گرفته و از پنجره زير استفاده نماييد.
ضمیمه 67652

براي تغيير در رفتار رشد اتوماتيك فايل روي Browse (...) مقابل Autogrowth را انتخاب كرده و در فرم زير مي توانيم اصلاحات لازم را انجام دهيد :
ضمیمه 67653

ساختار Data file ها در Database در زمان ايجاد Database ايجاد مي شود و فايل هاي Database با ظرفيت حداقل 3 مگابايت و رشد اتوماتيك بصورت 10 % براي Database در نظر گرفته مي شود.

با توجه به تعاريفي كه از Data File و File Group ارائه شد ، لازم است با ساختار دروني هر فايل بيشتر آشنا شويم بدين منظور ابتدا به تعاريف زير توجه كنيد
PAGE : به عنوان كوچكترين ثباته نگهداري اطلاعات در Database گفته مي شود كه اندازه آن برابربا 8 KB مي باشد .
EXTENT : به هر 8 Page يك Extent گوييم
در هر 1 مگابايت برابر با 128 page وجود دارد كه اين حافظه محل نگهداري ركوردهاي اطلاعاتي مي باشد .در هر Page مقداري برابر با 96 بايت براي Header Page كه يكي از انواع زير مي باشد و بقيه فضاي آن براي نگهداري اطلاعات مورد استفاده قرار مي گيرد.
سطرهاي اطلاعات در Page بصورت سريالي نوشته مي شود .هر Page شامل Header , Data مي باشد.
ضمیمه 67654

در قسمت Header اطلاعاتي مربوط به نوع Data و .... قرار دارد و در قسمت Data Row سطرهاي اطلاعاتي قرار مي گيرد .Row Offsets داراي اطلاعاتي است كه به اطلاعات درون يك Page اشاره مي كند. اطلاعات Row Offset سريالي معكوس از ترتيب Page هاي درون Page را درخود نگهداري مي كند .
نكته:

1. در اغلب مواقع كه روي Database تراكنشهاي زيادي انجام شود و در بيشتر اين تراكنشها بصورت Update و Delete باشد ، نياز دارين تا براي حفظ كارايي Database، Data File ها را بازسازي كنيم . براي اين كار با استفاده از Maintenance Plan مي توان تمامي Data Page ها و Offset Page ها را بازسازي كرد .
2. در Data Page قسمت Free Space بايستي طوري مديريت شود تا همواره براي جداولي كه اطلاعات روي آنها زياد ثبت مي شود ، به اندازي كافي فضاي خالي موجود باشد . زيرا در زمان Data Entry چنانچه فضاي خالي كم باشد فايل Expand مي شود .رشد فايل به تنظيم آن فايل مربوط مي شود .

براي فهميدين اطلاعات درون يك فايل مي توان از دستور زير استفاده كرد :

DBCC ShowContig ('TableName')

مثلا :

DBCC ShowContig ('ACC_AccDocRow')

و در نتيجه :
ضمیمه 67655

كه با تحليل نتيجه اين Script به وضعيت Data Page هاي مربوط به آن فايل پي برده و Tuning را مي توانيم اعمال كنيم .
در Page ، SQL Server 2005 هايي كه اطلاعات از آنها Delete مي شوند مجددا براي ثبت اطلاعات مورد استفاده قرار مي گيرند . اين عمل بصورت خودكار توسط MS SQL Server انجام مي شود. چنانچه عمل نوشتن بين Page هاي خالي با موفقيت انجام نشود ، درانتهاي فايل اطلاعات نوشته مي شود. و در بعضي اوقات براي نوشتن در قسمت خالي وقت زيادي را مي گيرد كه در اين حالت مي توان با استفاده از دستور فوق اين مورد را شناسايي و Data Page هاي را بازسازي كرد.

ادامه دارد....

[Reza_Yarahmadi]

Database Partitioning
در SQL Server 2000 براي ايجاد پايگاه داده مفاهيم File / File Group درسطح سيستم عامل ، جداول و View ها در سطح پايگاه داده مطرح و مورد استفاده قرار مي گرفت. در زماني كه اطلاعات زياد و قسمتي از اطلاعات به اطلاعات مرده تبديل مي شد ، نياز مبرمي به Archive كردن اطلاعات پيش مي آمد كه SQL Server براي آن هيچ راه كاري بجز استفاده از Partition View پيشنهاد نمي كرد.
در نسخه SQL Server 2005 امكان جديدي در مورد استفاده بهينه از فايلها و فضاهاي در اختيار دیتابیس توليد شده وجود دارد و آن Partition Tables and Indexes مي باشد. اين مورد ، راه كار جايگزين براي مفاهيم مانند Archive و يا Partition View نيست. اين امكان يك ويژگي بسيار مفيد در مورد استفاده از File ها و ايجاد Table , Index روي Partition هاي مشخص از پايگاه داده مي باشد.
مزاياي اين امكان عبارتند از:

1. استفاده بهينه از فايلها .
2. افزايش كارايي Query ها در استفاده ازاطلاعات Partition شده .

Partition در SQL Server 2000 :
در SQL Server 2000 اطلاعات در جداول و جداول روي Disk هاي متعدد قرار مي گيرد ، براي ايجاد Partition View بايستي View هايي با ساختار تركيبي Union All از جداول اصلي پايگاه داده ايجاد کرد تا به كليه اطلاعات يك جدول بتوان دسترسي پيدا كرد.
معايب اين روش عبارتند از :

1. در زمان ثبت اطلاعات سرعت مناسب است ولي چنانچه به ارتباط بين ركوردها نياز باشد بايستي اطلاعات را از Partition View خواند که اين مورد باعث كند شدن عملیات مي گردد .
2. پياده سازي اين مورد براي دیتابیس های OLTP (سیستم های تولید مکانیزه ای که داده ها را جمع آوری نموده و به مصرف می رسانند) بسيار سخت بود زيرا اولا نياز به تخصيص فيلدهاي تكراري جداول در Header / Item ها بود ثانيا سناريوهايي كه از بابت روابط بين اطلاعات موجود بود ، امكان Partition كردن اطلاعات را كم مي كرد .

در نتيجه براي سيستم هايي كه پياده سازي شده بودند راه اندازي اين امكان سخت بود .

مدل شماتيك همراه با مثال :
در يك دیتابیس 3 فايل براي يك File Group در نظر بگيريد. 2 جدول به نام A , B نيز روي اين گروه ايجاد شده اند.
در شماي زیر فرض كنيد يك ركورد در جدول A و يك ركورد در جدول B درج شود. در زمان ايجاد اين جداول به فرمت Database ، 64 kb معادل 8 page براي اين جداول تخصيص داده مي شود. در زماني كه ركوردي در A و B درج مي شود ، SQL Server براي ركورد جدول A يك Extent و براي ركورد B يك Extent در نظر مي گيرد و اين فرايند با رشد Extent ها ادامه پيدا ميكند .
حال اگر اين file ها روي Device هاي سخت افزاري متعدد قرار داشته باشد در كارايي بانك اطلاعاتي تاثيرات مثبتي مشاهده مي شود .
در همين حال اگر بخواهيم از اطلاعات قديمي آرشيو داشته باشيم با موانعي روبرو هستيم كه در بالا به آنها اشاره شد.

ضمیمه 67683

Partition در SQL Server 2005 :
با Partition table در SQL Server 2005 اين امكان وجود دارد كه با كمك Function و Scheme تمام ركورد ها را بر ميني Partition Key مشخص درجاي مشخص شده در Scheme قرار داد.

ضمیمه 67684

در نماي فوق اطلاعات مربوط به Jan ، در Partition مربوط به Jan نگهداري مي شود و همينطور ساير اطلاعات با كليد ماه Partition شده اند.
از مزاياي اين امكان :

1. سادگي در پياده سازي اين امكان براي جداول با حجم زياد اطلاعات و قرار دادن آنها روي Device هاي مختلف. كه در نتيجه در زمان خواندن اطلاعات ، سرعت پردازش موازي در خواندن اطلاعات به كارايي گزارش كمك مي كند.
2. Load شدن اطلاعات در Partition جديد در كمترين زمان ممكن صورت مي گيرد.
3. Partition كردن اطلاعات در ساده ترين حالت ممكن امكان پذير خواهد بود و حتي Delete كردن قسمتي از Partition نيز به سادگي امكان پذير خواهد بود.

ضمیمه 67685

SQL Server 2005 زمانيكه Table بصورت Partition تبديل مي شود

اطلاعات جدول طبق طبقه بندي در Partition از هم جدا مي شود و در هر Partition امكان مرور درختواره اطلاعات بصورت مستقل از هم فراهم مي شود. با استفاده از اين تكنيك سرعت بررسي دستيابي به اطلاعات زياد شده و در نتيجه كارايي Database افزايش پيدا مي كند.

در نسخه هاي قبل از SQL Server 2005 ، جداول و ايندكس ها مي توانستند روي يك File Group ايجاد شوند. در SQL Server 2005 اين امكان وجود دارد ، مضافا جداول و ايندكس ها مي توانند روي Partition Scheme ساخته شوند. Partition Scheme نيز Map چندين File Group مي باشد.
براي اينكه مشخصا Data ها روي موقعيت خود قرار بگيرند Partition Function ها اين كار را انجام مي دهند. در Partition Function الگوريتمي براي استفاده بصورت مستقيم از Data ها روي Partition Scheme وجود دارد .

تعاريف:

• Partition Scheme

شئي است كه اطلاعات مربوط به Partition مثلا File Group هاي تشكيل دهنده Partition را نگهداري مي كند.

• Partition Function

شئي است كه اطلاعات منطقي مربوط به Partition مثلا Align و فيلدي كه بايد بر مبني آن Partition انجام شود را مشخص مي كند .

• Range

مشخص كردن محدوده اي براي تفكيك منطقي ركودهاي يك جدول را Range گوييم. در واقع اين Range شامل كليدي است كه بر مبني آن اطلاعات Archive و یا Partition مي شوند. مشخص كردن اين كليد دقيقا منطقي و بر مبناي تحليل سيستم ها و به عبارت ساده تر ، تحليل Archive اطلاعات مي باشد.
در هر صورت از زماني كه اطلاعات امكان Partition شدن پيدا كردند بايستي در تعريف جداول براي فيلد كليد Partition يك Constraint تعريف شده تا اطلاعات متعلق به هر جدول در جايگاه خود قرار بگيرد .

• Index Partitioning

با استفاده از همان دستورات براي ايجاد Partition table ميتوان Index ها را نيز Partition كرد. زمانيكه جدول و ايندكس آنها روي كليد مشترك Partition شده باشند ، Align گوييم. زمانيكه ايندكسي را روي يك جدول Partition شده قرار مي دهيم بصورت اتوماتيك بر مبني Partition Scheme آنرا Align مي كند .
قرار دادن جداول و ايندكس هاي آنها روي يك فايل و گروه فايل به پردازش موازي در پردازنده كمك مي كند


روش ايجاد Partition

ضمیمه 67686

روش فوق روش استاندارد براي Partition كردن Data هاي دیتابیس مي باشد. ولي با روشهاي ديگري نيز مي توان دیتابیس را Partition كرد :

روش Partition كردن دیتابیس
با اين پيش فرض كه اشيا Table همواره با Clustered Index ها بايستي در يك نقطه ايجاد شوند . لذا با استفاده از چنين تكنيكي مي توان Table ها را در موقعيت مناسب قرار داد. فقط بايستي به محدوديت هاي موجود در استفاده از Clustered Index ها توجه كرد.
ملاحضات :

1. براي هر جدول نمي توان بيش از يك Clustered Index استفاده كرد.
2. براي ايجاد Partition روي جداول چنانچه داراي يك فيلد Clustered باشند بايستي محتويات Index تركيبي از فيلد Clustered و فيلد مربوط به Partition Key باشد.
3. با استفاده از Partition ممكن است استراتژي استفاده از Index نيز تغيير كند. از اين رو بايستي سيستم را مجددا مورد بررسي قرار داد.

در مكانيزم Partitioning ابتدا بايستي Partition Function ايجاد شود . براي ايجاد Partition Function مي توان از دستور زير استفاده كرد :

CREATE PARTITION FUNCTION   <Function Name> (<Data Type >)

AS RANGE LEFT FOR VALUES (<Range Value>)

مثلا:

CREATE PARTITION FUNCTION myRangePF1 (int)

AS RANGE LEFT FOR VALUES (1, 100, 1000);

مرحله بعدي ايجاد Partition Scheme مي باشد كه با كمك دستور زير اين كار امكان پذير است :

CREATE PARTITION SCHEME <Scheme Name>

As Partition   <Partition Function>  

TO( <File Groups>)

مثلا :

CREATE PARTITION SCHEME myRangePS1

AS PARTITION myRangePF1

TO (test1fg, test2fg, test3fg, test4fg)

حالا مي توان جداول را ايجاد كرد و در زمان تعريف جداول موقعيت ايجاد جداول را روي Partition Scheme معلوم کرد.

CREATE TABLE  <Table Name>

( FIELD LIST          DATA TYPE       ……

   …

  ) ON <Partition Scheme Name>  (<Field Name>)

مثال

CREATE TABLE PartitionTable (col1 int, col2 char(10))

ON myRangePS1 (col1)

فيلدي كه در تعريف جدول به عنوان فيلد Partition معرفي مي كنيم بايستي يكي از فيلدهاي همان جدول و با ساختار Partition Scheme سازگاري داشته باشد .
با توجه به مثالهای فوق :
در صورتی که col1 <= 1 باشد اطلاعات در test1fg ذخیره خواهند شد
در صورتی که 1 < col1 <= 100 باشد اطلاعات در test2fg ذخیره خواهند شد
در صورتی که 100 < col1 <= 1000 باشد اطلاعات در test3fg ذخیره خواهند شد
در صورتی که col1 > 1000 باشد اطلاعات در test4fg ذخیره خواهند شد

نکته : جداولي كه داراي اطلاعات مي باشند را تنها با بازسازي مجدد جدول و انتقال اطلاعات به جدول جديد ، مي توان Partition كرد .



نتيجه گيري :

1. استفاده از امكان Partitioning در Performance سيستم ها بسيار موثر است.
2. شرط اوليه براي استفاده از اين امكان ، ايجاد File Group هاي لازم براي دیتابیس بصورت كاملا بهينه و با انعطاف پذيري بالاست. در واقع استفاده از اين امكان براي هر مدل بانك اطلاعاتي (بسته به حجم اطلاعات ، تعداد سيستم ها و ... ) متفاوت و بر پایه طراحي File Group ها مي باشد.
3. استفاده از امكان Partition حتي براي دیتابیس هايي كه بر پایه ساختارهاي Raid نصب شده است، باعث افزايش سطح كارايي بانك اطلاعاتي مي شود.
4. وجود الگوهاي طراحي مناسب بر مبني نياز كاربر و معماري نرم افزار از اهميت زيادي برخوردار است .

[Reza_Yarahmadi]

مبحث Transaction Log در Sql Server با توجه به قابلیتهای ترمیم و بازیابی اطلاعات از اهمیت ویژه ای برخوردار است و بی تردید درک ساختار و عملکرد آن در تمام مراحل طراحی ، پیاده سازی و پشتیبانی سیستمهای عملیاتی لازم است.

هر بانک اطلاعاتی SQL Server دارای حداقل یک فایل Transaction Log جهت نگهداری تمام تغییرات داده ای ایجاد شده توسط تراکنشها می باشد. بدین وسیله SQL Server میتواند عملیات های زیر را انجام دهد.

• ترمیم تراکنشهای مجزاء : اگر در کاربرد یک ROLLBAK اجرا شود و یا SQL Server با خطای قطع ارتباط کاربر در هنگام اجرای یک تراکنش برخورد کند. از رکوردهای ذخیره شده در فایل Transaction Log عملیات داده‌ای انجام شده بر روی بانک اطلاعاتی جهت ترمیم و بازگشت داده ها به قبل از تراکنش ناقص استفاده می‌گردد.

• ترمیم تمام تراکنشهای ناقص هنگام شروع SQL Server : اگر اجرای SQL Server به هر علتی با مشکل مواجه شود ، ممکن است بعضی از داده‌های درون حافظه بر روی فایل فیزیکی بانک اطلاعاتی نوشته نشوند. و بدین ترتیب بعضی از تراکنشها بصورت کامل انجام نگیرند. بنابراین هنگامیکه هر نسخه از SQL Server اجرا می شود ، عمل ترمیم را برای تمام بانکهای اطلاعاتی انجام می‌دهد. هر تغییراتی که در حافظه بوده و در فایلهای اصلی نوشته نشده‌اند بطور کامل نوشته می‌شوند(rolled forward). و تمام تراکنشهای ناقص جهت حفظ جامعیت بانک اطلاعاتی به حالت قبل از تراکنش بازگردانده می شوند (rolled back).

• ترمیم بانک اطلاعاتی بازیابی شده تا نقطه خرابی بانک اطلاعاتی (rolling forward) : درصورتیکه بانک اطلاعاتی به هر علتی از بین برود. این امکان وجود دارد که بانک اطلاعاتی تا نقطه خرابی احیاء گردد. ابتدا باید پشتیبان گرفته شده از بانك اطلاعاتی بازیابی شده و سپس با استفاده از فایل گزارش تراكنش عملیات انجام شده بر روی داده‌ها تا نقطه خرابی ترمیم گردند. هنگام بازیابی یك فایل گزارش تراکنش تمام تغییرات ذخیره شده در فایل گزارش تراکنش بر روی بانك اطلاعاتی اعمال می گردند (rolling forward). سپس SQL Server تمام تراکنشهای را که تا آن نقطه کامل نشده‌اند را به حالت قبل بر می گرداند (rolling back).

فایلهای Transaction Log دارای مشخصات زیر می باشند :

• فایلهای Transaction Log بصورت جداول در بانک اطلاعاتی نگهداری نمی شوند بلکه بصورت یک یا چند فایل جدا نگهداری می شوند. حافظه نگهداری اطلاعات تراکنشها از حافظه نگهداری صفحات داده‌ای جدا بوده و بصورت جداگانه نیز توسط مدیر بانك اطلاعاتی مدیریت می‌گردد.

• قالب نگهداری اطلاعات در فایلهای تراکنش مانند قالب صفحات داده‌ای نمی باشد.

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

• مکانیزم قطع قسمتهای بلا استفاده فایل تراکنشها بسیار سریع بوده و حداقل تاثیر را بر روی ماهیت فایل گزارش تراکنشها دارد.

Write-Ahead Transaction Log
SQL Server مانند بسیاری از بانکهای رابطه‌ای دیگر، از Write-Ahead Transaction Log استفاده میکند. Write-Ahead Transaction Log این اطمینان را بوجود می‌آورد که هیچ تغییرات داده‌ای بر روی دیسک نوشته نمی شوند مگر آنکه قبل از آن ، رکورد سابقه آن در فایل گزارش تراکنش ثبت شده باشد.

SQL Server از یک حافظه سریع میانی ((buffer cache برای خواندن صفحات داده‌ای هنگام تقاضای دسترسی به داده‌ها استفاده می‌كند. تغییرات داده‌ای مستقیما" بر روی دیسک سخت نوشته نمی شوند، بلکه این تغییرات بر روی یک کپی از داده‌های موجود در حافظه میانی اعمال می‌گردد. تغییرات بر روی دیسک سخت نوشته نمی‌شوند مگر اینكه بانك اطلاعاتی به یک Checkpoint برسد و یا اطلاعات دورن حافظه جهت نگهداری یک صفحه جدید درون فضای حافظه ، بر روی دیسک سخت منتقل گردند. نوشتن اطلاعات از حافظه به روی دیسک سخت را تخلیه صفحات گویند و صفحه داده‌یی را كه درون حافظه تغییر یافته ولی هنوز به دیسک سخت منتقل نشده را اصطلاحا" صفحه کثیف(Dirty Page) می گویند. هنگامیکه یك صفحه داده‌ای در حافظه میانی تغییر می یابد یک رکورد متناظر با آن تغییر، در حافظه میانی تراکنشها ایجاد می‌گردد. این رکورد تراکنش باید قبل از تخلیه صفحه داده‌ای متناظر تغییر یافته بر روی دیسک ذخیره گردد. در صورتیکه صفحه داده‌ای تغییر یافته قبل از رکورد متناظر خود در حافظه میانی تراکنش بر روی دیسک سخت نوشته شود، تغییری بر روی داده‌های بانک اطلاعاتی اعمال گردیده که در صورت خرابی SQL Server قبل از نوشتن رکورد تراکنش بر روی دیسک قابل بازگشت نیست. SQL Server دارای مکانیزمی است که همیشه از تخلیه صفحات داده‌ای تغییر یافته قبل از رکورد متناظر آنها در حافظه میانی تراکنشها جلوگیری می‌کند. و به خاطر اینکه همیشه رکوردهای متناظر تراکنش قبل ار صفحات داده‌ای تغییر یافته بر روی دیسک سخت نوشته می‌شوند به فایل Transaction Log، فایل Write-Ahead Transaction Log می‌گویند.


معماری منطقی فایلهای Transaction Log
فایلهایTransaction Log بصورت یکسری از رکوردهای رشته‌ای تراکنش پشت سرهم ذخیره می شوند. هر رکورد تراکنش توسط یک شماره ترتیب تراکنش([LSN [Log Sequence Number) مشخص می‌گردد. هر رکورد تراکنش جدید در خاتمه آخرین رکورد تراکنش و با یک شماره LSN بالاتر از آن ذخیره می‌گردد.

انواع عملیاتی که در فایل Transaction Log ذخیره می‌شوند عبارتند از :

• شروع و خاتمه هر تراکنش.
• هر نوع تغییر داده‌ای (insert, update, or delete) ، این تغییرات حتی شامل تغییرات در جداول سیستمی توسط SPهای سیستمی و یا دستورات تعریف داده‌ها (DDL) می‌باشند.
• هرگونه تخصیص یا آزاد سازی حوضه‌ها.
• ایجاد و یا حذف جداول و Indexها.

Checkpoints
عملي است كه تمامي Dirty Page (تمامي Data Page هايي كه در حافضه میانی قرار دارد و هنوز روي دیسک نوشته نشده است) را روي دیسک اعمال مي كند. Checkpoint زمان Recovery كردن اطلاعات را نگهداري مي كند كه در Checkpoint بعدي عمليات تكراري بابت Checkpoint Data page هاي قديمي انجام ندهد. در مورد Log File نيز اين ساختار وجود دارد.
Checkpoint قسمتی از Transaction Log را که در طول عمل ترمیم کامل بانک اطلاعاتی(full recovery) باید مورد پردازش قرار بگیرند را به حداقل می رساند. در طول یک ترمیم کامل بانک اطلاعاتی دو نوع عملیات باید انجام بگیرد:

• تغییرات داده‌ای (مشخص شده با رکوردهای درون Transaction Log) که در دیسک ذخیره نشده اند ، باید ذخیره گردند (rolling forward).
• تمام تغییرات تراکنشهای ناقص (تراکنشهای که کامل نشده اند و رکورد COMMIT و یا ROLLBACK در فایل گزارش تراکنش برای آنها ثبت نشده است) باید به حالت قبل از انجام دستورات تراکنش بازگردند.

Checkpoints با تخلیه صفحات داده‌ای و رکوردهای تراکنش از درون حافظه سریع میانی بر روی دیسک سخت تعداد تغییراتی را که باید جهت ترمیم بانک اطلاعاتی صورت پذیرند را به حداقل می‌رسانند.

با ایجاد یک Checkpoint در SQL Server پردازشهای زیر در بانک اطلاعاتی جاری انجام می‌گیرد :

• یک رکورد بعنوان شروع Checkpoint در Transaction Log ذخیره می‌گردد.
• اطلاعات Checkpoint در یک زنجیره از رکوردهای Checkpoint در Transaction Log ذخیره شده و شماره LSN شروع این زنجیره در صفحه راه اندازی بانک اطلاعاتی ذخیره می‌شود.
• یک قطعه از اطلاعات ذخیره شده در رکورد Checkpoint شماره LSN صفحه‌ای است که بازگشت عملیات تغییرات اطلاعات (rollback) ، بصورت صحیح از آن امکان پذیر است. این شماره صفحه ، شماره صفحه‌ی حداقل ترمیم (MinLSN) نامیده می شود.
• Checkpoints لیستی از تمام تراکنشهای فعال که هنوز به دیسک منتقل نشده اند را در خود ذخیره می‌کنند.
• درصورتیکه از مدل ترمیم ساده(simple) بانک اطلاعاتی استفاده گردد، تمام رکوردهای تراکنش درون Transaction Log قبل از جدیدترین MinLSN ، حذف می‌شوند.
• تمام صفحات داده‌ای و رکوردهای تراکنش درون حافظه های میانی به دیسک منتقل می‌شوند.
• یک رکورد بعنوان پایان Checkpoint در Transaction Log ثبت می‌شود.

رکورد MinLSN تا آخرین رکورد نوشته شده در Transaction Log را قسمت فعال Transaction Log می نامند و این همان قسمتی است که برای ترمیم کامل بانک اطلاعاتی لازم است. هیچ محدوده ای از قسمت فعال فایل گزارش تراکنش قابل حذف (Truncate) نبوده و تنها قسمتهای قبل از رکورد MinLSN می توانند حذف گردند.

Checkpoint ها در مواقع زیر ثبت می‌گردند :

• هنگام اجرای یک جمله CHECKPOINT ، با اجرای این جمله برای بانک اطلاعاتی جاری یک Checkpoint ثبت می‌گردد.
• هنگام اجرای دستور ALTER DATABASE جهت تغییر مشخصات یک بانک اطلاعاتی.
• هنگام توقف یک نمونه از SQL Server با استفاده از جمله SHUTDOWN و یا با توقف سرویس SQL Server توسط مدیر سرویس SQL Server . در این صورت برای تمام بانکهای اطلاعاتی آن نمونه از SQL Server ، رکورد نقطه کنترل ثبت می‌گردد.
• در یک نمونه از SQL Server بصورت دوره‌ای جهت کاهش زمان ترمیم بانکهای اطلاعاتی برای هر بانک اطلاعاتی بصورت خودکار رکورد Checkpoint ثبت می‌شود.
• در Recovery Model هاي Bulked Log و Simple به ازاي ثبت هر ركورد اين اتفاق مي افتد.
• از دیتابیس Backup تهيه شود.

SQL Server همیشه بصورت خودکار Checkpoint ها را تولید می‌کند. دوره زمانی بین تولید Automatic Checkpoints به زمان وابسته نبوده و به تعداد رکوردهای موجود در Log File بستگی دارد. دوره زمانی بین Checkpoint می‌تواند خیلی متغییر باشد. درصورتیکه تغییرات کمی در داده‌های بانک اطلاعاتی صورت می‌گیرد این زمان زیاد بوده و در صورت تغییرات زیاد بانک اطلاعاتی زمان وقوع Checkpoint کم خواهد بود.

فاصله زمانی بین وقوع Checkpoint توسط پیکربندی Recovery Interval سرویسگر بانک اطلاعاتی محاسبه می‌گردد. این گزینه حداکثر زمانی را که SQL Server باید در هنگام شروع مجدد صرف ترمیم بانک اطلاعاتی کند مشخص می‌کند. SQL Server تخمین میزند که چه تعداد رکورد Log File را در مدت زمان مشخص شده برای ترمیم بانک اطلاعاتی میتواند پردازش کند و براساس آن دوره زمانی وقوع نقاط کنترل را تعیین می کند. فاصله زمانی وقوع خودکار نقاط کنترل همچنین به اینکه از مدل ساده ترمیم (simple) استفاده شود و یا خیر نیز بستگی دارد.

• درصورتیکه از مدل ترمیم full و یا bulk-logged استفاده شود، یک Checkpoint هنگامی ایجاد می‌گردد که تعداد رکوردهای جدید ایجاد شده (از Checkpoint قبلی) به تعداد رکوردهای برسد که SQL Server تخمین زده که میتواند در زمان مشخص شده برای ترمیم بانک اطلاعاتی در شروع مجدد پردازش نماید.
• در صورتیکه از مدل simple برای ترمیم بانک اطلاعاتی استفاده شود ، یک Checkpoint خودکار هنگامی ایجاد می شود که 70 درصد Log File پر شود و یا تعداد رکوردهای جدید ایجاد شده (از Checkpoint قبلی) به تعداد رکوردهایی برسد که SQL Server تخمین زده که می‌تواند در زمان مشخص شده برای ترمیم بانک اطلاعاتی در شروع مجدد پردازش نماید.

Automatic Checkpoints در مدل simple ترمیم قسمت بدون استفاده فایل تراکنش را خواهند برید. در صورتیکه در دو مدل ترمیم full و bulk-logged این عمل صورت نمی‌گیرد.

براي تغيير Recovery Interval در SQL Server از پنجره Server Property و قسمت Database Setting:
ضمیمه 67724

مقدار برابر با صفر يعني بصورت اتوماتيك Checkpoint مديريت شود.
براي تغيير با كمك دستور هم :

EXEC sp_configure 'recovery interval', 90

 RECONFIGURE WITH OVERRIDE

 

ادامه دارد ....

[Reza_Yarahmadi]

Truncating the Transaction Log
اگر رکوردهای موجود در فایل منطقی Transaction Log هیچگاه حذف نشوند ، این فایل به اندازه کل فضای موجود بر روی دیسک سخت که توسط فایلهای فیزیکی Transaction Log نگهداری می‌شوند گسترش می‌یابد. گاهی اوقات ، دیگر نیازی به رکوردهای تراکنش قدیمی جهت ترمیم و احیاء بانک اطلاعاتی نبوده و باید آنها برای ایجاد فضا جهت رکوردهای جدید حذف شوند. عمل حذف رکوردهای تراکنش جهت کم کردن حجم Transaction Log را Truncating the Transaction Log می‌گویند.

قسمت فعال Transaction Log هیچگاه نمی‌تواند بریده شود. قسمت فعال Transaction Log همیشه جهت ترمیم بانک اطلاعاتی، مورد نیاز می‌باشد. همانگونه که قبلا" گفته شد رکورد شروع قسمت فعال Transaction Log توسط شماره رکورد MinLSN مشخص می‌گردد.

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

رکورهای تراکنش قبل از MinLSN تنها برای ایجاد پشتیبان Transaction Log ها مورد استفاده قرار می‌گیرند.

• در مدل ترمیم ساده ، Transaction Log ها نگهداری نمی‌شوند و تمام رکوردهای تراکنش قبل از MinLSN در هر زمانی بجزء هنگامیکه دستور پشتیبانگیری در حال پردازش باشد می توانند بریده شوند. گزینه های NO_LOG و TRUNCATE_ONLY تنها گزینه های دستور BACKUP LOG هستند که برای مدل ترمیم ساده معتبر می‌باشند که البته در جای خود بیشتر توضیح داده خواهند شد.

نکته : بانک اطلاعاتی tempdb همیشه از مدل ترمیم ساده استفاده می‌کند و همیشه در این مدل در هر Checkpoint ، رکوردهای قبل از MinLSN حذف می‌گردند. بنابراین مدل ترمیم ساده برای بانکهای اطلاعاتی که تغییرات داده‌ای آنها زیاد است مناسب نیست و در مواردی که بانک اطلاعاتی حاوی اطلاعات ثابت و یا با حداقل تغییر هستند مناسب می‌باشد. البته در مدل ساده همانگونه که گفته شد اطلاعات تراکنشهای فعال همیشه جهت ترمیم بانک اطلاعاتی توسط SQL Server نگهداری می‌شوند. برای دیدن مدل ترمیم یک بانک اطلاعاتی میتوان از دستور زیر استفاده استفاده نمود.

 SELECT  DATABASEPROPERTYEX('dbname','RECOVERY')

نتیجه این پرس و جو یکی از جملات FULL , BULK_LOGGED , SIMPLE خواهد بود.
برای تغییر مدل ترمیم بانک اطلاعاتی میتوان از دستور زیر استفاده نمود.

ALTER DATABASE dbname  SET RECOVERY FULL

• در مدلهای ترمیم کامل و یا bulk_logged ، رکوردهای تراکنش جهت پشتیبان گیری از Transaction Log نگهداری می شوند و رکوردهای تراکنش قبل از MinLSN تا قبل از اینکه از Transaction Log پشتیبان گرفته نشود قابل حذف نخواهند بود.

توضیح : در مدل ترمیم کامل تمام عملیات داده ای انجام شده در بانک اطلاعاتی بصورت کامل در Transaction Log ثبت می‌شوند، بنابراین میتوان عمل ترمیم را تا یك نقطه و یا تراکنش مشخص انجام داد. برای حفاظت صددرصد داده‌ها مدل ترمیم کامل بهترین انتخاب است. در مدل ترمیم کامل با توجه به ثبت تمام عملیات بر روی داده‌ها ، حجم عملیات ثبت بالا می رود هر چند این مدل بهترین حفاظت از داده‌ها را فراهم می‌کند ولی بسته به مورد شاید استفاده از مدلهای دیگر نیازها را برآورده کرده و در نتیجه سربار نوشتن داده های کمتری نیز داشت. در مدل ترمیم Bulk_Logged داده‌های تغییر یافته توسط دستورات SELECT INTO ،BULK INSERT، CREATE INDEX و عملیات بر روی فیلدهای Text و (Binary (WRITETEXT , UPDATETEXT که معمولا داده‌های زیادی توسط آنها نوشته می شود در فایل گزارش تراکنش ثبت نمی‌شوند بلکه تصاویر صفحات تغییر یافته ثبت می‌شوند و بدین ترتیب در این مدل ترمیم بانک اطلاعاتی تا انتهای هر پشتیبان Transaction Log ، که شامل تراکنشها و تصاویر صفحات تغییر یافته است امکان پذیر بوده و ترمیم تا یک زمان یا تراکنش خاص امکان پذیر نخواهد بود.

عمل Truncating the Transaction Log در موارد زیر اتفاق می‌افتد :

• در انتهای اجرای کامل هر دستور BACKUP LOG.
• هنگام وقوع یک Checkpoint در مدل ترمیم ساده (چه این Checkpoint توسط دستور صریح CHECKPOINT یا بصورت خودکار توسط سیستم ایجاد گردد) تنها درصورتیکه دستور BACKUP فعال باشد عمل Truncating انجام نخواهد شد.

Transaction Log ها به قسمتهایی بنام قسمت مجازی (Virtual Log File) تقسیم می‌شوند. قسمتهای مجازی واحد بریده شدن تراکنشها از Transaction Log می‌باشند. هنگامیکه یک Transaction Log بریده می‌شود تمام رکوردهای تراکنش قبل از قسمت مجازی شامل MinLSN ، حذف می‌گردند.

شکل زیر یک فایل Transaction Log با چهار قسمت مجازی را نمایش می‌دهد. این فایل بعد از ایجاد هیچگاه Truncate نشده است. گزارش منطقی از قسمت مجازی یک شروع شده و در پایان قسمت مجازی چهار تمام می‌شود و فضای بالای قسمت مجازی چهار استفاده نمی‌شود.
ضمیمه 67726

شکل زیر همان Transaction Log را بعد از Truncate شدن نشان می‌دهد. همانگونه که ملاحظه می‌گردد قسمتهای مجازی قبل از قسمت مجازی شامل MinLSN بریده شده‌اند.
ضمیمه 67727

Truncate شدن Transaction Log اندازه فیزیکی فایل Transaction Log را کم نمی‌کند بلکه اندازه فایل گزارش منطقی را کاهش می‌دهد. با عمل shrink می‌توان حجم فیزیكی فایل Transaction Log را کم كرد. که در ادامه مطالب در جای خود بیشتر توضیح داده خواهد شد.


مكانيزم Transaction
هر عملي كه باعث تغيير در Database مي شود داراي 2 وضعيت مي باشد . يا اجرا شده يا با رخداد Error متوقف مي شود. حال براي اجراي چندين دستور در غالب يك دستور بايستي طوري عمل شود تا يا تمامي دستورات اجرا شده يا به ازاي رخداد يك خطا ، هيچيك از آن دستورات اجرا نشود. از اين رو بايستي مكانيزمي وجود داشته باشد تا اين كار مديريت شود .
Transaction شيي است كه با كمك آن اين كار انجام ميشود . دستوري كه به Database ارسال ميشود ابتدا در Log File مربوط به آن Database ثبت شده و تا پايان زمان نوشتن آن در Data File كل عمليات باز مي باشد . در اين حالت ميگويند "تراكنش باز است و در صورت موفقيت آميز بودن اجراي دستورات و ثبت كامل درData File تراكنش Commit خواهد شد .در صورت عدم موفقيت تمامي دستورات از Transaction Log ، Rollback ميشود .
به عنوان مثال :

Begin Tran
Update …..
Inert ….
Delete ....
اگر خطايي رخ ندهد Commit <كنترل رخداد خطا >
در صورت بروز خطا Rollback

در صورت بروز خطا هيچيك از دستورات فوق اجرا نمي شود و در صورت عدم بروز خطا همگي اجرا مي شود .
بنا به تنظيمات Recovery Model در دیتابیس ، Transaction Log ارسالي در Log file بصورت تاريخچه ايي باقي مي ماند و يا از Log File خارج مي شود .
از اين رو از Log File مي توان در كارهاي زير استفاده کرد:

1. بازيابي دیتابیس از طريق تراكنشهاي قبلي (تاريخچه ايي)
2. بازيابي دیتابیس در زماني كه سرويسهاي Start ، SQL Server مي شود (بصورت پيش فرض تمامي تراكنشهاي باز از Rollback ، Log File مي شود).
3. بازيابي دیتابیس در زماني كه دیتابیس خراب مي شود و مي خواهيم در زماني مشخص دیتابیس را بازسازي كنيم.
4. انتشار اطلاعات (Replication) از نوع Transactional از Transaction Log استفاده مي كند.
5. براي راه اندازي Stand By Server ( راه كاري براي ايجاد بستر مطمئن در نگهداري دیتابیس) از Transaction Log استفاده مي شود.

با توجه به مطالب فوق نگهداري Transaction Log يكي از مهمترين امكاناتي است كه مي تواند در نگهداري دیتابیس و پشتيباني مطمئن از آن مورد استفاده قرار گیرد. از اين روي Transaction Log Backup راه كاري براي مراقبت مناسب از Log File مي باشد .

[Reza_Yarahmadi]

Detach و Attach کردن دیتابیس
Attach يعني برقراري مجموعه ايي از فايلها به عنوان دیتابیس زير نظر SQL Server.
به عبارت ديگر تعدادي فايل را به SQL Server معرفي كرده و چنانچه ارتباطات بين اين فايلها طوري باشد كه به عنوان SQL Server جديد معرفي شود . دستور Attach آنها را بصورت دیتابیس معرفي مي كند.
برای Attach کردن یک مجموعه فایل روی پوشه Databases راست کلیک کرده و گزینه …Attach را انتخاب کنید و در پي آن با انتخاب گزينه Add به پنجره زير مي رسيم.

ضمیمه 68340

با انتخاب Add بايستي اولين فايل دیتابیس را به SQL Server معرفي كرد ، سپس SQL Server بطور اتوماتيك از مسير موجود ، فايلهاي بعدي دیتابیس را كامل مي كند. در اين هنكام در قسمت پايين اين صفحه اطلاعات شناسايي شده توسط SQL Server نمايان مي شود . در اين حالت مي توان دیتابیس را Attach كرد.

نكته :

Detach / attach بهترين روش براي جابجايي محل فيزيكي دیتابیس مي باشد.

استفاده از SP هاي سيستمي براي Attach كردن دیتابیس ها
براي Attach كردن دیتابیس از دستورات زير مي توان استفاده كرد.

SP_Attach_DB
اين SP داراي 17 ورودي است كه اولی نام دیتابیس و 16 پارامتر بعد به عنوان 16 فايل دیتابیس قابل معرفي مي باشند.
چنانچه 16 پارامتر Null باشند دیتابیس را با تمامي فايلها در موقعيتي كه قبلا در آن بوده است Default Path) Attach مي كند و در صورتي كه بخواهيد دیتابیسی با حداكثر 16 فايل در مسير فيزيكي جديد Attach نماييد می توانید با ذكر مسير جديد براي هر يك از فايل ها دیتابیس را Attach نماييد.

مثلا :

SP_Attach_DB  'DbName' ,

                          'C:\Data\dbName_Data.MDF' ,

                          'E:\Data\ dbName _Data1.MDF ,

                          'D:\Data\ dbName _Data2.MDF ,

                          'C:\Data\ dbName _Log.LDF

در اين وضعيت با Copy كردن آن فايلها در موقعيت هاي مشخص شده ميتوان دیتابیس را Attach كرد.

از اين دستور براي دیتابیس های با فايل هاي بيشتر از 16 نمي توان استفاده كرد و بايستي از دستور Create Database ….. For Attach استفاده كرد.

SP_Attach_Single_File_DB
اين دستور مشابه دستور فوق مي باشد وتنها فرقي كه با آن دارد ، نمي توان مسير فيزيكي فايلها را تغيير داد و بايستي در مسير پيش فرض آنها دیتابیس را Attach كرد از اين دستور براي بازسازي دیتابیس هايي كه تنها يك Log File دارند مي توان استفاده كرد.
اين دستور برای Attach كردن دیتابیس هايي كه قابلیت Replicartion را دارند قابل استفاده است

SP_Attach_Single_File_DB 

                    ' dbName' , 'C:\Data\dbName_Data.MDF'

Create Database ... For Attach
دستور عمومي براي Attach كردن دیتابیس از طريق ايجاد دیتابیس مي باشد

 
Create Database dbName
     
On(
     
      FileName =   'C: \ …… ',
     
      FileName =   'C: \ …… ',
     
      FileName =   'C: \ …… ',
     
      FileName =   'C: \ …… ',
     
      FileName =   'C: \ …… ',
     
      FileName =   'C: \ …… ',
     
      FileName =   'C: \ …… ',
     
      FileName =   'C: \ …… ',
     
      ) 
     
      For Attach   | For Attach_Rebuil_Log
 

گزاره Attach_Rebuil_Log براي ايجاد دیتابیس در زماني است كه بخواهيد دیتابیس را بدون استفاده از Attach Log ، كنيد . در اين حالت Log بازسازي خواهد شد و دیتابیس در اين شرايط Attach مي شود. براي استفاده از اين امكان بايد پيشنيازهاي زير را در نظر گرفت :

1. دیتابیس بصورت نرمال Shutdownشده باشد.
2. تمامي MDF , NDF ها موجود باشند.

Detach يعني قطع ارتباط و مديريت SQL Server به عنوان RDBMS با مجموعه فايل های یک دیتابیس.
با اجرا Detach براي دیتابیس ، تنها ارتباط مديريت SQL Server با فايلها برداشته و فايل هاي دیتابیس به عنوان فايلهاي مجرد باقي مي مانند.
براي Detach كردن روی دیتابیس مورد نظر راست کلیک کرده و از منوی Tasks گزینه Detach را انتخاب کنید و در پنجره بعد OK.

ضمیمه 68341

• براي Detach كردن دیتابیس بايستي Connection هاي دیتابیس همگي قطع شده باشند و براي اينكه ابتدا Connection ها قطع شوند گزينه Drop Connection را انتخاب كنيد .

Shrink کردن دیتابیس و فایل
Shrink به معناي كوچك كردن و آب رفتن مي باشد . اين عمل در كارايي و نگهداري بهينه دیتابیس بسيار موثر است. قبل از توضیح دادن مبحث Shrink بهتر است ابتدا با معماری فیزیکی Transaction Log آشنا شویم.

معماری فیزیكی Transaction Log
Transaction Log می‌تواند با یك یا چند فایل فیزیكی پیاده سازی گردد. منطقا" Transaction Log از تعدادی ركورد پشت سر هم و رشته‌ای تشكیل می‌شود و بصورت فیزیكی این ركوردهای پشت سر هم باید بصورت كارا در یك مجموعه از فایلهای فیزیكی پیاده‌سازی گردند.

SQL Server هر فایل فیزیكی را به قسمتهای مجازی تقسیم می‌كند. این قسمتهای مجازی اندازه ثابتی نداشته و تعداد ثابتی در هر فایل فیزیكی نیز ندارند. اندازه این قسمتهای مجازی بصورت پویا هنگام ایجاد و یا گسترش Transaction Log توسط SQL Server مشخص می‌گردند. SQL Server همواره سعی می‌كند تعداد كمتری از قسمتهای مجازی را نگهداری كند. اندازه این قسمتهای مجازی هنگام تعریف اندازه Transaction Log و همچنین اندازه افزایش آن مشخص می‌گردد. تعداد و اندازه قسمتهای مجازی توسط خود SQL Server بصورت پویا مشخص می‌گردد و قابل تعریف و تنظیم توسط كاربر سیستم نیستند. تنها زمانی كه قسمتهای مجازی بر كارائی سیستم تاثیر می‌گذارند زمانی است كه اندازه Transaction Log و مقدار افزایش آن با مقادیر كم تعریف شوند. در این صورت اگر Transaction Log با تعداد زیادی افزایش (با حجم كم) بزرگ شود، تعداد زیادی قسمت مجازی ایجاد خواهد گردید كه در نتیجه سرعت ترمیم را كاهش می‌دهند. بنابراین توصیه می‌گردد كه سعی شود اندازه فایل Transaction Log نزدیك به نیاز واقعی تعریف شود و همچنین مقادیر توسعه اندازه Transaction Log بزرگ در نظر گرفته شوند.

ذخیره اطلاعات در Transaction Log بصورت چرخشی است. برای مثال یك Transaction Log كه به چهار قسمت مجازی تقسیم شده را در نظر بگیرید ، هنگام ایجاد بانك اطلاعاتی فایل منطقی Log از ابتدای فایل فیزیكی Log آغاز می‌شود. و ركوردهای جدید به انتهای فایل منطقی Log اضافه می‌شوند و گسترش به سمت انتهای فایل فیزیكی Log ادامه می‌یابد. با انجام یك عمل Shrinking قبل از قسمت مجازی شامل MinLSN حذف می‌گردند.
هنگامیكه انتهای فایل منطقی Log به انتهای فایل فیزیكی Log برسد ، ركوردهای جدید بصورت چرخشی دوباره از ابتدای فایل فیزیكی Log ذخیره می‌شوند.
این چرخش تا هنگامیكه انتهای فایل منطقی Log به ابتدای فایل فیزیكی Log نرسد، ادامه خواهد داشت. درصورتیكه ركوردهای Transaction قدیمی Shrink شوند اغلب فضای كافی برای ركوردهای جدید Transaction تا Checkpoint بعدی وجود خواهد داشت و Transaction Log هیچگاه پر نمی‌شود.

درصورتیكه انتهای فایل منطقی Log به ابتدای فایل فیزیكی Log برسد ، یعنی Transaction Log پر گردد ، دو حالت ممكن است رخ دهد:

• اگر گزینه گسترش خودكار (autogrow) برای Transaction Log فعال باشد و فضای كافی بر روی دیسك وجود داشته باشد ، اندازه فایل فیزیكی Log به اندازه تعریف شده افزایش (growth_increment) ، افزایش یافته و ركوردهای جدید تراكنش در فضای اضافه شده ذخیره می‌شوند.
• اگر گزینه گسترش خودكار (autogrow) برای Transaction Log فعال نباشد و یا فضای كافی بر روی دیسك سخت به مقدار افزایش تعیین شده (growth_increment) وجود نداشته باشد، خطای شماره 1105 روی خواهد داد.

در صورتیكه Transaction Log دارای چندین فایل فیزیكی باشد، فایل منطقی گزارش قبل از چرخش ابتدا به ترتیب بر روی تمام فایلهای فیزیكی گسترش خواهد یافت و در انتها به ابتدای فایل فیزیكی اول چرخش خواهد نمود.

Shrinking
اندازه فایل Transaction Log بصورت فیزیكی در حالتهای زیر كاهش می‌یابد:

• هنگام اجرای دستور DBCC SHRINKDATABASE.
• هنگام اجرای دستور DBCC SHRINKFILE با اشاره به فایل Transaction Log مورد نظر.
• هنگام اجرای دستور خودكار كوچك شدن (autoshrink).

عمل كم كردن حجم Transaction Log به عمل Truncate آن بستگی دارد. Truncate شدن Transaction Log موجب كم شدن اندازه فیزیكی فایل آن نمی‌گردد ، بلكه اندازه فایل منطقی گزارش را كم می‌كند و قسمتهای مجازی فایل گزارش را كه اطلاعات فایل منطقی را در خود نگهداری نمی‌كنند غیر فعال می‌كند. واحد كاهش اندازه فیزیكی Transaction Log به اندازه یك قسمت مجازی می‌باشد. برای مثال در یك Transaction Log 600 مگا بایتی كه به شش قسمت مجازی 100 مگا بایتی تقسیم شده واحد كاهش اندازه فیزیكی آن در اندازه‌های 100 مگا بایتی می‌باشد و اندازه آن برای مثال می‌تواند به 500 یا 400 مگا بایت كاهش یابد و هیچگاه اندازه آن در این حالت به اندازه‌های 433 و یا 525 مگا بایت كاهش پیدا نمی‌كند.

قسمتهای مجازی Transaction Log كه حاوی گزارش منطقی تراكنشها هستند هیچگاه نمی‌توانند آزاد گردند. و درصورتیكه تمام قسمتهای مجازی نگهدارنده گزارش منطقی تراكنشها باشند Shrink كردن نمی‌تواند انجام گیرد. علاوه بر این عمل Shrink كردن تنها در صورتی انجام خواهد شد كه قسمتهای مجازی غیر فعال در انتهای فایل فیزیكی Transaction Log قرار گرفته باشند. هنگام كم شدن اندازه فیزیكی Transaction Log فضاهای خالی باید از انتهای فایل فیزیكی آزاد شوند و قسمتهای مجازی غیرفعال انتهای فایل به اندازه مورد درخواست كاربر آزاد خواهند گردید. اندازه مورد درخواست كاربر (target_size) به سمت قسمت مجازی بعدی نزدیكترین محدوده قسمت مجازی موجود گرد خواهد گردید. برای مثال اگر كاربر مقدار فضای 325 مگا بایت را برای اندازه Transaction Log ای با اندازه 600 مگا‌‌ بایت و قسمتهای مجازی 100 مگا بایتی مشخص كند ، دو قسمت مجازی آخر در صورت غیر فعال بودن حذف شده و اندازه فایل به 400 مگا بایت كاهش می‌یابد.

دستورات DBCC SHRINKDATABASE و DBCC SHRINKFILE اندازه فیزیكی Transaction Log را بصورت زیر كم می‌كنند.

• اگر هیچ قسمتی از فایل منطقی گزارش موجود در قسمتهای مجازی ورای اندازه مشخص شده توسط كاربر نبوده و قسمتهای مجازی بالاتر از فضای درخواستی كاربر غیرفعال باشند ، عمل Shrinking با حذف این قسمتهای مجازی غیر فعال انجام خواهد گردید.
• اگر قسمتی از فایل منطقی گزارش در قسمتهای مجازی بالای اندازه مشخص شده توسط كاربر باشد. SQL Server هر اندازه از فضا را كه بتواند ( قسمتهای مجازی غیر فعال در انتهای فایل فیزیكی) آزاد می‌كند و یك پیام نیز جهت راهنمائی درباره اعمالی كه می‌توان با انجام دادن آنها فایل منطقی را از قسمتهای مجازی انتهای فایل خارج نمود ، ارائه می‌نماید. بعد از انجام آن عملیات با اجرای مجدد دستور DBCC فضای باقی مانده آزاد می‌گردد.

برای مثال فرض كنید یك فایل گزارش با حجم 600 مگا بایت و 6 قسمت مجازی كه شروع فایل منطقی آن در قسمت مجازی 3 و انتهای فایل منطقی آن در قسمت مجازی 4 قرار دارد، دستور DBCC SHRINKFILE با target_size = 255 MB را اجرا كنیم.
قسمتهای مجازی 5 و 6 با توجه به اینكه حاوی هیچ قسمتی از فایل منطقی نیستند و در انتهای فایل فیزیكی واقع شده‌اند ، بلافاصله آزاد می‌شوند. برای رسیدن به اندازه مورد درخواست قسمت مجازی 4 نیز باید آزاد گردد ولی با توجه به اینكه این قسمت حاوی قسمتی از فایل منطقی گزارش می‌باشد این امر ممكن نخواهد بود. بعد از آزادسازی قسمتهای 5 و 6 مدیر بانك اطلاعاتی SQL Server فضای خالی باقی مانده از قسمت مجازی 4 را با ركوردهای ساختگی(dummy) پر می‌كند. این عمل باعث می شود كه انتها فایل منطقی گزارش بر ابتدای قسمت مجازی 1 منطبق گردد. و اطلاعات تمام تراكنشهای اصلی موجود در قسمت مجازی 4 به قسمت مجازی 1 منتقل شوند.
دستورDBCC SHRINKFILE همچنین یك پیام مبنی بر اینكه تمام فضای مورد درخواست آزاد شده است و اینكه برای آزاد سازی احتمالی بقیه فضا باید از دستور BACKUP LOG استفاده نمود ، نمایش می‌دهد. بعد از انتقال قسمت فعال فایل گزارش به قسمت مجازی 1 ، دستور BACKUP LOG قسمت مجازی شماره 4 را می‌برد (truncate).
با توجه به اینكه قسمت مجازی 4 دیگر هیچ قسمتی از فایل منطقی گزارش را نگهداری نمی‌كند و بریده شده است ، در صورت اجرای مجدد دستور DBCC SHRINKFILE با target_size 255 مگا بایت ، قسمت مجازی 4 هم آزاد شده و اندازه فایل فیزیكی گزارش تراكنشها به اندازه مورد درخواست كاهش خواهد یافت.


Automatic Database Shrink
اين امكان براي زماني است كه دیتابیس داراي فضاخالي باشد و بصورت اتوماتيك عمليات Shrink انجام مي شود. اين امكان بطور پيش فرض غير فعال است و فعال كردن آن روي كار كاربران تاثيري نمي گذارد زيرا اين مورد در خارج از فضاي كاري كاربران انجام مي شود.

Manual Shrink Database
امكان انجام Shrink روي دیتابیس ها بصورت دستي مقدور مي باشد . براي اين كار

ضمیمه 68342

براي زماني كه از گزينه Database استفاده مي كنيم :

ضمیمه 68343

* اين گزينه در مواقعي استفاده مي شود كه بخواهيم قبل از ShrinkPage كردن Data Page ها ، آنها را مرتب كنند. اين امكان در كارايي دیتابیس بسیار موثر مي باشد.
** مقدار فضاي خالي بر حسب درصد از سايز فايل پس از اتمام عمل Shrink بايستي تنظيم شود .

نكته :

طبق تعريف Shrink ، همزمان با مرتب سازي Data Page ها ، page هاي خالي ميان Page هاي دیتابیس به انتهاي فايل منتقل خواهد شد و در زمان Shrink فضاي خالي انتهاي فايل بريده شده و Page ها دقيقا به اندازه واقعي Shrink خواهد شد.

تنظيم اين مقدار برابر با 0% براي دیتابیس هايي كه Data Entry دارند و Data هاي آنها بصورت سريالي وارد دیتابیس نمي شود كاهش سرعت را به همراه دارد. به همين دليل توصيه مي شود تا بين 5% الي 10% فضاي خالي را براي دیتابیس در نظر بگيريد.

براي زماني كه از گزينه File استفاده مي كنيم :

ضمیمه 68344

1. نام دیتابیسي كه Shrink خواهد شد.
2. نوع فايلي كه مي خواهيم Srhink نماييم اين نوع يا Data و يا Log مي باشد
3. انتخاب File Group براي انتخاب فايل مربوطه
4. انتخاب فايلي كه مي خواهيم Shrink File كنيم
5. اندازه فعلي و ميزان فضاي خالي در فايل دیتابیس
6. حافظه آزاد شده از انتهاي فايل ها را به سيستم عامل باز گرداند
7. فايل را تا مينيمم ممكن كوچك كند
8. در سطح يك File Group مي توان اطلاعات را يكپارچه كرد و فايلهاي اضافي در File Group را پاك كرد

Shrinking the Transaction Log
براي Shrink كردن log File از حالت Shrink File استفاده مي كنيم. نكته آنكه براي Shrink كردنLog فايل بايستي از خالي بودن Log File مطمئن شويم . در اين حالت براي خالي كردن Log File بهتر است از Log File ، Backup گرفته و سپس Log File را Shrink كرد.

استفاده از دستورات براي Shrink كردن

DBCC ShrinkDatabase ('DatabaeName ' , درصد كوچك شدن)

مثال:

DBCC ShrinkDatabase (dbName, 10)

اين دستور را مي توان روي Master اجرا كرد.
براي Shrink File از دستور زير استفاده مي شود.

DBCC ShrinkFile ( نام فايل , %)

مثال:

 DBCC SHRINKFILE (DataFile1, 7) 

دستور فوق را بايد روي دیتابیس مورد نظر اجرا كرد .

[Reza_Yarahmadi]

هدف از Backup گيري ، ايجاد امكان بازيابي دیتابیس در زمان خرابي و از دست دادن دیتابیس مي باشد. يكي از اهداف ديگر Backup گيري ، جابجا كردن دیتابیس مي باشد كه اين روش ، روش پرهزينه ايي براي انتقال دیتابیس مي باشد .در عمل Backup گيري عملي است كه در آن با استفاده از توابعي ويژه دیتابیس را به File تبديل مي كند. اين فايل با ساختاري كاملا منحصر به فرد و قابل شناسايي توسط SQL Server ايجاد مي شود.
در واقع Backup مي گيريم براي بازسازي دیتابیس وقتي كه

1. دیتابیس خراب شود.
2. سخت افزاري خراب شود.
3. اشكالاتي از بابت كار كاربران پيش مي آيد.

Backup تهيه شده بايستي قابل اتكا و حتي الامكان با كمترين هزينه براي كاربر تهيه شود لذا تحليل و تهيه يك برنامه مدون براي Backup گيري از ملزمات اين كاراست.

شناخت فاكتورهاي مهم براي Backup گيري مناسب
براي تهيه Backup هاي بهينه از دیتابیس ها شناخت فاكتورهاي اساسي در نحوه Backup گيري بسيار موثر است . اين فاكتورها عبارتند از :

1. نرخ بروز رساني اطلاعات در هر روز
2. سخت افزار لازم براي تهيه Backup
3. اهميت بازيابي اطلاعات از Database Recovery) Backup)

در واقع بايستي عمل Backup گيري در شرايطي انجام شود كه هزينه backup گيري تا حد ممكن كم شود.


Recovery Model
رفتار هاي مختلفي كه SQL Server در مورد نگهداري Log هاي دیتابیس اجرا مي كند را Recovery Model گوييم اين حالات عبارتند از :

1. Full
2. Simple
3. Bulk-logged

Full Recovery Model
در اين مدل تمامي تراكنشها در Log file ثبت ميشود .اين حالت باعث افزايش حجم Log File و در نتيجه كندي در ثبت اطلاعات را پيش مي آورد.ولي در اين حالت امكان بازيابي دیتابیس از طريق Log file مقدور مي باشد .


Simple Recovery Model
در اين مدل تمامي تراكنشهاي دیتابیس فقط براي حفظ Database Consistency ثبت مي شود و از نگهداري Log ها Historical خودداري مي شود .به عبارت ديگر تمامي تراكنشهاي Commit شده از Truncate ، Log file مي شوند. استفاده از Log در دیتابیس مهمترين عامل براي باز سازي دیتابیس در زمان خرابي آن به حساب مي آيد. لذا ضرورت استفاده از Log در SQL Server از ملزمات مي باشد .

نكته:

1. در زماني كه دیتابیس در Recovery model = Simple باشد . امكان تهيه Transaction Log Backup مقدور نمي باشد و با Backup گيري از آن اندازه آن به اندازي ابتدايي خود ميرسد (Shrink) مي شود.
2. براي حفظ امنيت دیتابیس بايستي از امكان Transaction Log Backup استفاده كرد. از اين رو استفاده از Recovery model = Full را پيشنهاد مي نماييم .

Bulk-logged :
در اين مدل تمامي تراكنش ها روي دیتابیس Log مي شود مگر آندسته از Logهايي كه از نتيجه كارهاي حجيم مانند Import Data و Index Creation ... توليد مي شود.
براي تغيير Recovery Model مي توان بصورت زير عمل كرد:

ضمیمه 69802

انواع Backup ها
انواع Backup هاي موجود در SQL Server به شرح زير است :

1. Full Backup
2. Deferential Backup
3. File \ File Group Backup
4. Transaction Log Backup

قبل از معرفي انواع Backup بايستي بدانيم كه هر دیتابیس شامل 2 دسته اطلاعات است. يكي اطلاعات كاربر (User Data) و ديگري اطلاعات مربوط به ساختار هاي دیتابیسSystem Data) .

Full Backup - Complete Backup
در اين نوع Backup از تمام اشياي دیتابیس و Data ها مي توانيد Backup بگيريد. اين Backup كاملترين نوع Backup بوده و با Restore آن تمامي اطلاعات دیتابیس بازسازي مي شود .
اين Backup همواره نسبت به زمان ايجاد دیتابیس ، ايجاد مي شود . در نتيجه اندازه اين نوع از Backup با افزايش حجم Database نيز زياد مي شود. استفاده از اين نوع Backup بسيار مطمئن مي باشد ولي بايستي طوري از آن استفاده شود تا اندازه فايل Backup دردسر ساز نشود.

Deferential Backup
دراين مدل Backup از تغييرات Data ها نسبت به آخرين Full Backup ، Backup تهيه مي شود. استفاده از اين مدل Backup از محدوديت هاي خاصي برخوردار است و بايستي براي حفظ امنيت اطلاعات طبق زمانبندي و بطور منظم Backup تهيه شود.به عنوان مثال
شنبه ساعت 8:00 يك Backup Full تهيه مي شود و در طي روز 3 Backup از نوع Deferential گرفته مي شود. اين مدل در طي هفته تكرار مي شود ولی Backup از نوع Full فقط هفته ايي يكبار گرفته مي شود. آخرين Deferential Backup روز 3 شنبه شامل تغييرات دیتابیس از آخرين Full Backup يعني Backup روز شنبه مي باشد..
اين Backup سريعتر و در اندازه كمتر گرفته مي شود و زمان بازيابي كمتري را صرف مي كند.

Transaction Log Backup
يكي از مهمترين انواع Backup براي نگهداري از دیتابیس هايي است كه بين 2 Full Backup تغييرات زيادي دارند.
در اين مدل Backup از تمامي Log Entry هاي غير فعال در فايل Log ، يك Backup تهيه مي شود. ارزش اين Backup در آن است كه در صورت خرابي دیتابیس مي توان دیتابیس را از روي آنها باز سازي كرد.

تعاريف پايه ايي براي Backup
براي تشريح موضوع بايستي ابتدا تعاريف زير را بيان نماييم :

Backup Set
مجموعه ايي از Backup ها روي يك يا چند Backup Media و يا تركيبي از چندين Backup Media را يك Backup Set گوييم.

Media Set
تركيبي از چندين Backup Media مانند Disk و Tape را گوييم.

Backup Media
سخت افزار هاي مورد استفاده در Backup گيري را گوييم.

Media Family
زمانيكه از چندين Backup Media براي تهيه Backup استفاده مي نماييم به آن Media Family مي گوييم. معمولا از اين كار براي ايجاد Mirror Backup و يا توزيع Backup يك دیتابیس روي چندين Media مورد استفاده قرار مي گيرد .در اين رابطه بايستي ملاحظاتي را در نظر بگيريم:

• در حالت Backup گيري چنانچه از چندين Media استفاده نماييد تمامي زير مجموعه هاي Backup ها بايستي در Backup Device ها شركت كنند.

Backup Device
شيي از SQL Server مي باشد كه وظيفه مديريت Backup هاي يك يا چندين دیتابیس را برعهده دارد. در واقع اين شيي مديريت Backup هاي تهيه شده و مديريت Backup Media را برعهده دارد. براي ايجاد اين شيي به صورت زير عمل مي كنيم در سمت Server Object ، عنوان Backup Device را انتخاب مي نماييم :

ضمیمه 69801

با انتخاب اين گزينه مي توان يك Device را انتخاب كرد. مزاياي استفاده از Backup Device عبارتند از :

1. Device Backup شامل يك فايل مي باشد و تمامي Backup هاي ايجاد شده در يك فايل نگهداري مي شود.
2. همواره كاربر با يك فايل به عنوان Backup روبرو است ولي بايد اندازه فايل Backup را كنترل كرده زيرا Backup ها در اين فايل بصورت تصاعدي افزايش حجم پيدا مي كند.

ضمیمه 69800

همانطور كه در شكل فوق ملاحظه مي فرماييد مجموعهايي از Backup ها در يك Device قرار دارد و در زمان Restore كردن اين امكان وجود دارد تا با انتخاب هركدام از Backup هاي اين مجموعه به دیتابیس آن دسترسي پيدا كزد.

ادامه دارد...

[Reza_Yarahmadi]

تهيه Backup
براي تهيه Backup بصورت زير عمل كنيد.
پس از ايجاد يك Backup Device از مسیر زير استفاده كرده و Backup را تهيه نماييد.

ضمیمه 69826

در واقع مطلب ذكر شده ، ميسر اصلي براي تهيه Backup مي باشد ولي مسير كوتاه براي تهيه Backup بصورت زير است
روی دیتابیسی که قصد Backup گیری آن را دارید راست کلیک کرده و از منوی Tasks گزینه Back Up را انتخاب نمایید.

ضمیمه 69827

1 - Database : نام دیتابیسي كه مي خواهيد از آن Backup تهيه نماييد
2 - Recovery Model : Recovery Model دیتابیسي كه از آن Backup خواهيد گرفت
3 - Backup Type : در اينجا يكي از انواع Backup را مشخص مي كند

• Full
• Deferential
• Transaction Log

نكته:
اگر امكان Backup از نوع Transaction Log و يا Deferential ميسر نباشد Recovery Model ، دیتابیس از نوع Simple مي باشد.

4 - اين قسمت مشخص می کند كه از دیتابیس Backup بگيريد يا از قسمتي از آن به عنوان File / File Group بک آپ بگیرید.
5 - Backup Set Name : در اين قسمت مي توانيم نامي براي Backup خود انتخاب نماييد. توصيه مي شود براي مشخص شدن Backup ها از اين امكان استفاده نماييم.
6 - Backup Set Description : براي Backup مي توانيد شرح مختصري بنويسيد كه به عنوان تاريخچه Backup در دیتابیس MSDB نگهداري مي شود.
7 - Backup Set Expire Date : اين امكان خوبي براي Backup مي باشد. در زمان اعتبار Backup امكان Overwrite شدن روي Backup File وجود ندارد مگر آنكه كاربر Backup Set Name را كه در قسمت Option مشخص مي شود را بداند. مدت اعتبار Backup به دو صورت مشخص ميشود :

• بصورت زمان مشخص از لحظه تهيه Backup
• در زمان مقرر شده

8 - Destination : همان موقعيت Backup گيري مي باشد . كه همانطور كه در شكل مشخص است Disk يا Tape مي باشد .
چنانچه از Device استفاده شده باشد ، Device به عنوان پيش فرض براي Backup گيري انتخاب شده است در غير اين صورت با استفاده از Add مي توانيم Device يا ساير Backup Device ها را براي آن مشخص نماييد.

ضمیمه 69828

1 - مي توان يك مسير فيزيكي را به عنوان Path به آن معرفي کرد.
2 - از ساير Device هاي موجود براي Backup استفاده کرد.
در قسمت 1 مي توان علاوه بر Path ، از URL نيز استفاده نمود. براي استفاده از اين امكان حتما بايستي تنظيمات لازم را انجام داد. در قسمتهاي آينده بيشتر به آن مي پردازيم.

در قسمت Option موارد زير مشاهده مي شود

ضمیمه 69829

1 - در اين قسمت مديريت Backup Media Set را مي توان انجام داد
2 - در اين حالت مي توان به Backup Set موجود يك Backup اضافه كرد .
3 - در اين حالت مي توان روي Backup Set موجود Overwrite كرد. به عبارت ديگر با انتخاب اين آيتم Backup گرفته شده روي Backup Set قديمي كاملا Overwrite شده و اطلاعات قديم آن از بين مي رود.
4 - در اين آيتم مي توان نامي را انتخاب كرد كه در واقع اين نام كلمه ايي براي مشخص كردن اعتبار Backup مي باشد.
5 - اين آيتم صحت Backup گرفته شده را مورد بررسي قرار مي دهد. در صورت وجود اشكال در دیتابیس Backup گرفته شده اشكال دارد و امكان Restore كردن آنرا كم مي كند. توصيه مي شود كه براي Backup هاي گرفته شده اين آيتم را انتخاب نماييد. انتخاب اين آيتم براي دیتابیس هاي بزرگ توصيه مي شود. ضمنا اين آيتم زمان Backup گيري را كمي افزايش مي دهد.
6 - Checksum به مجموعه كنترل هايي اطلاق مي شود كه در طي انجام كاري بايستي وجود داشته باشد تا در زمان رخداد خطايي از پيشرفت آن كار جلوگيري كند .در SQL Server عمل Checksum براي 3 مورد زير انجام مي شود .

1. براي Torn page
2. براي Log Block
3. Backup گيري

در SQL Server اين امكان وجود دارد تا با انجام Checksum در عمل Backup گيري ، از Backup تهيه شده بيشتر اطمينان حاصل كرده و در واقع Backup مطمئن تري داشته باشيم. عمل Checksum براي Backup ، قبل از آنكه Page هاي Backup روي Backup Medial نوشته شود ، در Level- Page مورد بررسي قرار داده مي شود و چنانچه مشكلي مشاهده شود عمل Backup گيري را متوقف مي كند.
استفاده از اين عمل همانند Verifying Backup مطمئن تري را مي سازد. انتخاب اين مورد نيز براي دیتابیس هاي بزرگ توصيه مي شود.
در زمان Backup گيري اين امكان وجود دارد تا با وجود خطا عمل Backup گيري با موفقيت انجام شود.(گزینه Continue on error)
7 - در SQL Server براي مديريت Log هاي دیتابیس شرايطي جديد را در اختيار كاربر قرار مي دهد. يكي از اين امكانات Backup گرفتن از Transaction Log هاي خاتمه يافته مي باشد.
با كمك Backup از تراكنش ها مي توان مجموعه ايي مطمئن تر از Backup ها را براي نگهداري دیتابیس فراهم آورد. يا با كمك اين آيتم Log هاي خانمه يافته را از Log File خارج نمود.
عمل شماره 7 امكان حذف Log هاي خاتمه يافته را مي دهد. از اين آيتم در مواقعي كه Log File بزرگ شده باشد و از آنها Backup ايي به عنوان تاريخچه احتياج نباشد مي توان استفاده كرد. توجه كنيد اين امكان اندازه Log File را كوچك نمي كند بلكه اطلاعات بلا استفاده در Log File را از Log File خالي مي كند.
8 - معمولا در زمان خرابي دیتابیس مي توان در شرايطي خاص دیتابیس را بازيابي كرد .اين امكان در نسخه هاي قبلي SQL Server بصورت دستي بايستي صورت مي گرفت و معمولا از طريق Transaction Log Backup ها به سختي مي توانست دیتابیس رابازسازي كند ولي در نسخه 2005 امكانی قرار دارد كه در زمان بازيابي دیتابیس از طريق Transaction Log هم از دیتابیس يك Transaction Log Backup گرفته شود و دیتابیس را در وضعيتي قرار دهد تا بتوان از طريق Backup هاي قبلي و Transaction Log فعلي دیتابیس رابازسازي كرد ولي بايد توجه داشته باشيد Recovery Model براي دیتابیس دقيقا در استفاده از اين امكان موثر است به عبارت ديگر در زمان Recovery Model = Simple امكان تهيه Transaction Log Backup مقدور نيست. اين قسمت را در Restore بيشتر توضيح مي دهيم
9 - در اين قسمت مديريت Tape انجام مي شود.

1. پس از انجام Tape ، Backup را Load كند یا نه.
2. پس از اتمام Tape ، Backup را به ابتداي نوار آورد يا خير.

(عکسهایی که در این پست قرار داده شده به دلیل عدم دسترسی به 2005 از 2008 گرفته شده. ولی موارد توضیح داده شده همگی در 2005 وجود دارند)
ادامه دارد...

[Reza_Yarahmadi]

Backup Mirroring (Backup Family)
Backup يك دیتابیس بعنوان مهمترين و قابل اتكاترين شيي براي بازيابي اطلاعات يك پايكاه داده مي باشد لذا در امر Backup گيري از دیتابیس بايستي طوري عمل شود تا با حداقل خطا بتوان دیتابیس را بازسازي كنيم. يكي از راه هاي رسيدن به اين مهم استفاده از Mirrored Media Set مي باشد. مفهوم آن بصورت زير است:

ضمیمه 69878

Media Family 1 , Medial Family 2 بعنوان Backup Media و همانطور كه در شكل مي بينيد 4 عدد Tape بعنوان Backup Media وجود دارد.
Mirror 2 يك كپي مشابه از Mirror 1 مي باشد. به عبارت ديگر زمانيكه از دیتابیس يك Backup و Mirror آن تهيه مي شود. 2 Backup بصورت مستقل توليد مي كند و براي Restore آنها هر كدام بصورت مستقل از هم قابل Restore شدن مي باشند.
Mirror مي تواند روي Disk , Tape گرفته شود. در زمان Backup / Restore وجود Mirror الزامی نمي باشد .
براي اين كار مي توان بصورت زير عمل كرد :

Backup Database MyDB

To Disk  = 'c:\sss.bak'

mirror to disk = 'c:\ssss.bak'

      with format

روش Backup گيري با URL
در بسياري از مواقع لازم مي شود تا Backup را روي Backup Media خارج از سیستم Server گرفته شود. از اين رو ضرورت وجود امكان تهيه Backup روي يك Media در شبكه لازم مي شود. براي استفاده از اين امكان بايستي نكاتي را در نظر بگيريم كه به شرح زير مي باشد :

1. Startup Account مربوط به سرويس SQL Server را با Administrator Domain و در صورت Workgroup با كاربر Administrator كامپيوتر مبدا تنظيم شده باشد.
2. در كامپيوتر مقصد Folder با مجوز Write براي كاربري كه Service را Start كرده است ، ايجاد شود.
3. در قسمت معرفي مسير Backup گيري از عبارات ComputerName\ShaeredFolder\...) URL) استفاده شود.

Partial Backup
در SQL Server 2005 نوعي جديد از Backup گيري ايجاد شده است كه با كمك آن مي توان شبيه به يك Full Backup از اشياي زير Backup تهيه مي كند:

1. تمامي Data ها در Primary File Group
2. تمامي اطلاعات در Read – Write File Group
3. تمامي اطلاعات از فايلهاي Read – Only

ضمیمه 69879

نكته :
يك Partial Backup از دیتابیس Read-only فقط از گروه Backup ، Primary تهيه مي كند.
اين نوع Backup در Backup گيري از دیتابیس در مدل Simple بيشتر مورد استفاده قرار مي گيرد هر چند كه در ساير مدل ها نيز اين امكان وجود دارد.

Backup گيري با دستور T-SQL

 
BACKUP DATABASE { database_name | @database_name_var } 

TO < backup_device > [ ,...n ] 

[ [ MIRROR TO < backup_device > [ ,...n ] ] [ ...next-mirror ] ] 

[ WITH 

     [ BLOCKSIZE = { blocksize | @blocksize_variable } ] 

     [ [ , ] { CHECKSUM | NO_CHECKSUM } ]

     [ [ , ] { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } ]

     [ [ , ] DIFFERENTIAL ] 

     [ [ , ] PASSWORD = { password | @password_variable } ] 

     [ [ , ] { Init | NOINIT  } ] 

]

 

مثلا:

BACKUP DATABASE MyDB

TO DISK='c:\MyFolder\BackupDB.bak

برخي از كارها را نمي توان با كمك ابزار گرافيكي Backup گيري انجام داد و بايستي با استفاده از دستورات اين كارها را انجام داد. اين كارها در مجموعه Switch هاي دستور مي با شد كه در اينجا تعدادي از Switch هاي مهم را مورد بررسي قرار مي دهيم :

CHECKSUM | NO_CHECKSUM
CheckSum برای Backup محاسبه شود یا نه.( در زمان Restore کردن ، در نظر گرفتن CheckSum اختیاری است.)

STOP_ON_ERROR | CONTINUE_AFTER_ERROR
در زمان Backup گيري چنانچه Error رخ دهد بايستي كار Backup گيري ادامه داشته باشد و يا متوقف شود.

DIFFERENTIAL
انجام Backup روش deferential با اين Switch امكان پذير است.

{‌PASSWORD = {password‌
مي توان براي Backup گرفته شده Password قرار داد و در زمان Restore با ارائه Password دیتابیس بازيابي شود.

INIT | NOINIT
Init امكان Overwrite كردن روي دیتابیس موجود را فراهم مي آورد و NOINIT عمل عكس را انجام مي دهد.

ادامه دارد ...

[Reza_Yarahmadi]

(قبل از هر چیزی بخاطر تاخیر به وجود آمده از همه دوستان عذرخواهی میکنم)
Restore كردن دیتابیس
Restore كردن دیتابیس يعني بازسازي دیتابیس از Backup File
هر فايل Backup به صورت زير تشكيل شده است :

1. اطلاعات در مورد Backup و اطلاعات كلي در مورد Database كه در Header فايل Backup ايجاد مي شود اين اطلاعات شامل:

• Name : نام Backup Set
• Component : عناوين Backup Component مانند Database , File و براي Null , Transaction Log مي باشد.
• Type : نوع Backup مانند Full , Deferential , Transaction Log
• Server : نام Server ايي كه دیتابیس روي آن قراردارد.
• Database : نام دیتابیسی كه از آن Backup تهيه شده است.
• Position : محل قرار كرفتن Backup set روي Diskها مي باشد.
• First LSN : شماره اولين Transaction Log در Backup Set مي باشد.
• Last LSN : شماره آخرين Transaction Log در Backup Set مي باشد.
• Checkpoint LSN : شماره آخرين Transaction Log ايي كه روي Checkpoint زده شده باشد.
• و ساير طلاعات ديگر.

در زمان Restore شدن دیتابیس ابتدا اين اطلاعات از Header خوانده شده و جنانجه براي Restore مشكلي نباشد ، از روي ساير اطلاعات فايلهاي دیتابیس را ايجاد شده و پس از عمل Recovery Database بازيابي مي شود.
براي آنكه از اطلاعات درون يك Backup File را ببينيم از دستور زير مي توان استفاده نمود

RESTORE FILELISTONLY 

FROM <DISK , DEVICE> = <Backup File Path , Device Name>

و يا در زمان Restore در قسمت Option اطلاعات Header را مي توان مشاهده نمود.
براي Restore كردن دیتابیس بصورت زير عمل مي كنيم :

ضمیمه 76974

و سپس

ضمیمه 76975

1 - محل نوشتن نام دیتابیس
2 - Restore كردن دیتابیس در زمان مشخص(براي Restore كردن Transaction Log در تاريخ و زمان مشخص)
براي بازيابي دیتابیس ميتوان از دو موقعيت استفاده كرد.
3 - From Database : در اين Option امكان بازيابي دیتابیس از تاريخچه Backup هاي تهيه شده قبلي وجود دارد. اين تاريخچه در Database MSDB مي باشد. تا زمان Restore شدن مجدد دیتابیس اين تاريخچه كاملتر مي شود. در اين تاريخچه Backup ها با توجه به نوع Backup ها مشخص شده و اين Option از هوشمندي خاصي در زمان بازيابي ازاطلاعات برخوردارد است.
4 - From Device : امكان بازيابي دیتابیس از طريق Backup Device با (Backup File (Path امكان پذير مي باشد.
براي استفاده از اين امكان روی دکمه .... کلیک نمایید:

ضمیمه 76976

همانطور كه مشاهده مي شود از دو موقعيت File , Backup Device ميتوان دیتابیس را Restore كرد .با انتخاب گزينه ADD در مورد فايل ميتوان آدرس مربوط به فايل Backup را پيدا كرد و مي توان مسير فايل را مشخص كرد.

نكته:
نوع فايل بصورت پيش Bak.* و Trn.* مي باشد.

و در صورتي كه از Device استفاده نماييم :

ضمیمه 76977

مي توان از ليست مربوط به Backup Device ها استفاده كرد تا دیتابیس را Restore كرد.
5 - ليست Backup هاي قابل دسترس را مي توان مشاهده كرد. چنانچه از Backup Device استفاده كنيم ، ليست Backup هاي موجود در Backup Device را مي توان مشاهده كرد.

نكته :
براي Restore كردن دیتابیس حتما بايستي Backup مورد نظر را انتخاب كرد.


در Option موارد زير را مي توان مشاهده كرد.

ضمیمه 76978

1 - چنانچه بخواهيم Backup يك دیتابیس را روي دیتابیس موجود Restore كنيم بايستي روي دیتابیس موجود Overwrite كنيم . براي اين كار از اين گزينه استفاده مي نماييم.
2 - در زمانيكه دیتابیس شامل Replication باشد . براي Restore كردن آن بايستي از اين آيتم براي Restore كردن آن استفاده شود.
3 - براي زمانيكه بخواهيم دیتابیس ها را بطور متوالي Restore كنيم ، قبل از هر بار Restore از كاربر تاييديه مي گيرد.
4 - دیتابیس را در حالتي Restore مي كند كه دیتابیس با دسترسي هاي محدود مثلا Read Only يا Single User بازيابي شود.
5 - در اين جدول اطلاعات مربوط به Header فايل Backup را نمايش مي دهد. در اين جدول اگر بخواهيم مسير دیتابیس را عوض كنيم مي توانيم در قسمت Restore As مسير را به مسير جديد تغيير دهيم.
6 - در زمان Restore كردن دیتابیس ، چنانچه بخواهيم پس از Restore شدن دیتابیس بصورت عملياتي در اختيار كاربران قرار بگيرد از اين گزينه استفاده مي نماييم . توجه داشته باشيد در زمان بازيابي دیتابیس از روي Log File ، چنانچه از اين گزينه استفاده نماييم ، امكان Restore كردن Log File هاي ديگر را نداريم و دیتابیس بصورت عملياتي در اختيار كاربر قرار مي گيرد.
7 - در اين گزينه به عكس 6 اين قابليت وجود دارد تا پس از Restore كردن دیتابیس Transaction Log روي دیتابیس Restore شود . در اين حين دیتابیس بصورت عير عملياتي و با نماد Loading نمايش داده مي شود و هيچ كاربري نمي تواند با آن كار كند.
8 - در اين حالت شبه 7 عمل مي شود و فقط بجاي غير عملياتي بودن ، دیتابیس بصورت Read Only در اختيار كاربر قرار مي گيرد . امكان Restore كردن دیتابیس هاي ديگر نيز وجود دارد . در اين گزينه اين امكان وجود دارد تا با استفاده از Undo File عمل Restore كردن Transaction Log ها را كنترل كنيم.

Restore كردن دیتابیس با استفاده از T-SQL

RESTORE DATABASE { database_name | @database_name_var } 

[ FROM <backup_device> [ ,...n ] ]

[ WITH 

   [ { CHECKSUM | NO_CHECKSUM } ]

   [ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]

   [ [ , ] KEEP_REPLICATION ] 

   [ [ , ] MOVE 'logical_file_name' TO  

                                  'operating_system_file_name'         

   ][,...n ] 

   [ [ , ] PASSWORD = { password | @password_variable } ] 

   [ [ , ] { RECOVERY | NORECOVERY | STANDBY = 

          {standby_file_name | @standby_file_name_var } 

   } ] 

   [ [ , ] REPLACE ] 

   [ [ , ] RESTRICTED_USER ] 



]

با استفاده از دستورات مانند Backup گيري مي توان از امكانات بيشتري در زمان Restore استفاده كرد .
در اينجا به تعدادي از Switch هاي مهم اشاره مي نماييم :

CHECKSUM | NO_CHECKSUM:
CHECKSUM در زمان Restore كردن دیتابیس ، ساختار دیتابیس را مورد بررسي قرار داده و چنانچه دیتابیس از نظر ساختاري مشكلي باشد Restore را متوفق كند.

KEEP_REPLICATION
در زمان Restore تنظيمات مربوط به Replication را حفظ كند

MOVE 'logical_file_name' TO
'operating_system_file_name'
در زمان Restore مي توان با كمك اين Switch ميسر فيزيكي فايلهاي دیتابیس را عوض كرد.

REPLACE
هم مفهوم با Force Restore over existing Database مي باشد.

RECOVERY | NORECOVERY | STANDBY
RECOVERY : براي Restore كردن Log File اجازه مي دهد تا پس از Restore كردن ، Log هاي بعدي روي دیتابیس خوانده شود و دیتابیس در وضعيت غير عملياتي قرار مي گيرد.
NORECOVERY : پس از Restore كردن Log File امكان بازيابي Log هاي بعدي وجود ندارد و دیتابیس در وضعيت عملياتي قرار مي گيرد .
STANDBY : پس از بازيابي دیتابیس امكان بازيابي Transaction Log وجود دارد ولي دیتابیس در وضعيت Read Only قرار مي گيرد.

[Reza_Yarahmadi]

Database Snapshot
امكاني جديد در SQL Server 2005 مي باشد كه يك دبتابیس كاملا Read only و ثابت از دبتابیس كاري كاربر ايجاد ميكند. اين دبتابیس تمامي دبتابیس اصلي منهاي تراكنشهاي باز مي باشد. Database Snapshot در همان سروري است كه دبتابیس اصلي آنجا قرار دارد. از اين امكان مي توانيم :

1. براي استفاده از Reporting هايي كه صحت آنها لحظه ايي نمي باشد استفاده نماييم.
2. در زمان رخداد Error در دبتابیس اصلي با رجوع به Database Snapshot مي توانيم دبتابیس اصلي را در State ايي قرار دهيم كه از آن Snapshot ساختيم .

Database Snapshot چگونه كار مي كند ؟
Database Snapshot در سطح Page هاي دبتابیس كار ميكند .قبل از آنكه Page از دبتابیس اصلي تغيير كند، آن Page از دبتابیس اصلي در Database Snapshot كپي مي شود. به اين عمل COPY- ON –WRITE مي گوييم. و از لحظه تهيه Snapshot تغييرات در Page ها روي Database Snapshot منتقل نمي شود. اين اتفاق تا زماني كه Snapshot مجددا تهيه نشود تغيير نمي كند و همچنان تا زمان تهيه Snapshot از دبتابیس اصلي باقي مي ماند .
براي نگهداري از كپي Page ها در Snapshot Database از تكنيك Sparse File استفاده مي كند . در ابتدا يك Sparse File خالي كه هيچ Data از كاربر ندارد و فضايي را هم اشغال نمي كند در دبتابیس اصلي مي سازد. و در زمان Update شدن Page ها در دبتابیس اصلي آنها را Update كرده و در زمانيكه از دبتابیس يك snapshot تهيه مي شود آنها را روي Database Snapshot اعمال مي كند و Snapshot را بروز مي كند .
اين تكنيك از امكانات NTFS مي باشد و از هدر رفتن فضاي خالي در سطح Disk جلوگيري مي كند.
به شكل زير توجه كنيد

ضمیمه 77140

همانطور كه در شكل بالا مي بينيد در زمان تغيير Page در Page اصلي ، Sparse Page را Update مي كند و در زمانيكه Snapshot تهيه مي شود فقط تغييرات در Spars File روي Snapshot را بروز مي كند.

ايجاد Snapshot Database
با استفاده از Create Database مي توان Snaoshot را ايجاد كرد .

Create Database  <database Snapshot  نام > On

(Name = 'نام فايل' , FileName = <مسير فايل>) , ….. (n)

As Snapshot Of <نام ديتابيس اصلي >

مثلا

CREATE DATABASE DbSnap1 ON 

( NAME = N'Db_dat', FILENAME = N'E:\Data\Dbdat1.mdf'  ), 

( NAME = N'HRM_Data', FILENAME = N'E:\Data\Db_101.mdf'  ), 

( NAME = N'Base_Data', FILENAME = N'E:\Data\Db_Base_Data1.mdf' ), 

( NAME = N'Blob_Data', FILENAME = N'E:\Data\Db_Blob_Data1.mdf' ), 

( NAME = N'Index_Data', FILENAME = N'E:\Data\Db_Index_Data1.mdf'  ), 

( NAME = N'Temp_Data', FILENAME = N'E:\Data\Db_Temp_Data1.mdf'), 

( NAME = N'Tran_Data', FILENAME = N'E:\Data\Db_Tran_Data1.mdf' ), 

( NAME = N'trs_Data', FILENAME = N'E:\Data\Db_211.mdf' )

as 

snapshot of [DbName]

خواندن اطلاعات از Snapshot Database
دیتابیس Snapshot بصورت Read Only مي باشد. به همين دليل خواندن اطلاعات از Snap shot همواره از Page هاي اصلي دیتابیس خوانده مي شود.

ضمیمه 77141

چنانچه اطلاعات Page ها از دیتابیس اصلي تغييري نكرده باشد اطلاعات از Snapshot خوانده مي شود.

ضمیمه 77142

بعداز آنكه Page از دیتابیس تغيير كرد عمل خواندن اطلاعات در Snapshot هنوز به Page هاي اصلي كه در Sparse File ها وجود دارد دسترسي دارد و آن اطلاعات را مي خواند.


بازيابي Database ازروي Database Snap shot
با كمك دستور زير مي توان دیتابیس را بازيابي كرد

Restore Database   <نام Database>

From Database_SnapShot  =  <نام Database Shot >

محدوديت هاي Snapshot Database براي دیتابیس اصلي

1. در زمان وجود دیتابیس Snapshot امكان Drop و Detach و Restore كردن دیتابیس وجود ندارد. Backup گرفتن از Snapshot بصورت نرمال كار مي كند و در كليات دیتابیس اختلالي وارد نمي شود.
2. روي دیتابیس اصلي كاهش كارايي در نوشتن اطلاعات بوجود مي آيد. براي Write – on – Copy Page هايي كه تغييرات زياد دارند ، كندي محسوس است.
3. فايلهاي دیتابیس اصلي را نمي توان پاك كرد.

محدوديت هاي Snapshot Database

1. بايستي Snapshot Database در Server ايي ايجاد شود كه دیتابیس اصلي روي آن ثبت شده است.
2. Snapshot يك تصوير واقعي از دیتابیس اصلي بدون تراكنشهاي باز در زمان تهيه snapshot از دیتابیس اصلي مي باشد.
3. در زماني كه اطلاعات را از دیتابیس اصلي به Snapshot منتقل مي كنيم ، چنانچه فضاي كافي در Database Snapshot وجود نداشته باشد Suspect ، database Snapshot مي شود.
4. Snapshot هميشه Read – Only است.
5. Snapshot Database قابل Backup يا Restore شدن نمي باشد.
6. Snapshot Database را نمي توان روي FAT32 و یا FAT 16 ايجاد كرد.
7. Full Text Indexing در Snapshot پشتيباني نمي شود.

[Reza_Yarahmadi]

مقدمه :
SQL Server شرايطي را فراهم آورده است تا نگهداري یا اصطلاحا Administration تا حدودي آسانتر شود. اين مجموعه از كارها بايد بصورت اتوماتيك و طي زمانبندي بايد انجام شود .
در SQL Server شيي وجود دارد كه قابليت نگهداري دستورات را دارد و اين دستورات را با توجه به زمانبندي تعريف شده اجرا می نمايد.
در اين قسمت با اين قابليت آشنا مي شويم .

SQL Server Agent
SQL Server Agent يك سرويس است كه قابليت برنامه ريزي براي اجراي دستورات روي دیتابیس را دارد .
اين سرويس بطور پيش فرض Stop مي باشد و بايد در زمان نصب SQL Server اين Service را در حالت Auto Start قرار دهيم.

Job
شيي در SQL Server است كه مي تواند دستورات مرتبط با Administration را نگهداري كند. هر Job داراي چندين Step است كه اين Step ها با مديريت توسط Agent اجرا مي شود. در هر Step دستورات T-SQL نوشته مي شود .
به عنوان مثال مي خواهيم از دیتابیس بصورت دوره ايي Backup تهيه كنيم. اين عمل بايستي بصورت هفتگي و در ساعتي مشخص اتفاق افتد. برای این کار می توان مجموعه اين اعمال را در یک Job تعریف نمود تا بصورت زمانبندي شده از دیتابیس Backup تهیه نماید.
براي مديريت Job در SQL Server مي توان بصورت زير عمل كرد.

ايجاد Job
براي ايجاد Job بصورت زير عمل مي كنيم :

ضمیمه 79263

ضمیمه 79264

مدیریت Step های هر Job از طریق تب Step در پنجره ایجاد Job امکان پذیر می باشد. برای افزودن Step جدید پس از کلیک بر روی تب Step ، بر روی دکمه ...New کلیک نمایید.

ضمیمه 79265

و در Advance مي توان اطلاعات زير را مشاهده نمود :

ضمیمه 79266

1. فعاليتي كه بايد پس از اجراي آن Step انجام شود در صورتي كه اين Step با موفقيت انجام شود.
2. براي اجراي اين Step چند بار تلاش كند.
3. بين هر با اجرا اين Step چقدر فاصله زماني ايجاد كند.
4. فعاليتي كه بايد پس از اجراي Step انجام شود در صورتي كه اين Step اجرا نشود.
5. در قسمت Output File چنانچه Step داراي خروجي باشد می توان خروجي آنها را در File ذخيره كرد .

همانطور كه قبلا اشاره شد Job ابزاري براي انجام دادن فعاليتهايي است كه مي خواهيم بصورت اتوماتيك توسط Agent روي Database صورت بگيرد .پس اين Job بايستي Schedule شود براي اين كار مي توان بصورت زير عمل كرد.

Schedule كردن Job
براي اين كار بر روی تب Schedule کلیک کرده و در صورت نیاز به زمانبدی جدید بر روی دکمه ...New کلیک نمایید.

ضمیمه 79267

[Reza_Yarahmadi]

مفاهيم Tuning:
زمانيكه SQL Server را نصب مي نماييد SQL Server با پيش فرضهاي مشخصي نصب مي گردد.
اين تنظيمات براي پايگاه داده هايي كه از اندازه زيادي برخوردار هستند ، مناسب نمي باشد و براي بالا بردن سطح كارايي دیتایبس بايستي تنظيمات لازم صورت بگيرد.
اين تنظيمات براي هر SQL Server يكسان نمي باشد و بايستي هر Server را بنا به نياز ها تنظيم كرد.
در نهايت انجام تنظيمات انتظار می رود كارايي دیتایبس متناسب شود. به عبارت ديگر پس از انجام تنظيمات انتظار می رود

1. SQL Server در مقابل در خواست هاي كاربران ، سريعا پاسخدهي كند.
2. امنيت data ها را تضمين كند.
3. در برابر استفاده از منابع سخت افزاري طوري مصرف كند تا مجموعه Server و Client ها كار خود را به نحو مطلوبي انجام دهند .

معمولا در Tuning يك SQL Server نقاط زير به عنوان گلوگاه هاي سرعت سيستم شناسايي مي شوند

System Resources

1. Disk
2. RAM
3. Processor

Network
Operating system

1. Virtual Memory
2. Processor Scheduling
3. Memory Management
4. AWE

Database Tuning
Client Tuning

براي تنظيم اين 5 نقطه معمولا با استفاده از ابزارهاي Tuning بايد آماري از كاركرد آنها تهيه كرد و بعد تنظيمات لازم را انجام داد. براي بررسي دقيق اين نفاط بايستي در زمان Peak عمليات آنها را مورد ارزيابي قرار داد.


بطور خلاصه مي توان اتفاقاتي كه باعث كندي SQL Server مي شوند را با عناوين زير شمرد :

1. در نقاط كه عمليات I/O زياد روي حجم زيادي از اطلاعات بارها تكرار شود.
2. تعداد كاربران متناسب با فضاهاي كاري در اختيارشان از Server نباشند و كاربران در استفاده از منابع Server با نقصان روبرو شوند.
3. ترافيك اطلاعات در شبكه به حدي زياد باشد تا ارتباط با Server با كندي صورت بگيرد . در اين حالت Virus يكي از عواملي است كه ترافيك شبكه را بالا برده و امكان ارتباط را سخت تر مي كند.
4. تخصيص منابع سخت افزاري در Server متناسب با نياز هاي اجراي برنامه ها تهيه نشده باشد.
5. پياده سازي سيستم ، با كيفيت متناسب با كارايي مناسب توليد نشده باشد كه در اين حالت بايستي سيستم اصلاح شود.

ابزارهاي Tuning
بعد از مشخص شدن مفهوم Tuningبايستي با كمك ابزارهاي Tuning عمل Tuning را انجام دهيم :

ابزارهاي Tuning در سيستم عامل
Windows Monitor

با استفاده از اين ابزار مي توان عملكرد

1. Windows در مورد استفاده از منابع Server مانند Disk ها و Memeory و Processor و Lan و....
2. رفتارهاي SQL Server در مورد استفاده از منابع Server

را مورد بررسي قرار داد.


Performance Log and Alert
براي بررسي عملكرد سرويس Notification Service از اين ابزار استفاده مي شود.



Task Manager
ساده ترين ابراز براي بررسي لحظه به لحظه استفاده از Ram و Processor و ميزان Page File و همچنين رفتار هاي Lan مورد بررسي قرار مي گيرد .

ابزارهاي Tuning در SQL Server
SQL Profiler
ابزاري براي بررسي عملكرد SQL Server و در واقع عملكرد Service در ازاي Request ارسالي از Client ها. اين ابزار را كاملا تشريح خواهيم كرد.


SQL Server Management Studio Activity Monitor
در این قسمت مي توان از كاربران متصل به SQL Server اطلاعاتي شامل SPID ، زمان هاي Login ، آخرين Statement ارسالي به Server و مديريت Lock شدن User ها را مورد بررسي قرار داد.


SQL Server Management Studio Show Plan
براي بررسي منابع مصرف شده در اجراي يك Query مورد استفاده برنامه نويسان و كسانيكه مي خواهند Query هاي برنامه را Tune كنند مورد استفاده قرار مي گيرد.

Stored Procedure
تعدادي Procedure سيتسمی براي بررسي شرايط دیتابیس وجود دارد كه با استفاده از آنها مي توان آماري مناسب تهيه كرد و Tuning را انجام داد.

DBCC
مجموعه ايي از دستورات مي باشد كه با كمك آنها مي توان از دیتابیس گزارشاتي گرفت و دیتابیس را Tune كرد. اين دستورات بعضا قابليت اصلاح اشكالات را با اعمال Switch هايي دارند.

معرفي Profiler
يكي از بهترين ابزارها براي بررسي دستوراتي كه سرويس هاي SQL Server دريافت مي نمايند.
هر نوع دستور SQL كه به تنهايي يا از داخل يك برنامه يا از طرف يك روال ذخيره شده (Stroed Procedure) و يا هر جاي ديگر اجرا شود توسط اين برنامه شناسايي و ثبت مي‌شود. سپس برنامه مذكور عمل تجزيه وتحليل خود را بر روي اين دستور SQL انجام داده و نتايج آن را به مدير سيستم نمايش مي‌دهد

اين ابزار براي بررسي برنامه هايي كه Source آنها بسته شده است و امكان Trace برنامه مثلا در سايت مشتري وجود ندارد ، مورد استفاده قرار مي گيرد.
از كاربرهاي اين برنامه مي توان به موارد زير اشاره كرد :

1. بهترين ابزار براي بررسي كارايي دستوراتي است كه از جانب كاربران به SQL Server ارسال مي شود.
2. با كمك اين ابزار مي توان شناسه SPID كاربر ، ميزان زمان مصرفي براي پاسخدهي به آن درخواست ، ميزان مصرف از منابعي مانند I/O و CPU را بررسي كرد.

نحوه كار برنامه Profiler
برنامه Profiler ليستي از رخدادهايي را كه قادر به تعقيب آن‌ها است در اختيار كاربر قرار مي‌دهد. اين رخدادها پس از انتخاب كاربر در درون يك صف (Queue) قرار گرفته و هرگاه يكي از رخدادها به وقوع بپيوندد، Profiler شرح كاملي از جزييات آن را در يك فايل جهت گزارش به مدير سيستم ثبت مي‌كند. اين عمليات تعقيب كه در Profiler به آن Trace گفته مي‌شود كاملاً توسط كاربر قابل تنظيم است.
رخدادهاي قابل تعقيب توسط پروفايلر به انواع مختلفي تقسيم‌بندي مي‌شوند كه در قسمت Events از منوي WewTrace يعني زماني كه كاربر قصد تعريف يك تعقيب جديد را دارد، مشاهده مي‌شوند.


1 - Cursors
اين مجموعه رخدادهاي مربوط به اتفاقاتي است كه باعث ايجاد شدن، مورد استفاده قرار گرفتن و حذف شدن يك دسته ركوردهاي اطلاعاتي از يك يا چند جدول مي‌شود. همان‌طور كه مي‌دانيد در SQL Server مي‌توان با استفاده از دستور SELECT تعدادي از جداول بانك اطلاعاتي را با هم لينك كرده و مجموعه ركوردهاي اطلاعاتي مربوطه را در يك گروه به نام كرسر قرار داد (همان چيزي كه در زبان‌هاي برنامه‌نويسي مثل ويژوال بيسيك به آن Recordset گفته مي‌شود) هر عملي كه باعث ايجاد شدن يا هر نوع عمليات ديگر بر روي يك كرسر شود مي‌تواند مورد تعقيب پروفايلر قرار گرفته و ثبت شود.

2 - Data Base
اين مجموعه از رخدادها مربوط به فايل‌هاي داده‌اي يك بانك اطلاعاتي است. هر تغييري كه در ساير فايل‌هاي داده‌اي و فايل‌هاي لاگ يك بانك ايجاد شود در اين مجموعه قرار مي‌گيرد.

3 - Errors and Warning
پيام‌هاي خطا و هشدار كه در زمان اجراي دستورات SQL يا در زمان كامپايل و اجراي SPها و يا Triggerها به كاربر داده مي‌شود و همچنين خطاهاي مربوط به OLE DB در اين گروه قرار مي‌گيرد.

4 - Locks
اين گروه از رخدادها، بيشتر زماني مورد استفاده قرار مي‌گيرد كه يك برنامه كاربردي در قفل كردن و آزاد كردن ركوردهاي جداول بانك اطلاعاتي دچار ضعف و اشتباه مي‌شود.
همان‌طور كه مي‌دانيد بسياري از برنامه‌هاي كاربردي در مقاطع زماني خاص اقدام به قفل كردن يك يا چند جدول اطلاعاتي مي‌كنند كه اين كار و همچنين آزاد كردن آن جداول بايد با حساسيت و دقت خاصي انجام شود تا در كار بقيه كاربران اخلال ايجاد نكند اما متأسفانه بسياري از اين نوع برنامه‌ها خصوصاً برنامه‌هايي كه قدمت چنداني ندارند اغلب از اين لحاظ دچار بي‌دقتي و ضعف زيادي هستند.

5 - Scans
هر عملي كه در حافظه اصلي تخصيص داده شده به SQL server قابل دستيابي باشد در اين دسته قرار مي‌گيرد. بخصوص عمليات مربوط به Cache كه در داخل موتور بانك اطلاعاتي انجام مي‌شود جزو اين دسته محسوب مي‌شوند.

6 - Stored procedures
شامل كليه وقايعي كه ممكن است براي يك روال رخ دهد مي‌باشد. كامپايل، فراخواني، شروع اجرا، وضعيت در حال اجرا، پايان اجرا، همگي از جمله رخدادهاي قابل وقوع در اين دسته مي‌باشند.

7 - TSQL
اين نوع رخدادها شامل كليه وقايعي است كه باعث اجراي هر يك از دستورات زبان TSQL به صورت تكي يا دسته‌اي (Batch) مي‌شود. دستورات SELECT ، INSERT ، UPDATE ، DELETE و ... هر كدام آغاز و پاياني مشخص با نتايج معين در يك بانك اطلاعاتي دارند كه مي‌توانند به وسيله اين نوع رخداد مورد بررسي قرار گيرند.

8 - Transaction
در اين دسته، كليه وقايع مربوط به فرآيند(Transaction) از جمله شروع (BEGIN) تأييد (Commit) و بازگشت (Roll Back) قرار مي‌گيرند. هر فرآيند شامل چند دستور SQL مي‌باشد كه يا بايد همگي بدون اشكال اجرا شوند و يا اينكه هيچكدام اجرا نگردند.
اهميت فرآيند و استفاده مناسب از آن‌ها در يك بانك اطلاعاتي و برنامه كاربردي مربوط به آن جاي هيچ‌گونه ترديدي را براي وجود ابزاري جهت ثبت و مانيتورينگ وقايع باقي نمي‌گذارد. لذا اين دسته از رخدادها همانند رخدادهاي SQL يكي از پركاربردترين رخدادها قلمداد مي‌شوند.

9 - Session
اين دسته از وقايع شامل كليه رخدادهاي مربوط به اتصال كاربران به بانك اطلاعاتي (Login) و خروج از آن (Logout) يا قطع اتصال در اثر بروز هر عاملي (Disconnect) مي‌باشد و براي كنترل و رفع ايراد ورود و خروج كاربران به سيستم مورد استفاده قرار مي‌گيرد.


براي اجراي Profile از برنامه Management Studio و از منوی Tools گزینه SQL Server Profiler را انتخاب نمایید.
پس از اجراي اين برنامه بايد مواردي را كه مي خواهيد مورد بررسي قرار دهيد را مشخص نماييد و در مورد اين Trace يك Profile توليد نماييد. قبل از توليد اين Profile بايستي تمامي برنامه ها ابتدا به SQL Server متصل شوند و در واقع Server ايي را كه مي خواهيد مورد بررسي قرار دهيد را مشخص نماييد.

ضمیمه 85643

چنانچه بخواهيد از الگوهاي قديمي استفاده نماييد بصورت زير عمل كنيد

ضمیمه 85644

در Server هاي 2000 و 2005 و 2005Analysis Service مي توان الگوهاي زير را مشاهده كرد :
SP_Count : بررسي اجراي SP هايي كه به كرات اجرا مي شوند.
Standard : يك Trace معمولي و با جزييات استاندارد.
TSQL : بررسي TSQL هايي كه از جانب Client هاي ارسال مي شوند.
TSQL-Duration : بررسي زمان صرف شده براي اجراي يك TSQL
و برخي ديگر. در هر صورت براي اجراي يك رويه صحيح Tuning بايد با استفاده از Event Class هاي موجود يك Trace انجام داد.
در ايجاد يك Trace جديد مي توان نتيجه Trace را در يك File ذخیره کرد. براي ذخيره در فايل حتما حداكثر فضاي فايل را برابر با 640 قرار دهيد .
يا در يك جدول در دیتابیس مجزا ذخيره كرد.

در قسمت Event Class

ضمیمه 85645

براي بررسي يك Connection بايستي Filter اعمال كنيد در غير اين صورت تمامي درخواستهاي ارسالي به Server مورد بررسي قرار مي كيرد.
براي اعمال Filter بايستي :

ضمیمه 85646

پس از اجراي Profiler برنامه زير اجرا مي شود.

ضمیمه 85647

در ستونهاي Profiler موارد مهمي وجود دارد كه كاربر بايد آنها را بشناسد. از اين موارد مي توان به عناوين زير اشاره كرد :

• Application Name : نام Client ايي است كه به SQL Server متصل شده است
• CPU : ميزان زماني برحسب ميلي ثانيه كه اجراي Query بطول انجاميده است
• Read : تعداد دفعاتي كه عمليات خواند اطلاعات از روي ديسكها انجام ميشود را نشان مي دهد .
• Write : تعداد دفعاتي كه عمليات نوشتن اطلاعات از روي ديسكها انجام ميشود را نشان مي دهد .
• Duration : زمان مصرف شده براي پاسخدهي به درخواست ها مي باشد ( برحست ميلي ثانيه )
• SPID : شماره ايي بيشتراز 50 براي كاربران متصل به SQL Server ( يكي از بهترين اطلاعات براي Filter كردن )

در اين ميان تعدادي از Event Class هاي مهم را مورد بررسي قرار مي دهيم :
Database

1. Data File Auto Grow : ميزان رشد اتوماتيك Data File
2. Log File Auto Grow : ميزان رشد اتوماتيك Log File

Lock

1. Deadlock Graph : نمايي از Lock و Deadlock
2. Deadlock : كاربري كه deadlock گرفته است
3. Deadlock Chain : زنجيرهايي از كاربران شركت كننده Deadlock
4. Timeout : ميزان تحمل شيي Lock شده

Stored Procedure

1. Starting : زمان شروع Stored procedure
2. Stmt Starting : زمان شروع دستورات درون Stored procedure
3. Stmt Complete : زمان اجراي دستورات درون Stored procedure
4. Complete : زمان تكميل اجراي Stored Procedure

[cnmeysam]

با سلام سه سوال داشتم من یه دیتابیس دارم که تقریبا 600 تا ÷ایگاه داده داره sql server 2000 هم هست واسه نرم افزار هولو میخواستم بدونم راهی هست که بشه توش بعد از ویندوز زدن دیتا ها رو دونه دونه و یکی یکی ریستور یا اتچ نکر و همه رو یکجا بک آپ گرفت و یک جا ریستور کرد؟
دفعه قبل ویندوز زدم از صبح ساعت 8 تا 1 شب طول کشید یکی یکی اتچ کردنشون اگه میشه راهنمایی کنید
ایمیلم : life.h4rd@yahoo.com

[abbasTaghizadeh]

دوست عزیز حالا که شما اینهمه اطلاعات در رابطه با sql serverدارین میشه یه کمکی هم به ما بکنی
من یه برنامه سنگین دارم که پایگاه داده روی سرور قرار دارد و برنامه روی کامپیوتر های کلاینت نصب شده است وقتی این کاربران در طول روز با برنامه کار می کنن حجم رم در سرور هی بالا میره تا جایی که کل رم پر شده و سرور ریست میشه اگر امکانش هست منو راهنمایی بفرمایید

[javaan]

خیلی ممنون از راهنمایی خوبتان دوست عزیز...با سپااس فراوان

خرید پوند - قیمت پوند