Installing PowerPivot on an Existing Farm

At my current customer, we are putting together a sandbox MSBI environment with SharePoint as well.  We installed and configured SharePoint, SQL Server, Analysis Services, and integrated Reporting Services.  I was preparing to put together a PowerPivot demo in SharePoint and started to install it.  So here is where the fun began.

First, you need to use the SQL Server 2008 R2 install to

Next, you need to LOGIN using your Farm admin account and from what I could piece together, that user MUST have the following privileges.

  • SysAdmin on SQL Server
  • Local and Domain Administrator
  • SharePoint Farm Administrator

Of course, all of this violates any concept of minimum privileges for users.  While this level of access may not have been absolutely necessary, it is definitely where I ended up to get this working.  (The moral of this story is to install PowerPivot on new farms only?)

Here are some of the links I used to get me pointed in the right direction.  I would be interested in hearing if anyone else has had this issue and resolved it differently.

Now that it is running I can put the rest of my demo together.  This sure seemed more painful than it had to be.

February 2011 MN SQL Server User Group Meeting

 February PASSMN Meeting & Newsletter

Sponsored by Digineer


8300 Norman Center Drive, 9th Floor, Bloomington, MN  55437


February 15th, 2011


3:00 – 5:00

Please click here for meeting details and to RSVP 

High Availability & DR Options for SQL Server

Tim Plas, Virteva

A comparison of SQL HA & DR options, by a practitioner who has implemented & managed all the SQL HA and DR approaches (& various combinations thereof). Tim is an operational DBA, charged with keeping SQL servers up & running & optimized, for managed-services customers. We will compare trade-offs between the various SQL HA & DR options: for complexity, usability, hardware requirements, licensing, failover speed, initial costs, ongoing support costs, staff skill requirements, etc.

Also, as you may be aware, Microsoft has announced a set of very powerful “AlwaysOn” features for the upcoming version of SQL (“Denali”), features popularly referred to as “HADRON”  (“High Availability Disaster Recovery always ON”).  We’ll provide a brief overview of those features now, and will have a full presentation on that later in the year.

SQL Azure’s place in the data world (Part 3)

In the first two parts of this topic, I discussed how data is managed in SQL Azure and what the cost would be to an organization.  In this installment, I wanted to propose some data solutions that might be a fit for a SQL Azure database.

Here is some quick history that is shaping my thoughts on this.  While at Magenic, my focus is on business intelligence solutions on the Microsoft SQL Server product stack.  Prior to returning to Magenic, I worked at XATA Corporation as the data architect for a hosted, SaaS solution serving the trucking industry.  So, out of this background, I base my first suggestion.  In SaaS (Software as a Service), customers often use tools provided by the vendor for specific tasks which tasks generate data.  Most SaaS solutions offer some form of reporting or query based analysis for the data that is collected.  However, some users require a greater level of interaction with the data.  The issue is that the data is stored in the SaaS vendor’s multi-tenant data store which is usually not conducive to having ad hoc queries run against it.  This has led to one of the most common solutions to this problem – export the data to customer.  The problem is that the customer must now host the data on premise and is often responsible for merging new data as it comes from the vendor.  In this solution, SQL Azure could act as the “go-between” between the multi-tenant data store and the customer.  This will allow the vendor to provide a value-added BI service that the customer can leverage in a number of ways including reports, Excel, and even Access.  The vendor can keep the data synchronized and the customer can leverage the data as needed.

Beyond the SaaS BI ad hoc solution, SQL Azure can be used to support development of solutions that require a shared data store without putting the data on each desktop.  In keeping with the concept of the cloud being “anywhere”, SQL Azure can also be used to support distributed solutions that require a SQL data store to function. 

Obviously, there are still issues with using SQL Azure as a primary, production data store due to the lower SLAs from Microsoft.  However, it is not too early to investigate creative ways that will help your business leverage a relational database in the cloud.  I would encourage you to check out the free trial options from Microsoft to experiment with the platform. 

January PASSMN (Minnesota SQL Server User Group) Meeting

Update and Notes: Not only sponsoring this month’s meeting, but also offering a 10% discount to all PASSMN members to the class “SQL Server 2008 Architecture, Internals and Tuning“, is:  Benchmark Learning

Please click here for meeting details and to RSVP

Again, note the non-standard location, date and time.
Meeting Info:
Note: We will be meeting in the 8000 building this month Please click here for meeting details and to RSVP


8000 Norman Center Drive, 2nd Floor, Bloomington, MN  55437


January 25th, 2011


5:00 – 7:00

Error Logs and Deadlocks, Oh My!
Jason Strate, Digineer

Tired of turning to the SQL Server error log to find out what’s happened?  Would you rather just know about a deadlock when it’s occurred rather than later in the day when the manager as you why HRs bonus distribution application failed?  Find out how to get this information and more delivered to your inbox so that you can head off issues instead of drowning in them.  In this session we’ll look at Event Notifications and find out how to stop having to monitor SQL Server error logs today.

Seeking SQL Server Secrets
Kalen Delaney, SQLearning

Many experienced DBAs are aware that there are many undocumented commands and traceflags in SQL Server, and some DBAs may even keep track of all references to such options whenever they read about them in an article or blog posts, or hear about them at a conference. In this session, I’ll not only tell you about some of my favorite undocumented features, I’ll also tell you some of my tricks for discovering more undocumented secrets.

Training:  f you are looking for some training on SQL Server architecture, internals and tuning in January, then check out Kalen Delaney’s upcoming class at Benchmark Learning.  In her class she’ll talk about SQL Server architecture, the storage structures, index design and tuning, and much more.  There are still a few spots left for the class and it’s a great opportunity to get some instruction from one of the most knowledgeable trainers around.  Benchmark is now offering a 10% discount to this class for PASSMN members!

