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:
Step 3: Get the ID for the SQL Sentry database:
Step 4: Get a list of the largest tables in the SQL Sentry database:
Step 5: Create a new filegroup:
Step 6: Create a new file:
Step 7: Drop the existing table and re-create it in the new filegroup:
Step 8: The final step is to verify that the table now exists in the new filegroup: