SQL Server PI repository FAQ (Frequently Asked Questions) (4371543)

Frequently asked questions about the SQL Server PI repository release.

Q: What are the sizing requirements?

A: Sizing details are available in KB 4229792.  

Q: What type of training is available?

A: KB 4230783 details how to migrate database agents to a SQL Server repository from an Infobright PI repository. Please contact Quest for additional training information.

Q: What versions of SQL Server are required?

A: The customer will require SQL Server 2016 SP1 for Windows or higher for the MSSQL PI repository running on Windows 2016. A database must be created and available prior to the installation or conversion process. Quest does not have a licensing arrangement to use SQL Server on behalf of the customers. 

Q: Can a SQL Server instance on Linux be used for the SQL Server PI repository?

A: No, the SQL Server instance used for the SQL Server PI repository must be on Windows 2016 or higher. Support for a Linux based SQL Server PI repository is planned for an upcoming release of the SQL Server and Oracle cartridges.

Q: What general release cartridge versions includes the new SQL Server PI repository functionality?

A: Beginning with the 5.9.5.20 release PI-capable database cartridges use a SQL Server-based PI repository. Infobright/PostgreSQL based PI repository have been out of Support since version 5.9.5.10. 

Q: Is the Visual C++ 2010 redistributable required

A: Visual C++ was used for the installation of the Infobright software. This component is no longer used since the PI agents now make a direct connection to a SQL Server instance.

Q: Are different versions of the DLP executables required for mixed environments?

A: The brighthouse/Infobright components are not used in design. Mixed environment issues for Linux/Windows for the DLP processor are not applicable.

Q: Is port 5029 still required to connect between the SQL PI repository and the FMS/FglAM?

A: The SQL PI repository will use a SQL Server based repository. The port requirements will depend on the configured SQL Server port.

Q: Are VM CPU and memory reservations required?

A: Internal testing has determined the VM CPU and memory reservations are not a requirement, but memory reservations are highly recommended for SQL Server by Microsoft. The CPU reservations for Infobright SQL PI were largely required due to PostgreSQL requirements.

Q: Are antivirus exclusions still required?

A: General Foglight FMS and Foglight antivirus exclusions are still required. Antivirus exclusions applicable to SQL Server as per Microsoft recommendations are suggested. 

Note: Some Antivirus software (e.g. Crowdstrike) may prevent old log files from being deleted from the FMS and FglAM servers. Administrators may need monitor space on the server and manually delete old files as necessary.

Q: Can a Windows domain account be used to connect to a MSSQL PI instance?

A: Both a SQL Server account and a Windows (Active Directory) account is supported.

Q: Are specific full control or 744 permissions required on the PI repository server for the SQL Server PI to collect more than four hours of data when a named service account is used?

A: This was a specific issue with the Infobright installation. These additional permissions not necessary for a SQL Server based installation.

Q: Can agents of different database domains (e.g. SQL Server, Oracle) be monitored using the same FMS if the agents refer to different FglAMs and different PI repositories depending on the agent type?

A: SQL Server, Oracle, MySQL, Azure SQL, and SSAS agents are supported using the same SQL Server PI repository instance. 

Q: Does a SSAS agent require a matching SQL Server agent on the same FMS/PI repository?

A: No, the SSAS agent is independent of the SQL Server agent.

Q: Why was SQL Server chosen as the MSSQL PI repository?

A: Several reasons have been discussed

• Most of the Foglight customers use SQL Server so they are experienced in managing this type of system.
• A PostgreSQL database would have the same VM reservation issues that we had with Infobright. The AV exclusions and CPU reservation requirements were mostly because of the PostgreSQL
  • SQL Server Express is not an option because has a maximum DB size of 10GB

Q: Can existing SQL PI licenses be used with MSSQL PI?

A: Existing customer licenses for SQL PI can be used with the SQL Server repository installation. Foglight licenses are based on the cartridge name. The SQL PI cartridges (SPIRepository) and agent type names are not changing in the new design. 

Q: Will historical PI data from Infobright be available to the new MSSQL PI?

A: Beginning with the 5.9.5.20 cartridges, a migration utility for data from Infobright to SQL Server is available in KB 4230193.

Q: What type of authentication is supported for connections to the SQL Server based PI repository?

A: Both SQL Server and Windows (AD) authentication are supported for the connection the SQL Server PI repository.

Q: Can Oracle and SQL Server agents share the same SQL PI repository database?

