Bob Duffy's Blobby Blog

PowerPivot RTM has arrived

MSDNArchive — Thu, 22 Apr 2010 22:47:38 GMT

I’m just back from the office 2010 roadshow. Phew four cities and six presentations on PowerPivot later back at home with a nice cup of tea and my own bed for the night ;-)

A few people asked me how to get hold of the RC0 that I was using on the demo.

Well, no need – the full RTM is available to download. you will need:

1. Office 2010, available to MSDN or TechNet subscribers here

http://msdn.microsoft.com/en-us/subscriptions/downloads/default.aspx

2. Powerpivot RTM available here

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=e081c894-e4ab-42df-8c87-4b99c1f3c49b

You can also download SharePoint 2010 too from MSDN/TechNet

http://msdn.microsoft.com/en-us/subscriptions/downloads/default.aspx

The one gotcha is you won’t be able to download the SQL 2008 R2 RTM bits for few weeks.

I have no idea if the SharePoint 2010 RTM will work with the Beta of SQL 2008 r2, but I am guessing its best not to try. Probably best to wait till you can marry up the server side bits and play with the client side bits for the next few weeks…

SCOM for SQL Folk on 27th May

MSDNArchive — Thu, 22 Apr 2010 21:53:03 GMT

I’m talking at the SQL Users group on “SCOM for SQL folk”. this session is based on my experiences with SCOM and working to get the product to do what we need it to do for managing SQL performance.

Often as a DBA or SQL consultant, SCOM is “forced upon” you by the operations team or corporate strategy. Lets face it there is no way a SQL guy would pick SCOM as his number one favourite tool for managing SQL Server, when there are many rich, specialist tools just focusing on helping manage SQL Server like Quest, Idera, Red gate, etc (sorry if I missed any!).

However, like it or hate it, SCOM is here to stay from a SQL perspective. Personally I like SCOM as it allows us take an enterprise view of operations and management. EG manage all out infrastructure and application through one common platform. Not only can SCOM manage SQL but also the other service that underpin SQL such as DNS, AD, network, switches, hardware, windows and distributed applications.

I love the work the manageability team is doing in SQL (err well not as much love for DAC but that’s another story), but I wish they would show the SCOM team some more love and hand over more IP on how to manage SQL Server, rather than taking such an insular approach into building manageability into individual products.

I’m no SCOM expert by any shake of the stick, but if you are a SQL guy struggling with SCOM, or someone using SCOM and struggling to manage SQL Performance this could be a session worth coming to.

the link to register is here (its free)

http://mtug.ie/Default.aspx?TabId=38&ctl=Details&Mid=369&ItemID=52&ContainerSrc=%5bG%5dContainers%2f_default%2fNo+Container

Time to get serious with encryption

MSDNArchive — Thu, 22 Apr 2010 21:39:29 GMT

just read an article indicating the some states in the US are making data encryption on disk and on the wire compulsory with some pretty hefty fines

(see http://www.sqlmag.com/article/sql-server/A-New-Law-that-Will-Change-the-Way-You-Build-Database-Applications.aspx)

This pretty much means that soon you’ll need at least transparent data encryption and protocol encryption on every single SQL Server. Happy days for Microsoft in terms of the government practically forcing people to buy Enterprise Edition !!!

While I’ve been a big fan of SSL encryption on web servers to browsers, I was never fond of encrypting the pipe between the database and the app server. usually (on most systems I’ve been involved with locally here) the two boxes are like right next to each other, often with a private LAN. If someone had compromised the physical network at that point, network protocol encryption would be the least of your worries. But with increased security awareness I guess time are changing and we are going to eventually have encryption from disk to client regardless of level of threat.

lets hope no one passes a law saying that we have to have data encrypted in memory as well as on disk and on the wire!! An encrypted buffer pool would make querying fun…

SQL Immersion: Dublin 2010

MSDNArchive — Thu, 22 Apr 2010 19:35:36 GMT

The only local opportunity for advanced SQL training with sqlskills (Paul Randal and Kimberly Tripp) is happening in June/July this. As far as I know this will be the only SQL Immersion in Europe this year.

If you are a SQL professional, attending a sqlskills course is really something you can’t miss to progress your technical skills. The SQL Immersion course is four days of exposure to the worlds best trainers and mentors on the SQL platform.

I was lucky enough to attend over a week of Paul and Kimberly Tripp during the Certified Master/Architect Program and can testify that I learned more in these few weeks than years of self paced learning.

Here’s a piccie of the SQL Immersion last year – as you can see a bunch of smart looking SQL folk ;-)

