locked
MAX connections sqlserver RRS feed

  • Question

  • The maximum number of accesses is to sqlserver is unlimited and depends on the server hardware,
    Now if we use cluster sqlserver ..
    I guess the connections are distributed on servers and the performance will be better
    Tuesday, March 26, 2013 9:26 PM

Answers

  • There are many moving parts in the entire solution. It all depends on how the application is coded to deal with those parts moving/failing. If you have a specific scenario please lay it out, otherwise this is as generic as I can get. There are too many different things that could happen and different timelines that they could be part of.


    Sean Gallardy | Blog | Twitter

    • Marked as answer by neonash Thursday, March 28, 2013 11:56 PM
    Wednesday, March 27, 2013 7:32 PM
    Answerer

All replies

  • Hello,

    In clustered environments the same rules apply. Microsoft clustering is a shared nothing architecture and only a single node can hold the resources at a single point in time. There is no load balancing included with clustering as it's an HA only technology.

    -Sean


    Sean Gallardy | Blog | Twitter

    Wednesday, March 27, 2013 12:11 PM
    Answerer
  • That means that if a user connects to a server
    Microsoft SQLServer cluster, and this server is dropped;
    it will go connection error, and will have to leave and re-connect to the cluster, which is going to redirect to another server
    available.
    Wednesday, March 27, 2013 2:02 PM
  • Correct.

    If the node fails or the resource group is moved, SQL Server at some point will need to either be nicely shutdown (in the case of a resource group move) or could unexpectedly terminate (such as a node power outage). The SQL Server service will be offline until the resources are migrated and started back up. Since the service is offline, no connections can be made and all connections that were open would now be forcefully closed. When SQL Server starts up again, it has no idea who was and wasn't connected (just like a stand alone instance). Crash recovery is run, and the instance is once again open for business.

    -Sean


    Sean Gallardy | Blog | Twitter

    Wednesday, March 27, 2013 2:05 PM
    Answerer
  • So if for example my web application (eg CRM) stores an employee, and falls sqlserver server, my web system return me an error message at the time, while that
    recovery system is running and automatically redirects me to another sqlserver, and at the time and could save that employee: is so ..

    Another question that goes well with a clustered application aspnet, that if you drop a web server I'm connected, in
    that instant recovery system, I get redirected
    another web server from my web cluster.
    Wednesday, March 27, 2013 6:04 PM
  • What would happen (grossly understated because of scope) is the following:

    1. You lookup a person in your CRM, data is transferred back to the client (web browser).

    2. The node that this instance of SQL Server was running on crashes do to rack power failure.

    3. The instance goes down hard, the cluster notices this and starts the instance up on another node (if possible).

    4. Your web client doesn't know any better, you edit the persons' last name.

    5. You click "save" which makes a call back to SQL Server to update the information.

    6. SQL Server is started up on a different node and running through crash recover, but not yet available to end users.

    7. The connection no longer exists and SQL Server is not currently available to end users. A connection error is thrown at the data access layer.

    From here, you have a few different options.

    1. Your application can be coded in a way that sees this error and backs off for a small period of time, then retries the transaction. This is facilitated by the program attempting to open a valid connection to SQL Server and then attempting the transaction again. This would be transparent to the end user if done correctly, though it may seem like the application is "hung" for a while until either the transaction goes through or the retry logic hits a hard stop and decides to throw an error back to the user.

    2. The application is not coded for retry logic and fails, passing an error back.

    3. The application is not coded for errors at all and catastrophically fails.

    Because clustering uses a VIP, the name and IP always stays the same to the SQL Server instance - this won't actually change. The node that hosts the resources will change, though this will be transparent (other than service interruptions) to the end user.

    -Sean


    Sean Gallardy | Blog | Twitter

    Wednesday, March 27, 2013 6:29 PM
    Answerer
  • But the end user gets to make the upgrade to the employee, then you have spent a period of time and has risen another sqlserver .. or that the other server in the cluster
    web server has been lifted ..
    Wednesday, March 27, 2013 7:29 PM
  • There are many moving parts in the entire solution. It all depends on how the application is coded to deal with those parts moving/failing. If you have a specific scenario please lay it out, otherwise this is as generic as I can get. There are too many different things that could happen and different timelines that they could be part of.


    Sean Gallardy | Blog | Twitter

    • Marked as answer by neonash Thursday, March 28, 2013 11:56 PM
    Wednesday, March 27, 2013 7:32 PM
    Answerer