Bob Duffy's Blobby Blog

If you are using the new “Gemini Team Site” in SharePoint 2010 (I guess this must be renamed in the Beta-2). You may notice that we can’t add Data Connection Libraries to work with traditional BI sources.

This is just because it is not a site feature enabled by default. You can just go into “Site Settings”=>”Manage Site Features” and re-select the “Office SharePoint Server Enterprise Site Features” option to activate it

Three common issues integrating office and MOSS when using Server 2008 as a client:

1) Office cannot browse SharePoint

2) Credential Prompts

3) Where has my Network Places Gone

1. Office Cannot Browse SharePoint

I’ve seen a few times that office 2007 or 2010 when installed on Windows 2008 or 2008R2 will not by default allow you to browse SharePoint sites. You get an error like “Path does not exist. Check Path and try again”

or “You can’t open this location using this program. Please try a different location”

This is very frustrating when setting up a “BI build” for 2007 stack or the new 2008r2 stack as I have been doing recently.

the solution is documented here:

http://www.21apps.com/sharepoint/windows-server-2008-developing-sharepoint-cant-connect-from-office-clients/

To summarize we just need to add the “desktop experience feature” to the Server. Note that this only applies when you are actually using the server as a workstation such as a demo or dev build. Production servers do not need this.

2) Credential Prompts

Another common problem when using a server  as a desktop is IIS will continually prompt for credentials. The reason is security: Windows 2008 server and above have removed the “Intranet Zone” that logs you on automatically.  See this URL for more details

http://support.microsoft.com/default.aspx/kb/258063

In many cases removing the new “Enhanced IE security” and “Protected Mode” resolves this – again this is just for demo and dev environments…

3) Where is my network Places

Network places was useful as a way to store short cuts to document libraries.

See this site for this workaround

http://duitwithsbs.wordpress.com/2007/11/14/wheres-my-network-places-in-vista/

Note that you don’t “need” Network places in office 2010 as it has Favourites and Libraries which can both be customized.

Ok, everyone is hopefully aware that Gemini is now renamed to “PowerPivot”. IMO, this technology is set to make a very big impact on the Business Intelligence Industry. Here in Ireland, nice players like Qlikview have been doing very well over traditional BI tools.

The Beta 1 (or CTP2) was invite only ;-), so you can only download the bits on the Microsoft connect site if you were part of the programme.

The Beta 2 (CTP2) is “nearly out”. Everyone in Microsoft has been using it for ages, but the required office Beta-2 is not publically available.

The required SQL 2008 R2 Nov CTP2 is publicly available though, you can download it here: http://technet.microsoft.com/en-us/evalcenter/ee315247.aspx

Here are some upcoming blogs on the subject, that I am reading:

- Dave Wickert (Analysis Services guru) has a blog here http://powerpivotgeek.com with some good gotcha’s on deployment

- Denny Lee (SQLCAT dude) has a blog here in conjunction with Dave http://powerpivottwins.com/

- The Excel Services Team has started blogging on PowerPivot here http://blogs.msdn.com/excel/archive/2009/11/11/excel-services-in-sharepoint-2010-dashboard-improvements.aspx

- Rob Collie (MS PowerPivot dude) has started a blog here http://powerpivotpro.com/

- The official blog for the product is here http://blogs.msdn.com/powerpivot/

- Vidas Matelis (Fellow SQL MVP) has started a full site for independent news, webcasts, forums and the like on PowerPivot http://www.powerpivot-info.com/

- Donald Farmer has some videos up on you tube demonstrating Key features http://www.youtube.com/geminute

I’m helping deliver a series of advanced seminars for Microsoft TechNet Ireland. The SQL Academy aims to bring level 300-400 advanced content to practising SQL Server professionals in Ireland with the opportunity to bring any questions to a supposed expert in the field ;-)

The first session in on Advanced Configuration. We picked this topic in response to feedback from customers and attendees at previous events. Hopefully this topic will be immediately useful to people responsible for deploying, configuring and tuning SQL Server.

Registration is here and best of all it is FREE, 100% sponsored by TechNet Ireland. Its on Wed 21st October 2009.

Topics covered are below:

Configuration I (memory and CPU)

o SQL Server’s Memory Architecture

o How much memory do I need