You can register for the SQL Immersion: Dublin 2010 event at this link here http://www.prodata.ie/Events/SQLImmersionDublin2010/

PowerPivot at the Office 2010 Roadshow for IT Pros

MSDNArchive — Fri, 02 Apr 2010 14:29:31 GMT

If you haven’t seen PowerPivot yet, there is a great opportunity at the Office + SharePoint 2010 Roadshow for IT Pros coming to Dublin, Galway, Cork and Belfast.

We have been working on making an Irish Centric Demo of PowerPivot along with the great new features in Reporting Services such as map based reports. Hopefully in just abbot 40 minutes we will build and entire BI solution, publish it to SharePoint and show off some cool new reporting features.

If you are working with BI or an Excel Power user / consumer, this is a not to miss event. Best of all its free!

Full details are in the link below

http://blogs.technet.com/ieitpro/archive/2010/03/30/microsoft-office-sharepoint-2010-roadshow-for-it-pros.aspx

General Guidance for SQL Server on Virtualisation

MSDNArchive — Wed, 31 Mar 2010 09:53:33 GMT

I have been on about four engagements in a row this year where we are looking at SQL performance on VMWare or Hyper-V.

Here is a list of common things that you can do with virtualisation that may adversely affect SQL performance. Most of them also apply to physical environments, for example if you are consolidating SQL onto multiple instances.

Using a large Shared disk group for all virtual workloads
Mounting the VHD to VMWare disk on a server file system (instead of pass though disks)
Using a large disk pool when only one controller can own the disk group (some SAN’s are limited in this way and some are not)
Overcommiting CPU
Overcommiting Memory
Not using 64k block size and allocation unit size
Not using Volume alignment (on guest and host)
Using dynamic disks (much better in Hyper-V R2, but still not generally recommended)
Not ensuring Logs are on dedicated spindles
Not using multiple HBA channels on larger workloads
Sharing a switch between data, network and CSV
Not using CPU affinity (some virtualisation platforms support affinity)
Not using an “enlightened” operating system (Hyper-V)
Running multiple VM’s on a single host slightly decreases throughout, but this is kinda the point of virtualisation so hard to avoid.
Running lots of SQL Servers on one host and having too few HBA cards or a low queue depth
Running 32 bit SQL Server guest on workloads that need lot of memory.
Not pre-sizing TempDB
not planning for database growth events

The top one item (use of a shared disk group) is a very common configuration for disks, especially when using the clustered shared disk volumes. But we know they will adversely affect performance, so what to do ? ban these configurations?

A more practical approach may be to just accept that your latency for SQL Server will be slightly worse than it could be and to invest in making the disk infrastructure perform better, so we work out at the same performance on virtual environment as we would on the physical.

For example if you know that a shared disk volume is going to add 1ms+, can we add more spindles to the disk pool to take away 1ms + from the latency.

If we do need to use a shared disk pool, can we at least separate OLTP and reporting/data warehouse workloads into two disk pools. One might be suitable for RAID 5/6 with spikes in sequential IOPS and one might lean towards RAID 10 with mainly random IOPS and a larger reliance on writes.

If we are overcommiting CPU, as we have lots of SQL Servers only using 5% of CPU, can we dedicate CPU’s for the one mission critical ERP database.

If we are sharing Logs, can we at least dedicate a disk group to shared Logs and allocate a dedicated log disk to the 1 or two critical workloads.

Cindy Gross has a great blog article on troubleshooting SQL Server on VMWare and associated white papers which is well worth a read.

http://blogs.msdn.com/cindygross/archive/2009/10/23/considerations-for-installing-sql-server-on-vmware.aspx

Hidden Network Devices

MSDNArchive — Fri, 19 Mar 2010 11:33:31 GMT

One annoying thing about working with Hyper-V is you can end up with lots of “hidden” network devices. For example:

