‫انتقال دیتابیس bugnet به MS SQL 2008

bugnet (نرم‌افزار issue tracking دات‌نتی) را در جایی نصب کرده بوده و دیتابیسش را در SQL Server 2005 Express قرار داده بودم. به دلایلی تصمیم گرفتم دیتابیس را به نسخه‌ای از MS SQL 2008 منتقل کنم. یک دیتابیس خام با استفاده از installer باگ‌نت درست کردم و سعی کردم دیتا را از SQL 2005 Express به آن export کنم اما به خطاهایی برخورد کردم و موفق نشدم. به همین دلایل از روال دیگری برای انتقال دیتا استفاده کردم:

۱- یک دیتابیس خالی ساختم و دیتای قبلی را با استفاده از امکان Export به آن کپی کردم.
۲- روال Export قدرت تشخیص viewها را نداشت. در صفحه انتخاب جداول باید تیک viewها برداشته شود.
۳- باگ‌نت را به دیتابیس جدید وصل کردم. اولین خطایی که دیده می‌شد خطای Could not find stored procedure ‘BugNet_HostSetting_GetHostSettings’. بود.
۴- یک دیتابیس خام و خالی را با کمک installer باگ‌نت ساختم.
۵- با استفاده از گزینه Tasks – Generate Scripts دیتابیس مرحله قبلی را انتخاب کرده و گزینه‌های Stored Procedures و Views را انتخاب کنید.
۶- اسکریپت حاصله را در دیتابیس مرحله اول که همان دیتابیس اصلی است اجرا کردم. اصلاح use database نباید فراموش شود.
۷- یک بار خطای Item has already been added. Key in dictionary: ‘AdminNotificationUsername’ Key being added: ‘AdminNotificationUsername’ را دیدم. علت آن installer بود. که رکوردهای جدول HostSettings را duplicate کرده بود. رکوردهای تکراری این جدول باید پاک می‌شدند.
۸- اگر مقدار InstallationDate در AppSettings وجود نداشته باشد رکوردهای تکراری مجدداً اضافه می‌شود.
۹- همه مراحل اینجا را می‌شد یک جور دیگر هم انجام داد. ایجاد دیتابیس آماده توسط باگ‌نت، پاک کردن فقط جداول آن، export داده از دیتابیس حاوی داده‌ها.
۱۰- از اینجا به بعد مشکل login کردن وجود دارد. bugnet از ASP.NET Membership استفاده می‌کند. پس با کمی شناخت از مدل ASP.NET به راحتی می‌توان مشکلات login و کاربری را حل کرد.

‫Backup گیری از یک دیتابیس راه دور

فرض کنید به یک دیتابیس MS SQL Server راه دور فقط از طریق Management Studio دسترسی دارید. یعنی سرور هیچ راهی برای انتقال فایل برای ندارد. حالا شما می‌خواهید از دیتابیس مورد نظر backup بگیرید. راه معمول این است که از طریق Management Studio اقدام به Backup گیری کرده و سپس فایل bak مورد نظر را از درایو local سرور مربوطه به محل دلخواه خود کپی کنید. ولی حالا که دسترسی فایلی به سرور ندارید نمی‌توانید از این راه استفاده کرده و باید به فکر راه حل دیگری باشید.

بعضی از Data Centerها یک اینترفیس جداگانه برای کمک به این موضوع دارند. اما اگر این ابزار هم موجود نباشد چه باید کرد؟ بدتر از این تصور کنید دیتابیس مورد نظر از نوع Express بوده و دسترسی‌های شما در حداقل ممکن قرار داشته باشد. در این طور مواقع چند راه به ذهن می‌رسد.

۱- اسکریپت کردن دیتابیس و نگهداری اسکریپت‌ها به عنوان backup. من این راه رای برای یک دیتابیس SQL Express انجام دادم ولی جواب نگرفتم.

۲- استفاده از مکانیزم Export. خوشبختانه این راه را در مورد دیتابیس مورد قبل انجام دادم و جواب داد.

۳- کد نویسی و استفاده از Sql Server Management Object. من از این راه استفاده نکردم ولی خیلی به آن خوشبین نیستم. چون ظاهراً این کدها باید در همان ماشینی اجرا شوند که SQL Server در آن نصب است. خیلی از مواقع Database Server ما با Web Server یکی نیست.

۴- استفاده از راه حل خیلی خلاقانه‌ای که در این مقاله codeproject.com توضیح داده شده. در این راه حل هیچ نیازی نیست که برنامه backup گیری روی database server اجرا شود. می‌توان آن را روی کامپیوتر local غیر سرور خود اجرا کرد. روش این برنامه گرفتن backup روی خود سرور، ایجاد یک جدول دیتابیسی موقتی، insert کردن محتوای فایل backup در جدول موقتی، select معمولی از جدول موقتی و انتقال آن به کامپیوتر local و ذخیره آن به صورت یک backup واقعی! البته من با این که با یکی از قلق‌های این برنامه کنار آمدم ولی نتوانستم از آن استفاده واقعی بکنم. چون احتیاج به دسترسی bulk داشتم. هر چند که این روش برای من کار نکرد ولی خلاقیت آن مرا شگفت زده کرد. مطمئن هستم می‌توان با استفاده از راه حل های مشابهی مشکل دسترسی bulk را هم حل کرد.

