nikamooz logo
nikamooz logo
0
سبد خرید
هشت اشتباه بزرگ در SQL Server!

هشت اشتباه بزرگ در SQL Server!

SQL Server
نوشته شده توسط: حمید فرد
۳۱ مرداد ۱۳۹۴
زمان مطالعه: 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 به تصویب رسیده از طرف مدیریت سازمان بسیار کار پسندیده و عالی است اما در خیلی از موارد مدیران پایگاه داده فایل پشتیبان را تست نکرده و در هنگام بازیابی بعد از اختلال یا خرابی به خطا هایی همچون «فایل پشتیبان خراب است» برخورد می کنند که دیگر برای دانستن این موضوع خیلی دیر است.

چه رتبه ای می‌دهید؟

میانگین ۱ / ۵. از مجموع ۱

اولین نفر باش

title sign
برچسب ها
#
#
title sign
معرفی نویسنده
حمید فرد
مقالات
6 مقاله توسط این نویسنده
محصولات
0 دوره توسط این نویسنده
حمید فرد
پروفایل نویسنده
title sign
معرفی محصول
ایمان باقری

دوره آموزشی کوئری نویسی در SQL Server
2.190.000 تومان
title sign
مقالات مرتبط
تاثیر ایندکس های Computed-Column روی دستور DBCC CHECKDB
۰۲ اردیبهشت
SQL Server

تاثیر ایندکس های Computed-Column روی دستور DBCC CHECKDB
تورج عزیزی
دستور SELECT TOP در SQL Server
۲۶ فروردین
SQL Server

دستور SELECT TOP در SQL Server
تیم فنی نیک آموز
راهنمای پیکربندی Authentication و Authorization در SQL Server به بهترین روش
۱۹ فروردین
SQL Server

راهنمای پیکربندی Authentication و Authorization در SQL Server به بهترین روش
تیم فنی نیک آموز
آموزش رمزگذاری اطلاعات در SQL Server
۱۴ فروردین
SQL Server

آموزش رمزگذاری اطلاعات در SQL Server
تیم فنی نیک آموز
title sign
دیدگاه کاربران

    • سلام جناب آقای طاهری
      بنده یک سیستم حسابداری دارم و میخوام رو حالت 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 کنید.

        •    البته نیازی به تغییر SQL Server نیست به این دلیل که شما فقط نیاز دارید کد لایسنس را به روز رسانی کنید و اینکه شما نمی توانید از نماینده گی های خارج از کشور خریداری کنید مگر اینکه از شرکت شما یک نماینده گی در آن کشور باشد در غیر اینصورت غیر قانونی بوده.
          در ضمن در نظر داشته باشید که فقط ورژن Upgrade می شود نه Edition.

          •     بلی تغییر را اشتباه نوشتم. باید گزبنه Upgrade را در Setup  انتخاب کنیم

            راستی حمید در ایران روش های ناسناخته ای برای دور زدن تحریم ها وجود دارد از اینکارهای غیر قانونی خیلی از سازمان ها انجام می دهند و حتی در داخل ایران هم از خدمات مایکروسافت استفاده می کنند. چند تا سازمان دولتی که باهشون درگیر شدم دقیقا همین کار را انجام می دهند. بدون داشتن شرکت در کشور دیگر فقط از طریق واسطه و …. 

            •    درسته ولی لایسنس به نام آن شرکت واسطه ثبت شده و برای استفاده در ایران نیست و اینکه مایکروسافت می تواند از آن شرکت واسط شکایت کند 

            •     اتفاقا من هم از دقیقا این موضوع را به سازمان گوشزد کردم و دقیقا صفحه لایسنس و… را که مشاهده کردم شاخ در آوردم به نام خود سازمان و تا به حال که مشکلی نداشته است. شاید خیلی ها نتوانند این کار را انجام دهند اما واسطه و… (مثل ب.ز و امثال آن)

            •     و جالب تر این است که چندین بار هم پشتیبانی 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_schedulers
      WHERE  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های قابل استفاده به ازای لایسنس شما لاگی ثبت می کند ….

      •    بله همانطور که مشهود است مشکل از قسمت لایسنس است. در حال حاضر ۱۰ عدد از 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

        •  مسعود عزیز و خانم حجازی: 

          نکته اول: البته این رو بگم که در واقعیت هیچ کسی داده ها حجیم و زیادی را با دستور Delete حذف نمی کنند.

          نکته دوم:دستور Delete From با Begin Transaction Delete From تفاوت چندانی در فایل تراکنش ندارد به دلیل آنکه در هر دو دستور تمامی اطلاعات رکوردها در فایل تراکنش ذخیره می شود و بعد از دستورات Checkpoint یا backup Log فایل تراکنش دوباره قابل استفاده قرار می گیرد.

          شما می توانید با دستور fn_dblog تغییرات فایل تراکنش را بعد از هر دو دستور مشاهده کنید.

          امیدوارم کسی برداشت بد از نظر من نکه. بنده فقط می خواهم که دانسته هایم را با شما در اشتراک بگذارم.

      • حجازی: درسته ولی باید فایل تراکنش همانطور که گفتم به مقدار RPO مدیریت بشه و اینکه مثلا شما یک Bulk Operation دارید در این خصوص Recovery Model را به Bulk Logged تغییر داده و بعد از انجام کار به Full و بعد یک فایل پشتیبان از پایگاه داده می گیرید.

    •  خانم حجازی: در حقیقت Shrink کردن فایل تراکنش یک عمل اشتباه است به دلیل اینکه حجم فایل تراکنش دقیقا بر میگرده به مقدار RPO و اینکه فایل تراکنش درست مدیریت نشده. اصولا فایل تراکنش برای تراکنشهای بسیار بالا حجمی حدود ۲ تا ۸ گیگابایت را داشته باشه نه بیشتر. مگر اینکه….

هر روز یک ایمیل، هر روز یک درس
آموزش SQL Server بصورت رایگان
همین حالا فرم زیر را تکمیل کنید
دانلود رایگان جلسه اول
نیک آموز علاوه بر آموزش، پروژه‌های بزرگ در حوزه هوش تجاری و دیتا انجام می‌دهد.
close-link
وبینار رایگان ؛ Power BI کلید رقابت شما در دنیا داده‌ها      چهارشنبه 12 اردیبهشت ساعت 15
ثبت نام رایگان
close-image