Voice of the DBA

Author: way0utwest

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

  • Service Broker – SQL Server Connections

    These are some notes and thoughts from sessions and my time at SQL Server Connections/DevConnections in Nov 2010.

    Denny Cherry gives a nice warning at the beginning of his sessions. His parents were sailors, so beware of the

    Service Broker is alien to so many people. I agree with that. The idea of sending a message, or a insert somewhere and having it process “later” in some other transaction is scary. People are worried. They don’t like the idea of asynchronous processing.

    However “later” isn’t some long time in the future. You can have queues process immediately and automatically, and “later” is often milliseconds after you’ve sent the message.

    There also is a guarantee of delivery in the queue, so you have essentially transactional consistentcy, just not everything on one transaction that must complete at the same time.

    People use XML to send the data since it’s a flexible format. I agree. As heavy as XML can be with all the overhead of the tags, it’s as flexible as things come, and it’s much easier for me to understand and work with than some type of delimiter that gets in the way of the data you are processing. Not that XML doesn’t have issues, but I think it works well.

    Denny says that you should have queries ready to check your queues when you set up Service Broker since the first time you do it, it probably won’t work. I can attest that I have had issues, which seem to be a combination of a confusing technology and a dearth of documentation that makes it easy.

    Mr. Cherry’s former company was sending 2-3mm messages/hour through Service Broker. That’s a good scale of things happening in an instance. Open conversations can cause issues, but if you can process them quick enough, then you will be in good shape.

    ssbdiagnose is a good tool (command line app) to help you figure out what is happening.

    Denny uses multiple message types to let the receiver know that something is done. He has a second message type that is a “conversation switch” in which he sends an empty message that lets the receiver know that this set of data is done. That’s a good technique and one I hadn’t thought of. I’d send some EOM in a normal message type, but having a second type makes some sense.

    Leaving retention on is a bad idea. No easy way to purge. That wasn’t something I was aware of, though I haven’t sent 2mm messages/hour.

    Denny also uses two queues, one for messages and one for acknowledgements. This prevents a line of acks from blocking other messages, which you might have with one queue.

    Watching Denny send messages in a basic send/receive SSSB queue, I can see why this hasn’t necessarily caught on. There’s no “wow” moment. Seeing a conversation take place and an acknowledgement are just not exciting. But there are huge possibilities here for auditing, distributed processing and more.

    Seeing messages moving between instances in SSMS isn’t that impressive. I think there’s a chance here to write something like the Database Mirroring demo app from Kimberly Tripp that shows queries being run in real time. A series of messages shown on different instances, and being processed would be a simple, nice, .NET app.

    No errors are thrown if you aren’t processing queues, which isn’t great, but it’s what I expected. So you need to make sure that you are properly watching and managing queues, especially if you have autogrow enabled on your databases.

    Messages are delivered in order, only on a single conversation. So if you have multiple conversations, the messages may not be in chronological order. Denny recommends a single queue and then periodically end the conversation with a random value and restart it.

    Service Broker is a neat technology and if you haven’t tried using it, you might want to check out its capabilities.

  • Swarms

    First we had agile programming, designed to respond to quickly changing requirements for software development. Now we might have the addition of swarms as a way of getting things done in our jobs.

    One of the predictions that the Gartner Group has for the workplace in the next decade is the introduction of swarms as a more common way for companies to get things done. I think that a swarm is designed to be a rapidly formed, ad hoc group that comes together for a period of time. They are self forming, probably driven by one person that needs help with a problem and  either seeks out colleagues, or perhaps just somehow posts a request for help from people in the company.

    It sounds a lot like an action movie plot where the best soldiers are pulled together for a mission suited to their unique skills. Hopefully it’s more like a Mission Impossible team and not a Mystery Men one, though I wouldn’t be surprised to see more team formed like the one in the latter movie.

    This is really like the crisis team in a company, or a new project team  crossing multiple departments, but organized in a less-formal, and much quicker manner. I do think that more and more we will be called on to do work for people who are not our supervisors as businesses look to be more reactive and responsive to changing requirements.

    I can see us evolving some businesses to having a “guidance counselor” who approves our expense reports, performs periodic reviews, helps us manage our careers, and is available to discuss issues. I think many of us might prefer this to a direct manager that is looking to assign us work. It might be more interesting to go find your own work, or respond to requests from others inside the company.

    Steve Jones