مشکل دیتابیس در سرورهای اشتراکی

در سرورهای اشتراکی، از همان‌هایی که در ایران خیلی رایج هستند و قیمتی زیر ۱۰۰ هزار تومان در سال دارند، همیشه محدودیت در دیتابیس وجود دارد. بیشتر سرویس‌ها اجازه بیش از یکی دو تا دیتابیس MS SQL Server را نمی‌دهند، آن یکی دو تا هم گاهاً نسخه‌های قدیمی MS SQL Server هستند. علاوه بر اینها محدودیت‌های شدید حجمی هم برای دیتابیس وجود دارد. با این که MySQL رایگان است ولی مشابه همین محدودیت‌ها در رابطه با MySQL هم وجود دارد.

در ادامه چند راه جایگزین را بررسی می‌کنیم:

۱- استفاده از دیتابیس‌‌های Embed مثل Sqlite
۲- استفاده از فایل Access با کمک ODBC
۳- استفاده از MS SQL Server Express در صورت وجود در Web Server
۴- خرید VPS و نصب دیتابیس MS SQL Server Express به طور قانونی و MS SQL Server غیر Express به طور غیر قانونی
۵- تبدیل کامپیوتر منزل یا شرکت به یک سرور کوچک با اجاره ماهیانه IP Static از ISPها و نصب MS SQL Server مشابه مورد قبل
۶- خرید MS SQL Host از شرکت‌های خارجی
۷- استفاده از MS SQL Hostهای مجانی

همه این راه حل‌ها یک محدودیت مشترک دارند. آن هم ناسازگاری برنامه‌های مختلف با دیتابیس‌های مختلف و محدودیت‌های فنی در تکنولوژی‌های دسترسی به دیتابیس است. مثلاً برنامه BugNET نمی‌تواند با Access کار کند. برنامه ‌BlogEngine.NET هم نمی‌تواند با Sqlite کار کند. در مورد محدودیت‌های فنی می‌توان به عدم امکان استفاده از دیتابیس‌های غیر MS SQL Server با Entity Framework اشاره کرد. در مورد استفاده از VPS یا IP Static هم باید دقت کرد که این سرویس‌ها باید uptime واقعاً بالا داشته و از پهنای باندی خوبی استفاده کنند. در مورد نسخه‌های غیر قانونی هم نمی‌توان خیلی مطمئن بود. چون ممکن است سرور مورد نظر به خاطر عدم رعایت Copyright بسته شود. در مورد خرید Host از شرکت‌های خارجی می‌توان خوشحال بود که قیمت تمام شده آنها خیلی پایین‌تر از قیمت شرکت‌های داخلی است. از Hostهای مجانی هم می‌شود استفاده کرد ولی هم مشکل عدم اطمینان وجود دارد و هم مشکل کمبود امکانات فنی مثل نبود امکان ‌Backup/Restore.

‫نگهداری داده‌های حجیم در MS SQL

در خانواده MS SQL نسخه‌های ۲۰۰۰، ۲۰۰۵ و ۲۰۰۸ تعدادی فیلد خاص برای کار با داده‌های حجیمی مثل عکس یا متون خیلی طولانی وجود دارد. هر کدام از اینها شرایط خاص خودشان را دارند که در ادامه بررسی خواهند شد.

char و varchar: حداکثر ۸۰۰۰ کاراکتر

varchar(max)‎: حداکثر ۲ میلیارد کاراکتر – این نوع داده در SQL 2000 پشتیبانی نمی‌شود.

nchar و nvarchar: حداکثر ۴۰۰۰ کاراکتر (به طور کلی ظرفیت انواع یونیکد نصف انواع غیر یونیکد است)

nvarchar(max)‎: حداکثر ۱ میلیارد کاراکتر – این نوع داده در SQL 2000 پشتیبانی نمی‌شود.

binary و varbinary: برای داده‌های غیر متنی و حداکثر ۸۰۰۰ بایت

varbinray(max)‎: برای داده غیر متنی و حداکثر ۲ گیگا بایت.

text: حداکثر ۲ میلیارد کاراکتر – این نوع داده قرار است به زودی حذف شود.

ntext: حداکثر ۱ میلیارد کاراکتر – این نوع داده قرار است به زودی حذف شود.

image: حداکثر ۲ گیگا بایت – این نوع داده قرار است به زودی حذف شود.

FileStream: نوعی ارتباط بین MS SQL و سیستم فایل ویندوز یعنی NTFS – این نوع داده در SQL 2000 پشتیبانی نمی‌شود.

