Bob Duffy's Blobby Blog

Just got the email announcing that I've been awarded an MVP for SQL Server for 2009/2010.

The MVP award is for contribution to the technical community. I'm really chuffed to get this sort of recognition - the list of SQL Server MVP's includes some big names that I really respect like Paul Randal, Kimberly Tripp, Adam Machanic, Brad McGehee, Kalen Delaney, Brian Knight, Bob Beauchemin, Linchi Shea, Tony Rogerson, Greg Low, Kevin Kline and Darren Green to name but a few. Over the years I have used their blogs, White papers, and mountain of IP available to help with projects and learning about SQL Server.

A big thank you to our local MVP in SQL Server and a good friend, Niall Flanagan who runs the local SQL users Group (http://sql.mtug.ie/). Also thanks to Enda Flynn in TechNet Ireland for his support and endorsement ;-)

Regards,
Bob

Kimberly Tripp and Paul Randal are coming to Dublin in September for a week of what promises to the be the deepest SQL Server training ever held in Ireland. If there is any SQL event to attend in Ireland this year I'm pretty sure this is the one.

When:                  September 21^st - 25^th
Where:                 Microsoft Auditorium in Sandyford EPDC
Registration Site:   http://www.eventznet.com/SQLImmersion 

For those of you who did not get to see their last one day seminar and User Group event last year in Dublin - it was sold out !! Paul and Kim's sessions are truly Unique in their ability to captivate an audience with a fun and interactive style.

This five day event promises to cover some core areas of SQL Server of interest to both DBA's and DB developers:
Day 1 - SQL Internals
Day 2 - Designing for Performance
Day 3 - Indexing for Performance
Day 4 - Essential Database Maintenance I
Day 5 - Essential Database Maintenance II

In addition, I'm going to be speaking at a complimentary session covering SQL Consolidation on the Friday. I hope to answer key questions such as:
- Benefits of consolidation.
- Consolidation project planning.
- Consolidation strategies.
- Storage consolidation.
- Using virtualisation for consolidation.
- Implementing "SQL as a Service" for customers.
- Implementing SLA's on consolidated environments.

An honour from one perspective (and not so good to be measured by the same audience) that I'll be presenting after Paul and Kim.

Hope to see everyone there,
Bob

For anyone attending the SQL 2008 Academy on Friday (link here) we are going to be showing some details on project Gemini technology included in SQL 2008 R2, which is probably the first look at this exciting new self service BI platform in Ireland.

This is going to be a HUGE impact in the BI marker worldwide - The use of in memory self service BI is already the fastest growing type of BI engine and now we will be able to choose between the classic OLAP model and the in memory architecture.

This sort of technology is going to be fantastic for developing proto types and quick dashboards. What might have taken a lot of skill and weeks with traditional OLAP and presentation tier development will now take hours and a couple of trained monkeys.

One concern I have when designing solutions is deciding on the line between when to traditional OLAP cubes and when to use a Gemini style solution and when to use a hybrid. The empowerment of end users is great, but there must become a point when a more structured OLAP cube becomes the more sensible solution. I'm sure I'll get closer to an answer after we get more familiar with the technology.

I guess I predict that when Gemini comes out we will see solutions that should have been developed in Gemini done in OLAP cubes and visa versa. still progress is good, I look forward to riding the wave.

I must get about 50 emails a week offering a degree for 50 bucks mail order, but here's an offer which is not bad.

The Microsoft Certified Master Program is offering some $3,500 off the $18,500 ticket price. This has to be the best training on the planet and a real way to differentiate your technical skills. The link is here:

 http://www.microsoft.com/learning/en/us/certification/master.aspx#tab3

If anyone in Ireland is thinking of going for the SQL MCM or MCA and wants to chat about what its like, give me a shout - we are in Sandyford Dublin.

Its a great opportunity to spend three weeks nerding it up and really deepening your exposure to not only SQL Server but a who's who of the platform.

The rumours are true! We are helping to organise Paul Randal and Kimberly Tripp to return to Ireland to run a WEEK intensive SQL Immersion event in Dublin for the 21st to the 25th of September.

The last time Paul and Kimberly were over the session was massively over subscribed and it was the first time I have ever seen a perfect 100% score on a satisfaction survey.

This is a must for anyone heavily in SQL Server and wanting to get access to the best SQL trainers in the world! I was on the SQL Ranger/MCA course in Redmond, which Paul and Kim do a whole week of content. Even for someone with 16+ years in the database industry there is a huge amount to pick from them and move to the next level.

if you have been to or heard of a Paul and Kimberly session, then you know that this will be a not to miss SQL Server event!

Registration links will be up in a day or so!!

I'm presenting a complimentary session for Microsoft SQL Academy entitled "Analysis Services 101" next Friday (26th June) here in Sandyford, Dublin.

This session is aimed at people who are familiar with the relational engine, but just getting into Analysis Services and Data Warehousing. Hopefully this will be suitable for DBAs and  technical Implementers.

There are a "few" spaces left for the session and you can Register here

These topics have proved very useful in helping people get over "cube fear" ;-)

