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 - !  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 - !  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 - !  Unable to connect to server '(local),1433'; SQLServerAgent cannot start 2019-09-17 12:23:21 - !  SQLServerAgent could not be started (reason: Unable to connect to server '(local),1433'; SQLServerAgent cannot start) 2019-09-17 12:23:26 - !  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.
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