Tag: SQL Server 2008 R2

SQL Server Data Tools, BIDS, Visual Studio – What Do You Need?

Published April 16, 2015

The Business Intelligence design components of SQL Server have an identity crisis.

What I’m talking about are the tools that are used by designers to create BI objects in the SQL Server Business Intelligence stack, specifically Analysis Services (SSIS) OLAP cubes and tabular models, Integration Services (SSIS) ETL packages, and Reporting Services (SSRS) reports. These tools have always been bundled into a single product as part of the SQL Server distribution. The original incarnation of the tools was called Business Intelligence Development Studio, or just BIDS. It was originally introduced with SQL Server 2005, and was included on the SQL distribution media as an optional install component.

BIDS was a distribution of the Visual Studio shell, and a set of bundled project types for creating cubes, SSIS packages and SSRS reports. The original version was based on Visual Studio 2005, and subsequent releases of SQL Server stayed in step with more recent versions of Visual Studio. The projects were tied tightly to the Visual studio versions. If, for example you had Visual Studio 2010 already set up, installing BIDS would still install a different Visual Studio to support the projects. BIDS maintained its own identity. Installing BIDS is straightforward, you simply run setup from the SQL Server media, and select “Business Intelligence Development Studio”.

When SQL Server 2012 was released, a change was introduced. In addition to the projects required for tabular models in SSAS being added to the tools, they also received a new name. Henceforth they were to be known as SQL Server Data Tools (SSDT). As expected, the requisite level of Visual Studio was incremented and SSDT was based on Visual Studio 2010. The installation experience didn’t change fundamentally, the installation option just took on the new name.

So far so good. Products get renamed all of the time. Simply substitute SSDT for BIDS, and everything is pretty much as it once was.

SQL Server 2014 is the latest SQL Server version, and it introduced another major change. Data tools is no longer available from the SQL Server installation media.

This is where it gets very confusing. It was always possible to download BIDS or SSDT directly from Microsoft. These products don’t require a license to deploy and use, so they were freely available. This is also true with SQL 2014, but now, a download is the only way to get the tools. That’s simple enough. However, if you search for SQL Server Data Tools and download the version for either Visual Studio 2012 or Visual Studio 2013, you won’t find the projects that you were looking for. You’ll instead find projects for deploying databases and DACPACs. So what’s going on here?

As it turns out, a separate Microsoft team put out a separate set of VS project templates in the SQL Server 2012 timeframe that were also called SQL Server Data tools. Apparently there are a finite quantity of names.

The product that we originally knew as BIDS, and then SSDT was renamed one again for the SQL Server 2014 wave of products. Well, only sort of renamed. It’s now called SQL Server Data Tools – BI. Apparently this was intended to avoid confusion…. SSDT-BI is available for either Visual Studio 2012 or Visual Studio 2013. If you don’t already have a Visual Studio installed, it will install a VS shell for you.

The summary of all this is to say that if you want to build BI projects for SQL Server, you’ll need to have the right tooling for your target server, and that tooling is as follows:

Server	Toolset Name	Location
SQL Server 2008 R2 and below	Business Intelligence Development Studio	On SQL Media
SQL Server 2012 and 2012 SP1	SQL Server Data Tools	On SQL Media
SQL Server 2014	SQL Server Data Tools – BI	Download for Visual Studio 2012 Download for Visual Studio 2013

The complete current Data Tools product set is laid out and can be downloaded from here. The Data Tools team blog is here.

I hope that this helps to clear up some confusion. I can’t wait to see what they have in store for us in V.Next….

25 Comments

Installing and/or Upgrading a Multi-Server SQL Server Reporting Services 2012 SharePoint Mode Farm

Published September 7, 2012

A few months ago I posted an article discussing how to upgrade integrated mode Reporting Services from 2008R2 to 2012. That article pretty well assumed a small SharePoint farm, with a single SharePoint server, a single SQL server, and with Reporting Services running on the SharePoint server. In this article, I’d like to address upgrading and/or installing on a medium or large farm, but to do so, I need to first discuss the nuances involved in scaling out the Reporting Services service application of a SharePoint 2010 farm.

As I discussed previously, and as the Service Application architecture of Reporting Services 2012 makes plain, Reporting Services bits from the SQL server installation media must be installed on a server that has the SharePoint bits installed, and is joined to the farm. In the simple farm scenario mentioned above, that’s fairly straightforward – it’s the SharePoint server (NOT the SQL server!). However, if your SharePoint farm consists of multiple servers, you need to decide where you want your Report processing to be done. With 2012, Reporting Services is a full fledged SharePoint application, which means that it is relatively straightforward to load balance this processing.

