보미네

스레드 구조 차이
Database Mirroring: 데이터베이스마다 독립적인 전용 워커 스레드를 사용하여 데이터 전송, 로그 적용, 세션 관리 등을 수행한다.
AlwaysOn AG: 모든 데이터베이스가 HADR Thread Pool(hadr_thread_pool_worker)을 공유하여 스레드를 사용한다. 따라서 데이터베이스 개수가 증가하더라도 스레드 수가 1:1로 비례 증가하지 않는다.

DB 수 증가에 따른 영향
Mirroring은 데이터베이스 수가 늘어날수록 필요한 워커 스레드 수도 선형적으로 증가한다. 스레드 수가 많아지면 SQL Server의 max worker threads 한계에 영향을 줄 수 있으며, CPU 스케줄링과 병렬 처리에도 부하가 커진다.

메모리 사용량
SQL Server에서 워커 스레드 하나당 약 2MB의 스택 메모리가 예약된다. 이 메모리는 max server memory 설정에 포함되므로 buffer pool 이나 plan cache 등 메모리 용량 산정 시 고려해야 한다. 1000개의 max worker thread를 설정하면 max server mermory 가 4GB 이라면 2GB 가 thread 메모리로 사용될 수 있다. (최대값으로 가정 하는게 좋고 실제 동작을 보면, worker thread 개수는 자동으로 올라가고 내려간다.)

결론 및 설계 포인트
Mirroring 환경에서는 DB 개수가 많을수록 워커 스레드 증가 → 메모리 사용량 증가 → max server memory를 설정할 때 이 부분을 감안해야 한다. AG 환경은 Thread Pool을 사용하므로 DB 개수 증가의 영향이 상대적으로 적다. 따라서 이중화 솔루션 설계 시 스레드 구조와 메모리 소비 패턴을 반영한 용량 계획이 필요하다.

추가 고려사항

* 데이터베이스 미러링(DM)은 DB당 단일 스레드만 쓰지 않음. 일반적으로 DB당 약 2개 이상의 스레드를 소비하고, 미러(secondary) 쪽에서는 (Enterprise의 경우) 병렬 REDO(roll‑forward) 스레드가 추가로 할당될 수 있음
* 스레드 1개당 스택 메모리(64비트 환경) 는 대략 2 MB 이므로(스택/MTL 관련), 스레드 증가가 메모리(및 max server memory 계산)에 직접 영향을 줌 
* DM 미러에서의 병렬 REDO(Enterprise) 는 “CPU 4개당 1개의 REDO 스레드(대략)” 로 동작하는 식으로 최적화 됨

스크린샷1 (142개 미러링 데이터베이스가 있을때 Primary)

스크린샷2 (142개 미러링 데이터베이스가 있을때 Secondary)

결론?

* DM에서 미러링 대상서버의 max workerthread 숫자 조절 필요 

* 제발 데이터베이스를 너무 많이 만들지 말자 (살려주세요 이건 아니잖아요....)

추가 전문 아티클 

https://www.dbi-services.com/blog/sql-server-alwayson-number-of-databases-and-the-hadr-worker-pool/

Often, when I’m in charge to implement an SQL Server AlwaysOn infrastructure with availability groups, customers ask me if it exists some best practices and recommendations about the maximum number of databases in an availability group planning. In fact, we have to keep in mind the relation between databases and the SQL Server worker pool in this architecture. Remember that the SQL Server AlwaysOn and availability group feature is an extension of the SQL Server mirroring and we retrieve the same concept of worker thread usage with a different architecture: Mirroring feature dedicates worker threads for each database compared to SQL Server AlwaysOn that uses requests queue and worker pool (aka HADR pool). Let’s see how the HADR worker pool behaves and its tight relationship with the databases.

First of all, let’s take a look at the Microsoft documentation here – “Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups – and let’s have a focus on the “Thread Usage by Availability Groups” section. After reading the content we can notice that the number of databases is an important part of the minimum hadr pool size calculation among others factors. Others factors are the following:

• Number of replicas
• Log capture thread
• Log send queue thread
• Message handler thread

Another point to keep in mind is that the size of the HADR worker pool is capped by the “max worker thread” parameter as shown below:

Hadr pool size max = max worker threads – 40

I also encourage you to read the excellent article of Bob Dorr that provides a formula to estimate the minimum size of the HADR worker pool. Once again we can notice that the number of databases is still relevant here. As explained by Bob Dorr, the max databases parameter concerns only the “active” databases where “active”.

Let’s perform a quick calculation of the min pool size in the worst case (all databases are active) required on the primary replica with several configurations based on the Bob Dorr’s formula:

Min Pool Size = Max Databases * (Log Capture Thread + (Log Send Thread * Nb secondary replicas)) + Message Handler Thread

Then let me show you a more real picture with my lab that includes a SQL Server AlwaysOn 2014 (64 bits) with one availability group and two replicas (basically one primary and one secondary). Each replica is configured with 4 VCPU and no affinity mask, so all the CPU are visible from each replica.

