none
Why Remote MSSQL server posting back twice but local is not? RRS feed

  • Question

  • Hello

    I have an ASPX (.NET 4.7) website running with MSSQL 2008R2 on the same machine. Works fine. No problems. 

    Last week I moved the "ASPX portion" to A new Windows 2017 server on AWS keeping my MSSQL 2008 R2 server as the database a few hundred miles away.

    Recap: My setup worked fine on the "one server" solution but now I have a problem with "two server solution."

    I opened firewall ports for 1432-1435... then on the MSSQL server I ran some netsh ipsec thing to block everyone except the IP of the new web server.

    Problem: When my ASPX page now executes a stored procedure (on the remove MSSQL server) with NO code changes (same code as the first setup)....................it executes the sp TWICE. Does anyone know why this is? 

    Thank you

    Joe

    Thursday, May 9, 2019 12:50 PM

All replies

  • Why do you think the SP is executed twice?

    SQL Server execute those commands sent to it, not more.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Thursday, May 9, 2019 1:07 PM
  • Yes this is the question. Is there a chance that when I stopped using a server same as webserver........that when it executes the SP, it somehow "hits it, doesn't get a response fast enough, then sends the request to execute SP a second time?"


    Thursday, May 9, 2019 1:55 PM
  • What I'm saying here is... "Do I need to rev-up (like an engine) the connection to MSSQL before using it?"

    Meaning: Should I run some other SP right-before I want to do something of importance? Is there a setting somewhere to establish a connection before sending the execute-sp command?

    Thursday, May 9, 2019 2:10 PM
  • How do you conclude that the procedure is executed twice?

    If the procedure executes twice, it is because web application submits it twice. And that would be because you coded it that way.

    By thw way, the forum you have posted to is for using .NET inside SQL Server. People who monitor this forum are SQL Server people whose experience of implementing web sites may not be fantastic.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, May 9, 2019 9:08 PM
  • As I mentioned:

    1.) Code hasn't changed. It ran for over a year on the "MSSQL Server / IIS on same machine" combo.

    2.) The second I moved the IIS to another machine (far away, so some latency of course), I get this double-post.

    Now yes it is submitting twice I would assume. I ran SQL Profiler and it shows it running twice. 

    Does anyone know why this might happen?

    If I posted to the wrong forum please tell me where to post. Are people really that "narrow" today? I guess. Please advise. Thanks.

    Thursday, May 9, 2019 10:03 PM
  • Now yes it is submitting twice I would assume. I ran SQL Profiler and it shows it running twice. 

    And you are seeing to RPC:Completed for the same call? (As opposed to one RPC:Completed  and one SP:Completed which would be for the same call.)

    This means that your application is making two calls. It is not SQL Server thinking "Oh, since that web application is now in AWS, I need to run this procedure twice".

    And then it is not going help that you repeat that your code has not changed. That is one of these very common mistakes that programmers make. "Nothing has changed". But something had changed.

    What I can say from an SQL Server perspective is that you can use Profiler to find out the nature of these double calls. Are they on the same SPID? If the SPIDs are different, what about ApplicationName? ClientProcessID? HostName?

    If I posted to the wrong forum please tell me where to post. Are people really that "narrow" today? I guess. Please advise. Thanks.

    The reason you may want find a better forum is that you may get better help in a forum devoted to web applications. But which forum that would be? How would I know, since I don't do web applications myself?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, May 10, 2019 8:34 AM
  • Thank you for the response.

    Firstly: I see this (problem occurring) only after moving the servers physically apart (MSSQL in one state, and IIS in the other). Secondly, it only happens at random strange times of the day which I associate with "high latency times" but haven't tested. Thirdly, this server (when IIS and MSSQL were on the same machine) ran essentially with the same code for over one year, never one single solitary problem with double-inserts. 

    Below is a dump from SQL Profiler just now when I submitted the page online (IIS) and on the backend, it contacted my remote SQL server and executed the stored procedure. Furthermore, the method I am using is the .dbml method in .NET as opposed to executing it all in one set of strings as we used to do it but I would guess it doesn't matter.

    You'll note it does have two RPC:Completed sections but I only submitted it once (try this at 11AM and it probably will have ONE, not two...which is why I think it might be latency). Nothing crazy, just "not on the same machine anymore." Do I need something in web.config to accommodate?

    I see an error which throughout many languages, mechanisms, from hardware to software to routing means it might just be doing what I thought. Resubmitting itself. As indicated possibly by sp_reset_connection? I don't know but regardless, it seems to be doing that (my very original concern). Do I need something in my SP (tsql)? It just appears right now that because of I would assume latency (or maybe a port besides 1433 needing open?) that the IIS server not getting sufficient response so it resubmits. Or it's getting back an error so it resubmitts. There must be a fix for this.

    dump:

    Audit Login -- network protocol: TCP/IP
    set quoted_identifier on
    set arithabort off
    set numeric_roundabort off
    set ansi_warnings on
    set ansi_padding on
    set ansi_nulls on
    set concat_null_yields_null on
    set cursor_close_on_commit off
    set implicit_transactions off
    set language us_english
    set dateformat mdy
    set datefirst 7
    set transaction isolation level read committed
    .Net SqlClient Data Provider sa 3028 56 2019-06-18 07:57:57.477
    Audit Logout .Net SqlClient Data Provider sa 0 0 0 100 3028 56 2019-06-18 07:57:57.477 2019-06-18 07:57:57.577
    RPC:Completed exec sp_reset_connection .Net SqlClient Data Provider sa 0 0 0 0 3028 56 2019-06-18 07:57:57.577 2019-06-18 07:57:57.577 0X0000000...
    Audit Login -- network protocol: TCP/IP
    set quoted_identifier on
    set arithabort off
    set numeric_roundabort off
    set ansi_warnings on
    set ansi_padding on
    set ansi_nulls on
    set concat_null_yields_null on
    set cursor_close_on_commit off
    set implicit_transactions off
    set language us_english
    set dateformat mdy
    set datefirst 7
    set transaction isolation level read committed
    .Net SqlClient Data Provider sa 3028 56 2019-06-18 07:57:57.577
    RPC:Completed declare @p18 int
    set @p18=2440
    declare @p19 int
    set @p19=0
    exec sp_executesql N'EXEC @RETURN_VALUE = [dbo].[standalone_insert] @PARAMETERS AND DATA (I replaced this line with junk to hide stuff but this is the line that executes the SP).
    select @p18, @p19 .Net SqlClient Data Provider sa 0 75 2 1 3028 56 2019-06-18 07:57:57.577 2019-06-18 07:57:57.577 0X00000000140000001...
    Audit Login -- network protocol: TCP/IP
    set quoted_identifier on
    set arithabort off
    set numeric_roundabort off
    set ansi_warnings on
    set ansi_padding on
    set ansi_nulls on
    set concat_null_yields_null on
    set cursor_close_on_commit off
    set implicit_transactions off
    set language us_english
    set dateformat mdy
    set datefirst 7
    set transaction isolation level read committed
    .Net SqlClient Data Provider sa 3028 58 2019-06-18 07:57:57.587
    Audit Logout .Net SqlClient Data Provider sa 0 0 0 26 3028 58 2019-06-18 07:57:57.587 2019-06-18 07:57:57.613
    RPC:Completed exec sp_reset_connection .Net SqlClient Data Provider sa 0 0 0 0 3028 58 2019-06-18 07:57:57.613 2019-06-18 07:57:57.613 0X0000000000000...
    Audit Login -- network protocol: TCP/IP
    set quoted_identifier on
    set arithabort off
    set numeric_roundabort off
    set ansi_warnings on
    set ansi_padding on
    set ansi_nulls on
    set concat_null_yields_null on
    set cursor_close_on_commit off
    set implicit_transactions off
    set language us_english
    set dateformat mdy
    set datefirst 7
    set transaction isolation level read committed
    .Net SqlClient Data Provider sa 3028 58 2019-06-18 07:57:57.613
    RPC:Completed declare @p18 int
    set @p18=2441
    declare @p19 int
    set @p19=0
    exec sp_executesql N'EXEC @RETURN_VALUE = [dbo].[standalone_insert] @PARAMETERS AND DATA (I replaced this line with junk to hide stuff but this is the line that executes the SP).
    select @p18, @p19 .Net SqlClient Data Provider sa 0 83 2 1 3028 58 2019-06-18 07:57:57.613 2019-06-18 07:57:57.613 0X0000000014...
    Audit Logout Report Server NETWORK SERVICE NT AUTHORITY\NETWORK SERVICE 0 1850 0 10140 1780 51 2019-06-18 07:57:49.457 2019-06-18 07:57:59.597
    RPC:Completed exec sp_reset_connection Report Server NETWORK SERVICE NT AUTHORITY\NETWORK SERVICE 0 0 0 0 1780 51 2019-06-18 07:57:59.597 2019-06-18 07:57:59.597 0X0000000000000000...
    Audit Login -- network protocol: LPC
    set quoted_identifier on
    set arithabort off
    set numeric_roundabort off
    set ansi_warnings on
    set ansi_padding on
    set ansi_nulls on
    set concat_null_yields_null on
    set cursor_close_on_commit off
    set implicit_transactions off
    set language us_english
    set dateformat mdy
    set datefirst 7
    set transaction isolation level read committed
    Report Server NETWORK SERVICE NT AUTHORITY\NETWORK SERVICE 1780 51 2019-06-18 07:57:59.597





    Tuesday, June 18, 2019 12:09 PM
  • You'll note it does have two RPC:Completed sections but I only submitted it once

    Your application nevertheless submitted the calls to standalone_insert twice, using two different spids. The calls were submitted within 40 ms from each other. The calls were made from the same client process-id, but that is not startling. Unfortunately, the trace does not give you information about thread ids in the client - that could have helped. But possibly could the different spids, that you have two threads in your application doing the same thing.

    In any case, this is an issue in your application. I would recommend you not to focus too much on the fact that this never occurred when IIS and SQL Server were on the same machine. Whatever flaw you have in your application, it may be that it is more likely to exhibit when IIS and SQL Server are far apart.

    I would recommend that you ask in a forum for IIS or web programming to get help with how to troubleshooting your issue.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, June 18, 2019 9:37 PM
  • Thanks.

    But it DID only happen in that "remote" instance, and not for a year on the localized instance. That is important. Regardless if configuration changes needed in the application itself. It's just executing an SP using .dbml instead of the direct access. The "thing that changed," the variable if you will, is what I mentioned. And that will tell us what needs to be changed. I assume it will be in the code. But what? I can jerry-rig it, but I don't want to do that, I want the correct method and understanding of why it's happening.

    I will post this thread link if I may, into another forum. Which forum do you recommend? Thank you.

    Joe

    Wednesday, June 19, 2019 11:40 AM
  • But it DID only happen in that "remote" instance, and not for a year on the localized instance. That is important.

    Maybe it is to understand why it is happening now. You said that currently it happens intermittently, like if there is a load or so. It could be timing-related, and with a local connection you never got a delay which triggered this second call.

    It is not uncommon in the art of programming that flaws in an application goes unnoticed because fortunate circumstances mask the flaw. Until one those fortunate circumstances are not there any more...

    I will post this thread link if I may, into another forum. Which forum do you recommend? Thank you.

    I don't do web programming so I have no clue.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, June 19, 2019 8:50 PM