Technical Blog
SQL Server on Linux and Transactional Replication
Configuring transactional replication to work with SQL Server on Linux
💡 Update 16/9/20: SQL Server 2017 (14.x) (CU18) and later support SQL Server Replication for instances of SQL Server on Linux.
Many companies use transactional replication to scale out their database reads; which can get quite large and potentially expensive. Costs can be reduced by moving to SQL Server Web Edition, which is extremely cheap for what you get, but after that, you only really save money by reducing the server count.
As SQL Server 2017 now runs on Linux, we have another opportunity to reduce costs by removing the Windows Server licence entirely and switching to Linux (I’m using Ubuntu).
Implementing Replication Using SSMS
You can’t. At the time of writing, Microsoft has stated that transactional replication isn’t supported on Linux and we’re not sure if they intend to support it in the future. This means that if you try to add the server to a publication, you get the following message. So you can’t use the GUI, and it also means that you can’t use pull, as the necessary files won’t be there.
Implementing Replication Using Scripts
So the GUI doesn’t work, and you can’t use pull subscriptions, but there’s no reason why you can’t use scripts to configure a push subscription.
EXEC sys.sp_addsubscription @publication = 'xTEST', @subscriber = 'SQLOL-01', @destination_db = 'xDB', @subscription_type = N'push', @status = N'subscribed';EXEC sp_addpushsubscription_agent @publication = 'xTEST', @subscriber = 'SQLOL-01', @subscriber_db = 'xDB', @subscriber_security_mode = 0, @subscriber_login = 'repl_user', @subscriber_password = '##############';Ideally, we’d use integrated security for the replication agent, which could be possible once AD for SQL Server on Linux has been configured, but that’s another test for another day.
Once these scripts have returned, check that the replication agents are running and the new Linux subscriber is either waiting for the initial snapshot or initialising if you have immediate_sync set to true.
Example Savings from SQL Server on Linux (Sept 2017)
Using the Azure calculator as a guide, I’ve selected a D5 v2: 16 Cores, 56GB RAM for one month. In terms of our sweating assets, I’d prefer 72GB of RAM, but you can’t win them all. Anyway, based on this configuration and ignoring SQL Server licencing costs for now; moving to Linux would reduce our VM costs by 43%!!!!
Example Savings from SQL Server Web Edition (Sept 2017)
Slightly off-topic but as I’ve mentioned Web edition already, moving to SQL Server Web Edition for the same VM would reduce SQL Server licence costs by 43%, which means that you could have 3 x SQL Server on Linux (Web) servers for the same price of 1 SQL Server Standard on Windows.
