Voice of the DBA

Tag: Redgate

  • Monday Monitor Tips: Lots of Postgres Options

    Redgate Monitor is growing to include more than just Microsoft SQL Server monitoring. We added PostgreSQL support in 2023 and that continues to grow. This post looks at a few changes we’ve added.

    This is part of a series of posts on Redgate Monitor. Click to see the other posts

    PostgreSQL Monitoring

    When we added PostgreSQL support to Redgate Monitor, I assumed that we could monitor most types of PostgreSQL installs. I was very wrong. Apparently everyone that’s forked or used PostgreSQL in different ways has changed how a DBA might need to monitor the system. At first we monitored on-premises installs only, but that’s grown.

    AWS

    We now support RDS. If you search for bluebox, you’ll see a PostgreSQL RDS instance. If you click in the card, you can see the workload, and more specifics on this RDS database.

    2024-12_0220

    We also support Aurora, if you use that version. Search for pizza and these cards appear, the right one being Aurora. This is an AWS Aurora database, based on PostgreSQL.

    2024-12_0222

    Azure

    We also support Azure Flex PostgreSQL database. Search for the pgtips database and you’ll see this card.

    2024-12_0221

    If you’re in the cloud with AWS or Azure, we have some PostgreSQL monitoring available for you, with more coming all the time.

    Give Redgate Monitor a Try

    Redgate Monitor continues to change and grow. Look at our demo system at monitor.red-gate.com and click the What’s New in the upper right, where you’ll see all the new features, like the PostgreSQL support changes.

    2024-12_0218

    You can also see the release timeline for more specific features in each version.

    2024-12_0219

    Redgate Monitor is a world class monitoring solution for your database estate. Download a trial today and see how it can help you manage your estate more efficiently.

  • Monday Monitor Tips: CIS Compliant

    A new feature added to Redgate Monitor Enterprise automatically. CIS compliance is something many enterprises think about as their auditors use this as a benchmark.

    If you’ve never looked at the Center for Internet Security, you ought to glance at them, and check out the benchmarks they have for many systems.

    This is part of a series of posts on Redgate Monitor. Click to see the other posts

    The CIS SQL Server Benchmark

    You might get asked by an auditor how you know your SQL Server estate is secure. There are lots of things you can do, but an easy one is being CIS compliant. There are benchmarks from CIS for many SQL Server versions. You can download the benchmark from CIS as a PDF, go through it, and then start to compare that to your SQL Server instances.

    2024-12_0195

    That’s not complex, but it is complicated. Lots of moving parts, where do you keep the benchmark data, how do you compare it to your instances, how do you ensure it’s up to date or get notified if it’s not?

    This is a simple job, but labor intensive, boring, and tedious. There’s a better way.

    Redgate Monitor Compliance

    We’ve added a compliance section to Redgate Monitor, which I’ve written about in terms of looking for older versions. However, we also have added to this section with a CIS Benchmark template.

    At the top of Redgate Monitor, there is a Security section and Compliance is under this.

    2024-12_0196

    When I get to the compliance screen, on the right side, I have a drop down for the templates. We’ve pre-loaded the CIS Benchmark in here. I can select that to see how compliant I am.

    2024-12_0197

    In this case, I’ve filtered to the SSC servers and when I do that, I see that I’m mostly compliant, but just barely. I say me, but this is our IT group that manages the config.

    2024-12_0198

    There is a disclaimed at the top, which you should note. It links here, where the docs note that this is a template that cannot be deleted or changed. It can be duplicated. Note, this is only for SQL Server 2022.

    If I click a server, I see the details of where and where not I am compliant. In tis case, things like database mail ought to be disabled.

    2024-12_0200

    I can’t change things from here, but I can export this as a report and work on remediation. If I want to set a template that is like CIS, but I have a good reason for an exception, such as the Cost threshold for parallelism set to something different, I can duplicate this template and alter it.

    Summary

    Auditing and compliance are becoming more important at many organizations, especially in light of the main data breaches and other issues that many organizations have experienced. This might even be required by insurance companies who want to ensure that you have not left open configurations that might become attack vectors.

    If you haven’t tried the compliance templates in Redgate Monitor, give it a try, or have a play at monitor.red-gate.com..

    Redgate Monitor is a world class monitoring solution for your database estate. Download a trial today and see how it can help you manage your estate more efficiently.

  • Monday Monitor Tips: Am I Patched?

    One of the things that I think is neat is that Redgate Monitor helps you track patching on your systems. This is something that has been challenging in every position I’ve had, with some systems being forgotten or remaining unpatched for too long.

    This post looks at how you track patches and versions.

    This is part of a series of posts on Redgate Monitor. Click to see the other posts

    The Challenges of An Estate

    There are two aspects to tracking your systems: the version and the patch level. Microsoft releases versions periodically and unless you’re in the cloud with a PaaS service, you may or may not have just one version of your database platform. Azure SQL Database is evergreen and updates every quarter or so.

    If you install SQL Server 2016, unless you upgrade, it stays at 2016.

    The second part is the patch level. SQL Server 2016 has had 3 service packs, multiple CUs in between those, and a few post-SP3 security patches this year (2024). Are you up to date?

    It’s a good question since you might be vulnerable to issues, and you certainly can be out of compliance with auditors if you aren’t patched.

    What Systems Are Behind?

    The estate tab has a versions page, in which we list the installed versions of SQL Server. I hope PostgreSQL is coming soon as well (and others). Here is the overview, where you can see this estate spans SQL Server 2008 R2 to 2022, and includes the cloud.

    2024-11_0251

    This helps me with upgrades, as I can see which systems might be old and in need of an upgrade plan. I can filter at the top for different groups, tags, etc., but I can see what I have, and I get quick links to the current patch.

    Below this, I have details. Here is where I can dive down to individual groups and systems to see if they are patched, how long ago the last patch was released, and the end of support

    2024-11_0252

    This is a good way for me to see at a glance how patched I am. The yellow up arrows mean I need to patch. The green check mark means I am patched.

    This is a good view so you can tell how out of date you are. It’s one thing if there are patches released within the last month and not applied. It’s another thing when you have systems that are months or years out of date.

    Using This Data

    This isn’t something I’d check every day or week, but I would set reminders to have this monitored monthly and have plans in place to get patched. While lots of patches might not affect security, they often to affect support if you need it, and certainly these affect compliance and auditing.

    Even if no one audits you, if you have an issue and you aren’t patched, someone will use that as an excuse to blame you in some way. Get patched, at least within 60 days if not 30.

    BTW, this data is maintained by Redgate and updated as patches are released. Redgate Monitor downloads a file that populates the latest patches for each version. If your Redgate Monitor Base Monitor cannot reach the Internet, you can update this yourself by downloading this file and copying to your system.

    Summary

    This short posted highlighted what data you get about versions and patches, and my recommendation, which is to review this monthly.

    Having Redgate Monitor keep all this for you is nice and helps you keep a healthy, up-to-date estate.

    Redgate Monitor is a world class monitoring solution for your database estate. Download a trial today and see how it can help you manage your estate more efficiently.

  • Adding Test data to msdb.sysjobhistory

    One of our internal people was looking to test some process in (I assume) Redgate Monitor and needed more job history than they had in msdb.sysjobhistory. They wanted to use SQL Data Generator to help, but couldn’t make it work.

    This gives the solution I sketched for them. It can work with some other system tables, but not all. Many system tables do not allow user data to be inserted. Some do.

    The Main Problem

    The main problem here is that SQL Data Generator doesn’t see system tables. If I open a project in msdb, I see this:

    2024-11_0297

    That matches what I see in SSMS. Only user tables.

    2024-11_0296

    However, I know I can do this and it works.

    INSERT INTO dbo.sysjobhistory
      (job_id, step_id, step_name, sql_message_id, sql_severity, message, run_status, run_date, run_time, run_duration, operator_id_emailed, operator_id_netsent, operator_id_paged, retries_attempted, server)
    VALUES
      ('EA6B3BC3-D358-4B0C-A793-8C3C558098AB', 0, 'mystep', 0, 0, 'Executed as user: NT Service\SQLAgent$SQL2022. The job script encountered the following errors.'
      , 0, 20241111, 175438, 0, 0, 0, 0, 0, 'ARISTOTLE\SQL2022')

    Now, how to get SQL Data Generator to help?

    A Little System Table ETL

    Since I know I can insert data into the table, how can I generate data? Apparently SQL Data Generator cannot read these tables, but I can use a trick that I’ve used in the past.

    First, I’ll run this in msdb:

    SELECT *
      INTO mysysjobhistory
      FROM dbo.sysjobhistory AS s
      WHERE 1 = 0

    This code will make a copy of dbo.sysjobhistory with no data. However, this is a user table. Once I do this, now I can refresh SQL Data Generator and I can see my table.

    2024-11_0094

    Now I can use the settings to get the type of data I want. Here’s a preview of some data I set, using the data in my existing table.

    2024-11_0095

    Now, I can click generate data and I have data added to my user table. If I query this table, I see data:

    2024-11_0096

    The last step is to move this data. I’ll use this query:

    INSERT dbo.sysjobhistory
       (job_id, step_id, step_name, sql_message_id, sql_severity, message, run_status, run_date, run_time, run_duration, operator_id_emailed, operator_id_netsent, operator_id_paged, retries_attempted, server)
    
SELECT job_id, step_id, step_name, sql_message_id, sql_severity, message, run_status, run_date, run_time, run_duration, operator_id_emailed, operator_id_netsent, operator_id_paged, retries_attempted, server
      FROM dbo.mysysjobhistory AS m

    Once this runs, I can see the data in msdb.

    2024-11_0097

    Of course, I’d also have to populate sysjobs if I wanted this linked to a job and shown in the Agent Job History Viewer.

    Summary

    This post showed how I’ve sometimes worked in situations where I couldn’t directly access a table from an application. In this case, I want to get data into sysjobhistory, but SQL Data Generator doesn’t support that directly. My solution:

    1. make a copy of the table
    2. insert data into the copy
    3. move the data into the original

    This has worked in a few situations as well where I might need to move/stage data before it gets into an application table. In this case, we wanted to generate some random history for Redgate Monitor to read.

    This can work for other tables as well, as long as you can insert..select into them.

    SQL Data Generator is a neat tool to generate data quickly for a variety of purposes in SQL Server. Give it a try, especially if you already have the Toolbelt Essentials.