SQL Server on Linux and transactional replication

Many companies use transactional replication to scale out their database reads; which can get quite large and very 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 completely and switching to Linux (I’m using Ubuntu).

Implementing (GUI)

Microsoft have 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 (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 = 'grwX3bNuPyYf';

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

Using the Azure calculator as a guide, I’ve selected a D5 v2 : 16 Cores, 56GB RAM for 1 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%!!!!

Windows vs Linux

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.

SQL Standard vs Web