When running Movere, there are five categories of SQL Server data.
- List of databases (including state, size, creation date), clustering, mirroring and availability groups (master db),
- SQL Server performance and connection information including CPU usage per min by instance, CPU usage by DB, and DB connections,
- Log shipping details (msdb db),
- SQL Server-based data e.g. SCCM; and
- SQL DB and drive statistics.
Items 1 and 2 are the easiest to gather and do NOT require any special service account creation for Movere to use. Movere can collect these items by leveraging the ‘NT AUTHORITY\SYSTEM’ account, which it will do by default. This account must be enabled for login and granted permission to connect to the database engine, which is the default setting. The only server role it requires is ‘public’, which is also enabled by default. Now this is where it gets interesting. For SQL 2008R2 and prior, the ‘NT AUTHORITY\SYSTEM’ was given ‘sysadmin’ privileges by default. Microsoft removed ‘sysadmin’ privileges from this account beginning with SQL2012, but as I mentioned above, this account is given ‘public’ by default across all versions. If the customer disables this account, then the database state will come through on the Movere website as UNCONFIRMED.
Here are the results from a scan run with the ‘NT AUTHORITY\SYSTEM’ account disabled:
Here are the results when run with ‘NT AUTHORITY\SYSTEM’ enabled, with the ‘public’ server role only:
As you can see, we can now see each databases state and creation date.
This brings us to item 3, log shipping. This ONLY impacts log shipping, the other SQL HA features can be extracted with the settings above, but to get log shipping details, the ‘NT AUTHORITY\SYSTEM’ account must be manually given ‘db_datareader’ access to the ‘msdb’ db if the only server role it has is ‘public’. Alternatively, you can grant the ‘sysadmin’ server role to the ‘NT AUTHORITY\SYSTEM’ account and Movere will then be able to gather log shipping details, if log shipping is used on that server of course. Alternatively, a service account created by the customer for Movere to use can be added to the SQL Server and given ‘db_datareader’ access to the ‘msdb’ db. Most customers wouldn’t bother with this as it’s easier to just give the existing ‘NT AUTHORITY\SYSTEM’ account ‘db_datareader’ access to the ‘msdb’ database, but if the SQL Server is housing a secondary data source that they need Movere to collect, then a service account can be added and given ‘db_datareader’ access to both the ‘msdb’ database and the secondary database. NOTE: The service account only requires the ‘public’ server role. Here’s what that would look like for account ‘IO\sqltest’:
Giving a service account these settings would allow Movere to gather both log shipping settings AND extract SCCM data from the SCCM database ‘CM_ULI’, in addition to SQL items 1 and 2.
The last category (5) is the SQL DB and drive statistics. This level of detail requires ‘sysadmin’ permissions, which is why we turned SQL ARC’ing off by default, as this category would generate an error message on each ARC beat for these data points if they were using an account that did not have ‘sysadmin’ privileges. If you need this data then grant ‘sysadmin’ access to either the ‘NT AUTHORITY\SYSTEM’ account OR give it to the service account you want Movere to use:
Below you will see the difference in data returned when you give Movere ‘sysadmin’ access versus when you only give it ‘public’ only access.
SCCMSQL.io.priv was given NT AUTHORITY\SYSTEM ‘public’ only while ALTSQL.io.priv was given NT AUTHORITY\SYSTEM ‘sysadmin’ access.
SQL Server CPU instance level data is returned from both servers:
SQL CPU database level data is also returned from both servers:
SQL disk utilization is only returned for the ALTSQL server (scanned using ‘sysadmin’ access):
SQL Server database connections are returned from both servers:
SQL Server DB read/write data is only returned for ALTSQL:
SQL Server DB Used vs. Total is only returned for ALTSQL:
As you can see, the only thing you lose by only using ‘public’ access is the disk level detail from SQL Server.
REMEMBER: Movere turns CollectSQL off by DEFAULT. You need to set this option to ‘true’ in both the ARC2 and ARC4 configuration files to gather SQL Server data performance data during an ARC scan: