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-