Sharepoint integrated reporting services with SQL Always on availability group

It happened once that i needed to provision a Sharepoint 2013 farm including MS SQL Server Reporting Services (SRSS). I had some issues – maybe you found this post ‘cos of that – which i would also share and give a hint on the solution.

Environment:

  • 2 Web Front End servers
  • 2 Application servers
  • 2 MS SQL database servers at the back
  •  Operating System is MS Windows Server 2012 R2
  • MS SQL server version: 2012 SP1 (name: \\databaseserver1\instance1:3452;  \\databaseserver2\instance2:4564)

Usually i like to have some security in our implemented environment so i change the listening port of the given SQL database server beside i create a named instance.

Where to define the listening port for the SQL server instance?
Just open up the SQL Configuration Manager tool can be found among the applications. Open it with your SQL Server service account.
Enable named pipes at the SQLServer Network Configuration / Protocols for [instance name] and set the TCP Port parameter to your new number (in my case to 3452) under the TCP/IP / IP Addresses tab / IPall section. Not to forget to delete any number for the TCP Dynamic Ports, that should be empty.
Please do that modification at both the 32 and the 64 bit section.

sql_listeningport_config
First step after installing the prerequisites is to configure the SQL Server listener. I think that’s the easiest way to provide a basic fault tolerant configuration for the Sharepoint environment.

What does SQL alias means?
A name could be mapped to the SQL server instance and listener port. Then this name (alias) could be used at the Sharepoint server installation as well and also at the configurations of the given sharepoint services like search, Performance Point etc.

How to define SQL alias?
Next i would like to show you the way to define an alias to your given SQL instance.
Let’s have an example for the SQL instance

  • name: \\databaseserver\instance
  • this instance is listening at the port 3452

The SQL Alias should be defined at all Sharepoint servers. So in my example i ran the cliconfg.exe tool to define the alias at every Sharepoint machine. For details follow this link: http://blogs.technet.com/b/meamcs/archive/2013/01/22/creating-a-sql-server-alias-using-the-sql-server-client-network-utility.aspx

set_sql_alias

Now we are ready to install the Sharepoint itself 🙂 remember to use the SQL server alias name during the first configuration wizard when you are creating the new farm. (and also please remember for the other prerequisites as the server roles should be added to the installer account at SQL server level)

 

So now we have a basic one sharepoint server farm with a SQL back end which has already the Reporting service in sharepoint integrated mode installed.

Do the basic configuration settings at the sharepoint farm. then let’s look at the reporting services service application configuration.

As you would like to create a new Reporting services service application you can notice that you are not able to select such a service application from the drop down menu at the ribbon. That’s because the Reporting services were installed before the sharepoint. So you have to install/register/start the SQL  Server Reporting Services Service Application.

no_reporting_services
Let’s open our old friend the Sharepoint 2013 Management Shell powershell environment. by using the farm account. And type the following commands:

  • Install-SPRSService
  • Install-SPRSServiceProxy
  • get-spserviceinstance -all |where {$_.TypeName -like “SQL Server Reporting*”} | Start-SPServiceInstance

Afterwards we could be happy to have the SQL Server Reporting Services Service Application in our list. Let’s create one! Here you can also use the SQL Alias as the name of the SQL database server.

reporting_services_there

adding_reporting_services

Anyway why to use SQL Alias?

Just imagine a situation when you would like to upgrade your SQL server back-end and during this process you have to provide and available Sharepoint farm. In that scenario you have to have at least two SQL instances. Meanwhile you are working on one of the SQLs the Sharepoint should be able to connect the other. But configuring sharepoint to use another SQL server than the default was defined during the first installation – well it’s more simple to have an Alias which the sharepoint connects to and after having the sharepoint databases and logins “migrated” to the new SQL instance you need “just” to edit the SQL aliases at the sharepoint servers.

But please read further the post and you will understand another scenario as well (Always on group configuration).

Afterwards at the site collection level we have the possibility to activate the Report Server integration Feature and the Reporting Features.

site_features

But when clicking on the button “Activate” we get an error back which tells us that the Performance Point Serives service application is not installed/configured yet at the sharepoint farm.
And that’s the point. Before the reporting features could be activated we have to provision the Performance point services service application as well. So please do that also.

