Comparing SQL Server configurations with Aireforge Studio

For ease of maintenance and performance reasons, we always aim to standardised our SQL Server estates (some of which contain over 100 instances). Everything is synchronised for their specific workload pools, so all search servers and all reporting servers are identical etc. Not only do we standardise the hardware or the VMs, but we ensure that every setting bar the IP and name are identical. This is easy is to achieve if you’ve recently built your estate from clones, but after a while the server configuration can drift, which could impact the performance and / or stability.

When you have an issue on standardised kit, your first question is usually what’s changed or how is that server different to the others. With over 100 instances to check, this can take a while to diagnose, but you usually find that a setting has changed or a job has been disabled. If you don’t find anything, then you still have an answer and can focus your time elsewhere. You can get a long way with scripts, either dumping to a central table or using CMS and / or using policy-based management, but we found that these approaches still didn’t cut it.

Aireforge Studio instantly compares a large number of settings and uses some logic to ignore false positives, such as differences with server names within jobs. The list of what it compares increases with every release but here’s an overview.

What does Aireforge Studio compare?

  • Naming (server, instance etc.)
  • Version information (version, edition, service packs, cumulative updates etc.)
  • Instance configuration settings (memory, fill factor, max worker threads, recovery interval, CLR settings, compression settings etc.)
  • Instance properties (clustering info, edition, service pack & patch level information etc.)
  • Database settings (auto shrink, snapshot isolation, page verify option, CDC, CT, compatibility level, recovery model etc.)
  • Database file settings (auto growth, initial size, naming, state)
  • Users
  • Registered servers
  • Assemblies (creation / modification date, CRL name etc.)
  • Service information (account, start up type (manual or automatic))
  • Registry information (Port, working directory etc.)
  • Server (DDL) triggers
  • Endpoint information
  • SQL Server Agent Jobs

Compare

Examples of what you can use it for.

  • Full comparisons or partial comparisons; just checking jobs, versions, users etc.
  • Synchronising the estate. Keep all of your servers synchronised, taking some of the guess work out of service issues and misbehaving servers.
  • Auditing the estate. You can easily compare every server (limited to 500 but contact the team if you need more).
  • Testing / Performance Tuning. Create a backup of the configuration before making changes during testing. Store the output with your test results so you can look back and recreate the exact configuration during the tests.
  • Compare single output files to check for differences over time.
  • Share configurations with colleagues or use saved configurations as a reference.
  • Create example files to distribute with your software to aid installations.
  • Use Aireforge Studio to remotely check customer configurations, reducing support costs and turnaround times.

Comare_Results.png

If you would like to download Aireforge Studio, you can do so by using the following the link.

Download Aireforge Studio

 

Advertisements

Testing WMI for Redgate SQL Monitor

I tend to use Redgate SQL Monitor when diagnosing hard to find issues or when helping teams to better understand their estates. Sometimes, we run into issues with the network during the installation which is usually down the Windows Management Instrumentation (WMI) ports. SQL Monitor uses WMI calls to get all the non SQL Server information from Windows, such as disk, network and CPU usage.

It’s not completely obvious if connectivity is the issue and testing via the application can be difficult due to connection time outs and retries etc. Here’s where WMI Tester comes in handy. It’s a free download that doesn’t require an installation or a separate runtime.

WMITester.png

  • Download the zip from https://www.paessler.com/tools/wmitester
  • Unpack the zip and run WMITest.exe or read PaesslerWMITester.pdf for more information.
  • For testing, I tend to use an admin account to make sure WMI works, then try the domain account used for SQL Monitor to test that the privileges are correct.
  • If it works, you’ll get some results from the default command. If not, you’ll get an error.

If you’re using an admin account and it isn’t working then check the following:

  • Check that the services are started on the remote machine
    • Remote Procedure Call (RPC) should be running and set to start automatically.
  • Check that the ports are open on the local firewall and any in-between.
  • Consider using a fixed port for WMI.

 

Useful links

Redgate help covering port numbers.

Setting Up a Fixed Port for WMI

 

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