Posts by Phil Grayson

Data & Infrastructure Generalist @xTENLtd | Co-Founder @Aireforge | Volunteer @LeedsData, @DataAndGravy & @SQLBits | Friend of Redgate | Surfer, Biker, Boarder

Aireforge Studio Community Edition

It’s no secret that the SQL Server community is one of the best technical communities going. The amount of free content out there is amazing, from great blogs on almost every feature available, to direct access to experts via #sqlhelp on Twitter, or on the SQL Server Community Slack channel (join here) and the free events like SQLBits and SQLSaturday.

There’s also loads of great free scripts and programs out there like sp_whoisactive, Ola’s maintenance scripts and the awesome dbatools project, and now there’s another free tool to add to your armoury; Aireforge Studio Community Edition.

Free & Unlimited

Our instance comparison tool, Compare, and our multi-server scripting tool, Script, have always been free; but limited to 2 instances of SQL Server. Starting with v.1.1.7, this limit was lifted, so you can now use it against an unlimited number of SQL Server or Azure SQL Database instances. Since then we’ve also added, Estate, our RDP / SSMS manager and Checks, our lightweight health check module; both are also free and unlimited.

Screenshots

Estate

Organise servers into tags, colour code them, change the columns on the overview and even launch Remote Desktop Manager (RDP) and SQL Server Management Studio (SSMS). Any changes here will be instantly propagated out to your colleagues, if you’re using a shared profile. This is especially useful for servers with complex names or when ensuring that all members of the team share a single view of the estate, using tags, notes and colours to reinforce the purpose of each server.

Checks

A lightweight health check module that can analyse hundreds of servers in minutes, or seconds if you choose individual checks over the full scan. The default health checks are designed to find the most common and most worrying issues, but it’s also possible to create your own custom health checks.

Compare (inc. CLI)

The original instance comparison tool and obviously the best, starting life as OmniCompare in 2015. Now Aireforge Compare, it’s able to quickly scan and display the differences between hundreds of servers.

Most users will only compare two or three instances, but we wrote this to maintain large replication environments, where every subscriber needs to be perfectly synchronised with each other. Another great use case is synchronising availability groups, especially agent jobs and users.

Compare currently ships with 45 checks, each one customised for specific versions of SQL Server. Like health checks, you can even create your own custom comparisons.

Configuration Snapshots

Snapshots enable you to create a point in time record of the instance, which can then be shared with colleagues or saved for reference purposes. Use cases include: attaching the snapshot to health checks, performance reviews or with each release, creating a record of the state before and after.

Four ways to use instance snapshots

OmniCompareCLI.exe

Yes, it hasn’t been renamed yet but the OmniCompare CLI enables developers to automate the comparison within Aireforge Compare. Add a check for server objects into your deployments scripts or automate a comparison against a snapshot, highlighting any unexpected configuration changes.

Script

The Microsoft Central Management Servers (CMS) and Remote Desktop Manager are great, but they’re lacking functionality and haven’t had much attention in recent years or are no longer maintained. So, given that Aireforge users already have their estates loaded into their profiles (which can be shared), we thought it made sense to build a scripting module into Aireforge Studio too.

Optimize (free trial)

This module isn’t free, so we won’t talk about it for too long, but you get a free 14 day trial of it with Community Edition. Basically, it’s an advanced health check and best practice analyser which not only gives you the scripts to fix your issues, but also points you to some of the great articles that we talked about at the start of the blog. This not only increases your knowledge but helps to make a more informed decision about the changes the application recommends.

Photo by William White on Unsplash

Why you should love your connection strings

Who is responsible for configuring and maintaining connection strings within your organization? Developers, operations, the database team? For me, the correct answer should be everyone – each team has an important part to play in configuring optimal connection strings. Unfortunately in most organizations, once they’re configured that’s it – but there’s so much to gain from giving them a bit of love.

Connection strings are essential to every application – without them we can’t connect and nothing works. So it’s pretty obvious when they are wrong, but what about when they’re just not quite right? Optimizing your connection strings can improve the performance of your application and databases. Using the correct options will make them more secure, faster, require fewer resources, simplify maintenance and reduce the time it takes to diagnose issues.

