Press "Enter" to skip to content

Category: Administration

SSIS Perfmon Counters

Published 2016-09-02 by Kevin Feasel

Lonny Niederstadt notes that you cannot see SSIS counters in Perfmon without administrative rights:

A colleague and I were hoping to review SSIS perfmon counters on a VM.  We use a logman command with a counters file to log perfmon to csv.

Opened up the csv that was captured on the VM… there were all of my typical SQL Server counters… but the following SSIS counters were missing.

\SQLServer:SSIS Service\SSIS Package Instances
\SQLServer:SSIS Pipeline\Buffer memory
\SQLServer:SSIS Pipeline\Buffers in use
\SQLServer:SSIS Pipeline\Buffers spooled
\SQLServer:SSIS Pipeline\Flat buffer memory
\SQLServer:SSIS Pipeline\Flat buffers in use
\SQLServer:SSIS Pipeline\Private buffer memory
\SQLServer:SSIS Pipeline\Private buffers in use
\SQLServer:SSIS Pipeline\Rows read
\SQLServer:SSIS Pipeline\Rows written

Huh.

The more you know.

Comments closed

Data Migration Assistant

Published 2016-09-02 by Kevin Feasel

Kenneth Fisher reviews the new Data Migration Assistant:

First things first the DMA is a replacement of the Upgrade Adviser. In fact it’s an upgrade of the Upgrade Adviser. It has some amazing new features.

  • You can install this on a workstation. It doesn’t have to be installed on the server itself.

  • You can have multiple projects saved with data from multiple server/instances.

  • There is the option to get compatibility issues and/or new feature recommendations.

  • You can check issues/feature recommendations for upgrades to 2012, 2014 or 2016.

Looks like it’s a step up from the old Upgrade Advisor.

Comments closed

Reporting From Linked Servers

Published 2016-09-02 by Kevin Feasel

Dave Mason has a few scripts he uses to pull database metrics from his SQL Server instances:

There is a total of “N” linked servers. The query selects from each one and combines the results via UNION ALL. Here’s a sample of the output on my CMS:

What we’ve seen so far could be used as a template for other queries, such as a backup report, a database files report, etc. The code could be put in a stored procedure and run as needed for automated tasks. I’ve used similar code to retrieve data for an SSRS report.

Let’s examine a problem you’re likely to encounter by including @@SERVERNAME within the query

Give it a read.

Comments closed

Ambari 2.4

Published 2016-09-01 by Kevin Feasel

Jeff Sposetti discusses improvements in Ambari 2.4:

Reduce time to troubleshoot problems. Apache Hadoop components create a lot of log data. Accessing that log data to understand what the component is telling you, especially when issues arise, is critical. Apache Ambari includes a new Log Search service that provides agents for log collection and a delivers a custom UI for searching those logs. This is essential to providing a streamlined approach to searching for stack traces and exceptions across all nodes in the cluster.

I have enjoyed watching Ambari mature as a product.

Comments closed

Renaming SQL Servers

Published 2016-09-01 by Kevin Feasel

Wayne Sheffield shows what to do when you need to rename your SQL Server instance:

Sometimes you make a mistake, and forget to rename a syspred’d server before installing SQL Server. Or perhaps your corporate naming standard has changed, and you need to rename a server. Maybe you like to waste the time involved in troubleshooting connection issues after a server rename. In any case, you now find yourself where the name of the SQL Server is different than the physical name of the server itself, and you need to rename SQL Server to match the server’s physical name.

You could always rerun the setup program to rename the server. Fortunately, SQL Server provides an easier way to do this. You just need to run two stored procedures:sp_dropserver and sp_addserver.

Click through for details, including important considerations.

Comments closed

Thoughts On Linked Servers

Published 2016-08-29 by Kevin Feasel

Dave Mason defends the honor of linked servers:

I seem to be in the minority when it comes to SQL Server linked servers. When it’s another SQL Server instance on the other end, I quite like them for administrative purposes. But other SQL pros have some reservations and gripes. I’ve even seen the word “hate” thrown around freely. Most of the complaints seem to fall into one of these categories: poor performance, insufficient permissions, poorly configured security, and challenges related to remote execution of queries.

I think Dave’s reasoning makes a lot of sense.  Linked servers are not themselves evil.  I think it’s likely a mistake to incorporate them into your mainline application (a mistake I’ve made in the past), but for the kinds of administrative tasks Dave mentions, it’s certainly not a bad idea.

Comments closed

Confirming Checkpoints

Published 2016-08-25 by Kevin Feasel

Arun Sirpal shows how to log when checkpointing runs:

Via Configuration manager I enabled trace flags 3502 and 3605 – both needed to get the checkpoint information and write it to the error log.

I then shutdown the machine, on start-up I looked into the error log.

1
EXEC XP_READERRORLOG

Notice the ‘s’ in front of the spid<number>? Well that means the checkpoint was done via the automatic process; if you do a manual checkpoint it won’t see this letter.

I did not know that the “s” indicated that this was an automated process.

Comments closed

Azure SQL Database Maintenance

Published 2016-08-23 by Kevin Feasel

Jeffrey Verheul mentions that Azure SQL Database databases need regular maintenance, too:

Before I’m going into detail, I want to give full kudos to Ola Hallengren (Website | @olahallengren). He has spend a lot of his time to build a SQL Server Maintenance Solution that is completely free for everyone to use. And he did such an excellent job a lot companies (also huge companies) use his solution to run maintenance tasks on their databases.

None of the scripts below are written by me, but only small changes are made in order to make things more clear when the solution is deployed to an environment. The original scripts can be downloaded via the download page on Ola’s website.

Most of the to-dos are the same between on-premises and Azure SQL DB, but some of the implementation steps are a bit different.  This is worth checking out if you have any Azure SQL Database instances.

Comments closed

Azure SQL Database Alerts

Published 2016-08-18 by Kevin Feasel

Julie Koesmarno shows how to set up Azure SQL Database alerts:

Over the last year, I have been intentionally seeking out to get feedback from the community via various SQL events, particularly those who plan to use or are currently using Azure SQL Database. A lot of questions have come up about managing Azure SQL Database better – i.e. being more proactive and more responsive in managing Azure SQL Database. One of the ways to be more proactive about your SQL Database is by setting up alerts. As an example, you can create an alert in case DTU goes above 95% – say in the last 5 minutes, so that you can either investigate why this might be or upgrade it to a higher SKU.

This article walks through how you can setup an Alert on Azure SQL DB.

I really like the fact that they offer web hooks; that way, I can integrate these alerts with Slack or other messaging systems.

Comments closed