March 13, 2014 at 9:10 am
Hi Experts,
We have 2-node failover cluster in data center A for PROD. Recently we built another 2-node failover cluster in data center B for DR purpose. Our windows team mistakenly built DR nodes on a separate windows cluster, therefore we cannot make use of Always-On AG as it requires all nodes must belong to the same windows cluster. We are then implementing database mirroring between PROD and DR, but we now running into problem: We have 300 databases to mirror. While mirroring one database at a time and up to half way, we notice the current_workers_count had already exceeded max_workers_count. We have increased the "Maximum worker threads" from default 640 to 2000 on DR. So far, the PROD (12-core/264GB mem) and DR (12-core/264GB mem) still perform ok.
My questions are:
- How big is the risk of having "Maximum worker threads" way higher than the default value?
- If we remove database mirroring and rebuild the DR nodes to make them part of the same PROD windows cluster so we can make use Always-on AG, would we run into this "Maximum worker threads" issue again?
I need your expert advise to weigh my options:
- Continue database mirroring for the rest of databases and risking perf due to "Maximum worker threads"
- Get it of db mirroring, rebuild DR, make use of Always-On AG
- Leave DR the way they are, remove db mirroring and implement transactional replication
BTW, I don't have a test environment to test these options out.
Thanks much for any opinion
Thanh Nguyen
March 13, 2014 at 9:23 am
Database mirroring will be removed in a future version, for this reason it would make sense not to use an exiting technology, but use the option that has effectively replaced it.
Not aware of anyone raising an issue with worker threads in AO groups
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 13, 2014 at 11:03 am
Thanks for quick response, Perry.
From what I read Always-On AG is a combination of database mirroring and failover cluster. So I wasn't sure if they still utilize worker threads the same way they use in db mirroring. From article below, I would need at least 600 worker threads on principal and at least 1500 worker threads on mirror site to accommodate 300 dbs.
http://blogs.msdn.com/b/docast/archive/2013/02/19/useful-information-on-db-mirroring.aspx
Thanh Nguyen
March 13, 2014 at 11:35 am
My questions are:
- How big is the risk of having "Maximum worker threads" way higher than the default value?
Mirroring:
As per your Scenario, assuming 64-bit system, Max Worker Threads = 640.
Max No of mirror threads required for 300 Dbs = 1500
Assuming all DBs are busy, taking into account other DB tasks, Max no of threads needed = 2140.
For 64- bit systems , Max recommended value is 2048.
Actually when SQL Server gets started, it reserves some amount of memory for each thread. As the # of threads increase, this memory also increases and it comes from the MemToLeave portion. So this impacts the Max Memory setting on the server.
Changing this value needs to be well tested as it may lead to SQL Server stability issues.
Coming to the # of DBs, I don't think that's a good decision to DB mirror 300 DBs. It may lead to performance\stability issues and makes administration a nightmare too.
- If we remove database mirroring and rebuild the DR nodes to make them part of the same PROD windows cluster so we can make use Always-on AG, would we run into this "Maximum worker threads" issue again?
The way in which threads are handled is different for DB Mirroring (DBM) and Always ON. DBM uses a dedicated thread per DB whereas Always ON uses a shared worker pool (HadrThreadPool) using a request queue.
For AlwaysON :
Min Pool Size ~= (Max No of Dbs *2) + 1 = 601
HadrThreadPool Cap = (Max Worker Threads - 40) = 2008
I would recommend AlwaysON but it has to well tested before implementing in Production.
Again Transaction Replication for 300 DBs would become very complex. And that too Replication is not a DR solution.
--
SQLBuddy
March 13, 2014 at 12:57 pm
Thanks so much for your clear and concise reply, SQLBuddy. I appreciate so much.
March 13, 2014 at 2:04 pm
You are welcome, Thanh 🙂
--
SQLBuddy
March 5, 2015 at 11:02 am
Perry Whittle (3/13/2014)
Database mirroring will be removed in a future version, for this reason it would make sense not to use an exiting technology, but use the option that has effectively replaced it.Not aware of anyone raising an issue with worker threads in AO groups
The problems with AVG is that only works with enterprise edition.
Then I continue to use database mirroring in clients with SQL Standard.
Fabrício França Lima
MCITP – Database Administrator
https://www.fabriciolima.net/Blog
March 6, 2015 at 3:48 am
FabricioLimaDBA (3/5/2015)
The problems with AVG is that only works with enterprise edition.Then I continue to use database mirroring in clients with SQL Standard.
Yes, but you're limited to synch only mode and no database snapshots.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 6, 2015 at 5:14 am
Perry Whittle (3/6/2015)
FabricioLimaDBA (3/5/2015)
The problems with AVG is that only works with enterprise edition.Then I continue to use database mirroring in clients with SQL Standard.
Yes, but you're limited to synch only mode and no database snapshots.
I think this is better then nothing to small clients
Fabrício França Lima
MCITP – Database Administrator
https://www.fabriciolima.net/Blog
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply