Category: SQL Server

  • How to: SQL Server Failover Cluster

    สิ่งที่ต้องมี

    • Software
    • Hardware
      • เครื่องคอมพิวเตอร์อย่างน้อย 2 เครื่องสเป็คเดียวกัน (ในเอกสารนี้ใช้ 2 เครื่อง)
      • ในเอกสารนี้ใช้ VMware ESXi
      • รายละเอียดเพิ่มเติม Hardware Requirements
      • เครื่องคอมพิวเตอร์ 1 เครื่องสำหรับเป็น iSCSI เซิร์ฟเวอร์
    • IP address
      • สำหรับเครื่อง Server 2 เลข
      • สำหรับ Microsoft failover cluster 1 เลข
      • สำหรับ Microsoft SQL Server failover cluster 1 เลข
      • ทุกไอพีต้องมีชื่อใน domain .psu.ac.th
    • Shared Storage
      • iSCSI (ในเอกสารนี้เลือกใช้ iSCSI)
        • IP address สำหรับ  iSCSI Server
      • หากมี shared storage อื่นๆ สามารถใช้ได้เลย
    • Domain Account
      • ติดต่อศูนย์คอมพิวเตอร์เพื่อขอสิทธิ์จอยโดเมน

    มีครบแล้วก็เริ่มกันเลย

    • Shared Storage (iSCSI) (Windows Server 2012R2 Data center)
      • เข้าระบบด้วยบัญชี administrator
      • คลิกปุ่ม start พิมพ์ windows update เพื่อเปิดโปรแกรม windows update ให้ update ทุกอย่างให้เรียบร้อย (restart หากต้อง restart หากรีสตาร์ทกลับมาแล้วเข้าระบบด้วยผู้ใช้ administrator)
      • เปิด Server Manager (ไอคอนแรกข้างปุ่ม start , โดยปกติจะถูกเรียกใช้งานโดยอัตโนมัติ)
      • ที่ Dashboard มองไปที่ QUICK START ข้อ 2 Add roles and features คลิก
      • คลิก Next เลือก Role-based of feature-based installation คลิก Next เลือก Select a server from the server pool เลือกเครื่องของเราในช่องด้านล่างคลิก Next มองหา File and Storage Services คลิกสามเหลี่ยมก็จะมี File and iSCSI Services ทำเครื่องหมายถูกหน้าข้อความ iSCSI Target Server จะมีหน้าต่าง สรุปการติดตั้งขึ้นมา คลิก Add Features คลิก Next ไปเรื่อยๆ จนเจอคำว่า Install คลิก Install รอจนกว่าจะติดตั้งเสร็จ (Installation succeeded on …) คลิก Close
      • กลับมาที่ Server Manager คลิกที่ File and Storage Services คลิก iSCSI มองไปด้านขวาจะปุ่ม TASKS
      • ให้คลิก TASKS เลือก New iSCSI Virtual Disk… จะมีหน้าต่าง New iSCSI Virtual Disk Wizard เลือก Type a custom path ให้ Browse… ไปยังที่เก็บไฟล์ที่ต้องการ คลิก Next จะเป็นหน้าที่ให้ตั้งชื่อไฟล์ ตั้งชื่อแล้วคลิก Next ระบุขนาดที่ต้องการที่นี้ให้ใส่ไป 5GB Fixed size คลิก Next เลือก New iSCSI target คลิก Next ตั้งชื่อสำหรับ shared disk ลูกนี้ คลิก Next ในหน้า Access Servers คลิกปุ่ม Add… เลือก Enter a value for the selectd type ให้เลือกเป็น DNS Name หรือ IP address ก็ได้แล้วใส่เครื่องทั้งสองเครื่องที่จะทำ Cluster ลงไป (ทำทีละเครื่อง) เมื่อใส่ครบแล้วคลิก Next 2 ครั้ง คลิก Create คลิก Close
      • ทำซ้ำข้อที่แล้วอีกครั้งโดยขนาดดิสก์เป็น 10GB เปลี่ยนตรงขั้นตอน iSCSI Target ให้เลือก Existing iSCSI target: คลิก Next แล้วคลิก Create คลิก Close
    • Join Domain (Windows Server 2012R2 Data center)
      • ล็อคอินเข้าระบบด้วยผู้ใช้ administrator
        • คลิกปุ่ม start พิมพ์ windows update เพื่อเปิดโปรแกรม windows update ให้ update ทุกอย่างให้เรียบร้อยทั้ง 2 เครื่อง
        • คลิกขวาที่ปุ่ม start เมนูเลือก system
        • ที่หัวข้อ Computer name, domain, and workgroup settings คลิก Change settings
        • ที่หน้าต่าง System Properties แท็บ Computer Name คลิก Change…
        • Member of  เลือก Domain พิมพ์ psu.ac.th คลิก OK จะมีการให้ยืนยันตัวตนว่ามีสิทธิ์ join domain หรือไม่ รอสักครู่ แล้วจะมีข้อความว่าต้อง restart เครื่อง ให้ restart ได้เลย
        • เมื่อรีสตาร์ทเสร็จแล้ว เข้าระบบด้วยผู้ใช้ administrator
        • เปิด Computer Management โดยคลิกขวาที่ปุ่ม start เลือก Computer Management คลิกที่สามเหลี่ยมหน้าข้อความ Local Users and Groups มองไปช่องทางขวาจะมี Administrators ให้ดับเบิ้ลคลิกเปิดขึ้นมา เลือก PSU\Domain Admins คลิก Remove คลิก Add… ใส่ Username ของเราลงไปคลิก OK ออกมาจนสุด
        • Sign Out ออกจากระบบ
        • Log In ใหม่ด้วย Username ของเราเอง psu\firstname.s
        • ทำเหมือนกันสองเครื่อง
        • จบการ Join Domain
    • ติดตั้ง Microsoft Failover Cluster
      • เข้าระบบด้วยผู้ใช้ Domain ที่เพิ่งเพิ่มเข้าไปในกลุ่ม Administrators
      • ที่ Server Manager ที่เมนู Tools คลิกเลือก iSCSI Initiator คลิก Yes จะได้หน้าต่าง iSCSI Initiator Properties ที่แท็บ Targets ในช่อง Target: ใส่ IP Address ของเครื่อง iSCSI Server คลิก Quick Connect… คลิกเลือก disk คลิก Done ทำเหมือนกันทั้งสองเครื่อง และเพิ่มดิสก์ทั้งสองก้อนเข้าไป
      • ที่ Server Manager คลิก Manage เลือก Add Roles and Features
      • จะได้หน้าต่าง Add Roles and Features Wizard คลิก Next เลือก Role-based or feature-based installation คลิก Next เลือก Server ใน Server Pool (ซึ่งมีอยู่เครื่องเดียว) คลิก Next ในหน้า Server Roles คลิก Next หน้า Features เลือก Failover Clustering คลิก Next จะมีหน้าต่างสรุปการติดตั้งขึ้นมาให้กด Add features แล้วคลิก Next ไปจนเจอคำว่า Install คลิก Install รอไปจนเสร็จ คลิก Close
      • ที่ Sever Manager คลิก All Servers คลิกขวาที่เครื่องในช่อง Server เลือก Failover Cluster Manager
      • ที่ Failover Cluster Manager
      • คลิก Validate Configuration
        • Before You Begin คลิก Next
        • เพิ่มชื่อเครื่องที่เตรียมไว้ทั้งสองเครื่องลงไป โดยพิมพ์ชื่อแล้วคลิก Add ทีละเครื่อง
        • เลือก Run all tests (recommended) ปล่อยให้ทำไปจนเสร็จ
        • ตรวจสอบ Log การ valid อาจมีเตือนเรื่อง network เป็น single points of failure เนื่องจากใช้ internet card เพียงใบเดียว
        • ทำเครื่องหมายถูกหน้าข้อความ Create Cluster Wizard เลือกเครื่องที่จะทำ Cluster กำหนดเลขไอพีสำหรับ Microsoft failover cluster (IP ข้อ 2) กำหนดชื่อ Cluster next ไปจนเสร็จ*
    • ติดตั้ง Microsoft SQL Server Cluster 2016
      • mount แผ่นติดตั้ง Microsoft SQL Server 2016 Server Cal เลือก New SQL Server failover cluster installation ในหน้า Installation
      • แทบจะ Next technology ได้เลยสิ่งที่ต้องกำหนดเพิ่มคือ ชื่อของ Microsoft SQL Server failover cluster และ IP address สำหรับ Microsoft SQL Server failover cluster เลือกดิสก์ที่ใช้งานสำหรับ SQL Server ให้เลือก shared disk ลูกที่ 2
      • รอจนเสร็จแล้วให้มาติดตั้งที่เครื่องที่ 2 โดยเลือก add new node และทำเหมือนเดิม
      • เสร็จ
    • จบขอให้สนุก…

    อ้างอิง

    https://msdn.microsoft.com/en-us/library/hh231721(v=sql.130).aspx

    https://blogs.msdn.microsoft.com/clustering/2012/04/06/installing-the-failover-cluster-feature-and-tools-in-windows-server-2012/

  • SSIS (Integration service) ตอนที่ 1 พื้นฐาน SSIS และ ETL

    SSIS (Integration service) เป็น Business Intelligent Tools ของ Microsoft ที่ออกมาเพื่อใช้งานในการจัดการข้อมูลในรูปแบบ ETL

    ซึ่ง ETL คืออะไร
    E – Extract การนำข้อมูลออกมาจาก Source database ซึ่งมาจากแหล่งเดียวหรือหลายแหล่ง
    T – Transform การแก้ไขและเปลี่ยนแปลงข้อมูลให้เหมาะสมที่จะในไปใช้งานต่อไป
    L – Load การเอาข้อมูลไปเก็บที่ปลายทาง (destination) แหล่งเดียวหรือแยกเป็นหลายๆแหล่ง

    โดย Business Intelligent Tools ของ Microsoft นั้นประกอบด้วย 3 ตัวได้แก่

    SSIS2

    Integration service :: เป็น ETL Tool
    Analysis service :: เป็น Tool ที่ใช้วิเคราะห์ข้อมูล ใช้ทำ Data warehouse
    Reporting service :: เป็น Tool ที่ใช้ในการสร้างรายงาน

     

    ก่อนที่จะมี Tools ที่ใช้ทำ ETL (ซึ่งมีหลายตัว หลายค่าย ทั้งฟรีและไม่ฟรี)
    เราต้องลำบากในการจัดการข้อมูลดังรูป

    unSSIS

    ที่มาของภาพ
    คือต้องจัดการข้อมูลจาก Source ต่างๆลงใน Database แยกเป็นแต่ละ Source กันไปแล้วค่อยมาทำการ Cleaning data ที่หลังแล้วค่อยไปรวมฐานข้อมูลอีกครั้งจึงจะเอาข้อมูลไปใช้งานได้ ซึ่งจะทำให้การทำงานค่อนข้างยุ่งยากและซับซ้อนและต้องทำซ้ำๆหลายๆครั้ง

     

    SSIS ทำงานอย่างไร

    SSIS1

    ที่มาของภาพ

    เตรียมข้อมูลให้พร้อมที่จะ load เข้าไปเก็บในฐานข้อมูล มี audit data เพื่อทำให้ตรวจสอบได้ว่าข้อมูลที่ได้มาได้มากจากไหน เมื่อไร process ไหนเป็นผู้จัดการข้อมูล สามารถทำ data cleaning ได้ก่อนที่จะนำข้อมูลเข้าสู่ฐานข้อมูล สามารถใช้ได้กับข้อมูลที่ใหญ่และซับซ้อนได้เป็นอย่างดี

    SSIS ประกอบด้วย

    • Package ไฟล์ของ SSIS นามสกุล *.dtsx
    • Control flow คือ Workflow engine สำหรับจัดการ tasks และ Containers ที่สั่ง Execute

     

    Control Flow

    • เป็น Workflow engine โดยมี Objects ใน Control Flow ดังนี้
      -Control flow tasks คือ การเนินการของ Workflow Object
      -Control flow Containers เป็น Grouping tasks กับ Tasks หรือ Containers อื่นๆ
      -Precedence constraints ให้ติดต่อ Tasks และ Containers และ กำหนดลำดับการ execute และ Precedence

    สำหรับจัดการ tasks และ Containers ที่สั่ง Execute

     

    Data flow

    • ควบคุมการการประมวลผลข้อมูลต่างๆ
    • Transform data จากแหล่งข้อมูล (Sources) ไปยัง ปลายทางข้อมูล (Destinations)

    Data flow task ยอดนิยมมีดังนี้

    1. Aggregate
    2. Conditional Split
    3. Data Conversion
    4. Derived Column
    5. Lookup
    6. Merge
    7. Merge Join
    8. Multicast
    9. Sort
    10. Union All

    ไว้จะลงรายละเอียดในแต่ละ task กันในตอนต่อๆไปนะครับ สำหรับตอนที่ 1 นี้ก็ขอจบไว้เท่านี้ก่อนครับ

     

     

  • เกร็ดความรู้ประกอบการกู้หรือย้ายฐานข้อมูล SQL Server

    Count record แต่ละ tables ใน database

    SELECT T.name AS [TABLE NAME], I.row_count AS [ROWCOUNT]
    FROM sys.tables AS T
    INNER JOIN
    sys.dm_db_partition_stats AS I
    ON T.object_id = I.object_id AND I.index_id < 2
    ORDER BY I.row_count DESC

    หา Trigger ทั้งหมดในทุกๆ tables ใน Database

    SELECT [so].[name] AS [trigger_name], USER_NAME([so].[uid]) AS [trigger_owner],
    USER_NAME([so2].[uid]) AS [table_schema], OBJECT_NAME([so].[parent_obj]) AS [table_name],
    OBJECTPROPERTY( [so].[id], ‘ExecIsUpdateTrigger’) AS [isupdate],
    OBJECTPROPERTY( [so].[id], ‘ExecIsDeleteTrigger’) AS [isdelete],
    OBJECTPROPERTY( [so].[id], ‘ExecIsInsertTrigger’) AS [isinsert],
    OBJECTPROPERTY( [so].[id], ‘ExecIsAfterTrigger’) AS [isafter],
    OBJECTPROPERTY( [so].[id], ‘ExecIsInsteadOfTrigger’) AS [isinsteadof],
    OBJECTPROPERTY([so].[id], ‘ExecIsTriggerDisabled’) AS [disabled]
    FROM sysobjects AS [so]
    INNER JOIN sysobjects AS so2 ON so.parent_obj = so2.Id WHERE [so].[type] = ‘TR’

    ค้นหาข้อความในทุกๆ Stored Procedure ใน Database

    SELECT DISTINCT obj.name AS Object_Name,obj.type_desc
    FROM sys.sql_modules sm INNER JOIN sys.objects obj ON
    sm.object_id=obj.object_id
    WHERE sm.definition Like ‘%xxx%’

    วิธีการ Rebuild Full-text Catalogs of Database

    Use Management Studio
    1. In Object Explorer, expand the server, expand Databases, and then expand the
    database that contains the full-text catalogs that you want to rebuild.
    2. Expand Storage, and then right-click Full Text Catalogs.
    3. Select Rebuild All.
    4. To the question, Do you want to delete all full-text catalogs and rebuild them?,
    click OK.
    5. In the Rebuild All Full-Text Catalogs dialog box, click Close.

    rebuild all index in Database

    use DatabaseName;
    DECLARE @TableName varchar(255)
    DECLARE TableCursor CURSOR FOR
    SELECT table_name FROM information_schema.tables
    WHERE table_type = ‘base table’
    OPEN TableCursor
    FETCH NEXT FROM TableCursor INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    DBCC DBREINDEX(@TableName,’ ‘,90)
    FETCH NEXT FROM TableCursor INTO @TableName
    END
    CLOSE TableCursor
    DEALLOCATE TableCursor

    ค้นหา tables ทั้งหมดใน Database ที่มี Identity Column

    select o.name,’set identity_insert [‘+s.name+’].[‘+o.name+’] ON;’ as ION,’set identity_insert
    [‘+s.name+’].[‘+o.name+’] OFF;’ as IOff
    from sys.objects o
    inner join sys.schemas s on s.schema_id=o.schema_id
    where o.[type]=’U‘ and
    exists(select 1 from sys.columns where object_id=o.object_id and is_identity=1)
    order by o.name

    ตัอย่างการ insert table ที่มี Identity Column

    set identity_insert [dbo].[AGroup] ON;
    insert into [xDB].[dbo].[AGroup] ([AGroupID],[AGroupName])
    select [AGroupID],[AGroupName]
    from [aDB].[dbo].[AGroup]
    order by AGroupID;
    set identity_insert [dbo].[AGroup] OFF;

    Compare two table data

    แบบที่ 1
    select * from zlog1
    except
    select * from zlog
    แบบที่ 2
    select * from
    ( select checksum(*) as chk, id as k from zlog1) as t1
    left join
    ( select checksum(*) as chk, id as k from zlog) as t2 on t1.k = t2.k
    where t1.chk <> t2.chk

    ## เสนอแนะ ถ้ามีการกู้หรือย้ายฐานข้อมูลโดยที่ตัวเดิมยังเปิดใช้งานอยู่ สิ่งที่ควรทำคือให้สร้าง User ขึ้นมาใหม่ที่มีสิทธิใช้งานเฉพาะ Database ตัวใหม่เท่านั้น เพื่อป้องกันการเรียกใช้งานฐานข้อมูลทั้งสองที่โดยที่เราอาจจะไม่รู้ซึ่งจะทำให้ข้อมูลในฐานข้อมูลมั่วมากจนอาจจะเกินเยียวยา โดยมากจะเกิดกับ การเขียน code Stored Procedure ที่มีการระบุชื่อ Database ไว้ใน Stored Procedure

  • การกู้ Suspect Database ของ SQL Server

    Recovery SQL Server Suspect Database

    สาเหตุของ Suspect Mode

    การที่ฐานข้อมูล SQL Server เข้าสู่ Mode Suspect นั้นมีได้จากหลายสาเหตุ ดังนี้

    • Hardware เกิดความเสียหาย
    • มีการปิด (Shutdown) ฐานข้อมูลที่ไม่เหมาะสม คือปิดโดยที่ยังมีกระบวนการทำงานยังไม่เสร็จสมบูรณ์ หรือมีบาง Transaction ค้างอยู่
    • เกิดความเสียหายกับ Database Files (*.mdf,*.log)
    • SQL Server ไม่พบ Device ที่เก็บ Files
    • SQL Server ไม่พบ Database Files
    • Database Resource ถูกใช้งานอยู่โดย Operation System
    • ไม่มีพื้นที่มากพอใน Page space ที่มีการเพิ่ม (Insert) ข้อมูลเข้าไป

    วิธีแก้ปัญหา

    แต่ต้องอยู่บนพื้นฐานที่ไม่มีอะไรผิดปกติที่เกิดจาก Devices หรือ ตัว Database Files จึงจะ สามารถ Recovery โดยวิธีดังนี้

    EXEC sp_resetstatus [YourDatabase];
    ALTER DATABASE [YourDatabase] SET EMERGENCY
    DBCC checkdb([YourDatabase])
    ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DBCC CheckDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)
    ALTER DATABASE [YourDatabase] SET MULTI_USER

    อธิบาย

    • EXEC sp_resetstatus [YourDatabase];

    เป็นคำสั่งทำการปิด suspect mode เมื่อทำแล้วก็ต้อง Stop และ Restart SQL Server ด้วย

    • ALTER DATABASE [YourDatabase] SET EMERGENCY

    เป็นคำสั่งให้ Database เข้าสู่ READ_ONLY MODE และจำกัดการเข้าถึงให้เข้าได้เฉพาะ SysAdmin Account เท่านั้น

    • DBCC checkdb([YourDatabase])

    CheckDB จะตรวจสอบการจัดสรรทรัพยากรสำหรับทุกๆ Object และตรวจสอบความสมบูรณ์ของโครงสร้างฐานข้อมูลทั้งหมด

    • ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    กำหนดให้สามารถเข้าใช้งานฐานข้อมูลได้เพียง User เดียวเท่านั้น

    • DBCC CheckDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)

    จะมีการทำงานหลายขั้นตอนซึ่งแรกสุดจะเป็นการตรวจสอบทรัพยากรต่างๆและมีการตรวจสอบโครงสร้างของฐานข้อมูลจะมีการ Run คำสั่ง DBCC CheckAlloc, DBCC CheckTable, DBCC CheckCatalog ทั้งฐานข้อมูล มีการตรวจสอบข้อมูลของแต่ละ Indexed View ตรวจสอบ Link-Level Consistency ระหว่าง table metadata และ file system directories ตรวจสอบ Service Broker Data ในฐานข้อมูลทั้งหมด

    ส่วน Option REPAIR_ALLOW_DATA_LOSS นั้นจะมีการพยายามซ้อมแซ่มฐานข้อมูลในส่วนที่เสียไปตามรายการที่ได้จากการ CheckDB และเป็นการอนุญาตให้ข้อมูลสามารถศูนย์หายได้บาง

    • ALTERDATABASE [YourDatabase] SET MULTI_USER

    เป็นการ set database ให้กลับมาใช้งานตามปกติ

    หลังจากขั้นตอนเหล่านี้แล้ว ผมจะทำการ Shutdown Server แล้วเปิดขึ้นมาใหม่แล้วลองใช้คำสั่ง DBCC CheckDB() ตรวจสอบอีกครั้งเพื่อความแน่ใจ

  • T-SQL นับจำนวน record ที่เกิดจากการสั่ง execute Dynamic Query

    ตัวอย่างไม่ต้องอธิบายมาก

    DECLARE @str_SQL NVARCHAR(100), @rowcount INT –กำหนดตัวแปร
    SET @str_SQL = ‘select * from sysobjects’ — set ค่าให้ตัวแปร
    EXEC sp_executesql @str_SQL — สั่ง execute dynamic query
    SELECT @rowcount = @@ROWCOUNT — get ค่าที่จำนวน record ที่เกิดผลกระทบจากการ execute dynamic query
    PRINT ‘@rowcount = ‘ + CAST(@rowcount AS VARCHAR(4)) — แสดงผลออกหน้าจอ