While running my home IoT system, based on HomeAssistant and Node-RED on the docker containers, I have decided to try to implement MSSQL docker based solution as a back-end database for HomeAssistant.

As my host is running Debian Linux, I had to get a Linux based MSSQL container. After looking into available options – 2019-GA version was chosen as it has SQL Agent support implemented, that I needed to configure SQL DB replication to Azure DB.

Final version of the Docker Compose configuration for my SQL Server Container can be found below:

  mssql:
    image: mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04
    container_name: mssql
    restart: unless-stopped
    ports:
      - 1433:1433
    user: "0:0"
    volumes:
      - "/opt/hasetup/mssql/:/var/opt/mssql"
      - "/etc/localtime:/etc/localtime:ro"
      - "/etc/timezone:/etc/timezone:ro"
    environment:
      - "TZ=Europe/London"
      - 'ACCEPT_EULA=Y'
      - 'MSSQL_SA_PASSWORD=xxxxxxxxx'
      - 'MSSQL_AGENT_ENABLED=true'
    network_mode: "host"

Configuration is pretty simple – you just need to:

  • mount local volume, to make sure that you data would persist [“/opt/hasetup/mssql/:/var/opt/mssql”]
  • Accept User License Agreement [ACCEPT_EULA=Y’]
  • Provide your SQL SA password [‘MSSQL_SA_PASSWORD=xxxxxxxxx’]
  • Enable MS SQL AGENT [‘MSSQL_AGENT_ENABLED=true’] (if only it would be as easy)

While it it is now all seems easy, I have spent some time trying to make SQL Agent to work. It was originally failing with the following error:

2019-09-17 12:22:50 - ! [150] SQL Server does not accept the connection (error: 11001). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.
2019-09-17 12:23:21 - ! [150] SQL Server does not accept the connection (error: 11001). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.
2019-09-17 12:23:21 - ! [000] Unable to connect to server '(local),1433'; SQLServerAgent cannot start
2019-09-17 12:23:21 - ! [103] SQLServerAgent could not be started (reason: Unable to connect to server '(local),1433'; SQLServerAgent cannot start)
2019-09-17 12:23:26 - ! [298] SQLServer Error: 11001, TCP Provider: No such host is known. [SQLSTATE 08001] 

I have found that I am not the only one that have such an issue, as it was already raised on GitHub.

Looking into the error [TCP Provider: No such host is known. [SQLSTATE 08001] ] – I have noticed that it seems like failing to connect.

While it took me some time – I have discovered, that SQL Agent within the container is trying to connect to the SQL server using it instance name. And it seems to be failing.

In my original Docker Compose configuration I have had an additional line: [ hostname: mssqlserver ]. This setting define the host name withing the container.

I am not sure if it is by design or not, but in my case container couldn’t resolve it’s name neither to localhost, nor to it’s IP address.

“/etc/hosts” however has had an entry for my physical host name, so it was resolvable.

Based on this finding – I’ve decided to try to fall back to my physical host name within the container removing [ hostname: mssqlserver ] from my Docker Compose configuration.

After amending the SQL server name to match my physical host name – SQL agent was able to connect.

SQL Server name change

SELECT @@SERVERNAME

sp_dropserver mrvmssqlserver

sp_addserver mrvserversmall, local

Having MS SQL Server and MS SQL agent up and running – I’ve configured SQL replication to Azure DB following the MSFT article.

It seems like you can have an easier option for this if your MS SQL is running on Windows, as you can enable Replication Group. Unfortunatly MS SQL sync agent is only available on Windows.

SQL Configuration

DECLARE @distributor AS sysname
DECLARE @distributorlogin AS sysname
DECLARE @distributorpassword AS sysname
-- Specify the distributor name. Use 'hostname' command on in terminal to find the hostname
SET @distributor = N'MyLocalSQLServerName'--in this example, it will be the name of the publisher
SET @distributorlogin = N'MySecureLogin'
SET @distributorpassword = N'MySecurePassword'
-- Specify the distribution database. 

use master
exec sp_adddistributor @distributor = @distributor -- this should be the hostname

-- Log into distributor and create Distribution Database. In this example, our publisher and distributor is on the same host
exec sp_adddistributiondb @database = N'distribution', @log_file_size = 2, @deletebatchsize_xact = 5000, @deletebatchsize_cmd = 2000, @security_mode = 0, @login = @distributorlogin, @password = @distributorpassword
GO

DECLARE @snapshotdirectory AS nvarchar(500)
SET @snapshotdirectory = N'/var/opt/mssql/data/ReplData/'