I just spent ages troubleshooting an annoying thing with SCCM. Every time I tried an OSD PXE installation the PXE would load and then just before the task started it would just reboot.

I followed all the troubleshooting tips for Windows PE environment and reviews the "smsts.log" log to just see a message "NO MP Certificates" and a few similar floundering people on DL's

As a last straw before calling a proper SCCM expert, I checked all the Network Access Accounts in client agents and saw that the "Computer Client Agent" was not using the same account as the one used to install SCCM (although it was a domain admin!).

Switched this to be the same as the main install account and all worked fine ;-))

I've recently been installing a lot of MS Server products into a lab. One thing I noticed is immediately after the install the server is nearly useless, especially if its a Virtual Server with only one core.

The reason for me was after the install the dot.net framework was busy in the background pre-compiling all the binaries. You can see this be the process "mscorsvw.exe" hogging 100% of CPU time.

According to this blog article http://blogs.msdn.com/davidnotario/archive/2005/04/27/412838.aspx

the pre-compiling is just Temporary and will finish eventually, but, it can be pretty annoying if you are trying to do the configuration immediately after install, or demoing to someone and want a smooth ride.

So, you can manually force the pre-compile with this nice command to flush the queued compiles, go away and get a cup of tea/lunch and hey presto nice performance.

ngen.exe executequeueditems

I think I'll include this as part of all build plans of dot.net server based products when installed on Virtual Platforms.

If anyone is in Cork on Thursday 4th June, I'll be presenting a seminar on Reporting services 2008 Architecture and Report Builder 2.0 for the MTUG.

Well be covering:

- Key Engine Changes in 2008

- Rendering Improvements

- A Walk through of how to sue Report Builder 2.0 for end user reporting.

For me, Report Builder 2.0 is really exciting as it really helps empower end user reporting, and allow ISV's and IT to focus more on the model and the data than on the specific of reporting.

So often we see a cycle where the IT department makes hundreds of reports, none of which actually do exactly what's wanted. The basic problem is that while the IT department gives the business what they "ask, the business and end users can be very visual in nature: they can't fully articulate exactly what they need until the see something that's close, which is just after the IT department has made them sign off on the project.

One solution is to try and treat reporting as more a proto-typing exercise and another is to use tools like Report Builder 2.0 and other tools from the BI stack to let end users make up their own reports.

One definite "anti-pattern" for reporting is where you need to develop 100+ reports, and users don't see the goods till the end of the project and a big formal "UAT" period. A much better approach is to break the reports down into subject area and implement small sets of reports at a time. It might take longer to implement, but the risk is much less and the final result is better.

The link for the event is here
http://www.cork.mtug.ie/Events/EventInfo.aspx?ID=74aac54f-ede2-4f20-b30f-2be7723388d2

I’m a SQL/Apps dude, not an Infrastructure guy.

My weakness is I love technology so much I dabble in things that I should definitely leave to more expert specialists: windows, active directory, Group Policy, Exchange, ISA Server, networking, patch management, server hardening, Hyper-V, playing with toys, SC*, UC, MOSS, config management.

Recently I’m playing with System Centre Configuration Manager. I just like the idea of more automated deployments, inventory control, patching, config management, etc. Even on our humble setup here.

While doing the usual install of an MS server product the AD schema needs to be extended. Of course I get the cryptic “Error Code 8206”.

As a tip to troubleshoot extending AD, just running the “dcdiag".exe” tool before any schema upgrade has been invaluable. This helps tell the health of the AD and can find what was blocking the Schema extension.

In my case I had a secondary domain controller which was being replicated to, but had been turned off. Fixed it up and the schema extension worked perfectly ;-)

It would be really cool if products that extended the AD Schema put in the error message for AD schema extension “GO and run dcdiag.exe NOW” – this would cut down on a lot of hair pulling and mad searching online for solutions.

Hopefully next time I’ll remember to run dcdiag.exe before starting the install….

Ok, I’m sure many people already know this , but I just found out the hard way.

When you perform a restore or certain reconfigure options, the entire plan cache is flushed – not only just the database. This might wreck havoc with shared environments or multi purpose servers.

