Study plan for “Administering a SQL Database Infrastructure” || Exam 70-764

-

To study or understand something, we have to create or follow better plan. Dividing topics into sections gives us direction for learning. also, things do not get messed up.

The same way Microsoft has divided IMP topics in below chapters.

1)  Configure data access and auditing (Security)

a.    Configure Encryption.

       i.    Cell- level Encryption (Column Level Encryption)
       ii.   Implement Always Encrypted.
       iii.  Implement backup encryption.
       iv.  Configure transparent data encryption. (TDE)
       v.   Configure encryption for connections.
       vi.  Troubleshoot encryption errors.

b.    Configure data access and permissions

       i.    Manage database object permissions
       ii.   create and maintain users
       iii.  create and maintain custom roles
       iv.  configure user options for Azure SQL Database
       v.   configure row-level security
       vi.  configure dynamic data masking

c.    Configure auditing

       i.    Configure an audit on SQL Server.
       ii.   query the SQL Server audit log.
       iii.  manage a SQL Server audit
       iv.  configure an Azure SQL Database audit
       v.   analyse audit logs and reports from Azure SQL Database.

2)   Manage backup and restore of databases (Backup & Maintenance)

a.    Develop a backup strategy

       i.    Backup database
       ii.   configure alerting for failed backups
       iii.  back up databases to Azure
       iv.  manage transaction log backups
       v.   configure database recovery models
       vi.  configure backup automation

b.    Restore databases

       i.    Perform piecemeal restores
       ii.   Perform page recovery
       iii.  Perform point-in-time recovery
       iv.  restore file groups
       v.   develop a plan to automate and test restores

c.    Manage database integrity

       i.    Implement database consistency checks
       ii.   identify database corruption
       iii.  recover from database corruption

3)   Manage and monitor SQL Server instances (Monitoring and Performance tuning)

a.    Monitor database activity

       i.    Monitor current sessions
       ii.   identify sessions that cause blocking activity
       iii.  identify sessions that consume tempdb resources
       iv.  configure the data collector

b.    Monitor queries

       i.    Manage the Query Store
       ii.   configure Extended Events and trace events
       iii.  identify problematic execution plans
       iv.  troubleshoot server health using Extended Events

c.    Manage indexes

       i.    Identify and repair index fragmentation
       ii.   identify and create missing indexes
       iii.  identify and drop underutilized indexes
       iv.  manage existing columnstore indexes

d.    Manage statistics

       i.    Identify and correct outdated statistics
       ii.   implement Auto Update Statistics
       iii.  implement statistics for large tables

e.    Monitor SQL Server instances

       i.    Create and manage operators
       ii.   create and manage SQL Agent alerts
       iii.  define custom alert actions
       iv.  define failure actions
       v.   configure database mail
       vi.  configure Policy-Based Management
       vii. identify available space on data volumes
       viii.identify the cause of performance degradation

4)   Manage high availability and disaster recovery (DR and HA Solution)

a.    Implement log shipping

       i.   Configure log shipping
       ii.  monitor log shipping

b.    Implement AlwaysOn Availability Groups

       i.   Configure Windows clustering
       ii.  create an availability group
       iii. configure read-only routing
       iv. manage failover
       v.  create distributed availability groups

c.    Implement failover cluster instances

       i.   Manage shared disks
       ii.  configure cluster shared volumes

Ref: https://www.microsoft.com/en-us/learning/exam-70-764.aspx


Note: Exam 70-764 will be retiring soon. For more details clickhere

Comments

Post a Comment

Popular posts from this blog

What is DACPAC and how to use it?

-
Image
Few days before someone asked me a question about DACPAC and BACPAC. To be honest I was really not aware of it and I replied that I have heard these terms for the first time. That person replied DACPAC is used for deploying and upgrading database objects. Even after the clear statement, my reply was like developers would answer this better. Even though DBAs are responsible for migration and deployments. After few days, I got a requirement to migrate database schema (Only Schema) to another instance. In such a case most of us would have used the traditional way to script out the database objects and run the script on another instance. But what if there are many objects? I was exploring the easiest way to migrate the objects and got to know about DAC. After reading about DAC, I felt very sorry myself that I was not aware of such a simplest way of migrating and deploying database objects. I went to my friends who are senior developers and asked this question. None of them was aware ...
Read more

What’s the difference between a hotfix, cumulative update, Service Pack, and Feature Pack?

-
Hotfixes: A hotfix is created to address a specific issue, problem, or customer scenario. A hotfix can address either a single issue or a cumulative set of issues. Hotfixes are distributed only to those customers, partners, and organizations that Microsoft technical support personnel determine can benefit from the changes that are made to the code. Each hotfix includes documentation that indicates what files, tables, code, or functions are changed by the hotfix. Cumulative updates: A cumulative update (CU) is an update that contains all previous hotfixes to date. Additionally, a CU contains fixes for issues that meet the criteria for hotfix acceptance. These criteria may include the availability of a workaround, the effect on the customer, the reproducibility of the problem, and the complexity of the code that must be changed.           Note: Any hotfixes that you previously applied are not included i...
Read more

Always on setup end to end - Part 1 || Pre-requisites

-
Image
Hello readers,  Welcome to the new blog after a long time. In this blog I would like to discuss complete end to end always on setup guide for beginners. I will be posting couple of blogs regarding always on in SQL server. I believe that would be helpful to understand Always on in SQL server in easy way. Always on setup involved multiple steps. Hence one may find it difficult. However, if you could plan it well then you may find that always on is much easier to configure and handle. So, without wasting the time lets proceed further. Firstly, we will see what is Always on in SQL Server ? Always on is the high availability and disaster recovery solution introduced in SQL server 2012. As the name suggest, Always on means availability of database all the time even in case of disaster. We can say it is the alternative of mirroring. Only thing is windows failover cluster service also required to be configured as a pre-requisite for always on. In this blog, we will only d...
Read more