Xml: نگهداری اطلاعات به صورت Xml – این نوع داده در SQL 2000 پشتیبانی نمی‌شود.

توصیه خود مایکروسافت برای نگهداری داده‌های حجیم استفاده از سه نوع varchar(max)‎ و nvarchar(max)‎ و varbinary(max)‎ است. زیرا انواع text و ntext و image دیگر پشیبانی نشده و در نسخ آتی MS SQL برداشته خواهند شد. جهت کسب اطلاعات بیشتر به اینجا و اینجا مراجعه کنید.

‫تاثیر Index گذاری بر سرعت جداول MS SQL

si2 چند روزی را در حال بررسی سرعت Query بر روی جداول حجیم بودم. Query مورد نظر من یک sum ساده بر روی جدولی به اسم amort بود. این Query به صورت یک function پیاده سازی شده بود. البته آن Query نهایی که من زمان آن را بررسی کرده و نتایجش را در نظر داشتم این function را همراه با یک select ساده صدا می زد. خود این select آخر روی یک جدول با ۷۰۰۰ رکورد اجرا می‌شد. در نتیجه اگر فرض کنیم جدول amort دارای ۳۰۰ رکورد است پس ما عملا با انجام عملیات روی ۳۰۰ ضرب در ۷۰۰۰ یعنی ۲۱۰۰۰۰۰ رکورد طرف هستیم. تنها عاملی که آن را در آزمایشات مختلف تغییر می‌دادم تعداد رکوردهای جدول amort (که function مورد نظر من روی آن اجرا می‌شد) و ایندکس داشتن و نداشتن همان جدول amort بود. تمام آزمایشات بر روی یک کامپیوتر Pentium 4, 2.26 Ghz, 1GB RAM, Win Xp SP2 و Microsoft Sql Server 2008 انجام شده است.

نتایج بدون ایندکس واقعا فاجعه بار بودند. برای ۷۲۰۰ رکورد ۲۱۳ ثانیه طول کشید! این در حالی بود که هر سال همین تعداد رکورد به جدول مورد نظر من یعنی amort اضافه می‌شد. به عبارتی دیگر اجرای Query مورد نظر برای سال هشتم حدود نیم ساعت کشنده طول می‌کشد! با این که روی همه جداول دیتابیس بر روی ستون id ایندکس وجود داشت به علت کندی بیش از حد مجبور شدم باز هم به ایندکس‌ها، انواع آن و تاثیرشان بر کارایی فکر کنم. در MS SQL چهار نوع ایندکس وجود دارد که من فقط دو تای پر استفاده‌تر آن را مطالعه و بررسی کردم: Clustered Index و Non-Clustered Index. هر دوی آنها اطلاعات را به صورت sort نگهداری می‌کنند. بر اساس توضیحات اینجا، نوع Clustered اطلاعات را به صورت فیزیکی به حالت sort نگه می‌دارد و در نتیجه در هر جدولی فقط یک ایندکس از این نوع مجاز است در حالی که نوع Non-Clustered از یک ساختار اضافه برای نگهداری اطلاعات sort جدول استفاده می‌کند و می‌توان حدود اقلا ۲۵۰ ایندکس از این نوع در یک جدول داشت. در بعضی منابع خوانده بودم که performance ایجاد شده در هر دو نوع تقریبا یکسان است در حالی که در آزمایشات خودم فهمیدم سرعت Clustered حدود ۱۰ برابر Non-Clustered است. دقت کنید تعریف یک یا چند ستون به عنوان Primary Key باعث می‌شود آن ستون یا ستون‌ها خود به خود به یک Clustered Index تعریف شوند. در همه انواع ایندکس‌ها دو مفهوم ReOrganize و ReBuild وجود دارد که مفهوم آنها را خیلی نفهمیدم ولی متوجه شدم که برای دستیابی به حداکثر کارایی هر از چندگاهی باید این دو عملیات را بر روی جدول مورد نظر اجرا کرد.

نتایج آزمایشم را با هر دو نوع ایندکس تکرار کردم. پرس و جو آن قدر سریع اجرا می‌شد که خیلی سریع تعداد رکوردها را از ۷۲۰۰ به ۷۲۰ هزار، یک و نیم میلیون رکورد و نهایتا ۳ میلیون رکورد رساندم. نتایج کار اگر من مرتکب اشتباهی نشده باشم واقعا شگفت آور بود: زمان مورد نیاز برای اجرا در حالت استفاده از Clustered Index کمتر از یک ثانیه و در حالت استفاده از None Clustered Index حدود ۱۰ ثانیه بود! دقت کنید که ۳ میلیون رکورد در جدول amort و ۷ هزار رکورد در select استفاده کننده function من یعنی ۲۱ میلیارد حالت!!

 

پ. ن.: برای تولید ۳ میلیون رکورد از مقادیر اتفاقی int و تاریخ در یک حلقه while استفاده کردم. نحوه تولید «تاریخ» به صورت random در اینجا و اینجا بحث شده است.