The Ultimate Study Guide for Exam 70-764: Administering a SQL Database Infrastructure

- Overview of the exam objectives and format - How to prepare for the exam: study materials, practice tests, tips and tricks H2: Configure Data Access and Auditing - Skill 1.1: Configure encryption - Skill 1.2: Configure data access and permissions - Skill 1.3: Configure auditing H2: Manage Backup and Restore of Databases - Skill 2.1: Develop a backup strategy - Skill 2.2: Restore databases - Skill 2.3: Implement point-in-time recovery H2: Manage and Monitor SQL Server Instances - Skill 3.1: Monitor database activity and queries - Skill 3.2: Manage indexes and statistics - Skill 3.3: Manage log shipping H2: Manage High Availability and Disaster Recovery - Skill 4.1: Implement AlwaysOn Availability Groups - Skill 4.2: Implement failover cluster instances - Skill 4.3: Manage database mirroring H1: Conclusion - Summary of the main points - Call to action: download the book and take the exam - FAQs Table 2: Article with HTML formatting Exam Ref 70-764 Administering a SQL Database Infrastructure: What You Need to Know

If you are a database administrator who wants to demonstrate your skills and knowledge in SQL Server 2016, you might be interested in taking the Microsoft Exam 70-764. This exam is one of the two exams required to earn the MCSA: SQL 2016 Database Administration certification, which validates your ability to install, maintain, and configure SQL Server databases.

Exam Ref 70-764 Administering a SQL Database Infrastructure download

In this article, we will give you an overview of what Exam 70-764 is about, what topics it covers, how you can prepare for it, and where you can download the official study guide for it. By the end of this article, you will have a clear idea of what to expect from this exam and how to ace it.

Overview of Exam 70-764

Exam 70-764 is titled "Administering a SQL Database Infrastructure". It is intended for database administrators who are responsible for tasks such as setting up database systems, ensuring their efficient operation, storing and backing up data, and securing data from unauthorized access.

The exam consists of 40-60 questions that are either multiple-choice, drag-and-drop, or scenario-based. You have 120 minutes to complete the exam and you need to score at least 700 out of 1000 points to pass it.

The exam measures your expertise in four main areas:

• Configure data access and auditing

• Manage backup and restore of databases

• Manage and monitor SQL Server instances

• Manage high availability and disaster recovery

Each area has three sub-skills that you need to master in order to pass the exam. We will go over each sub-skill in more detail in the following sections.

Configure Data Access and Auditing

This area covers how to configure encryption, data access and permissions, and auditing for SQL Server databases. You need to know how to:

Skill 1.1: Configure encryption

You need to know how to implement different types of encryption for SQL Server data, such as:

• Column-level encryption: encrypting specific columns in a table using symmetric or asymmetric keys.

• Always Encrypted: encrypting sensitive data on the client side before sending it to the database server.

• Transparent data encryption (TDE): encrypting the entire database file and the backup files on the disk.

• Backup encryption: encrypting the backup files using a certificate or a symmetric key.

• Encryption for connections: encrypting the network traffic between the client and the server using SSL or TLS.

You also need to know how to troubleshoot encryption errors, such as invalid certificates, key management issues, or performance problems.

Skill 1.2: Configure data access and permissions

You need to know how to create and maintain users and custom roles for SQL Server databases, and how to grant or revoke permissions on database objects, such as tables, views, stored procedures, or functions. You also need to know how to configure row-level security (RLS) and dynamic data masking (DDM) to limit the data that users can see or modify based on their roles or attributes.

Additionally, you need to know how to configure user options for Azure SQL Database, such as firewall rules, authentication methods, or service tiers.

Skill 1.3: Configure auditing

You need to know how to configure an audit on SQL Server or Azure SQL Database, and how to query, manage, and analyze the audit logs and reports. You need to understand the different types of audit actions, such as server-level or database-level events, and the different audit destinations, such as the Windows Security log, the Windows Application log, or a file.

Manage Backup and Restore of Databases

This area covers how to develop a backup strategy, restore databases, and implement point-in-time recovery for SQL Server databases. You need to know how to:

Skill 2.1: Develop a backup strategy

You need to know how to plan and implement a backup strategy for SQL Server databases, taking into account factors such as recovery models, backup types, backup frequency, backup retention, backup compression, backup verification, and backup encryption. You also need to know how to use tools such as SQL Server Management Studio (SSMS), Transact-SQL (T-SQL), PowerShell, or Azure Portal to perform backups.

Skill 2.2: Restore databases

You need to know how to restore SQL Server databases from backups, using tools such as SSMS, T-SQL, PowerShell, or Azure Portal. You need to understand the different restore scenarios, such as full restore, partial restore, piecemeal restore, or tail-log restore. You also need to know how to troubleshoot restore errors, such as missing log files, corrupted backups, or insufficient disk space.

Skill 2.3: Implement point-in-time recovery

You need to know how to implement point-in-time recovery for SQL Server databases, which allows you to restore a database to a specific point in time in the past. You need to understand the prerequisites and limitations of point-in-time recovery, such as the recovery model, the backup chain, and the log sequence numbers (LSNs). You also need to know how to use tools such as SSMS, T-SQL, PowerShell, or Azure Portal to perform point-in-time recovery.

Manage and Monitor SQL Server Instances

This area covers how to monitor database activity and queries, manage indexes and statistics, and manage log shipping for SQL Server instances. You need to know how to:

Skill 3.1: Monitor database activity and queries

Skill 3.2: Manage indexes and statistics

You need to know how to manage indexes and statistics on SQL Server databases to improve query performance and optimize storage. You need to understand the different types of indexes, such as clustered, nonclustered, filtered, columnstore, or spatial indexes, and how to create, modify, or drop them using tools such as SSMS, T-SQL, PowerShell, or Azure Portal. You also need to know how to monitor index usage and fragmentation, and how to perform index maintenance tasks, such as reorganizing or rebuilding indexes.

Additionally, you need to know how to manage statistics on SQL Server databases, which are used by the query optimizer to estimate the selectivity and cardinality of queries. You need to know how to create, update, or delete statistics using tools such as SSMS, T-SQL, PowerShell, or Azure Portal. You also need to know how to monitor statistics usage and accuracy, and how to troubleshoot statistics-related issues.

Skill 3.3: Manage log shipping

You need to know how to manage log shipping for SQL Server databases, which is a technique that allows you to automatically send transaction log backups from a primary server to one or more secondary servers. Log shipping can be used for disaster recovery or high availability purposes. You need to understand the components and prerequisites of log shipping, such as the backup server, the copy folder, the restore mode, and the network share. You also need to know how to configure, monitor, and troubleshoot log shipping using tools such as SSMS, T-SQL, PowerShell, or Azure Portal.

Manage High Availability and Disaster Recovery

This area covers how to implement AlwaysOn Availability Groups, failover cluster instances, and database mirroring for SQL Server databases. You need to know how to:

Skill 4.1: Implement AlwaysOn Availability Groups

Skill 4.2: Implement failover cluster instances

You need to know how to implement failover cluster instances for SQL Server databases, which is a feature that provides high availability by using Windows Server Failover Clustering (WSFC) to create a virtual server that can fail over to another node in the cluster. You need to understand the concepts and components of failover cluster instances, such as shared storage, cluster network name, cluster IP address, and possible owners. You also need to know how to create, configure, monitor, and troubleshoot failover cluster instances using tools such as SSMS, T-SQL, PowerShell, or Failover Cluster Manager.

Skill 4.3: Manage database mirroring

You need to know how to manage database mirroring for SQL Server databases, which is a feature that provides high availability and disaster recovery by maintaining a copy of a database on another server. You need to understand the concepts and components of database mirroring, such as principal server, mirror server, witness server, operating modes, endpoints, and certificates. You also need to know how to set up, configure, monitor, and troubleshoot database mirroring using tools such as SSMS, T-SQL, PowerShell, or Database Mirroring Monitor.

Conclusion

In this article, we have given you an overview of what Exam 70-764 is about, what topics it covers, how you can prepare for it, and where you can download the official study guide for it. We hope that this article has helped you get a clear idea of what to expect from this exam and how to ace it.

If you are ready to take your SQL Server skills to the next level and earn the MCSA: SQL 2016 Database Administration certification, we recommend that you download the Exam Ref 70-764 Administering a SQL Database Infrastructure book from Microsoft Press Store or O'Reilly. This book will provide you with comprehensive coverage of the exam objectives, strategic scenarios to challenge you, and practice questions to test your knowledge.

Don't wait any longer. Download the book today and start preparing for Exam 70-764. You can do it!

FAQs

Here are some frequently asked questions about Exam 70-764:

• How much does the exam cost?

• Where can I take the exam?

• How long is the exam valid for?

• What are the prerequisites for the exam?

• What are the benefits of passing the exam?

Here are the answers:

• The exam costs $165 USD in the United States. The price may vary depending on your location and currency.

• You can take the exam online or at a Pearson VUE testing center near you. You can schedule your exam at https://www.microsoft.com/en-us/learning/exam-70-764.aspx.

• The exam is valid for three years from the date you pass it. You can renew your certification by taking a recertification exam or a higher-level exam.

• regularly storing and backing up data, and securing data from unauthorized access.

• The benefits of passing the exam are:

• You will demonstrate your real-world mastery of skills for database administration.

• You will earn credit toward the MCSA: SQL 2016 Database Administration certification, which can boost your career prospects and credibility.

• You will gain access to the Microsoft Certified Professional (MCP) community, which offers exclusive benefits such as discounts, networking opportunities, and recognition.

71b2f0854b