Learn How to Provision SQL Databases for Exam 70-765 and MCSA Certification
Exam Ref 70-765 Provisioning SQL Databases Paul 12
If you are an IT professional who wants to demonstrate your real-world mastery of provisioning SQL Server databases both on premise and in SQL Azure, then you should consider taking Exam 70-765: Provisioning SQL Databases. This exam is one of the requirements for earning the MCSA: SQL 2016 Database Administration certification, which qualifies you for a position as a database administrator or infrastructure specialist.
Exam Ref 70-765 Provisioning SQL Databases Paul 12
In this article, we will give you an overview of what the exam covers, how to prepare for it, and what resources are available to help you succeed. We will also provide some tips and best practices for taking the exam and answer some frequently asked questions.
Implement SQL in Azure
One of the main topics of the exam is how to implement SQL in Azure, which is Microsoft's cloud computing platform. SQL in Azure offers many benefits, such as scalability, availability, security, and cost-effectiveness. However, it also poses some challenges, such as network latency, bandwidth limitations, and compatibility issues.
To implement SQL in Azure, you need to know how to provision SQL Server and Azure SQL Database instances, which are two different types of database services offered by Azure. SQL Server is a relational database management system that runs on virtual machines (VMs) in Azure. Azure SQL Database is a managed database service that runs on shared or dedicated resources in Azure.
You also need to know how to configure firewall rules and secure access to SQL in Azure, which are essential for protecting your data from unauthorized or malicious users. You can use Azure portal, PowerShell, Transact-SQL, or REST API to configure firewall rules for both SQL Server and Azure SQL Database. You can also use various authentication methods, such as SQL authentication, Active Directory authentication, or certificate authentication, to secure access to your databases.
Another important skill for implementing SQL in Azure is how to deploy and migrate databases to SQL in Azure, which are common scenarios for moving your data from on-premise or other cloud platforms to Azure. You can use various tools and methods for deploying and migrating databases, such as backup and restore, export and import, Data Migration Assistant (DMA), Data Migration Service (DMS), or Azure Database Migration Service (ADMS).
Manage databases and instances
Another major topic of the exam is how to manage databases and instances, which are essential for maintaining optimal performance and availability of your data. You need to know how to configure SQL Server performance settings and resource limits, which affect how your database engine uses CPU, memory, disk I/O, network I/O, and other resources. You can use various tools and methods for configuring performance settings and resource limits, such as Resource Governor, Database Engine Tuning Advisor (DTA), sp_configure stored procedure, or sys.configurations catalog view.
You also need to know how to manage SQL Server instances using PowerShell and Transact-SQL, which are powerful scripting languages that allow you to automate and execute various administrative tasks. You can use PowerShell cmdlets, such as Invoke-Sqlcmd, Get-SqlInstance, or Set-SqlMaxMemory, to perform common operations on SQL Server instances. You can also use Transact-SQL statements, such as ALTER SERVER CONFIGURATION, ALTER DATABASE, or CREATE LOGIN, to perform similar operations.
Another important skill for managing databases and instances is how to automate SQL Server maintenance tasks using jobs and alerts, which are essential for ensuring the health and reliability of your databases. You can use SQL Server Agent, which is a service that runs scheduled tasks, to create and manage jobs and alerts. You can also use Maintenance Plans, which are graphical tools that help you create and execute common maintenance tasks, such as backups, integrity checks, index rebuilds, or statistics updates.
Finally, you need to know how to monitor and troubleshoot SQL Server health and performance issues, which are inevitable in any database environment. You can use various tools and methods for monitoring and troubleshooting SQL Server, such as Dynamic Management Views (DMVs), Extended Events (XEvents), Performance Monitor (PerfMon), SQL Server Profiler, or SQL Server Error Log.
The last topic of the exam is how to manage storage, which is a critical factor for the performance and availability of your data. You need to know how to design and implement storage solutions for SQL Server and Azure SQL Database, which involve choosing the right type and size of storage for your databases. You can use various types of storage for SQL Server and Azure SQL Database, such as local disks, network disks, blob storage, or managed disks.
You also need to know how to configure disk storage, backup storage, and tempdb storage, which are important aspects of managing your database files. You can use various tools and methods for configuring disk storage, such as Disk Management, Storage Spaces, or Storage Pool. You can also use various tools and methods for configuring backup storage, such as Backup Devices, Backup Media Sets, or Backup Retention Policies. You can also use various tools and methods for configuring tempdb storage, such as Trace Flags, Startup Parameters, or Database Properties.
Another important skill for managing storage is how to manage database files and filegroups, which are logical structures that organize your data on disk. You can use various tools and methods for managing database files and filegroups, such as File Explorer, SSMS Object Explorer, or Transact-SQL statements. You can also use various operations on database files and filegroups, such as creating, modifying, deleting, moving, shrinking, or growing.
Finally, you need to know how to implement data compression, encryption, and partitioning, which are advanced techniques that help you optimize your storage usage and security. You can use various tools and methods for implementing data compression, such as Data Compression Wizard, sp_estimate_data_compression_savings stored procedure, or CREATE TABLE statement with DATA_COMPRESSION option. You can also use various tools and methods for implementing encryption, such as Transparent Data Encryption (TDE), Always Encrypted (AE), or CREATE DATABASE ENCRYPTION KEY statement. You can also use various tools and methods for implementing partitioning, such as Partition Function Wizard, sp_estimate_data_compression_savings stored procedure, or CREATE PARTITION FUNCTION statement.
In this article, we have given you an overview of what Exam 70-765: Provisioning SQL Databases covers, how to prepare for it, and what resources are available to help you succeed. We have also provided some tips and best practices for taking the exam and answered some frequently asked questions.
If you want to demonstrate your real-world mastery of provisioning SQL Server databases both on premise and in SQL Azure, then you should consider taking this exam and earning the MCSA: SQL 2016 Database Administration certification. This certification will validate your skills and knowledge and qualify you for a position as a database administrator or infrastructure specialist.
To prepare for the exam, you should review the exam objectives and skills measured by the exam and study the topics in depth. You should also practice with hands-on labs and mock exams to test your understanding and identify your strengths and weaknesses. You should also use the official study guide and other resources to supplement your learning and review the key concepts.
Some of the resources that you can use to prepare for the exam are:
Exam 70-765: Provisioning SQL Databases, which is the official exam page that contains the exam objectives, skills measured, and preparation options
Provisioning SQL Databases (70-765) Path, which is a collection of online courses that cover the exam topics in detail
Provisioning SQL Databases - Microsoft Exam 70-765, which is an online course that covers the exam topics with lectures and labs
Provisioning SQL Databases (Microsoft Official Practice Test), which is a mock exam that simulates the real exam environment and provides feedback and explanations
Some of the tips and best practices that you can follow to take the exam are:
Read the exam questions carefully and understand what they are asking for
Eliminate the obviously wrong or irrelevant answers and focus on the remaining ones
Use the process of elimination and logic to narrow down the possible answers
Pay attention to the keywords and phrases in the questions and answers, such as "best", "most", "least", "not", "except", etc.
Manage your time wisely and don't spend too much time on a single question
Review your answers before submitting them and make sure they are correct and complete
Trust your instincts and don't second-guess yourself too much
Here are some frequently asked questions about the exam and their brief answers:
How many questions are on the exam and how long is it?The exam has 40-60 questions and lasts for 120 minutes.
What is the passing score for the exam?The passing score for the exam is 700 out of 1000.
What is the format of the exam questions?The exam questions can be multiple-choice, single-choice, drag-and-drop, case study, or scenario-based.
How much does the exam cost?The exam costs $165 USD in the United States and varies by country.
How can I register for the exam?You can register for the exam online through Pearson VUE or at a local testing center.