PowerShell script to configure SQL Server Reporting Services in SharePoint mode

SSRS.png

The below mentioned script will take care of the steps listed below :

  1. Installs Reporting Services service and service proxy, and starts the service.2
  2. Creates a service proxy named “Reporting Services”.
  3. Creates a Reporting Services service application named “Reporting Services Application”.
  4. Enables the Power View feature for a site collection.

 

#Script for SSRS Configuration in SharePoint Integrated mode

$starttime=Get-Date
write-host -foregroundcolor DarkGray StartTime>> $starttime

Write-Host -ForegroundColor Green “Import the SharePoint PowerShell snappin”
Add-PSSnapin Microsoft.Sharepoint.Powershell –EA 0

Write-Host -ForegroundColor Green “Install SSRS Service and Service Proxy, and start the service”
Write-Host -ForegroundColor Green “>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>”

Write-Host -ForegroundColor Green “Install the Reporting Services Shared Service”
Install-SPRSService

Write-Host -ForegroundColor Green ” Install the Reporting Services Service Proxy”
Install-SPRSServiceProxy

# Get the ID of the RS Service Instance and start the service
Write-Host -ForegroundColor Green “Start the Reporting Services Service”
$RS = Get-SPServiceInstance | Where {$_.TypeName -eq “SQL Server Reporting Services Service”}
Start-SPServiceInstance -Identity $RS.Id.ToString()

# Wait for the Reporting Services Service to start…
$Status = Get-SPServiceInstance $RS.Id.ToString()
While ($Status.Status -ne “Online”)
{
Write-Host -ForegroundColor Green “SSRS Service Not Online…Current Status = ” $Status.Status
Start-Sleep -Seconds 2
$Status = Get-SPServiceInstance $RS.Id.ToString()
}

$time=Get-Date
write-host -foregroundcolor DarkGray StartTime>> $starttime
write-host -foregroundcolor DarkGray $time

Write-Host -ForegroundColor Green “Create a new application pool and Reporting Services service application”
Write-Host -ForegroundColor Green “>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>”
Write-Host -ForegroundColor Green “Create a new application pool”
#!!!! update “-Account” with an existing Managed Service Account
New-SPServiceApplicationPool -Name “Reporting Services” -Account “<domain>\User name>”
$appPool = Get-SPServiceApplicationPool “Reporting Services”

Write-Host -ForegroundColor Green ” Create the Reporting Services Service Application”
#!!!! Update “-DatabaseServer”, an instance of the SQL Server database engine
$rsService = New-SPRSServiceApplication -Name “Reporting Services Application” -ApplicationPool $appPool -DatabaseName “Reporting_Services_Application” -DatabaseServer “<server name>”

Write-Host -ForegroundColor Green “Create the Reporting Services Service Application Proxy”
$rsServiceProxy = New-SPRSServiceApplicationProxy -Name “Reporting Services Application Proxy” -ServiceApplication $rsService

Write-Host -ForegroundColor Green “Associate service application proxy to default web site and grant web applications rights to SSRS application pool”
Write-Host -ForegroundColor Green “>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>”
# Associate the Reporting Services Service Applicatoin Proxy to the default web site…
Get-SPServiceApplicationProxyGroup -default | Add-SPServiceApplicationProxyGroupMember -Member $rsServiceProxy

$time=Get-Date
write-host -foregroundcolor DarkGray StartTime>> $starttime
write-host -foregroundcolor DarkGray $time

Write-Host -ForegroundColor Green “Enable the PowerView and reportserver site features”
Write-Host -ForegroundColor Green “>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>”
#!!!! update “-url” of the site where you want the features enabled
Enable-SPfeature -identity “powerview” -Url http://server/sites/bi
Enable-SPfeature -identity “reportserver” -Url http://server/sites/bi

####To Verify, you can run the following:
#Get-SPRSServiceApplication
#Get-SPServiceApplicationPool | where {$_.name -like “reporting*”}
#Get-SPRSServiceApplicationProxy

Useful SQL Queries for SharePoint Practitioners:

The SQL queries listed below can be very useful for SharePoint Farm administrators to manage/administer the SharePoint content databases .

Note: The queries mentioned below are not specific only to SharePoint databases and can be used with any SQL database.

