Alternatives?

Pho3nix

Pho3nix

The Legend
Joined
Jul 31, 2009
Messages
32,826
Reaction score
3,033
Location
On the toilet
Hi all,

Happy Monday :)

So my dilemma, there is a WCF service that pulls data from multiple sources and inserts them into a DB for other things. Service is kicked off by a SSIS package and bob is your uncle.
Well that's what it is supposed to do.
Service isn't working and after spending 2 days debugging it (it works on local but not on prod/qa) I am at a loss. And yes, it used to work fine until recently. No patches or security changes as far as I can see.

Team wants us to all spend a couple days looking into this and I'm of the opinion I should re-look the initial implementation and possibly re-dev.

Question is, what alternatives are there to get the same functionality where it isn't a WCF webservice. Console apps and the like are locked down so it can't be anything that needs a user to be logged in etc.

Thoughts?
 
What do you mean the service isn't working? What exactly doesn't work?
 
semaphore said:
What do you mean the service isn't working? What exactly doesn't work?
Click to expand...

As per logging.. It pulls all the relevant info but actually bulk inserting it isn't working. No error messages, just passes even when we run tests pointing to that box.

Will post the portion of code doing that work in a couple minutes.

Disclaimer: Not my code :o previous Dev that was here
 
Unless you can dev a new solution faster than you (and everyone else) can debug it in, I'd rather find the root cause of the issue and fix it. Sounds like someone changed the SQL user password. If it's working on local but not on dev/prod, most certainly user-access related. "Nothing changed" is a bit of a stretch if something that worked suddenly stopped working.
 
I find SQL bulk inserts are overused and can have a number of problems with access to the file - if you are using a SQL account, it will use the service account running SQL server to access the file. If you are using AD integrated authentication, it will try impersonate the AD user and use their credentials to access the file. In this case you will need to enable kerberos delegation.
You can check the security logs on both the SQL server and the server hosting the file to look for any Access Denied errors.

Considering you are pulling the file over WCF, I assume that the data is quite small.
If that's the case, I would just get rid of bulk insert. Opening a connection and using a standard parameterized SQL Command to perform inserts should provide similar performance and unless you are importing millions of records, there really isn't a need for bulk insert.
This is a much simpler approach and you are less likely to have these kinds of issues.
 
Last edited:
Good old investigation work...run every aspect seperately with the credentials as used in your prod environment. Don't assume anything. inSERT not working, did the table change perhaps...dare I mention is there a select * somewhere? Yes, I know, but it happens...
 
Pho3nix said:
As per logging.. It pulls all the relevant info but actually bulk inserting it isn't working. No error messages, just passes even when we run tests pointing to that box.

Will post the portion of code doing that work in a couple minutes.

Disclaimer: Not my code :o previous Dev that was here
Click to expand...

Are you missing a commit ? :P
 
envo said:
Unless you can dev a new solution faster than you (and everyone else) can debug it in, I'd rather find the root cause of the issue and fix it. Sounds like someone changed the SQL user password. If it's working on local but not on dev/prod, most certainly user-access related. "Nothing changed" is a bit of a stretch if something that worked suddenly stopped working.
Click to expand...
Pretty sure I can. Code is a little "special" which might make the thing faster as well.
But as all things.. decision rests with management.
me_ said:
I find SQL bulk inserts are overused and can have a number of problems with access to the file - if you are using a SQL account, it will use the service account running SQL server to access the file. If you are using AD integrated authentication, it will try impersonate the AD user and use their credentials to access the file. In this case you will need to enable kerberos delegation.
You can check the security logs on both the SQL server and the server hosting the file to look for any Access Denied errors.

Considering you are pulling the file over WCF, I assume that the data is quite small.
If that's the case, I would just get rid of bulk insert. Opening a connection and using a standard parameterized SQL Command to perform inserts should provide similar performance and unless you are importing millions of records, there really isn't a need for bulk insert.
This is a much simpler approach and you are less likely to have these kinds of issues.
Click to expand...

So, access. Account being used is configured correctly. Same one I am using. Same issue.
You make sense on the bulk insert though..
 
freddster said:
Good old investigation work...run every aspect seperately with the credentials as used in your prod environment. Don't assume anything. inSERT not working, did the table change perhaps...dare I mention is there a select * somewhere? Yes, I know, but it happens...
Click to expand...
Trust me. I have looked. Access issues. Config issues :D
Nerfherder said:
Are you missing a commit ? :P
Click to expand...

:p
 
Just to expand on this.
The code till it stopped working would work intermittently. ie. Works on just QA for 3 days. Stops. Works on just Prod for 2 days. Stops. Now it's stopped completely. Keep in mind this was while the code was exactly the same on all the boxes.

code doing the save :
Code: 
try
            {

                AfricaStuffDataContext dataContext = new AfricaStuffDataContext (ConnectionString);

                foreach (AfricaStuff item in africaS)
                {
                    dataContext.AfricaStuff.InsertOnSubmit(item);
                    dataContext.SubmitChanges();
                }

            }
            catch (Exception)
            {
                throw;
            }
        }
 
Pho3nix said:
Works on just QA for 3 days. Stops. Works on just Prod for 2 days. Stops. Now it's stopped completely. Keep in mind this was while the code was exactly the same on all the boxes.
Click to expand...

Can you see process resource usage at the time of being "stopped"? Could be resource limitations, Memory or HDD?
 
Are you maybe leaking connections? Maybe the server runs out of connections and then comes to a grinding halt. You are passing a connection string, so probably creating a connection, but I do not see any code that release/close it, if through that might be in the guts of the methods you are calling. And that could explain why you break faster in production than QA. Closing the connection could be either a call to close or a using block.

(Just catching and throwing an exception without any logging is also a bad move, but that is probably not directly relevant here. You mess with the exception stack, without any benefits, by doing that.)
 
Last edited:
gkm said:
Are you maybe leaking connections? Maybe the server runs out of connections and then comes to a grinding halt. You are passing a connection string, so probably creating a connection, but I do not see any code that release/close it, if through that might be in the guts of the methods you are calling. And that could explain why you break faster in production than QA.

(Just catching and throwing a connection without any logging is also a bad move, but that is probably not directly relevant here. You mess with the exception stack, without any benefits, by doing that.)
Click to expand...

this is a snippet code implementing it, close the connection at the end. Or is this more a question of am I closing the connection from the implementation end? :

Code: 
static void Main(string[] args)
        {
            ChannelFactory<IAfricaStuff> channelFactory;
            IAfricaStuff client;

            try
            {

                //create the binding
                var binding = new WSHttpBinding();
                //configure the binding
                binding.Security.Mode = SecurityMode.Message;
                binding.MaxReceivedMessageSize = 2147483647;
                binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Windows;
                binding.SendTimeout = new TimeSpan(0, 10, 0);
                binding.CloseTimeout = new TimeSpan(0, 10, 0);
                binding.OpenTimeout = new TimeSpan(0, 10, 0);
                binding.ReceiveTimeout = new TimeSpan(0, 10, 0);
                var endpointAddress = new EndpointAddress("http://localhost:54358/AfricaStuff.svc");
                channelFactory = new ChannelFactory<IAfricaStuff>(binding, endpointAddress);
                
                client = channelFactory.CreateChannel();
                var result = client.SaveStuff();
                //create the channel
                channelFactory.Close();
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
 
Pho3nix said:
Just to expand on this.
The code till it stopped working would work intermittently. ie. Works on just QA for 3 days. Stops. Works on just Prod for 2 days. Stops. Now it's stopped completely. Keep in mind this was while the code was exactly the same on all the boxes.

code doing the save :
Code: 
try
            {

                AfricaStuffDataContext dataContext = new AfricaStuffDataContext (ConnectionString);

                foreach (AfricaStuff item in africaS)
                {
                    dataContext.AfricaStuff.InsertOnSubmit(item);
                    dataContext.SubmitChanges();
                }

            }
            catch (Exception)
            {
                throw;
            }
        }
Click to expand...

You perhaps not swallowing an exception higher up in the call stack? So while you "think" it works, it actually doesn't? Perhaps an invalid datatype attempted at saving.
 
semaphore said:
Its entityframework, it will manage the connection's itself within the datacontext.
Click to expand...

OK, then guess more hunting is needed. I have never used Entity Framework, so do not know the details. It just smelled like a resource of some kind running out and DB connections was my first suspect.
 
gkm said:
OK, then guess more hunting is needed. I have never used Entity Framework, so do not know the details. It just smelled like a resource of some kind running out and DB connections was my first suspect.
Click to expand...

Well I mean SubmitChanges in a for-loop is daft in its own right. I'd imagine this particular service has tons of design flaws.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X