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

  • مقالة

Shehap El-Nagar

نيسان/أبريل عام 2015

بالمقالة السابقة لقد بدانا بتناول موضوع نقل و دمج البيانات و قمنا بتحديد 10 محاور رئيسية لعملية النقل حيث تناولنا بالتفصيل المحور الأول و الثاني و نحن بصصد تناول المحور الثالث و الرابع و الخامس و ساتحدث لاحقا عن البند الاخير من المحور الثاني الخاص بالتشفير ( Encryption ) من اجل القدوم اسرع في تنفيذ عملية نقل و دمج قواعد البيانات من الخادم الأول إلى الخادم الثاني.

  1. نسخ كل حسابات المحرك ( SQL logins ) مع الاخذ في الاعتبار مستخدمينهم على كل قواعد البيانات و الخادم كذلك ايضا فضلا عن كلمة السر ( ان كان الحساب غير موصول بالدليل النشط ).
  2. نسخ كل مكونات التقارير (+.rds .rdl ) بين الاصدارات المختلفة حيث لا يمكن تنفيذ عملية التخزين و الاسترجاع لقواعد بيانات خدمة التقارير ( Reporting Service ) ألا وهما reportserver and reportservertemp DBs.
  3. نسخ كل المهام المجدولة زمنيا لأداء وظائف بيزنس بالاضافة إلى بيانات المسئولين عن متابعة هذه المهام و مراقبتها عبر الاميل.

 

ثالثا : نسخ كل حسابات المحرك ( SQL logins ) مع الاخذ في الاعتبار مستخدمينهم على كل قواعد البيانات و الخادم كذلك ايضا فضلا عن كلمة السر ( ان كان الحساب غير موصول بالدليل النشط ).

  1. يجب نسخ كل حسابات محرك قواعد البيانات ( SQL Server Logins ) من الخادم الأول A إلى الخادم الثاني B باستخدام الكود sp_help_revlogin الذي يجب انشاءه اولا كما هو موضح ادناه:
[sql]

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
	
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL  
AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname
CREATE TABLE #logins (
	[logins] [nvarchar](max) NULL )


IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR


      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
  
  END
  insert into #logins select @tmpstr
  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
 
select * from #logins
drop table #logins

GO

--- to run the procedure execute the next step, 
--and bear in mind to remove the accounts already exist or the current service accounts

[/sql]
  1. حينذاك يمكنك تنفيذه على الخادم الأول A كما هو موضح ادناه
[sql]

EXEC master..sp_help_revlogin

[/sql]
  1. و من ثم يمكنك نسخ مخرجات التنفيذ إلى الخادم الثاني B كالتالي :

حتى هذه اللحظة ، فلقد قمنا بنسخ الحسابات فقط لكن بدون اي صلاحيات على قواعد البيانات إلا اذا كانت هذه الحسابات موصولة بالدليل النشط ( AD ) حينذاك كل الصلاحيات يتم نسخهم اتوماتيكيا مع عملية التخزين و الاسترجاع لقواعد البيانات التي تم شرحها سابقا بالمقالة الاولي حيث ان صلاحيات مثل هذه النوعية من المستخدمين الموصولين بالدليل النشط تعتبر جزءا من اعدادات الامان الخاصة بكل قاعدة بيانات على حدة خلافا للمستخدمين الغير موصولين بالدليل النشط اعني ( SQL Authenticated users ) حيث ان صلاحياتهم تعتبر جزءا من اعدادات الامان المخزنة داخل قاعدة بيانات المحرك الرئيسي ( Master ) لذا يجب نسخهم على النحو التالي :

اولا قم بتنفيذ الكود ادناه على الخادم A 

[sql]

CREATE TABLE #permissions (xx varchar(2000))
INSERT INTO #permissions 