The KB article is here
http://support.microsoft.com/kb/917828

One very interesting change between SQL 2005 and SQL 2008 is that DBCC FREEPROCCACHE now allows you to just remove a single plan from the cache. This could be extended to say remove all the plans for a single database. Here’s some sample code to do just that

CREATE PROC usp_freeproccache_db(@db_name sysname)
AS
BEGIN
    declare @i int
    declare @handle varbinary(64)       
    declare  proc_cursor cursor for
    SELECT plan_handle from sys.dm_exec_cached_plans
    CROSS APPLY sys.dm_exec_sql_text(plan_handle)  AS st
    WHERE dbid =db_id(@db_name)
    set @i=0
    OPEN proc_cursor
    FETCH NEXT FROM proc_cursor into @handle
    WHILE @@FETCH_STATUS =0
    BEGIN
        DBCC FREEPROCCACHE (@handle) WITH NO_INFOMSGS
        FETCH NEXT FROM proc_cursor into @handle
        set @i=@i+1
    END
    CLOSE proc_cursor
    DEALLOCATE proc_cursor
    print convert (varchar(10),@i) + ' Plans removed from cache for ' + @db_name
END

I've chatted to a few customers about the possibnility of using a SAN based snapshot to initialise transactional replication.

This can be fantastic when you are using a replica to provide "near real time" reporting. Up until now, documentation has been a bit light on the ground on this apporoach, and people have been cautious to try it.  The results can really save a lot of time, such as getting a 17 TB replicated solution fully restored in a couple of hours!

My fellow MCM, Benjamin Wright-Jones in conjunction with the SQLCAT team has written a technical article based on his experience gettign this to work http://sqlcat.com/technicalnotes/archive/2009/05/04/initializing-a-transactional-replication-subscriber-from-an-array-based-snapshot.aspx

This gives step by step instructons on the procedure. Great stuff!

I think that in a short space of time anyone doing replication with a VLDB and SAN will consider this approach. Partly for technical reasons and party political. I mean you've just paid a fortune for that SAN - put it to work  ;-)

Jimmy May has compiled a great white paper on Disk Partition Alignment Best Practices for SQL Server along with Denny Lee

http://msdn.microsoft.com/en-us/library/dd758814.aspx

This is a must read for anyone configuring a disk subsystem for SQL Server.

Jimmy must be going for an award for good performance blogging articles along with two other good references

DMV All Stars
http://blogs.msdn.com/jimmymay/archive/2008/10/30/drum-roll-please-the-debut-of-the-sql-dmv-all-stars-dream-team.aspx

Wait Stats Introductory Reference
http://blogs.msdn.com/jimmymay/archive/2009/04/26/wait-stats-introductory-references.aspx

Thanks to everyone who came to the Dublin SQL users Group session on Tuesday. I hope that we addressed some aspects of “cube fear” that is common in even very experienced relational professionals. For me just being comfortable building that first cube is a great feeling.

We got some great feedback with a a 95% VSAT rating, so I’m quite happy to make up a 201 seminar later in the year. Hey if you can’t wait that long – we do private training courses and design sessions too ;-)

As the 101 session was quite popular I’ll see if we can re-deliver the content to some of the User Groups around the country.

The link to the SQL Ireland User Group Site is here
http://sql.mtug.ie/Events/EventInfo.aspx?ID=bbad6f63-2448-41f2-9352-bb573496af4a

And I have posted my slides here:

One thing I do notice about Ireland is we have lots of people who have great knowledge about writing Stored Procedures, data modelling and application design. However when it comes to dimensional modelling and cube building/design getting started can be trickier.  Cubes are seen as magical, complex black boxes to be avoided when good old tables will do, and grown men run scared.

I’m running a session for the SQL Users group to try and help here on the 5th May in Sandyford.

The first part will be an Introduction to Dimensional Modelling for people coming from a relational/OLTP perspective: Star schemas, keys and surrogates, partitioning, aggregation techniques, sizing, Slowly Changing Dimensions, dealing with Time, Booleans, Nulls and indexing.

The second part will introduce the concepts behind cube building with Visual Studio 2008: Measure groups, Dimension Types, Hierarchies, cube aggregations/indexing, basic MDX calculations, the UDM, Time Intelligence and Periodicity.

if you have any questions on this aspect of Business Intelligence, please do come along.

The link to the SQL Users Group Site for the event is here:

http://www.sql.mtug.ie/Events/EventInfo.aspx?ID=bbad6f63-2448-41f2-9352-bb573496af4a