question

JKH1 avatar image
0 Votes"
JKH1 asked JKH1 commented

Sql Server Project Publish to a Docker Hosted Instance - SQLCMD and DefaultDataPath

I am having a VERY difficult time publishing a pre-existing Sql project to a Docker hosted instance of Sql Server. What I am attempting to do is make a clean pipeline for a Docker hosted instance to use in testing a Sql project, which of course starts with doing it first by hand to understand all the steps involved. The Sql project itself has been around for many years, and has no problems deploying to Sql Server instances hosted on Windows boxes.

As near as I can tell, the issue comes while SSDT is generating the Sql deployment script itself. In a normal deployment to a Windows hosted Sql Server, the generated script starts out with some :setvar commands, including:

:setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\"
:setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\"

However, when publishing to a Docker hosted instance of Sql Server, and the same deployment process, the sql script has:

:setvar DefaultDataPath ""
:setvar DefaultLogPath ""

The 1st thing this deployment does is to alter the database by adding in an additional data file, e.g.:

ALTER DATABASE [$(DatabaseName)]
ADD FILE (NAME = [ARCHIVE_274A259D], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_ARCHIVE_274A259D.mdf') TO FILEGROUP [ARCHIVE];

The Docker based deployment then craps itself because the file path is (obviously) invalid.

In researching this problem, I've seen MANY solutions which hand-edit the generated deployment Sql script, and manually set the "proper" values for DefaultDataPath and DefaultLogPath ... and even one solution that ran the generated Sql through some sort of post-processor to make that same edit in a programmatic way with string replacement. This does work, but is less than optimal (especially in an automated build/test/deploy pipeline).

I've checked in the Docker instance itself, and its mssql.conf file does have defaults defined:

$ cat /var/opt/mssql/mssql.conf
[sqlagent]
enabled = false

[filelocation]
defaultdatadir = /var/opt/mssql/data/
defaultlogdir = /var/opt/mssql/log/

Can anybody shed light on why these are not being picked up by the SSDT process of generating the deploy script?

I spent a few days trying various workarounds to the problem ...

  1. Defined the DATA and LOG directories in the Docker "run" command, but this had no effect on the gnerated Sql deploy script, e.g.: -e 'MSSQL_DATA_DIR=/var/opt/mssql/data/' -e 'MSSQL_LOG_DIR=/var/opt/mssql/log/'

  2. Configure the Sql Project with SQLCMD Variables:
    58696-image.png
    This method could not override the DefaultDataPath or DefaultLogPath. I could add new Variables, sure, but those would not affect the file path of the ALTER DATABASE command above.

  3. Tried a Pre-Deployment script specifically tailored to override the values of DefaultDataPath and DefaultLogPath. While this technically CAN override the default values, the Pre-Deployment script is included in the generated Sql deployment script AFTER the ALTER DATABASE commands to add data files. It would effectively work for the rest of the script, just not the specific portion that was throwing the error on initial deployment of the database.

I'm pretty well stumped. Any ideas?















sql-server-general
image.png (14.1 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi JKH1,

Sorry that I am not familiar with SQL Server docker container, and few experts are familiar with SQL Server docker container in this forum. Maybe you can ask professional engineer for help, they can check your environment to find if we missing something, and they will deal with your problem separately and confidentially. https://support.microsoft.com/en-us/assistedsupportproducts
And please refer to this thread which might help.

Best Regards,
Amelia


0 Votes 0 ·

The Stackoverflow thread on commenting out SetVar declarations was interesting. Had not found that one yet. The publish setting did what it advertised, but during the deploy that simply left the variables undefined causing errors in any statement that expected them to exist. I feel there is some flaw in SSDT generation of the sql deploy script which doesn't pick up the actual configured default paths from the Docker instance of Sql Server ... either that or a Sql Server configuration option that I am unaware of.

Thanks, I'll see if my organization has a support contract that I can channel this question through. Figured a message board would be faster since I am clearly not the only person experiencing the issue.

-kevin

0 Votes 0 ·

Hi @JKH1

I have a similar issue and used bits of your post to raise a support ticket myself: https://developercommunity.visualstudio.com/t/Sql-Server-Project-Publish-to-a-Docker-H/1393864

Let's see if the team comes back with some feedback/solution.

0 Votes 0 ·
Show more comments

0 Answers