A recent project that I worked on had an architecture very similar to the diagram found on the MSDN article explaining how to scale out Reporting Services:

The only difference in my case was that there were 4 servers in the front end role (1). Of course the servers in the application role served up more than just Reporting Services, but the diagram is essentially accurate. In our case, the SQL Server cluster was running SQL Server 2008 R2, but that was inconsequential because only the Reporting Services instances on the two Application Servers were being upgraded.

If you are are upgrading, there are a number of other steps in addition required first. No matter what, the encryption key should be backed up first. It will be needed after the upgrade unless you want to recreate all of your data connections. Once that is backed up, you should of course back up your two Reporting Services databases. Next, if you have a load balanced (Reporting Services) environment, I recommend removing from rotation all of the load balanced RS servers, leaving only the server that will be used for the primary upgrade. To make things very simple, I also recommend completely uninstalling Reporting Services from all but the one server.

Once complete, the main server can be upgraded according to the instructions laid out in my previous article. However, you may find, as I have, that SSRS doesn’t always want to be upgraded cleanly. The good news is that this is relatively easy to recover from. The SSRS service application works like other service applications in that when it is being created, and you specify an existing database, that database will be upgraded automatically. Therefore, if you find yourself with an uncooperative SSRS installation, simple uninstall it, and install the 2012 components from scratch, making sure to use the name of your existing RS database when the service application is being created. Once complete, restore your encryption key, and you should be good to go.

Whether or not you are upgrading or installing fresh, there are a number of differences when installing to a multi server farm compared to a single server installation.

Firstly, when installing SSRS 2012, you will be presented with a screen where you may choose the SQL features to be installed.

There are two components to Reporting Services in SharePoint mode, Reporting Services – SharePoint, and the Reporting Services Add-in for SharePoint Products. A lot of the guidance simply says to select both options and continue. However, in a multi farm environment it is important to understand the difference between the two.

Feature #1 is the core of Reporting Services, what in the past would have been the instance, but is now the service application itself. Feature #2 is the add-in, which has been around since the first integrated mode SSRS. It is used by SharePoint to connect to SSRS. In the past, that was a connection to the SSRS web services, but is now how the SharePoint front end servers connect to the service application. Therefore, in a multi server farm, feature 1 should be installed on every application server that will process SSRS reports, and feature #2 must be installed on every server participating in the front end role. Since application servers often perform a dual role, at least for administrators, I recommend installing both features on application servers.

I’m not going to walk through the steps required to create the service application and light up the features in this article, as there is quite a bit of good guidance on that available. I also wrote one up for installing on SharePoint 2013 which is pretty much identical to 2010 for SSRS.

Once the initial installation is complete, it should be repeated on every server that will participate in the Application server role. Obviously the service application only needs to be created once. Once all of the bits are installed on all of the relevant servers, simply navigate to Services on Server under System Settings in Central Admin, and start the “SQL Server Reporting Services” service on every application server. Once that’s done, you’ll have a load balanced, multi-server Reporting Services service.

It is worth calling out a common error encountered in the multi-server farm scenario. You may find that after your upgrade or installation has completed, attempts to access a report from the front end servers result in a connection error, “The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version”.

It’s the last bit of the error that’s relevant. This problem arises when the add-in hasn’t been installed to the front end servers in the farm. Now, you may remember that the Reporting Services Add-In is one of the prerequisites that the prerequisite installer installs on your SharePoint boxes. In the case of an upgrade, you may also remember that you never had to do this before to get SSRS working. So why is it that we need to do this now? It’s because the add-in included with the prerequisite installer is for SSRS 2008 R2, and we’ve just added SSRS 2012. The add ins are not forward compatible, and therefore, it need to be on every front end server in the farm.

So to recap, in order to scale out reporting services, Install the service on one application server, and get it working in the farm. Then, install the service on the remaining application servers, start the service on each server, and install the RS add-in for 2012 on all of the front end servers.

1 Comment

Migrate Reporting Services from Native Mode To SharePoint Integrated Mode

Published April 26, 2012

I have previously written about upgrading and moving Reporting Services to SSRS/2008 R2/SP2010, and also on upgrading to the new Service application in 2012. Both of these deal with moving prior versions of Reporting Services running in SharePoint mode to more recent versions, also running in integrated mode. What has been lacking from Microsoft until now was a mechanism to help move an organization from Reporting Services in Native mode to Reporting Services in integrated mode.

