Frequently asked questions about the SQL Server PI repository release.
Q: What are the sizing requirements?
A: Sizing details are available in the Foglight for Databases Deployment Guide available for download from the Technical Documentation area of Supportlink.
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 port is used to connect between the SQL PI repository and the FMS/FglAM?
A: The SQL PI repository uses a SQL Server based repository. The port requirements depends 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 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: 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, MySQL, PostgreSQL, 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
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, Azure SQL, PostgreSQL, MySQL 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 SQL 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, Azure SQL, PostgreSQL, 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'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_PostgreSQL_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 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: 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?
A: Due to the significant number of topology changes. The database cartridges should not be rolled back to an older release.
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.
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: 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.