We’ve create some connection string generators for .NET and JDBC, which you may find useful.

SQL Connection String Generator for .NET
SQL Connection String Generator for JDBC


Basic Settings

There’s an evergrowing list of options available to tune connection strings to suit your application, but it’s difficult to know what does what. This is made worse by most settings having 2 or 3 synonyms, just to keep you on your toes! To help with this, we created an online Connection String Generator which reduces some of the complexity and builds your connection string for you.

You don’t need much to get a connection string working. Most settings have a default if you don’t explicitly set them. Therefore, the minimal amount you need is simply the server name and how you wish to authenticate.

Data Source=database_server;Integrated Security=false;User ID=sql_user;Password=password123

Another setting that I’d like to class as a basic one is Application Name. It won’t improve the performance of your application but you’ll be glad you set it when you’re diagnosing server issues. Application name simply populates the program_name field in sys.dm_exec_sessions. So the next time your application is overloaded or having issues, you’ll know exactly what program on the host is causing it.

Application Name=Steves Test App;

Network and Security

Most people connect to SQL Server using SQL authentication purely through habit. Instead, try running your applications under service accounts, then connecting to SQL Server using integrated security. Here’s why.

Connecting via integrated security removes the requirement of storing passwords in plain text web.config files. Although these can be encrypted, you don’t have the same level of control over these. For example, web.config files may also be stored in source control or emailed around in zipped up folders of the application, which enables more people to gain control to your production systems.

Using Windows authentication over SQL authentication also removes the requirement of synchronizing security ids (SID) between availability group nodes. If you’re not aware of this issue, here’s a blog on synchronizing AG nodes, but if your SIDs are not synchronized, you won’t be able to authenticate after a failover.

Integrated security is simple to implement and much easier after the first time, so please have a good stab at it. Most applications can be updated using the same method when altering the service account for SQL Server and SQL Server Agent. You could go one step further by adding the service accounts that require similar access into AD groups, which will make maintenance and administration easier.

Data Source=database_server;Integrated Security=sspi

Network settings are an area you can get lost in and cause some interesting application problems, but there are few that should be considered when building connection strings.

Setting connection timeout is one. You may not want to wait for the default 15 seconds for new connections if your application tries to log thousands of requests a minute. On the other hand, you may want to allow 60 seconds to connect to a server on the other side of the world.

Connection Timeout=60;

Once the connection is established, subsequent queries will be quicker as the application will reuse the connection. This is where connection pooling comes in.

Connection pooling is a blog in itself but there are three main settings you should consider. Min pool size and max pool size control the size of the connection pool, so how many connections you want to keep open and how many connections you’re likely to use. The main gotcha here is that max pool size has a default of 100, so if you don’t set it, this is what you get. If you’re happy with 100 then actively set that, so you can see what it is rather than relying on secret knowledge.

Creating connections to a database can be expensive. Imagine that person on the phone in videos of the stock exchange. Instead of keeping their colleague on the phone and relaying commands, they dial the phone number of the other person every time, say hello, give them the command to sell (or order pizza? I’m not actually sure what they do) and then say goodbye.

So, we want to keep them open, but there’s a balance. Too few connections will starve your application, but too many can consume precious resources – from using memory in SQL Server, to playing havoc with your load balancer by exhausting the number of concurrent connections allowed and causing issues with load distribution (covered next).

Min Pool Size=10;Max Pool Size=100;

The third option I think you should consider is Connection Lifetime. Its synonym, Load Balance Timeout, gives us a pretty good clue to why you should set it. With pooling, we create a connection and leave it open so it can be reused. This is great until we need to add a new server in a distribute the load. Let’s say we have a hundred connections to servers 1, 2 and 3 but none to 4 as it’s just been restarted. Once this server comes back online it won’t get a new connection until the application needs to create one, and if we’ve set our pools correctly, it won’t.

Setting the connection lifetime tells the application to kill a connection after a certain length of time, giving the load balancer the opportunity to evenly distribute the database connections and therefore load.

Connection Lifetime=300

Complete Connection String

