Module 2db - Exploring Azure Core Products - Database Solutions - SQL Server Flashcards
What is Azure SQL Database?
A relational database based on the latest stable version of SQL Server
- PaaS offering
- No infrastructure management - instances are fully managed (upgrades, patches, backups, monitoring)
- Support for programming language
- SLA: 99.99 (quad 9s)
- Built-in High Availability
What’s the biggest advantage with Azure SQL Database w.r.t. new features?
Microsoft deploys new features on Azure SQL Database BEFORE deploying to SQL Server…so you get the added advantage of early access without the overhead of updates, upgrades or testing (i.e. it’s already been tested)
Azure SQL Server handles relational data as well as non-relational data like graphs, JSON, spatial and XML (T/F)?
True
Once you create an Azure SQL Database, you should be able to log in immediately and start developing (T/F)?
False. The IP address you are logging into the DB Server from needs to be whitelisted in Firewall Settings
Where do you set Client IP Address for Azure SQL Database Logins?
Firewall Settings > click Add client IP .
Your IP address should appear in the details page, labeled Client IP Address
What is a Azure SQL Managed Instance?
A scalable cloud data service that provides comprehensive SQL Server Database compatibility with ALL benefits of a fully managed PaaS…
Think of it like:
- Azure SQL Server == IaaS version of SQL Server
- Azure SQL Managed Instance == PaaS version of SQL Server
…though they are technically both PaaS…
What features does Azure SQL Managed Instance have?
Generally all the same features as Azure SQL Database:
- It’s already a fully managed environment (no hardware purchases or management
- Quick provisioning and scaling
- Automated upgrades and patches
- Built-in High Availability, 99.99% SLA (Quad 9s)
- Data protection with configurable backup retention period
Note that SQL Managed Instance has SQL Profiler, while SQL Database does NOT…
Auto-backups for Azure SQL Managed Instance can recover and restore as far back as you want to configure it (T/F)?
True. The Backup Retention Period is purely configurable.
Azure SQL Managed Instance has the exact same feature availability as Azure SQL Database (T/F)?
FALSE!
There are a TON of differences…Managed Instance has a lot of features DB doesn’t. Some of which are:
- BACKUP command
- Change Data Capture
- Collation can be set whereas the default in SQL Database is SQL_Latin1_General_CP1_CI_AS
- CLR access
- Cross-DB Transactions
- DbMail
- Distributed partition views
- Linked Servers between SQL Server and SQL Database
- Query notifications
- Resource Governor
- Restore Statements
- Service Broker
- SQL Server Agent
- SQL Server Auditing
What are some CAPABILITIES are available for Azure SQL Database that are NOT for Azure SQL Managed Instance?
- Active Geo-Replication
- Auto-Scale but only in serverless mode
- Azure Resource Health
- Hyperscale Architecture
- Pause/Resume
- SQL Data Sync
- Query Performance Insights (QPI)
- VNet Service Endpoint
Key point being…Managed Instance has LESS Features than SQL Database
Azure SQL Database and Azure SQL Managed Instance have virtually all the same available Tooling (T/F)?
False. Azure SQL Database does NOT have SQL Server Profile available, while Managed Instance does.
What options do you have for migrating On-Prem SQL Server instances to Azure SQL Database?
- Microsoft Data Migration Assistant (you did this locally, it created a Standard Tier DB, killed your spending limit for the month)
- Azure Database Migration Service
- The classic life-n-shift; take a backup, restore in the cloud
What is Microsoft Data Migration Assistant?
An On-Prem solution for migrating On-Prem SQL Server instances to Azure SQL DB
- Download it to your On-Prem database machine to migrate data
- Generates assessment reports for recommendations to help guide your migration
- Manually perform requirements remediation
What is Azure Database Migration Service?
A Cloud solution for migrating On-Prem SQL Server instances to Azure SQL DB
- The service handles requirements remediation for you
- You need to update the connection string in your apps to point to your new Azure SQL Database!
General Knowledge: What tier is used when Microsoft Data Migration Assistant creates and migrates to your new Cloud SQL Database?
It creates a new Database using the STANDARD tier…
Note: I didn’t know this when playing with the Assistant app. It blew up my monthly budget. Go in and switch this to BASIC Tier to keep your costs down.
