SharePoint Farm Reporter uses SQL Server database for its data storage to generate
SharePoint Farm Reporter requires an SQL Server running
Microsoft SQL Server 2012 (Enterprise / Standard / Developer / Express edition)
or Microsoft SQL Server 2008 (Enterprise / Standard / Developer / Express
edition) or Microsoft SQL Server 2005 (Enterprise / Standard / Developer /
Express edition) to connect and create a database.SharePoint Farm Reporter will connect to the specified SQL Server based on authentication mode and user credential to manage its own
SharePoint Farm Reporter will prompt for the SQL settings (Server name, authentication mode, user name and password) when Quick Reports / Power Reports / Custom Reports feature is
launched for the very first time. SQL Server Settings dialog appears as shown below :
Subsequently, SharePoint Farm Reporter maintains its application settings (SQL settings, E-mail settings, Power Reports tasks and history) in a set of XML files created in the application installation folder.
You can also specify SQL Server settings
to use by clicking Tools -> SharePoint Farm Reporter Settings menu in the SharePoint Farm Reporter main application window, as shown below :
How SharePoint Farm Reporter manages database in SQL Server using the given SQL settings:
To connect to SQL Server, SharePoint Farm Reporter uses the relevant user accounts based on the authentication mode as listed below:
In this method, SharePoint Farm Reporter uses the currently logged on user account while running Quick Reports (under interactive mode) and uses 'Run As' account while running Power Reports task (run as a scheduled task or using Run Now
option) to authenticate the user in SQL Server.
In this method, SharePoint Farm Reporter uses the specified SQL user account and password for running Quick Reports and Power Reports tasks. It stores the SQL user name and password as a user profile in 'Stored User Names and Passwords' applet for its usage.
Read Profile Manager for more details.
Note: SharePoint Farm Reporter expects the user account to have sufficient privileges to create, add to and delete database in the SQL
SharePoint Farm Reporter creates databases in SQL
Server as per the information outlined below:
- Quick Reports
Quick Reports feature in SharePoint Farm Reporter creates a single application database in the default data storage location used by the SQL
Server during application launch. SharePoint Farm Reporter uses the following
naming convention for its Quick Reports database:
For example: SharePoint Farm Reporter creates 'SharePointFarmReporterDATA-RD61' with data ('SharePointFarmReporterData-RD61.mdf') and log ('SharePointFarmReporterData-RD61_log.LDF') files stored in the default SQL data folder in the SQL server
(C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data).
- Power Reports
Power Reports feature in SharePoint Farm Reporter creates a table in the application database(ex:SharePointFarmReporterData-RD61) itself
for every run instance of the Power Reports (scheduled) task, either at
the scheduled time interval or when using Run Now option. SharePoint Farm Reporter uses the following name convention for its Power Reports
For Scheduled Run: <TABLE NAME><TASK NAME><yyyymmddhhmmss>
For Run Now: <TABlENAME><TASK NAME>RN<yyyymmddhhmmss>
For example, SharePoint Farm Reporter creates
Scheduled Run: 'ListPermissionsTableTaskName120081125120409'.
Run Now: 'ListPermissionsTableTaskNameRN20081125120409'.
SharePoint Farm Reporter does not delete the Quick Reports database. You
must delete the database manually after uninstalling SharePoint Farm Reporter application.
SharePoint Farm Reporter deletes the Power Reports tables after
generating the reports as specified by the user. You can also cleanup the SharePoint Farm Reporter tables corresponding to a task or a task history instance using the 'Remove all' or 'Remove' or 'Cleanup History'
options in SharePoint Farm Reporter main application.
Application Database Truncation
Some of the SharePoint Farm Reporter reports perform more transactions in its Quick
Reports database (as mentioned above). This action may result in
increase in its database file size, which can be reclaimed later by
using the Microsoft SQL Server Management Studio. Please refer to the
following MSDN articles to reclaim database disk space in SQL Server:
Shrinking a Database
How to: Shrink a Database (SQL Server Management Studio)
SharePoint Farm Reporter can truncate and shrink the application database related files based on the
settings provided in Application Database Truncation Settings.
To configure the settings, Go to Tools menu and click Application Database Truncation Settings .
This will display a dialog as shown below:
If the time interval configured in the settings dialog is, say 7
days, SharePoint Farm Reporter will perform the truncation and shrink process
every 7 days from the last truncation date.
Also refer SharePoint Farm Reporter Settings section.