Setting up a machine for my 70-431 studies

I just added a new netbook to my arsenal of computers, so I am going to use my big power laptop as my new training machine. It is a couple year old HP DV9543CL that has a nice 17″ display, 4GB Ram, and what I like most about this is it has two hard drives in it, which I put in (2) 7200 rpm drives 160GB & 500GB. I installed a demo version of Windows 2008 R2 ENT, SQL Server 2005 ENT x64, and Office 2010 x64 as well.

I am very happy with the performance, the computer is very snappy.  I also like running Windows 2008 R2 on my laptop.

Studying for 70-431

At the start of the year I set a goal to take a Microsoft Certification test.  After some hesitation I set my sights on the 70-431 “SQL Server 2005 Implementation and Maintenance”. I chose 70-431 because this is the version that I have been primarily working with at work and have more experience with. I have been studying off and on since the beginning of the year, but serious in the last month. After pushing out the test at the start of June, I finally decided to get my butt in gear and set a test date so I have no more excuses.  I decided Friday July 30^th and will make it official next week when I call and schedule.  This will give me just over a month to put the final touches on my study.

My primary books of study are:
Wiley –70-431: SQL Server 2005 Implementation and Maintenance (Microsoft Official Academic Course)
MSPRESS – MCTS Self-Paced Training Kit (Exam 70-431)
MSPRESS – Inside Microsoft SQL Server 2005: The Storage Engine
MSPRESS – Inside Microsoft SQL Server 2005: Query Tuning and Optimization

My game plan for the next month:
[6/27 – 7/11]
I just finished reading Wiley MOAC and now plan to go back over it the next 2 week and go through each of the labs and go back over several of the areas that I need more work on, like XML, Service Broker, Endpoints, and Replication.

[7/12 – 7/23]
Go through MSPRESS Self-Paced Training Kit, just another angle of information and study.

[7/24 – 7/29]
Go through as many practice tests as possible and research questions; go back over areas of question and concern.

[7/30]
Take test.

Next on my plate, I am thinking SQL Server 2008 Imp/Mtce 70-432 or the Optimization and Tuning cert for 2005 70-450. 

One thing that this process has done for me is gotten me more exposure to other areas in SQL Server that I have not worked with.

Who ran what?

Request came in from above to see how many times the Finance Reports have been run and who ran them.

I took a look a look at the ReportServer database and started looking at the tables until I found the two tables that gave me what I needed.  Found that I could get this data out of the Executionlog table and join it to the Catalog table to get what I needed.

The following Script give me a list of of each report, when it was run, and who ran it.

SELECT C.NAME as ReportName, E.UserName, E.TimeStart, E.Format, E.Status
FROM EXECUTIONLOG E JOIN CATALOG C ON (E.ReportID = C.ItemID)
WHERE PATH LIKE '%Finance%'
ORDER BY [ByteCount] DESC

This is good, but the follow up question will be, can you get me a list of how many times a report has been run?  Why yes, I can do that too.

SELECT COUNT(Name) AS [Report Count], Name AS [Report Name]
FROM Catalog C JOIN ExecutionLog E ON C.ItemID = E.ReportID
WHERE PATH LIKE '%Finance%'
GROUP BY Name
ORDER BY COUNT(Name) DESC, Name

I have blacked out the names to protect the innocent and my job.

SQL Saturday Event #43 – June 12th Redmond, WA

I can’t believe that SQL Saturday 43 (#sqlsat43) Redmond, WA is only a couple of days away.  I am looking forward to learning new things, meeting new people, and just absorbing SQL.   

As of today these are the sessions that I am planning on attending.

I chose Scott’s session because I have just started using SSIS and want to learn more about it.

9:00 a.m.
Scott Stauffer @SQLSocialite “SSIS for the faint of heart”
Scott’s gentle introduction to SQL Server Integration Services (or SSSIS) will primarily be demonstrating how to you can use the tool in the real world to get work done. READ: very little PowerPoint time. SSIS is a high performance ETL solutions introduced in SQL Server 2005 to replace SQL Server’s DTS functionality. ETL? It’s okay, Scott will briefly cover some of these concepts too. Relax, sit back and learn how to simplify your “Extract, Transform, and Load” processes.

I haven’t made up my mind on which one that I want to attend.  They both look interesting.  It will be a game time decision.

10:30 a.m.
Dean Richards “Tuna Helper for DBAs and Developers”
Many DBAs and developers are faced with tuning poorly performing SQL statements. There is no way to learn everything you need to know about SQL tuning in an hour, but you can learn a process to employ when badly written SQLs are ruining database performance. However, many tuning projects fail because the process being used is inefficient. This presentation will walk through a process I use with great success and it will include topics such as: SQL diagramming, wait type data, column selectivity, and others that will help you succeed on future tuning projects.

or

10:30 a.m.
Donabel Santos @sqlbelle “SQL 2008 Reporting from the Ground up”
In this session we will explore the rich reporting features introduced in SQL Server 2008 and 2008 R2 Reporting Services, including Report Builder 3.0. We will walk through how to create basic reports, add parameters, enhance layout, and integrate new visualization components including sparklines, data bars, gauges and maps.

11:45 p.m.
LUNCH “I will definitely attend”

The 12:30pm session was  also a tough choice.  I am interested in Donabel’s PowerShell session, but I can’t miss out on seeing Kalen Delaney, the Queen of SQL Server.

12:30 p.m.
Kalen Delaney “The Compression Session”
SQL Server 2008 allows you to compress your data in several different ways. Your tables can be compressed using row compression or page compression, and you can also choose to compress your backups. In this session we’ll look at what kinds of data give you the most space savings when compressed, and we’ll also discuss the algorithms SQL Server uses to compress your data. Knowing how SQL Server performs compression will allow us to evaluate when the space savings will be worth the cost, and when you might want to avoid compression.

The 2 pm was also tough choice, because there are really 3 sessions that I would love to attend.  Robert Davis’s @SQLSoldier “Profiler” and Todd McDermid’s @Todd_McDermid “Data warehouse” session.  I chose Clifford Dibble’s “UCP” just because I wanted to learn more about this feature.

2:00 p.m.
Clifford Dibble “Utility Control Point in SQL 2008 R2”
Learn how the Utility Control Point (UCP) in SQL 2008 R2 can help you with your capacity management and consolidation projects. In this talk, you will learn the key concepts around the UCP and see a demo. This is also your chance to give us feedback about how you want the UCP to evolve in the 11.0 release of SQL Server.

There is no doubt about this session.  Seeing Buck Woody is worth the price of admission, oh it’s free.  Well he is still worth every penny.  I have had the pleasure to see and hear Buck present multiple times at multiple SQL Saturday events, PASS 2009, and other misc. webcasts and podcasts.  He is a great presenter, very entertaining, and knowledgeable.  Even though the topic mentions the “O” word I know that I will take away something from his session, if not, it will at least be very entertaining.

3:30 p.m.
Buck Woody @BuckWoody “SQL Server for the Oracle DBA”
Come hear Buck Woody, Microsoft’s “Real World DBA” give a marketing-free introduction to SQL Server for the Oracle professional. No experience in SQL Server is necessary – we’ll cover the basics of SQL Server Architecture using Oracle concepts as a guide. If you’re an Oracle professional and you want to add SQL Server to your “knowledge arsenal”, come hear this overview. You’ll also get a list of resources that will enable you to research further.

The event is getting bigger and better each time.  Last I heard there were over 400 signed up with more than 40 on the waiting list.  Awesome! 

Hope to see you there.