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.

Error.png

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

 

 

Comparing SQL Server configurations via the command-line

Aireforge OmniCompare 0.9.0 was the first release to ship with the CLI module. This is a separate application, allowing comparisons to be called from 3rd party applications, such as SQL Server Agent jobs, custom scripts and software deployment tools including Octopus Deploy. OmniCompare CLI will then feedback basic information using termination or error codes; much like log shipping alerts.

The information required to perform the comparison and the credentials required to access the servers are stored within the .adc export file, therefore the main OmniCompare application will be required to perform any future changes such as new servers or new / updated comparisons.

 

What is OmniCompare?

OmniCompare performs a growing list of comparisons, which can be restricted if certain values are known to differ, such as core count or edition (e.g. Enterprise vs Developer). All comparisons in OmniCompare are available in OmniCompare CLI.
A common use case for OmniCompare is checking production against staging and staging against test. This manual task could be automated using a SQL Server Agent job or building OmniCompare into your deployment process, checking for differences and aborting the release if any are found.

Comparing trace flags via the command-line

Creating the comparison file

Open OmniCompare and select the servers you wish to compare. Once selected, you can now choose a full comparison or restrict it to individual checks.

For the purposes of this blog, we’ll limit the comparison to trace flags.

OmniCompare selection screem
Selecting servers to check and comparisons to run

Note: Although trace flag comparisons work for both SQL Server and AzureDB, many of them are not interchangeable as AzureDB is a very different database to SQL Server. AzureDB also has many trace flags enabled by default (17 at the time of writing).

 

Checking current values

If no differences are found, nothing will be displayed. Although it’s often interesting to check the current values. This can be done by changing the results filter from Differences only to All.

4_nodiff

Showing all results has highlighted that each instance has the non-standard trace flag 1222 enabled, which enabled by Redgate SQL Monitor to improve deadlock information.

OmniComapre results page showing trace flags
OmniCompare comparisons results without a filter

 

Exporting the comparison file

OmniCompare CLI is a lightweight application, therefore the comparison file must be created using the main OmniCompare application and exported. This exported file contains the servers to check, authentication credentials and the comparisons to perform.

To create the .adc file, select the command line icon at the top right of the comparisons section. You will then be prompted to enter a secure password and the location of the saved file.

We will save this file to d:\tf.adc.

Exporting OmniCompare comparison files
Comparison files are currently password protected

 Download OmniCompare (inc. CLI)

Using OmniCompare CLI

The OmniCompare CLI executable resides in the OmniCompare program folder. The argument for the application will be logged out if none are passed in or the help argument (-h) is use.

OmniCompare CLI Commands
omnicomparecli.exe -h

Performing a CLI comparison

To perform a trace flag comparison against the servers detailed above we simply pass in the comparison file and password.

For the purposes of this blog I have enabled full logging, although this should be used for debugging only as a lot of information is logged; especially during full comparisons of large estates.

omnicomparecli.exe -i d:\tf.adc -p <password> -v full
CLI_NoDiff.png
Successful OmniCompare comparison with error code 0

This command could then be run from a SQL Server Agent job or from software deployment tools or from your own custom scripts.

Handling differences

We will now enable trace flag 1117 on the SQL2014 instance to demonstrate how the application changes. OmniCompare CLI has highlighted the change and returned an error code of 1. This error code could be caught and an alert raised or a deployment aborted.

Unsuccessful OmniCompare comparison with error code 1
Unsuccessful OmniCompare comparison with error code 1

 

The information detailed above could be logged to file using the -o command, although a simpler option would be to run the comparison via the OmniCompare application.

OmniCompare difference in trace flag
TF1117 enabled for DEV1\SQL2014

Summary

Discrepancies between configuration settings or database objects can be difficult to identify, especially with larger estates or multiple developer environments. Custom scripts or policy-based management could be used to check some of these settings but they will require updating and only work if the script or rule has been created.

OmniCompare checks a large and growing number of settings, highlighting unexpected changes and preventing configuration drift between instances that should be the same.

Other useful pre-deployment checks

  • Counts of database objects (tables, columns, indexes, functions, views etc.).
  • Server triggers,
  • CLR assemblies,
  • Trace flags,
  • Operators, users or permissions have been applied,
    and database settings such as delayed durability, RCSI etc.

Download OmniCompare (inc. CLI)