SQL Server reporting and SQL PowerPivot Disappear after Removing from and existing farm and re-join to the existing farm

If you have more than 1 server to place sharePoint farm, sometimes you would like to remove a particular server from the existing farm due to some reasons that you must do and afterwards you plan to join it back to the existing farm.

When you are having Powerpivot within your sharepoint web application, you will realize that you need at least two services on server in order to make your powerpivot ready to use. The first service is SQL Server PowerPivot System Service and the second one is SQL Server Reporting Service.

I write this blog because I experienced this kind of case when I was setting up Availability Group in SQL server where I need to reconfigure the connection string from sharepoint to virtual IP of the database so that the HA is working properly. After reconfiguring the connection between SharePoint and SQL Server, I was having problem with the secure store service which is used for my powerview web application. In order to solve the problem what I did was removing sharepoint server from the existing farm then rejoining again with the new name of SQL server instance.

Once I finished disconnecting the server to the existing farm, then I directly Re-joined the server to the previous farm with the new database server instance name to retrieve sharepoint database configuration. 

After I successfully rejoined my sharepoint server into existing farm, I started to play around with the SQL Server by failing over between primary and secondary server. And every time I swing the server, I at the same time check the secure store service is working properly... :D

I thought my job with the issue was done and I can use my sharepoint server normally, but when I was trying to open my powerview file through sharepoint content, It gave me the error which I don't remember exactly the message, but what I know is that I was unable to open the file because the service is missing and then I panic. I tried to check the uls log, but it did not give me enough information until I check the service on server from central admin. Then I realized that two services that have been used to run the powerview (SQL Server PowerPivot System Service and SQL Server Reporting Service) in my application went missing :O

After I find out that these two services were gone, I started to search through the internet regarding this case happening to me. Until I got an idea. What I did is I run the Sharepoint Shell management run it as administrator and I run the script as picture below 

After running the script, I ran PowerPivot for SharePoint 2013 Configuration tool chose 'configure or repair powerpivot for sharepoint'

After I finish reconfiguring powerpivot sharepoint, I then refreshed my central admin and went back to manage services on server, and then check the services are back on track and at the same time my powerview is back to normal as well :D 

Happy trying...

Hope this will be useful and informative for you

Thanks your visiting my very first blog, Cheers :D