A: SQL Server, Oracle, and SSAS agents cannot store data in the same SQL Server PI Repository database. Each type of agent must use their own unique database. 
 

Q: How many database agents can be monitored by a SQL Server database?

A: There is a limit of 300 monitored agents per SQL Server PI Repository database. 


Q: Can a SQL Server instance contain two SQL PI Repository databases for the same domain?

A: Yes, a SQL Server instance can contain two SQL PI Repository databases for the same domain. For example, a single SQL Server instances could have two SQL Server PI Repository databases, each with up to 300 instances, giving the ability for 600 SQL Server instances to be monitored using that one SQL Server instance.  

Q: Can multiple SQL Server instances (each for a different SQL Server PI repository) be installed on the same server?

A: Server Level Consolidation in which the customer can has 2 PI repository instances on the same machine is supported, but we recommend that they use different physical storage, and also need to set Maximum SQL Server Memory according to the PI Sizing Guide for each instance.

Q: Is there a limit to the number of FglAMs connecting to a SQL Server PI repository?

A: The FglAM limitation has been removed (3 FglAM to one PI repository). One large customer is already using local monitoring, about 100 Foglight Agent Managers to one PI repository. Quest does have a repository limitation in the FMS.

Q: As there is a limit of 600 agents for a single MSSQL Server SQL PI instance, even though it would need to be an incredibly beefy host, could you have two SQL PI instances on the same server?

A: Yes, multiple SQL Server instances can be installed on the same server with each instance handling up to 600 database agents (Oracle, SQL Server, SSAS, and/or MySQL).

Q: Can a FMS repository and SQL Server PI repository share the same SQL Server database.

A: No, the SQL Server PI repository and FMS repository data must use different SQL Server databases. If the sizing requirements are sufficient for both databases then placing the FMS repository database and SQL Server PI repository databases on the same SQL Server Instance is supported.

Q: Can a FMS repository and SQL Server PI repository share the same SQL Server instance.

A: Yes, if the sizing requirements are sufficient for both databases then placing the FMS repository database and SQL Server PI repository databases on the same SQL Server Instance is supported.

Q: Can the SQL PI migration tool be run locally from the server where the Infobright SQL PI database is installed?

A: R&D does not recommend running the SQL PI migration tool locally on the Infobright server due to performance concerns. The location server where the SQL PI migration tool is run should have a bidirectional port open for 5029 to the Infobright server and also a port open bidirectionally to the SQL Server instance (default is 1433). For this reason, it may be preferable to run the SQL Server PI migration tool locally on the new SQL Server instance server.

Q: Can't a user just add more CPU and RAM at a single instance and that single instance be able to handle a higher number of agents?

A: SQL PI has a code based limit preventing more than 300 database agents per SQL Server PI repository database.

Quest R&D has done extensive testing with 600 agents per instance over many months both with performance and the number of connections handled. Going beyond 600 agents in an instances would be an untested scenario. In regards to potential bottlenecks, this could be a combination of CPU and memory. Each SQL Server instance has dedicated access to it's own memory, so having a single repository on separate instances, gives each repository it's own dedicated access to a block of memory. If there were two databases on the same instance, each database would have to compete for the same memory space.

Q: Will using multiple SQL Server instances require separate licenses for each instance?

A: SQL Server itself is licensed per core, so multiple instances on the same host would not affect their licensing with Microsoft.

Q: Can multiple PI repositories be used with a single FglAM?

A: Each FglAM can only connect to a single PI repository. The DB_SQL_Server_SPI_Repository, DB_Oracle_SPI_Repository, DB_Azure_SPI_Repository, DB_MySQL_SPI_Repository, and SSAS_SPI_Repository agents on each FglAM include ASP (agent status properties) specific with connection information to a single SQL Server Instance (and databases).

There can only be one PI Repository agent (e.g. DBSS-SPIRepository) managing a PI repository database, or there would be duplicate database manager.

When adding PI to an agent on the monitoring agent manager, it will create the PI repository there and become the SQL PI agent manager.

When adding PI to an agent on a different monitoring agent manager, if cannot create a second PI repository agent, so the first SQL PI agent manager must be chosen. 

The monitoring agent manager and the SQL PI agent manager do not have to be the same machine. You cannot have multiple agent managers connecting to the same repository databases, but they can create a new repository database in the same SQL instance and use it. They can be spread across three different FglAMs. They all connect to one PI repository agent on only one FglAM. That one PI Repository agent connected (by itself) to the PI database.

