Is there a limit on Background Workers? Technical or Common Sense...

Question

In Program.cs I have the below method that is checking and the Syncing 5 SQL db's with the central server. Each one is separate from the other so I thought to speed up my program's load time by having them all run at the same time.

Unfortunately it is very flaky working one time then not the next. The local DB is SQLExpress 2005 and the central DB is SQL Server Standard 2005.

Is there a limit on how many connections either of those can have? How about Background Workers, can I only have so many running at once? I am sure there is a MUCH more eloquent way of doing this, I'd love to hear(see) them.

This is how I call this in Main() in Program.cs -->

if(IsSqlAvailable()) SyncNow();

---

internal static void SyncNow()
    {



            #region ConnectDB Merge Sync Background Thread

            BackgroundWorker connectBW = new BackgroundWorker
                                             {
                                                 WorkerReportsProgress = false,
                                                 WorkerSupportsCancellation = true
                                             };
            connectBW.DoWork += new DoWorkEventHandler(connectBW_DoWork);
            if (connectBW.IsBusy != true)
                connectBW.RunWorkerAsync();

            #endregion

            #region aspnetDB Merge Sync Background Thread

            BackgroundWorker aspBW = new BackgroundWorker
                                         {
                                             WorkerReportsProgress = false,
                                             WorkerSupportsCancellation = true
                                         };
            aspBW.DoWork += new DoWorkEventHandler(aspBW_DoWork);
            if (aspBW.IsBusy != true)
                aspBW.RunWorkerAsync();

            #endregion

            #region MatrixDB Merge Sync Background Thread

            BackgroundWorker matrixBW = new BackgroundWorker
                                            {
                                                WorkerReportsProgress = false,
                                                WorkerSupportsCancellation = true
                                            };
            matrixBW.DoWork += new DoWorkEventHandler(matrixBW_DoWork);
            if (matrixBW.IsBusy != true)
                matrixBW.RunWorkerAsync();

            #endregion



            #region CMODB Merge Sync Background Thread

            BackgroundWorker cmoBW = new BackgroundWorker
                                         {
                                             WorkerReportsProgress = false,
                                             WorkerSupportsCancellation = true
                                         };
            cmoBW.DoWork += new DoWorkEventHandler(cmoBW_DoWork);
            if (cmoBW.IsBusy != true)
                cmoBW.RunWorkerAsync();

            #endregion

            #region MemberCenteredPlanDB Merge Sync Background Thread

            BackgroundWorker mcpBW = new BackgroundWorker
                                         {
                                             WorkerReportsProgress = false,
                                             WorkerSupportsCancellation = true
                                         };
            mcpBW.DoWork += new DoWorkEventHandler(mcpBW_DoWork);
            if (mcpBW.IsBusy != true)
                mcpBW.RunWorkerAsync();

            #endregion

    }

    static void mcpBW_DoWork(object sender, DoWorkEventArgs e)
    {
        BackgroundWorker worker = sender as BackgroundWorker;
        try
        {
            MergeRepl mcpMergeRepl = new MergeRepl(SystemInformation.ComputerName + "\\SQLEXPRESS", "WWCSTAGE", "MemberCenteredPlan", "MemberCenteredPlan", "MemberCenteredPlan");
            mcpMergeRepl.RunDataSync();
            areAllInSync += 1; 
        }
        catch (Exception)
        {
            if (worker != null) worker.CancelAsync();
        }
    }

    static void cmoBW_DoWork(object sender, DoWorkEventArgs e)
    {
        BackgroundWorker worker = sender as BackgroundWorker;
        try
        {
            MergeRepl cmoMergeRepl = new MergeRepl(SystemInformation.ComputerName + "\\SQLEXPRESS", "WWCSTAGE", "CMO", "CMO", "CMO");
            cmoMergeRepl.RunDataSync();
            areAllInSync += 1; 
        }
        catch (Exception)
        {
            if (worker != null) worker.CancelAsync();
        }
    }

    static void connectBW_DoWork(object sender, DoWorkEventArgs e)
    {
        BackgroundWorker worker = sender as BackgroundWorker;
        try
        {
            MergeRepl connectMergeRepl = new MergeRepl(SystemInformation.ComputerName + "\\SQLEXPRESS", "WWCSTAGE", "CONNECT", "Connect", "Connect");
            connectMergeRepl.RunDataSync();
            areAllInSync += 1; 
        }
        catch (Exception)
        {
            if (worker != null) worker.CancelAsync();
        }
    }

    static void matrixBW_DoWork(object sender, DoWorkEventArgs e)
    {
        BackgroundWorker worker = sender as BackgroundWorker;
        try
        {
            MergeRepl matrixMergeRepl = new MergeRepl(SystemInformation.ComputerName + "\\SQLEXPRESS", "WWCSTAGE", "MATRIX", "MATRIX", "MATRIX");
            matrixMergeRepl.RunDataSync();
            areAllInSync += 1; 
        }
        catch (Exception)
        {
            if (worker != null) worker.CancelAsync();
        }
    }

    static void aspBW_DoWork(object sender, DoWorkEventArgs e)
    {
        BackgroundWorker worker = sender as BackgroundWorker;
        try
        {
            MergeRepl aspnetdbMergeRepl = new MergeRepl(SystemInformation.ComputerName + "\\SQLEXPRESS", "WWCSTAGE", "aspnetdb", "aspnetdb", "aspnetdb");
            aspnetdbMergeRepl.RunDataSync();
            areAllInSync += 1; 
        }
        catch (Exception)
        {
            if (worker != null) worker.CancelAsync();
        }

    }

Answer 1

I only use one.

I figure the BackgroundWorker is there to allow me to execute a long-running task and keep the UI responsive.

If I want multiple threads I use the ThreadPool.

Answer 2

You need to be more specific what the 'flakiness' is, how does it manifest. If I understand this correctly, you are trying to drive manually a merge replication with RMO classes, in lack of the Agent support.

One thing to note is that SQL Express supports only one scheduler so adding multiple workers (pending requests) to it won't make much of a difference, they'll just pile up in the runnable queue and fight for the one CPU to run them.

Second, I'm not sure the RMO replication classes (which I assume you use) support doing sync in multiple, parallel, instances, so I probably there's no point in doing more that just one BackgroundWorker per db (I may be wrong on this one, I'm not an RMO expert by any stretch).

Answer 3

Well, to start with and i am very sorry to say this, but your code hurts my eyes...

That whole mess can be rewritten just as this:

        internal static void SyncNow()
        {
            CreateWorker(new MergeRepl(SystemInformation.ComputerName + "\\SQLEXPRESS", "WWCSTAGE", "aspnetdb", "aspnetdb", "aspnetdb"));
            //etc...
        }

        private static void CreateWorker(MergeRepl repl)
        {
            BackgroundWorker connect = new BackgroundWorker { WorkerReportsProgress = false, WorkerSupportsCancellation = true };
            connect.DoWork += new DoWorkEventHandler(DoWork);

            if (connect.IsBusy != true)
                connect.RunWorkerAsync(repl);
        }

        private static void DoWork(object sender, DoWorkEventArgs e) 
        { 
            BackgroundWorker worker = sender as BackgroundWorker; 
            try 
            { 
                MergeRepl aspnetdbMergeRepl = e.Argument as MergeRepl;
                aspnetdbMergeRepl.RunDataSync(); 
                areAllInSync += 1; 
            } 
            catch (Exception) 
            { 
                if (worker != null) worker.CancelAsync(); 
            } 
        }

Next, I would use the ThreadPool for things like this, which will ensure that only a specific number of threads are spawned to do this sort of work.