Voice of the DBA

Tag: powershell

  • Manage By Delegation

    powershell
    Powershell might be a great skill if you need to manage lots of instances.

    More and more SQL Server instances are being deployed all the time. In fact, with the ease with which we can build a new virtual machine (VM) through snapshotting and cloning, it seems that many administrators are finding that the number of servers for which they are responsible might be doubling or tripling.

    Even moving to the cloud doesn’t completely remove the need for some administration of your data and databases, though it does require you to rework the type of administration that you perform. I foresee more hybrid solutions over time, which will require DBAs to not only manage data, but help analyze the financial impacts of moving data (and analysis) to, or back from, the cloud.

    In SQL Server 2008 we had the chance to begin managing our servers through a set of declared rules with  Policy Based Management (PBM). I haven’t seen that feature take off, and it seems relatively few people are using PBM to manage their servers. I think it’s a great platform for ensuring that your instances are conforming to certain rules, though I think there is a bit of creativity needed to ensure that this system works well for you.

    Powershell is becoming integrated into all Microsoft products. Virtually everything in SQL Server, perhaps even every thing by now, can be managed through Powershell scripts that access the SMO objects. I hear various people say that Powershell is a critical skill for DBAs of the future. I’m not sure of that, but I do think it will be used more and more if you have the need to perform repeated actions on multiple servers. Whether you use it now or not, it doesn’t hurt to learn how it works and what it can do for you.

    It just might be the tool to make your job easier as you get more and more instances to manage, something that seems to happen more and more.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Powershell and Performance Monitoring

    When I was doing performance monitoring of servers, I typically struggled with a good way to get the data. In years past it was cumbersome to keep track of server information and rarely was it done well. So often I found that many problems were the result of simple mis-configuration settings, or a lack of patches. Once a DBA or sysadmin can’t remember everything about every instance, it invites chaos.

    Allen White is showing how to better track a “server inventory” to learn all about the setup, configuration, and performance of SQL Server using Powershell. It’s easy to find this information in an ad hoc manner, but keeping track of it and catching exceptions requires something more formal. Powershell is a great way to do that in a repeatable way.

    Based on an article at Simple Talk, Allen shows how you can put your servers in an XML file and then read that in from Powershell and use it to drive a series of queries against a particular instance and machine.

    Most of this session gives you a good look at a script that will gather various information, including performance data, from your instances.  One of the more practical sessions that can really help you understand a large Powershell script in detail.

    However Powershell takes some practice. It’s a set of fairly concepts that are designed to be put together, but you need to take a little time to understand how the scripts work.

    If you get the chance to see Allen speak, it’s worth it. He does a good job of walking through code and helping you understand how it works.

  • Powershell and Policy Based Management

    If you haven’t looked at Policy Based Management (PBM), and you manage multiple instances, you ought to really look at it. Even if you manage just a few, having your systems automated just saves you a lot of time and effort.

    Allen White is the Powershell guy for me, and he’s showing how you can automate the PBM setup with Powershell. Powershell is another tool that every administrator probably should spend some time working with since it just allows you to repeat the work you need to do with minimal effort.

    This is a good session that shows the basics of what PBM is and how it is structured. What’s a policy, a facet, a condition. As an example, Allen shows how to make sure that your database (facet) has autoshrink = False (condition) with a policy that checks this. Loading these types of policies into all your instances can prevent any strange issues that might come about from having differing setups.

    A smart DBA will probably have whole sets of these policies, perhaps even different groups for development and production, that are applied to ensure that the knobs are tweaked to your particular environment.

    Powershell allows you to script actions. For things you do once, there’s no benefit, but I’ve learned in IT that if you do something once, likely you’ll have to do it again. So having the ability to script things that you might repeat is useful.

    Allen shows how to load assemblies in Powershell, connect to SQL Server, and then access objects properties and methods. Most of what I see done here is done in many Powershell sessions. It’s basic programming, but so often people don’t understand basic programming if they are sysadmins. Developers ought to pick this stuff up instantly since it’s the kind of thing they do often. It’s just interpreted, not compiled.

    One nice tip from Allen, if you use single quotes, then everything is literally. If you use double quotes, you can have variable substitution.

    Building policies with Powershell helps you understand the SMO objects, and it you have a fairly repeatable process. These scripts are something you can easily carry to the next environment, which isn’t as easy in the GUI.

    All kinds of things Allen shows, reading the registry, deploying to multiple instances consistently, and more, is easier in Powershell. It’s confusing when you see so many variables, but if you play with Powershell a bit, you’ll find that it’s a great way to manage your systems.