مانیتور کردن SQL Server و ساخت برنامه ی Real Time

مانیتور کردن SQL Server و ساخت برنامه ی Real Time

بسم الله الرحمن الرحیم



در این آموزش قصد دارم شما را با یکی دیگر از ویژگی های SQL Server آشنا کنم. متاسفانه بسیاری از افراد فکر می کنند SQL Server فقط یک ابزار برای نگهداری اطلاعات است! و از بسیاری از سرویس های SQL Server بی خبر هستند.

آموزش مانیتورینگ SQL Server و ثبت تغییرات ایجاد شده

SqlDependency

کلاس SqlDependency و Query notifications ابزارهایی هستند که به شما اجازه می دهند SQL Server را مانیتور کنید این ابزار ها در SQL Server 2005 ارائه شدند.

Query Notifications

Query Notifications به برنامه شما اجازه می دهد در صورتی که داده ای در دیتابیس تغییر داده شد، از این تغییر اطلاع پیدا کند.
هدف این کلاس ذخیره کردن اطلاعاتی جدیدی است که مداوم در دیتابیس ایجاد می شود.


اگر شما بخواهید این کار را بدون استفاده از این سرویس انجام دهید باید یک تایمر داشته باشید که در بازه های زمانی معین اطلاعات را بررسی و در صورتی که تغییری ایجاد شد این اطلاعات را برای شما ارسال کند که تعیین این تغییر خود امری دشوار خواهد بود، اگر بخواهیم ساده بگوییم شما کاری را که SQL Server قبلا انجام داده و آن را بهینه کرده است می خواهید انجام دهید!

قبلا در مباحث دیگرSQL Server Broker را توضیح داده ام، در اینجا نیز برای استفاده از این سرویس باید از Service Broker و QUEUE استفاده کنید.

بسم الله الرحمن الرحیم



در این آموزش قصد دارم شما را با یکی دیگر از ویژگی های SQL Server آشنا کنم. متاسفانه بسیاری از افراد فکر می کنند SQL Server فقط یک ابزار برای نگهداری اطلاعات است! و از بسیاری از سرویس های SQL Server بی خبر هستند.

آموزش مانیتورینگ SQL Server و ثبت تغییرات ایجاد شده

SqlDependency

کلاس SqlDependency و Query notifications ابزارهایی هستند که به شما اجازه می دهند SQL Server را مانیتور کنید این ابزار ها در SQL Server 2005 ارائه شدند.

Query Notifications

Query Notifications به برنامه شما اجازه می دهد در صورتی که داده ای در دیتابیس تغییر داده شد، از این تغییر اطلاع پیدا کند.
هدف این کلاس ذخیره کردن اطلاعاتی جدیدی است که مداوم در دیتابیس ایجاد می شود.


اگر شما بخواهید این کار را بدون استفاده از این سرویس انجام دهید باید یک تایمر داشته باشید که در بازه های زمانی معین اطلاعات را بررسی و در صورتی که تغییری ایجاد شد این اطلاعات را برای شما ارسال کند که تعیین این تغییر خود امری دشوار خواهد بود، اگر بخواهیم ساده بگوییم شما کاری را که SQL Server قبلا انجام داده و آن را بهینه کرده است می خواهید انجام دهید!

قبلا در مباحث دیگرSQL Server Broker را توضیح داده ام، در اینجا نیز برای استفاده از این سرویس باید از Service Broker و QUEUE استفاده کنید.

مثل همیشه آموزش را با مثال ادامه می دهیم. مثال بسیار ساده است، ما یک دیتابیس با جدولی به نام Users که شامل دو فیلد FirstName و LastNeme است ایجاد می کنیم. در این مثال ما دو فرم داریم یکی فرم نمایش اطلاعات و دیگری درج اطلاعات که پس از درج اطلاعات، فرم نمایش سریعا آن را نمایش خواهد داد.

SQL Server تمامی سرویس های خود را به صورت پیش فرض متوقف کرده است و شما برای استفاده از هر سرویس باید ابتدا آن را اجرا کنید و یا دسترسی های آن را نیز فعال کنید.

برای استفاده از سرویس های این مثال باید ابتدا یک Queue ایجاد و سپس Broker اسکیوال را برای دیتابیس خود فعال و در نهایت دسترسی به کاربر SQL Server خود بدهیم تا بتواند از این سرویس استفاده کند؛ کاربر شما همان کاربری است که در SqlConnection با نام userId آن را معرفی می کنید.

