Saving Settings For SQL Sentry

How can I save my settings for SQL Sentry?

This is a common question that comes in to our support team. Because the settings for SQL Sentry are stored in a variety of tables throughout the database, our reply in almost all cases is to make sure you are regularly backing up the SQL Sentry database as you would do with any other SQL Server database.

There are some special cases where customers are storing large amounts of data in the database for extended periods of time. This can cause the database to become very large over time, making the backup process take more time than is acceptable to complete.

In these cases, we can move the largest tables (these will most likely be the tables that house the performance data being collected and stored) to their own filegroup. This will decrease the size of the Primary filegroup, allowing you to back up the settings stored there much faster. The remaining content in this post will provide you with instructions on how to do this.

We will want to query the SQL Sentry database (please note that the comments are the results of my queries):

Step 1: Before we get into the fun stuff, you will want to stop the SQL Sentry monitoring service(s). You can do this from the Services applet (services.msc) found in Control Panel > Administrative Tools:

Or by running net stop SQLSentryServer from an elevated command prompt (“Run as administrator”):

Step 2: Get the Event Source Connection ID for the server hosting the SQL Sentry database:

SELECT ID

  FROM dbo.EventSourceConnection

  WHERE ServerName = N'SSWS16.INTERCERVE.COM'; --EventSourceConnectionID = 3

Step 3: Get the ID for the SQL Sentry database:

SELECT DatabaseID
  FROM dbo.PerformanceAnalysisSqldatabase
  WHERE Name = N'SQLSentry'
  AND EventSourceConnectionID = 3; --DatabaseID = 7

Step 4: Get a list of the largest tables in the SQL Sentry database:

SELECT
  past.TableName,
  past.DatabaseID,
  pasip.UsedSpaceMB
FROM dbo.PerformanceAnalysisSqlTable AS past
INNER JOIN dbo.PerformanceAnalysisSqlIndexPartition AS pasip
  ON pasip.tableid = past.TableID
  WHERE past.DatabaseID = 7
  AND pasip.EventSourceConnectionID = 3
ORDER BY pasip.UsedSpaceMB DESC;

Step 5: Create a new filegroup:

ALTER DATABASE SQLSentry ADD FILEGROUP New_FG;

Step 6: Create a new file:

ALTER DATABASE SQLSentry ADD FILE 
(
  NAME = TestName, FILENAME = 'D:\SQLSentry.ndf', SIZE = 500 MB, 
  MAXSIZE = 5000 MB, FILEGROWTH = 1 MB
) 
TO FILEGROUP [New_FG];

Step 7: Drop the existing table and re-create it in the new filegroup:

CREATE CLUSTERED INDEX [IX_PerformanceAnalysisData_Wide]
   ON [dbo].[PerformanceAnalysisData]
   (
     [Timestamp] ASC, [PerformanceAnalysisCounterID] ASC, 
     [DeviceID] ASC, [EventSourceConnectionID] ASC
   )
   WITH (DROP_EXISTING = ON, ONLINE = OFF)
   ON [NEW_FG];

Step 8: The final step is to verify that the table now exists in the new filegroup:

SELECT OBJECT_NAME (IDX.object_id) AS [Object Name],
  IDX.name AS [Index Name],
  FG.name AS [FG Name]
FROM   sys.indexes AS IDX
INNER JOIN sys.filegroups AS FG
ON IDX.data_space_id = FG.data_space_id
WHERE IDX.object_id = OBJECT_ID (N'PerformanceAnalysisData');

You are now free to back up your Primary filegroup, which includes all of your settings for SQL Sentry, and restart the SQL Sentry monitoring service(s). This will allow you to restore your settings quickly, if you can, using a piecemeal restore to get just the Primary filegroup back online.

I hope you have enjoyed this post. As always, if you have any suggestions for new posts, please let me know in the comments below.

Until next time,
Jim

Jim Holden

Jim is a Technical Training Specialist at SentryOne. Jim develops training materials to help our customers realize the full potential of the SentryOne product suite.

Jim shares some “How To” information and other tidbits to help you get the most out of your SentryOne installation.