Category: SQL Server

How to: SQL Server Failover Cluster

grianggrai.n

November 7, 2016
สิ่งที่ต้องมี
- Software
  - Windows Server 2012 R2
  - Microsoft SQL Server 2016
  - รายละเอียดเพิ่มเติม Software Requirements
- 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/
November 7, 2016

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

theerayuth.p

July 15, 2016

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

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

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

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

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

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

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

ที่มาของภาพ

เตรียมข้อมูลให้พร้อมที่จะ 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 ยอดนิยมมีดังนี้

Aggregate
Conditional Split
Data Conversion
Derived Column
Lookup
Merge
Merge Join
Multicast
Sort
Union All

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

July 15, 2016

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

theerayuth.p

July 14, 2015

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

July 14, 2015

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

theerayuth.p

July 14, 2015

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() ตรวจสอบอีกครั้งเพื่อความแน่ใจ