Microsoft Certified Solutions Associate (MCSA) (SQL 2016)
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.
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