مثال:

<add name="Context" providerName="System.Data.SqlClient" connectionString="Data Source=DadehNegar.Blog.ir\SQL-ENG-Full2012;Initial Catalog=DadehNegar.Blog.ir; user ID=sqlUser; password=********;" />

حال این دستورات را در SQL Server اجرا کنید

USE YourDatabaseName;
    
CREATE QUEUE NameChangeQueue;
CREATE SERVICE NameChangeService ON QUEUE NameChangeQueue ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);
    
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO YourUserName;
 
ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

به جای YourDatabaseName نام دیتابیس خود را قرار دهید و به جای YourUserName نام کاربر SQL Server خود را وارد نمایید.


پس از اجرای این دستورات باید یک صف و یک سرویس جدید را در دیتابیس خود مانند زیر مشاهده کنید:

SqlDependency


حال یک پروژه Windows Application ایجاد کنید و سپس یک فرم جدید با نام InsertForm ایجاد و دو TextBox با نام های textBox_FirstName و textBox_LastName به همراه یک Button با نام button_Insert بر روی فرم قرار دهید.

حال بر روی Button مربوط به Insert دابل کلیک کنید و کد زیر را در آن قرار دهید:

try
     {
        using (SqlConnection cn = new SqlConnection(ShowData.connectionString))
         {
            using (SqlCommand cmd = cn.CreateCommand())
             {
                cmd.CommandText = "INSERT INTO Users VALUES (@FirstName, @LastName)";
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@FirstName", textBox_FirstName.Text);
                cmd.Parameters.AddWithValue("@LastName", textBox_LastName.Text);

                 cn.Open();

                 cmd.ExecuteNonQuery();
              }
            }
      }
 catch (Exception exception)
     {
       MessageBox.Show(exception.Message);
     }

در اینجا ما کد مربوط به درج اطلاعات را ثبت کردیم. حال به سراغ فرم مربوط به نمایش اطلاعات می رویم، فرم اصلی برنامه خود که Form1 نام دارد

انتخاب کنید و نام آن را به ShowData تغییر دهید.

بر روی فرم یک Button با نام button_ShowInsertForm برای نمایش فرم InsertForm قرار دهید و همچنین یک لیست باکس برای نمایش اطلاعات

ثبت شده با نام listBox_ShowData بر روی فرم قرار دهید.

بر روی Button ایجاد شده (button_ShowInsertForm) دابل کلیک کنید و کد زیر را در آن قرار دهید:

 private void button_ShowInsertForm_Click(object sender, EventArgs e)
        {

            try
            {
                InsertForm insertForm = new InsertForm();
                insertForm.Show();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
        }

کد زیر را در بخش اصلی کلاس فرم، (خارج از Event ها) در انتهای کد

public partial class ShowData : Form
    {

کد زیر را اضافه کنید:

 public static string connectionString = @"Data Source=DadehNegar.Blog.ir\SQL2012;Initial Catalog=SQlDepedency_Dadaehnegar; user ID=sa; password=******;";

حال باید کد مربوطه را بنویسیم، اولین چیزی که می نویسیم این است که چک کنیم که آیا این کاربری که شما برای متصل شدن به SQL Server معرفی کرده اید دسترسی به query notifications دارد یا خیر؟

در اینجا ما از کلاس SqlClientPermission برای اینکار استفاده می کنیم. بر روی فرم InsertForm کلیک راست کنید و گزینه View Code را انتخاب و در بخش کد نویسی این متد جدید را اضافه کنید:

private bool DoesUserHavePermission()
{
    try
        {
         SqlClientPermission clientPermission = new SqlClientPermission(PermissionState.Unrestricted);

          // will throw an error if user does not have permissions
            clientPermission.Demand();

             return true;
         }
     catch
         {
          return false;
         }
}

حال متد مورد نظر خود را برای بدست آوردن اطلاعات از  می نویسیم

private void GetNames()
 {
   try
      {
       if (!DoesUserHavePermission())
          return;

        listBox_ShowData.Items.Clear();

        //  You must stop the dependency before starting a new one.
        //  You must start the dependency when creating a new one.
        SqlDependency.Stop(connectionString);
        SqlDependency.Start(connectionString);

         using (SqlConnection cn = new SqlConnection(connectionString))
          {
            using (SqlCommand cmd = cn.CreateCommand())
                {
                      
                 cmd.CommandType = CommandType.Text;
                 cmd.CommandText = "SELECT FirstName, LastName FROM dbo.[Users]";

                  cmd.Notification = null;

                  //  creates a new dependency for the SqlCommand
                  SqlDependency dep = new SqlDependency(cmd);
                  //  creates an event handler for the notification of data
                  //      changes in the database.
                  //  NOTE: the following code uses the normal .Net capitalization methods, though
                 //      the forum software seems to change it to lowercase letters
                 dep.OnChange += new OnChangeEventHandler(dep_onchange);

                 cn.Open();

                  using (SqlDataReader dr = cmd.ExecuteReader())
                  {
                   while (dr.Read())
                     {
                       listBox_ShowData.Items.Add(dr.GetString(0) + " " + dr.GetString(1));
                     }
                   }
               }
          }

     }
   catch (Exception exception)
      {
        throw new Exception(exception.Message);
      }

 }

چند نکته در ارتباط با SqlDependency

همانطور که می بینید من در Query خود از کارکتر * برای بدست اوردن تمام اطلاعات ستوان های جدول خود استفاده نکردم، زیرا در صورت استفاده از این کارکتر به جای قید کردن نام تمام ستون ها شما اطلاعات نادرست دریافت خواهید کرد.
در نتیجه باید ستون هایی را که نیاز دارید، تک تک قید کنید.
نکته بعد در مورد قید کردن نام جداول است در SqlDependency هیچوقت نام جداول را به همراه owner آن قید نکنید (owner.TableName) بلکه فقط نام جدول را به تنهایی قید کنید زیرا اگر با owner نام جدول مشخص شود، اطلاعات نادرست دریافت خواهید کرد.

این  event را نیز به برنامه خود اضافه کنید:

void dep_onchange(object sender, SqlNotificationEventArgs e)
        {
            // this event is run asynchronously so you will need to invoke to run on UI thread(if required).
            if (this.InvokeRequired)

                listBox_ShowData.BeginInvoke(new MethodInvoker(GetNames));
            else
                GetNames();

            // this will remove the event handler since the dependency is only for a single notification
            SqlDependency dep = sender as SqlDependency;

            //  NOTE: the following code uses the normal .Net capitalization methods, though
            //      the forum software seems to change it to lowercase letters
            dep.OnChange -= new OnChangeEventHandler(dep_onchange);
        }

کد مربوط به Form_Load را به صورت زیر تغییر دهید:

 private void ShowData_Load(object sender, EventArgs e)
        {
            try
            {
                GetNames();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
        }

شما باید SqlDependency در زمانی که می خواهید از برنامه خارج شوید متوقف کنید، پس به قسمت نمایش فرم بازگرید و در قسمت ساخت Event ها در پنجره Properties رویداد (Event) مربوط به FormClosing را انتخاب و برروی آن دابل کلیک کنید تا این رویداد ایجاد شود و سپس کد زیر را در آن قرار دهید:

   private void ShowData_FormClosing(object sender, FormClosingEventArgs e)
        {
            try
            {
            }

            finally
            {
                SqlDependency.Stop(connectionString);
            }
        }

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

با استفاده از این آموزش ما در دوره آموزشی SignalR  می خواهیم برنامه های Real Time مانند چت و ... ایجاد کنیم.

اگر شما نمی توانید پروژه خود را به درستی پیاده سازی کنید با استفاده از کد پروژه قرار داده شده در زیر می توانید مشکل خود را برطرف کنید و از این مثال استفاده کنید:

دانلود پروژه SQLDependency

به دلیل اینکه ممکن است نتوانید SQL Server خود را به درستی برای این پروژع تنظیم کنید، از دیتابیس خود یک Script در لینک زیر برای دانلود قرارداده ام کافی است این فایل را در SQL Server باز کنید و آن را اجرا (Excute) کنید.

دانلود اسکریپت دیتابیس SqlDependency

نظرات

  • Hannah Martinez
    mitra
    دو شنبه 11 دی 1278 - 0:00

    سلام، خیلی ممنون قسمت sql درست شد دو تا سوال کدنویسی پیش اومده ممنون میشم اینا رو هم راهنمایی کنید.


    _یک فرم  اصلی هستش با کلی کمبوباکس و یک دیتاگرید که هر کدوم از این ها در یک فرم جدا ثبت میشن و بالای صفحه منو فرم ها ثابته و فرم ها به صورت تب پایین منو باز میشن_

    روی یکی از کمبوها این کدها رو امتحان کردم ( از قسمت  private bool DoesUserHavePermission() به بعد کدهای بالا رو تو فرمی که کمبو بود استفاده کردم) مشکلی که پیش اومد این بود: وقتی فرم برای اولین بار میشه برای 3000 تا رکورد حدود 1 دقیقه طول میکشه واینکه میشه راجع به بقیه کمبوها هم راهنمایی کنید که برای هر کدوم تابع جدا و ... بنویسم یا همشون یکجا در یک تابع باشن.

    • Judith Bell
      پاسخ
      اسماعیلشیدایی
      دو شنبه 11 دی 1278 - 0:00

      با سلام

      چند نکته را در خصوص سوال شما باید مطرح کنیم:
      1- طبیعتا استفاده از SQL Dependency هزینه ای اضافه را بر SQL Server ایجاد خواهد کرد، پس باید کارایی را برای داشتن برنامه جذاب تر فدا نکنید.
      2- راه های زیادی برای واکشی اطلاعات وجود دارد و طبیعتا وابسته به Relation جداول و نحوه کوئری گرفتن زمان لود متفاوت خواهد بود.
      برای بررسی دقیقتر و رفع مشکل پیشنهاد می کنم دو مقاله زیر را بررسی کنید.

      http://ably.ir/post/Entity-Framework

      و

      http://ably.ir/post/SQL%20SERVER%20%E2%80%93%20Tips%20for%20SQL%20Query%20Optimization%20by%20Analyzing%20Query%20Plan

  • Hannah Martinez
    mitra
    دو شنبه 11 دی 1278 - 0:00

    سلام، من موقع نصب sql مد windows athentication رو انتخاب کردم و بعد داخلش login تعریف کردم و وقتی این کدها رو میزنم این  error رو میده:

    Cannot find the user 'mitra', because it does not exist or you do not have permission.
    اگه ممکنه راهنمایی کنید.

    • Judith Bell
      پاسخ
      اسماعیلشیدایی
      دو شنبه 11 دی 1278 - 0:00

      با عرض سلام

      آنچه مشخص است یا نام کاربری و کلمه عبور شما اشتباه است یا چنین نام کاربری و کلمه عبوری وجود ندارد.
      ما روش های مختلفی برای متصل شدن به SQL Server داریم که شما می بایست بر اساس اینکه از چه Mode و یا روشی استفاده می کند، Connection String خود را ایجاد کنید.

      یک آموزش ساده برای تعیین و ایجاد و مطمئن شدن از Connection String را در لینک زیر توضیح داده ایم لطفا این مقاله را مطالعه کنید تا انشالله مشکل شما برطرف گردد.


      آموزش ساخت Connection String

      موفق باشید

  • Hannah Martinez
    devotion
    دو شنبه 11 دی 1278 - 0:00

    سلام. خسته نباشید
    این دستور رو تو sql اجرا کردم خطای syntax داد، میشه لطفا راهنمایی کنید؟
    REATE SERVICE NameChangeService ON QUEUE NameChangeQueue ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification])

    • Judith Bell
      پاسخ
      اسماعیلشیدایی
      دو شنبه 11 دی 1278 - 0:00

      باسلام
      عذر خواهی می کنم بنده کاراکتر C را در خط دوم ننوشته بودم
      کد را تصحیح کردم

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

      USING [YourDatabaseName]
       
      CREATE QUEUE NameChangeQueue;
      CREATE SERVICE NameChangeService ON QUEUE NameChangeQueue
      ([http://schemas.microsoft.com/sql/notifications/postquerynotification]);
       
      GRANT SUBSCRIBE QUERY NOTIFICATIONS TO YourUserName;

      موفق باشید

  • Hannah Martinez
    دو شنبه 11 دی 1278 - 0:00

    حل شد مرسی

    باید این خط رو بجاش اجرا کنیم
    DATABASE db_name SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

  • Hannah Martinez
    دو شنبه 11 دی 1278 - 0:00

    من این دستور رو اجرا میکنم خطای دسترسی میده.


    GRANT SUBSCRIBE QUERY NOTIFICATIONS TO sa;
     چکارش کنم

  • Hannah Martinez
    alale
    دو شنبه 11 دی 1278 - 0:00

    ممنون از شما

    من یه مشکلی با sqlDependency پیدا کردم و اون این هست که اگر به این پروژه یه فرم دیگه اضافه کنم و  فرم  ShowData  رو از داخلش فراخوانی کنم Event onchange به تعداد بارهای باز شدن فرم تکرار میشه. خیلی دنبال این قضیه گشتم ولی متاسفانه نتونستم به جوابی برسم. ممنون میشم راهنماییم کنید.





    • Judith Bell
      پاسخ
      اسماعیلشیدایی
      دو شنبه 11 دی 1278 - 0:00

      باعرض سلام خدمت شما دوست عزیز

      متاسفانه منظور شما متوجه نمی شوم، مشکل شما دقیقا چیست؟

  • Hannah Martinez
    wahab
    دو شنبه 11 دی 1278 - 0:00

    بصورت وب سایت   asp.net جواب نداد !

    • Judith Bell
      پاسخ
      اسماعیلشیدایی
      دو شنبه 11 دی 1278 - 0:00

      برای استفاده از تکنولوژی و سرویس در ASP.NET می بایست از SignalR و یا از JSON و سایر روش هایی مرتبط استفاده کنید. مثالی در مورد این سرویس در MVC در دوره آمورشی SignalR وجود دارد، اگر دوره را بخوانید، می توانید این کار را به سادگی در ASP.NET و یا ASP.NET MVC انجام دهید.

  • Hannah Martinez
    wahab
    دو شنبه 11 دی 1278 - 0:00

    حرف نداشت ممنون

    • Judith Bell
      پاسخ
      اسماعیلشیدایی
      دو شنبه 11 دی 1278 - 0:00

      ممنون از لطفتان

  • Hannah Martinez
    ali
    دو شنبه 11 دی 1278 - 0:00

    با سلام

    من sql dependency رو به صورت کامل اجرا کردم و جواب گرفتم حالا وقتی دیتابیس و برنامم رو منتقل میکنم به یه سیستم دیگه sql dependency کار نمیکنه تمام برنامه کار کیکنه ولی به صورت realtime کار نمیکنه .

    در ضمن broker اون سیستم رو هم فعال کردم و یوزر اونو هم عوض کردم ممنون میشم اگه کمک کنید.

    • Judith Bell
      پاسخ
      اسماعیلشیدایی
      دو شنبه 11 دی 1278 - 0:00

      با عرض سلام

      تنها راهکاری که می شود در این مورد داشت این است که مرحله به مرحله موارد را چک کنید. ابتدا دیتابیس کامل منتقل کنید (منظور دیتابیسی است که قبلا همه ی موارد روی آن کار می کرده است) سپس اگر کار انجام شد به معنی این خواهد بود که تنظیمات دیتابیس جدید بر روی سرور را درست انجام نداده اید. 

      اما اگر دیتابیس کار نکرد بهتر است به مباحث سرور بپردازید مثل IIS و تنظیمات SQL Server  و یا Firewall و وابسته به نوع سرور از مدیر سرور کمک بگیرید.

      موفق باشید

       

  • Hannah Martinez
    alireza
    دو شنبه 11 دی 1278 - 0:00

    با عرض سلام و خسته نباشید

    دیتابیسو کامل منتقل کردم و بدون هیچگونه مشکلی کار میکنه فقط برنامه realtime نمیشه یعنی اون تابع dep_onchange تغیییرات درون برنامه رو متوجه نمیشه بازم

    ممنون

    • Judith Bell
      پاسخ
      اسماعیلشیدایی
      دو شنبه 11 دی 1278 - 0:00

      با عرض سلام

      بهتر است اين موارد را با مدير سرور نيز هماهنگ كنيد و از ايشان بخواهيد يك بررسي كنند

       

  • Hannah Martinez
    alireza
    دو شنبه 11 دی 1278 - 0:00

    با عرض سلام دوباره

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

    -- بازم ممنون از اینکه وقت میزارید و به سوالا جواب میدید .

نظرات یا سوالات خودرا با ما درمیان بگذارید

0912 097 5516 :شماره تماس
0713 625 1757 :شماره تماس