Press "Enter" to skip to content

Category: Administration

Troubleshooting Client Disconnections

Published 2017-12-11 by Kevin Feasel

Mike Hays looks at a trace flag that can help you troubleshoot why client connections drop:

About once a month, I get support ticket regarding SQL Server dropping an application’s or user’s connection.  The problem is SQL Server does not just randomly drop a connection and continue to work normally.  Some force outside the control of SQL Server breaks the connection.  By default, SQL Server does not record when this event occurs.

In my history of working with SQL Server, only in extreme situations have I ever seen SQL Server drop its connections.  The most common example is when SQL Server is in the process of shutting down.

Click through for the trace flag and details.

Comments closed

Upgrading Cassandra To Version 3

Published 2017-12-08 by Kevin Feasel

Mikhail Chinkov has a process for upgrading Cassandra from version 2 to the latest release of 3:

At first sight it should be obvious. Cassandra is a distributed storage and you’re able to upgrade each node independently. But also it’s a kind of tricky, because Cassandra has so many concepts and moving parts. Introducing such a major change, you’ll be probably excited about how not to break one.

Also, as with every DB upgrade, the most important outcome will be your app behaviour. Protocol versions support might be removed from the future versions. Storage might work another way application doesn’t expect. There might be a lot of pitfalls. So, to start getting the benefits of upgrade, we have to be 200% sure that the application works. And at least it won’t work worse with database.

The whole process is straightforward but there do seem to be a couple places where you can shoot yourself in the foot.

Comments closed

Updating @@SERVERNAME

Published 2017-12-08 by Kevin Feasel

Eitan Blumin has a script to change what you get when you reference @@SERVERNAME:

If, for whatever reason, the Windows Computer Name is changed after SQL Server is already installed, then @@SERVERNAME and the information in sysservers would not automatically reflect the change.
This means that @@SERVERNAME contains the incorrect value for the machine name.

Sometimes, and especially in production environments, the value in that global variable is important and is used as part of business processes.
And if @@SERVERNAME doesn’t reflect the actual server name, it could cause problems.

Read on for that script.

Comments closed

Process Mapping On Linux With SQL Server And Oracle

Published 2017-12-05 by Kevin Feasel

Kellyn Pot’vin-Gorman contrasts SQL Server versus Oracle outputs when running a couple common Linux process commands:

In our Oracle environment, we can see every background process, with it’s own pid and along with the process monitor, (pmon)db writer, (dbwr), log writer, (lgwr), we also have archiving, (arcx), job processing, (j00x) performance and other background processing.  I didn’t even grep for the Oracle executable, so you recognize how quickly we can see what is running.

In the SQL Server environment, we only have two processes- our parent process is PID 7 and the child is 9 for SQL Server and nothing to distinguish what they actually are doing.  If we decide to use the pmap utility to view what the parent and child process aredoing, we see only sqlservr as the mapping information.

I imagine that things like this will improve over time for SQL Server, but Oracle definitely has a leg up in this regard.

Comments closed

Using Pester For Configuration Checks

Published 2017-12-01 by Kevin Feasel

Andrew Pruski shows how to use Pester to audit SQL Server configuration settings:

One Pester test running!

What I like about this is that it can be easily dropped into a job scheduler (e.g.- Jenkins) and then you’ve got a way to routinely check (and correct) all the configuration settings of the SQL instances that you monitor.

Pester would not have been my first thought for configuration checking, but it does serve as another useful option.

Comments closed

Against Simply Closing Connections With Rollback

Published 2017-11-28 by Kevin Feasel

Kenneth Fisher shows how to close all database connections and immediately roll them back, but then explains why you shouldn’t default to that:

But, let’s take a minute and think about this a bit closer. Should we? Should we dump everyone out of the database? Are you sure?

  • Well, it’s dev and we’ve talked to the development team. No one is doing anything right now.
  • It’s prod so we’ve been extra careful. We arranged an outage with the business and confirmed that now is a good time.

Well, in theory, we’ve been told no one is using the database. However, that’s theory, this is reality, and in fact, someone is in the database. But what are they doing? Is it just a leftover connection? Someone left a query window open and forgot about it? The application creates a connection and holds on to it 24×7 even if it’s not doing anything?

Click through for more thoughts on the topic.

Comments closed

Database Migrations With DBATools

Published 2017-11-22 by Kevin Feasel

Jason Squires has a series on database migrations using dbatools.  The first part covers capacity planning:

Great results, I now know that I cannot move the database from one server to another on any of the drives shown without expanding the drive volume.  Even though this was an awesome output, I wanted more. I know my data and log drives that I wanted to focus on so I decided to dump everything into an array.

The second part is the actual migration:

In this post, I am going to show you the steps that I decided to take to do this particular migration.  This migration was going to be a bit different. I am merging two systems together, so I had to really think the process through and ensure I get everything I needed. I also did not want to rollback due to the systems being legacy and unsupported. Some of the hosts were going to be pushing storage limits so I had to ensure everything went flawlessly so I could decommission the legacy systems quickly.

Both of these have scripts and explanations attached, making it easy to follow along.

Comments closed

Alerting In Azure SQL Database

Published 2017-11-17 by Kevin Feasel

Arun Sirpal shows how to set up an alert for an Azure SQL Database:

I keep things simple and like to look at certain performance based metrics but before talking about what metrics are available let’s step through an example.

For this post I want to setup an alert for CPU percentage utilised that when it is greater than 50% over the last 5 minutes I would like to know about it. First step is to navigate to your Azure SQL Database.

Click through for a screenshot-driven guided tour.

Comments closed

Validating Database Mail

Published 2017-11-15 by Kevin Feasel

Frank Gill has a script to validate that your database mail settings are valid:

In my last post, I shared a script to automate the migration of SQL Server Database Mail settings. In this post, I show how to send test e-mails from all Database Mail profiles on an instance. The migration I was working on contained 21 Database Mail profiles. The following script will send a test e-mail from each profile to confirm successful configuration. I hope you can put this code to use in your migrations.

Click through for the script.

Comments closed