June 20, 2011
This perforce has to be a short note, as you will see, things are very busy right now, but I wanted to take a moment to send a giant THANK YOU! out to the SQL Community in general, and to a few people specifically.
Why am I so thankful, you ask? I got a job! I will be the SQL Server DBA at a prestigious private school in New Hampshire, where I will get to work with the latest SQL 2K8 R2 running on Windows Server 2K8 R2. They are building a data warehouse for reporting, and consolidating a bunch of disparate older product data onto SQL Server, and I can’t wait!
OK, you say, you got a job. Again, why are you so thankful to us, you ask? Simply because it wouldn’t have been possible, I wouldn’t be where I am today without the help of you guys. The PASS Chapter folks, the SQL Saturday folks, all the people at last year’s Summit, the folks who answer #sqlhelp, and just the community in general. I learn from you guys every day, and it is this learning that has allowed me to get to this position.
I specifically want to call out a few folks who went the extra mile for me personally, and know that these folks are not listed in any particular order, and if I leave someone out, I am so sorry! Thank you Jorge Segarra (Blog | Twitter), Mike Walsh (Blog | Twitter), Jack Corbett (Blog | Twitter), Tim Radney (Blog | Twitter), Adam Jorgenson (Blog | Twitter), Brian Knight (Blog | Twitter), Nicholas Cain (Blog | Twitter), Paul Randall (Blog | Twitter), Julie Smith (Blog | Twitter), Audrey Hammonds (Blog | Twitter), Jes Borland (Blog | Twitter), All the SQL University professors; all of these people and so many more have helped me, taught me, made me a better person, inspired me, made it possible for me to turn in my two-week notice this morning, and go home to New England.
Thank you, thank you, thank you, a thousand times thank you!
April 25, 2011
I’d like for you all (y’all, if you’re in the South to join us at the Application Development Virtual Chapter on Tuesday, April 26, 2011 at Noon Eastern, to enjoy the infamous Louis Davidson present on Great Relational Databases.
The link for the LiveMeeting is this –> Attendee Link
The blurb for the presentation is (from the AppDev_VC site)
Characteristics of a Great Relational Database
April 26, 2010, 12 PM Eastern
Presenter: Louis Davidson
Attendee URL: LiveMeeting
When queried, most database professionals would mention normalized as one of the most important characteristics that tell the difference between a good and bad database design. I won’t disagree in the least, but there is so much more to be considered. Even if you did a great job of normalization, poor naming, poorly implemented keys, too many or too few indexes, and so on can derail your design. In this session I will present seven primary characteristics of a design that differentiates between an ugly design that will have your colleagues nitpicking you to death and one that will have them singing your praises. Characteristics such as comprehensible, documented, secure, well performing, and more (including normalized, naturally) will be discussed.
Louis has over 15 years as a corporate database developer and architect. Currently he is the Data Architect for the Christian Broadcasting Network and NorthStar Studios in Nashville, Tennessee. Nearly all of Louis’ professional experience has been with Microsoft SQL Server from the early days to whatever is the latest version currently in beta. Louis has been the principal author on four editions of a book on database design, including one for SQL Server 2008. Louis’ primary areas of interest are database architecture and coding in T-SQL, with experience designing many databases and writing thousands of stored procedures and triggers through the years.
How do I attend?
Attendee URL: LiveMeeting
I have been learning a lot lately, both SQL Server and .NET, and today I’d like to tell you a little about what I’ve learned on the SQL Server side.
I took a Microsoft eLearning course this weekend, called Introduction to Virtualization of Microsoft SQL Server (Retired). I don’t know why it’s retired, I was able to take it, and it seems very current, working with Microsoft Server 2008 R2 and SQL Server R2. If it weren’t retired I could give you a link, but you can find similar courses at this link. I don’t have virtualization at my current place of employ, but have been threatened with it I thought it prudent to learn, and, as this course is only a two hour introduction, I thought it would be good to take it.
The course focuses on Hyper-V as the virtualization solution, and goes to great lengths demonstrating how to install and set up the host and guest machines, getting them networked and clustered, then demonstrating getting SQL Server installed as a pair of nodes on a cluster.
The course describes both clustering two SQL Servers on one host, and also clustering two physical machines, each with a virtual machine with SQL Server on it, all of them clustered. This seems like a great HA (High Availability) solution, as there is multiple redundancies going on. Maybe even better with two VMs on each machine, but the course didn’t cover that.
Another thing the course didn’t cover (because it was from Microsoft, I’m sure) was other Virtual machine solutions, such as VMWare, and VirtualBox. I’ve never dealt with VMWare myself, but I do have VirtualBox, and I am going to be playing around with clustering SQL Server on it, and see about making my own Highly Available Cluster.
Update: Funny how things work out sometimes! Looking through Twitter this afternoon, I saw that @MSLearning put out a tweet about VMWare learning! I’m going through the videos as soon as I have a spare moment, you can check them out here –> Technet Edge Categories
April 11, 2011
Among a bunch of other things that I do, I am also the Volunteer Coordinator for the Application Development Virtual Chapter of the Professional Association for SQL Server (AppDev_VC of PASS). We had one of the best Marketing Coordinators ever, recently made SQL Server MVP Aaron Nelson (Blog | Twitter), but as is the case with many great people, he has left us for other pursuits. This leaves us here at AppDev_VC with myself and our awesome Speaker Coordinator John Jakubowski (Blog | Twitter).
Now, I’d like to think that I’m the best at getting things done in a timely manner, but, like everyone, I am only human, and one thing piles on another, priorities get shifted, and things fall through the cracks. This time, it was the marketing for the VC that fell through. I think it was a case of I thought someone else was doing it, someone else thought I was doing it. Wasn’t getting done!
In an effort to correct that, I present this blog post
We are pleased and proud to announce that Andy Warren (Blog |Twitter) will be presenting for us Tuesday, April 12, 2011 at 12 NOON Eastern time! His presentation will be on building applications not only for end users, but for DBAs to like also! I, for one, can’t wait to hear what he has to say, and I hope to see lots of you there also! Below I have copied the Description and Attendee links that are also posted on the AppDev_VC site, http://appdev.sqlpass.org.
Building Applications That A DBA Will Love
April 12, 2011, 12 PM Eastern
Presenter: Andy Warren
Attendee URL: LiveMeeting
It’s natural for the focus to be on the end user when we build applications, but it’s important and worthwhile to build into our design some things that make it easier for the DBA to provision, tune, and support the product we deliver. So what does a DBA care about? Performance of course, but also security, scalability, space usage, and yes, even documentation. This presentation will cover tips in a number of areas that address the concerns of the DBA, and we’ll talk about why you should care about making the DBA happy too!
Andy Warren is a SQL Server consultant and trainer who occasionally writes some code too. He’s on the PASS Board of Directors, was a founding partner in SQLServerCentral.com, started the SQLSaturday event format, and is currently a SQL Server MVP. Andy blogs at www.sqlandy.com, and can be reached via Twitter as @sqlandy and on LinkedIn at www.linkedin.com/in/sqlandy.
How do I attend?
Attendee URL: LiveMeeting
March 24, 2011
This blog is all about learning, and recording things that I have learned. The thing I learned most recently is that I have been remiss in recording what I have learned. Mostly through the lack of time that has been my bane for months, but also because of the high level of interest I have had in what I have been learning, and for a SQL Server DBA, it’s a strange thing – it’s .NET!
Let me work backwards through time. I will be a bit brief, because, once again, lack of time is creeping up on me (it’s nearly midnight as I write this, and I need to be up in the morning.) This past weekend, I attended, and spoke at, SQL Saturday #70. I have been to quite a few of these events in the last couple of years, and they always recharge my batteries, so to speak.
I won’t go into session by session detail about the event, but I will say it was one of the more well run SQL Saturday’s I have been to, with an as usual for SQL Saturday stellar lineup of speakers (aside from your humble author, of course) people like Andy Leonard, Julie Smith, Jessica Moss, and those are just the BI track folks. I spoke at some length with several people at the Speaker dinner and the actual event. One man, Brian K McDonald, said to me, if you’re going to get anywhere in this business, you have to blog, blog often, and blog more. Along with the general battery charge mentioned, that was the impetus for me to dust off my WordPress password and get back out here
Like I said, briefly and going backwards in time, the .NET learning I have been subjecting myself to is the result of a huge project given to me at my place of work. While I am the DBA there, I am also sort of a jack of all trades, dabbling in mechanical Quality issues, training, SQL Server, and Office programming. We have Office 2003 there, still, and, if you’ve ever done any programming with that version, maybe you know what a bear it is.
The project is to take our paper work instructions, and create a paperless work instruction delivery system. So far, I have written a VB.NET program do deconstruct our Word 2003 instructions into their constituent main body text and what Office calls Shapes (all the Textboxes, Callouts, Pictures, etc.) get them into a memory stream of XML, and then get that resultant XML into a SQL Server database. Then I wrote another program that takes a part number and station ID, and displays the correct page(s) of work instruction for that station. It’s not done yet, right now I display everything except the callout lines, and I hope to have that going tomorrow. After I get that, I will wrap it all up into a WCF Service and Client set of programs so that I can have the service running on our server and get Thin Clients at each workstation to display the instructions.
Brief, as I said, but I hope you get a sense of how big this thing has been, how much of my time it has taken. All of this is to, in my own way, apologize for not being around much these last couple of months. I know some of you asked, after the first few weeks, why I wasn’t blogging, and I know I was letting some folks down. I am sorry. I have several blog posts lined up describing in more detail what I have learned in my .NET adventure, and how it relates to SQL Server, how I have built the database and what stored procedures and whatnot I am writing for it, both for my own documentation and, hopefully, your interest.
Thanks, and I hope to see you around here more, as I plan on being around here more!
October 19, 2010
I’ll be presenting my “To Click or Not to Click…” Presentation for the Columbus, GA Chapter of PASS, Wednesday, October 20, at the Main Branch of the Columbus Public Library, 3000 Macon Road.
I’m quite honored to be asked! I’ve only spoken four times ever before, two SQL Saturdays and twice at this User Group. I assume, of course, that someone else bowed out
The blurb on my session goes something like this (well, exactly like this, as I copied and pasted it from the UG site!!)
When administering SQL Server, in SSMS are you a clicker or a typer? This session explores the use of the GUI vs. using scripting in SSMS to get day-to-day tasks accomplished, with a bent toward scripting for increased accuracy, control and speed. Tasks are illustrated through the use of the Day-to-Day section of Brad McGehee Sure DBA Checklist at http://www.bradmcgehee.com , Used with Permission and Great Thanks.
Please visit http://columbusga.sqlpass.org/default.aspx for more information about our little group, and I hope to see you there!
October 5, 2010
Forgive me readers, it has been too long. Seems the last time I sat down to write a blog post, it was to tell you about a SQL Saturday I attended, and now I am doing it again. Good thing I go to a lot of these things, you might never hear from me
Seriously, though, I have had a month of too busy and not enough learning. My job entails doing both Quality and Data work, and lately we have been having a lot of Quality problems, so I have been doing less Data work. Not none, but just maintenance stuff, nothing to write here about.
I am very excited to tell you about this past SQL Saturday, though, if you’ve a mind to listen. It was so exciting for a couple of reasons. One, because I go to so many of these things, I get to see people more, people I highly respect and admire, and this trip was no exception. Two, I was a speaker at this particular event, and this time (as opposed to my session in Tampa in January) it actually went well!
This event was held in Columbia, SC, put on by the Midlands PASS Chapter headed up by K. Brian Kelly (Blog | Twitter), whom I met for the first time in Nashville at #sqlsat51. They did a really good job up front with planning, had a great venue at the Midlands Technical College, and a wonderful restaurant for the speaker dinner, a place called Grecian Island, which served both Greek and Italian food.
Another reason this was to be a great trip was that I was to have a riding buddy for the trip. Tim Radney (Blog | Twitter) got up with me on Twitter asking if I was going, suggesting we could ride together. Tim is a fellow member of the Columbus, GA PASS Chapter where I am also a member, and, as I had to pass through Columbus on the way, it would be perfect to pick him up and ride with him. Unfortunately, in the days leading up to the weekend, he took ill, and so I made the trip solo. Fortunately, as the day got older, Tim felt better and got himself to Columbia, so he didn’t miss out on the SQL Goodness.
I arrived early to the Speaker Dinner; I got to meet up with Brian at first, then others started to arrive – Andy Warren (Blog | Twitter) , Godfather of SQL Saturdays; Julie Smith (Blog | Twitter), one of the first people I ever met at a SQL Saturday, who came to present her Cool Tricks to Pull from your SSIS Hat (who also brought her Mom, who became the group’s Mom for the weekend J); Aaron Nelson (Blog | Twitter), *THE* PowerShell Guru, and one of my partner volunteers with the Application Development Virtual Chapter; Andy Leonard (Blog | Twitter) himself; Ed Wilson (Blog | Twitter), Microsoft’s Scripting Guy, who brought his Scripting wife, who is a lovely lady; Eric Humphrey (Blog | Twitter), another of my partner volunteers at AppDev_VC; Jessica Moss (Blog | Twitter), Business Intelligence Queen, Stuart Ainsworth (Blog | Twitter), who gave me impetus to start out speaking; Geoff Hiten (Blog | Twitter), from whom I first learned about Bad SQL and how to fix it, and of course Jose Chinchilla (Blog | Twitter), newly named President of the Tampa BI User Group. Wow, I have made some incredible friends along the journey, and it feels so good to have these high caliper people walk into a room, see me and say “Hi, David!” If I hadn’t mentioned it lately, the SQL Community Rocks!
During the afternoon, Tim Radney was driving up to us, and he got to the restaurant in time for me to have the privilege of introducing him to several of the SQL Community’s Rock Stars. I was very happy to be able to do so!
I did meet others that night, but I am notoriously bad with names, and need to meet folks more than once to be able to remember them, so if I met you this weekend and didn’t mention you, I am sorry, and please reintroduce yourself in the comments!
In the morning, I shared breakfast at the hotel with Stuart, and then drove the few minutes to the event site. Did I mention great planning? I was at one of the recommended hotels, which put me no more than a mile or so from the facility, so easy-peasy getting there! Registration was a very calm affair, people behind the tables having it pulled together very well, an envelope for every registered attendee, those for speakers with a double asterisk on them – each filled with sponsor raffle tickets preprinted with name and contact info, and enough eval forms for every session one might attend.
The opening remarks were held in a large enough auditorium to hold everyone, during which we learned that all the speakers would be given Prize Tickets to give out during their sessions to those they felt deserving, something that would ease raffle pressure at the end of the day. Sponsors were setup in an area right at the bottom of the stairs to the majority of the classrooms, and incidentally, the way to the lunchroom, so you couldn’t help but stop and chat with some of the nice folks that were there. Someone else mentioned in their wrap up that there should have been more onsite presences, and I agree, I think there were only four actual vendors present.
I started my day with Jessica Moss, and I can’t imagine a better way to start the day! She did her presentation Make Reporting Services Work for You, a very good slightly higher level than intro into what can be done in Reporting Services, using some of the higher level things like adding custom code and expressions into a report. As it turned out, I was on a BI track this day, aside from my session.
Next I joined Wayne Snyder (Blog | Twitter) in his Information Visualization – Making great Charts. I started out my journey in SQL Server making charts and graphs, etc. in my day to day work as a Quality Technician, graduating to creating Digital Dashboards, which I learned about by reading Steven Few. This session was all about how the eye and brain sees elements of visualization, and how to use those ideas to develop best practices. Very good timing for this session, as I have been tasked with making a new set of dashboards for our production facility.
I took the next hour off from joining any session, as I wanted to spend some time networking, and of course I was the only attendee to do so, so I spent time in the speaker room talking with some of the other speakers (and getting Andy Leonard to autograph my copy of Deep Dives! There were some very interesting discussions going on about how different shops work, and Project Manager expectations vs. reality. It was so high level I mostly listened, though.
Then it was lunch time, during which Stuart Ainsworth demonstrated a new RedGate product, SQL Source control. Looks very promising and Stuart did a great job of letting us know its best uses.
After lunch it was my session. We had trouble with the projector, enough so that it took three guys helping me before we finally got it going, and I was almost ten minutes into my time. I had a really cool video I wanted to play to go along with my mascot Kermit, but we were late enough that I just launched into my talk. I spoke about “driving around” SSMS, and how to do things with the mouse or the keyboard. I was lucky enough to get permission from Brad McGehee to use part of his “Brad’s Sure DBA Checklist.” I used the ten points in the “Day to Day” portion of the list, showing how to do each of the things with the mouse and with scripts. Thanks so much, Brad!
I’ll be honest here, I only had three people in my session, but I will be even more honest in saying that each of them learned something from me, and that, my friends, gives this learner a brightly glowing feeling! To be able to give back to the community has been a big goal of mine. I didn’t do so well in my first foray into speaking back in January, but I think I found the right session for me to present at this point in my learning and career that I can use to give back successfully.
After my timeslot, I then went to see Julie giving her SSIS presentation. I had actually seen her do this topic before, but boy, has she improved it! Not that it was bad last year, not at all, but it’s much better this year than I remember. Maybe I had no idea what she was talking about before, and I understand it now, or maybe she made changes to it, I don’t know, I just know it was rocking good! She tells things you won’t find anywhere, saving much pain in the learning process. Thanks, Julie!
The last session I attended was also a BI session, John Welch’s (Blog | Twitter) Processing Flat Files with SSIS. John is a very good presenter, covering all sorts of details that can sneak up and bite you if you’re trying to do this type of activity. This was also good timing, as I am starting to get into doing things like this at work, also. (Just between me and you, though, he’s not nearly as pretty as Jessica or Julie
At the end of the day, the SQL Saturday staff had the closing ceremony, thanking everyone for attending, volunteering, speaking and sponsoring, and then held the sponsor raffle, giving away software licenses, MSDN Subscription, training time with a local training company, cameras, etc.
All I can say in the end was I had a really good time, I think I have found the beginning of my speaker groove, and I can’t thank Brian and his cohorts enough for such a great event! If you’d like to experience it, albeit second-hand, you can find speakers’ PowerPoint decks and demo scripts at http://www.sqlsaturday.com/48/schedule.aspx.
I’ve said it before, I’ll say it again and again, the SQL Community Rocks!
August 30, 2010
Thoughts on #sqlsat51
I made the trip up to Nashville to attend SQL Saturday #51 last weekend (August 21), and I am so glad I did! I’ve written, and read, ‘reviews’ of SQL Saturdays before, detailing things like how registration worked, whether there were enough signs pointing the way, how foot traffic flowed, etc. These are good, and probably helpful to people putting on SQL Saturdays, but one thing I learned this time was that you blog for yourself, and hope others are interested. Consequently, I will be blogging not about the mechanics of this SQL Saturday, but how I experienced it, and what I took away from it.
First, though, I’d like to say that I drove up Friday morning, and stayed at a hotel in Franklin, TN. This was about 20 miles from the event, but I justified staying that far away because I drove over six hours to get there, another 20 minutes the next morning wouldn’t kill me, and I found a hotel with an indoor pool and a decent breakfast for under a hundred dollars. Franklin, I found out, has a really neat downtown area, has a great Italian restaurant, and has a Woodcraft store in town! Nearby is a place called Arrington Vineyards, a local winery. They have a tasting, some incredible Riesling, and jazz on the lawn. Nice relaxing afternoon and evening after the long drive.
For this SQL Saturday I wanted to take a little different tack than I usually do, based on the schedule and speakers. SQL Saturdays are known for their excellent technical content, and I usually fill my day with technical learning. To be sure, I attended several excellent sessions with some highly technical content, but my focus for this one was ‘professionalism’ for lack of a better word. I also wanted to make sure I was taking care of the ‘Connect’ part of ‘Connect, Share and Learn,’ touching base with SQL folks I have met over the last year, and meeting as many new folks as would be practical.
I don’t think I can write these next lines and convey the enthusiasm I actually felt, so you’ll have to use your imagination. These are in the order they happened. I met Thomas LaRock (Blog | Twitter)! I met Andy Leonard! (Blog | Twitter) I met Joe Webb (Blog | Twitter)! I met Douglas McDowell (Twitter)! I met Jeremiah Peschka (Blog | Twitter)! I met Glenn Berry (Blog | Twitter)! I met Kendra Little (Blog | Twitter)! I met Brian Kelly (Blog | Twitter)! I’m going to be accused of name dropping! Sorry, but being able to meet these stars of the SQL world just blew me away! Not to mention all the folks I met that are like me, ordinary people who are stars in their own lives, but not well known outside their own circles. And it was awesome to see people I only see at these events, but interact with on Twitter. I think one of the highlights of the day was when I was walking down the hall and Jessica Moss (Blog | Twitter) (whom I met in Charlotte) smiled and waved and said “Hi, David!” I about fell over when that happened! I was recognized by someone I greatly admire! I tell you what, the SQL Community Rocks!
Now to get into the sessions themselves. One of the best things about SQL Saturday is the number of sessions. One of the hardest things about SQL Saturday is the number of sessions! How do you choose? This time around I chose to focus on the sessions and speakers that covered Professional Development, and in time slots that didn’t offer the subject, get into technical sessions covering things I feel I need or am ready to learn.
First up was Andy Warren’s (Blog | Twitter) Building a Professional Development Plan. I never realized how important having such a plan would be. Andy went over covering how much time do you want to spend on your development, how much money you want to or can spend, and, most importantly because you are probably working on your development outside your normal working hours, how much of your personal life are you willing to give up. Throughout, he emphasized keeping records – what you’ve gotten accomplished, how much time and money was spent. This, I found, was a recurring theme. Write. Take notes. Keep records. These things come in handy. Planning your development, actually writing out your plan, makes your goals concrete and accessible, and keeping good records allows you to see if you are accomplishing what you set out to do.
I then went to see Douglas McDowell’s Realizing ROI for Business Intelligence Projects. While this was geared more toward a business ensuring ROI on a given project, there were a lot of good ideas for individual professional development, and realizing ROI on any project. I had been taking notes on Evernote all morning, and for this session, the only note I took was “Get this slide deck!” It’s available on the SQL Saturday site.
Following this I attended Fundamentals of SQL Server Internals, presented by Jeremiah Peschka. This was one of those sessions I felt I was ready to learn. Internals in SQL Server is a huge subject, a fact confirmed by Jeremiah. His style was great, though, as he explained concepts to a packed room in an easy manner, pointing out how the various parts of a query get handled by different parts of the Query Engine. His slides are also available on the SQL Saturday site.
After lunch was another Professional Development type session, this one put on by Jeremiah Peschka. Here he reiterated Andy’s points of making plans and keeping records. He illustrated by pointing out that when review time comes around, if you have been keeping records all along, you can easily show what value you have added to your employer’s business. This emphasis on record keeping must mean something… J
I’m going to admit that I went to Kendra Little’s Take the Awesomeness Home: the Data Collector more to meet Kendra than to learn about the Data Collector. She was someone I followed on Twitter and wanted to meet. Her talk on the Data Collector was very informative, though, and it pointed out to me a couple of things I didn’t know about it. One was that it is not intended to gather data on the server it is on, and secondly, it’s true it can’t be removed easily. I only have one server in my environment, so there is no sense in me having it, but I can only disable it. Ah, well, at least I learned how to use it if I get into an environment where I can.
The last session of the day was Thomas LaRock’s What Are You Waiting For query tuning session. As far as technical sessions went, this one was the one from which I could take the most immediate usefulness back to my server. Using DMAIC (Define, Measure, Analyze, Improve and Control) from the Six Sigma world, you go through your queries, defining what’s right and wrong, measure the results, analyze for improvements, make controlled changes, then hit the cycle again. His presenting style was both funny and informative, and his generosity was tempered with tough love J
Overall I had a great time, and learned a great deal. SQL Saturdays are hands down the best training deals out there for SQL Server, with so much given by volunteers, and so much received by attendees. My next SQL Saturday I have submitted a session for, in an attempt to give some back.
Thanks so much, speakers, volunteers, sponsors and PASS!
July 20, 2010
Last week, I passed the Microsoft 70-450 exam, PRO: Designing, Optimizing and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008. I have the SQL Community to thank for it, and I’ll outline why as we go. Mainly, I was asked to write a blog post about how I prepared for the exam, so I will cover what I found helpful, and what I found not so much.
The preparation starts, as with all Microsoft exams, on Microsoft’s Learning site. For this exam, one would start at http://www.microsoft.com/learning/en/us/exam.aspx?ID=70-450, which gives the overview, skills measured, preparation, and community links. The overview gives just that, the 50,000 foot look at the exam and the intended audience, along with what Microsoft thinks should be the set of features and concepts the candidate should have experience in. Good enough. I then skipped right to the Preparation Materials tab, thinking I would, like I did for the 70-432 MCTS exam, order up a Training Kit. How often do you see these two words together? #Microsoft – #Fail!
What I found was the following, copied verbatim from the page:
Preparation Tools and Resources
To help you prepare for this exam, Microsoft Learning recommends that you have hands-on experience with the product and that you use the following training resources. These training resources do not necessarily cover all of the topics listed in the “Skills Measured” tab.
Classroom Training There is no classroom training currently available.
Microsoft E-Learning There is no Microsoft E-Learning training currently available.
Microsoft Press Books There are no Microsoft Press books currently available.
OK, plan B. Apparently Microsoft isn’t interested in training their users for a product that has been on the market for two years, and has actually been superseded by an R2 product! Looks like I needed to find my own way to study for this thing. Luckily, I am a member of the online SQL Server community, and can learn from the best! So, following Buck Woody’s school of thought, I turned to the Skills Measured tab.
If you haven’t seen it, this exam covers a lot. I mean a whole lot! The list of skills measured is actually kind of intimidating, at first. There are seven broad categories of features covered, with innumerable bullet points within each category, which in turn all have multiple objectives. I had taken the MCTS in February, and that was a little difficult, now I had scheduled the MCITP for July 6th, figuring that six months should be ample time to prepare for it.(I know, I know, I missed the Second Shot deadline, but there were reasons.) I was starting to wonder if that were long enough.
The first thing I did was look to see what was out there besides Microsoft’s non-offerings. I had heard good things about Train Signal, and they were having a sale, buy their Advanced SQL Server 2008 Database Administration Training DVD, and get their Windows Server 2008 R2 Training DVD free. Their DVDs do not come cheap, but one thing I had going for me was that getting certified was part of my annual goal at work, so they footed the bill.
So I sat through the 20-some hours of videos, thinking through 60-70 percent of it, ‘I know this stuff! And I am being talked to as if I were in the studio audience at a taping of Barney the Dinosaur!’ Really, it wasn’t that bad, but for something that bills itself as ‘Advanced Training,’ they sure spend a lot of time on the basics! Overall, I would say it’s a decent reference to have around, but I’m not sure it’s worth what they’re charging.
Next, I figured that I should have followed my first instinct, and turned to the community. I asked around a bit, and heard that several people had gone several ways, but the common denominators were Books Online, and Practice.
Always, it’s the basics that works. If you want to lose weight, eat less and exercise. If you want a good retirement, live frugally and save. How often is it that we lose sight of these things?
So for the last few weeks before the exam, that’s what I did. I went down the list of objectives in the Skills Measured list, looked up each term I didn’t already know in BoL, and tried each thing out that I could on a test server (my home computer, really – I don’t have a real ‘test server.’ At the time, (and even this moment) I did not have the proper setups to actually practice or perform any of the multiserver HA stuff, like clustering, or replication. I only have one little server box at work, with a whopping 2GB ram, and my home computer to play with. So really, I just read through everything I could about those technologies, trying to at least make sure I had a proper understanding of the concepts.
Throughout, as I said in the beginning, I had the community of which I am so proud to be a part. Every day folks were writing multiple blog posts, SQL University had a week on Certifications, just all kinds of resources to learn from. Actually, some of the best tidbits came from people’s #sqlhelp questions on Twitter, as they tried to figure out why one or the other of the things listed in the Skills Measured wasn’t working, the answers given were actually found as some of the multiple choices on the exam!
So that’s how I prepared. I found out what the requirements would be, I determined how to learn those requirements, I got a nifty couple of training videos, then I followed the community’s recommendation – I got back to basics. I followed the RTFM method, and practiced all I could. Now, because of all that, you are now reading the words of a Certified Professional.
July 2, 2010
Welcome Back Once Again!
For today, our last class together at SQL University, (#SQLU on Twitter, the brain child of Jorge Segarra, who blogs at The SQL Update Statement, and can also be found championing the Social side of DBAness as @SQLChicken on Twitter), let’s talk about keys, both primary and foreign, discuss indexing, and finally, how to get information from data. Busy day today, so let’s get into it!
Thanks for my involvement (one more time!)
I would like to once more express my heartfelt thanks to Chancellor Segarra for inviting me to lecture in this esteemed forum. I’d also like to thank Coach Larock for re-emphasizing what I am telling you about here – his lecture was better than all three of mine combined! Read it, if you haven’t already!
Where are my Keys?
Keys are important!
When we talk about keys in a database, we are talking about a couple of different things. Primary keys are those fields in a table which help define and delineate the table, like an order or customer number. These are not repeated in a table because they are used to identify each row of data, and so can be used to index a table, as we will discuss shortly.
Foreign keys are important, too!
Foreign keys are sometimes a tricky concept to understand. Tables are related in what are known as Parent-Child relationships, and it is the keys that define and control that relationship. In my previous lecture, we laid out a data model for Customers and Orders. When we normalized the table, we ended up with one table that had Orders, separated out from the Customers table, as illustrated below.
In this table, the Order# field is the Primary key for this table, and the Cust# field is the foreign key. It relates this table back to the Customers table, so that orders can be matched to customers.
Another thing Primary and Foreign keys allow besides relationships is a concept known as constraints. When the database is set up properly, the rows of data in both tables are constrained to each other. This means that the relationship between them is such that, for example, you can not delete a customer without first deleting that customer’s orders. Imagine if that constraint was not in place, and someone deleted a customer. The orders would still be out there, with a foreign key pointing to nothing.
Indexes, or how do I find things?
Primary Keys Define Indexes
The Primary keys that you define when constructing a table are generally also used to define the index for the table. Database indexing is a large subject unto itself, in this class we will just go over them briefly. Essentially, an index is how data gets found within a table, much like an index is used in a book to find information.
In a book, you would turn to the index to look up a certain word, and the index, in turn, would point you to the page on which that word was found. A database index works much the same way. When a table is queried, and the query contains a Where clause to limit data, the database engine takes the predicate of that Where clause and looks, or seeks, through the index looking for that predicate. Once found, the rest of the record is also located, and the query returns the data to the user.
Without an index, the database engine would need to search through every row of the table looking for the predicate before it could return the row in question, so you can see how much greater performance will be with an index.
Indexes can be defined as something other than the Primary key, but in general, that’s what the index is based on.
Getting Information from Data
Raw Data is hard to Read
When a database is designed properly, with the correct amount of normalization for the data, in its raw form it is not very intelligible to the human mind. There are keys and tables, bits of data here in this table, other bits in that table, and simply reading through a table will not get you very far if you want to know what’s going on in your business. Properly queried, though, there is real information in your tables for the business to keep tabs on their business, find trends in data, and help the business stay afloat and even grow.
All of this is made possible with the right set of queries to pull from the data. In my previous lecture, I showed how not to build a table, and would like to illustrate here why it shouldn’t be built like that. To remind, here is the obnoxious offender.
|John||Smith||123 Any St||Big City||MA||02134||001012||345||Disc|
|Jane||Doe||456 Main St||Small City||MA||01234||001453||651||Bowl|
|William||Smith||2 Mulberry Ln||Small City||MA||01234||001232||345||Disc|
I will ask again, for the sake of time, to imagine that there are more orders added to the right side of the table, 3 columns for each, and, further, imagine that a sales manager would like to know how many of what product is being sold to a certain city. The query would have to include lines that take each order column and collate them together into their own column, bringing the city only once for each row, and only then being able to get counts for each city’s orders. This would involve messy and low performing pivots or CASE statements to get the orders separated out, so bad I am not even going to try to right it out here, please just imagine it. And then imagine that this little table is thirty or forty columns wide with millions of records. The manager may as well start the query on Monday and hope for a result by the end of the week!
In a properly designed database, though, the query would simply get the cities from one table, join to orders in another, possibly tying them together with customers as a binder, and his count of product orders per city could be returned in seconds. And if such data could be returned quickly, other ways of querying can be used also taking mere seconds each to get concepts like trends over time, trends over time and area, customer payment histories, etc.
I realize this has been a very simplified discussion, but (A) I’m up against having to take care of other things, and so must finish this lecture, and (B) this is a 100 level class, so I am just trying to get the concepts relayed to you, with the hope that you will endeavor to take up the ideas and go further with them yourselves in your homework and research on your own.
Wrap it up
Thanks so much for joining me!
This week has been an incredible experience for me being able to share with you all this information. I thank you so much for joining me! It is my sincere hope that you’ve gotten something to take away from it, if even only a single concept. If you have any questions, be sure to post them in the Comments, below, and I will answer them ASAP.
Check this out for homework
For homework, I am going to once again suggest someone *much* smarter than I to reference on the subject of Indexing. I am giving here a 100 level class; the lady to whom I refer can take you as far as you want to go. I speak here of the Incredible Kalen Delaney, who literally wrote the book, Microsoft® SQL Server® 2008 Internals, the definitive guide to the internals and architecture of the Microsoft SQL Server 2008 relational database engine. She can be found at http://www.sqlserverinternals.com/, and her blog is at http://sqlblog.com/blogs/kalen_delaney/default.aspx. I would simply suggest reading everything there, you won’t be sorry!
Thank you all for joining me this week, I hope I was able to teach you something you can use.