SQL Server Settings 

SharePoint Farm Reporter uses SQL Server database for its data storage to generate SharePoint reports. 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 application databases.

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:

User Authentication

To connect to SQL Server, SharePoint Farm Reporter uses the relevant user accounts based on the authentication mode as listed below:

  1. Windows Authentication:
    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.
  2. SQL Authentication:
    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 server.

Database creation

SharePoint Farm Reporter creates databases in SQL Server as per the information outlined below:

  1. 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:

    SharePointFarmReporterDATA-<SERVER NAME>

    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).

  1. 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 database:

    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'.

    Database cleanup

    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
    http://msdn2.microsoft.com/en-us/library/ms189080.aspx

    How to: Shrink a Database (SQL Server Management Studio)
    http://msdn2.microsoft.com/en-us/library/ms189035.aspx

    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.