SQL AZURE Tip 1: Use auto-scaling to reduce the cost during off hours.

Background: Below Tip can save you some cost in Azure. 

Almost every project has some peak hours (Day time) and non-peak hours (Night Time) throughout a
week or day. Normally the DTU’s (CPU, Memory & IO) or vCore’s consumptions are lower at non-
peak hours and higher at peak hours. In such scenarios, we can save some money by auto scaling
down and scaling up the databases during these hours. Below simple automation Job(attached) will
do the same thing.
By moving from On-premise to AZURE, we can save lot of money. AZURE offers a range of uses and
benefits for organizations. In AZURE, maintenance is very easy and you can only pay for what you
use the resources and you can also scale up on demand. 
Below is the step by step document, how we implement this in windows scheduler
Note: You can also use Runbooks, Elastic Jobs and other job schedulers to automate same task. Just
for easy understanding, I showed here in windows scheduler job

SQL AZURE Tip 2: How to refresh/restore a Database from prod to dev in Azure?
Background: There is a database called RajSQLDB in Prod server name called ProdDBServer under
premium tier. This database need to restore in my DEV server with standard tier (I don’t want to
keep same premium tier as this db is in prod. In dev workload is low and keeping low tier always
saves the money for the clients) 
Solution:
RUN the below query in management studio in Dev server
CREATE DATABASE RajSQLDB AS COPY OF ProdDBServer.RajSQLDB
(EDITION = 'standard', SERVICE_OBJECTIVE = 'S12', MAXSIZE = 250 GB ) ;
You can see the restore progress using below queries in master Database in dev server
SELECT * FROM sys.dm_database_copies where name=’RajSQLDB’
SELECT state_desc, * FROM sys.databases where name=’RajSQLDB’

SQL AZURE Tip 3: How to check deadlock information in the AZURE SQL DB?
Solution: We can track the deadlocks using below query. Please execute below query in Master DB.
SELECT * FROM sys.event_log   
WHERE event_type = 'deadlock';
Once you found the Deadlocks, you can further dig and find in-detail information on each deadlock
using below query.
Need to run below query in Master DB only.
WITH CTE AS (  
SELECT CAST(event_data AS XML)  AS [target_data_XML]   
FROM sys.fn_xe_telemetry_blob_target_read_file('dl', null, null, null)  
)  
SELECT target_data_XML.value('(/event/@timestamp)[1]', 'DateTime2') AS Timestamp,  
target_data_XML.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,  
target_data_XML.query('/event/data[@name=''database_name'']/value').value('(/value)[1]',
'nvarchar(100)') AS db_name  
 FROM CTE
--where target_data_XML.value('(/event/@timestamp)[1]', 'DateTime2')>='2020-03-01'
--and target_data_XML.query('/event/data[@name=''xml_report'']/value/deadlock') like
'%Employee%'
order by Timestamp desc

You can copy and save the deadlock_xml column data in notepad file and same with extension of  .x
dl (example deadlock1.xdl) and open the saved file in the Management Studio. You can see the
deadlock information in the graph(s).

Note: Like on premise, we don’t need to setup any trace flag in azure sql db. Even you cannot enable
any trace flags here 

SQL AZURE Tip 4: How to change MAXDOP Setting in the azure DB?
Solution: We (SQL DBA Guys) all know the what is MAXDOP? YES!! The Maximum Degree of
Parallelism (MAXDOP) is a server, database or query level option that is used to limit the number of
processors that the parallel plan can use.
Below in azure, once you changed the MAXDOP setting to 1. You can see only serial execution plans.
Just  

SELECT * FROM sys.database_scoped_configurations

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1
 

SQL AZURE Tip 5: Interesting facts about Azure SQL Databases:

 Recovery model is FULL and you cannot change this

 Backups are automatically scheduled and taken and you cannot perform manual database
backup 
 You cannot download/watch automated backups 
 No SQL agent but you can automate using Azure Automation Runbooks and/or Elastic Jobs
and/or windows scheduler. Widely used one is Elastic Jobs
 Restore with Overwrite not supported on Azure SQL DB
 Deleted database also can be restored on limit of t retention period, but once you delete the
server not possible restore the backup from the portal. 
 By default, the database is READ_COMMITTED_SNAPHOT isolation
 You can’t enable the trace flag in Azure SQL DB model
 You can’t keep the database in Single User mode. This won’t work
ALTER DATABASE DBA SET SINGLE_USER WITH ROLLBACK IMMEDIATE
 You can’t make the database Offline. This won’t work
ALTER DATABASE DBA SET OFFLINE WITH ROLLBACK IMMEDIATE
 How to stop the application connecting to database is either you need to rename the
database or need to revoke the access for the application user.
ALTER DATABASE DBA MODIFY NAME = DBA_Old
REVOKE CONNECT FROM ApplicationUser

SQL AZURE Tip 7: Can we enable Trace flags in Azure SQL databases?
Ans) No. We can’t enable trace flags in Azure SQL databases. But, we can see some of the trace flags
which were already enabled in Azure SQL Databases.
Note: Total 25 Trace flags are there and all these were globally enabled. 