There are so many options that can be set and I haven’t gone into enough detail (more blogs to come) but if we use the Connection String Generator and set the options we’ve discussed in this thread, we’ll end up with the following:

Data Source=database_server;Application Name=Steves Test App;Connection Timeout=60;Integrated Security=sspi;Min Pool Size=10;Max Pool Size=100;Load Balance Timeout=300

Connection String Generators


Four ways to use instance snapshots

Instance snapshots can be invaluable to anyone working with servers. From tracking differences over time, to change deployment, here are some ideas on how they can help you. Then, discover how you can use the free tools in Aireforge Studio to implement them.

Tracking differences over time

By taking a snapshot of your servers’ configuration at the right moment, you can use it as a baseline for comparison in the future. This is helpful when:

  • You’re taking a holiday and you want to know what happened while you were away.
  • You want to capture the ‘before’ of a client’s servers prior to a health check. You might add this to a report to show the improvements you’ve made.
  • There’s a change freeze and you’d like extra reassurance nothing has been amended by accident.

Change deployment

When you’re deploying changes:

Take a snapshot prior to any changes to use as a configuration back up. Then, when you’re making changes, you can test them against an exact recreation of your servers, helping to minimize mistakes when you go live. You can also do this during performance tuning.

When someone else is deploying changes:

With a snapshot, you’ll be able to see any impact others’ changes may have had on your servers by comparing a pre-changes snapshot with post-release servers.

Remote troubleshooting

Working with servers that you don’t have access to? Get your customer to capture a configuration snapshot, then use this for analysis, comparison and more. This way, you’ll get the information you need, save support costs and reduce turnaround time.

Golden configuration template

When you’ve got a server into an ideal state, you might use a snapshot of it to:

  • Share configurations with colleagues as an example of best practice.
  • Use as a reference or template for future server setup.
  • Use as future reference for the same server if it experiences performance issues later down the line.
  • Create example files to distribute with your software to aid installations.

How Aireforge Studio helps

In Aireforge Studio’s Compare module (totally free), you can capture and save a snapshot. Then, use it for comparison against the same servers (for comparing server differences over time), comparing against other servers (useful when using your server as a golden template, or for viewing and sharing.

Taking a snapshot

From the Compare tab, check the server/s you want to take a configuration snapshot of.  Press the ‘Snapshot’ button, then choose ‘All comparisons’ or ‘Selected comparisons’ (depending on whether you want to snapshot all configuration or just a subset).

Snapshot button

You will then be prompted to save the snapshot to file. That’s it! You can now use this saved snapshot to compare against any of your servers within Aireforge Studio.

Using a snapshot

To compare the snapshot against the same servers captured within it, go straight to the next step. To compare against other servers, select those you wish to compare against first.

Press ‘Load’ and select the snapshot to open. If you recently took the snapshot, it will appear on the drop-down menu.

snapshot 2

Next, a dialog will be shown to let you choose what to do with the snapshot:

Loading snapshot dialog
  • Compare with same servers: compares the contents of the snapshot against the current state of the same servers.
  • Compare with currently selected servers: compares the contents of the snapshot against the currently checked servers on the Compare tab.
  • View only: simply opens the snapshot for viewing, without taking any new snapshot.

When comparing the snapshot against any servers, the comparison will be against the current state of the servers. You will now be presented with the comparison results or a view-only snapshot file.

All of the functionality in this post is free. Download yours at aireforge.com

Image courtesy of https://unsplash.com/@makariostang

Continue reading →

Aireforge Studio 1.1.9

A lot of work has gone into this release but many of the changes won’t be immediately obvious as we’ve;

  • Changed and standardized every button.
  • Relaxed the server limits for Pro versions.
  • Improved the handling of licensing issues.

A good example of the changes can been seen in the splash screen. Not huge changes but they’ve been made everywhere. Another example is the comparison configuration screen.

Improvements to Advise

  • Added partial support for column store indexes.
  • Ignore fragmented indexes now based on pages rather than rows. Defaults to Microsoft recommended value of 1,000 pages.

New Checks

  • Database owner does not exist.
  • Auto update statistics is disabled.
  • Data files larger than 10GB with more than 90% free.

Improved Checks

  • Cost threshold for parallelism alerts if set to < 15, but suggests setting it to 30.

New Scripts

  • If page verify is NONE or TORN_PAGE, suggest setting to CHECKSUM.
  • If auto shrink is enabled, suggest setting to disabled.
  • If auto create statistics is disabled, suggest setting to enabled.
  • If auto update statistics is disabled, suggest setting to enabled.
  • If auto close is enabled, suggest setting to disabled.
  • If the compatibility level isn’t the latest, suggest setting to the latest but with a warning.

Improved Scripts

  • The suggested size for new tempdb data files are now set to the total space / existing files * number of suggested files.

Multi-server scripting with Aireforge Studio

We’ll keep this short, but we’ve created a scripting tool for running queries against multiple SQL instances via Aireforge Studio. Oh, and it’s FREE.

Script.png

The Microsoft Central Management Servers (CMS) are great (link), but they’re lacking functionality and haven’t had much attention in recent years. So, given that Aireforge users already have their estates loaded into their profiles, we thought it made sense to build a scripting module into Aireforge Studio. You can use the same servers and groups from the estate manager or select individual servers; giving you more control over which scripts are run and where. You can also export to CSV, validate your queries before running them and change how the results are displayed or grouped.

We have a healthy backlog in JIRA for Script, but any feedback is always appreciated. If you think that we’re missing something, please let us know (via Wantoo) and we’ll do our best to implement it.

Enjoy.

New Advise module and Aireforge Studio v1.0.7

What’s new at Aireforge?

We’re pleased to announce that Aireforge Studio is now officially live and supported. Version 1.0.7 ships with hundreds of improvements with a new UI, simpler comparisons with advanced options, 2017 support and our new tuning / best practices module; Advise.
Depending on your version, the application should prompt to update after starting. If not, please download the latest version using the link below.

Introducing Advise

Download images

The new Advise module within Aireforge Studio analyses instances, databases, tables and indexes, before providing recommendations. The growing list of checks vary from best practices and naming convention violations to orphaned users and missing, duplicate or overlapping indexes.

Read more…

Compare v1 & Custom Comparisons

Download images

The Compare module within Aireforge Studio 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).

Common use cases for Aireforge Studio Compare are checking production databases against staging and / or staging against test, auditing the SQL Server estate, sharing configurations with colleagues / support teams.

Read more…

Viewing configuration settings without access to SQL Server

Have you ever been asked to help with a database issue but you can’t get access? Maybe you’re helping out a colleague over Skype, responding to a #sqlhelp request or asking for help yourself and want to provide more information. Quite often, there’s a simple configuration setting that may not be optimal but checking every setting could be time consuming and something could be missed (there’s a lot of settings).

You could start by running some scripts like sp_blitz (really useful) but they don’t always give you the complete picture or you need to extract all settings to store, share or compare them. Aireforge Studio can take an in-depth snapshot of a single instance or the entire estate. This can be really useful to do before or after carrying out any changes or if you need to share your configuration with someone else. You could also attach the saved comparison file to the ticket you’re working on, so you know exactly how it looked before and after any changes were made.

Here’s a quick tutorial on how you could share configuration information about your SQL Server instance or instance(s) with another person or organisation.

Taking a configuration snapshot

If you’re new to Aireforge Studio check out the getting started pages, although it’s a quite simple application. Once downloaded, create a profile and add the server(s) in question.

Go to the Compare tab and select the comparisons you wish to run or select Full to run them all. A full comparison should finish in a few seconds and won’t display any results if you only compared one server or if all of your servers are identical. Now save the results as an .afd (Aireforge Data) file and send it to the other party.

save

Viewing the results

The receiver will also need to have Aireforge Studio installed. Once installed you can either double click on the .afd file to open, or load the file from within the application. To view the results change Differences to All Rows and the View to Grid. If information has been collected from more than one server, you could also use the summary view to quickly see any differences.

results

Aireforge Studio performs a lot of preconfigured checks but if it doesn’t gather the information you require, you can create your own custom comparisons.

Any problems, however small, please contact Aireforge support.

You can also use our Wantoo board to request new features or improvements?