Well after having this issue solved we can create a BI site under our root site collection (please activate the publishing feature as well – Sharepoint Server Publishing infrastructure).

new_BI_site

 

content_types

 

 

 

So when you are creating a subsite you can choose the Business Intelligence Center Enterprise template which contains content types for the reporting stuff.

Let’s create a document library and go to the Library settings and add the new content types to it.

BI_site_created

Now you can create a new Report Builder Report document.

create_new_report

 

 

report_builder

So we are at the middle of our trip.

Another performance tip here

Use fixed size content database files and 64K sized cluster block at the file system level (for the database drive at SQL server)

During the new content database creation from the powershell you can meet the next issue running the new-spcontentdatabase commandlet. You define the credential to connect the database server you provide at the database server attribute but get back the following error:

New-SPContentDatabase -name WSS_Content_Cube1 -DatabaseSe
rver SP_SQL -WebApplication httpcube -DatabaseCredentials $cred

New-SPContentDatabase  Could not connect to SP_SQL with user
sp_farm Cannot connect to database master at SQL server at SP_SQL.
The database might not exist, or the current user does not have permission to
connect to it.
At line1 char1
+ New-SPContentDatabase -name WSS_Content_Cube1 -DatabaseServer SP_SQL -We …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~
+ CategoryInfo           InvalidData (Microsoft.Share…ContentDatabase
SPCmdletNewContentDatabase) [New-SPContentDatabase], SPException
+ FullyQualifiedErrorId  Microsoft.SharePoint.PowerShell.SPCmdletNewConte

The given message is very frustrating as our farm is up and running so sharepoint is able to connect to the given SQL server and databases but our commandlet not. If you don’t enter the credentials the commandlet tries to connect with the user account that you opened the powershell window of course.
Checking the SQL Server logs you don’t have any related information that some mchine/user tries to connect the server. So maybe there could be a firewall issue? unfortunatley not. If you define all the ruled (inbound/outbound) or shut down the firewall and you checked the SQL server connection settings at the configuration manager tool five more times you still have this error message.

the solution is that drop away the -Database attribute    in that case the commandlet will use the sharepoint default SQL connection and able to create thee new content database for the given webapp.

Get-SPContentDatabase -WebApplication “http://cube”

Id               : 3b69f86a-ba71-42eb-a4e0-05955184774e
Name             : WSS_Content_Cube
WebApplication   : SPWebApplication Name=The Cube
Server           : SP_SQL
CurrentSiteCount : 1

New-SPContentDatabase -Name WSS_Content_Cube1 -webapplication http://cube/

Id               : f906c8d2-654d-419b-8286-499e1e0ccb9e
Name             : WSS_Content_Cube1
WebApplication   : SPWebApplication Name=The Cube
Server           : SP_SQL
CurrentSiteCount : 0

 

Now we should configure the Always on part.

Always on setup

As  you check the SQL you can find so many databases created for sharepoint. So these databases will be added to the Always on group to provide a fault tolerant back-end solution for our farm without having a shared storage connected to the SQL machines.

To succeed on all the SQL servers which should be part of the Always on group you have to install the Fail-over cluster feature at Windows level. No worry, no shared storage needed 🙂 – follow this great post about that: http://www.sqlpassion.at/archive/2012/03/21/sql-server-2012-alwayson-availability-groups-part-1/

After having the Fail-over feature deployed at the SQL Server Configuration Manager Tool you are able to check the Enable AlwaysOn availability Groups check box – hurray!

Then At the SQL management studio we can define a new AlwaysOn availability group. Please follow this great post: http://mssqltrek.com/2012/09/11/setting-up-sql-server-2012-alwayson-availability-groups-part3/

At the end if you have the Listener for your AlwaysOn group you can configure the SQL client cliconfg.exe to modify our settings to point to the listener’s settings.

   NOTE: So Reporting services in sharepoint integrated mode should be installed to the Apps servers and Reporting Services Sharepointt add-in should be installed to the WFE machines 🙂

Not an easy but another very interesting journey to the SharepointLand!

 

Cheers, Adam

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s