Find more interesting things on below my YouTube channel

https://www.youtube.com/channel/UCvo_n6QXy8RWp26HWZwh7Jg
 SQL AZURE Tip 8: How many of you know about HA/DR for your Virtual machine in Azure?
Azure VM high availability and disaster recovery includes below 3 options.
Below are 3 options for ensuring a Virtual Machine (VM) is highly available
 No infrastructure redundancy required (Single VM)—running a Virtual Machine (VM) on
Azure with no replication.
 Availability Sets—running a VM with one or more replicated copies on separate
hardware within the same Availability Zone, providing resiliency against machine failure.
 
 Availability Zones—running a VM with one or more replicated copies on different
Availability Zones within same region, providing resiliency against data center failure.
99.99% = Availability Zones

SLA VM in Azure No infrastructure redundancy required Availability Sets Availability Zones

99.90% 99.95% 99.99%

SQL AZURE Tip 9: How to optimize the performance of Microsoft SQL Server workloads running in an
Azure Virtual Machine environment?

The following is a quick check list that you can follow: 

• Use minimum Standard Tier A2 for SQL Server VMs.
• Keep the storage account and SQL Server VM in the same region.
• Disable Azure geo-replication on the storage account.
• Avoid using operating system or temporary disks for database storage or logging.
• Avoid using Azure data disk caching options (caching policy = None).
• Stripe multiple Azure data disks to get increased IO throughput.
• Format with documented allocation sizes.
• Separate data and log file I/O paths to obtain dedicated IOPs for data and log.
• Enable database page compression.
• Enable instant file initialization for data files.
• Disable autoshrink on the database.
• Move all databases to data disks, including system databases.
• Move SQL Server error log and trace file directories to data disks.
• Apply SQL Server performance fixes.
• Setup default locations.
• Enable locked pages.
• Backup directly to blob storage.

Note: Above uses results from specific test scenarios to provide guidance and best practices for
optimizing the performance of SQL Server workloads running in Azure virtual machine (VM). 

SQL AZURE Tip 10: AZURE SQL DATABASE Backup Storage Types 

 Locally redundant storage (LRS)

 Design characteristics: replicates your data three times within a single physical
location in the primary region. LRS provides at least 99.999999999% (11 9’s)
durability of objects over a given year. LRS protects your data against server rack and
drive failures. However, if a disaster such as fire or flooding occurs within the data
center, all replicas of a storage account using LRS may be lost or unrecoverable.
 Best for: LRS keeps your data in the same region and provides capability of data
residency and helping you to stay compliant with regulatory requirements. In
addition, LRS is the lowest-cost redundancy option (but offering the least durability
compared to other options) which is good fit for dev/test scenarios.
 Zone-redundant storage (ZRS)
 Design characteristics: replicates your Azure Storage data synchronously across
three Azure availability zones in the primary region. Each availability zone is a
separate physical location with independent power, cooling, and networking. ZRS
offers durability for Azure Storage data objects of at least 99.9999999999% (12 9's)
over a given year.
 Best for: ZRS also provides capability of data residency but offers higher durability
due to data replicated across availability zones. It is good fit for production scenarios
that are cost sensitive.
 Geo-redundant storage (RA-GRS) - RECOMMENDED (DEFAULT)
  Design characteristics: replicates your data synchronously three times within a
single physical location in the primary region using LRS. It then copies your data
asynchronously to a single physical location in a secondary region that is hundreds of
miles away from the primary region. RA-GRS offers durability for Azure Storage data
objects of at least 99.99999999999999% (16 9's) over a given year.
 Best for: RA-GRS is best disaster recovery option which gives highest durability. In
addition, geo-redundant backup storage enables Geo-restore capability – a cheap
and economically efficient disaster recovery option. This is default configuration
value and if there is no need for data residency compliance, it is recommended to
use RA-GRS backup storage for all production workloads.

TIP 11: Azure SQL:

Transform your existing applications with databases that do more for you
Modernize Today with Azure SQL
  Ensuring your organization can cost-effectively adapt and scale to today's rapidly changing
environment is key.  Azure SQL can help you optimize your costs and focus resources on the
tasks that matter most to you.  With Azure SQL, you can take advantage of built-in high
availability and automated backups, updates, patches and performance tuning. 
 Explore Azure SQL to see how these database services meet the most demanding workload
requirements:
 SQL Server on Azure Virtual machines - easily migrate SQL Server workloads to the cloud
while maintaining 100 percent SQL Server compatibility and OS-level control.
 Azure SQL Managed Instance - modernize your existing SQL Server applications at scale with
an intelligent, always up to date, fully managed instance in the cloud.
 Azure SQL Database - support modern apps in the cloud with a fully managed service that
includes serverless compute and Hyperscale storage.


Hi, this is Raj, Azure SQL DBA Trainer. 

 Reach me on WhatsApp- 9966246368/[email protected] for AZURE SQLDBA
Trainings/Interviews. 
These trainings are completely real time, hands-on, interview based. 
My LinkedIn profile: 
https://www.linkedin.com/in/rajasekhar-reddy-bolla/