Art of the DBA Rotating Header Image

administration

A short ride on a PoSH machine

Jun 26th, 2012
by Mike Fal.
No comments yet

I want to talk about one line of code.  Ok, well, two lines of code, but the first line isn’t that important, much like the opening act of a rock concert.  Let’s just pretend that the first line, while necessary, just gets us warmed up for the main event.

My problem was this:  I was collecting a SQL Server inventory but couldn’t retrieve all the information I was looking for.  I had started with some of the elements of Allen White’s(b|t) script as well as what Collen Morrow(b|t) assembled, but I was still missing the TCP/IP address and SQL port.  In my shop we use static addresses and I wanted to have that information handy.  So, to the Google-mobile.

My searching led me to a Sean McCown(b|t) video that showed me how to grab the TCP/IP address, so I was halfway home.  However, I just couldn’t find a clear way to grab the port information.  I think I was stretching my fingers to start pulling my hair out when I bumbled into the ManagedComputer class in the SMO.  Down the rabbit hole I went, using Get-Member, and eventually came up with this:

$managedcomp = new-object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') $sname
$port=$managedcomp.ServerInstances[$iname].ServerProtocols["Tcp"].IPAddresses["IPAll"].IPAddressProperties["TcpPort"].Value

Hopefully that doesn’t look to intimidating, but let’s break it down a bit.  The ManagedComputer class (in this case $managedcomp) represents the physical host for the machine.  Using this, we can then access the various properties of that.  In succession, we have:

  • ServerInstances – An array of all the named instances installed on the machine.  I’m passing a variable to this, which happens to be the name of the instance I want the port for.
  • ServerProtocols – An array of the different protocols, so in this case we want the TCP/IP protocols.
  • IPAddresses – Note, these are the different address configurations SQL is listening on, but it’s what appears in the SQL Server configuration tool.  Your array elements are named IPN or IPAll (if it applies to everything).  Since I’m looking for the port that is assigned for all my addresses, I’ll look at the IPAll object.
  • IPAddressProperties – Simple enough, the properties of the IPAddress object, and we want the TcpPort property specifically and its value.

How did I get here?  Making extensive use of Get-Member.  Much like Theseus in the Labyrinth, I used that to figure out where to go next each time I went to a deeper property level.

And this is what I hope people take away from this.  There’s so much information buried in the .Net objects that we can take advantage of, especially by using Powershell.  I’ll be the first to admit that there might be a better way to do this(please tell me if there is).  This shows, though, that by using the tools in front of me, I was able to expose the secrets of this particular set of objects and bend them to my will.

 

P.S.  For those interested, I’ll post the full server inventory script here in the near future.

Brushing up on your (backup) history

Nov 14th, 2011
by Mike Fal.
2 comments

Backups are huge, right? As administrators, they should never be far from our minds. Backups are one of the cornerstones of disaster recovery and safeguarding our data. It only makes sense that we want to keep an eye on when backups happen and if they’re successful. Recently, I’ve been making a lot of use of a simple little query that shows me how my backups are doing:

SELECT
  database_name,
  type,
  max(backup_start_date) last_backup
FROM
  msdb.dbo.backupset
GROUP BY
  database_name,
  type

Yes, this is a VERY straightforward query, no hidden magic here. But with this one little SELECT statement, I have been able to quickly establish if my backups are getting run, what specific databases may have problems, and where I need to focus my troubleshooting. By breaking it down by database and type, I am also able determine whether or not my log backups are running (key to out of control log growth), if I’m properly maintaining my recovery chain, and what backups are going to be necessary to restore my database.

Unlocking the secrets

The key is the the backupset table in msdb. This table stores the history of all successful backup operations on the server(emphasis on successful). It’s not just native SQL backups, but those by third party tools as well, so we can have insight in to any backups (or lack of) that are happening. I first came across this table working with Brent Ozar’s(b|t) Blitz script. Brent introduces this query as part of his server take over to establish whether or not your backups are being taken, which is the primary reason I check it. Backing up our data is vital and not doing this would probably be an RGE. If nothing else, this table says “Hey dummy, I need you to look at something!”

If we dig in a little more, though, we can find other useful information. Take the backup_start_date and backup_finish_date fields. By using another simple query, we can then see how our backups perform over time:

SELECT
  database_name,
  backup_start_date,
  datediff(mi,backup_start_date,backup_finish_date) backup_duration,
  backup_size/(datediff(mi,backup_start_date,backup_finish_date)+1) bytes_per_minute
FROM
  msdb.dbo.backupset

Backup duration is a handy metric for base-lining our systems. After all, if our backups start taking longer and longer, it could be an indication of resource contention. If we’re backing up to local disk, we could be choking our throughput. If we’re backing up to a network share, we could be seeing increased bandwidth usage. Also, our databases could just be getting bigger and might require more resources to support them. By including the backup_size field, we can see if longer backups are the result of simply backing up more data or if there might be something else to it.

If we use the backup_size field and its sibling, the compressed_backup_size field, we can gain some additional insights. Since backups will grow in relation to how much the database grows, this gives us a quick and dirty way to report on those growth patterns. Ideally, you’d want to be track the actual database size, but if you don’t have something to capture that historically, this will work in a pinch. Additionally, by combining it with the compressed backup size, you can get an idea of how much space you’re saving if you use the WITH COMPRESSION option in SQL 2008. A quick gotcha here: This will not show the benefits of compression using third party backup tools. Both fields will show the fully compressed backup size if you’re using something like Red Gate SQL Backup, Quest LiteSpeed, or Idera’s SQL Safe.

Cleaning up after yourself

Now not everything with this table is sunshine and rainbows. Maintaining this history is one of the more commonly overlooked areas of SQL Server administration. Ever wonder why your msdb database keeps growing and growing over time? Bingo, it’s because your backupset table keeps increasing with all the additional history stored inside it. What you want to do is schedule a regular run of sp_delete_backuphistory to keep your history to a relevant period. What that time frame is depends on your database size and needs, though I personally go with 90 days as my default.

Those who don’t learn from history…

Successful database administrators are the ones who make monitoring a part of their practice. With it, we can be proactive with our systems and solve problems before they occur. The great thing about the backupset table is that it’s a very easy way to start checking up on your instances, leveraging information that is already a part of the SQL Server core functionality. You don’t need any fancy tools or complex systems, just a few basic queries to get started understanding how your servers, databases, and systems perform on a day to day basis.

It’s a heterogeneous world

Jun 13th, 2011
by Mike Fal.
No comments yet

Here’s my big “DUH” statement to kick things off with: The information technology world is one of constant change. Shocking, I know! For most of this, it’s why we love the business. There’s always something new to learn and fresh challenges to solve.

But it’s also a world where the old things don’t go away. I’m sure many of us have stories of having to keep some Access application afloat or supporting a legacy website that had most of the back end hard coded. When companies make investments, they want to get the most of that investment that they can, which means platforms will stay around for years because it’s to resource intensive to replace things that are already working.

This is especially noticeable if you work in a company that grows by acquisition. Rarely do different company’s have the same systems, so every acquisition means having to adapt to a new set of software. I’ve had to deal with acquisitions in every job I’ve worked in, and through that period, I’ve had to support 5 different database platforms. My focus has always been on SQL Server, but I’ve had to learn to adapt to these other technologies in order to support either a migration, a legacy application, or a proof of concept system.

So what am I getting at here? Mostly, that it’s important to not become to focused or attached to any one platform, particularly when it comes to databases. Only the very lucky will work with one only SQL Server (or another database system) for their entire careers. Here’s a couple tips to keep in mind so that you don’t short circuit yourself when challenged with a new system.

Be Flexible

If a new technology comes into the company, don’t be afraid to take it on. As far as databases go, you have a couple things working in your advantage. While some of the underlying mechanics may be different, such as storage and memory use, many of the fundamental concepts are the same. You’re still going to have tables, keys, and indexes. You’re still going to need to take backups, update statistics, manage access. Chances are that this is most of what you will be required to do to support a new platform, so don’t be intimidated by how “foreign” a new system might be.

Be Aware

The next stage beyond flexibility is awareness. How many database systems can you name? Three? Four? Even if you just know the names of some other systems, you’ll have an edge. Take some time and research what else is available. Once you know some names, you can then take it a step further and get a general idea of the capabilities of these platforms, as well as what separates them from each other. If you can get to the point where you can describe a platform in a paragraph, not only will you be more comfortable learning to support a new system, you can also provide high level feedback on transitioning a platform into your enterprise.

Be Ready

Don’t get blindsided by a new situation. If you can get ahead of the game and at least have cursory knowledge of how to do things in a different database system, your life will be a whole lot easier. Expand on your basic knowledge and take time to figure out how to do some of the basics, like:

  • Taking a backup
  • Creating a database
  • Importing/exporting data

You never know if or when you will need to do any of these tasks. You don’t need to be a certified expert, but if you take the time to at least experiment with other platforms, then you won’t feel like you’re floundering for a life preserver when a new acquisition gets thrown at you. If you don’t know some of the basics when you have a new database thrust upon you, make it a priority to learn these things first, taking all the basic tasks you perform on the database you’re familiar with and find out how to do them with the new technology.

As data professionals, we are expected to do our part for the company and take on these new challenges. Don’t be afraid of having to adapt to other database solutions. From my perspective, this is a great way to be the hero for your department, simply by taking a little time to get ahead of the game. And by knowing more about other technologies, you are also prepared to help plan your company’s acquisition and integration efforts, ultimately furthering your career and preparing yourself for the next stage.

Newer Posts →