o 32bit memory configuration

o Memory settings

o Monitoring memory usage

o CPU Architecture

o CPU settings

Configuration II (Database Settings)

o Common database settings

o Recommended practices

o Controlling database configuration

Configuration III (Storage)

o SQL IO Architecture and Requirements

o SAN/Disk Configuration

o Windows Configuration

o Data and Log File Configuration

o TempDB configuration

o key metrics for monitoring storage

o testing storage with sqlio.exe

For anyone looking to find out how many SQL Servers they have deployed, and gain insight into resources used or performance - here is a selection of tools I have been using a lot on projects.  Ok System Centre isn't free, but sometimes I am lucky enough to find it is already installed before we are doing a SQL discovery or assessment exercise.

Paul Randal and Kimberly Tripp are presenting at the SQL Ireland users group on the 23rd September. They were here last year and it was fully booked, with many people not able to get in, so I'd be very surprised if it wasn't a packed house again. You can book this FREE event at the link below:
http://sql.mtug.ie/Events/EventInfo.aspx?ID=6e54f06f-9ad6-4f4f-9acc-e4f89d653362

The topic is "Essential database Maintenance". Paul and Kimberly's sessions are a must for anyone with a career or interest that involved SQL Server.

They are in Ireland that week teaching at the SQL Immersion: Dublin 2009 event which is paid event and pretty full. There's a few places left and you can book here http://www.eventznet.com/sqlimmersion

Microsoft's new self service business intelligence platform (project Gemini), is set to revolutionize how Business Intelligence projects are implemented, with many projects choosing a much more agile approach with less formal IT involvement in the modelling.

If you are looking to get ahead of the game on this exciting new technology, There is a one day course at SQLBits in the Wales on the 18th November, with Donald Farmer from the Analysis Services Team.

http://www.sqlbits.com/information/TrainingDay.aspx

Another hot topic in Business Intelligence is the MDX language. You can't move beyond the basics with analysis services without rolling up your sleeves and using MDX. The one day "Introduction to MDX" course by Chris Web, author of the book "MDX solutions" looks an excellent investment in time for anyone getting into business intelligence.

courses are 199 STG if you register before the 30th September.

I just got the email to say I have passed the SQL 2008 MCM exam. http://www.microsoft.com/learning/en/us/certification/master.aspx.

The six hour hands on lab is pretty gruelling. Luckily there is no need to fly to the lab in the states for it, with web cams and remote proctoring it can all be done over a recorded live meeting session and remote desktop.

I'm always installing SQL Server or talking to customers about installations and the topic comes up of which version am I on, or what version should I be on ?

A fellow MVP, Bill Graziano has lovingly compiled and maintains a list of version numbers and how they map to SP and CU level. Its a must for the favourites!http://www.sqlteam.com/article/sql-server-versions

Another common question is should I apply the CU and the latest SP ?

Well applying a SP is a no brainer as Microsoft only supports an old service pack for 12 months since the release of the last one, so your patch management strategy needs to apply the latest service pack in a reasonably timely fashion.

Cumulative Updates are more complex. Obviously if it is a critical security patch this needs to be applied ASAP. Apart from that you only NEED to apply a CU if you are affected by something it addresses. However, especially with SQl 2005 SP2 and CU 11 it is nearly impossible to read through the release notes and identify if you will be affected, so for most installs I recommend the latest CU level.

For SQL 2008 the Cu can be slipstreamed for a smooth install as per here http://blogs.msdn.com/petersad/archive/2009/04/24/sql-server-2008-slipstream-frequently-asked-questions.aspx

There is four weeks until the SQL Immersion event here in Dublin and I'm getting really excited.

For anyone like me who career is based on SQL technology this is one not to miss. There has never before been an opportunity like this to learn from Paul Randal and Kimberly Tripp open to the public in Ireland. I have been to Paul and Kimberly's sessions a few times now and I still want to see some more. Both to bone up on the finer points of forward pointers and I get an added bonus - as a speaker myself I can steal some tips on mentoring/training from the experts.

On the Personal level, Paul and Kim are great guests with a fantastic sense of humour. They must need it to both work in the same area and stay sane;-)

