search

microsoft / mssql-docker

Official Microsoft repository for SQL Server in Docker resources
MIT License
1.73k stars 757 forks source link

Failing to CREATE EXTERNAL DATA SOURCE (SQL commands) #547

Open tappoz opened 4 years ago

tappoz commented 4 years ago

I am trying to create some external data sources using this documentation details: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=azuresqldb-current

However I keep seeing similar errors to these:

Msg 102, Level 15, State 1, Server 8aa9ba718496, Line 10
Incorrect syntax near 'RDBMS'.
Msg 102, Level 15, State 1, Server 8aa9ba718496, Line 18
Incorrect syntax near 'EXTERNAL'.

The minimum piece of SQL code to reproduce the errors above is:

CREATE EXTERNAL DATA SOURCE FooExternalData
WITH
(    LOCATION = 'sqlserver://<MY_DB_NAME>.windows.net'
,    TYPE     = RDBMS
)
;

The code is actually more complex containing the CREDENTIAL details, regardless I see the same error message.

I've used a variety of docker images (2017, 2019 etc.) all showing the same error, including the latest one: mcr.microsoft.com/mssql/server:2017-CU18-ubuntu-16.04.

Being a syntax error I am wondering if this stuff can not be executed on a Docker version of SQL Server? Is this functionality implemented for the Docker version of SQL Server?

How can I add external sources to SQL Server running on Docker?

nocentino commented 4 years ago

Hi there, you'll need to build a custom SQL container with Polybase installed and use that image for your container. Check out the link below...

https://github.com/microsoft/mssql-docker/tree/master/linux/preview/examples/mssql-polybase

tappoz commented 4 years ago

Oh nice, thanks for that. I had to increase the sleep time to 24 seconds before being able to target the running Docker container without seeing spurious errors due to the bootstrap time (e.g. Login failed for user 'sa'. Reason: Server is in script upgrade mode. Only administrator can connect at this time).

However now the "polybase commands" are showing these warnings when executed with sqlcmd:

sqlcmd -S 127.0.0.1 -U SA -P ${SA_PASSWORD} -q "exec sp_configure @configname = 'polybase enabled', @configvalue = 1;" -W < /dev/null
Configuration option 'polybase enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.

Sqlcmd: Warning: The last operation was terminated because the user pressed CTRL+C.
sqlcmd -S 127.0.0.1 -U SA -P ${SA_PASSWORD} -q "RECONFIGURE;" -W < /dev/null

Sqlcmd: Warning: The last operation was terminated because the user pressed CTRL+C.

Then I see the same original syntax error, so I think that's because of the sqlcmd warnings. Is there a way to automate the whole process?

nocentino commented 4 years ago

That error has to do with the system databases being updated during the container start, this is a one-time operation when changing major versions of the database engine.

After creating your custom container image you could start a container up from that image...wait...ensuring that process is finished and then create another image from that container. This way you've completed that step.

Another option would be to add a sleep to your code to wait out that process...which takes about a minute.

tappoz commented 4 years ago

@nocentino thanks for that, yes I've added a sleep 30 assuming after that SQL Server is warm enough. When the docker container is up and running I still see a syntax error when trying to add an external source.

These are the minimum steps required to reproduce the syntax error:

Build the docker image from a local docker file (called sql-server.Dockerfile) containing a copy-paste from https://github.com/microsoft/mssql-docker/blob/master/linux/preview/examples/mssql-polybase/Dockerfile with this command:

docker build -t ss-polybase-i -f sql-server.Dockerfile .

Run the docker container, then wait for the warmup:

docker run \
   -e 'ACCEPT_EULA=Y' \
   -e 'SA_PASSWORD=${MY_SECRET_PASSWORD}' \
   -p 1433:1433 \
   --name ss-local-c \
   -d ss-polybase-i ; sleep 30

Configure the polybase stuff (I need to invoke sqlcmd because this is part of an automated process):

sqlcmd -S 127.0.0.1 -U SA -P ${MY_SECRET_PASSWORD} -q "exec sp_configure @configname = 'polybase enabled', @configvalue = 1;" -W < /dev/null
sqlcmd -S 127.0.0.1 -U SA -P ${MY_SECRET_PASSWORD} -q "RECONFIGURE;" -W < /dev/null

Try (and failing) to add a very basic external data source:

sqlcmd -S 127.0.0.1 -U SA -P ${MY_SECRET_PASSWORD} -i sql/polybase-test.sql -b

where the SQL script polybase-test.sql contains:

CREATE EXTERNAL DATA SOURCE FooExternalData
WITH
(    LOCATION = 'sqlserver://<MY_EXISTING_AZURE_DB>.windows.net'
,    TYPE     = RDBMS
)
;

Then seeing this error message:

Msg 102, Level 15, State 1, Server dbb5a7f0fc0a, Line 4
Incorrect syntax near 'RDBMS'.

Worth note that the version for SQL Server is 15 (which I think should be the right one):

sqlcmd -S 127.0.0.1 -U SA -P ${MY_SECRET_PASSWORD} -q "SELECT CAST(SUBSTRING(CAST(SERVERPROPERTY('RESOURCEVERSION') AS varchar), 1, 2) AS INT);" -W < /dev/null

-
15
nocentino commented 4 years ago

From this link it doesn't look like RDBMS is a valid TYPE for CREATE EXTERNAL DATASOURCE you should consider updating that parameter with a valid argument.

tappoz commented 4 years ago

Right, so I think the confusion comes from the index at the top of that link you provided which is pointing at "SQL Server", whereas the first link I provided was pointing at "SQL Database (Azure)".

From those docs we have:

I can create a hierarchy as "external data source -> external table" with the Docker stuff without specifying the TYPE but providing as URL with prefix sqlserver something like LOCATION = 'sqlserver://<MY_AZURE_THING>.database.windows.net' where <MY_AZURE_THING> behaves like a SQL Server and can contain multiple databases (not sure if there is a definition for an Azure thing containing multiple Azure "databases").

However this same solution (avoiding to use TYPE) does not work when targeting something on Azure (the wrapper "Azure thing / SQL Server" as external data source, then the "Azure database" as the wrapped external table).

Is there a work-around to have the same SQL scripts work targeting either a local Docker instance and an Azure SQL Server?

I would like to point to external tables:

Is this possible at all?