Microsoft Certified Solutions Associate (MCSA) (SQL 2016)

From Minor Miracle Software
Jump to: navigation, search

MCSA SQL 2016[1]

Requires two exams:
70-764
70-765



Objectives

  • Manage and monitor SQL Server instances
  • Manage high availability and disaster recovery
  • Configure data access and auditing
  • Manage backup and restore databases

Passing score 80%.

Question Pool is 150.

https://www.amazon.com/MCSA-2016-Database-Administration-2-pack/dp/067233805X/ref=sr_1_2?ie=UTF8&qid=1523884181&sr=8-2&keywords=70-764+administering+a+sql+database+infrastructure $64.00

PluralSight

More like 30 courses

  • sql server 2016
  • Applying SQL Server 2016 Features to Real-world Data Problems (Course)
  • SQL Server: Installing and Configuring SQL Server 2016 - done
  • What's New in SQL Server 2016 Reporting Services

Main Article: Pluralsight_SQL_Server_Standard_Edition_Survival_Guide (Course)


Leonard Lobel SSMS vs SSDT SQL Server management Studio (SSMS)

SQL Server Data Tools (SSDT)

DIE (Drop If Exists)

Always Encrypted 2016 Keys are kept with the client. Cloud provide can not decrypt your data. 2008 Keys are stored in the database, allowing cloud provider to decrypt your data. Connection string, new setting, "Column Encryption Setting=Enabled". Encrypted columns are case-sensitive Need ADO.NET to connect and provide cypher services. CMK Rotation - Create new CMK then decrypt CEKs and re-encrypt them with the new CMK.

Limitations
 No support for xml, rowversion, image, ntext, text, sql_variant, hierarchyid, geography, geometry.
 - FILESTREAM, ROWGUIDCOL, IDENTITY, indexes, columns with default constraints, temporal tables, 
 - Stretch database.

Stretch Database - Database tables on Azure and local.

JSON and SQL Server JSON is just text so store as nvarchar(max), no native "json" data type. No custom JSON indexes. FOR JSON Clause, auto, and path Good authors

Lenni Lobel
Robert Caine.

- done with the course.

2016 Mobile Report Publisher, phone, tablet, laptop, web. - done.


2018-04-30 pluralsight SQL Server: Upgrading and Migrating to SQL Server 2016 by Glenn Berry SHOULD run on Windows Server 2016

1 Enterprise - only supports Encryption at rest and in transit
2 Standard
3 Web - aimed at web hosting
4 Developer - free
5 Express - evil incarnate

Limits

Sockets
Cores
Memory
Physical vs. Virtual

Clustered Columnstore Index

Upgrade Testing

Windows Server 2016 Standard is sufficient.
Many default settings are incorrect.
upgrade issues
 System Configuration Checker - install check
 Data Migration Assistant
 Query for 'SQL Server Deprecated Features' in PerfMon.
 

Clean Up Legacy Databases

Archive or delete old data
Defrag indexes
Drop unused or duplicate indexes
Shrink Log files.
Database Instant File Initialization - 2014+
Microsoft Robocopy
Microsoft Database Experimentation Assistant (DEA) - new tool.
Comparing canned queries for efficiency on the legacy vs. SQL 2016.
Reviewing Query Plans, changes might be needed. Efficient on 2008 might not be efficient on 2016.
 Power Management
 Cost threshold for parallelism
 Max Degree of Parallelism
 Database compatibility level
 Tempdb configuration
Upgrades
 Upgrade in place, update legacy SQL Server
 Side-by-Side, install SQL 2016 on legacy hardware. Not a good choice but does eliminate network problems when upgrading. Then, migrate the new database to SQL 2016 with new hardware and Windows Server.
 Log Shipping - 
 Database Mirroring
 -- done

Applying SQL Server 2016 Features to Real-world Data Problems

2h 36m
Main Article: pluralsight Applying SQL Server 2016 Features to Real-world Data Problems

Internal Links

Parent Article: Main Page