The first box of swag for SQL Saturday #41 in Atlanta has arrived. Thank You Manning Books!! There are more boxes of swag headed our way but this was the first to arrive. Manning has donated these 6 books to be given away. I kind of feel bad that they sent two copies of the Deep Dives books since all the proceeds go to benefit War Child International. Hopefully whoever wins these books will love them as much as I do and write a review to entice others to buy a copy. Tomorrow the Call for Speakers is going to close so please get your sessions submitted ASAP. We are checking on room availability to see if we can fit in a few more topics and give you the most options possible. The event is near capacity. At this time there are fewer than 20 spots left. Our presenters should include a 6-pack of MVPs so if you want to secure a spot then please sign up quickly. Next year we may need to find a larger facility to meet demand. We’ve attracted this crowd despite very little promotion outside of our own discussions and the support of Steve Jones at SQLServerCentral.com
March, 2010:
Late March PASS VC Presentations
The PASS AppDev and DBA Virtual Chapters have even more free training lined up for you: Index Statistics March 23rd at 12:00 PM Eastern (GMT -4) Presenter: Josef Richberg Add to Calendar A semi-technical guide to understanding and using non-clustered indexes in your databases. Learn the benefits of covered indexes and their differences between SQL 2000 and SQL 2005+. Josef Richberg Josef Richberg is a DBA for HarperCollins Publishers working with SQL Server and SSIS. He has over 17 years experience designing, building, and tuning SQL Server. He is the recipient of the ’2009 Exceptional DBA’ award and actively blogs at http://josef-richberg.squarespace.com How do I view the presentation? Attendee URL: Live Meeting link _____________________________________________________________________ Topic: SQL Server Internals and Architecture Date: March 24, Noon EasternTime (GMT -4) Abstract: Let’s face it. You can effectively do many IT jobs related to SQL Server without knowing the internals of how SQL Server works. Many great developers, DBAs, and designers get their day-to-day work completed on time and with reasonable quality while never really knowing what’s happening behind the scenes. But if you want to take your skills to the next level, it’s critical to know SQL Server’s internal processes and architecture. This session will answer questions like: – What’s different about 32- and 64-bit systems? – What are the various areas of memory inside of SQL Server? – How are queries handled behind the scenes? – What does SQL Server do with procedural code, like functions, procedures, and triggers? – What happens during checkpoints? Lazywrites? – How are IOs handled with regards to transaction logs and database? – What happens when transaction logs and databases grow or shrinks? This fast paced session will take you through many aspects of the internal operations of SQL Server and, for those topics we don’t cover, will point you to resources where you can get more information. So strap on your silly, as we cover all these topics and more at speed with tongue planted firmly in cheek! Prerequisites: Basic understanding of SQL Server operations and activities – such as transactions, queries, and preventative maintenance tasks like backup and recovery. Speaker: Kevin Kline is the Technical Strategy Manager for SQL Server Solutions at Quest Software. A Microsoft SQL Server MVP, Kevin is a founding board member of PASS and the author of popular IT books like “SQL in a Nutshell” (O’Reilly & Associates). Kevin is a top-rated speaker at industry trade shows and has been active in the IT industry for over 20 years. Door Prize: Kevin will have two copies of his “In a nutshell” book that will be raffled off to attendees. You must register no later than 5:00 PM Eastern on March 23rd to be included in the raffle. Registration: You can attend the meeting without registering but if you want to be entered in the drawing, you must register at https://www.livemeeting.com/lrs/8000181573/Registration.aspx?pageName=pzjr7664vdwssm3t Live Meeting Link (just paste into your browser): https://www.livemeeting.com/cc/8000181573/join?id=344ND5&role=attend
Atlanta PowerShell User Group -March Meeting
The inaugural meeting of the Atlanta PowerShell User Group will be held on Tuesday March 16th from 6:00 to 8:00 PM. You can sign up now at http://powershellgroup.org/atlanta.ga. Please RSVP so we can get a head count. Our meeting will be held at the New Horizon’s training center at 211 Perimeter Center Parkway, Suite 200, Atlanta, GA 30346. Click here for directions and a map. Our meeting is graciously sponsored by PowerShellCommunity.org who will be providing the PIZZA. Our first meeting will be a script club. What is a script club you ask? You bring an idea for a script, and ask your fellow PowerShell users for help getting the script written. If it’s PowerShell, its covered — just bear in mind that it may be hard to test things like Exchange scripts or Active Directory management scripts unless you have a nice virtual lab on your laptop. What are the Rules of Script Club? You always talk about script club You always talk about script club If someone asks for help, and you can help, you help Two people help one person at one time One module per person per night All scripts, all PowerShell Scripts will be as short as they can be If this is your first time at Script Club, You have to script! We currently do not have access to the site’s computers so bring your laptop if you can.
Speaking at SQL Saturday #29 in Birmingham
I just received word that I will be speaking at SQL Saturday #29 in Birmingham Steel City. I’ll be speaking on two topics, PowerShell and Virtualization. I’ve got two new tricks that I’ve put together for the PowerShell session since #33 in Charlotte so we’ll see if I can squeeze them into my already overflowing session. I guess I’ll just have to leave out a few of the developer jokes . I’m been busily deleting things off of my hard drive so that I can get better demos available for the Virtualization session. I hope that does the trick because I don’t have an extra $700 to splurge on a larger SSD. Before and between my sessions I will be trying to catch Adam Jorgensen PowerPivot for Excel and SharePoint 2010 (since so far he’s snubbing us in Atlanta) Kevin Boles SQL Server Memory Deep Dive and I’m thinking I might check out Rodney Landrum DBA Repository Update 2010 Using SSRS and SSIS. I hope to see you there! More Details: What: An all day FREE training event with 20 technical SQL Server sessions spread out over three tracks of Business Intelligence, Database Administration and SQL Development. Breakfast and lunch will be provided at the event and there will be an after party at the Tilted Kilt on Hwy 280. When: Saturday, March, 27, 2010. Online registration is now open, but it is filling up fast so reserve your spot now. Attendee check-in will begin at 8:00am until 8:45am with opening comments from 8:45am to 9:00am and the first sessions beginning at 9:15am. A full list of session tracks and schedule is available. Where: The Shelby-Hoover Campus of Jefferson State Community College at the new Health Sciences Building at the intersection of Valleydale Road and Jaguar Drive across from Veterans Park near Spain Park High School at 4600 Valleydale Road Birmingham, AL 35242. register online to reserve your spot. Show up and attend the sessions that you like. SQL Saturday! #29 is presented by Steel City SQL, the Birmingham Chapter of the Professional Association for SQL Server (PASS) and brought to you by these sponsors. Start Time Business Intelligence Database Administration Database Administration 2 SQL Development 9:15 AM Adam Jorgensen Louis Davidson Jim Wooley Sven Aelterman PowerPivot for Excel and SharePoint 2010 Database Design Fundamentals LINQ Kinq for the DBA Guy FILESTREAM in Breadth 10:30 AM Kevin Grohoske Louis Davidson Kevin Boles Sven Aelterman Location Aware Applications Using SQL Server 2008 Database Design Patterns SQL Server Memory Deep Dive FILESTREAM in Depth 12:15 PM Vincent Mayfield Janis Griffin Kevin Boles Aaron Nelson ASP.NET for the Enterprise with NLB and Clustering Tuna Helper for SQL Server DBA’s Parallel Query Execution Deep Dive PowerShell for Data Professionals 1:30 PM Robert Cain Geoff Hiten Rodney Landrum Joe Webb Introduction to SQL Server Integration Services Scale-Out the DBA. DBA Repository Update 2010 Using SSRS and SSIS. Tips & Tricks for Writing Better Queries 2:45 PM Barry Ralston Geoff Hiten Aaron Nelson Joe Webb Introduction to MDX for SQL Programmers Clustering for Mere Mortals. Virtualize This! SQL Server Locking & Blocking Made Simple
Coding for the Weekend
I’ve mentioned that my company is doing a data center move in the near future and I’ve been building out some PowerShell scripts to get ready for that. Before we get there though we are having to rebuild one of our test environments from the ground up. When I told my coworker Shyam of my approach he slightly disagreed and said he thought he had a better way. So today Shyam and I are holding a little contest, We’re each going to put together some code to detach all of the databases on one server, copy them to a new server, and then attach them all. Shyam is using an approach where he uses a lot of SMO calls, I am using an approach where I do a bunch of SQL queries. I’ll keep all of you posted on who wins, why, and give you the code once we’re done (minus any edits for security but it will be working code). It’s really great to have a coworker like Shyam to hold a friendly little competition like this with. I can’t wait to see how he ends up solving a couple of the problems that we’ve already come across. Here’s some of the P. of C. code that we’re both using to get us started; it’s basic ‘code that writes code’ but what we’re going after is ‘code that writes AND executes code’. This generates the list of databases and files including file# to use with the sp_attach_db command. I know that in 2005+ you should use ALTER DATABASE … WITH ATTACH but we have a mix of SQL 2000 and 2005 in this project. (which means I’m actually running this command through a linked server connection on the 2000 boxes.) SELECT CASE WHEN ROW_NUMBER() OVER(PARTITION BY db.name ORDER BY sf.filename) = 1 THEN ‘GO sp_attach_DB @dbname=”’+SUBSTRING(db.NAME, 1, LEN(db.NAME))+”’,’ ELSE ‘, ‘ END+‘ @filename’+CONVERT(VARCHAR, ROW_NUMBER() OVER(PARTITION BY db.name ORDER BY sf.filename))+‘=”’+SUBSTRING(sf.filename, 1, LEN(sf.filename))+”” FROM master..sysaltfiles sf JOIN master..sysdatabases db ON sf.dbid = db.dbid WHERE db.sid !=0×01 ORDER BY db.name, sf.filename This just generates the copy file statements in PowerShell that we will need to move the database files. SELECT ‘ copy-item “‘+SUBSTRING( saf.filename, 1, LEN(saf.filename))+‘” -destination “\\DestinationServerName\Drive$\’+SUBSTRING(SUBSTRING(saf.filename, 1, LEN(saf.filename)), 4, LEN(saf.filename))+‘”‘ FROM master.dbo.sysaltfiles saf JOIN master.dbo.sysdatabases db ON saf.dbid = db.dbid WHERE db.sid !=0×01 ORDER BY db.name, saf.filename
SQL Saturday #33 PowerShell Slide Deck
I’ve posted a copy of my slide deck that I used for my PowerShell sessions at SQL Saturday #33 in Charlotte. Anyone is welcome to use all or part of it if they would like to do they’re own PowerShell session. I’d love to hear any suggestions for improving it. I’m working on getting all of the scripts I used together so that I can post them up on the Scripting Guys repository. I’m still not satisfied with the Virtualization slide deck so I’m going to continue to tweak that one and hopefully post that after SQL Saturday #29 in Birmingham.
MacGyver Meets TSQL2sDay
Please be aware that I haven’t had a chance to double-check the technical details of this post before the deadline so if you see an error please feel free to comment about it. The events happened years ago but it’s funny how history has a way of repeating itself. Because I have too few hours in each day and I still suck at this blogging thing I am combining my MacGyver Moment and TSQL2sDay posts into this one so here we go. The Beginning: Once upon a time in a conference room on a very high floor sat over a dozen technical people trying to quickly fix a server that had begun crashing the day before. This company was pretty big. Their data, it was massive. We only worked with summary rows because of this and yet it was still in numbers that make you stop and shake your head every once in a while. For instance, one time the daily import process failed just a few percent in and it already had over 400,000 rows cleansed and loaded. To set the scene a little: We had developers, data folks, server experts, network experts, a SAN expert, the SAN expert’s boss, application SMEs, business liaisons, directors, and the company’s TAM from Microsoft. Oh and another SAN expert who also doubled as a Microsoft SQL Server MVP who just happened to specialize in High Availability (specifically clustering). This conference room had been turned into a War Room and was going to stay that way for as long as it took until the problem was fixed. The Problem: A problem child of an application that already had a reputation for crashing every once in a while had now started crashing every couple of hours. I don’t know the exact specs of the hardware anymore but it was pretty close to the top of the line. It had something like 16 LPs and 16 Gigs of RAM backed by nearly 2 dozen disks on a SAN that held over 200 disks. This server had a twin and based on the error logs that was part of the problem. It was in a single node failover cluster and all of a sudden it had started logging 15,000 millisecond timeout errors while trying to check on it’s disk resources. This timeout in turn was causing whichever node was active to freak out and think that it had lost contact with the SAN altogether. The Situation: While the overall SAN supporting this server is large by a lot of companies’ standards it was actually just a run of the mill small SAN at this place. In fact we later ordered up another one that looked just like it (only 2 years faster in every aspect) but at the time we kept on being told how solid this particular SAN was. There were a lot of moving parts and we were a fresh set of eyes being brought in. One of the moving parts that we had taken note of was that another server on different disks on the same SAN had recently been rebuilt as a 64 bit server. We took a look and said I’m sorry but your SAN just can’t support the needs of this SQL Server. You would have thought I had just told the SAN guys that I had caught them cross-dressing or something like that. They assured me that “No [little] Windows box” could ever take down their mighty SAN. Regardless of that we were stuck with that particular SAN for at least the next couple of weeks and needed to come up with something to help. The Other Problem: We asked for all the IO stats they could give us but unfortunately there was something about a new version that needed to be installed and needing to wait for an outage window and all that. Bottom line we were stuck with some total IO numbers over a wide enough time frame that they weren’t all that useful however; being a glorified mathematician I took all the numbers I had and tried to figure out the size of the ‘unknown’ portion by subtracting out the three servers I could get stats for myself. As it turned out in one particular time interval that was just enough information for me to notice a trend. It wasn’t much but it was more solid than anything else being offered up (or at least I felt so) and the situation was to say the least a little tense. The Lame-Brained Idea: One of the sever experts mentioned that he could “borrow” some RAM from a server that hadn’t gone into production yet and we could stick that in the servers (plural because they are a FC) that were crashing to see if that would fix it. Given the error message mentioned above I just didn’t think this would work. I had come up with something that just didn’t sound right even saying it but par for the course I said it anyways. I asked them to humor me and stick the “borrowed RAM” into a box that wasn’t crashing; that other box in the situation that had recently been rebuilt as 64 bit. They laughed at my idea and I don’t blame them. Unless you stepped through the situation in the the exact same order as I did this was like saying let’s just reboot and see if that fixes it (oh wait it does reboot every time it crashes ). It’s easy to decide to stick the only spare RAM you have into a server that’s crashing; it’s a little bit tougher to make the call to bring down a completely different system and have them stick the RAM in that one. If you’re wrong you’ve just caused a second outage for no reason. Stupid Windows Guys: That’s what they must have thought when they agreed to give my idea a try. We had all noticed the crashes had been happening almost like clockwork. Almost but no one could tie them back to anything else happening on that machine. As we all know, almost only counts in horse shoes and hand grenades. And occasionally, wild ass guesses. Funny thing happened though. We were able to guesstimate the next crash within 5 to 10 minutes at this point only it didn’t happen this time. The Hidden Pattern: Now I’ve always enjoyed learning about the storage engine internals but I sure ain’t no Paul Randal. I am a quick study and love a good disaster though. What I had found was that there was cube that was being built every couple of hours on the newly minted 64 bit server and while the system did a good job of still letting you query the old cube while the new cube was being built there would inevitably come a point where something was happening that was causing the SSAS engine to flush ooh-gobs of data back down to the disk. More data than the total size of the cubes even. The SAN ended up getting saturated with gigs and gigs of non-stop writes (and I hear with RAID 5 that’s like a bad thing but maybe it’s just a rumor). The Takeaway: Make sure you know what tools your SAN admin has available and more specifically find out what they can collect on a continuous basis for weeks on end, you might be surprised by the answer. The disks your server relies on may be fine but the controller cards accessing it might be getting saturated from something else. You may need to purchase some additional software or something. Look for solutions in places they might not make sense unless you hold your head just right. The error log is your friend. And above all, just because someone says it’s not possible, don’t completely believe them until they prove it to you. Tag-You’re-it: To keep the fun going I’m tagging Peter Schott and Matt Schultz (Matt blame Jorge) to find out what their MacGyver moments have been. Thank you Geoff Hiten for tagging me.
Early March 2010 PASS VC Presentations
Free Training from Virtual Chapters: AppDev: Jessica Moss on “Adding SSRS Report Bells and Whistles,” March 9, noon ET (GMT-5) DBA: Clifford Dibble“Microsoft SQL Server 2008 R2 Application and Multi-Server Management,” March 10, 12-1pm MT (GMT-7) Virtualization: “Microsoft Hyper-V” presented by Sylvia Vargas. March 16, noon ET (GMT-5) _____________________________________________________________________ Adding SSRS Report Bells and Whistles March 9th at 12:00 PM Eastern (GMT -5) Presenter: Jessica M. Moss Add to Calendar Producing a good looking report will wow your business users even if your data isn’t all that impressive. If you’ve ever had trouble finding the best way to display that data or how to show trends appropriately, this is the session for you. We will review grouping logic, graphs, gauges, and more in Reporting Services 2008. With the knowledge in this session, you won’t ever need to show a plain, boring report again. Jessica M. Moss Jessica M. Moss, an architect with Ironworks Consulting and a Microsoft SQL Server MVP, is a well-known practitioner, author, and speaker in Microsoft SQL Server business intelligence. Jessica has created numerous data warehousing solutions for companies in the retail, internet, health services, finance, and energy industries and authored technical content for multiple magazines, websites, and the book “Microsoft SQL Server 2008 Integration Services Problem-Design-Solution. Jessica enjoys working with the central Virginia community and speaks regularly at user groups, code camps, and conferences. You can read about her work on her blog,http://www.jessicammoss.com. How do I view the presentation? Attendee URL: Live Meeting link _____________________________________________________________________ Microsoft SQL Server 2008 R2 Application and Multi-Server Management March 10, Noon Mountain Time (GMT-7) Presenter: Clifford Dibble Are you missing critical utilization insights into your growing database environment? Attend this session to learn how the application and multi-server management capabilities shipping with SQL Server 2008 R2 will expose resource utilization data and help streamline consolidation management. New extensions in SQL Server Management Studio allow DBAs to quickly create SQL Server control point and enroll instances them into multi-server management. Once enrolled, DBAs can define utilization policies across applications and Database Engine instances and view resource utilization to maximize investments. Meanwhile, the introduction of a single unit of deployment helps accelerate deployments, moves and upgrades associated with consolidation management. Clifford Dibble: Clifford Dibble has been a program manager in SQL Server since 2002. Among other things, he was the active PM for “system views”, “DMVs”, “DDL triggers”, and “resource database” during the 9.0 release. During the 10.0 release, Clifford was off working on an incubation project. For the 10.5 and 11.0 releases, he is focused on multi-instance manageability and SQL management packs. Live Meeting Link (just paste into your browser): https://www.livemeeting.com/cc/usergroups/join?id=P7FJ9Z&role=attend _____________________________________________________________ Microsoft Hyper-V March 16th at 12:00 PM Eastern (GMT -5) Presenter: Sylvia Vargas Overview of how Microsoft IT uses, configures, and manages their virtualization environment. Learn how Microsoft leverages Virtualization for their SQL Server environments using System Center VMM. Sylvia is a Microsoft certified SQL Server professional and Senior Systems Engineer in Microsoft IT’s Virtualization Engineering group. Prior to joining Microsoft in 2006, Sylvia spent 24 years in various management and technical roles specializing in the architecting, development and deployment of data based solutions for companies including Areva, Boeing, ORACLE, Merrill Lynch, Nestle and Texaco. Sylvia has been a contributing author on The Real MCTS SQL Server 2008 Exam 70-432 Prep Kit by Syngress and an instructor at the University of Washington SQL Server Certificate program. Mark Pohto is the Principal Service Engineer responsible for the IT Cloud organization which provides virtual server engineering and delivery in Microsoft data centers. Previous to his current role, Mark was instrumental in architecting corporate monitoring systems, database administration automation solutions and IT SQL Server consolidation. As the Group Manager for the Microsoft SQL Server Center of Excellence, Mark managed the team responsible for the Microsoft Certified Architect program and the SQL Risk Assessment program. Event Link: https://www.livemeeting.com/cc/usergroups/join?id=QB6GSJ&role=attend&pw=Fz8b%7BT%27jN