Thứ Sáu, 10 tháng 7, 2020

Cấu hình replication sql server - Phần 1

Cấu hình replication sql server - Transactions Replication.


Microsoft SQL server có một tính năng rất tuyệt vời là Replication, Có rất nhiều ứng dụng với tính năng này: Sao lưu, đồng bộ dữ liệu trên nhiều máy chủ, chia tải hệ thống... Trong bài viết này tôi đề cập đến tính năng Transactions Replication và cách Cấu hình Cấu hình sql server replication.

sql replication là gì? bạn có thể tham khảo bài viết này.

Ứng dụng thực tế của tính năng Replication là chia bớt kết nối từ hệ thống webserver tới DB server nhằm mục đính giảm tải cho server chính. Đặc biệt trong các hệ thống lớn, cần chạy report một lần hàng triệu bản ghi.

Chuẩn bị:

Trong thực tế sử dụng, để hiệu quả thì thường dùng tổ hợp 3 cụm server:
  • PUBLISHER: Có thể coi là các Main DB, nới dữ liệu chính được lưu ở đây.

  • DISTRIBUTOR: Máy chủ phân phối, Máy chủ này chứa database distribution, lưu lại tất cả các lịch sử phân phối của hệ thống.

  • SUBCRIBER: Các máy chủ tiếp nhận dữ liệu từ PUBLISHER, thường chạy report báo cáo trên các DB này.

Note: Nhiều publisher và subcriber có thể chia sẻ 1 Distributor.

Bài lab sử dụng 3 máy ảo thiết đặt IP như sau:

File: c:\windows\system32\drivers\etc\hosts

10.10.10.221 WIN-A8HSQ05U569 #distributor

10.10.10.252 WIN-6KH8AA92HTS #Main Production => Publisher

10.10.10.41 WIN-SD9ABIHM6FA #Backup/Report server => Subcriber

Cần mở port SQL cho các IP trong danh sách

Chú ý: Các thao tác cần đang nhập sử dụng COMPUTER NAME hoặc DOMAIN NAME của server, do vậy cần ping thông các server bằng COMPUTER NAME trước khi bắt đầu. Trong trường hợp không thể ping bằng COMPUTER NAME mà chỉ ping được qua IP thì có thể đặt lại trong file HOST như trên.

Bước 1: tạo Database test replication sql server.


Ta sử dụng bộ database tiêu chuẩn của Microsoft trong các ví dụ trên technet để thực hiện. tải tại: https://www.microsoft.com/en-us/download/details.aspx?id=49502

Trong SMMS, từ khung Object Explore, Right click vào database, chọn Restore

Sau khi restore xong, right click vào db và chọn Properties, trong mục file và set quyền owner cho db (rất quan trọng).

restore database on sql server

Bước 2: Tạo dữ liệu test sql replication server tự động, liên tục.


Để đơn giản ta sẽ thiết kế 1 job nhỏ sử dụng StoredProcedure (SP) để tự động chèn 100 bản ghi ngẫu nhiên mỗi 10 giây vào table DimAccount. Như vậy tính ra mỗi giây sẽ là 10 transactions, một con số không hề nhỏ, tương đương 1 hệ thống ngân hàng rồi.

Script tạo SP :
USE [AdventureworksDW2016CTP3]
GO

