SQL Server Certification: Provisioning SQL Databases

Learn how SQL Server evolved from a relational database to a data platform, covering data stores, data models, integration, and visualization across on-premises and cloud.

Explore SQL Server editions and features, including Enterprise, Standard, Web, Developer, Express, and Evaluation, and learn which free editions you can download from Microsoft.com for hands-on practice.

Explore square-one foundations of databases, from what a database is and how data is organized to how provisioning SQL Server databases—especially in cloud environments—ensures efficient storage, retrieval, and indexing.

Compare OLTP and OLAP databases: OLTP supports day-to-day operations with highly normalized tables for fast inserts and updates, while OLAP denormalizes data for historical analysis and reporting.

Learn how Transact-SQL, a proprietary extension of the SQL standard, powers SQL Server for provisioning, administering, and developing databases, with cross-flavor relevance.

Learn to download and install SQL Server 2016 using developer or evaluation editions, download the ISO, run the installer, and gain practical SQL Server experience.

Mount the SQL Server 2016 ISO, run setup as administrator, and complete the planning and installation steps, selecting database engine services and managing default versus named instances.

Configure SQL Server 2016 by selecting a named or default instance, enabling Windows authentication, and adding the current user as administrator to complete installation and prepare for management tools.

Install SQL Server Management Studio as a separate step in SQL Server 2016 by downloading the SSMS installer and running it as administrator, then connect to your server.

Verify your SQL Server installation using the SQL Server installation center and the installed SQL Server features discovery report, and explore essential management tools like SQL Server Management Studio.

Explore the three SQL server deployment options—on premise, Azure SQL database, and Azure virtual machine with SQL Server—along with their costs, control, and maintenance considerations.

Explore cloud computing fundamentals and how Microsoft Azure SQL databases fit into the cloud, highlighting five core characteristics: provider management, resource flexibility, network accessibility, sustainability, and on demand self-service.

Explore virtualization as the core of cloud computing, and learn how virtual machines, virtual hard disks, and hypervisors enable multi-tenant hardware sharing and provisioning of SQL servers in the cloud.

Provision cloud-based applications by provisioning SQL Server 2016 on Azure and explore distributed application design, scalability, and proximity to users through load balancing.

Explore how resource management automation in cloud computing enables on-demand provisioning of disk space, CPU, and memory, guided by service level requirements.

Learn why cloud computing improves cost control and elasticity for SQL Server deployments. See how global redundancy and pay as you go models enhance high availability and reduce hardware procurement.

Explore cloud deployment modes including private, public, hybrid, and community clouds, and how they affect governance and cloud functionalities across SQL Server and Azure environments.

Explore the three standard cloud service models: IaaS, PaaS, and SaaS, and map them to administrators, developers, and users to understand how database as a service fits within these layers.

Discover infrastructure as a service, where cloud providers supply on-demand hardware, storage, and networking, automatically handling maintenance and resiliency while you rent virtual machines and install software.

Explore platform as a service (PaaS) and learn how cloud providers offer a ready development, runtime, and management platform with infrastructure, middleware, tools, and licensing.

Discover SaaS in cloud computing, with pay-as-you-go access to cloud-based applications like Gmail and Office 365, while the cloud provider ensures security and availability.

Move to the cloud, IT staff roles and user expectations shift, demanding dependable, familiar applications while you compare providers, costs, and interoperability across clouds.

Explore how cloud computing reshapes the standard IT operations model, shifts IT roles to the cloud, and transforms security, governance, and business-technical skills for SQL Server provisioning.

Explore how Microsoft Azure, a cloud computing platform, supports provisioning SQL databases through a pay-as-you-go, a la carte model across global data centers.

Discover cloud terminology used in Azure, including IaaS, PaaS, SaaS, SOA and other as-a-service models, plus terms like SFTP, SLA, SSL, VLAN, API, CRM, OpEx, MCO, and GFS.

Learn how to obtain an Azure subscription by creating a Microsoft account, selecting a plan, and subscribing to services like virtual machines and SQL databases, with $200 30-day free trial.

Sign up for the free Azure trial, verify identity with a phone 2FA and a credit card, then navigate the Azure portal to manage SQL databases and other services.

Explore the Azure portal interface to create and manage Azure cloud services, connect SQL databases to SQL Server Management Studio, and navigate dashboards and blades.

Explore how Azure SQL Database, a PaaS single database in the cloud, offers price and performance tiers, sandbox isolation, and management via SSMS and the Azure portal.

Learn how database transaction units (DTUs) benchmark CPU, memory, data, and log I/O to balance performance and cost in Azure SQL databases, and adjust DTUs as workloads grow.

Explore how a logical server provides a cloud-based admin point for multiple Azure SQL databases. Microsoft manages the physical server, while the logical server controls access and security across subscriptions.

Create a new sql database in the azure portal by configuring a logical server, subscription, resource group, and pricing tier. Review deployment progress, connection details, and post-creation options.

Learn to connect to an Azure SQL Database from SQL Server Management Studio, configure firewall rules with your client IP, and manage the database remotely like a local server.

Explore elastic pools in Azure to share DTUs across multiple Azure SQL databases on one server, with autoscaling and min/max controls for efficient cloud performance.

Navigate the Azure portal to create an elastic pool for SQL databases, selecting basic or standard tiers. Add databases to the pool, set per-database limits, and monitor progress with PowerShell.

Learn how Azure virtual machines provide IaaS capabilities, covering image selection, operating system, sizing, regions, networking, storage, cost, and scaling for cloud-based SQL Server deployments.

Explore how Azure Resource Manager and resource groups group related resources with a common lifecycle, enabling template-based deployments and consolidated access control.

Explore the Azure portal to create a Windows Server 2016 virtual machine with SQL Server 2016 standard or developer edition. Configure disk types, cores, memory, resource group, and networking.

Configure Azure virtual machine with SQL server connectivity by enabling public internet access, port 1433, SQL authentication, and deploy SQL Server 2016 standard on Windows Server 2016 using templates.

Access Azure virtual machines using RDP or SQL Server Management Studio, connect via public IP, and manage SQL Server databases directly from the VM or locally.

Plan your Azure VM storage by choosing between unmanaged and managed disks, understanding IOPS limits, and selecting standard (HDD) or premium (SSD) storage to optimize performance and cost.

Explore how Azure virtual networks (VNets) enable secure connectivity between Azure resources and virtual machines, with address space, subnets, DNS options, site-to-site VPNs, and automation templates.

Explore the advantages of moving on-premises SQL Server to an Azure virtual machine, including bring-your-own-license options, cost efficiencies, rapid deployment, scalability, disaster recovery, security, and compliance.

Provision a SQL Server virtual machine in Azure by selecting an operating system with SQL Server, then configure VM settings, connectivity, authentication, storage, patching, backups, and Azure Key Vault integration.

Discover how to provision sql server in Azure BYOL, choosing between a BYOL VM and installing from media via RDP, including sharing a local drive for the ISO.

Learn how SQL Server memory behaves in Azure Virtual Machines and configure min and max server memory per instance to prevent contention and optimize performance when running multiple instances.

Learn to set min and max memory for a SQL Server instance on a VM to avoid dynamic memory contention, using SQL Server Management Studio and megabyte calculations.

Master SQL Server Agent for task automation and alerts, including scheduled jobs, automatic startup, and alert responses, with emphasis on the SQL Server service, MSDB, and the Windows Application Log.

Configure operators and alerts in SQL Server Agent, enable Database Mail for email notifications, and create an operator on an Azure VM SQL Server to receive alerts.

Configure Azure SQL Server agent based alerts that notify operators or execute jobs, by using SQL Server Management Studio on an Azure virtual machine.

Learn to implement built-in Azure alerts for SQL Server virtual machines, including metric and activity log alerts, threshold-based triggers, and automated responses via email, webhooks, or Azure Automation runbooks.

Discover Azure SQL Database security, including access control, authentication, data in transit and at rest protection (TDE and Always Encrypted), and dynamic data masking, auditing, and compliance.

Explore how Azure SQL Database enforces access control via physical, network, and data access layers, using firewalls, contained databases, server and database roles, and Azure Active Directory or SQL authentication.

Explore transparent data encryption (TDE) for SQL Server and Azure SQL databases. It encrypts data at rest in real time using a database encryption key and a built-in server certificate.

Learn to enable transparent data encryption (TDE) for Azure SQL databases using the Azure Portal GUI, Transact-SQL, or PowerShell, with simple on/off encryption controls.

Explore dynamic data masking in SQL Server, applying database-level masking rules to protect sensitive data for non-privileged users, with default, credit card, email, random, and custom string masks.

Learn to implement dynamic data masking in Azure SQL Database by applying masks to sensitive fields and testing masked results in SQL Server Management Studio.

Use always encrypted to protect data at rest and on the move, with client applications holding the encryption keys so admins cannot view it.

Explore strategies for migrating on premise SQL Server databases to Azure, plan for incompatibilities, and leverage Microsoft resources like the Data Migration Assistant to ease cloud migration.

Assess on-premises SQL databases with the data migration assistant to identify compatibility issues. Prepare fixes with transact-sql scripts, create a consistent copy, and migrate via backpack file to Azure SQL.

Explore migrating an on-premises sql server to Azure SQL database. Compare downtime and zero-downtime methods using data migration assistant, transactional replication, SSMS, and the SQL package.

Explore practical options for migrating an on-premises SQL Server to a SQL Server instance on an Azure Virtual Machine, including replication, Always On, backups, and deployment wizards.

Tune performance and monitor Azure SQL with Azure Monitor, Application Insights, and Log Analytics. Create dashboards, drill into errors in Visual Studio, and use historical queries to resolve issues.

Explore how to use Azure Monitor to create custom dashboards that visualize activity logs and metrics for a VM, pin charts, and set alerts.

Discover Azure availability sets to boost high availability by distributing virtual machines across update and fault domains, with automatic placement and guidance on creating VMs within a set.