Conditional Alerting

Dave Mason revs up SQL Server alerts using tokens and conditional responses:

There are three tokens within the T-SQL (highlighted in yellow above): A-MSG, DATE, and TIME. SQL server replaces these three tokens as follows:

 

  • A-MSG: Message text. If the job is run by an alert, the message text value automatically replaces this token in the job step.
  • DATE: Current date (in YYYYMMDD format).
  • TIME: Current time (in HHMMSS format).

See the MSDN documentation for a list of tokens and their descriptions.

 

This is a great way of being smarter with alerts.  Your SQL Server instance has a lot of information at the ready, so get familiar with what’s up for offer.

How To Shrink A Database

If “shrink that database” makes you cringe, that’s a good sign.  Nevertheless, sometimes it has to happen.  On that rare occasion in which it needs to happen, Andy Mallon gives you a how-to guide:

Perhaps you just implemented data compression, and have 60% free space in your database.
Perhaps you just deleted a bunch of old data, and have significant free space in your database.
There are a handful of scenarios where you have free space in the database, you’ll never use it, and you need to shrink. Just don’t make a habit of it.

Shrink should not be part of your normal routine, but it does come in handy sometimes.  Auto-shrink, though?  Burn that to the ground.

Windows Server Core And SQL Server

Slava Murygin walks through running SQL Server on Windows Server core.  First, the steps for installing SQL Server at the command line:

There are three reasons why Windows Server with Core is better than Server with a GUI:
1. Security: Core has less features. That means less surface for intruders to attack.
2. Management: No unnecessary updates and planned reboots.
3. Resource consumption: Less memory usage and less CPU overhead

Even if you aren’t using Windows Server Core, it’s a good idea to be able to script out your SQL Server installation so you can automate it later (or at least so you don’t forget options and have some servers look different than others).

Second, Slava notes that certain features are not compatible with Windows Server core:

The following error occurred:
You have selected a feature that is not supported on Windows Server Core.
The supported features on Windows Server Core are:
Database Engine Services,
SQL Server Replication,
Full-Text and Semantic Extractions for Search,
Analysis Services,
Client Tools Connectivity,
Integration Services,
and SQL Client Connectivity SDK
.

That list is a bit more limiting than I’d like, to be honest.

Powershell Remoting

Andrew Pruski demonstrates Powershell remoting:

Hey guys, differing from usual this is a quick post on setting up powershell remote sessions. I know you can remotely connect to powershell sessions using the Server Manager that comes with Windows Remote Administration Tools but it’s a bit of a clicky process and I like to eliminate using the mouse as much as possible.

Disclaimer! I’m not a scripter, there are probably much better ways of doing this but I’ll show you the way I set it up and how to fix any errors you may come across.

If you’re using Remote Desktop to connect to servers, especially for regular actions, you should definitely check out Powershell remoting.

Truncating Log Files

Sean McCown has a script to truncate log files:

I want to show you a great piece of code to truncate and shrink all your log files. The biggest question you always ask is why should you shrink your log files? It’s been drilled into everyone’s head that shrinking log files is bad because you can cause too many VLFs, and of course there’s the zeroing out that happens when they grow.
OK, so let’s answer that question. There are a couple reasons you’d want to shrink all the files to a small size.

Regularly shrinking log files in production isn’t a particularly great thing, but as Sean points out, there are valid reasons for doing this.

Migrating TFS

Dave Mason has notes on migrating TFS from one server to another:

If you are migrating, but want to keep the databases on SQL 2012 Express, then you can skip this part. I wanted them moved to my SQL 2014 instance. So I did a traditional backup/restore from SQL 2012 Express to SQL 2014. I took new backups of the SQL 2014 databases, and then uninstalled SQL 2012 Express. Then I had to configure TFS to connect to a different SQL instance. Within the web.config file (%ProgramFiles%\Microsoft Team Foundation Server 12.0\Application Tier\Web Services\web.config), I found an application setting named “applicationDatabase”. I made a backup copy of web.config first, then I changed the “applicationDatabase” value. It should be in a recognizable format if you’re familiar with SQL Server connection strings. You can also make this change within IIS. It was there that I noticed a few other settings that contained SQL connection strings. Check out the following in IIS and change settings as needed:

Dave has lots of screen shots to make the process easier to understand, but my main takeaway is that for the most part, migrating TFS  is a huge pain…

Max Server Memory

SQL Sasquatch explains what “max server memory” does and how it relates to private bytes:

Well, there’s obviously a relationship between private bytes and “total server memory”.  The peaks and valleys occur at the same time.   But there’s a delta between them, and the delta isn’t constant.

The difference between private bytes and “total server memory” is pretty well confined, too – not varying more than 10 mb in this interval.

Read the whole thing.

Against DBCC Commands

Erik Darling doesn’t like (most) DBCC commands:

Not what they do, just that the syntax isn’t consistent (do I need quotes around this string or not?), the results are a distraction to get into a usable table, and you need to write absurd loops to perform object-at-a-time data gathering. I’m not talking about running DBCC CHECKDB (necessarily), or turning on Trace Flags, or any cache-clearing commands — you know, things that perform actions — I mean things that spit tabular results at you.

I completely agree.  One of the nicest things about SQL is that I can use the same syntax to read metadata that I can data.  DBCC commands are a jarring difference.

Azure Status Alerts

Ron Dameron has built Outlook rules for Azure status alerts:

I do have alerts set up on the Azure portal and in Application Insights to notify me when availability or performance thresholds are violated but I also need to know if there is a global or regional issue that might affect our app so that I can respond and notify the staff when appropriate. Azure status changes are reported on the Azure Status web page.

The following will describe how to use the Azure Status page RSS feeds and Outlook rules for notification if things go sideways in Microsoft Azure.

This is a good use of Outlook’s built-in RSS reader.

Change Management Template

Kendra Little walks through a fairly simple but very useful change management template:

Good Change Management makes your team smarter. When you change things a lot, things are going to break sometimes. If you’ve done a good change request, you’ll know exactly what to do when something breaks: either you’ll roll the change back, or have a Plan B to execute on. Good change requests also mean that business owners understand the risks of the changes and have approved them, and that teammates have reviewed them: good changes aren’t done in isolation.

Change Management isn’t just for IT. If you’re a developer who deploys changes to production, you need this as well.

Change management doesn’t need to be a complex endeavor.  It should, however, give you enough information to save yourself if things get crazy.

Categories

September 2017
MTWTFSS
« Aug  
 123
45678910
11121314151617
18192021222324
252627282930