Q: Can Infobright and SQL Server repositories be located on the same server?

A: No. Only a single SQL PI repository (either Infobright or SQL Server) can be located on a single server system.

Q: Can multiple FglAMs connect to a single SQL Server PI repository?

A: Multiple FglAMs can connect to a single SQL Server PI repository. Quest recommends up to three (3) FglAMs reporting to a single PI repository for connection management reasons.

Q: Can a single SQL Server PI repository be shared between different FMS environments.

A: A SQL Server PI repository cannot be shared between different FMS environments because the PI data would be rolled up multiple times causing the data to become inaccurate.

Q: A FMS has multiple Infobright SQL PI repositories, each of which have less than 100 instances. The user wants to retain the data and minimize the number of SQL Server instances. Is it possible to migrate the history from three Infobright PI repositories into a single SQL Server PI repository?
Could the migration utility be run three times and point to the same SQL Server PI repository each time?

A: Migrating the contents from multiple Infobright PI repository into one SQL Server PI repository is supported with the Migration Tool 2.1.2 and higher. Each Infobright PI repository uses the same instance key (i.e. 1-100). When migrating the Infobright data to a SQL Server repository, Foglight has to manage the instance mapping, otherwise the data will be merged by the instance key.

A example consolidation recently undertaken was

Infobright server 1 - 20 DB agents
Infobright server 2 - 50 DB agents
Infobright server 3 - 100 DB agents
Infobright server 4 - 30 DB agents

In the PI migration utility, the application could be run four times
For each iteration, the source would be the hostname, port, and user logins for the Infobright server
The target would be the same each time, the connection details for the new SQL Server repository. The databases would be precreated by temporarily adding a new agent.

Q: After upgrading to the 5.9.5.20 (or higher) cartridge, will Infobright continue to run as an extension to the SQL Server and Oracle database agents already configured?

A: The upgrade wizard will automatically disable the Infobright PI extension for existing database agents that were upgrade from cartridge versions below 5.9.5.20. The following steps will be done by the upgrade wizard when upgrading.

• Disable Infobright PI monitoring for SQL Server agents
• Disable Infobright PI monitoring for Oracle agents
• Pause the PI Engine for SSAS agents
• Disable PI for SQL PI agents

Q: How is the data migration handled in situations where there are already more than 300 database agents using an Infobright SQL PI repository and it will be necessary to split the data set among two SQL Server PI repositories?

A: The PI migration utility includes a dialog box to select among the database agent instances in the source database to choose those agents whose data will be migrated. Please refer to KB 4230193 for details on using the SQL Server PI repository migration tool.

Q: Once the cartridges have been upgraded to a newer release (such as 5.9.5.20) can the cartridges be rolled back to an older release and Infobright be reinstalled?

A: Due to the significant number of topology changes. The database cartridges should not be rolled back to an older release. The SQL Server PI repository cannot be replaced with an Infobright based PI repository.

Q: Where is the data stored in the SQL Server PI repository (for backup purposes)?

A: All of the tables should be backed up in the database, they all contain data for SQL PI.

• The ldf and mdf file names will have the database names set in the first field of the popup that appears where the PI repository database is created in the installer.
• The primary filegroup (mdf) and transaction log can also optionally be defined in the same popup be in the second location, or will have been defined when the instances was created.
• The RAW short term data is located in the fourth field in the popup.

Q: Is the SQL Server PI repository supported on a SQL Server Always On Availability Group?

A: Yes. For details on installing a SQL Server PI repository on a SQL Server Always On Availability Group please refer to KB 4226824.

Q: Will Support perform the cartridge upgrade and migration from Infobright to the SQL Server PI repository?

A: Performing a walk-through and hands-on upgrade of database cartridges and migration of data are out of scope for Support.
Quest Professional Services can assist with these tasks on a fee basis. If a user encounters an issue while upgrading or migrating the data, please contact Support for assistance in the resolving the issue.

Q: Can the migration tool be used to migrate PI repository data from Infobright to a SQL Server repository on a different or new FMS?

A: No, the PI migration utility tool is only supported being used in the upgrade process on a single FMS.


Q: Is the SPIRepository agent still used in Foglight?

A: The SPIRepository agent has been deprecated since Foglight 6.0.0 and can be removed. The SPIRepository cartridge in the FMS continues to be used and the version appropriate for the cartridges should be enabled.