AOG on Azure Blob with a different region SQL Server instance

Hello there, everyone! I hope everything is going well for you. Today, I'll show you AOG on Azure Blob with different region SQL Server Instance.

Ø Created SQL disk layout and formatted with 64K-

·       Disk E: SQL Binaries

·       Disk T: TempDB

·       Disk R: User Databases

·       Disk L: User DB transaction logs

Ø SQL Server Memory Configuration as per memory installed on the server-

It reserves memory and caches data into memory to decrease access to disk drives

Increase performance.

Configured value is the environment is 12800/16383 MB.

Ø TempDB Configuration as per processor count-

SQL Server uses tempdb database to store user objects, internal objects and version

stores. Number of tempdb recommended as per processer count.

Configured value is the environment is 4 with equal file size.

Ø Changed SQL Port from default to 34256-  

It highly recommended to not to use default port.

Ø SQL Server Engine and Agent Service Account changed-

Startup accounts used to start and run SQL Server can be domain user accounts, local user accounts, managed service accounts, virtual accounts, or built-in system accounts. To start and run, each service in SQL Server must have a startup account configured during installation.

Ø Grant Lock Pages in Memory enabled for SQL Service Account-

Use to process to keep data in physical memory.

Prevent the system from paging the data to virtual memory on disk.

Locking pages in memory may boost performance

Ø Latest Patch (CU16) installed on the SQL box-

For security and performance benefit, we installed latest DB patch on the DB server.

Ø DB backup configuration on Azure Blob URL- 

The SQL Server Credential stores this authentication information and is used during the

backup or restore operations.

Ø Enabled AOAG on SQL Instance For-

·      HA solution.

·      Automatic or Manual Failover.

·      Enhanced Performance.

·      Combined HA / DR.

·      Zero Data Loss Protection.

Ø Enabled SQL Brower services for DAC-

SQL Server uses the SQL Server Browser service to enumerate instances of the Database Engine installed on the computer. Also, it enables DAC to be connect remotely.

Ø Renamed and disabled sa login-

This is to protect from attackers trying to login as the sa login.

Ø Enabled instant file initialization for data files-

Instant file initialization allows for faster execution of the previously mentioned file operations, since it reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files.

Ø Opened Bi-directional ports between both the DB nodes.  3022 for AG and 34256 for SQL Server-

Bidirectional is a communications mode that is capable of transmitting data in both directions (send and receive).

This was done as a part of AG configuration.

Ø Configured Cloud File-share witness on Windows Failover Cluster-

A Windows File Share Witness is a file share that is available to all nodes in a high availability (HA) cluster.

The job of the Witness is to provide an additional quorum vote, when necessary, to ensure that a cluster continues to run in the event of a site outage.

Ø Limit auto growth of the database-

SQL Database Auto-growth is a procedure due to which SQL Server engine expands its database size when all its space runs out.

The amount due to which the size of database file grows is based on the settings, which is for the growth of file option for database.

Ø Enabled optimize for adhoc workloads-

The optimize for ad hoc workloads option is used to improve the efficiency of the plan

cache for workloads that contain many single use ad hoc batches. 

Ø DB migration from the current prod environment to the newly built environment-

DB migration is moving data from one or more source platforms to another target database. There are several reasons for migrating from one database to another.

Ø Enabled DAC to connect remotely-

DAC is an acronym for Dedicated Admin Connection: a tool for connecting to SQL Server so you can run basic troubleshooting queries in cases of serious performance problems.

Ø Hide DB instance on network-

There is an option to allow to hide an instance of SQL Server, so the name of the

instance is not exposed to other machines on your network.

Ø Set compressed DB backup as default settings-

A compressed backup is smaller than an uncompressed backup of the same data, compressing a backup typically requires less device I/O and therefore usually increases backup speed significantly.

Ø Configure DNN Listener with Port 34257-

DNN listeners are especially useful in Azure VM environments, as they eliminate the need to configure Azure Load Balancers, thus simplifying the configuration and setup.  

Ø How to configure Distributed Network Name (DNN) Listener-

Here we have to configure DNN Listener with the help of Windows PowerShell script as given below-

•       Open the Windows PowerShell and paste this script and execute this script.
•       After that it ask for AG name and Listener name and port no.
•       This is Script for configure DNN Listener as given below-

 param (

   [Parameter(Mandatory=$true)][string]$Ag,

   [Parameter(Mandatory=$true)][string]$Dns,

   [Parameter(Mandatory=$true)][string]$Port

)

 Write-Host "Add a DNN listener for availability group $Ag with DNS name $Dns and port $Port"

 $ErrorActionPreference = "Stop"

 # create the DNN resource with the port as the resource name

Add-ClusterResource -Name $Port -ResourceType "Distributed Network Name" -Group $Ag

 

# set the DNS name of the DNN resource

Get-ClusterResource -Name $Port | Set-ClusterParameter -Name DnsName -Value $Dns

# start the DNN resource

Start-ClusterResource -Name $Port

$Dep = Get-ClusterResourceDependency -Resource $Ag

if ( $Dep.DependencyExpression -match '\s*\((.*)\)\s*' )

{

$DepStr = "$($Matches.1) or [$Port]"

}

else

{

$DepStr = "[$Port]"

}

Write-Host "$DepStr"

# add the Dependency from availability group resource to the DNN resource

Set-ClusterResourceDependency -Resource $Ag -Dependency "$DepStr"