1.To get the total number of space utilized by all the SharePoint databases in SQL server:

SELECT CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00 / 1024.00)) As UsedSpace
FROM master.sys.master_files

2.To get the name of all the SharePoint databases in a SQL instance :

Select * from Sys.Databases

3.To get the total number of space utilized by all the SharePoint databases in SQL server:

SELECT CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00 / 1024.00)) As UsedSpace

FROM master.sys.master_files

4.To find the space used by a SharePoint DB and its free size :

*Replace MY_DB with the concerned database name

use “MY_DB”
exec sp_spaceused

5.To find the size consumed by SharePoint Databases individually in SQL Server:

SELECT DB_NAME(database_id) AS DatabaseName,

 

Name AS Logical_Name,

 

Physical_Name, (size*8)/1024 SizeMB

 

FROM sys.master_files

6.To get the total number of SharePoint databases in the SQL server:

select  * from sys.databases

or

select  COUNT(*) from sys.databases

7.To find the path for SQL Server error logs:

sp_readerrorlog

8.To get the total number of site collections in a Web application :

select  count(*) as ‘Total Site Collection’ from sites

Note: Point to the content database hosting that site collection and run this query

9.To get the total number of sites in a web application :

select count(*) from Webs

Note: Point to the content database hosting that site collection and run this query

10.To get the Site Title and Site ID :

select Title as ‘Site title’,FullUrl, SiteId as ‘Site Collection Id’ from Webs order by SiteId

11.To get the number of sites under each site collection in a web application :

select SiteId, count(*) as ‘Total Sub Sites’ from Webs inner join Sites on Sites.Id = Webs.SiteId group by SiteId

Note: Point to the content database hosting that site collection and run this query

 

 

 

 

 

Step-by-Step Installation of Microsoft SQL Server 2014:

Alright, for those who are already working as SQL DBA’s this is nothing new and this should  just be a piece of cake for you guys. For those who are new to SQL, please consider this article as a primer for SQL Server 2014 installation.

Prerequisites for installing SQL Server 2014:

  1. 32- bit computers not allowed
  1. The minimum operating system requirements for the SQL Server 2014 are one of the following:

–>Windows Server 2012

–> Windows 8 RTM

–> Windows Vista SP2

–> Windows Server 2008 SP2

–> Windows 7 SP1

–> Windows Server 2008 R2 SP1

  1. .NET Framework 3.5 is required.

Listed below are the steps to be followed to install SQL Server 2014:

  1. Download SQL Server 2014 SP1 from the below mentioned link

https://technet.microsoft.com/en-in/evalcenter/dn205290.aspx

  1. Once you’re done downloading the ISO file, please extract it to your preferred drive location and keep it ready for installation.
  2. Run the SQL Server 2014 setup as shown below .Make sure that you’re running it with elevated permissions as shown in the image below.

2

4. Please select “installation” from the list of options given in this screen.

3

5. This screen shows the types of installation available. Let’s select the first type i.e.  “New SQL Server stand-alone installation” as shown in the image below.

4

  1. Click on “Run” in the next screen.

5

7. Since I’m running an evaluation version, I’m going to keep the option as evaluation in this screen.

6

8. If you want to participate in the CEIP program, then put a checkmark in the box highlighted in the image below .If not just accept the license agreement and click next.

7

9. The “rule check”  is in progress as shown in the screen below.

8

10. Now if you want you’re SQL server installation wizard to look for any new updates, please put a checkmark in the box shown in the image below. The most preferred and suggested practice is to not check that box as it’s good to have a look at those updates in the Microsoft website and then run them manually . Hence I’m not checking that checkbox here.

9

11 . You might get an error as shown below . That’s a common error which you would get since I didn’t check the “Check for updates” checkbox .I’m just going to ignore it and proceed further with the installation by clicking next.

10

12. The set-up files are getting installed as shown in the image below.

11

13. Now you can see the below mentioned screen displaying warnings about the domain controller, .NET Application Security & Windows Firewall .Now if you’re performing this installation in a test lab its ok to ignore these errors and proceed further.However,if you’re performing this installation in a production server its mandatory that you fix these issues . Since I’m performing this installation in a test lab I’m just going to ignore this error and proceed further with this installation by clicking next.

12

