Performance issues with Docker SQL 2019-latest image

[rapolas-oag]

After SQL2019 image was updated, tests on 2019-latest docker container performance is poor, I started using to 2019-CU27-ubuntu-20.04 and performance of tests is normal again.

Is it known what causes performance issues?

Image	Ubuntu v	Created date	Last updated
2019-latest	Ubuntu 20.04	11/07/2018	08/01/2024
2019-CU27-ubuntu-20.04	Ubuntu 20.04	06/13/2024	06/13/2024

Docker SQL container tags

Tested with versions 1.4.0 and 1.4.1

[rapolas-oag]

Seems that problem is the way file group is deployed:

Creating Filegroup [TAB_ADJ_2010]...
Creating File [TAB_ADJ_2010_1]...
EXEC : error SQL72014: Core Microsoft SqlClient Data Provider: Msg 5009, Level 16, State 1, Line 1 One or more files listed in the statement could not be found or could not be initialized. [C:\[path]\DBPROJECT.Tests.csproj]
EXEC : error SQL72045: Script execution error.  The executed script: [C:\[path]\DBPROJECTB.Tests.csproj]
  ALTER DATABASE [$(DatabaseName)]
      ADD FILE (NAME = [TAB_ADJ_2010_1], FILENAME = '$(DefaultDataPath)FILENAME_TAB_1.ndf', SIZE = 5 MB, FILEGROWTH = 10 %) TO FILEGROUP [TAB_ADJ_2010];


EXEC : error SQL72014: Core Microsoft SqlClient Data Provider: Msg 5170, Level 16, State 1, Line 1 Cannot create file 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\FILENAME_TAB_1.ndf' because it already exists. Change the file path or the file name, and retry the operation. [C:\[path]\DBPROJECTB.Tests.csproj]

[cagrin]

These new (and latest) SQL Server images do not start at dotnet-sqltest at all:

• 2019-CU28-ubuntu-20.04
• 2022-CU14-ubuntu-20.04
• 2022-CU14-ubuntu-22.04

It is cause by major changes at the path to sqlcmd tool inside the container. It was reported at testcontainer-dotnet: testcontainers/testcontainers-dotnet#1220

I need to update couple of packages and it will handle new and old images without any issues. Additionally sqltest must have a better and faster error handling on that and similar situations.

[cagrin]

Interesting that command dotnet publish (MSBuild.Sdk.SqlProj) tries 20 times to start the connection to SQL Server and then fails:

[rapolas-oag]

These new (and latest) SQL Server images do not start at dotnet-sqltest at all:

• 2019-CU28-ubuntu-20.04
• 2022-CU14-ubuntu-20.04
• 2022-CU14-ubuntu-22.04

It is cause by major changes at the path to sqlcmd tool inside the container. It was reported at testcontainer-dotnet: testcontainers/testcontainers-dotnet#1220

I need to update couple of packages and it will handle new and old images without any issues. Additionally sqltest must have a better and faster error handling on that and similar situations.

I reported this as well microsoft/mssql-docker#898 as we noticed issues when running our docker containers locally. Wondering what would be their suggestion or next version release, change path to executable binary or revert to previous path as they did with CU14

[rapolas-oag]

Note Starting with SQL Server 2022 CU 14, we are updating SQL Server 2022 container images to include the new mssql-tools18 package. With the introduction of SQL Server 2022 CU 14, and in all future container images, the previous directory /opt/mssql-tools/bin will be phased out. The new directory for Microsoft ODBC 18 tools will be '/opt/mssql-tools18/bin', aligning with the latest tools offering. For additional details on all the changes and security enhancements, please refer to the blog post [ODBC Driver 18.0 for SQL Server Released - Microsoft Community Hub⁠](https://techcommunity.microsoft.com/t5/sql-server-blog/odbc-driver-18-0-for-sql-server-released/ba-p/3169228). This new iteration, ODBC driver version 18, is designed with an ‘encryption-first’ approach, ensuring that utilities like sqlcmd and bcp that utilize the Microsoft ODBC driver will operate under the ‘secure by default’ principle. Users who wish to disable encryption will need to do so explicitly.

It seems that's the future CU14 for SQL2022 was release on the same date . From docker.com

[rapolas-oag]

I think test containers already have fix for this (implemented 3 weeks ago):
https://github.com/testcontainers/testcontainers-dotnet/blame/7174786e4803f66158d6cd753ec55352f46953b7/src/Testcontainers.MsSql/MsSqlContainer.cs#L72

[cagrin]

Yes, testcontainers-dotnet 3.10.0 has fix for this issue, so does new version of dotnet-sqltest 1.4.2.

[rapolas-oag]

Thank you. This resolved issue on pipeline and locally