MODULE 7

Lesson 1: SQL Server on Virtual Machines and

Azure SQL Databases
1. What resources are provided by running SQL Server on an Azure VM?
a. {{1 -}}
b. {{2 -}}
2. What model of cloud services does provisioning SQL Server on Azure VMs fall under?
a. {{3 - - - - - - - - - - - - - - -}}
b. It is conceptually similar to {{4 - - - - - - - - - - - - - - - -}}
3. What aspects of management of SQL Server on VMs are required by DBA?
a. {{5 -}}
b. {{6 - -}}
c. {{7 - - - - - -}}
d. Majority of the administrator aspects remain the same as on-premise machines
4. Licensing models for SQL
a. Platform-provided SQL Server Image:
i. Use a {{8 - - - - - - - - - - -}}
ii. The VM contains {{9 - - - - - - - - - - - - -}}
iii. Supported versions
1. {{10 - - - - - - - - - -}}
iv. Supported editions
1. {{11 - - - - - - - - - - - - - - - -}}
v. What are the license costs for SQL Server based on?
1. Specification of {{12 - - -}}
2. {{13 - - -}} of SQL Server
vi. NB:/ it is not based on the SQL Server {{14 -}}.
b. Bring-your-own SQL Server License:
i. What is Microsoft Volume Licensing?
ii. What is provided by Volume Licensing to coroporations?
1. Price: {{15 - - - -}}
2. Tenure: {{16 - - - - - - - -}}
3. Rights not included in FPP ({{18 - - - - - - - -}}) Licenses - e.g. right
to{{19 - - - - - - - - - - - - - - - -}}.
iii. How does this licensing option work?
1. Use {{20 - - - - - - - - - - - -}} to install SQL Server{{21 - - - -}}.
iv. Versions supported
1. 64 bit versions from SQL Server 2008 R2 → Latest
v. Who is this licensing model available to?
1. {{22 - - - - - - - - - - - - - - -}} who have purchased Software
Assurance
 2. Essentially {{23 - - - - - - - - - - - - - - - - - - - - - - -}}
c. Which SQL Server version is supported by linux distros?
i. {{25 - - - - -}}
ii. Linux distros supported for Azure VMs
1. {{26}}
2. {{27}}
3. {{28 - - - - - - - -}}
5. Aside from the SQL Server licensing model, what are the additional costs expected to be
charged?
a. Usage of the {{30 - - - - - -}}
b. {{31 - - - - - -}} for the VM disks
c. {{32 - - - - - - - - - -}} (from Azure data centers)
6. What are the limitations on SQL Server functionality when it is used on Azure Vms?
a. They are no restrictions but this depends on;
i. {{33 - - - - - -}}
ii. {{34 - - - - - -}}
iii. {{35 - - -}}
7. What is the service level agreement of Azure VMs and how does this impact services
running on them e.g. SQL Server?
a. {{36 - - - - - - - - - - - - - - - -}}
b. {{37 - - - - - - - - - - - - - - - - - - - - - - - -}}
8. Find out more information on the licensing models
a. Licensing Programs - Eligibility requirements
b. Pricing - Linux Virtual Machines
9. What are resource groups in Azure?
a. By default when resources such as {{77 - - - - -}} and {{78 - - - - -}} are created in
Azure they are {{39 - - - - - - - - - - - - -}} known as resource groups.
10. Why is it a recommended practice to place all Azure resources used by an
application be placed in a single resource group?
a. Easier to {{40 -}}
b. Manage {{41}} to the resources
c. Manage {{42 - - - - -}} with the resources
11. What Azure resources required to run a database application should be placed in the
same resource group?
a. {{43}}
b. {{44 - - - - -}} where those VMs maintain {{45 - - - - - -}}
c. {{46 - - - - -}} (Vnets)
d. {{47 - - - -}} to distribute the workload efficiently across resources
12. What is the purpose of the Azure Resource manager?
a. {{49 - - - - -}} created in {{0 - - - - -}}
b. Can be used alongside a template to easily {{79 - - - - - - - - - - - - - - - - - - - - - - - -
-}} in a single operation.
i. What is the benefit of this approach?
 1. Useful for {{51 - -}} an application {{52 - - - - - - - - -}} in {{53 - - - - -
- - - - - -}} e.g. for staging & production envs.

Azure SQL Database

13. How does Azure SQL Database minimize costs of using SQL Server?
a. It lets Microsoft be responsible for;
i. {{54 - - - - - - -}}
ii. Database administration aspects e.g.
1. {{55 -}}
2. {{56 -}}
3. {{57 - - - - - -}}
b. Leaving the user to be responsible for;
i. {{58 - - - - - - - - - -}}
14. What architectural model(s) does Azure SQL Database fall under?
a. {{59}} - instance
b. {{60}}
15. What are the billing costs for Azure SQL Database?
a. Depends on {{62 - - - - - - -}}
b. {{63 - - - - - - - - - -}}
16. How does Azure SQL Database differ from self installed SQL Server versions?
a. It does not have a comparable {{64 - - - - - -}} to the self installed versions
b. New features are {{65 - - - - - - - - - - - - - - - - - - - - - - - -}}
c. Some features are not {{80 - - - - - - -}} to Azure SQL Server including {{66 - - - - -
- - -}}
17. What is the max size limit imposed on Azure SQL Database and what does it vary by?
a. The size limit varies by {{69 - - - - -}} with the highest having a max size of {{70 -}}
18. What is the SLA availability for Azure SQL Database?
a. {{71}}

Virtual Machines or Azure SQL Database

1. Scenarios SQL Server on VMs is recommended over Azure SQL Database
a. Migrate existing dbs to the cloud with minimal changes
b. It is imperative to run SQL Server on dedicated servers without buying on-
premise hardware or software
c. The company has existing IT resources to provide support and maintenance
d. There is a need for full control of SQL Server and Windows
e. Work with databases upto 64TB
f. Use a hybrid approach with some components on the cloud and others on-
premise
2. Azure SQL Database is recommended for;
a. New applications that are cloud based
b. Applications that use the scale out pattern
 i. Scale out approach is where multiple servers with identical specifications
are provided to handle high traffic volume.
1. Multiple virtual servers with a load balancer frontend to distribute
traffic to individual virtual servers
ii. Scale out means adding more components to spread out a load.
iii. Scale up means making a component bigger so that it can handle a more
load.
c. There is a need for high availability without any application downtime
d. Minimise administration and administration costs
e. There is no need for full administrative control of SQL Server
f. There maximum required size of database does not need to exceed 4TB

What is Azure SQL

1. Forms of Azure SQL on the cloud;
a. Azure SQL Database - intelligent managed database service that includes
serverless compute. DBaaS
b. Azure SQL Managed Instance - managed instance as a service that provides
100% feature parity with the SQL Server database engine. Can be used for most
migrations to the cloud. PAAS
c. SQL Server on Azure VMs - lift and shift workloads to the cloud while maintaining
SQL Server compatibility operating system level access. IAAS
2. Important features of Azure SQL Database
a. Allows developer to spend more time innovating than administering
i. It automatically applies updates and patches to eliminate end of support
hassle
ii. It is also eliminates heavy administration works such as performance
tuning, high availability, disaster recovery and backups
b. Protect data with intelligent built in security
i. Advanced threat detection and proactive vulnerability assessment
ii. Muiti layered protection; TSQL, authentication, network and key
management
iii. Comprehensive compliance coverage
3. Motivations for IAAS or PAAS
a. Cost
i. Both PAAS and IAAS include the base price for the underlying
infrastructure and license costs.
ii. WIth PAAS there is no overhead for administration as these are included
in the base price
iii. With IAAS you can shutdown resources when they are not in need unlike
IAAS which is always running (unless you drop and recreate resources
when they are needed)
b. Administration
i. CLR
 1. Provides various functions and services required for program
execution
a. JIT compilation
b. Exception handling
c. Type Safety
d. Allocation and management of memory
e. Thread management
f. Security
2. Useful for creating complex execution logic for stored procedures
and triggers using the .NET BCL (Base Class Libraries)
ii. Although PAAS reduces the administration time it limits the custom
scripts that can be run e.g. not supporting CLR - this can be useful for
custom administration tasks.
c. Service Level Agreement
i. PAAS provides 99.99% SLA
ii. IAAS provides 99.95% SLA meaning additional mechanisms must be
implemented to increase the SLA.
1. By adding an additional VM and implementing Always On
Availability group high availability SLA can be increased to
99.99%
d. Time to move to Azure
i. IAAS (SQL Server on Azure VM) is a replica of the on-premise
environment so no changes are required for the migration.
ii. Azure SQL Database/Azure SQL Managed instance might require some
changes to be applied before the migration.

Azure Virtual Networks

1. Virtual Network is analogous arrangement of VMs and other resources such that they
can communicate using private IPs.
2. Machines on separate VNets can communicate with each other using a gateway at
either end.
3. Virtual Private Network (VPN)
a. Encrypted end to end connection
b. Method of connecting to a local network through the internet.
c. It can be seen as an extension of the private network
d. A VPN tunnel encapsulates data packets for security such that if the packets
were sniffed they would be unreadable and if they were to be tampered with the
VPN gateway would be aware of the compromise.
e. VPN Scenarios
i. SIte-to-site
1. VPN data is encrypted from one site’s gateway to another
allowing sharing of resources through the VPN link
 2. E.g two different businesses can have a VPN link for secure
communication
ii. Remote Access VPN
1. A secure connection is made from an individual computer to a
VPN gateway
2. E.g. A vpn connection that allows remote users to access a
secure private network at work for email, files and other
resources.
f. VPN Network Protocols
i. PPTP
1. Point to Point Tunneling Protocol
2. Remote users access secure remote servers by dialing their ISP
who set up a secure call to the server via the internet
3. Used by Microsoft Windows Platform to connect with other PPTP
enabled systems
ii. L2TP
1. Extension of PPTP that uses 256bit keys.
2. It is a combination of PPTP with L2F (cisco’s version of PPTP)
3. It is slightly slower as it encrypts data twice making it less efficient
iii. IPSEC
1. Provides application layer cryptography
2. Provides security for router sending data across the internet
3. Authenticate data such that it can be verified as originating from a
known sender
4. Setup circuits using IPSec tunnelling in which all the data sent
between the two endpoints is encrypted - VPN
iv. SSL VPN
1. Difference from IPSec is that you do not require client software or
configuration for this
2. Access is through a URL. A lightweight client will be installed and
is easily configured which creates VPN tunnel between the end
user and the remote resources.
3. Has less configuration on the end user compared to IPSec - which
requires downloading client software and configuring it
4. Contains a feature called host checking or Network Access
Control which verifies if an end users computer is compliant and
upto date. E.g. if their firewall and anti-virus are updated, or
running a certain application that the remote server requires, or
their OS is update to a certain patch level etc.

Using Vnets to host SQL Server VMs

1. Advantages of migrating from on-premise server to Azure VNets containing multiple VMs
a. To reduce maintenance costs
b. To eliminate the need for on-premise data centers
 c. Multiple VMs means that the database system enjoys the 99.95% availability and
resilience afforded by the SLA
d. VMs in a VNet can communicate with each other without additional configuration
2. Additionally, Requires little or no modification
a. The only modification will be the connection string used by client software to
connect
b. The database schema remains the same
3. Lastly, A VPN can be used to securely connect on-premise clients to the database VMs
securely

Using VNets to Isolate SQL Server VMs

1. A common arrangement of on-premise servers, that are for instance running an e-
commerce website is to separate the web server and database server using two firewalls
and ensuring the database server is only accessible through a certain private port.
2. The separation of the web server and sql server VMs can be done in Azure using two
separate VNets and NSG
a. Azure Network Security Groups (NSG) can be used to used to filter traffic to
determine which can hosts can communicate with the VMs on the VNets

Azure Active Directory

Windows Active Directory

1. The recommended authentication method for SQL Server is Windows Authentication
a. Users are stored in the Active Directory
b. For mixed authentication users are stored in the database
2. Active Directory is a distributed (for servers and machines on a network) directory server
that can replicate security principals to multiple physical directory servers.

Azure Active Directory

1. Migration to Azure through Azure VMs or Azure SQL database allows for the use of SQL
Server Authentication or Windows Authentication to connect.
2. Windows Authentication requires for the user accounts and credentials to be stored in
Azure Active Directory
3. Azure Active Directory enables the use of a single user account to access a myriad of
Azure resources and applications
4. Azure Active Directory can also be used in conjunction with the on-premise Active
Directory forest so that user will only use on account to access both local and cloud
resources.
SQL Performance Tiers