Indexes for SQL Server replication

Distribution databases need to be administered and maintained like any other database, which includes indexes. Microsoft have used index hints in several stored procedures, which limits us slightly, but these indexes have worked well for me on a number of systems and continue to do so.

Indexes for transactional replication

Indexes for merge replication

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

Windows OS Only  $  1,382.35  £  1,030.28
Linux OS Only  $     785.66  £     585.56
Savings from Linux  $     596.69  £     444.72

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

Windows SQL Server Standard  $  2,572.75  £  1,917.50
Windows SQL Server Web  $  1,477.58  £  1,101.26
Savings from Web  $  1,095.17  £     816.24



Scripting pull subscriptions (SQL Server replication)

I’ve created some simple scripts for creating subscriptions and their pull jobs on single or multiple subscribers. It makes life much easier to use scripts, especially if you’re bringing in multiple servers and / or the subscribers are running SQL Server Web edition as they don’t get created automatically via the GUI; which can be confusing.

There are various tools to run scripts across multiple servers, my favourites are SQL Server Central Management Servers or if you want something more advanced and easier to use, try SQL Multi Script from Redgate.

You can also use Aireforge Studio to compare the jobs afterwards, making sure they were created properly and are running. It’s also useful for flagging up other configuration inconsistencies such as missing database objects, users or database and instance configuration settings.

Create local subscriptions and generate publisher scripts

Create the local subscriptions and generate the script for adding the subscriber to the publisher (this will need to be copied out and run against the publisher database).

Create and start the replication agent jobs

Remember to locate and set the location of DISTRIB.EXE. I’ve also included an option to set an alternative snapshot location. Very useful for remote servers, especially if you distribute the snapshot files using DFS-R (search here until I blog about it).

Now check that the agent is working properly and either waiting for the initial snapshot to be created or propagating out any active snapshots.

Please let me know if you spot any issues or would like to improve the scripts.