Tag: SharePoint

Upgrading SQL Server Reporting Services to 2012 In SharePoint Integrated Mode

Published March 18, 2012

That title could actually be longer….

SQL Server 2012 brings with it a number of key Business Intelligence features that apply directly to SharePoint environments. One of the major improvements is the way that Reporting Services installs. Prior to version 2012, when running in SharePoint integrated mode, it installed along side of SharePoint, and connected through an add-in. With 2012, it is now a full SharePoint Service application, with all of the associated benefits that brings.

It is simple enough to set this up on a new farm, but what about organizations that are already using SSRS in integrated mode? Since I was unable to find any prescriptive guidance on the upgrade process, I ran through it on a test farm, and below are my findings. This describes the process of upgrading from SSRS 2008 R2 to the RTM version of SSRS 2012.

SQL Server 2012 has some relatively strict operating system requirements. First and foremost, you need to be running at least Windows 7 or Windows Server 2008 R2 Service Pack 1, or Windows Vista or Windows Server 2008 Service Pack 2. If not, you’ll get the following message immediately.

In addition, depending on what you’re upgrading, it’s pretty fussy about your source environment as well. For example, if you’re upgrading management tools or BIDS, and you already have Visual Studio 2010 installed, it will need to be at least at the Service Pack 1 level. Your source SQL Server also has specific service pack requirements. The complete supported upgrade matrix can be found here. Unfortunately, if these requirements are not met, the installation will fail much further along in the process, and you’ll need to repeat several steps after correcting.

Once the SQL Server Installation Center launches, you’ll want to pick the Installation tab, and then the Upgrade option.

After a few steps, you’ll encounter one of the new screens pertaining to Reporting Services.

Previously, the installer was totally unaware of Integrated Mode Reporting Services. You would use SSRS configuration to set it up, but now the upgrade wizard, as well as the full product installer, is fully aware of Integrated Mode.

When performing the upgrade, the installer will go ahead and create the SharePoint service application for you. This is different than when you perform a fresh install – in that case you manually create the service application after installation. However, in order to do so, it needs to create an application pool for the service application, and you will be prompted for the credentials of that pool.

After several more standard screens, the upgrade rules will be run. This is where you will find out if you are missing a prerequisite, or it is not at the required patch level. However, if all is good, all of the rules should show as Passed, with the exception of “Direct Browsing to Report Server”, which will show a warning.

Previously, if you knew the correct URL, you could navigate directly to Reporting Services and the reports stored within SharePoint through a very rudimentary interface. This warning is simply alerting you to the fact that this is no longer an option with 2012.

The remainder of the installation is straightforward. When done (and if successful), you can navigate to the Service Applications section of Central Administration. There, you should see the new SSRS application.

I’m not a big fan of the name that the default upgrade uses for the application, but that’s simple enough to change. The important thing to note is that all of your subscriptions, snapshots, etc, will have migrated over. The upgrade upgrades the two Reporting Services databases (ReportServer, and ReportServerTempDB by default), and adds a new one, ReportServer_Alerting, which are all used by the service application.

In order to enable data alerts and subscriptions, a number of security modifications need to be made to the SQL Server. In addition, the SQL Server Agent must be running to use these features. Editing the Service Application shows a screen that has a link to Provision Subscriptions an alerts. Clicking through it reveals the following screen:

The View Status section simply gives you an indication of whether or not the Agent is running on the server, but clicking the “Download Script” button will give you a SQL script that will set up the required roles and permissions on your SQL Server. This script must be run on the SQL Server that holds the Reporting Services databases. In order to run it, simply open up SQL Server Management Studio, connect to the server, and click the New Query button. Once the query window opens, paste the query in, and run it (the Execute button).

Finally, enter the credentials for SharePoint to connect to your SQL Server Agent, and click OK. Once done, you’ll be in a position to use the new features available in Reporting Services, and all of your existing investments in reports should continue to operate as before.

For a major architectural change, this is actually a pretty smooth transition.

As I post this, it’s quite early in the life of 2012, so I would be quite interested to hear of any other experiences or gotchas. If there’s something that I should add to this post, please post a comment, I’d love to hear about it.

18 Comments

TechNet Radio Community Corner Interview

Published February 9, 2012

Last November, I was interviewed by Sr. Technical Evangelist John Weston on the MVP program, Office 365, Cloud Computing, Business Intelligence, and how these things all tie together. The entire interview was conducted online using Lync online, available in Office365. It’s now online, and can be seen below.

You can see other Technet Radio episodes by visiting the Edge site here

