تطوير مهارات إدارة قواعد البيانات باستخدام الأكواد الديناميكية (3/100)

مقالة
05/18/2015

Shehap El-Nagar

أيار/مايو عام 2015

بالمقالات السابقة قمنا بتناول المراحل الرئيسية لعمليات النقل و الدمج لقواعد البيانات من خادم A إلى الخادم B لكي يصبح الخادم B جاهز لكي يؤدي المهام الرئيسية لكن لكي تكتمل مهامه بشكل اكبر سوف انتقل سريعا الان إلى المحورين السادس و السابع على النحو التالي:

نسخ كل اعدادت البريد الالكتروني المستخدمة في ارسال التنبيهات و الانذارات المختلفة.
نسخ كل بيانات الروابط مع خوادم قواعد البيانات الاخرى بالاضافة إلى ترميزات خاصة لأسماء الخوادم ان وجدت

[sql]

USE msdb
GO

Declare @TheResults varchar(max),
        @vbCrLf CHAR(2)
SET @vbCrLf = CHAR(13) + CHAR(10)        
SET @TheResults = '
use master
go
sp_configure ''show advanced options'',1
go
reconfigure with override
go
sp_configure ''Database Mail XPs'',1
--go
--sp_configure ''SQL Mail XPs'',0
go
reconfigure
go
'
SELECT @TheResults = @TheResults  + '
--#################################################################################################
-- BEGIN Mail Settings ' + p.name + '
--#################################################################################################
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE  name = ''' + p.name + ''') 
  BEGIN
    --CREATE Profile [' + p.name + ']
    EXECUTE msdb.dbo.sysmail_add_profile_sp
      @profile_name = ''' + p.name + ''',
      @description  = ''' + ISNULL(p.description,'') + ''';
  END --IF EXISTS profile
  '
  +
  '
  IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE  name = ''' + a.name + ''')
  BEGIN
    --CREATE Account [' + a.name + ']
    EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name            = ' + CASE WHEN a.name                IS NULL THEN ' NULL ' ELSE + '''' + a.name                  + '''' END + ',
    @email_address           = ' + CASE WHEN a.email_address       IS NULL THEN ' NULL ' ELSE + '''' + a.email_address         + '''' END + ',
    @display_name            = ' + CASE WHEN a.display_name        IS NULL THEN ' NULL ' ELSE + '''' + a.display_name          + '''' END + ',
    @replyto_address         = ' + CASE WHEN a.replyto_address     IS NULL THEN ' NULL ' ELSE + '''' + a.replyto_address       + '''' END + ',
    @description             = ' + CASE WHEN a.description         IS NULL THEN ' NULL ' ELSE + '''' + a.description           + '''' END + ',
    @mailserver_name         = ' + CASE WHEN s.servername          IS NULL THEN ' NULL ' ELSE + '''' + s.servername            + '''' END + ',
    @mailserver_type         = ' + CASE WHEN s.servertype          IS NULL THEN ' NULL ' ELSE + '''' + s.servertype            + '''' END + ',
    @port                    = ' + CASE WHEN s.port                IS NULL THEN ' NULL ' ELSE + '''' + CONVERT(VARCHAR,s.port) + '''' END + ',
    @username                = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''' + c.credential_identity   + '''' END + ',
    @password                = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''NotTheRealPassword''' END + ', 
    @use_default_credentials = ' + CASE WHEN s.use_default_credentials = 1 THEN ' 1 ' ELSE ' 0 ' END + ',
    @enable_ssl              = ' + CASE WHEN s.enable_ssl = 1              THEN ' 1 ' ELSE ' 0 ' END + ';
  END --IF EXISTS  account
  '
  + '
IF NOT EXISTS(SELECT *
              FROM msdb.dbo.sysmail_profileaccount pa
                INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
                INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id  
              WHERE p.name = ''' + p.name + '''
                AND a.name = ''' + a.name + ''') 
  BEGIN
    -- Associate Account [' + a.name + '] to Profile [' + p.name + ']
    EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
      @profile_name = ''' + p.name + ''',
      @account_name = ''' + a.name + ''',
      @sequence_number = ' + CONVERT(VARCHAR,pa.sequence_number) + ' ;
  END --IF EXISTS associate accounts to profiles
--#################################################################################################
-- Drop Settings For ' + p.name + '
--#################################################################################################
/*
IF EXISTS(SELECT *
            FROM msdb.dbo.sysmail_profileaccount pa
              INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
              INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id  
            WHERE p.name = ''' + p.name + '''
              AND a.name = ''' + a.name + ''')
  BEGIN
    EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = ''' + p.name + ''',@account_name = ''' + a.name + '''
  END 
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE  name = ''' + a.name + ''')
  BEGIN
    EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = ''' + a.name + '''
  END
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE  name = ''' + p.name + ''') 
  BEGIN
    EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = ''' + p.name + '''
  END
*/
  ' 
FROM msdb.dbo.sysmail_profile p
INNER JOIN msdb.dbo.sysmail_profileaccount pa ON  p.profile_id = pa.profile_id
INNER JOIN msdb.dbo.sysmail_account a         ON pa.account_id = a.account_id 
LEFT OUTER JOIN msdb.dbo.sysmail_server s     ON a.account_id = s.account_id
LEFT OUTER JOIN sys.credentials c    ON s.credential_id = c.credential_id

   ;WITH E01(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL  
                    SELECT 1 UNION ALL SELECT 1 UNION ALL  
                    SELECT 1 UNION ALL SELECT 1 UNION ALL  
                    SELECT 1 UNION ALL SELECT 1 UNION ALL  
                    SELECT 1 UNION ALL SELECT 1), --         10 or 10E01 rows  
         E02(N) AS (SELECT 1 FROM E01 a, E01 b),  --        100 or 10E02 rows  
         E04(N) AS (SELECT 1 FROM E02 a, E02 b),  --     10,000 or 10E04 rows  
         E08(N) AS (SELECT 1 FROM E04 a, E04 b),  --100,000,000 or 10E08 rows  
         --E16(N) AS (SELECT 1 FROM E08 a, E08 b),  --10E16 or more rows than you'll EVER need,  
         Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E08),  
       ItemSplit(  
                 ItemOrder,  
                 Item  
                ) as (  
                      SELECT N,  
                        SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf),CHARINDEX(@vbCrLf,@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf)) - N - DATALENGTH(@vbCrLf))  
                      FROM Tally  
                      WHERE N < DATALENGTH(@vbCrLf + @TheResults)  
                      --WHERE N < DATALENGTH(@vbCrLf + @INPUT) -- REMOVED added @vbCrLf  
                        AND SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N,DATALENGTH(@vbCrLf)) = @vbCrLf --Notice how we find the delimiter  
                     )  
  select  
    row_number() over (order by ItemOrder) as ItemID,  
    Item  
  from ItemSplit  
 
  

[/sql]

الجدير بالذكر أن هذا الكود يغطي بشكل شامل ادق تفاصيل اعدادت البريد الالكتروني بشكل مذهل مثل :

سجلال اعدادات البريد الالكتروني

سابات البريد الالكتروني

اسم خادم البريد الالكتروني

البريد المرسل

البريد المستخدم في الرد

..... الخ

بالطبع ليس من الضروري تنفيذه كما هو بل يمكنك تعديله كيفما تشاء ليتماشي مع اعدادات الخادم الثاني B مثل اسم خادم البريد الالكتروني الذي قد يتغير من بيئة عمل الخادم A إلى بيئة عمل الخادم B... الخ.

بعد الانتهاء من هذه الخطوة يصبح من مقدورك انشاء تنبيهات البريد الالكتروني لكل المهام المجدولة التي تم انشاءها في المقالة السابقة على النحو التالي :

[sql]
	USE [msdb]  

	 DECLARE @OpName VARCHAR (50),  
		   @OpID INT,  
		   @Count INT,   
		   @Recs INT  

	SET @OpName = 'DBAlerts'  /*Check if operator exists*/  SELECT @Recs = COUNT(*)  
	  FROM [msdb].[dbo].[sysoperators] WHERE [name] = @OpName  
	

	/*Clear record count*/  SET @Recs = 0  

	/*Get Operators ID */  SELECT @OpID = ID   FROM sysoperators  WHERE name = @OpName  

	/*Add Job Alert Notification*/  UPDATE sysjobs   SET [notify_level_email] = 2, [notify_level_eventlog] = 0, [notify_email_operator_id] = @OpID  
	/*Add Alert notification*/  UPDATE sysalerts  SET has_notification = 1  
	SET @Count = (SELECT MIN(id) FROM sysalerts)  WHILE @Count <= (SELECT MAX(id) FROM sysalerts) BEGIN  
	   /*Check Alert exists*/  
	   SELECT @Recs = COUNT(*)   
	   FROM sysnotifications  
	   WHERE alert_id = @Count  
	     
	   /*If alert doesn't exist add*/  
	   IF @Recs = 0  
		   INSERT INTO [msdb].[dbo].[sysnotifications]  
			   ([alert_id]  
			   ,[operator_id]  
			   ,[notification_method])  
		   VALUES  
			   (@Count  
			   ,@OpID  
			   ,1)  

	   SET @Count = @Count +1  
	END  
	 
[/sql]

و لضمان صحة و دقة اعدادات البريد الالكتروني يفضل اجراء عملية اختبار لعملية ارسال بريد الالكتروني من خلال محرك قواعد البيانات على النحو التالي :

الضغط بيمين الفآرة على ايقونة ال Database Mail >>>Test Mail >>> و من ثم قم بتنفيذ الكود ادناه للتاكد من وصول الاميل او لا :

[sql]
use msdb
SELECT *
FROM sysmail_mailitems
GO
SELECT *
FROM sysmail_log
GO 
[/sql]

نسخ كل بيانات الروابط مع خوادم قواعد البيانات الاخرى بالاضافة إلى ترميزات خاصة لأسماء الخوادم ان وجدت:

لقد اصبح بالفعل استخدام الروابط بين الخوادم شئ غالية في الضرورة لعدة اسباب اما لآجراء الأستعلامات الموزعة بين قواعد البيانات الموجودة على خوادم مختلفة لأجل انظمة تمركز البيانات (Data Warehousing) او لأجل الترابط بين بعض الانظمة أو ربما من اجل عمليات التزامن بين قواعد البيانات (Replication) لذا يجب مراعاة نسخ بيانات الترابط بحرص شديد مع التنويه على اهمية الاخذ في الاعتبار ايضا اي ترميزات خاصة ( اسماء مستعارة ) لاسماء الخوادم التي ربما تكون مستخدمة على الخادم A للوصول إلى خوادم قواعد بيانات اخرى باستخدام هذه الاسماء المستعارة لكن كيف يمكن استكشاف و نسخ هذه الترميزات ( الاسماء المستعارة) ..؟

توجه للخادم A >>>اضغط RUN >>>Cliconfg أو ربما يمكنك استخدام ال Registry من خلال الضغط على :

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo حيث يمكنك استخدام كل الترميزات و الاسماء المستعارة و من ثم نسخهم إلى الخادم B مع مراعاة نسخهم إلى الخادمين الخاصين بمضيف قواعد البيانات B اذا كان خادم مزدوج ( DB Cluster ) و من اجل معرفة المزيد عن هذا الموضوع "الاسماء المستعارة أو ترميزات الخوادم" ( Aliases ) يمكنك الاطلاع على المقالة التالية :

https://blogs.msdn.com/b/sql_protocols/archive/2007/01/07/connection-alias.aspx

بعد الانتهاء من هذه الخطوة يمكنك القدم في انشاء روابط الخوادم بشكل سلسل و سهل على النحو التالي:

توجه للخادم A >>> اضغط على ايقونة Server Objects >>> Linked Servers >>> حينذاك ظلل على كل الروابط و اضغط بيمين الفآرة >>> ثم انشاء كود واحد لانشاء كلهم مرة واحدة و من ثم نسخه إلى الخادم B لتنفيذه هناك بشكل سلسل و سهل

و كالعادة يجب التاكد من الاعدادات الجديدة بعد نسخها من الخادم A لتاكيد صحة و دقة عملية النقل و الدمج في كل مرحلة على النحو التالي :

توجه للخادم B >>> و من ثم Linked Servers >>> اضغط بيمين الفآرة على كل رابط على حدة و قم بعملية اختبار اتصال

( Test Connection )

أبقوا معنا على اتصال في المقالة القادمة لمعرفة المزيد و المزيد من حزم الأكواد الدينامكية

يمكنك التواصل معنا من خلال

Facebook Page, LinkedIn Group, Twitter, Networked Blogs, Facebook Group, Youtube Channel

تطوير مهارات إدارة قواعد البيانات باستخدام الأكواد الديناميكية (3/100)

Shehap El-Nagar

أيار/مايو عام 2015

يمكنك التواصل معنا من خلال

الموارد الإضافية