#bounce the AG resource

Stop-ClusterResource -Name $Ag

Start-ClusterResource -Name $Ag 

Ø DB Backup Policy-

Full Backup – A data backup that contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.

Occurs Every Week on Sunday at 12 AM IST.

Differential Backup – A differential partial backup records only the data extents that have changed in the filegroups since the previous partial backup.

It occurs daily at 09 PM IST. 

Log Backup – A backup of transaction logs that includes all log records that were not backed up in a previous log backup. 

It occurs every 15 minutes.

Here, we are keeping 30 days backup on Blob Storage Disk.

Ø DB Mail Configuration-

Database Mail is a solution for sending e-mail messages from the SQL Server Database Engine to users.

Using Database Mail, applications can send e-mail messages.

Configured Mail Profile Account Name. 

Ø DB HA Configuration Report-

New features of SQL Server 2022

Hello guys, hope you are doing well. In this blog I'm going to discuss about the "New features of SQL Server 2022" 

The release of the SQL Server 2022 is much anticipated by the audience since the announcement of it being in the works on November 2nd, 2021. Although the complete details of this brand-new version will be brought to light in due time as it is currently in private preview. However, Microsoft has already disclosed its key features that the audience should look forward to.

Although most of the product’s features are still private, the released information has introduced some unique new performance-based features in the SQL Server 2022. Here is a detailed list of what you’d be working with once you get your hands on the product: 

1. Performance Improvement: This feature enables SQL Server to build better execution plans and potentially multiple execution plans, the performance of which depends on the parameter values that are provided at runtime.

The Query Store can now help resolve performance issues related to the MAXDOP (maximum degree of parallelism) setting, memory grants, and the cardinality estimator using a feedback cycle to adapt and improve query execution plans. Finally, Query Store now supports availability group (AG) read-only replicas. 

2.  High Availability and Connection Improvements: In SQL Server 2022, you may construct a distributed AG between an on-premises SQL Server and an Azure SQL Managed Instance (MI) for use as a disaster recovery backup server or as a read-only replica for reporting workloads, making high availability even better. With a few clicks, you may manually failover to the MI and back to the on-prem SQL Server.

3. Security and Governance Improvements: Using "blockchain" technologies, the  new functionality creates an immutable record of data alterations throughout time. This safeguards data from tampering, which is important in some contexts and use cases, as well as providing benefits for internal and external audits. Microsoft's initial feature set has been developed into a more comprehensive data governance platform. Purview now has tighter integration with SQL Server and Azure SQL, allowing you to scan SQL Server for metadata, classify data using common classifier labels and tagging (such as PII or HIPAA data), and configure and control specific SQL Server access rights and privileges from a single Azure Purview console.

4.  Query Store and Intelligent Query Processing: The SQL Server 2022 ensures that the Query Store will be available on all new databases on the server. It addresses the common issue in the previous models by allowing write access from readable secondaries. This feature will enable transparency into activity on secondary replicas. However, if a database from the older version of SQL is accessed, the Query Store will have to be manually enabled.

5. Parameter Sensitive Plan Optimization: In older SQL Server versions, input parameters at the time of storage had an impact on certain stored processes. Parameter Sniffing was the popular name for this problem. One of the unique features of SQL Server 2022 is the Parameter Sensitive Plan Optimization, which allows you to cache multiple parameter-sensitive queries while procedures are being saved. This will assist you avoid having to alter your code by using the recompile option or allocating the values of the input parameter to variables within the procedure.

6.  TempDB and Latch: During SQL Service Performance Tuning in older SQL server versions, 1 out of 10 clients would struggle with TempDB and Latch. Although you can address it in multiple ways, the practice took valuable time and energy. The SQL Server 2022 addresses these issues by enhancements to the TempDB database and the Memory Optimized TempDB Metadata. Experts are hopeful that clients will not struggle with these issues with the release of this new version.

 

7.  Bidirectional HA/DR to Azure SQL Managed Instance: For enhanced data backup and security, SQL Server 2022 adds Bidirectional HA/DR to Azure SQL Managed Instance. It allows you to connect a database to the cloud in only a few clicks. This not only saves money, time, and labour by eliminating the technicalities of data storage via hardware and virtual machines, but it also encourages better backup and recovery methods. It creates an Availability Group (AG) between a SQL Server and an on-premises Azure SQL Managed Instance.

8.  Azure Synapse Link: The Azure Synapse Link helps move data from an on-premises SQL Server to Synapse without ETL. It captures the changes in the server and feeds them to Azure Synapse Analytics. The Synapse link puts minimal strain on operating systems when conducting real-time analysis and analytical processing. It fastens data transfer and increases the connectivity of the data to other robust analytic programs.

9.  SQL Server Ledger: In comparison to the competition, SQL Server has experienced fewer security concerns in the recent decade. The new SQL Server Ledger, on the other hand, secures data even further by maintaining an immutable record of any changes made to the data or stored processes. The ledger prevents unauthorized parties from tampering with or corrupting the data, lowering the chance of a security breach.

10.Multi-Write Replication: Previously, when rewritten data clashed with different clones of the same database, the dispute had to be manually resolved. SQL Server 2022, on the other hand, addresses this problem by adding the concept of 'last write wins.' As a result, if a conflict between duplicates arises, the most recent rewrite will take precedence and be copied to all copies.

 --By Harigovind Gupta (Software Engineer)

    Clota Technology