-- Log into distributor and create Distribution Database. In this example, our publisher and distributor is on the same host
use [distribution] 
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U ')) 
       create table UIProperties(id int) 
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null))) 
       EXEC sp_updateextendedproperty N'SnapshotFolder', @snapshotdirectory, 'user', dbo, 'table', 'UIProperties' 
else 
       EXEC sp_addextendedproperty N'SnapshotFolder', @snapshotdirectory, 'user', dbo, 'table', 'UIProperties'
GO


-- Configure publisher Run the following TSQL commands on the publisher.

DECLARE @publisher AS sysname
DECLARE @distributorlogin AS sysname
DECLARE @distributorpassword AS sysname
-- Specify the distributor name. Use 'hostname' command on in terminal to find the hostname
SET @publisher = N'MyLocalSQLServerName' 
SET @distributorlogin = N'MySecureLogin'
SET @distributorpassword = N'MySecurePassword'
-- Specify the distribution database. 

-- Adding the distribution publishers
exec sp_adddistpublisher @publisher = @publisher, 
@distribution_db = N'distribution', 
@security_mode = 0, 
@login = @distributorlogin, 
@password = @distributorpassword, 
@working_directory = N'/var/opt/mssql/data/ReplData', 
@trusted = N'false', 
@thirdparty_flag = 0, 
@publisher_type = N'MSSQLSERVER'
GO

-- Configure publication Job Run the following TSQL commands on the publisher.

DECLARE @replicationdb AS sysname
DECLARE @publisherlogin AS sysname
DECLARE @publisherpassword AS sysname
SET @replicationdb = N'mydbname'
SET @publisherlogin = N'MySecureLogin'
SET @publisherpassword = N'MySecurePassword'

use [mydbname]
exec sp_replicationdboption @dbname = N'mydbname', @optname = N'publish', @value = N'true'

-- Add the snapshot publication
exec sp_addpublication 
@publication = N'SnapshotRepl', 
@description = N'Snapshot publication of database ''mydbname'' from Publisher ''MyLocalSQLServerName''.',
@retention = 0, 
@allow_push = N'true', 
@repl_freq = N'snapshot', 
@status = N'active', 
@independent_agent = N'true'

exec sp_addpublication_snapshot @publication = N'SnapshotRepl', 
@frequency_type = 1, 
@frequency_interval = 1, 
@frequency_relative_interval = 1, 
@frequency_recurrence_factor = 0, 
@frequency_subday = 8, 
@frequency_subday_interval = 1, 
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959, 
@active_start_date = 0, 
@active_end_date = 0, 
@publisher_security_mode = 0, 
@publisher_login = @publisherlogin, 
@publisher_password = @publisherpassword



--Configure Subscription Run the following TSQL commands on the publisher.

DECLARE @subscriber AS sysname
DECLARE @subscriber_db AS sysname
DECLARE @subscriberLogin AS sysname
DECLARE @subscriberPassword AS sysname
SET @subscriber = N'mycloudSQLservername.database.windows.net' -- for example, MSSQLSERVER
SET @subscriber_db = N'mydbname'
SET @subscriberLogin = N'mycloudSQLusername@mycloudSQLservername'
SET @subscriberPassword = N'mycloudSQLPassword'

use [mydbname]
exec sp_addsubscription 
@publication = N'SnapshotRepl', 
@subscriber = @subscriber,
@destination_db = @subscriber_db, 
@subscription_type = N'Push', 
@sync_type = N'automatic', 
@article = N'all', 
@update_mode = N'read only', 
@subscriber_type = 0

exec sp_addpushsubscription_agent 
@publication = N'SnapshotRepl', 
@subscriber = @subscriber,
@subscriber_db = @subscriber_db, 
@subscriber_security_mode = 0, 
@subscriber_login =  @subscriberLogin,
@subscriber_password =  @subscriberPassword,
@frequency_type = 1,
@frequency_interval = 0, 
@frequency_relative_interval = 0, 
@frequency_recurrence_factor = 0, 
@frequency_subday = 0, 
@frequency_subday_interval = 0, 
@active_start_time_of_day = 0, 
@active_end_time_of_day = 0, 
@active_start_date = 0, 
@active_end_date = 19950101
GO




SELECT name, date_modified FROM msdb.dbo.sysjobs order by date_modified desc

USE msdb;  
--generate snapshot of publications, for example
EXEC dbo.sp_start_job N'MRVSERVERSMALL-mydbname-SnapshotRepl-1'
GO


USE msdb;  
--distribute the publication to subscriber, for example
EXEC dbo.sp_start_job N'mrvserversmall-mydbname-SnapshotRepl-MRVSQLHA.DATABASE.WIN-1'
GO

Once configured – I have checked that new data appeared in my Azure SQL DB by running:

select TOP (100) * 
FROM [dbo].[states]
order by last_changed desc