Exam 70-465
Designing Database Solutions for Microsoft SQL Server
Published: June 11, 2012
Languages: English, Chinese (Simplified), French, German, Japanese, Portuguese (Brazil)
Audiences: IT professionals
Technology: Microsoft SQL Server
Credit toward certification: MCP, MCSE
Skills measured
This exam measures your ability to accomplish the technical tasks listed below. The percentages indicate the relative weight of each major topic area on the exam. The higher the percentage, the more questions you are likely to see on that content area on the exam. View video tutorials about the variety of question types on Microsoft exams.
Please note that the questions may test on, but will not be limited to, the topics described in the bulleted text.
Do you have feedback about the relevance of the skills measured on this exam? Please send Microsoft your comments. All feedback will be reviewed and incorporated as appropriate while still maintaining the validity and reliability of the certification process. Note that Microsoft will not respond directly to your feedback. We appreciate your input in ensuring the quality of the Microsoft Certification program.
If you have concerns about specific questions on this exam, please submit an exam challenge.
As of February 18, 2016, this exam includes content covering both SQL Server 2012 and 2014. Please note that this exam will not include questions on features or capabilities that are present only in the SQL Server 2012 product. To learn more about these changes and how they affect the skills measured, please download and review this document.
Design a database structure (20-30%)
Design for business requirements
Business to data translations, identify which SQL Server components to use to support business requirements, design a normalization area, de-normalize technically (versus by remodeling) by using SQL Server features (materialization via indexed views and more)
Design physical database and object placement
Design a physical database, including file placement, FILESTREAM, FILETABLE, file groups, and RAID; configure system database settings
Design a table and index partitioning strategy
Develop the optimal strategy for indexing, archive using partitions and tables, design columnstore indexes, design XML indexes
Design a migration, consolidation, and upgrade strategy
Upgrade with minimal downtime; design a cross-cluster migration; plan a database deployment, including Windows PowerShell, Server Core, and contained databases; migrate to SQL Database; migrate query plans; design a migration strategy using Distributed Replay Controller; design a SQL Server virtualization strategy
Design SQL Server instances
Identify hardware for new instances; design CPU affinity; design clustered instances using Microsoft Distributed Transaction Control (MSDTC); define instance memory allocation; design installation strategies, including sysprep, slipstream, and SMB file server; define cross db ownership chaining
Design backup and recovery
Design a backup strategy based on business needs, including differential, file, log, striped, and Microsoft Azure Blob Storage Service; design a database snapshot strategy; design appropriate recovery models; design a system database backup strategy; recover Tail-Log backups
Preparation resources
Create indexed views
Move user databases
Partitioned tables and indexes
Design databases and database objects (30-35%)
Design a database model
Design a logical schema; design a data access and data layer architecture; design a database schema; design a security architecture; design a cross-server instance database model, including linked servers, security, providers, distributed transactions, distributed partitioned views, and Service Broker
Design tables
Design tables appropriately, including physical tables, temp tables, temp table variables, common table expressions, commonstore indexes, user defined table types, FILESTREAM, FILETABLE, and In-Memory OLTP; design views and table valued functions; design a compression strategy, including row and page; select an appropriate data type; design computed columns
Design for concurrency
Develop a strategy to maximize concurrency; define a locking and concurrency strategy; design a transaction isolation strategy, including server database and session; design triggers for concurrency
Design T-SQL stored procedures
Create stored procedures; design a data access strategy using stored procedures; design appropriate stored procedure parameters, including input, output, and Table Valued; design error handling; design an In-Memory OLTP strategy for stored procedures
Design a management automation strategy
Create a data archiving solution; design automation and auditing, including jobs, alerts, operators, SSIS, CDC, auditing, DDL triggers, and Windows PowerShell; automate across multiple databases and instances; design data batch processing: design a database load test; deploy to different environments, including development, staging, and production
Design for transactions
Manage transactions, including time, savepoint, and mark; design for implicit and explicit transactions; ensure data integrity by using transactions; design error handling for transactions, including TRY, CATCH, and THROW
Preparation resources
ALTER DATABASE SET options (Transact-SQL)
In-Memory OLTP (In-Memory Optimization)
Design database security (15-20%)
Design an application strategy to support security
Design security, including security roles, signed stored procedures, encryption, contained logins, EXECUTE AS, and credentials; implement schemas and schema security; design security maintenance, including SQL logins, integrated authentication, permissions, and mirroring
Design database, schema, and object security permissions
Design a database schema that meets security requirements, schema ownership, ownership chaining, cross database chaining
Design instance-level security configurations
Implement separation of duties using different login roles; choose an authentication type, including logon triggers, regulatory requirements, and certificates; implement data encryption, including database master key and configuration; implement Data Description Language (DDL) triggers; define a secure service account
Preparation resources
Tutorial: Signing stored procedures with a certificate
cross db ownership chaining server configuration option
DDL triggers
Design a troubleshooting and optimization solution (20-25%)
Design a maintenance strategy for database servers
Design maintenance plans; design index maintenance, including rebuild, defragmentation, statistics, online rebuilds, offline rebuilds, and thresholds; maintain physical and logical consistency (DBCC); manage database files, including LDF, MDF, In-Memory OLTP, and garbage collection; define a retention policy
Troubleshoot and resolve concurrency issues
Examine deadlocking issues using SQL Server logs and trace flags; design a reporting database infrastructure, including replicated databases; monitor concurrency, including Dynamic Management Views (DMV); diagnose blocking, including live locking and deadlocking; diagnose waits; use Extended Events; implement query hints to increase concurrency
Design and implement a high availability solution
Configure failover clustering, including multi-subnet; design readable mirrors; create a highly available configuration with low recovery time; design and ensure uptime requirements, including monitoring and patching; design and implement a database mirroring architecture; design and implement a replication architecture; implement a mirroring solution, including AlwaysOn and Availability Groups; design geographical fault-tolerance using Microsoft Azure SQL Database
Design a solution to monitor performance and concurrency
Identify performance monitor counters; monitor for performance and bottlenecks, including Wait Stats; design a query monitoring and review strategy; monitor for missing statistics
Design a monitoring solution at the instance level
Design auditing strategies, including Extended Events, Event traces, SQL Audit, Profiler-scheduled or event-based maintenance, Performance Monitor, and DMV usage; set up file and table growth monitoring; collect performance indicators and counters; create jobs to monitor server health; audit using Windows Logs
Preparation resources
sys.dm_tran_locks (Transact-SQL)
Overview of AlwaysOn Availability Groups (SQL Server)
Monitoring and troubleshooting merge for data and delta file pairs
Make The Best Choice Chose - Examkingdom
Reday to get certified today competitive computer industry Examkingdom's preparation material includes the most excellent features, prepared by the same dedicated experts who have come together to offer an integrated solution. We provide the most excellent and simple method to pass your Microsoft MCSA: SQL Server 2012 70-465 exam on the first attempt "GUARANTEED".
Unlimited Access Package
will prepare you for your exam with guaranteed results, 70-465 Study Guide. Your exam will download as a single 70-465 PDF or complete 70-465 testing engine as well as over +4000 other technical exam PDF and exam engine downloads. Forget buying your prep materials separately at three time the price of our unlimited access plan - skip the 70-465 audio exams and select the one package that gives it all to you at your discretion: 70-465 Study Materials featuring the exam engine.
Examkingdom 70-465 Exam Prepration Tools
Examkingdom Microsoft MCSA: SQL Server 2012 preparation begins and ends with your accomplishing this credential goal. Although you will take each Microsoft MCSA: SQL Server 2012 online test one at a time - each one builds upon the previous. Remember that each Microsoft MCSA: SQL Server 2012 exam paper is built from a common certification foundation.
70-465 Exam Testing Engines
Beyond knowing the answer, and actually understanding the 70-465 test questions puts you one step ahead of the test. Completely understanding a concept and reasoning behind how something works, makes your task second nature. Your 70-465 quiz will melt in your hands if you know the logic behind the concepts. Any legitimate Microsoft MCSA: SQL Server 2012 prep materials should enforce this style of learning - but you will be hard pressed to find more than a Microsoft MCSA: SQL Server 2012 practice test anywhere other than Certkingdom.
70-465 Exam Questions and Answers with Explanation
This is where your Microsoft MCSA: SQL Server 2012 70-465 exam prep really takes off, in the testing your knowledge and ability to quickly come up with answers in the 70-465 online tests. Using MCSA: SQL Server 2012 70-465 practice exams is an excellent way to increase response time and queue certain answers to common issues.
70-465 Exam Study Guides
All Microsoft MCSA: SQL Server 2012 online tests begin somewhere, and that is what the Microsoft MCSA: SQL Server 2012 training course will do for you: create a foundation to build on. Study guides are essentially a detailed Microsoft MCSA: SQL Server 2012 70-465 tutorial and are great introductions to new Microsoft MCSA: SQL Server 2012 training courses as you advance. The content is always relevant, and compound again to make you pass your 70-465 exams on the first attempt. You will frequently find these 70-465 PDF files downloadable and can then archive or print them for extra reading or studying on-the-go.
70-465 Exam Video Training
For some, this is the best way to get the latest Microsoft MCSA: SQL Server 2012 70-465 training. However you decide to learn 70-465 exam topics is up to you and your learning style. The Examkingdom Microsoft MCSA: SQL Server 2012 products and tools are designed to work well with every learning style. Give us a try and sample our work. You'll be glad you did.
70-465 Other Features
* Realistic practice questions just like the ones found on certification exams.
* Each guide is composed from industry leading professionals real Microsoft MCSA: SQL Server 2012notes, certifying 100% brain dump free.
* Study guides and exam papers are guaranteed to help you pass on your first attempt or your money back.
* Designed to help you complete your certificate using only
* Delivered in PDF format for easy reading and printing Examkingdom unique CBT 70-465 will have you dancing the Microsoft MCSA: SQL Server 2012 jig before you know it
* MCSA: SQL Server 2012 70-465 prep files are frequently updated to maintain accuracy. Your courses will always be up to date.
Get MCSA: SQL Server 2012 ebooks from Examkingdom which contain real 70-465 exam questions and answers. You WILL pass your MCSA: SQL Server 2012 exam on the first attempt using only Examkingdom's MCSA: SQL Server 2012 excellent preparation tools and tutorials.
This is what our customers are saying about Examkingdom.com.
These are real testimonials.
Hi friends! Examkingdom.com is No1 in sites coz in
$25 I cant believe this but when I purchased the $25 package it was amazing I Microsoft passed 10 Exams using Examkingdom guides in one Month So many thanks to Examkingdom Team , Please continue this offer for next year also. So many Thanks
Mike CA
Thank You! I would just like to thank Examkingdom.com for the Microsoft MCSA: SQL Server 2012 70-465 test guide that I bought a couple months ago and I took my test and pass overwhelmingly. I completed the test of 231 questions in about 90 minutes I must say that their Q & A with Explanation are very amazing and easy to learn.
Jay Brunets
After my co-workers found out what I used to pass Microsoft MCSA: SQL Server 2012 70-465 the test, that many are thinking about purchasing
Examkingdom.com for their MCSA: SQL Server 2012 exams, I know I will again
John NA
I passed the Microsoft MCSA: SQL Server 2012 70-465 exam yesterday, and now it's on to security exam. Couldn't have done it with out you. Thanks very much.
Oley R.
Hello Everyone
I Just Passed The Microsoft MCSA: SQL Server 2012 70-465 Took 80 to 90 Minutes max to understand and easy to learn. Thanks For Everything Now On To 70-465
Robert R.
Hi
Examkingdom.com thanks so much for your assistance in Microsoft MCSA: SQL Server 2012 i passed today it was a breeze and i couldn't have done it without you. Thanks again
Seymour G.
I have used your Exam Study Guides for preparation for Microsoft MCSA: SQL Server 2012 70-465. I also passed all those on the first round. I'm currently preparing for the Microsoft and theMCSA: SQL Server 2012. exams
Ken T.
I just wanted to thank you for helping me get myMCSA: SQL Server 2012
$50 package for all guides is awesome you made the journey a lot easier. I passed every test the first time using your
Guide
Mario B.
I take this opportunity to express my appreciation to the authors of
Examkingdom.com Microsoft MCSA: SQL Server 2012 test guide. I purchased the 70-465 soon after my formal hands on training and honestly, my success in the test came out of nowhere but Examkingdom.com. Once again I say thanks
Kris H.
Dear
Examkingdom.com team the test no. 70-465 that i took was very good, I received 880 and could have gain more just by learning your exams
Gil L.
Hi and Thanks I have just passed the MCSA: SQL Server 2012 Directory Services Design exam with a score of 928 thanks to you! The guide was excellent
Edward T.
Great stuff so far....I love this site....!! I am also on the Microsoft MCSA: SQL Server 2012 I decided to start from Examkingdom and start learning study MCSA: SQL Server 2012 from home... It has been really difficult but so far I have managed to get through 4 exams....., now currently studying for the more exams.... Have a good day.................................................. Cheers
Ted Hannam
Thanks for your Help, But I have finally downloaded Microsoft MCSA: SQL Server 2012 70-465 exam preparation from examkingdom.com they are provided me complete information about the exam, lets hope I get success for the 70-465 exam, I found there exams very very realistic and useful. thanks again
lindsay Paul