The solution to date has been to go back to the source projects in BIDS and redeploy them to the Integrated Mode server. This of course assumes that BIDS was used for report design (not Report Builder), and that the projects are available. You also lose all server side configurations (like subscriptions) with this approach.

On Friday, April 20 2012 Microsoft released Version 1.0 of the Reporting Services Migration Tool, which allows you to do just that. It’s a high level tool that brings all of the artifacts out of the Native mode instance, and at a later point in time, import them into the Integrated Mode instance. Ultimately, the stated aim of the tool is to allow a file system level backup of your Reporting Services Instances, be they Native or Integrated mode.

It can be run either by command line, or through a GUI. A snapshot of the GUI screen can be seen below.

The tool is definitely version 1, and has several limitations which I’ll outline below, but it does work. It does so by connecting to either the WMI provider, or the Reporting Services web services, then extracting all of the available content, and then building a PowerShell script which can be run to place the backed up content in a SharePoint document library that has been properly configured to support the Reporting Services content types.

Operation of the tool is relatively straightforward, and is adequately documented on the download page, so I won’t go through a step by step, but I do want to share a few observations.

Firstly, migration is from Native Mode to Integrated Mode only. The stated objective of this tool is to support both modes on either end of the migration path, but for now it’s a one way trip. For the moment, it does limit its ability to perform as a backup tool. However, if you examine your output folder, you’ll find all of your report files, connection files (etc), so if you’ve built your reports with BIDS, and lost the original source project, it’s a great way to get them out of the Reporting Services database.

I have also been unable to get the WMI provider to work at all. I’ve tested with both SSRS 2008R2 and SSRS 2012 Native mode sources, but the tool can’t seem to find the WMI instance. The tool still works in this configuration, but it will not back up passwords or history snapshots. I’ll update this post if/when a solution to this can be found.

UPDATE – Thanks to Tristan in this MSDN forum thread – The WMI provider is working. I have added the paragraph and image below.

The Instance Name field is mislabelled. It should be SERVERInstance for non default instances, or just SERVER for default instances. Unfortunately the nowhere in the help is the requirement for SERVERNAME mentioned. Essentially, you should treat this field the same as you would the Server field when connecting via Management Studio. The image above has been updated to show the correct value for Instance Name (In this case, although not necessary, I have included the name of the default instance).

As outlined on the download page, the tool does not back up Reporting Services security information, or role information – which makes sense when moving to a new security model. Also, linked reports aren’t supported in Integrated mode, so they’re not backed up at all.

For a complete list of constraints and instructions, visit the download page.

For it’s limitations, this tool is a very welcome addition to the toolkit. Migrating from Native Mode to SharePoint Integrated mode Reporting Services no longer needs to be painful.

2 Comments

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

Using SQL Server Report Builder with Internet Explorer 9

Published February 7, 2012

One of the unsung heroes of the Microsoft Business Intelligence stack is Report Builder. Report Designer has been part of Business Intelligence Development Studio (BIDS) for quite some time, but BIDS is more of a designer tool. In order to get report design into the hands of power users, Microsoft provided Report Builder initially with a reduced set of functionality SQL Server 2005, but with Report Builder 3.0 which ships with SQL Server 2008 R2, it’s just as capable as BIDS. When running in SharePoint Integrated mode, you can design reports as if they were any other type of Office document.

Unfortunately, if you edit a report, you may be greeted with the message: “To use Report Builder, you must install .Net Framework 3.5 on this computer.”

Report Builder does require .Net Framework 3.5, but you’ll see this message even when you have it installed. Clicking on the “Install .Net 3.5” will reinstall it, but won’t help.

This only happens when using Internet Explorer 9, and is due to the fact that it doesn’t correctly detect the Framework’s presence. This doesn’t happen with any other browser, including previous versions of IE. It can be worked around by setting the browser’s compatibility mode.

To do that, either press the F12 key, or turn on developer tools from IE’s Tools menu:

Doing so will bring up the developer tools window, where you can set the broswer mode. Setting it to anything other than IE9 will work.

Just the act of setting it should start the Report Builder download process. The setting will persist for the life of the browser window, so you’ll need to do it again the next time that you edit the report.

I’d love to hear of any better fixes to the problem, but for now, this lets you get the job done.

2 Comments