a) If you installed using a RIS server and legacy NIC
b) If you moved a VHD between servers, the NIC gets a new MAC address and device
c) If you use clustering and haven’t got the network setup identically on all nodes

d) If you use clustering and accidently use the old Hyper-V Manager instead of cluster manager

To remove the hidden NIC’s the steps are:
1) Add system environment variable DEVMGR_SHOW_NONPRESENT_DEVICES with a value of 1

2) Open up device manager and select “show hidden” devices, then delete the offending NIC’s.

You can see how these invisible NIC’s clutter up tools like “bginfo” that show the system summary data by adding extra NIC’s with settings of “none” (see the DNS Server entries below)

Taking PowerPivot and Your Domain on the Demo Road

MSDNArchive — Thu, 18 Mar 2010 23:23:06 GMT

One of the current Technical Limitations of PowerPivot for SharePoint is the fact that the PowerPivot Server must be connected to a domain controller for full functionality. My holy grail has been to get PowerPivot Server working quickly in my humble laptop running Windows Hyper-V R2 – Not so easy when you need to either install it on a domain controller or also have a domain controller running on the Hyper-V host, and you only have 4gb ram.

I just plain feel dirty installing SharePoint or SQL on a domain controller, and those “all in one” demo builds generally perform like dogs.

Just to be greedy, I also want the demo environment to be able to fully authenticate with my nice workstation in the office as I hate working off laptops and want to seamlessly swap between using desktop with the Hyper-V image running on our SAN and running by itself on my laptop.

can this be done ? well to the rescue, two new features of Windows 2008 server: server core and Read Only Domain Controllers.

Step 1 – Windows 2008 R2 Server Core

The first thing I tried is Windows 2008 R2 Server “core”. One of the key changes in 2008 R2 is the introduction of the “sconfig” command line that enables you to do 90% of the setup required such as:

- computer name
- networking
- Enabling remote admin and Power shell, so you can go back to gui mode.

There are lots of guides on how to automate the “dcpromo” command to make the server a domain controller. http://windowsmvp.spaces.live.com/Blog/cns!80195647FE07388F!629.entry?sa=760842989

Now this gets me a virtual server which only needs just over 512mb of RAM and takes up about 3gb of disk. BUT it has all the domain accounts on it for the entire domain or if I use a child domain for the lab servers the entire child domain – not so good in terms of size and also in terms of security.

If you are not clustering the domain controller you only need standard edition, which uses a bit less disk space.

One gotcha I found is for 2008 R2, it would not work for me unless the domain was in “2008R2” functional mode. This was a bummer as our main domain controllers are not R2 and I couldn't mess with them, but luckily I could raise the domain functional level in the lab child domain to “2008R2” and leave the parent domain as “2008”.

Step 2 – Read Only Domain Controller

Windows 2008+ introduces a new role of “Read Only Domain Controller”. This allows me to only replicate the 2-3 service accounts needed to the offline domain controller and also does not store the passwords locally, so great for security. its also a lot lighter on resources than a full domain controller.

Now I have a fully working Domain Controller letting me take virtual images from the lab to offline on my laptop while still keeping domain authentication and only needing about 400mb of ram and 3gb of disk space ;-)))

Haven’t got the setup fully working yet with Powerpivot, but it is showing much promise…

The Last SQL Academy in this series goes out in style

MSDNArchive — Thu, 18 Mar 2010 10:15:23 GMT

The last topic in the series is entitled “SQL 2008 R2 – Next Generation Business Intelligence”. We hope to cover three main improvements in the SQL 2008 R2 release:

a) PowerPivot for Excel
b) PowerPivot for SharePoint
c) Reporting Enhancements

As usual we hope for it to have a lot of demos and the more questions and the better ;-)))

I was just over Seattle last month and grabbed a session with Dave Wickert who is one of the leading authorities on all things PowerPivot and Analysis Services. He really helped go a bit deeper behind the scenes, especially on how authentication works (what no Kerberos!!) works, what drives the need for SharePoint Enterprise Edition and Active Directory from a Technical Perspective. He runs the popular site www.powerpivotgeek.com if you want to read more.

