Tag Archives: #DatabaseRecovery

Standard

Posted by

Posted on

January 16, 2024

Posted under

Query Store

Comments

Leave a comment

Understanding Query Store Data Retention in SQL Server

Introduction

In the ever-evolving landscape of database management, one crucial aspect is performance monitoring. SQL Server provides a powerful tool called the Query Store, which allows database administrators to capture and analyze query performance over time. One key metric to consider is the duration for which the Query Store retains data. In this blog post, we will explore how to determine the number of days the Query Store has data for a specific database.

The Query Store: An Overview

The Query Store in SQL Server is a feature designed to simplify performance troubleshooting by persisting query execution plan information. It captures a wealth of data, including execution plans, runtime statistics, and wait statistics. However, it’s essential to be aware of how long this valuable information is retained, as it influences historical analysis and trend identification.

Querying the Query Store

To ascertain the duration for which the Query Store retains data, we can use a simple T-SQL query. Let’s break down the script:

DECLARE @current_date DATE = GETDATE();

DECLARE @min_date DATE = (SELECT MIN(last_execution_time) FROM sys.query_store_runtime_stats);

DECLARE @days INT = DATEDIFF(DAY, @min_date, @current_date);

PRINT 'The Query Store has data for ' + CAST(@days AS VARCHAR) + ' days.';

Interpreting the Result

When you execute this script, you’ll get a clear output stating the number of days the Query Store has data. This information is crucial for understanding the historical context of your query performance.

Practical Use Cases

Understanding the retention period of the Query Store can be beneficial in various scenarios:

  • Performance Analysis: Evaluate query performance trends over time.
  • Troubleshooting: Investigate issues by comparing current performance with historical data.
  • Capacity Planning: Plan resources based on long-term query behavior.

Conclusion

In conclusion, the Query Store is a valuable tool for database administrators, and knowing the retention period of its data is essential for effective performance analysis. The provided T-SQL script allows you to quickly determine the number of days the Query Store has data, empowering you to make informed decisions about your database performance strategy.

Stay tuned for more insights into SQL Server and database management best practices!

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Standard

Posted by

Posted on

January 9, 2024

Posted under

AlwaysON, Tsql

Comments

Leave a comment

Querying Connect Permissions on Endpoints in SQL Server

Introduction 🚀

Securing your SQL Server environment involves a meticulous examination of permissions granted to various entities. In this blog post, we’ll explore a T-SQL script designed to query if endpoints have been granted connect permissions. Understanding and managing these permissions is crucial for maintaining a secure and well-configured SQL Server infrastructure.

Requirements 🛠️

Before using the T-SQL script, make sure you have the following prerequisites:

  1. SQL Server Endpoints: The script is designed for environments with configured endpoints.
  2. Permissions: The account executing the script should have sufficient permissions to query the necessary system views (sys.server_permissions, sys.server_principals, and sys.tcp_endpoints).

T-SQL Script 📜

Use the following T-SQL script to determine if endpoints have connect permissions:

SELECT

    perm.class_desc,

    prin.name,

    perm.permission_name,

    perm.state_desc,

    prin.type_desc as PrincipalType,

    prin.is_disabled

FROM

    sys.server_permissions perm

LEFT JOIN

    sys.server_principals prin ON perm.grantee_principal_id = prin.principal_id

LEFT JOIN

    sys.tcp_endpoints tep ON perm.major_id = tep.endpoint_id

WHERE

    perm.class_desc = 'ENDPOINT'

    AND perm.permission_name = 'CONNECT'

    AND tep.type = 4;

This script retrieves information about connect permissions on endpoints, including details about the principal, permission state, and principal type.

Conclusion 🎉

Checking connect permissions on SQL Server endpoints is a critical step in ensuring a secure database environment. By utilizing the provided T-SQL script, database administrators can easily identify whether connect permissions have been granted to specific entities.

Regularly auditing and managing these permissions is essential for maintaining the integrity of your SQL Server infrastructure. May your endpoints always be secure, and your data remain protected! 🔒

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Standard

Posted by

Posted on

January 2, 2024

Posted under

AlwaysON, Tsql

Comments

Leave a comment

Identifying Login Permissions on Always On Endpoint with T-SQL

Introduction 🚀

Security is paramount in a SQL Server environment, especially when dealing with high availability solutions like Always On Availability Groups. In this blog post, we’ll explore a T-SQL script designed to identify which logins have been granted permissions on Always On endpoints. Understanding these permissions is crucial for maintaining a secure and well-configured SQL Server infrastructure.

Requirements 🛠️

Before using the T-SQL script, ensure that the following requirements are met:

  1. SQL Server Always On: The script is designed for environments with configured Always On Availability Groups.
  2. Permissions: The account executing the script should have sufficient permissions to query the necessary system views (sys.server_permissions and sys.endpoints).

T-SQL Script 📜

Use the following T-SQL script to identify login permissions on Always On endpoints:

-- Metadata Check

SELECT 'Metadata Check';



SELECT

    EP.name,

    SP.STATE,

    CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id)) AS GRANTOR,

    SP.TYPE AS PERMISSION,

    CONVERT(nvarchar(46), suser_name(SP.grantee_principal_id)) AS GRANTEE

FROM

    sys.server_permissions SP, sys.endpoints EP

WHERE

    SP.major_id = EP.endpoint_id

ORDER BY

    PERMISSION, GRANTOR, GRANTEE;

This script retrieves information about the state, grantor, and grantee of permissions on endpoints related to Always On Availability Groups.

Conclusion 🎉

Ensuring the correct permissions on Always On endpoints is a critical aspect of securing your SQL Server environment. By running the provided T-SQL script, database administrators can quickly identify which logins have been granted permissions, allowing for a comprehensive security review.

Regularly review and audit these permissions to maintain a secure configuration, especially in environments where access control is crucial. May your SQL Server environment always be fortified against unauthorized access! 🔒

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.