If you need to install BizTalk Server 2016, there is a big chance you will also install SQL Server 2016. Not only does SQL Server 2016 support the AlwaysOn capabilities that BizTalk Server 2016 might need, it comes with a much-welcomed upgrade to the support life-cycle. At the moment of writing, the latest service pack of SQL Server 2016 is SP1, which offers support until Jul 13 2021.
Installing BizTalk Server properly will require you to make some changes to the SQL Server Configuration as well. Specifically, you might want/need to disable the Shared Memory Protocol.
Shared Memory Protocol
The SQL Server Shared Memory Protocol per the MSDN documentation around SQL protocols:
Shared memory is the simplest protocol to use and has no configurable settings. Because clients using the shared memory protocol can only connect to a SQL Server instance running on the same computer, it is not useful for most database activity. Use the shared memory protocol for troubleshooting when you suspect the other protocols are configured incorrectly.
Basically, in the case your application is not hosted on the same server as your SQL Database Engine, it makes zero sense to enable the protocol. Therefore, it is considered good practice to disable the protocol and only enable the Named Pipes and TCP/IP protocol:
The above screenshot gives an overview of how the protocol configuration at server side should look like. At Codit, we disable the protocol at server side, since it might pose somewhat of a security risk.
SQL Server Agent not running
While the above has been a good practice for many years and many different BizTalk versions and never gave us any issues, for SQL Server 2016 this meant that the SQL Server Agent (windows service) was stopping unexpectedly. Something that came to my attention while trying to configure the BizTalk SQL Jobs.
The below is a screenshot of the event log and the error logged:
SQLServerAgent could not be started (reason: Unable to connect to server ‘SOMESERVER’; SQLServerAgent cannot start).
(Re)starting the SQL Agent service works fine, but after about a minute, we get the same issue and the service crashes.
After some google-Fu, I finally managed to find the culprit due to a message in the SQL Server log:
2017-03-27 13:33:18 – !  ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
And with some further research, it seemed the ODBC version that ships with SQL Server 2016, which is ODBC v13.0, contains a bug that will prevent the SQL Server Agent from running with Shared Memory disabled!
To check the version of the ODBC drivers on your machine, there is actually a page on Microsoft Docs: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/check-the-odbc-sql-server-driver-version-windows
Note: 2015.130.x = v13.0
We now need to upgrade ODBC to version v13.1. You can download ODBC v13.1 from the Microsoft website: https://www.microsoft.com/en-us/download/details.aspx?id=53339
After upgrading, the Drivers tab mentions v2015.131.x = v13.1
And, as expected (and hoped), the SQL Server Agent service now start successfully and keeps running:
Let me know if this helped you or if you have any questions.