SQL Azure’s place in the data world (Part 2)

In Part 1, I discussed the synchronization of data and availability of data on Microsoft’s SQL Azure platform.  In Part 2, I would like to discuss the next set of questions related to using SQL Azure with your data solution.  Here are the questions I will address in this post:

  • How much data can be supported by SQL Azure?
  • Is it really cost effective?

How much data can be supported by SQL Azure?

The answer to this question is entirely dependent on how and, in some cases, who you ask.  SQL Azure currently only supports databases up to 50 GB in size.  Because this is subscription based, you will only pay for what you use or allocate.  I will delve into the subscription and their cost in the next section.  For our purposes, it is important to realize that any database up to 50 GB is supported by Microsoft.  This limitation leads to a couple of additional questions.  First, how is the size measured?  Keep in mind that you are managing by the database not the server, so the system databases are not included in this size.  Furthermore, Microsoft does not include the size of the log in this measurement.  So when you determining how much space you will need, focus on the size of the MDF file and be sure to check how much is actually in use.

The second question that comes to mind is what happens if you need more than 50 GB? While not supported directly, it is possible to distribute or to “shard” a larger database across multiple smaller databases.  Currently this is a custom solution, but Federation is coming.  With Federation, SQL Azure will natively support sharding a large database and be able to present the database as a single unit which will make development easier.  If you can’t wait for SQL Azure Federation, check out the SQL Server and SQL Azure Shard Library available on CodePlex (

So you can see that if you would like to support or scale out a larger database solution it is possible with SQL Azure.

Is it cost effective?

At the time of this writing SQL Azure subscriptions break out as follows (

Edition Max Size Cost per Month
Web Edition 1 GB $9.99
Web Edition 5 GB $49.99
Business Edition 10 GB $99.99
Business Edition 20 GB $199.98
Business Edition 30 GB $299.97
Business Edition 40 GB $399.96
Business Edition 50 GB $499.95

Microsoft also charges for data transfers.  Currently it is $0.10/GB for data in and $0.15/GB for data out.

For those of you who don’t have the privilege of working with software licensing, may not be able to appreciate the complexity of determining the cost effectiveness of the solution.  For comparison let’s review the MSRP cost for SQL Server Standard and Enterprise licenses.  (You check out other licensing options here:  SQL Server 2008 R2 Enterprise Edition costs $27,595/processor and SQL Server 2008 R2 Standard Edition costs $7,171/per processor.  These are the listed prices.  Customers can get a variety of discounts or other pricing options depending on their agreements with Microsoft including Software Assurance, Enterprise Agreements, and Volume Licensing.  In any case, there is is a significant license cost when ramping up a production SQL Server.

Besides licensing, you have to consider the cost of the hardware and personnel to maintain the servers.  By the time you have premium servers and a disk solution in place you could easily have spent around $100,000 for a simple production environment.  The next piece of the puzzle is related to additional manpower required to support the environment.

Now we have a bunch of numbers, but how can you compare solutions?  There are some significant differences between a SQL Azure solution and a SQL Server solution.  First, and most glaring to me, is the fact that SQL Azure is managed at the database level while SQL Server is managed at the server or instance level.  When you are trying to determine the cost-effectiveness for your application in either solution, you need to determine the number of databases required for your solution.

Next, you need to determine the cost of an on premise solution on a monthly basis so you can compare the solutions over an expected period of time.  Generally speaking, we expect hardware and software licenses to last around three to four years before an upgrade will be required for the solution.  In the case of SQL Azure, you have a known monthly cost with hardware and software licensing and related upgrades “included” in the cost.

Finally, when looking at SQL Azure, you might think that the fact it does not include Analysis Services or Integration Services in the license will make it cost-prohibitive in your solution.  However, since best practices dictate that separate servers be used for these services, they really do not factor into the cost-effectiveness of the solution.

So, what does this all mean?  It means you have some work to do to determine how cost-effective a SQL Azure solution will be for your application.  So let’s assume the following hardware/software solution to provide disaster recovery and high availability for your on premise database:

Component Cost factor Total
Dell PowerEdge with PowerVault disk – 2 Servers (2 proc/16GB) $65,000 with Rack $65,000
SQL Enterprise Licenses $25,000 / processor (Active/Passive Cluster) $50,000
Install and setup $100/hour $4,000
Total $119,000
$ Per month for 3 years $3305

As you can see, a simple on premise solution without considering the cost of maintenance and without considering the setup time will cost you around $3300 a month.  At this point, $500 a month plus data transfer costs should be less on SQL Azure for your database.  So, like me, you might think that dropping Enterprise to Standard licensing will make up the difference, but it still costs $2300 a month.  That means that you can get Enterprise capabilities with HA built in for less than standing up your own Standard environment.

Where this will break down is when your databases are larger than 50GB.  At that point you will have to scale both solutions and determine what is the more cost-effective solution for you.  You could reasonably use sharding to increase your space to 200 GB and still be cost-effective.  I admittedly did not think the cost savings would be that significant for SQL Azure.  Microsoft appears to be on the right track to provide reasonable pricing for SQL Azure.  I would be interested in hearing additional cost factors for on premise versus off premise database solutions.  (Please comment on this blog with additional factors you see that would impact the cost-effectiveness analysis of the SQL Azure solution.)

In the final blog entry on this topic, I will delve into solutions that make sense on SQL Azure and yes there are some cases for using SQL Azure sooner rather than later.

Thoughts on data, business analytics, and the SQL Server community

%d bloggers like this: