Voice of the DBA

Author: way0utwest

  • 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.

  • One Time Passwords

    Facebook seems to be constantly under fire for one privacy issue or another. I think it’s likely something that they will deal with forever, since their fundamental purpose is to find ways to share data with others and many people don’t understand the tools that Facebook has built for them. As I follow the growth of Facebook and see the new features that they add, I think they do have some commitment to making it easier for people to better secure their information and only share it in the way they want.

    Recently I saw on the Facebook blog two neat features that I really liked, and I think might be nice additions to SQL Server. One was the ability to remotely log your account off from other locations. This could be handy for people that might access Facebook from a public terminal and forget to log off. We can easily have an administrator do this in SQL Server by killing off a session.

    The other feature was the addition of a one-time password for someone that might want to access their account from an unsecured terminal. At first I thought I’d never need to use this, but then I thought about all the times that I had accessed a server from a friend’s computer. Or how often I had a request for some data that required a new account. What if I could setup a one-time password for an account in Reporting Services that would allow someone to view a report, or download some data without permanent access?

    It would be an interesting way to handle ad-hoc access to systems. In the past I’ve usually enabled a specific  account for a short period of time, but then I’d have to set a reminder or remember to do disable it. That wasn’t something I always remembered to do.

    However allowing someone a one-time password might be a good way to allow them access to data they need on a limited basis. I could see the need for a one-time execution of a report being a feature that would allow me to distribute data easily for a single use. It could be very useful in ensuring that accounts that were granted rights did not have them forever.

    Steve Jones

    This was also published at SQLServerCentral, which includes a discussion of this piece.

  • 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.

  • VLDB Issues at SQL Server Connections

    Kim Tripp is a great speaker, with a wealth of knowledge. This session was talking about some of the issues with VLDBs.

    What’s a VLDB? Definitely the TB range, but Kim talks about the issues that come with 100s of GBs as well.  She mentions one client with a 3TB table. Definitely time for VLDB planning.

    She also says that in the last couple of years, almost all of her clients use some type of partitioning. Granted, Kim tends to work with larger, more expensive operations, but interesting to see that partitioning is part of many clients.

    This is a DR session. When data is damaged, what do you do? The first think is think about recovery as a step by step process. Don’t freak out.

    Your first questions should be who, what, and when. Who noticed it, what is wrong, and when did it happen. Key questions to know, not necessarily for blame, but for recovery purposes. Knowing who is important for security or tracing purposes. Kim says no select/insert/update/delete on tables. Always some other mechanism to access data.

    If you don’t know who did something, then it becomes more of a detective case to investigate what might be wrong. Definitely more challenging if you don’t know who did something.

    What do you do first? It depends. You might not be able to take the database or server offline, so what do you do? It’s an issue. You can’t usually shut down the database services, so you must think carefully about what you will do.

    Recovery from a dropped table

    Interesting. A nice sales/customer/employee/product table relationship, with “Sales” being dropped. That’s the easiest one to delete with FKs, but it’s the most important table.

    A good backup is needed first. As Kim notes, if you don’t have a good backup, you can’t go very far in recovery.

    The sales table gets dropped, and one of Kim’s insert scripts starts failing. As expected, if this for real, likely your phone is going to ring.

    First thing: note the time that you first hear of something going wrong.

    Second thing, Kim looks to set the database access to “restricted access” meaning only dbos are allowed in. I have never used this, but if you really lost something big, you want to do this. If this happens, that means that your application also needs to handle this and show a good message to clients.

    Is it better to be back up or have all the data. That’s a good question. It probably will be different for each business, and maybe each database. What is more important? Might be just getting back up in many cases.

    MAKE SURE YOU GET THE TAIL of the log if you can. You don’t want to restore without having this.

    Use stopat in all restore statements. It doesn’t have an effect in RESTORE DATABASE, but allowing this means that you get in the habit of using it in all RESTORE commands. The same with the NORECOVERY option.

    Her final statement has a WITH RECOVERY, RESTRICTED_USER. That’s nice to have, and let the DBA check without users/systems connecting right away.

    Right away, Kim creates a snapshot. She can then easily go back to this point. This is also then used to compare to an investigation database that is a copy of what you restored.

    She also resets the identity seed to a higher value in this case. She has a gap, but it’s a known gap that might be useful if you recover more data.

    One of the key things that I see is Kim keeping notes of times, and what is done, as she goes through the recovery process for this demo. That’s key in disasters. Having a pad/pen or Notepad around is indispensible.

    Kim has a cool script that starts running log restores with ever incrementing STOPAT values to find out where there was an issue. In each restore, then uses tablediff to grab the differences between the snapshot and the test restore to get the missing data that might not be in the database that is now live.

    Main points:

    • contain the damage
    • decide if data loss or downtime is more important
    • recover the damaged data
    • bring offline components online
    • prevent this in the future

    A good DR list to keep handy, and think about first.

    Containment part 2

    One important thing is to contain the issue and perhaps not take the entire db or server offline.

    One thing in Enterprise Edition is to take the secondary files offline, can be useful to take an .ndf file offline. You cannot take an .mdf or .ldf offline, but you can with a .ndf, so for large dbs, separating tables into separate filegroups is a good thing.

    Kim shows an online index rebuild that moves a table across multiple files and it’s pretty cool. While users are in the system, the table is moved to a new filegroup/files. This partitions the table to get better control of disasters.

    Now we lose a file, and the file needs to go offline. This may or may not affect your application. The connections are killed to the db, but a good application will retry and handle this as a transient error. Users that access the partition that is offline will get a trappable, level 16 error.

    Restoring a single file with the move command. That’s cool. Never had to do that myself. Not sure you could do this in SQL 2000 when I had a few disasters. Not sure I had Enterprise Edition in most of my jobs.

    Very cool session, highly recommended if you have to plan for, or execute, a DR plan.