Cấu hình MS SQL Replication - P1

Cấu hình MS SQL Replication – 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 MS SQL Replication.

Ứng dụng thực tế của tính năng này 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.

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).

Bước 2: Tạo dữ liệu test 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 ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:		admin-- Create date: -- Description:	-- =============================================CREATE PROCEDURE [dbo].[SP_InsertDimAccount]	-- Add the parameters for the stored procedure here	ASBEGIN	-- 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  	ENDGO

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

USE [msdb]GO/****** Object:  Job [InsertDimAccount]    Script Date: 12/15/2017 8:35:58 AM ******/BEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @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)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @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 OUTPUTIF (@@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=0IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @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 QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback:    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave: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…

Next tiếp

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

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

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.

Bấm next

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

6S5NY6hee6cCD8z

 

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

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

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

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..

Bấ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

Chọn database cần publish

Chọn Transactional publication:

 

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.

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

 

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:

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

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ý.

Chọn Push subcriptions

 

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

 

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.

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

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

Chọn run continuously => next

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

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.

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

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:

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

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:

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

* Chạy Logreader Agent:

Tại Publisher => right click =>

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

Đợ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

 

Kết luận

Trên đây là  các bước cấu hình Transaction 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.