خانه SQL Server هشت اشتباه بزرگ در SQL Server! SQL Server SQL Server Backup نوشته شده توسط: حمید فرد تاریخ انتشار: ۳۱ مرداد ۱۳۹۴ آخرین بروزرسانی: ۲۲ مهر ۱۴۰۲ زمان مطالعه: 7 دقیقه ۱ (۱) مقدمه نصب و راه اندازی SQL Server به واسطه وجود Wizard Installation توسط مایکروسافت در این سالها بسیار آسان شده است. به صورت پایه ای چند عملیات باید قبل و بعد از نصب SQL Server بر روی سیستم سخت افزاری و تنظیمات SQL Server توسط مدیران پایگاه داده انجام شود تا از نبود اشکالات پایه ای اطمینان حاصل نمایند. بر اساس تجربه کاری بنده برخی از مشکلات اساسی SQL Server از انجام ندادن این عملیات قبل و بعد از نصب و راه اندازی است. هیچ وقت این اشتباهات را انجام ندهید. اشتباهات جدید دیگری هستند…. اشتباه ۱: نصب و راه اندازی SQL Server بدون انجام تست های سخت افزاری و نرم افزاری بر روی دیسک سخت. اصولا مدیران پایگاه داده باید سیستم سخت افزاری اعم از دیسک سخت و قدرت پردازنده را برای استفاده در محیط SQL Server ارزیابی کنند و این ارزیابی باید دقیقأ به صورت باشد که SQL Server از این دو منابع استفاده می کند. اشتباه ۲: استفاده از تنظیمات پیشفرض در SQL Server. مدیران پایگاه داده در همه حال از تنظیمات پیشفرض در SQL Server استفاده میکنند. همیشه به یاد داشته باشید که تنظیمات در هر محیط سخت افزاری و طرز استفاده از SQL Server متفاوت است. اشتباه ۳: استفاده از Primary Filegroup در پایگاه های داده مدیران پایگاه داده اصولا باید تمامی داده های کاربران را از داده های سیستمی جدا کنند. این عمل چند فواید به همراه دارد. اشتباه ۴: قرار دادن فایل داده و تراکنش در یک درایو. قرار دادن این دو فایل بر کاهش سرعت تراکنش و استفاده از منابع سیستم تأثیر بسیاری دارد. اشتباه ۵: کم حجم کردن فایل تراکنش. این بدترین عملی است که یک مدیر پایگاه داده می تواند انجام دهد به این صورت که اول Recovery Model را به Simple تغییر داده و بعد فایل تراکنش را کاهش داده و بعد Recovery Model را به Full تغییر داده و در آخر بدون گرفتن Backup پایگاه داده را به امید خدا رها کند. این عمل زنجیره تراکنش را در پایگاه داده از بین میبرد و باعث می شود که در هنگام اختلال و خرابی دیگر نتوانیم داده ها را تا زمان قبل از خرابی بازیابی کنیم. اشتباه ۶: فعال سازی Auto_Close در پایگاه داده این تنظیمات استفاده از دیسک سخت را افزایش داده و سرعت کلی سیستم را پایین می آورد. اشتباه ۷: استفاده و فعال سازی Auto_Growth در پایگاه داده. این تنظیمات باعث میشود که فایل تراکنش به صورت اصولی و مرتب ساخته نشود. اشتباه ۸: گرفتن فایل پشتیبان پایگاه داده بدون تست کردن. گرفتن فایل پشتیبان بر اساس RTO و RPO به تصویب رسیده از طرف مدیریت سازمان بسیار کار پسندیده و عالی است اما در خیلی از موارد مدیران پایگاه داده فایل پشتیبان را تست نکرده و در هنگام بازیابی بعد از اختلال یا خرابی به خطا هایی همچون «فایل پشتیبان خراب است» برخورد می کنند که دیگر برای دانستن این موضوع خیلی دیر است. چه رتبه ای می‌دهید؟ میانگین ۱ / ۵. از مجموع ۱ اولین نفر باش معرفی نویسنده مقالات 6 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده حمید فرد معرفی محصول مسعود طاهری دوره آموزشی نگهداری از بانک‌های اطلاعاتی در SQL Server 1.180.000 تومان مقالات مرتبط ۰۲ آبان SQL Server ابزار Database Engine Tuning Advisor؛ مزایا، کاربردها و روش استفاده تیم فنی نیک آموز ۱۵ مهر SQL Server معرفی Performance Monitor ابزار مانیتورینگ SQL Server تیم فنی نیک آموز ۱۱ مهر SQL Server راهنمای جامع مانیتورینگ بکاپ ها در SQL Server تیم فنی نیک آموز ۰۸ مهر SQL Server Resource Governor چیست؟ آشنایی با نحوه پیکربندی و اهمیت های آن تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ رضا ۲۳ / ۰۷ / ۹۷ - ۱۲:۰۲ سلام جناب آقای طاهری بنده یک سیستم حسابداری دارم و میخوام رو حالت cloud اجرا کنم و تمام دیتا های تمام شرکتها در یک دیتابیس ثبت شوند ولی مشکل تو backup و restore کردن هر شرکت به صورت مجزاست آیا این امکان وجود داده که داده های هر شرکت رو جداگانه backup گرفت پاسخ به دیدگاه مسعود طاهری ۲۳ / ۰۷ / ۹۷ - ۰۹:۵۲ سلام راحت ترین راه ایجاد دیتابیس جدید – استفاده از دستور Select into برای درج جدول و دیتاهای مورد نیاز در اون دیتابیس جدید است بعدش تهیه بکاپ از اون دیتابیس موفق باشید پاسخ به دیدگاه رضا ۲۳ / ۰۷ / ۹۷ - ۱۲:۰۲ سلام جناب آقای طاهری بنده یک سیستم حسابداری دارم و میخوام رو حالت cloud اجرا کنم و تمام دیتا های تمام شرکتها در یک دیتابیس ثبت شوند ولی مشکل تو backup و restore کردن هر شرکت به صورت مجزاست آیا این امکان وجود داده که داده های هر شرکت رو جداگانه backup گرفت پاسخ به دیدگاه مسعود طاهری ۲۳ / ۰۷ / ۹۷ - ۰۹:۵۲ سلام راحت ترین راه ایجاد دیتابیس جدید – استفاده از دستور Select into برای درج جدول و دیتاهای مورد نیاز در اون دیتابیس جدید است بعدش تهیه بکاپ از اون دیتابیس موفق باشید پاسخ به دیدگاه جواد ۱۹ / ۰۴ / ۹۷ - ۰۴:۲۴ سلام وقت بخیر یک سوالی داشتم میخواستم بپرسم برای شیرینک کردن خودکار لاگ فایل باید چیکار کنم ؟ فول بکاپ هم به صورت خودکار گرفته می شود البته بدون اینکه به دیتابیس آسیبی برسد پاسخ به دیدگاه مسعود طاهری ۱۹ / ۰۴ / ۹۷ - ۰۸:۱۴ سلام دوست عزیز ۱- اگر می خواهید که Log Backup داشته باشید می توانید لاگ بکاپ بگیرید (با جاب) در این حالت لاگ فایل شما عموما حجم ش خیلی زیاد رشد نخواهد کرد ۲- اگر لزومی به داشتن لاگ بکاپ ندارید می توانید Recovery Model بانک را در حالت Simple قرار دهید پیشنهاد من به شما استفاده از حالت اول است در این حالت می توانید مزایای Log Backup را بدست آورید برای بدست آوردن اطلاعات بیشتر به این لینک مراجعه کنید https://nikamooz.com/product/database-maintenance-sql-server/ پاسخ به دیدگاه جواد ۱۹ / ۰۴ / ۹۷ - ۰۴:۲۴ سلام وقت بخیر یک سوالی داشتم میخواستم بپرسم برای شیرینک کردن خودکار لاگ فایل باید چیکار کنم ؟ فول بکاپ هم به صورت خودکار گرفته می شود البته بدون اینکه به دیتابیس آسیبی برسد پاسخ به دیدگاه مسعود طاهری ۱۹ / ۰۴ / ۹۷ - ۰۸:۱۴ سلام دوست عزیز ۱- اگر می خواهید که Log Backup داشته باشید می توانید لاگ بکاپ بگیرید (با جاب) در این حالت لاگ فایل شما عموما حجم ش خیلی زیاد رشد نخواهد کرد ۲- اگر لزومی به داشتن لاگ بکاپ ندارید می توانید Recovery Model بانک را در حالت Simple قرار دهید پیشنهاد من به شما استفاده از حالت اول است در این حالت می توانید مزایای Log Backup را بدست آورید برای بدست آوردن اطلاعات بیشتر به این لینک مراجعه کنید https://nikamooz.com/product/database-maintenance-sql-server/ پاسخ به دیدگاه محمد استقامت ۱۷ / ۱۲ / ۹۴ - ۰۵:۱۱ در مورد اشتباه چهارم : قرار دادن فایل های تراکنش و داده روی یک درایو به احتمال زیاد منظور درایوهایی است که روی دیسک های جداگانه باشند. حال در اکثر سازمان های بزرگ دیگر سرورها بصورت فیزیکی و جداگانه نیستند بلکه سرورهای مجازی هستند که اکثر آنها هم از SAN استفاده می کنند. حال سوال من این است : روی درایو هایی که همه از یک SAN هستند و می دانیم که پشت همه این درایو ها یک سخت افزار واقعی است ، ایا هنوز این جداسازی می تواند مزیتی محسوب شود یا نه ؟ چون به نظرم دلیل یکی از دلایل اصلی این جداسازی دسترسی موازی با سرعت بالاست یعنی دو دیسک با هم و بصورت موازی روی داده و تراکنش کار کنند. که در حالت SAN واقعا نمی دانم چه مزیتی می تواند داشته باشد؟ پاسخ به دیدگاه مسعود طاهری ۱۷ / ۱۲ / ۹۴ - ۰۷:۵۹ سلام هنگام استفاده از Storage باید نکات زیر را در نظر داشته باشید ۱- معمولا در اکثر سازمان ها enclosureهای SAN با دیسک های فیزیکی پر شده و برای بدست آوردن ظرفیت بالا یک RAID مثل ۵ استفاده می کنند و روی آن برای همه چیزی DB، VM و… LUN تعریف می کنند که در نهایت تمامی IO فیزیکی مربوط به LUN روی دیسک ها پخش شده که این مورد در برخی مواقع متاسفانه کارایی لازم به ازای DB سرور ها را نخواهد داشت ۲- یکی از بهترین کارهای ممکن ایجاد RAID جداگانه به ازای Data File و Log File می باشد. در این حالت شما حداقل دو RAID جداگانه برای دیتا فایل و لاگ فایل تعریف می کند و روی آن LUN خود را ایجاد می کنید. با توجه به این که دیسک های فیزیکی هر کدام از آنها جداگانه می باشد می تواند کارایی لازم را برای شما به ارمغان آورد به دلیل این که مکانیزم دسترسی مربوط به دیتا فایل Random Access و مکانیزیم دسترسی Log File به صورت Sequential Access می باشد. در ضمن باید به این نکته هم توجه داشته باشید که قرار دادن فایل هایی که دسترسی Random Access در LUN مربوط به Log File باعث می شود که رفتار دیسک های مربوط به صورت Random Access شود و… (در خصوص مورد آخر یکی از دوستانم خوب R&D کرده و حتی با یک نرم افزار عملکرد مربوط به دیسک را زیر نظر گرفته بود که جالب بود برام) در پاسخ به سوال شما باید بگم اگر بتوانید مورد ۲ را رعایت کنید نور علی نور است در غیر این صورت ایجاد LUN روی یک RAID زیاد کارایی لازم را ارئه نخواهد داد. چون در آخر همه آنها روی تعدادی دیسک فیزیکی که تحت اختیار یک RAID خاص است ذخیره می شوند. توجه داشته باشید که تعداد دیسک های موجود در RAID + نوع دیسک های + نوع RAID در این خصوص بی تاثیر نمی باشد. پاسخ به دیدگاه محمد استقامت ۱۷ / ۱۲ / ۹۴ - ۰۸:۳۹ بسیار سپاس گذارم. توضیح کاملی بود. پاسخ به دیدگاه محمد استقامت ۱۷ / ۱۲ / ۹۴ - ۰۹:۳۰ سلام بر اساتید عزیز سپاس از پاسخ شما. این به این معنی است که همه نسخ sql server 2014 enterprise موجود در ایران از همین نسخه با لایسنس های محدود هستند. (ساپورت حداکثر ۲۰ Core CPU) یا اینکه نسخه دیگری وجود دارد که بتوانم از آن استفاده کنم ؟ و یا اینکه با تغییری روی همین نسخه بتوان Licence آنرا ارتقاء دارد؟ قطعا تعویض sql server روی سیستم های عملیاتی بسیار هزینه بر است. پاسخ به دیدگاه مسعود طاهری ۱۷ / ۱۲ / ۹۴ - ۰۹:۵۹ تهیه نسخه enterprise core license مستلزم پرداخت هزینه و… می باشد. (از نمایندگی های مایکروسافت در خارج از ایران تهیه کنید) در ضمن پس از تهیه لاینس باید مجدد SQL Server را Change کنید. http://blog.sqlauthority.com/2015/05/29/sql-server-using-20-logical-processors-based-on-sql-server-licensing/ https://www.brentozar.com/archive/2014/08/sql-server-edition-change-standard-edition-enterprise-evaluation/ https://www.brentozar.com/archive/2014/04/sql-server-2014-licensing-changes/ پاسخ به دیدگاه Hamid J. Fard ۱۷ / ۱۲ / ۹۴ - ۱۰:۰۸ البته نیازی به تغییر SQL Server نیست به این دلیل که شما فقط نیاز دارید کد لایسنس را به روز رسانی کنید و اینکه شما نمی توانید از نماینده گی های خارج از کشور خریداری کنید مگر اینکه از شرکت شما یک نماینده گی در آن کشور باشد در غیر اینصورت غیر قانونی بوده. در ضمن در نظر داشته باشید که فقط ورژن Upgrade می شود نه Edition. پاسخ به دیدگاه مسعود طاهری ۱۷ / ۱۲ / ۹۴ - ۱۰:۴۳ بلی تغییر را اشتباه نوشتم. باید گزبنه Upgrade را در Setup انتخاب کنیم راستی حمید در ایران روش های ناسناخته ای برای دور زدن تحریم ها وجود دارد از اینکارهای غیر قانونی خیلی از سازمان ها انجام می دهند و حتی در داخل ایران هم از خدمات مایکروسافت استفاده می کنند. چند تا سازمان دولتی که باهشون درگیر شدم دقیقا همین کار را انجام می دهند. بدون داشتن شرکت در کشور دیگر فقط از طریق واسطه و …. پاسخ به دیدگاه Hamid J. Fard ۱۷ / ۱۲ / ۹۴ - ۱۰:۰۸ درسته ولی لایسنس به نام آن شرکت واسطه ثبت شده و برای استفاده در ایران نیست و اینکه مایکروسافت می تواند از آن شرکت واسط شکایت کند مسعود طاهری ۱۷ / ۱۲ / ۹۴ - ۱۰:۰۶ اتفاقا من هم از دقیقا این موضوع را به سازمان گوشزد کردم و دقیقا صفحه لایسنس و… را که مشاهده کردم شاخ در آوردم به نام خود سازمان و تا به حال که مشکلی نداشته است. شاید خیلی ها نتوانند این کار را انجام دهند اما واسطه و… (مثل ب.ز و امثال آن) مسعود طاهری ۱۷ / ۱۲ / ۹۴ - ۱۰:۳۹ و جالب تر این است که چندین بار هم پشتیبانی Remote از آن طرف داشته اند و حتی در حد ارائه Hotfix و… وقتی این گونه چیزها را می بیننم به این نتیجه رسیدم آخر الزمان نزدیک است محمد استقامت ۱۶ / ۱۲ / ۹۴ - ۱۰:۲۵ سلام به تنظیمات پیش فرض اشاره کردین. مشکلی برای ما پیش اومده که شاید به نحوی به این تنظیمات مرتبط بشه اگه راهنمایی کنید ممنون می شم. مشخصات محیط ما : windows server 2008 R2 – enterprise sql server 2014 – sp1 Microsoft SQL Server 2014 – 12.0.4100.1 (X64) Apr 20 2015 17:29:27 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) و تنظیمات مربوط به استفاده از همه هسته ها هم فعال است اما از ۳۰ هسته CPU فقط ۲۰ تای آن کار می کند. در گروه های مختلف طرح کردم. و اطلاعات بیشتری از سرور گرفتم به قرار زیر اما هنوز نتیجه عملی نگرفنم. لطفا راهنمایی کنید : SELECT scheduler_id, cpu_id, status FROM sys.dm_os_schedulersWHERE status LIKE 'VISIBLE%'; نتیجه اجرای این Query : scheduler_id cpu_id status ۰ ۰ VISIBLE ONLINE ۱ ۱ VISIBLE ONLINE … ۱۹ ۱۹ VISIBLE ONLINE ۲۰ ۲۰ VISIBLE OFFLINE ۲۱ ۲۱ VISIBLE OFFLINE … ۲۹ ۲۹ VISIBLE OFFLINE ۱۰۴۸۵۷۶ ۰ VISIBLE ONLINE (DAC) و اولین لاگ بعد از استارت sql server هم این است : SQL Server detected 5 sockets with 6 cores per socket and 6 logical processors per socket, 30 total logical processors; using 20 logical processors based on SQL Server licensing. This is an informational message; no user action is required. درسته به Licence مربوطه و اگر هست چه راهی با توجه به شرایط نرم افزارها در ایران پیش روم هست ؟ ممنون پاسخ به دیدگاه مسعود طاهری ۱۷ / ۱۲ / ۹۴ - ۰۷:۴۹ سلام بر حمید عزیز و آقای استقامت بلی این موضوع صحیح است. بحث لایسنس است. در قسمت SQL Server Logs هم زمانی که سرویس SQL استارت می شود. در خصوص تعداد Coreهای قابل استفاده به ازای لایسنس شما لاگی ثبت می کند …. پاسخ به دیدگاه Hamid J. Fard ۱۷ / ۱۲ / ۹۴ - ۰۴:۱۴ بله همانطور که مشهود است مشکل از قسمت لایسنس است. در حال حاضر ۱۰ عدد از Scheduler ها به صورت Offline هستند یعنی SQL Server هیچ گونه Thread را بر روی آن پردازنده ها اجراء نمی کند. پاسخ به دیدگاه سیامک محمدی ۲۳ / ۰۷ / ۹۴ - ۰۷:۲۵ موارد زیر هم میتونن جزو Bad practice ها باشن: – ایندکس گذاری‌های تکراری و یا بیش از حد – ایجاد جداول Heap – استفاده از تکنیک‌های کوئری نویسی در جاهایی که میتوان ساده تر به جواب رسید. مثل استفاده زیاد از Curser, Temp Tableها، ویوهای تودرتو و .. – نصب ننمودن پچ‌ها و سرویس پک‌ها – رعایت ننمودن مسائل امنیتی و ناخود آگاه فراهم کردن فضا برای SQL Injection پاسخ به دیدگاه حجازی ۰۷ / ۰۶ / ۹۴ - ۰۱:۵۱ ببخشید اینقدر سوال می کنم بهر حال یکسری عملیات هست که باعث افزایش حجم فایل تراکنش میشه که بعدش مجبور به این کار میشیم. اینو بایستی چیکار کرد؟ پاسخ به دیدگاه مسعود طاهری ۰۹ / ۰۶ / ۹۴ - ۰۷:۰۹ حمید جان+ خانم حجازی سلام حمید عزیز جواب به جا و مناسبی دادید در تکمیل بحث در برخی مواقع اتفاق می افتاد که کاربران حجم زیادی از رکوردها را می خواهند تغییر (Inser,Update,Delete,Merge) دهند عملیات از خاندان Bulk Operation نمی باشد در این حالت باید از روش هایی استفاده کنیم که حجم لاگ رابیش از اندازه افزایش ندهد. مثلا قرار است ۲۰۰ میلیون رکورد را حذف کنیم. اگر با یک دستور Delete اینکار را انجام دهید حجم لاگ به شدت افزایش پیدا می کند مثال زیر روش خوبی برای انجام اینکار است SET NOCOUNT ON; DECLARE @r INT; SET @r = 1; WHILE @r > 0 BEGIN BEGIN TRANSACTION; DELETE TOP (100000) — this will change dbo.TransactionData WHERE TransType IN (20, 23, 27); SET @r = @@ROWCOUNT; COMMIT TRANSACTION; — CHECKPOINT; — if simple — BACKUP LOG … — if full END پاسخ به دیدگاه مسعود طاهری ۰۹ / ۰۶ / ۹۴ - ۰۹:۳۵ بلی این دو دستور تفاوتی ندارد چون خود دستور Delete به تنهایی Autocommit Transaction است اما در برخی مواقع سناریوهایی دیده ام که داده های زیادی بنا به دلایلی از سیستم پاک می شود یا حتی Update می شود در این گونه مواقع می توان با انجام این نوع کارها تا حدودی از افزایش حجم لاگ جلوگیری کرد. (تهیه لاگ بکاپ یا Chekpoint با توجه به Recovery Model) در تکمیل صحبت های حمید –نظارت بر فضای لاگ فایل DBCC SQLPERF(LOGSPACE) GO –مشاهده وی ال اف ها DBCC LOGINFO GO –مشاهده لاگ رکوردها SELECT * FROM fn_dblog(NULL,NULL) GO پاسخ به دیدگاه Hamid J. Fard ۰۹ / ۰۶ / ۹۴ - ۰۹:۵۵ مسعود عزیز و خانم حجازی: نکته اول: البته این رو بگم که در واقعیت هیچ کسی داده ها حجیم و زیادی را با دستور Delete حذف نمی کنند. نکته دوم:دستور Delete From با Begin Transaction Delete From تفاوت چندانی در فایل تراکنش ندارد به دلیل آنکه در هر دو دستور تمامی اطلاعات رکوردها در فایل تراکنش ذخیره می شود و بعد از دستورات Checkpoint یا backup Log فایل تراکنش دوباره قابل استفاده قرار می گیرد. شما می توانید با دستور fn_dblog تغییرات فایل تراکنش را بعد از هر دو دستور مشاهده کنید. امیدوارم کسی برداشت بد از نظر من نکه. بنده فقط می خواهم که دانسته هایم را با شما در اشتراک بگذارم. پاسخ به دیدگاه Hamid J. Fard ۰۹ / ۰۶ / ۹۴ - ۱۰:۵۲ حجازی: درسته ولی باید فایل تراکنش همانطور که گفتم به مقدار RPO مدیریت بشه و اینکه مثلا شما یک Bulk Operation دارید در این خصوص Recovery Model را به Bulk Logged تغییر داده و بعد از انجام کار به Full و بعد یک فایل پشتیبان از پایگاه داده می گیرید. پاسخ به دیدگاه Hamid J. Fard ۰۴ / ۰۶ / ۹۴ - ۱۱:۰۳ خانم حجازی: در حقیقت Shrink کردن فایل تراکنش یک عمل اشتباه است به دلیل اینکه حجم فایل تراکنش دقیقا بر میگرده به مقدار RPO و اینکه فایل تراکنش درست مدیریت نشده. اصولا فایل تراکنش برای تراکنشهای بسیار بالا حجمی حدود ۲ تا ۸ گیگابایت را داشته باشه نه بیشتر. مگر اینکه…. پاسخ به دیدگاه 1 2 3 4