Why I Love Office 365

Published October 21, 2011

OK, so my company is a Microsoft partner, and we’re supposed to like everything that they throw our way right? That’s actually not true. I’ll certainly give most things that they do a fair shot. It’s also true that I’m willing to sacrifice a certain amount of capability for either ease of use, or for the way that Microsoft products work well together, but as I noted in a previous post, I only gave up my BlackBerry when Microsoft came out with a product that was worth using.

My company is small (currently 6 people) and widely distributed. Cloud solutions make perfect sense to us,and we have been using Exchange Online for over 2 years now. Our requirements for SharePoint went beyond what was possible in BPOS’ offering, but since migrating to Office 365 6 months ago, the new SharePoint online fits the bill, and more and more of our corporate assets live there now.

UnlimitedViz is currently primarily a SharePoint services company focused on Business Intelligence, and a significant portion of those services involve architecting SharePoint environments at a lower level, which involves sizing servers, making resource decisions, etc. I personally love designing solutions and watching them come to life. We are certainly more than capable to maintain our own SharePoint infrastructure, so why would we want to use an admittedly more limited version of the product that is maintained by someone else?

Pretty much because it’s maintained by someone else.

As mentioned above we’re small, and we need to be focused on what we do best, which is providing services to our customers, and building product. Maintaining internal systems, no matter how good we are at it, is a distraction, and a significant cost, both capital and operational. The per user cost of Office 365 is pretty simple to justify from just a cost standpoint, but there are many more benefits that are brought to the table.

No matter what the location of a team member, they can easily access what they need to. Lync brings that down to the voice and IM communication level. No need to mess around with access methods, VPNs, Firewalls, Reverse Proxy servers and the like. We can get to our content easily on site, at home via whatever device we happen to need. Granted, I could set that stuff up on-premise, but now I don’t have to! I also know that my data is safe, and the performance is going to be good. Two months ago, Exchange online suffered an outage for about two hours (the only hiccup I’ve experienced so far). My initial reaction was “what can I do to fix this”, but that was quickly superseded by “It’s not my problem to fix”, so I just sat back and got other work done.

As we bring more customers onto Office 365, supporting them just gets simpler. A simple client request can be acted upon immediately by launching a browser window, and connecting to their site, seamlessly. With most onsite installations, I need to start a virtual machine, connect through a VPN client, and then hope that the correct tools are installed on the VM, or the client site, depending on the access mechanism. I try to keep a VM image available for every type of VPN client used, which is a hopeless and necessary task due to the incompatibilities between clients. In my opinion, the world will be a better place when VPN clients are eliminated (or at least consolidated”).

Customers using Office 365 don’t need VPN clients, and it makes it that much easier (and cheaper for them) for us to support them.

There a a whole bunch of great features about Office 365 (Shared OneNote files accessed via Windows Phone, browser and client is a good one, not to mention Lync), but the reason that I really like it is that it’s solid, it works, and it lets my business focus on using its tools, not maintaining them.

SharePoint Online Fall 2011 Service Update Starts Oct 20

Published October 13, 2011

Today, Microsoft sent out a bulletin to SharePoint Online users notifying them that they would begin rolling out the Fall 2011 Service Update. This is the much vaunted update announced at the SharePoint Conference 2011 that includes BCS services. For those that don’t already subscribe, the list of new features can be found below (copied from the official email).