This topic is fiercely popular, sp hopefully a full house…

How good is Data Compression in SQL 2008

MSDNArchive — Tue, 23 Feb 2010 23:32:13 GMT

Data compression in SQL 2008 is best for data warehouses, where they tend to be very IO constrained and can take a wee hit on CPU and latency.

So how much space will you save with data compression? Well I just took a sample table from our SCOM data warehouse and applied compression. Results are below. The total size went from 1.7 Gb to about 740 MB. Not bad for one table and its supporting NC indexes.

Type	Rows	Total (Kb)	Data Space	Index Space
Uncompressed	10,006,556	1,714,632	700,352	1,010,544
PAGE compression	10,006,556	739,744	289,360	450,112
ROW compression	10,006,556	1,027,672	387,832	639,576

In total the data warehouse went from 17GB to 10 GB when all the tables and indexes were compressed with page compression.

How long does it take to compress? Well on a two core laptop and a modest disk drive the 17GB database took 30 minutes.

Which Compression is better page or row ? It depends – while page compression seems to always give better compression, if your query only wants one row in a page the whole page needs to be uncompressed, so you’ll take a cpu and latency hit. If you are doing a large table scan then page compression should work great.

How can you compress and entire database at once ? AFAIK you have to compress each table and index separately. Here is a quick script to generate code to compress an entire database in one go. Just don’t try this on your 3TB data warehouse in one go…

select 'ALTER ' + case when si.type =1 then 'INDEX [' + si.name + '] ON ' else 'TABLE ' END +  ' [' + s.name + '].[' + o.name + '] REBUILD PARTITION=ALL WITH (DATA_COMPRESSION=PAGE); ' 
from sys.indexes  si
inner join sys.objects o on o.object_id =si.object_id
inner join sys.schemas s on s.schema_id=o.schema_id 
where si.type>0 and o.type='U'
order by s.name, o.name, si.index_id

IW 2010 Demo Hyper-V Image Released

MSDNArchive — Sat, 06 Feb 2010 15:44:38 GMT

For those of you like me running Hyper-V, there is a VPC with the some of the 2010 “IW” stack loaded:

- Domain Controller
- MOSS 2010
- office 2010
- SQL 2008
- fast Search
- Project server
- Communicator

I’m going to see how easy it is to put PowerPivot on the VPC image…

I’ve just started to download it from here
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=0c51819b-3d40-435c-a103-a5481fe0a0d2#filelist

SQL Server and Kerberos

MSDNArchive — Sat, 06 Feb 2010 10:09:03 GMT

One side effect of following the best practices and running SQL Server under a Domain User account is that “Kerberos” authentication may not work due to the fact that the SPN cannot be registered.

To step back a bit – what’s an SPN? This is a mapping maintained in Active Directory between a service account and a service on a specific port number. This allows the client to be assured that the service it is connected to has not been spoofed and that the account that is listening on the service is the correct one.

Why do I need Kerberos ? well there are some very minor performance advantages as it is not as chatty to the Domain Controllers, but the main advantage is that it allows for reporting services to pass identity down to a data source without the “double hop” issue (see http://weblogs.asp.net/owscott/archive/2008/08/22/iis-windows-authentication-and-the-double-hop-issue.aspx)

The SPN can ONLY be registered in Active Directory by an account with “Validated write to service principle name” permission. Usually this is a domain Administrator.

How do I know if Kerberos is working properly ?

you can query the current connection or all connection and check if NTLM or KERBEROS have been used for authentication

select auth_scheme from sys.dm_exec_connections where session_id=@@spid

you may also see an error message in the SQL Error log
The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies

How do I fix this

There are three methods:

a) quickly add the service account to the Domain Admin’s and restart SQL Server then remove from group – might raise a few eye brows but hey it works

b) manually register the SPN using an appropriate account like Domain Admin – maybe in a larger firm someone might need to do this for you

setspn -A MSSQLSvc/mysqlservername:1433 DOMAIN\SERVICEACCOUNT

c) Grant the computer account the rights to set its own SPN. This is done via the “Validated write to service princ” property in Active Directory users and Computers Tool.

What about Analysis Services?

One key difference between the DBEngine and Analysis Services seems to be that Analysis Services will not automatically try and add its own SPN if it is missing, so you will need to manually configure the SPN as per KB article below
http://support.microsoft.com/kb/917409

Shorts VBS Script to Determine if SQL Server is installed and running

MSDNArchive — Mon, 11 Jan 2010 18:03:16 GMT

I am playing with System Centre Operations Manager and Config Manager. They all use VBS to determine if SQL Server is present, but often fall foul of a stopped SQL Server service or a different major version (Sql 2008 v Sql 2005)and are very wordy – some 75 lines of code in the configuration Items for DCM!

For example the downloadable DCM packs for SQL Server only largely work on SQL 2005. Really we need them to work in any edition or at least 200x.

So here is a one line VBS script I have started using to detect if SQL Server Service is installed and running (any version)

If GetObject("winmgmts:\\.\root\cimv2").ExecQuery("Select Name from Win32_Service where state='Running' and PathName like '%sqlservr.exe%'", "WQL", &H10).Count > 1 Then WScript.Echo “True”

Works great in System Centre Configuration Manager as a Detection Method for SQL Server. A sample screenshot in config manager is below.

January Seminar – BI with SQL 2008 R2

MSDNArchive — Thu, 07 Jan 2010 16:27:13 GMT

Rafal Lukewiecki who is a top rated Tech Ed speaker is hosing a whole day BI event at the Burlington Hotel, Dublin on January the 19th.

If you want to see some good demos of PowerPivot and other R2 features this is definitely worth a visit.

Link is below.

http://blogs.technet.com/ieitpro/archive/2009/12/17/rafal-lukewiecki-business-intelligence-seminar-in-january.aspx

Installing PowerPivot for SharePoint

MSDNArchive — Tue, 08 Dec 2009 09:18:37 GMT

Installing and Configuring PowerPivot has been painful for me – Partly because I managed to dive in and not read the readme.txt and release notes ;-)

The three things I did wrong and have learned are:

- “Standalone” SharePoint option is not supported as this uses network service accounts. (see below why)

- Domain accounts are a MUST. No local option (yet)

- You need to login with the service account in “Interactive” MODE.

More tips are on http://powerpivotgeek.com/server-installation/reported-problems/#001 thanks to Dave Wickert.

SQL Academy – Performance Tuning & Optimisation

MSDNArchive — Sat, 28 Nov 2009 14:22:47 GMT

Registration is still open for SQL Academy on Tuesday 1st December in the usual place (Sandyford, Dublin).

For those of you who are new to SQL Academy – the aim is to bring level 300-400 content to SQL professionals in Ireland via half day seminars. The target audience is folk who already have experience with SQL Server but are looking for a deeper look into key aspects or to bone up on SQL 2008 and above.

The link to registration is:

http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032428012&Culture=en-IE

The topic this session is a very popular one: performance Tuning and Optimisation. We will be running through common diagnostics and monitoring tools, presenting the most common methodology for bottleneck analysis (waits and queues), and look at precision query tuning tools available such as RML utilities.

The Gemini/PowerPivot Gallery View

MSDNArchive — Sat, 28 Nov 2009 12:02:07 GMT

On my first week trying to setup PowerPivot I was plagues with an inability to see the sexy new silverlight previews of the mashup’s in SharePoint. The error message was “An Error Occurred while capturing snapshots for this document”

To help debug, this – powerPivot has a log file located here:

[SystemDrive]:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS\gemini.log

This had a nasty error below:

11/20/2009 10:54:46 PM : Currency Filed.xlsx - SUCCESS: GetSnapshot self-terminated (after 298.4346258 seconds) .
11/20/2009 10:54:46 PM : Currency Filed.xlsx - INFO: 'http://sharepoint:80' was found in a different trust zone (URLZONE_INTERNET). Attempting to remove from URLZONE_INTERNET. .
11/20/2009 10:54:46 PM : Currency Filed.xlsx - INFO: 'http://sharepoint:80' was removed from (URLZONE_INTERNET) .
11/20/2009 10:54:46 PM : Currency Filed.xlsx - INFO: 'http://sharepoint:80' cannot be added to the list of trusted intranet sites: -2147024890 .
11/20/2009 10:54:46 PM : ERROR: Currency Filed.xlsx - INFO: System.TimeoutException: The operation has timed out. .

