I’ll be speaking at SQL Saturday #48 this weekend and I am happy to announce I will be unveiling my more advanced PowerShell session to help people customize scripts for their own environment. I will be showing off several new scripts that I built for my session at the PASS Summit this year. Please be ready to give plenty of brutal feedback on these scripts so that I can incorporate that feedback in the final versions that I take to the Summit. Besides my two sessions I will be joined by Eric Humphrey ( blog | twitter ) and Microsoft Scripting Guy Ed Wilson ( blog | twitter ) who will also be speaking about PowerShell. Eric totally stole the idea for his session from me but hey I guess ‘Great Minds Think Alike’ Just a quick glance at the schedule shows experts descending on Columbia from Richmond, Tampa, Orlando & Alabama just to name a few so don’t even think of coming up with some lame excuse that it’s too far to drive. We’ll also be joined by a bunch of SQL Experts from Microsoft’s Charlotte campus so it’s definitely work the trip and I hope to hear you heckle me there! Start Time Large Session BI 1 BI 2 DB Admin 1 DB Admin 2 DB Dev 1 DB Dev 2 Misc 08:15 AM SQLSat Staff Opening Remarks 8:30 AM Alejandro Mesa Parameter Sniffing Jessica Moss Make Reporting Services Work For You Stuart Ainsworth Confessions of a Data Integrator: Bad Designs Andy Warren DBA 101: The Basics Sergey Pustovit SQL Server Performance Related DMVs Alex Tocitu PowerSQL(CLR) Eric Humphrey Things To Do With PowerShell & SMO William Pearson Attribute Discretization in Analysis Services 9:45 AM Andy Leonard Database Design for Developers Wayne Snyder Information Visualization – Making great Charts John Welch Creating Custom Components for SSIS Janis Griffin SQL Server Service Broker – An Overview Aaron Nelson The Dirty Dozen: PowerShell Scripts for Busy DBAs Matthew Campbell Going Spatial Andrew Kelly Maximizing Plan Re-use in SQL 2008 Jose Chinchilla Get Cert! Get Cred! 11:00 AM Sergey Pustovit SQL Server Diagnostics Tools Unleashed Evan Basalik Troubleshooting SSRS Performance William Pearson Getting Started with MDX Sandra Mueller Data Files and Transaction Logs — beyond the GUIs Geoff Hiten Clustering for Mere Mortals Tim Chapman How, where, why, and when to use Dynamic SQL Rafael Salas Managing Database Schemas With VS201 DB projects Andy Warren Building a Professional Development Plan 12:00 PM Stuart Ainsworth Lunch & Red Gate Software Demo 1:15 PM Andrew Kelly Storage and I/O Best Practices for SQL Server Andy Leonard SSIS Design Patterns Mark Tabladillo Data Mining with PowerPivot 2010 David Taylor To click or to type, that is the question. Aaron Nelson PowerShell 2.0 Beyond the Dirty Dozen Brett Tomson T-SQL Enhancements in SQL Server 2008 Alex Tocitu CLR 101 Chris Skorlinski Top 5 fastest ways as DBA to get fired 2:30 PM Ed Wilson Windows PowerShell Best Practices for SQL DBA’s Julie Smith Cool Tricks to Pull from your SSIS Hat Sandra Mueller OLTP (yes!) Databases and Cube Design Ben DeBow Consolidated SQL Server Architectures Evan Basalik Diagnosing connectivity issues with SQL Server Stuart Ainsworth You Got XML In My Database? What’s Up With That? Bob Langley Introduction To Column Level Encryption Eric Humphrey Object Relational Mappers for the DBA 3:45 PM Geoff Hiten Bad SQL Jessica Moss Who Needs a Data Warehouse? Jose Chinchilla Business Intelligence: Decaffeinated Please! Chris Skorlinski Introduction to Transactional Replication Janis Griffin Tuna Helper – Proven Process for Tuning SQL Brett Tomson What’s New In SSRS 2008 (With Added R2 Flair) John Welch Processing Flat Files with SSIS Rafael Salas Planning your ETL architecture with SSIS 4:45 PM SQLSat Staff Closing Ceremonies & Raffle
September, 2010:
End of September Free Training from PASS VCs
Next-Level SQLCLR: Parallel Processing and Bulk Load SSRS R2: The New Stuff _____________________________________________________________ Next-Level SQLCLR: Parallel Processing and Bulk Load September 28nd 12:00 PM EDT (GMT -4) Presenter: Adam Machanic The power of SQLCLR as a performance tool has been well-documented at this point; certain queries, when re-written the right way using SQLCLR components, can run an order of magnitude more quickly. If you’re already using SQLCLR, or need that extra push, attend this session to discover how to take things one step further using advanced techniques and a custom SQLCLR component that will help you leverage the power of parallelism to burn through your rows more quickly than you ever thought possible. This session includes some of the material that will be covered in Adam Machanic’s full-day PASS Summit post-conference seminar, “A Day of Doing Many Things at Once: Multitasking, Parallelism, and Process Distribution.” More information on the seminar is available here: http://sqlpass.eventpoint.com/topic/details/AD311P Adam Machanic Adam is a Boston-based independent database consultant, writer, and speaker. He has been involved in dozens of SQL Server implementations for both high-availability OLTP and large-scale data warehouse applications, and has optimized data access layer performance for several data-intensive applications. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including “SQL Server 2008 Internals” (Microsoft Press, 2009) and “Expert SQL Server 2005 Development” (Apress, 2007). Adam regularly speaks at user groups, community events, and conferences on a variety of SQL Server and .NET-related topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, Microsoft Certified IT Professional (MCITP), and a member of the INETA North American Speakers Bureau. How do I attend? Attendee URL: LiveMeeting Link _____________________________________________________________ SSRS R2: The New Stuff September 29th 12:00 PM EDT (GMT -4) Presenter: Mike Davis In this session Mike shows you the new features in Reporting Services 2008 R2. You will learn how to use the Map control, Sparkline, Indicator, and Data Bars. See the new report builder 3.0. Learn about shared Data sets and report parts. Learn how to drill down from the US level to the state level using the map controls. Mike Davis Mike is a MCTS, MCITP, Senior BI consultant, and Trainer at Pragmatic Works. He is an author of two Business intelligence books. Mike is an experienced speaker and has presented at many events such as several SQL Server User Groups, Code Camps, SQL Server Launches, and SQL Saturday events. Mike is an active member at his local user group (JSSUG) in Jacksonville, FL. URL: https://www.livemeeting.com/cc/usergroups/join?id=H2JWQQ&role=attend&pw=tS3%24Gtj%60z
Finding SQL Servers with PowerShell
Have you ever clicked a drop-down to connect to one of your databases and noticed an instance that you don’t remember setting up? Or maybe a server that was recently setup for some new application in your organization, that no one mentioned it needing SQL, but you come back from your day off and notice that server is now one of your available SQL Servers? If your office is like mine those things happen all too often. There are several different methods to finding out just how many SQL Servers are running on your “network” right now. Each method has pros and cons. But the biggest problem that I run into is: ‘what is defined as the network?’ Is it just the network of computers at your office? Does it include servers at your collocation facility? What if you have a completely separate domain for Development? The first method I’m going to point out to you is really easy to use but it’s results vary depending on your network rights and where you run it from, but, it still can find machines that you didn’t even know about so lets have a look. Wait, why would I start out with an example that’s so imperfect? Because this method uses the same .Net components that are used to generate the list of servers you see when you click on the “<Browse for more>” option in the drop down of the SSMS connection window*. This means that you’ll see a list that you recognize (probably). Fire up the ISE and kick off this command: [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() Now if you’re just on you laptop or sitting at home or something you’ll probably get a nice short list like this: But if you’re at work, you’ll get back a much longer list; maybe even longer than you expected Heck how many servers just came back anyways! The answer to that question is really easy, just pipe the output of the command to Measure-Object and find out like this: [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() | Measure-Object and you’ll get some output like this: Now like I said earlier, these results are going to vary depending on where you run the command so here’s what I recommend. Run this command from your desk. Then try it from a machine that is on a different network segment that you’re usually on – like a Dev domain. Then go home, VPN in and run the command, see how many you get back now. OK that’s great but with all of these variances can I do anything useful with this example? Sure you can. Take your results and put them in a table. First build a simple table to use: CREATE TABLE FoundSQLServers ( ServerName VARCHAR(128), InstanceName VARCHAR(128), IsClustered VARCHAR(3), VersionNumber VARCHAR(64) ) Then build some insert statements off of the results like this: $SQL = [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() | ` foreach { “INSERT INTO dbo.FoundSQLServers VALUES (‘$($_.ServerName)’, ‘$($_.InstanceName)’, ‘$($_.IsClustered)’, ‘$($_.Version)’)” ` >> C:\Temp\INSERTFoundSQLServers.sql } To open up the file that was just created in Management Studio just run this Ssms.exe C:\Temp\INSERTFoundSQLServers.sql …and you should get some nice insert statements for that table like this: INSERT INTO dbo.FoundSQLServers VALUES (‘WIN7ULT’, ‘KILIMANJARO’, ‘No’, ’10.50.1600.1′) Go ahead and insert those rows into your table, maybe even give the table a little bit different name like: FoundSQLsFromVPN or FoundSQLsFromDev. Run some queries to compare the results you get from different network segments; see which servers we found both at home (through VPN) and at work from your desk; and more importantly which ones are only found from one of those locations. We’ll take a look at a couple of different methods I’ve learned about in future posts but I wanted to get you started with finding out how well this method finds your servers and knowing how your network location can affect it. Just in case you’ve recently started a new job or something Special Thanks: I just wanted to take a moment to thank Grant Fritchey ( blog | twitter ) for doing a tech-check on this post and helping convey my points a little more concisely. * Small Disclaimer: I was told that it’s the same .Net component used by applications like SSMS but since I don’t work at Microsoft and I’m not a developer I wouldn’t even know where to start to verify this but the list produced has always been very similar or down-right identical.
Late September Free Training from PASS VCs
The DBA and Virtualization Virtual Chapters hope that last weeks 24 Hours of PASS got you in the mood for more free training. Here’s what they have on tap for you this week: Top 10 SQL Server Mistakes and Mis-steps Storage and Virtualization for the DBA _____________________________________________________________ Top 10 SQL Server Mistakes and Mis-steps September 22nd 12:00 PM EDT (GMT -4) Presenter: Tim Ford From Tim Ford’s (@SQLAgentMan’s) continuing blog series on the same topic comes a presentation in which we’ll go over common mistakes made by new and seasoned SQL Server Professionals alike. Learn how to avoid these issues before they happen and correct matters after you’ve seen them in your environments. Tim Ford Tim is a Microsoft SQL Server MVP and SQL Server Professional with Next Wave Logistics, has been working with SQL Server since 1999. He is also an author of SQL-centric articles for MSSQLTips.com, SQLServerCentral, and Simple-Talk.com and just completed a book on the topic of SQL Server DMVs with Louis Davidson. He is the host for the annual Quizbowl at the PASS Summit each Fall. Live Meeting Link: https://www.livemeeting.com/cc/usergroups/join?id=5B3JGF&role=attend _____________________________________________________________ Storage and Virtualization for the DBA September 23rd 12:00 PM EDT (GMT -7) Presenter: Denny Cherry This is a preview of Denny’s full day PASS Summit pre-conference session in which Denny disccusses how storage can be one of the biggest bottlenecks when it comes to database perfomance and how to troubleshoot storage performance issues and tie the virtual platforms to the storage array so that you can maximize the storage performance for your SQL Servers and the virtual environment. Denny Cherry Denny has over a decade of experience managing SQL Server, including MySpace.com’s over 175 million user installation, one of the largest in the world. Denny’s areas of technical expertise include system architecture, performance tuning, replication and troubleshooting. Denny currently holds several Microsoft Certifications related to SQL Server as well as being a Microsoft MVP. Denny is a longtime member of PASS and Quest Software’s Association of SQL Server Experts and has written numerous technical articles on SQL Server management. Live Meeting Link: https://www.livemeeting.com/cc/usergroups/join?id=J8RZQ8&role=attend&pw=RFxB5D%2FsM
Speaking About PowerShell at SQL Saturday in Raleigh
I’ll be speaking at SQL Saturday #46 in Raleigh this weekend! I’m really excited to be speaking at this event because I talked Microsoft Scripting Guy Ed Wilson ( blog | twitter ) into being a presenter too and take a wild guess what we’ll both be speaking about! It wasn’t really hard to get Ed to join us actually; I sent him a tweet saying something like ‘Hey Ed can you come out and speak to us SQL folks’ and his reply was ‘I’ll submit something tonight’. Might want to keep that in mind the next time that you’re looking for a speaker. Take a look at the schedule and you’ll see that it’s packed with SQL Server experts so come on out and join us, the football hasn’t gotten all that good yet so you’ve got no reason not to. Also, I have a new demo that Lee Holmes helped me get working that if you love to hate extracting data from Excel, this will make your life much simpler. Start Time Track 1 Track 2 Track 3 Track 4 Track 5 Track 6 8:30 AM Andy Leonard Build Your First SSIS Package Rafael Salas Dimensional Modeling: Why Should You Care? Sergey Pustovit SQL Server Performance Related DMVs Kevin Boles Common TSQL Programming Mistakes* Flavio Almeida Self-Service BI with PowerPivot Geoff Hiten Clustering for Mere Mortals 9:45 AM John Welch Processing Flat Files with SSIS Mark Tabladillo Data Mining with PowerPivot 2010 Jana Sattainathan Partitioning in SQL Server 2005/2008 Tim Chapman How, where, why, and when to use Dynamic SQL Jessica Moss Who Needs a Data Warehouse? Aaron Nelson The Dirty Dozen: PowerShell Scripts for Busy DBAs 11:00 AM Chris Skorlinski Introduction to Change Data Capture Mark Tabladillo Document Classification using DMX in SSAS Andrew Kelly Maximizing Plan Re-use in SQL 2008 Kevin Goode Embracing the CLR Mike Davis Reporting Services 2008 R2, the New Stuff Brian Kelley Fortress SQL Server 12:00 PM SQL Saturday Red Gate Software 01:00 PM John Welch Patterns for SSIS Configuration and Deployment William Pearson Attribute Discretization in Analysis Services Grant Fritchey Identifying and Fixing Performance Problems using Andy Leonard Database Design for Developers Jason Hall Recipe for a Happy DBA – A Guide for SQL Server De Ed Wilson Windows PowerShell Best Practices for SQL DBA’s 02:15 PM Andy Leonard SSIS Design Patterns Steve Wright The Shade Tree Mechanic’s Guide to SSAS Thomas LaRock Performance Tuning Made Easy Allen White XQuery Basics Geoff Hiten Bad SQL Brian Kelley The Dirty Business of Auditing 3:30 PM Chris Skorlinski Integrating CDC and SSIS for Incremental Data Load Mike Davis Using Parameters in SQL Server Reporting Services Kevin Goode Statistics, how to prove everything but the truth. Kevin Boles Advanced TSQL Solutions SQL Saturday Q & A — Stump the Speakers Amy Styers Virtualizing SQL Best Practices 4:30 PM SQL Saturday SWAG Give Away
24 Hours of PASS: Summit Preview and Me?
I *Might* be speaking at the 24 Hours of PASS: Summit Preview event. I say “Might” because I’m speaker #25, the first Alternate. The lineup is pretty freaking awesome so I hope that no one cancels but if they do I’ve got some fresh PowerShell for SQL Server content to show everyone. I’ll also be moderating questions for Kalen Delaney’s session on Locking tomorrow at 3pm EDT. Make sure you pick up a case of Red Bull one your way to work because you’re not going to want to miss a minute of this FREE TRAINING. Also, this time the event has been super-sized with 4 additional Microsoft sessions so if you already signed up and you didn’t know that, check them out; they do require additional registration. And yes, technically that makes me speaker #29 so let’s just go ahead and change that to speaker #1A 24 Hours of PASS: Summit PreviewSeptember 15, 2010 Session 01 (DBA) – Start time 12:00 GMT on September 15 I Am a DBA – Why Should I Care About SQL Server 2008 R2? Presenter: Peter Ward Attend Meeting Now Session 02 (DBA) – Start time 13:00 GMT on September 15 Gather SQL Server Performance Data with PowerShell Presenter: Allen White Attend Meeting Now Session 03 (BI) – Start time 14:00 GMT on September 15 Why Data Warehousing Projects Fail (And What You Can Do About It) Presenter: Craig Utley Attend Meeting Now Session 04 (DBA) – Start time 15:00 GMT on September 15 Hardware 201: Selecting and Sizing Database Hardware for OLTP Performance Presenter: Glenn Berry Attend Meeting Now Session 05 (PD) – Start time 16:00 GMT on September 15 Managing Teams Presenter: Andy Leonard Attend Meeting Now Session 06 (DBA) – Start time 17:00 GMT on September 15 Enforcing Compliance with Policy-Based Management Presenter: Ken Simmons Attend Meeting Now Session 07 (BI) – Start time 18:00 GMT on September 15 Intro to MDX Presenter: Stacia Misner Attend Meeting Now Session 08 (Dev) – Start time 19:00 GMT on September 15 Isolation vs Concurrency: What Are the Choices? Presenter: Kalen Delaney Attend Meeting Now Session 09 (DBA) – Start time 20:00 GMT on September 15 Identifying Costly Queries Presenter: Grant Fritchey Attend Meeting Now Session 10 (PD) – Start time 21:00 GMT on September 15 How to Rock Your Presentations Presenter: Douglas McDowell Attend Meeting Now Session 11 (Dev) – Start time 22:00 GMT on September 15 eXtreame Database Design Presenter: Paul Nielsen Attend Meeting Now Session 12 (BI) – Start time 23:00 GMT on September 15 Delivering KPIs with Analysis Services Presenter: Peter Myers Attend Meeting NowMicrosoft Bonus Session – Start time 00:00 GMT on September 16 Peer-to-Peer Transactional Replication Presenter: Jean-Yves Devant Attend Meeting Now Microsoft Bonus Session – Start time 01:00 on September 16 TempDB Configuration and Management Presenter: Dipti Sangani Attend Meeting Now September 16, 2010 Session 13 (BI) – Start time 12:00 GMT on September 16 Upgrading DTS Packages to SSIS Presenter: Brian Knight Attend Meeting Now Session 14 (DBA): Start time 13:00 GMT on September 16 Statistics: How to Prove Everything but the Truth Presenter: Kevin Goode Attend Meeting Now Session 15 (BI) – Start time 14:00 GMT on September 16 SQLBI Methodology Presenters: Alberto Ferrari and Marco Russo Attend Meeting Now Session 16 (PD) – Start time 15:00 GMT on September 16 Preparing for Your Next Job Presenter: Chris Shaw Attend Meeting Now Session 17 (DBA) – Start time 16:00 GMT on September 16 Storage for the DBA Presenter: Denny Cherry Attend Meeting Now Session 18 (DBA) – Start time 17:00 GMT on September 16 SQL Server 2008 R2 StreamInsight Presenter: Klaus Aschenbrenner Attend Meeting Now Session 19 (Dev) – Start time 18:00 GMT on September 16 T-SQL Brush-Up: The Best Things You Forgot You Knew Presenter: Jen McCown Attend Meeting Now Session 20 (DBA) – Start time 19:00 GMT on September 16 Automate Your Database Maintenance Presenter: Brad McGehee Attend Meeting Now Session 21 (Dev) – Start time 20:00 GMT on September 16 Relational Database Design for Utter Newbies Presenter: Kevin Kline Attend Meeting Now Session 22 (BI) – Start time 21:00 GMT on September 16 Zero to Cube: Fast Track to Analysis Services Development Presenter: Adam Jorgensen Attend Meeting Now Session 23 (Dev) – Start time 22:00 GMT on September 16 Top 10 Design Mistakes Presenter: Louis Davidson Attend Meeting Now Session 24 (Dev) – Start time 23:00 GMT on September 16 Understanding SARGability Presenter: Rob Farley Attend Meeting Now Microsoft Bonus Session – Start time 00:00 GMT on September 17 SQL Server R2 Utility Presenter: Eddie Fong Attend Meeting Now Microsoft Bonus Session – Start time 01:00 GMT on September 17 SQL Server Security Tips & Tricks Presenter: Li-Sung Lee, Microsoft Attend Meeting Now http://www.sqlpass.org/24hours/Fall2010
Picture the Index: T-SQL Tuesday #10 – Indexes
I don’t really have the time to write this month, but indexes are such a key feature of databases that I thought I should. A whole post would take more time than I have. Instead I’m going to mention something that Rob Farley (of LobsterPot Solutions in Australia) said in a SARGability talk for the AppDev Virtual Chapter of PASS a few months back. Rob talked about the way that we find entries in an index using a Seek operation, and compared to using a phonebook. In particular, he described the bit at the top corner of the phonebook, which we use to find the right page before looking at each record. This is very much how an index works. An index is stored in a b-tree, with the levels at the top being like the corner sections in the phonebook, and the leaf level of the tree being like all the records there. A quick bit of searching flickr found an image at http://www.flickr.com/photos/blinky5/376596220/ which describes this perfectly. If you’re looking for Wilma Todd, you can use the top corner to figure out which page she’s on and then find her record on the page very easily. So what’s my point?: The next time that you’re picking a data type for a column think of this. If you use a data type that’s twice as large as what you really needed (int vs. smallint, nvarchar vs varchar) and then you realize you need to put an index on it, it would be be like doubling the font in this phone book thereby requiring twice as many pages to print the book. May not seem like much but when you have to scan for data you’re now going to have to physically touch twice as many pages. And hey, now the books twice as heavy as it needed to be just like your backups are going to be! Anyway, I don’t have any more time to spend on this post, gotta get my code ready in case I need to fill in tomorrow for someone during the 24 Hours of PASS, so I’ll just publish it and wish you all a Happy T-SQL Tuesday.