I'm just finishing preparation for a complimentary half day session on Consolidation and Virtualisation which could be really useful for anyone looking to reduce costs,  rationalize their SQL Server estate or review consolidation tools and planning techniques.

There are still a few places left at this event and Registration is still open here
www.eventznet.com/sqlimmersion

The largest Irish conference/ expo is starting this wed. the list of speakers and events is definitely worth checking out http://epicenter.ie/  It event combines four previous events in one: Java, Open Source, Microsoft and Web Technologies.

I'll be presenting two sessions and be at the speaker dinner on Thursday evening. I'll also be hanging out at the event on Wed and Fri. if anyone wants to chat about SQL Server or Business Intelligence over a pint, give us an email.

Microsoft BI Overview:
This will be a nearly 100% demo based walkthrough of the Microsoft BI stack covering end user reporting, end user analytics, web based analytics, portals, scorecards and dashboards. I'm also checking with the powers that be if I can show the next generation business intelligence client - project Gemini which comes with SQL 2008 R2 and office 2010. This is set to make a huge storm in the Irish Business Intelligence industry when it hits.

SQL Server 2008 Virtualisation Considerations and Best Practices
I'm chatting to lots of customers at the moment about virtualisation. Both from the planning aspect as part of a consolidation strategy and from the troubleshooting aspects when things aren't working out so well.

This session explores the recommended practices for virtualizing SQL Server using the Microsoft Hyper-V product. We will discuss the following:
- Where does virtualization fit into a consolidation strategy
- Capacity Planning for consolidation
- Tools to get started
- Resource sharing and control
- Storage configuration
- Performance considerations
- Monitoring Strategies
- Zero touch provisioning

Hope to see you there!

Its very common when I arrive on site to do a performance review or some tuning that a good first place to look for is windows task manager and anti-virus.  By selecting View-Select Columns we can add interesting counters such as Threads, Handles, I/O Reads (since start-up) and I/O Writes since start-up (a sample is below)

The screenshot above shows that while anti-virus has only read 10% of the bytes of SQL Server, it has performed three times as much read I/O as the read activity isn't as efficient as SQL Server - its a lot more ad-hoc with smaller avg read sizes.

If Anti Virus exceptions are not configured, its possible for the Anti-Virus to consume many times the I/O's of SQL Server which can really hamper performance. IMO Anti-Virus is just a utility program running on a server, and such it should be quietly running in the background, hopefully not consuming more than 5% of I/Os. On many servers it is consuming 50%+ of total I/O activity.

Now no one can say "don't use anti-virus" without being hung drawn and quartered by the security teams, but the Microsoft KB on anti-virus for SQL (KB309422) is carefully worded

"Microsoft strongly recommends that you individually assess the security risk for each computer that is running SQL Server in your environment and that you select the tools that are appropriate for the security risk level of each computer that is running SQL Server. Additionally, Microsoft recommends that before you roll out any virus protection project, test the whole system under a full load to measure any changes to stability and performance.

Virus protection software requires some system resources to execute. You must perform testing before and after you install your antivirus software to determine if there is performance impact to the computer that is running SQL Server."

We can re-word this to say "don't apply your common anti-virus policy to SQL Server and be careful if you do". From a practical perspective this means considering a few factors:
a) The level of virus risk to the server
b) How much performance can this SQL Server workload spare
c) How intrusive is the Anti Virus

For most servers, you can run anti-virus fine, but we just need to be careful as to the exceptions. The official exceptions in the KB are:
- SQL Server data files (*.mdf, *.ldf, *.ndf)
- backup files (*.trn, *.bak usually)
- full text catalog files. This is the FTData folder in SQL Server
- The directory that holds Analysis Services data

I would add these files/folders as well to be sure:
- Trace files (*.trc)
- The Log Folder. Something like "MSSQL\Log

Also, note that the KB has not yet been updated for SQL 2008 file stream. I'm not sure what the MS official policy will be, but I guess you "may" have to exclude the file stream folder and rely on a virus sweep instead of these externally stored BLOB's, or suffer a performance hit.

In addition, there are further recommendation for windows (KB822158) should be applied as well.

One last comment is pick your anti-virus product well. Most people buy anti-virus on the cost and features, I would add resource consumption as an additional thing to consider. AFAIK, no one has yet published a league table comparing products on this basis.

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.