exec sp_msforeachdb 'Use [?]; SELECT 
   CASE dp.state_desc 
     WHEN ''GRANT_WITH_GRANT_OPTION'' THEN ''GRANT'' 
     ELSE dp.state_desc  
   END  
     + '' '' + dp.permission_name + '' ON '' + 
   CASE dp.class 
     WHEN 0 THEN ''DATABASE::['' + DB_NAME() + '']'' 
     WHEN 1 THEN ''OBJECT::['' + SCHEMA_NAME(o.schema_id) + ''].['' + o.[name] + '']'' 
     WHEN 3 THEN ''SCHEMA::['' + SCHEMA_NAME(dp.major_id) + '']'' 
   END  
     + '' TO ['' + replace (USER_NAME(grantee_principal_id),''domain\'',''domain\'') + '']'' + 
   CASE dp.state_desc 
     WHEN ''GRANT_WITH_GRANT_OPTION'' THEN '' WITH GRANT OPTION;'' 
     ELSE '';''  
   END  
   COLLATE DATABASE_DEFAULT 
FROM sys.database_permissions dp 
  LEFT JOIN sys.all_objects o 
    ON dp.major_id = o.OBJECT_ID 
WHERE dp.class < 4 
  AND major_id >= 0 
  AND grantee_principal_id <> 1;  ' ;


SELECT * FROM #permissions
drop table #permissions


[/sql]

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

رابعا: نسخ كل مكونات التقارير (+.rds .rdl ) بين الاصدارات المختلفة حيث لا يمكن تنفيذ عملية التخزين و الاسترجاع لقواعد بيانات خدمة التقارير ( Reporting Service ) ألا وهما reportserver and reportservertemp DBs.

لا شك أن نسخ قواعد البيانات ال reportserver and reportservertemp بين الخادم الأول A و الخادم الثاني B هو اسهل و افضل الطرق لكن ليس ممكنا دائما بسبب اختلاف الاصدارات إما اصدار رئيسي مثل SQL Server 2008, SQL Server 2012, SQL Server 2014 أو حتى اصدار فرعي مثل SQL Server 2008R2 لذا لا حل لمثل تلك الحالات إلا نسخ ملفات التقارير بشكل يدوي من الخادم A إلى الخادم B و هذا بالطبع يعتبرغاية في الصعوبة لفعل هذا لعدد كبير من التقارير لذا يفضل استخدام بعض الادوات الخارجية مثل RSSscripter لاستخراج كل ملفات التقارير الموجودة على الخادم A سواء .rdl أو ال .rds و نقلهم معا مرة واحدة إلى الخادم B لكن يجب مراعاة التالي :

  1. هذه الأداة RSSscripter لا تغطي صلاحيات المستخدمين على المجلدات و المجلدات الفرعية .
  2. ملفات ال .rds الخاصة بارتباط التقارير بقواعد البيانات سيتم نسخها كما هي بنفس الاعدادات التي تشير الي اسم الخادم A لذا يجب تغييرها لتشير الى الخادم B .
  3. هذه الأداة RSSscripter لا تغطي نسخ بعض الاعدادات الخاصة بخدمة التقارير مثل ال assemblies , data extensions rendering options, .
  4. بشكل افتراضي هذه الأداة RSSscripter لا تغطي نسخ اشتراكات التقارير الدورية التي ترسل عبر الاميل للمستخدمين لكن يمكنك ضبط اعدادات الاداة لكي تقوم بهذه المهمة .

يمكنك تنزيل هذه الاداة هنا و تجربتها ببساطة و للمزيد من المعلومات عن كيفية استخدامها يمكنك قراءة ال Readme.txt

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

بصفة عامة يمكن فعل هذا بعدة طرق لكن من الطبع ان نسخ قاعدة البيانات MSDB من الخادم الأول إلى الخادم الثاني يعد من اسهل الطرق لكن مرة اخرى هذا ليس ممكنا دائما خاصة ان اختلف اصدارات الخوادم A و B لكن بالتاكيد يمكن نسخهم يدويا بسهولة و بسرعة من غير اي مشقة على النحو التالي :

نسخ المهام المجدولة زمينا

نسخ بيانات المسئولين عن متابعة هذه المهام.

التنبيهات

كلا منهم يمكن نسخهم بمجرد التظليل على مجموعة مكوناتهم و الضغط على يمين فآرة الماوس حينذاك قم باستخراج اكواد انشاء لكل منهم و قم بتنفيذها على الخادم B

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

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

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