SQL SERVER DBA, Linux and Azure

SQL DBA Checklist/Activities

 SQL DBA Checklist/Activities

Daily Checklist

1) Backups

2) SQL Server Error Logs

3) SQL Server Agent Jobs

4) HA or DR Logs

5) Shift Handover (New/Pending Tickets)

6) Implementing planned Change Tickets

Weekly Checklist

1) Integrity Checks (DBCC CHECKDB)

2) Index Maintenance

3) Updation of Statistics

4) Cycle SQL Server Error Logs

5) Reporting of Tickets Handled

6) Planning of Change Tickets

7) If any Weekly Meetings, then Prepare MOM (Minutes of Meeting).

8) Attending CAB Meetings and taking approvals for Changes.

Monthly Checklist

1) Backup Validation Test.

2) Capacity Planning: Disk, CPU, and Memory.

3) Plan if any Security Patches or Critical Hotfixes are released.

4) Report on overall Uptime/Downtime

Quarter Checklist

1) DR Test

2) Check who all have SYSADMIN permission and remove unwanted logins having this permission.

3) SOX Audit Standard

Yearly Checklist

1) Licensing Validation (if it is Volume Based Licensing)

2) Service Pack validation and applying newly released SPs.




Getting Database Backup History In SQL Server

--Getting Database Backup History In SQL Server 

SELECT

    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.server_name,
    bs.user_name,
    bs.type,
    bm.physical_device_name
FROM msdb.dbo.backupset AS bs
INNER JOIN msdb.dbo.backupmediafamily AS bm on bs.media_set_id = bm.media_set_id

New in SQL Server 2022 – Generate_Series

One of the new language features added in SQL Server 2022 is the GENERATE_SERIES function. This allows you to generate a

SELECT * FROM GENERATE_SERIES(start=1, stop=7)

This gives me a simple sequence of numbers in a result set, with the column header, value.



Change data capture in onprem and Azure sql database

CDC in Azure SQL Databases offers a similar functionality to SQL Server and Azure SQL Managed Instance CDC.

However, on Azure SQL Databases, CDC provides a scheduler which automatically runs the capture and cleanup processes, which are run as SQL Server Agent jobs on SQL Server and on Azure SQL Managed Instance.

Limitations for CDC in Azure SQL Databases  

In Azure SQL Databases, the following tiers within the DTU model are not supported for Change Data Capture: Basic, Standard (S0, S1, S2). If you want to downgrade a Change Data Capture-enabled database to an unsupported tier, you must first disable Change Data Capture on the database and then downgrade. 

Running point-in-time-restore (PITR) on an Azure SQL Database that has Change Data Capture enabled will not preserve the Change Data Capture artifacts (e.g. system tables). After PITR, those artifacts will not be available. 

If you create an Azure SQL Database as an AAD user and enable Change Data Capture on it, a SQL user (e.g. even sys admin role) will not be able to disable/make changes to Change Data Capture artifacts. However, another AAD user will be able to enable/disable Change Data Capture on the same database.

DIFFERENCES BETWEEN SQL SERVER CLUSTERED INDEX SCAN AND INDEX SEEK

Index scan means it retrieves all the rows from the table and index seek means it retrieves selective rows from the table.


INDEX SCAN:

Index Scan touches every row in the table it is qualified or not, the cost is proportional to the total number of rows in the table.

Thus, a scan is an efficient strategy if the table is small or most of the rows qualify for the predicate.

INDEX SEEK:

Index Seek only touches rows that qualify and pages that contain these qualifying rows.

The cost is proportional to the number of qualifying rows and pages rather than the total number of rows in the table.

They are two types of Indexes are there:

1. Clustered Index.

2. Non Clustered Index.

Clustered Index:

A non-clustered index can consist of one or more columns, but the data storage is not dependent on this create index statement as is the case with the clustered index.

For a table without a clustered index, which is called a heap, the non-clustered index points the row (data).

In the circumstance where the table has a clustered index, then the non-clustered index points to the clustered index for the row (data).

Although many implementations only have a single column for the clustered index, in reality a clustered index can have multiple columns.

Just be careful to select the correct columns based on how the data is used. The number of columns in the clustered (or non-clustered) index can have significant performance implications with heavy INSERT, UPDATE and DELETE activity in your database.