Next, according to the Microsoft documentation here, we will be capped by the “max worker threads” parameters equal to 512 in my case. Thus, the maximum size of the HADR worker pool size will be 512 – 40 = 472.

Now let’s play with different scenarios:

1- First scenario (no availability groups)

The first scenario includes an environment with no availability groups. The global number of worker threads is as following:

This view is not perfect in our case because it includes all the worker threads of the SQL Server instance (hadr worker threads are included to this number). But we will use it as a starting point because there is no activity on my lab and we can admit that the active_workers_count column value will be relatively close than the number of HADR worker threads.

2 – Second scenario (availability group with 100 idle databases)

The second scenario consists in adding 100 databases to my newly availability group but there is no activity. Let’s have a look at the global number of worker threads:

The number of worker threads has increased but this is not a big deal here because the availability databases are not very active. At this point I want to introduce another way to get the number of hadr worker threads by using the extended events and the hadr_thread_pool_worker_start event:

The extended event session I used during my test …

… the data extraction script:

… and the result:

3- Third scenario (availability group with 200 idle databases)

Let’s add 100 additional databases and let’s have a look at the sys.dm_os_schedulers DMV:

Here, the result from the extended event session:

As the previous scenario, the number of active worker threads is still low because there is no activity on the availability databases.

4- Fourth scenario (availability group with 300 “active” databases)

In this scenario, let’s add 100 additional databases (for a total of 300 databases) but this time we will simulate a workload that will perform randomly on different availability databases.

Let’s take a look at the extended event session to see the number of active workers:

As you can notice, the story is not the same. A big increase of the number of HADR active workers (roughly 258) can be noticed here. However, if I refer to Bob Dorr’s formula in his article, we may be disappointed by the number of threads that we get from the extended event session that is theoretically lower than we can expect for a total number of 300 databases (300 *2 + 1 = 601). At this point, my guess is that my workload and my lab configuration didn’t allow to reach out the HADR worker thread starvation scenario but in fact, this is not so important here because the goal was simply to highlight the difference between the number of active worker threads between an idle and active situation.

5- Fifth scenario : case of a worker threads exhaustion scenario

In the previous scenarios, we saw that increasing the number of databases can have an impact on an availability group. From my point of view, facing this situation is probably the worst scenario but we have to take into account in your design regarding your context.

In this scenario, I will voluntary increase the number of databases up to 500 to reach out more quickly the number of allowed active worker threads.

Here’s a picture of such scenario from the extended event session:

We are in a situation of HADR worker pool starvation (active workers = work limit = 472). Moreover, the work_start_success column value (= false) indicates the SQL Server’s inability to start a new HADR worker.

After a big coffee break, here what I saw from the SQL Server error log. This message indicates an excessive worker thread contention after 15 minutes.

If you see this error message in your availability groups environment, it’s time to check the number of availability databases. You may face this situation more often than you may think. I can see a lot of customer situations where availability groups are tied to consolidated databases environments. In such situation, adding a database can become a semi-automatic process and the number of HADR worker threads can be out quickly of the control.

What is the solution in this case? Add more worker threads? More processors? Reduce the number of databases? I guess you know what I will answer: it depends on your context.

So, in this blog post we’ve seen that the number of databases in an availability group may be a problem regarding your infrastructure and the number of databases hosted in your availability group. Therefore, this topic can be probably one of your concern if you plan to consolidate a lot of databases in your availability groups. In this case, I would probably recommend to my customers to include the hadr worker pool in his monitoring tool.

Hope this helps.

By David Barbarin

메시지 3201, 수준 16, 상태 2, 줄 1
Cannot open backup device '\\10.0.1.11\backup\a.bak'. Operating system error 1272(You can't access this shared folder because your organization's security policies block unauthenticated guest access. These policies help protect your PC from unsafe or malicious devices on the network.).
메시지 3013, 수준 16, 상태 1, 줄 1
RESTORE HEADERONLY is terminating abnormally.

Windows 2019 이상에서는 아래 옵션이 적용되어 있어야 한다. 

Set-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Services\LanmanWorkstation\Parameters" -Name AllowInsecureGuestAuth -Value 1 -Type DWord
gpupdate /force

아래와 같이 Distributed Transaction을 사용하려면 ?

EXEC master.dbo.sp_addlinkedserver @server = N'a', @srvproduct=N'',@provider = 'sqlncli',@datasrc = '10.10.60.17,1433'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'a',@useself=N'False',@locallogin=NULL,@rmtuser=N'admin',@rmtpassword='adminP@ss0rd'




SET XACT_ABORT ON
GO
BEGIN DISTRIBUTED TRANSACTION
-- 서버A 작업
INSERT INTO bdb.dbo.tbly (idx) VALUES (1);
-- 서버B 작업
INSERT INTO a.adb.dbo.tblx (idx) VALUES (2);
COMMIT TRANSACTION

아래 설정이 양쪽에 동일하게 잘 설정 되어야 동작함

service process

C:\>sc query msdtc

hosts

admin notepad c:\windows\system32\drivers\etc\hosts

dcomcnfg

net stop msdtc
net start msdtc

port