/****** Object: StoredProcedure [dbo].[SP_InsertDimAccount] Script Date: 12/15/2017 8:19:34 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: admin
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[SP_InsertDimAccount]
-- Add the parameters for the stored procedure here

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
DECLARE @Count int = 0
WHILE @Count < 100
BEGIN
INSERT INTO [dbo].[DimAccount]
([ParentAccountKey]
,[AccountCodeAlternateKey]
,[ParentAccountCodeAlternateKey]
,[AccountDescription]
,[AccountType]
,[Operator]
,[ValueType])
VALUES
(17
,1220
,1200
,'Buildings & Improvements'
,'Assets'
,'+'
,'Currency'
)


SET @Count = @Count + 1
END


END
GO

Sau khi có SP, ta tạo  1 job để chạy định kỹ 10s 1 lần gọi SP:

create a job
USE [msdb]
GO

/****** Object: Job [InsertDimAccount] Script Date: 12/15/2017 8:35:58 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 12/15/2017 8:35:58 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'InsertDimAccount',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'WIN-6KH8AA92HTS\Administrator', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [1] Script Date: 12/15/2017 8:35:58 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'
EXEC [dbo].[SP_InsertDimAccount]',
@database_name=N'AdventureworksDW2016CTP3',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'1',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=2,
@freq_subday_interval=10,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20171215,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'be16bfc4-64d4-465a-94fe-75050a4fc64f'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Để kiểm tra dữ liệu được chèn vào Table, ta đếm số record của table:
select count (*) from dbo.dimaccount;



Vậy là ta đã giả lập 1 hệ thống với 860.000 bản ghi 1 ngày.

 

Bước 3: Tạo các User logins cần thiết.


Trên Publisher:

loginID: topublisher

Password: 53gyYrd589E7bh2

trên server subcripber:

LoginID: tosubcribber

Password: Y7sBKJ9gQ2AbQtD

Gán quyền sysadmin cho 2 user này.

 

Bước 4: Cấu hình distributer:


Trên server distributer: Right click vào Mục repication chọn Config Distribution...

Configure Distribution Wizard 1

Configure Distribution Wizard 2

Next tiếp

Chọn thư mục chứa file snapshot: C:\snapshotdata

Configure Distribution Wizard 3Configure Distribution Wizard 4

Chọn tên cho database distribution và đường dẫn nới chứa file database replication

Configure Distribution Wizard 5

Bấm add => Add SQL Server Publisher để chọn Publisher dược phép sử dụng distributor này.

Bạn cần nhập thông tin đăng nhập lúc nãy tạo để tiếp tục.

Bấm connect.

Login to MS SQL using SSMSConfigure Distribution Wizard - Chon Publishers

Bấm next

Nhập mật khẩu cho distribution: (distributor_admin)

6S5NY6hee6cCD8z

Configure Distribution Wizard - Distributor password

Configure Distribution Wizard Before Finish

 

Bấm finish để hoàn thành việc cấu hình distributor.

Configure Distribution Wizard - Finish

Kiểm tra lại 1 lượt trong OB:

Check Database

Ta thấy có links server , Distribution Databases, Distributer account đã được tạo.

MS SQL link server

Trên server publisher, đã có links server được tạo

Bước 5: Cấu hình publisher.


Vào Publisher server, Trong mục Local Publication chọn New Publication..

Cau hinh Publisher

New Publication Wizard 1New Publication Wizard 2Bấm vào Add...  để thêm distributor đã tạo lúc trước.

thông tin đăng nhập vào distributor server:

distributor_admin

6S5NY6hee6cCD8z

New Publication Wizard 3

New Publication Wizard 4

Chọn database cần publish


New Publication Wizard 5Chọn Transactional publication:

New Publication Wizard 6

 

Chọn các mục muốn đồng bộ Ở đây tôi chỉ đồng bộ DimAccount nên chọn table DimAccount. Chú ý chỉ các Table chứa Primary Key mới có thể sử dụng Transaction Replication.

New Publication Wizard 7

Nếu filter row thì add thêm filter.

New Publication Wizard 8

 

New Publication Wizard 9

Bấm vào mục Security Setting để thiết lập thông số làm việc co snapshot agent và logreader agent:

New Publication Wizard - Agent Security Config

New Publication Wizard - 10

New Publication Wizard 11

New Publication Wizard 12

Bấm finish để hoàn tất.

Bước 6: Cấu hình tạo Subcriber.


Tại server Publisher, bấm chuột phải vào publiser vừa tạo và chọn New Subcribtions

New Subcriber Wizard

Một Wizard hiện ra để cấu hình Subriber. Cũng tương tự như tạo publisher, ta cần chọn Đúng Publiser cần xử lý.

new Subcription Wizard 1

new Subcription Wizard 2

Tới đây Chọn Push subcriptions

new Subcription Wizard 3

 

Chọn add subcripber và nhập thông tin đăng nhập tạo lúc trước tới server subcription.

 

new Subcription Wizard 4

Chọn Newdatabase nếu chưa có database hoặc chọn database tìm thấy trong danh sách.


Trong bài viết tôi chưa có database nên sẽ chọn tạo database mới.

new Subcription Wizard 5new Subcription Wizard 6

Sau khi bấm next, hiện ra mục cấu hình cho agent kết nối với subcriber. Bấm vào dấu.... như trong hình để cài đặt

new Subcription Wizard 7

Có 3 mục chọn như trên, phần connect to the Subcriber thì nhập thông tin account đã tạo lúc trước.

Bấm next để tiếp tục

new Subcription Wizard 8

new Subcription Wizard 9

Chọn run continuously => next

new Subcription Wizard 10

Tick chọn Initialize và At first synchronization => Next

new Subcription Wizard 11

Bấm next => finish, quá trình tạo hoàn tất => Close.

Kiểm tra lại đã thấy kết sucriber được tạo thành công.

new Subcription Wizard 12

Chuyển sang server distribution, vào mục SQL agent

new Subcription Wizard 13

Các job đã được tạo để thực hiện replication. Quay trở lại Publisher tiến hành chạy các agent:
* Snaphot agent:

View Snapshot Agent Status

Right click lên publisher chọn View Snaphot Agent Status

Nếu được yêu cầu thìNhập thông tin kết nối đến Distribution server (vì agent chạy trên đó). Bấm start agent

Snapshot Agent Status

Agent chỉ chạy 1 lần đầu. Nếu cần tạo lại snapshot thì ấn start 1 lần nữa. Sau đó đóng cửa sổ lại

Qua server distributor, kiểm tra thấy thư mục snapshot có dữ liệu:

Snapshot Agent Data - UNC check

Vậy là snapshot đã tạo thành công.

* Chạy Logreader Agent:

Tại Publisher => right click =>

Logreader Agent status

Log Reader Agent Status

Nếu agent chưa chạy bấm start, nếu agent tự động chạy và thành công sẽ có thông báo số transaction đẩy sang subcriber.

Kiểm tra trên server subcription ta thấy dữ liệu đã có:



Đếm số bản ghi:
/****** Script for SelectTopNRows command from SSMS  ******/

SELECT count (*) from dbo.dimaccount

T-SQL Querry sample

Đợi 10s sau chạy lại kết quả tăng lên chứng tỏ transactions đã được đẩy sang database.

Có thể kiểm tra băng thông card ethernet thấy dữ liệu sang rất đều: 6gói/60sec, chứng tỏ Cấu hình MS SQL Replication đã hoạt động tốt

 

Check Bandwidth Replication

Kết luận


Trên đây là  các bước cấu hình sql replication. Trong các bài viết sau tôi sẽ giới thiệu một số vấn đề liên quan đến Ms SQL Server như:
- Vận hành hệ thống SQL server

- Xử lý các trường hợp liên quan đến Database dung lượng lớn.

- Xử lý các vấn dề liên quan đến Replication.

Chúc các bạn thành công.

Xem thêm:

Fault tolerance là gì

các lệnh trong powershell liên quan đến mạng
Link: https://toiyeuit.com/cau-hinh-replication-sql-server-p1/

Không có nhận xét nào:

Đăng nhận xét