SQL Server 2022 new features
The new features in SQL Server 2022 include:
Parameter Sensitive Plan Optimization
Query Store enhancements
Link to Azure SQL Managed Instance
Contained Availability Groups
Azure Synapse Link for SQL
Multi-Write Replication
Azure Active Directory authentication
Azure Purview integration
SQL Server Ledger
AWS S3 storage integration
Page Life Expectancy (PLE) in SQL Server:
Page Life Expectancy is the number of seconds a page will stay in the buffer pool without references.
If your page stays for more time in the buffer pool your PLE is high which leads to high performance. Every time request comes there are more chances that it may find its data in the cache itself, instead of going to the hard drive to read the data.
PLE measures in seconds.
PLE is one of the performance counters of SQL Server.
Higher the PLE, performance of SQL Server is good.
As per recommendation, the value of PLE counter is around 300 seconds.
You can check this value for your SQL server using below DMV.
SELECT object_name, counter_name, cntr_value from sys.dm_os_performance_counters WHERE [object_name] LIKE '%Buffer Manger%' AND [counter_name] = 'page life expectancy'Buffer Cache Hit Ratio
Buffer Cache Hit Ratio indicates the percentage of pages found in the buffer cache without having to read from the disk.
The ration is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses.
Buffer Cache Hit Ratio is one of the performance counters of SQL server.
You can increase the buffer cache hit ratio by increasing the amount of memory available to SQL Server or by using the buffer pool extension feature.
You can check this value for your SQL server using below DMV.
SELECT object_name, counter_name, cntr_value from sys.dm_os_performance_counters WHERE [object_name] LIKE '%Buffer Manger%' AND [counter_name] = 'Buffer cache hit ratio'A Database Administrator (DBA) plays a crucial role in managing, configuring, and securing databases within an organization. They handle a range of responsibilities from installation to maintenance, ensuring that the database systems are efficient, secure, and reliable.
Installing and Upgrading Database Tools: Setting up and upgrading database server tools to ensure the latest features and security patches are applied.
Planning Storage Requirements: Assessing and planning for future storage needs to accommodate database growth.
Modifying Database Structure: Making necessary changes to the database structure based on requirements provided by application developers.
User Management and Security: Enrolling users, managing system security, and ensuring compliance with database vendor license agreements.
Access Control: Monitoring and controlling user access to the databases to maintain data security.
Performance Monitoring and Optimization: Regularly monitoring database performance and making optimizations to enhance efficiency.
Backup and Recovery Planning: Developing strategies for backup and recovery to ensure data integrity and availability.
Data Archiving: Implementing data archiving strategies to manage historical data efficiently.
Database Backup and Restoration: Performing regular backups and restoring databases as needed.
Vendor Support: Contacting database vendors for technical support and troubleshooting issues.
Report Generation: Generating reports by querying the database as required.
Disaster Recovery Testing: Participating in disaster recovery tests to prepare for potential database failures.
Collaboration: Working closely with other teams such as Network Operations and Monitoring Teams to ensure seamless database operations.
Communication Skills: Excellent communication skills are essential for interacting with various teams and stakeholders.
Database Theory: Strong understanding of database theory and principles.
Database Design: Knowledge of both logical and physical database design.
RDBMS Knowledge: Proficiency in Relational Database Management Systems (RDBMS).
SQL Expertise: Skills in SQL, including Transact-SQL, for querying and managing databases.
Distributed Computing: Understanding of distributed computing architectures.
Operating Systems: Familiarity with the underlying operating system that supports the database.
Storage Technologies: Knowledge of storage technologies, including memory management, disk arrays, NAS/SAN, and networking.
Maintenance and Recovery: Expertise in routine maintenance, recovery procedures, and handling database failovers.
Production Support DBAs: Focused on the physical aspects of database management, including installation, configuration, patching, backups, restores, and disaster recovery.
Development DBAs: Specialize in the logical and development aspects, such as data model design, SQL writing, performance tuning, and pre-production activities.
Application DBAs: Typically work with third-party application software like ERP and CRM systems to manage and optimize databases.
Hybrid DBAs: Versatile professionals who perform a wide range of tasks and may evolve into architectural roles within the enterprise.
As the field of database administration continues to evolve, DBAs are expected to adapt and specialize in various aspects of database management, contributing to their growth and career advancement.
Now the DBA can have peace of mind by giving access to these roles if any user requested for performance or monitoring tools access .
Now no need of giving SA access to such requests.
Below is 2019 SQL SERVER roles.
| Fixed server-level role | Description |
|---|---|
| ##MS_DatabaseConnector## | Members of the ##MS_DatabaseConnector## fixed server role can connect to any database without requiring a User-account in the database to connect to. To deny the CONNECT permission to a specific database, users can create a matching user account for this login in the database and then DENY the CONNECT permission to the database-user. This DENY permission will overrule the GRANT CONNECT permission coming from this role. |
| ##MS_DatabaseManager## | Members of the ##MS_DatabaseManager## fixed server role can create and delete databases. A member of the ##MS_DatabaseManager## role that creates a database, becomes the owner of that database, which allows that user to connect to that database as the dbo user. The dbo user has all database permissions in the database. Members of the ##MS_DatabaseManager## role don't necessarily have permission to access databases that they don't own. |
| ##MS_PerformanceDefinitionReader## | Members of the ##MS_PerformanceDefinitionReader## fixed server role can read all catalog views that are covered by VIEW ANY PERFORMANCE DEFINITION, and respectively has VIEW PERFORMANCE DEFINITION permission on any database on which the member of this role has a user account. This is a subset of what the ##MS_DefinitionReader## server role has access to. |
| ##MS_SecurityDefinitionReader## | Members of the ##MS_SecurityDefinitionReader## fixed server role can read all catalog views that are covered by VIEW ANY SECURITY DEFINITION, and respectively has VIEW SECURITY DEFINITION permission on any database on which the member of this role has a user account. This is a small subset of what the ##MS_DefinitionReader## server role has access to. |
| ##MS_DefinitionReader## | Members of the ##MS_DefinitionReader## fixed server role can read all catalog views that are covered by VIEW ANY DEFINITION, and respectively has VIEW DEFINITION permission on any database on which the member of this role has a user account. |
| ##MS_LoginManager## | Members of the ##MS_LoginManager## fixed server role can create and delete logins. |
| ##MS_ServerPerformanceStateReader## | Members of the ##MS_ServerPerformanceStateReader## fixed server role can read all dynamic management views (DMVs) and functions that are covered by VIEW SERVER PERFORMANCE STATE, and respectively has VIEW DATABASE PERFORMANCE STATE permission on any database on which the member of this role has a user account. This is a subset of what the ##MS_ServerStateReader## server role has access to. |
| ##MS_ServerSecurityStateReader## | Members of the ##MS_ServerSecurityStateReader## fixed server role can read all dynamic management views (DMVs) and functions that are covered by VIEW SERVER SECURITY STATE, and respectively has VIEW DATABASE SECURITY STATE permission on any database on which the member of this role has a user account. This is a small subset of what the ##MS_ServerStateReader## server role has access to. |
| ##MS_ServerStateReader## | Members of the ##MS_ServerStateReader## fixed server role can read all dynamic management views (DMVs) and functions that are covered by VIEW SERVER STATE, and respectively has VIEW DATABASE STATE permission on any database on which the member of this role has a user account. |
| ##MS_ServerStateManager## | Members of the ##MS_ServerStateManager## fixed server role have the same permissions as the ##MS_ServerStateReader## role. Also, it holds the ALTER SERVER STATE permission, which allows access to several management operations, such as: DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE ('ALL'), DBCC SQLPERF() |
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
SELECT
bs.database_name, bs.backup_start_date, bs.backup_finish_date, bs.server_name, bs.user_name, bs.type, bm.physical_device_nameFROM msdb.dbo.backupset AS bsINNER JOIN msdb.dbo.backupmediafamily AS bm on bs.media_set_id = bm.media_set_idOne 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.
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.Index scan means it retrieves all the rows from the table and index seek means it retrieves selective rows from the table.
The Following script will retrieve the size of all your databases in MB and GB.
SELECT d.NAME ,ROUND(SUM(CAST(mf.size AS bigint)) * 8 / 1024, 0) Size_MBs ,(SUM(CAST(mf.size AS bigint)) * 8 / 1024) / 1024 AS Size_GBs FROM sys.master_files mf INNER JOIN sys.databases d ON d.database_id = mf.database_id WHERE d.database_id > 4 -- Skip system databases GROUP BY d.NAME ORDER BY d.NAME
--Exclude Offline Databases
SELECT d.NAME
,ROUND(SUM(CAST(mf.size AS bigint)) * 8 / 1024, 0) Size_MBs
,(SUM(CAST(mf.size AS bigint)) * 8 / 1024) / 1024 AS Size_GBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4
--and d.state_desc='online' -- Skip system databases
GROUP BY d.NAME
ORDER BY d.NAME
Database security is critical for an organization to protect unauthorized access to the sensitive and critical data stored in the database objects. There are many layers of security in terms of infrastructure security, database authentication, authorization, encryption.
Once you create a new Azure SQL DB using Azure portal or Azure CLI, the provisioning process deploys a logical Azure SQL Server in the Azure region. You get a virtual master database for managing the configuration and security at the server level. It also configures a server-level principal as a database owner of the Azure database. This account has the highest permissions in Azure SQL DB(PaaS) and has sufficient rights to manage server and database-level security.
The following table summarizes the difference in database security management of Azure SQL Database and on-premises SQL Database.
Fixed Database Roles in Azure DB
Expand the Azure SQL DB and navigate to security -> Roles -> Database Roles to get a list of available fixed database roles, expand the Azure SQL DB and navigate to Security -> Roles -> Database Roles. You get the following fixed-database roles.
Additional roles in the virtual master database
If you look at the same database roles in the virtual master database, you get additional database roles, as shown below.
Loginmanager role:
Users in the loginmanager database role can create and delete the logins in the master database.
dbmanager role
The dbmanager role allows the user to create a database, delete a database as a database owner. It allows users to connect the Azure database as a DBO user that contains all DB permissions. The user in the role does not have permission to access other databases that they do not own.
Few years ago when I started my career as a SQL Server DBA, I remember a 200-500 GB sized database was considered as a very large database. During that time maintaining and managing those databases were not a tedious task. But over the years the definition of a very large database has changed. Today we’re looking at database size in terabytes and petabytes.
With cloud computing, hardware requirements needed to sustain the growth of databases are now just one click away. Auto-scaling is now a blessing to companies, as they previously had to run through their budgets whenever there was a need to add resources to the database servers.
Because database sizes have grown over time, managing and maintaining them have become a pain. And when I say managing and maintaining a database, it means taking regular backups, performing index maintenance, integrity checks, etc. etc.
Most of the time we try to archive the old/cold data so that we can keep the database size in check. But sometimes there are cases where the scope to archive the database is very limited. This is especially in medical and financial sectors where old data is still used for various purposes.
Given the rate of database daily growth, I’m going to take you through some database management tasks to give you a better understanding of how you can keep up.
Consider an OLTP database that is active 24*6 and is around 10TB in size.
Backup Strategies
Taking daily full backup of databases whose size is 10TB can be a very demanding task, especially for an OLTP database. Even with better hardware, the backup time would be around five to seven hours. Therefore, having a proper backup strategy in place is as important as maintaining its availability.
It would be wise to consider having a weekly full backup with daily differential and hours transaction backup when looking at the time and cost of resources.
Using third party tools to backup the database is a better option. These tools not only help you in reducing the time taken to backup the database, but also reduces the size of the compressed backup.
INDEX Maintenance
Performing normal index maintenance tasks on a very large database is not the same as performaning on a regular size database. REBUILDING index on big tables having big indexes is very time consuming. This is something that also causes blocking on the servers which hampers the performance of other applications.
The only way to maintain the indexes on such a huge database is to REORGANIZE them. REBUILD index option can only be chosen during index corruption or when there is an absolute need to REBUILD a particular large index.
One important option that we need to consider for creating indexes on such a large database is to use is to specify WITH SORT_IN_TEMPDB in the statement. SORT_IN_TEMPDB forces the index build to occur in tempdb, and when it is complete, it is then written to its destination filegroup. This is an often-overlooked option that can provide huge reductions in index build times.
Another advantage of reorganizing the index is that we can stop the operation in the middle if the execution time overlaps business hours – and this will not cause the index to go in ROLLBACK state.
Database Consistency Check (DBCC)
DBCC command is used to check the consistency and integrity of the database. This command helps us to make sure that our databases are in a healthy state and if in case any issue/corruption occurs then this command helps to identify and fix the issue.
Executing the DBCC command is very resource-intensive. It causes constrain on both memory and disk. Running DBCC command on such a large database can be very risky because if the command does not execute in the allotted time frame and if we try to KILL the execution process it will go in ROLLBACK state. This is not only time consuming but also jeopardizes the consistency of the database. Hence running this command on a very large OLTP database is not a feasible option.
The speed of completion of this command completely depends on the Memory provided and the type of RAID used for hosting the tempdb database.
Other options can be used such as DBCC CHECKTABLE against individual tables or groups of tables on a rotating basis, DBCC CheckDB WITH PHYSICAL_ONLY. This option limits the processing to check the integrity of the physical structure of the page and record headers in addition to the consistency between the pages for the allocation structures (data and indexes).
The most recommended and best option is to have a STANDBY server, restore the backup of the production database on that server, and then run the DBCC command. If the consistency checks run ok on the standby database, the production database should be ok as it is the source of the standby. If the standby database reports corruption, then DBCCs or other tests for corruption can be run against the production database.
I hope this gives you a better understanding of the complexity and management options for very large databases in SQL Server.
$ServerList = "C:\DBA\Servers.csv"
$OutputFile = "C:\DBA\Output.htm"
$emlist="sqldbanow@gmail.com"
$MailServer="smtp.sqldbanow.com"
$HTML = '<style type="text/css">
#Header{font-family:"Trebuchet MS", Arial, Helvetica, sans-serif;width:100%;border-collapse:collapse;}
#Header td, #Header th {font-size:14px;border:1px solid #98bf21;padding:3px 7px 2px 7px;}
#Header th {font-size:14px;text-align:left;padding-top:5px;padding-bottom:4px;background-color:#A7C942;color:#fff;}
#Header tr.alt td {color:#000;background-color:#EAF2D3;}
</Style>'
$HTML += "<HTML><BODY><Table border=1 cellpadding=0 cellspacing=0 width=100% id=Header>
<TR>
<TH><B>Database Name</B></TH>
<TH><B>RecoveryModel</B></TD>
<TH><B>Last Full Backup Date</B></TH>
<TH><B>Last Differential Backup Date</B></TH>
<TH><B>Last Log Backup Date</B></TH>
</TR>"
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
Import-CSV $ServerList |ForEach-Object {
$ServerName=$_.ServerName
$AppName=$_.ApplicationName
$HTML += "<TR bgColor='#ccff66'><TD colspan=8 align=center>$ServerName - $AppName</TD></TR>"
$SQLServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName
Foreach($Database in $SQLServer.Databases)
{
$DaysSince = ((Get-Date) - $Database.LastBackupDate).Days
$DaysSinceDiff = ((Get-Date) - $Database.LastDifferentialBackupDate).Days
$DaysSinceLog = ((Get-Date) - $Database.LastLogBackupDate).Days
IF(($Database.Name) -ne 'tempdb' -and ($Database.Name) -ne 'model')
{
if ($Database.RecoveryModel -like "simple" )
{
if ($DaysSince -gt 1){
$HTML += "<TR >
<TD>$($Database.Name)</TD>
<TD>$($Database.RecoveryModel)</TD>
<TD bgcolor='RED'>$($Database.LastBackupDate)</TD>
<TD>$($Database.LastDifferentialBackupDate)</TD>
<TD>NA</TD>
</TR>"
}
}
if ($Database.RecoveryModel -like "full" )
{
if ($DaysSince -gt 1){
$HTML += "<TR >
<TD>$($Database.Name)</TD>
<TD>$($Database.RecoveryModel)</TD>
<TD bgcolor='RED'>$($Database.LastBackupDate)</TD>
<TD>$($Database.LastDifferentialBackupDate)</TD>
<TD>$($Database.LastLogBackupDate)</TD>
</TR>"
}
}
if ($DaysSince -lt 1)
{
$HTML += "<TR >
<TD>$($Database.Name)</TD>
<TD>$($Database.RecoveryModel)</TD>
<TD bgcolor='GREEN'>$($Database.LastBackupDate)</TD>
<TD>$($Database.LastDifferentialBackupDate)</TD>
<TD>$($Database.LastLogBackupDate)</TD>
</TR>"
}
}
}
}
$HTML += "</Table></BODY></HTML>"
$HTML | Out-File $OutputFile
Function sendEmail
{
param($from,$to,$subject,$smtphost,$htmlFileName)
$body = Get-Content $htmlFileName
$body = New-Object System.Net.Mail.MailMessage $from, "$to", $subject, $body
$body.isBodyhtml = $true
$smtpServer = $MailServer
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($body)
}
$date = ( get-date ).ToString('MM/dd/yyyy')
$emlist
sendEmail sqldbanow@gmail.com $emlist "SQLDBANOW Test Server Backup Report for - $Date" $MailServer $OutputFile
---------------------------------------------
-- You need to create one csv file with servers list as below mentioned screenshot C:\DBA\Servers.csv
