This should fix the problem encountered with SQL Server 2019 agent in CU2 (along with some other important fixes)
.NET Core Standalone Executable
.NET Core 1.0 came out June 27, 2016. 4 years later, and who knows how many hundreds of thousands of person hours development, I figured it would be quite mature.
On that premise, feeling quite hopeful, I decided to see what’s involved in converting a .NET 4.7.1 standalone console application to .NET Core 3.1, which you’d think would be relatively straight forward.
Three hours later, my 5MB standalone console application has ballooned to 74MB! If you select ‘PublishTrimmed=true’, then the size drops to 44MB but then the application doesn’t work. Apparently, trimming is not able to work out what’s needed, even when reflection isn’t involved.
Turns out even the un-trimmed 74MB app. still doesn’t work as you can’t use the built-in encrypted connection strings section in app.config file. (It hasn’t currently been implemented in .NET Core, along with DbProviderFractory, and a few other surprises…)
I went looking for resources and other people’s experiences converting to .NET Core.
https://docs.microsoft.com/en-us/dotnet/core/porting/
https://docs.microsoft.com/en-us/dotnet/standard/analyzers/api-analyzer
https://github.com/hvanbakel/CsprojToVs2017
https://ianqvist.blogspot.com/2018/01/reducing-size-of-self-contained-net.html
Scott Hanselman gets really excited about making a 13MB+ “Hello world” .Net Core application. He even calls it tiny!! (and that’s after he got it down from 69MB). His post starts out with the line “I’ve always been fascinated by making apps as small as possible, especially in the .NET space.” Irony, or what? In what kind of insane world is a “Hello World!” application 13MB!?!
On a tangential side note; just ditched ILMerge for creating standalone executables. In the past I’ve used Jeffrey Richter’s technique of embedding assemblies in the resource manifest, adding a startup hook to load assemblies into the app. domain at runtime, but like a FOOL, I thought that ILMerge was the ‘better’, more .NETway of doing things.
The amount of pain ILMerge has caused me over the last few years is staggering. It has to be one of the most fragile tools out there. If the planets aren’t aligned it spits the dummy. If there’s ever a problem it spits out an unhelpful cryptic “exited with error X” message. Good luck finding the problem!
Just moved over to using Fody/Costura; it uses that same technique of embedding assemblies in the executable.
It worked the very first time! Unlike ILMerge. As an added bonus it automatically compresses/decompresses assemblies, and my .NET 4.7.1 standalone executable is 2 MB smaller!
SQLFrontline: Snapshot SQL Server Configuration
Taking a snapshot of a SQL Server’s configuration, enables you to see what changes over time. It can also provide a record of the date changes were made, so that you can correlate if problems occur and determine if a change might be to blame. It’s also a great way to document any fixes you have made.
An example: some months ago I had generated a SQLFrontline report against a server I had been asked to look at and update to industry best practices. Some time after the work had been done, I re-ran the report and discovered that someone had turned on SQL Server’s ‘Priority Boost’ setting since the previous data collection! (You should never turn this setting on):
“Raising the priority too high may drain resources from essential operating system and network functions, resulting in problems shutting down SQL Server or using other operating system tasks on the server.”
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-priority-boost-server-configuration-option
SQLFrontline currently performs 310+ checks looking at reliability, performance, configuration, security, database design and emails you the results, with clear instructions on what needs attention.
SQLFrontline: Server Overview
Have you just been given a bunch of SQL servers that you’re now responsible for? Do you want to get a really fast overview of each server’s hardware, SQL server version and service pack update, configuration, database sizes and usage, performance problems, weak passwords?
Want the results emailed to you in a prioritised, easy to read format?
SQLFrontline can do this with just a few commands. Behind the scenes it runs hundreds of lightweight, metadata collection queries against all the specified servers/databases (no user data is collected). SQLFrontline currently performs 300+ checks across the categories of Reliability, Performance, Configuration, Security and Database Design
SQLFrontline Case Study: Failing Backups
Over the course of 20 years dealing with SQL server, I’ve come across failing backups more times than I’d like to recall. (And that’s not counting the times there were no backups setup in the first place!)
In the case of failing backups, backups were set up, checked to be working, and then at a later date subsequently failed to notify of backup failures for several reasons (non-exhaustive):
- Configuration on the SMTP server changed, such as the allowed IP white list for forwarding, permissions changed, or the actual SMTP server changed.
- Virus scanner configuration changed preventing emails to be sent.
- AD group permissions changed or SQL server service identities changed.
In all these cases, backups were failing but no one was being alerted and no one was periodically checking the SQL agent logs.
SQLFrontline checks for no backups in the last 7 days, backups done without compression (compressed backups take up less space obviously, but are also faster to backup and restore), backups done without verifying page checksums, and backups done without encryption (if your version of SQL Server supports it). It also checks that you are periodically running DBCC CHECKDB to maintain database integrity, and whether any data corruption has been detected (automatically repaired or otherwise).
SQLFrontline currently performs 62 Reliability checks on each SQL Server you monitor, with 300+ checks performed across the categories of Reliability, Performance, Security, Configuration and Database Design.
Side Note: Another thing to consider is, do you delete older backups BEFORE making sure the latest backup succeeded? If so, you might end up with no recent local backups at all when your backup job starts failing… If you use Ola Hallegren’s maintenance solution scripts, this check is performed correctly for you.
Six Sentence Emails that Get Fast Responses
Great advice from Brent Ozar: Six Sentence Emails that Get Fast Responses
SQLFrontline Case study: NUMA Configuration
A little while ago, I was doing SQL Server consultancy work for a large organisation here in Perth, Western Australia. They had a sizeable physical SQL Server machine for their business reporting needs: 48 cores (4 sockets of 12 cores each and 384GB of RAM).
I discovered that despite the company having paid for Enterprise licenses for 48 cores, the licensing had not been applied, and so only 40 cores were actually in use! It had been that way for almost 2 years…
In fact, the situation is worse than it first seems: not only were 20% of the cores not being used, but the cores were partitioned into 4 NUMA groups, having 3 groups of 12 cores and one group of 4 cores. With the workload distributed equally over the 4 NUMA groups, this was obviously very unbalanced, and detrimental to the server’s throughput.
In addition, ‘max degree of parallelism’ was set to its default value, which means that a parallel query that spans the 12 core and 4 core NUMA groups will very likely have threads waiting in the 12 core NUMA group and possibly incur foreign memory accesses.
This is just one of the NUMA configuration checks that SQLFrontline runs.
How can SQLFrontline help me as a DBA, Accidental DBA or Developer?
If you’re doing a good job as a DBA you want to have an in-depth knowledge of all the SQL Servers you are responsible for. That includes the SQL Server hardware, configuration and performance. You want to be pro-active and not swamped. You want active notifications of when things change or fail, and ideally you would like this documented, so you can show it to your boss!
Rather than you having to remember to run checks on servers (and when you get busy, it’s easy to miss something), SQLFrontline does this for you on a scheduled basis (usually once per day, but configurable). You don’t have to install any software or configure any local repositories.
As an accidental DBA (despite reading blogs and investing time learning SQL Server) you might not be aware of everything required to make SQL Server reliable and perform well. SQLFrontline embodies SQL Server industry best practices. Not only does it notify you of problems found, but it also explains the problem and shows you how to fix them.
As a developer, you want to be made aware of any design decisions that could affect performance, and/or track down any existing performance/blocking issues.
I use SQLFrontline in my day-to-day SQL Server consultancy business. It generally takes me less than an hour to do what would have previously taken over a week (running and documenting the results of over 300 checks can be time consuming). I recently used SQLFrontline to efficiently diagnose and fix all the SQL Servers for an entire business, making them standardised and industry best practice in the process.
SQLFrontline: a Diagnostic and Monitoring Service for all your SQL Servers
If you’re an overworked DBA (or a DBA that wants to be responsible for more servers and databases), an accidental DBA, or a developer wanting to get a better handle on your SQL Server and databases, SQLFrontline can help you do that.
SQLFrontline is a lightweight, low impact, nothing to install diagnostic tool for all SQL Server versions 2008+. It carries out over 300+ checks (more checks are added frequently), across the areas of security, reliability, performance, configuration and database design. Issues are organised by server and by priority (Critical, High, Medium, Info).
It compares and notifies you what was fixed between data collections. It has a built in reminder list for high priority items that haven’t been fixed over time.
It even has a demo mode, so you can try it out for free.
Here’s an excerpt from an example notification email:
Managing Performance Enhancing Indexes for Third Party Databases
IT staff are sometimes reluctant to make any changes to third party databases, as they are worried that doing so will invalidate their support agreement. But sometimes it’s essential to create indexes for third party databases to improve performance/blocking.
You might ask, “Why doesn’t my third party database vendor have all the required indexes in place already?”
There are several reasons:
1) The third party’s developers and testers don’t always have access to large enough data sets.
2) End users (you) might not all have the same usage patterns, data distributions or data volumes.
3) End users might not have channels to give the vendor performance feedback.
Assuming we have identified an index (or indexes) that would increase performance, we want to apply these but not interfere with any future upgrades supplied by the vendor.
The simple solution is to create two scripts in source control: one to create required indexes, and another to remove those indexes prior to any vendor upgrades.
After a vendor upgrade, we examine the database’s indexes, and if the vendor still hasn’t created the indexes we require for our workload, we simply re-create our own version again.