14. The next screen gives you an option to choose which feature components to install, please click on “SQL Server Feature Installation” and click next.

13

15. Please select the evaluation features for this instance by checking the appropriate check box. Now how this works is if you want this instance to just have the “Database Engine Services” running, then please check the check box for “Database engine services” alone . Similarly if you want “Analysis Services” as well as “Reporting Services” running in the same instance, then check those check boxes as well.

14

16. Once you’re done choosing the required features for this instance the installation wizard will calculate the required disk space and will display it and shown in the above image.

15

17. Once you’re done choosing all the necessary features make sure that you  check the “client connectivity tools”  and “management tools”  (SSMS) check box and click on next. These tools are mandatory to manage SQL Server.

18. You will see the next window where the operation proceeds further as shown in the image below.

16

19. Now once that’s complete and the operation is successful you will be taken to a window where you’re supposed to make the “Instance Configuration” . Now in SQL Server its very important that you understand the two different types of instances

a) Default Instance –>Choosing the “default instance” will pick up the server name/PC name in which you’re performing the installation and will proceed further with the installation.

b) Named instance–>Choosing the “Named instance” option will ask you to name the instance and the same name will be assigned to the “Instance ID” as well.

To access a default instance once it is installed  using SQL Server Management Studio or any other program, you just need provide the computer name. To access a named instance, you will need to provide the name of the instance in the form of “Computername\nameoftheinstance”

It’s always preferable to go with “Named instance “option so that you would be able to differentiate between instances if many instances are installed in the same SQL server.

11

  1. Once that’s done the next screen (shown below) will ask you to specify the service account details for all the features that has been chosen , please specify the appropriate username and password as required . Now ,since this is my test lab, I’m going to stick to the defaults. It also gives you the option to choose the “startup type” .It can be either “Automatic” or “Manual”.

12

However there are couple of things to notice here, if the computer is not part of a domain, use a local user account without Windows administrator permissions. You can create this account using Control Panel -> User Accounts. Please provide a strong password to this account since SQL Server setup will required it on this “Server Configuration” page.

Use a Domain User Account if the service must interact with network service or access domain resources. This account should be pre-created by a domain administrator in AD.

  1. Make sure that you’re providing the credentials correctly or else you would end up getting a “validation error” as shown below.
  2. Once that’s done the next screen will ask you to specify your “authentication mode”. You have two authentication modes in SQL Server namely 1. Windows Authentication and 2. Mixed mode (Windows + SQL) authentication. If you choose Windows Authentication then users who are created within SQL server won’t be able to access SQL, so the best and recommended practice is to go with “mixed mode authentication”.

14

23) Once you’re done specifying the “authentication mode” please click on “Add current user” button as shown in the image below and this will make the current user who is running this wizard as the SA (i.e SQL Server Admin).

15

24) You can also add few more users to the “SQL Server Administrators” group by clicking on the “Add” button next to “Add current user” as shown in the above image.

25) Once that’s done , please click next and this will take you to the ‘Analysis Services Configuration “ wizard , please specify the “ Analysis Services administrator “ detail as shown in the image below and click next .

16

26) The next screen would give you the path for the “Data Directories”, you can change this path if required.

17

27) The next screen would be for “Reporting Services Configuration”, I strongly recommend to go with the second option (i.e. Install Only) as once the installation is complete you can configure “Reporting services” later using the “Reporting Services Configuration Wizard”.

19

28) Finally, On the Ready to Install page, review the summary of features and components for your SQL Server installation. To proceed, click Install…

20

You would also notice  the “Configuration file path” which has the “configurationfile.ini” and this file can be used later if you want to create a different instance of SQL Server with the same configuration.This file basically holds all the configuration information for this instance of SQL installation.

29)  Once you’re done reviewing the summary of features, please click on Install as shown below and you would notice the installation proceeding further.

21

30) Finally you would get a window as shown below which displays the status of our installation and if everything ran well you would notice a bunch of green tick marks. You can view the “summary log “ file if you want which will give you the complete information of the installation.

22

31) Now that brings us to the end of the SQ Server 2014 installation and you can open “SQL Server Management Studio” to access it.

24

If you’re also looking for the new features in SQL Server 2014 , please check this site : _ http://windowsitpro.com/sql-server-2014/top-ten-new-features-sql-server-2014