Feature	Description
*Business Connectivity Services (BCS) <WCF Connector> Enterprise plans only**	Enables connecting to external systems via web service based endpoints
External Sharing: Windows LiveID support	Allows Office 365 tenant administrators to invite external users to a site collection. They sign in with a Windows Live ID-based user name and password.
Windows Phone 7 “Mango” (official support and http:// connectivity)	Windows Phone 7.5, codenamed “Mango,” now enables both small business and enterprise Office 365 customers to access SharePoint Online lists and document libraries from their Windows Phone.
Recycle Bin: deleted site self-recovery	Self-service ability to recover sites from a site collection’s recycle bin
Browser support: Internet Explorer 9	Adds official support for the Internet Explorer 9 (IE9) browser
Browser support: Chrome	Adds official support for the Chrome browser

2 Comments

SharePoint Conference 2011– That’s A Wrap

Published October 8, 2011

After a whirlwind week of activity, I thought that it would be a good time to jot down a few observations from this year’s SharePoint conference, before life catches up and it all evaporates. One thing is certain is that the conference exceeded my expectations. Although it was light on new product information, it was more than made up by the wealth of best practices information from some absolutely stellar speakers. To be sure, the organizers had telegraphed this focus well in advance, and expectations were set correctly.

There conference was sold out, and while I never got an official number of attendees, the numbers that I heard ranged from 7,000 to 8,000. This is the attendance for a conference for a single product, which is really quite an achievement. In fact, at the keynote, Jared Spataro announced that SharePoint had sold 125 million licences to 65,000 customers making it the fastest growing product in Microsoft’s history. If SharePoint were a separate company, it would be in the top 50 software companies worldwide.

The keynote contained a single new announcement – that Business Connectivity Services will be available in Office365 before the end of the year. This is great news for my organization given that we specialize in both Office 365 and in Business Intelligence, but then, I may have been aware of this previously.

The demo highlight of the keynote was when Richard Riley demonstrated a 10 TB SharePoint farm with a single content database experience a SQL Server failure and fail over to a hot standby. This was a very impressive demonstration, but it was really just a demo of the new “Always On” features in the upcoming SQL Server “Denali”. I also feel the need to point out that just because you can have a 10 GB content database, it doesn’t mean that you should. As my friend Ziad Wakim whispered to me during the demo “try backing THAT up!”.

The sessions were the real meat of the show. In fact, I only skipped one time slot. In choosing my sessions, I was looking for 3 things – to fill in any gaps, or to confirm my field practices or to pick up demonstration tips, to see how Denali was being positioned within SharePoint, and to see any new development patterns and practices, particularly around Office365 and Azure. I didn’t get to everything that I wanted to, but I wanted to acknowledge the ones that I did.

On the development front, Andrew Connell laid out what I think will be the definitive design pattern for working with external data in Office365 and Azure. Fellow Critical Path Training founder Ted Pattison drove home the UI patterns in his talk on using jQuery and HTML5 with SharePoint. Between them, they mentioned a number of useful tools and add ins which I’ll document shortly in an upcoming post. As always, both presenters were highly entertaining.

On the BI front, I attended sessions by Carolyn Chau (Reporting Services), Kevin Donovan (Analysis Services), Peter Myers (PowerPivot), and John Hancock (PerformancePoint and PowerPivot). They outlined the BI features of SQL Server Denali that specifically pertain to SharePoint, in particular project Crescent, and the new version of PowerPivot. PowerPivot is now more than just an analysis tool, it’s also a designer for Analysis Services. Project Crescent is a power user tool meant to support ad hoc reporting. In fact, in my opinion, Crescent has the same relationship to Reporting Services that the current version of PowerPivot has to Analysis Services. In Carolyn’s last session she also noted that Denali would be released in Q1 2012 – we now have a “date”!

In the “best practices” category (I hate that term….) there were a number of standouts. Darvish Shadravan and Asif Rehmani gave some great insights into Infopath. What many don’t understand is how important Infopath is to SharePoint, particularly to Office365 where coding is significantly more problematic. Darvish was also monitoring a Twitter feed during the session to hand out swag, which I thought was a great idea. I’ll be using something similar for my next presentation for feedback at least. Asif was clear and concise as usual, and both sessions were highly entertaining.

Eric Shupps led an early morning session on performance tuning, an increasingly important topic for SharePoint farms. I took quite a few notes. I attended the session to confirm my deployment approaches, which I did, but also came away with a laundry list of items to add. Entertaining, and a very good use of my time.

The king of the User Profile service, Spencer Harbar delivered a clear and concise talk on the User Profile service. I follow Spencer’s blog quite closely, so many of the points that he made I was already aware of. However, I did pick up one technical tidbit that I was previously unaware of. The service requires that the synchronization account be granted the “Replicating Directory Changes” permission in Active Directory. This at times terrifies AD administrators, and it turns out that the name is bad. All that the permission does is to allow the account to read a change log. Spencer also made an excellent point in that the deployment of a user directory is 90% political and only 10% technical.

Finally, Christina Wheeler wrapped up the session with a spirited, if challenged talk on packaging Branding elements with Visual Studio. I have done extensive work with both branding and with solution packaging, but oddly, have never put the two together. I certainly will in the future. Christina was badly ill with bronchitis, and had to battle a possessed projection system, but did so without ever losing her sense of humour. I didn’t know Christina before attending, but will certainly seek her out in the future.

The best part of SPC is of course the networking events, of which there were several every night. Even though the big Disney night was a letdown in my opinion, the people more than made up for it. I left the conference knowing much more than I did going in, rekindled some (very!) old friendships and started a few new ones. Overall, a great conference.

Now the countdown begins to SPC 2012!!!!

4 Comments