All our existing servers are in SQL 2008 and I am in the process of upgrading to SQL 2014 and as well moving SAN to different SAN storage.
As part of it I have to upgrade my distributor.
We got about 7 production database servers and data from all that servers are replicated using a separate distributor to multiple subscribers. Totally got about 78 publications going to multiple subscribers for various reasons!!! Don’t ask why so many. It is what I inherited as part of new job.
So I started with creating a new VM with Windows 2012 and SQL 2014, made that as the distributor and started changing the distributor of each publisher to the new one and eventually once everything is moved the old distributor can be dropped.
We setup replication from first 2 servers (about 30 publications or assume x publications) to multiple subscribers and things worked fine. It is all Transaction replication with pull subscription. NO delays in replication things working great.
The moment I add a one more publication or a new article the new transaction replication goes into “between retries” or sql agent jobs complains about no worker threads to start. I tested by deleting one of the existing publication and the new one works fine. So I digged around and read that the number of worker threads on the server level and subsystem level needs to be high enough to run mulitiple SQL agents jobs continously.
So I updated the worker threads on server level setting, restarted server, and that didn’t help. I found the subsystem worker thread information in msdb.dbo.subsystems and updated the worker threads as defined here http://blogs.msdn.com/b/karthick_pk/archive/2011/02/01/sql-agent-maxworkerthreads-and-agent-subsystem.aspx and that doesn’t seem to help.
I also found about Desktop Heap issue and updated the Shared allocation of member in registry as specified @ https://support.microsoft.com/en-us/kb/949296
My distributor is a VM, 2 processors and 8GB ram on it. It is same as the existing SQL 2008 box. I do see that the existing 2008 has a high desktop heap and as well subsystem processor count updated. One issue in updating msdb.dbo.syssubsystems table is it gets reset whenever SQL server restarts by this proc: dbo.sp_verify_subsystems. So I tried adding a startup proc that updates the worker threads and that doesn’t help. This issue has been discussed @ SQL Server 2012 Replication Subsystem Increase Number of Working Threads
One thing that seems to work in the new server is, after I setup more replication if I restart the server then it works. But, the moment I add a new replication the new one doesn’t work. In my older server I could as much new replication I and want and doesn't require any restarts. So restart doesn’t sound right.
Any help is appreciated. Should I update the worker threads or other settings for Agent jobs? or Do I have to rely on restarts?