Press "Enter" to skip to content

Category: Administration

Getting To ZDT

Published 2016-12-19 by Kevin Feasel

Kendra Little gives thoughts on getting to zero-downtime releases:

I’ve worked in a bunch of environments where we rolled schema changes to production throughout the week, and worked hard to limit downtime. Our biggest challenges were:

  • The code released wouldn’t have the intended effect, and multiple hotfixes would sometimes need to be written quickly if the issue was urgent.

  • Problems would happen with replication in production – either replication would be delayed because a lot of modifications were occurring, or a change in an “upstream” system would cause a huge amount of data processing to happen in a “downstream” database

I’m in a ZDT environment.  There’s a pretty good amount of overhead work that goes into getting there; there are times in which a 20 minute window would have saved hours of development time, so it’s important to keep that trade-off in mind.

Comments closed

Hadoop And Active Directory

Published 2016-12-16 by Kevin Feasel

RK Kuppala explains how to integrate a Hadoop cluster with Active Directory:

This post explains kerberizing an existing Hadoop cluster using Ambari. Kerberos helps with the Authentication part of enterprise security (while authorization, auditing and data protection being the remaining parts).

HDP uses Kerberos, which is an industry standard for authenticate users and resources and providing strong identity for users. Apache Ambari can kerberize an existing cluster by using an existing MIT key distribution center (KDC) or Microsoft’s Active Directory.

This was a lot easier than I expected.

Comments closed

SQL Agent Alerts

Published 2016-12-15 by Kevin Feasel

David Alcock has a script to create SQL Agent alerts for common errors:

These alerts cover a range of errors from potential IO subsystem problems to failed logins, all of which are things a DBA needs to know about, and quickly too.
As well as error notifications you can set up alerts to cover performance conditions. The final statement in the script below sets up an alert that triggers when Page Life Expectancy drops below 1000. In all honesty I don’t set up these performance alerts that often but I wanted to show you the kind of thing that is possible and would be handy if you don’t have any third party monitoring.

He follows this up with a post on appropriate response:

But what do I mean by sensible? Typically I see a number of problems with alerting setups; either alerts are inadequate and don’t cover the necessary errors (or there are none at all) but I also see the notifications to alerts not being set up correctly meaning problems go backwards and forwards delaying any fixes.
The other problem I see is an over provision of alerts. This usually is because one or more other monitoring systems have been deployed and error notifications have been duplicated as a result. Imagine having an operational tool like System Centre, some SQL monitoring software and native alerting all pinging the same message to the one recipient mailbox. Now on top of that let’s say the alerts have not been configured correctly so information emails are being issued every second. It’s a scary thought but it is easy to see how a critical error might be missed in this scenario.

If you don’t have automatic alerts for high-severity errors, this is an easy way of gaining insight into the problems your server is experiencing.

Comments closed

Filtering Event Log Entries

Published 2016-12-13 by Kevin Feasel

Kevin Hill gets into Powershell:

What follows is a step by step of how I expanded on the most basic command to get more and more out of it. I spent a lot of time on MSDN looking up cmdlets, and on Stack Overflow twice with basic questions. Even went to my “DBA” Twitter feed early in the process using #sqlhelp since a lot of my colleagues are veteran PoSH users.

Warning…its very possible I’ve gained a syntax error here and there when copying and formatting this into a post…run at your own peril 😉

One bit I’d recommend is using Out-GridView for occasional display and potentially getting rid of the Excel requirement.

1 Comment

SQL Server On Linux Service Commands

Published 2016-12-13 by Kevin Feasel

Andrew Peterson shows how to start, stop, and restart the SQL Server service on Linux:

Start Service

                 sudo systemctl start mssql-server

He also shows how to do a status check.  This is for distributions which use systemd, which includes the Red Hat distribution set (Fedora, CentOS, Red Hat Enterprise).  If you’re on Ubuntu, there’s no support quite yet, but you can use start and stop.

Comments closed

Data Migration Assistant Timeouts

Published 2016-12-09 by Kevin Feasel

Kenneth Fisher shows that the Data Migration Assistant developers thought ahead:

I’ve been really excited about the new Data Migration Assistant (DMA) since I first heard about it. One of the things I like best about it is that unlike the old Upgrade Advisor it doesn’t have to be run on the server being upgraded. You can run it against any number of instances from a single workstation. The other day I was working from home and tried running the DMA against a couple of moderate size databases (about 1.25tb total) and I consistently got timeout errors.

Click through for the solution.  I’d prefer “have the queries be quick enough not to require this change” be the solution, but I don’t know exactly which queries they’re using, and some DMVs/DMFs can be quite slow.

Comments closed

Cleaning Up SSISDB

Published 2016-12-06 by Kevin Feasel

Peter Schott extends a script to clean up the SSIS catalog database:

I really appreciate what MS has done w/ the SSIS Catalog. We have built-in logging at a level that wasn’t possible in prior releases, but that comes at a cost. The default retention is pretty high (365 days) and MS chose to handle cleanup using cascading deletes. This sort of coding makes life easier for the developers, but almost always performs poorly. That’s especially the case when you have 1 parent row with 100’s of thousands or child rows related to it.  The problem is compounded because the first time people realize they need to lower the retention limit is about the same time that the database is starting to fill up or has filled up. At that point, it’s too late to lower the retention by a large number because the corresponding delete will still cause issues.

Click through for a script which helps extricate you from sticky situations.  The ideal scenario here would be to set your retention period correctly and not have to delete rows directly, but sometimes you’re stuck in a less-than-ideal situation.

Comments closed

Powershell Remoting To Linux

Published 2016-12-05 by Kevin Feasel

Max Trinidad shows that Powershell remoting works for connecting Linux to Windows and vice versa:

Connecting Linux to Windows

This is strange but, more likely, you will get an error the first time you try to connect. Just try the second try and it will work.

I need to see what Powershell objects for Linux currently exist; my guess is “not many, if any” but as those start getting fleshed out, I think even the most adamant of grep-sed-awk users will want to pick up at least a little bit of Powershell.

Comments closed

Capacity Planning

Published 2016-12-01 by Kevin Feasel

Erin Stellato walks through capacity planning:

Capacity planning for a new solution is really tricky.  You have to come up with estimates about workload based on information you collect from the business.  This means you have to ask hard questions about how much data they will expect in the first month, the first six months, and the first year.  When a new solution is coming in, this is often the LAST thing the business is thinking about, so very often you’re going to get vague answers.  In the case of a new solution, you really have make a best guess effort.  Don’t pull your hair out trying to get exact numbers.

If the solution is from a vendor, you must ask the vendor for planning recommendations about both space needed and the resources needed.  I admit, they may not have that data, but you don’t get what you don’t ask for.  It never hurts to try.

Click through for Erin’s discussion points across hardware and storage requirements over time.

Comments closed

Using Event Notifications

Published 2016-12-01 by Kevin Feasel

Dave Mason lays out how to set up Event Notifications:

When an Event Notification is created, one or more conversations is created between the SQL Server database engine and a Service. I tend to think of these as “message channels”. When the related event occurs, SQL Server calls the EVENTDATA() function, returning the event information results (as a variable of type XML) to a Service. The Service in turn writes the information to a Queue. A Queue is a FIFO data structure. Conceptually it is similar to a table of rows and columns. You can SELECT from it, but you can’t directly insert or update its rows. You “delete” rows from a Queue via the RECEIVE statement.

Dave has a full example worked out at the link.

Comments closed