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,