Microsoft SQL Server Tips

Edit online

Additional tips are provided pertaining to Microsoft SQL Server in the context of Sterling B2B Integrator.

  • When using Sterling B2B Integrator with Microsoft SQL Server, Windows Integrated authentication is not supported.
  • Ensure that network components such as routers, firewalls, and so on, do not drop the idle connections between Sterling B2B Integrator and Microsoft SQL Server where they are on separate physical servers. Sterling B2B Integrator uses JDBC connection pool, and idle connections are typical.
  • It is recommended that you run Microsoft SQL Server and Sterling B2B Integrator on separate physical servers because this helps improve performance, ease of maintenance, and recoverability.
  • It is important to understand the difference between simple blocking and deadlocks:
    • Blocking is an unavoidable characteristic of Microsoft SQL Server because it uses lock-based concurrency. Blocking occurs when one session holds a lock on a specific resource, and a second session attempts to acquire a conflicting lock type on the same resource. Typically, the time frame for which the first session locks the resource is small. When it releases the lock, the second session is free to acquire its own lock and continue processing. This is the normal behavior of Microsoft SQL Server with Sterling B2B Integrator, and is generally not a cause for concern. It is a cause for concern only when sessions are getting blocked for a long time.

    • Deadlocks are much worse than simple blocking. A deadlock typically occurs when a session locks the resources that another session has to modify, and the second session locks the resources that the first session intends to modify. Microsoft SQL Server has a built-in algorithm for resolving deadlocks. It will select one of the deadlock participants and roll back its transaction. This session becomes the deadlock victim. Microsoft SQL Server has two trace flags that can be set to capture deadlock-related information. The flags are Trace Flag 1204 and Trace Flag 1222. These trace flags can be used as an alternative to using SQL Server Profiler.

      For more information about trace flags, see https://msdn.microsoft.com/en-us/library/ms188396%28v=sql.105%29.aspx

      For more information about Detecting and Ending Deadlocks in Microsoft SQL Server 2005 and Microsoft SQL Server 2008, refer to the Microsoft Developer Network Web site, which can be accessed from: http://msdn.microsoft.com/en-us/library/ms178104(SQL.90).aspx.

      In Microsoft SQL Server 2012, the System Health session detects deadlocks. See https://technet.microsoft.com/en-us/library/ff877955%28v=sql.110%29.aspx for more information.