Thanks to Russell in the MSDN forums (here) who supplied a workaround. The issue seems to be that the service account for PowerPivot in SharePoint needs to have the site in a security zone and if that account has not been logged onto the server it won’t work.

I logged on with the service account, uploaded a new PowerPivot mashup and everything worked hunky dory ;-)

One key thing I found with the new Gallery is you MUST use the silverlight PowerPivot Gallery library to fully utilise PowerPivot. Although you can upload PowerPivot documents to other types of libraries in SharePoint, the ability to create a report of excel report off the original PowerPivot cube is only accessible via the PowerPivot Gallery (option is top right on the screen shot above)

New Install Option for SQL 2008 R2

MSDNArchive — Wed, 18 Nov 2009 21:16:15 GMT

Using the new “Default Install” option in SQL Server 2008 R2 I managed to get a full instance installed (Tools, Db Engine, SSAS, SSIS) in 18 minutes flat on a Hyper_V guest.

Maybe this will become some sort of benchmark test for new servers…

Data Connection Libraries missing in SharePoint 2010

MSDNArchive — Sun, 15 Nov 2009 17:58:03 GMT

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

Integrating Office and SharePoint in Windows 2008

MSDNArchive — Sun, 15 Nov 2009 15:37:10 GMT

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.

Good Power Pivot / Gemini Resources

MSDNArchive — Sun, 15 Nov 2009 11:53:09 GMT

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

SQL Academy (Dublin, Ireland)– Advanced SQL Configuration

MSDNArchive — Thu, 15 Oct 2009 12:56:12 GMT

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

Tools for SQL Discovery

MSDNArchive — Thu, 17 Sep 2009 01:22:55 GMT

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.

Tool	Pros
System Centre Operations Manager 2007R2 Microsoft's flagship operational management product. Uses an agent based topology to discover SQL and also capture performance and capacity wmi metrics	- Runs 24x7 and maintains a data warehouse. - If it is already installed is tool of choice, with most of the data you need in a query able repository Cons - Huge task to deploy it so not suitable for a quick discovery and inventory exercise. - Does not capture all required metrics "out of the box" with the SQL Management pack - for example performance metrics like Page Life Expectancy and database space usage. - Limited out of box reports for SQL inventory/analysis - Complex data schema for querying
Microsoft Assessment Planning Toolkit 4.0 http://technet.microsoft.com/en-us/solutionaccelerators/dd537566.aspx Free agentless tool to sweep a network and discover SQL instances	Pros - Easy to install and run - Agentless - not intrusive - can capture performance counters - some basic automated reports Cons - performance counters limited - Annoyingly insists on installing sql express locally
SQL Server Health and History Tool (SQLH2) http://www.codeplex.com/sqlh2/ The tool allows you to collect information from instances of SQL Server, store this information, and run reports against the data in order to determine how SQL Server is being used	Pros - open source - very extensible wrt performance counters - Dumps data into a nice data warehouse for reporting and analytics - comes with some sample reports to get you started Cons - can be fiddly to configure - does not do discovery only inventory
SQL Ping http://www.sqlsecurity.com/Tools/ FreeTools/tabid/65/Default.aspx SQLPing 3.0 performs both active and passive scans of your network in order to identify all of the SQL Server/MSDE installations in your enterprise	Pros - Easy to run and very lightweight - uses multiple discovery techniques. - Better discover than MAP too - Also does brute some password probing capability Cons - Only does discovery, so needs to be combined with another tool to get performance data across the enterprise
Perfmon Good old performance can be configured to capture performance counters from multiple servers and the result scan be imported into a database for analysis	Pros - zero install - widely used - very flexible - Agentless Cons - not a discovery tool - analysis of data is quite manual - only captures perfmon counters, no hardware information.

Paul Randal and Kimberly Tripp at SQL Ireland Users Group in Sept

MSDNArchive — Tue, 01 Sep 2009 20:36:15 GMT

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

Want to learn more about Self Service Business Intelligence (Project Gemini)

MSDNArchive — Sat, 29 Aug 2009 16:38:40 GMT

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.