book

Training Kit Exam 70-462: Administering Microsoft® SQL Sever® 2012 Databases

by Peter Ward Orin Thomas and boB Taylor

July 2012

Intermediate to advanced

624 pages

19h 54m

English

Microsoft Press

Read now

Unlock full access

System RequirementsHardware RequirementsVirtualization Hardware RequirementsPhysical Hardware RequirementsSoftware RequirementsPractice Setup InstructionsPrepare a Computer to Function as a Windows Server 2008 R2 Domain ControllerPrepare AD DSPrepare a Member Server and Join It to the DomainPrepare a Second Member Server and Join It to the DomainPrepare a Third Member Server and Join It to the DomainPrepare a Fourth Member Server and Join It to the DomainPrepare a Computer Running the Server Core Installation Option and Join It to the DomainUsing the Companion CDHow to Install the Practice TestsHow to Use the Practice TestsHow to Uninstall the Practice TestsAcknowledgmentsErrata & Book SupportWe Want to Hear from YouStay in Touch
Before You BeginLesson 1: Planning Your InstallationEvaluating Installation RequirementsOperating System RequirementsProcessor and RAM RequirementsHard Disk RequirementsInstalling SQL Server 2012 on a Domain ControllerSoftware RequirementsVirtualization RequirementsDesigning the InstallationPlanning Scale Up versus Scale Out BasicsShrinking and Growing DatabasesIncreasing Database SizeDecreasing Database SizeDesigning the Storage for New DatabasesRemembering Capacity ConstraintsIdentifying a Standby Database for ReportingIdentifying Windows-Level Security and Service-Level SecurityPerforming a Core Mode InstallationBenchmarking a ServerPractice: Prepare for the Installation of SQL Server 2012Practice: Prepare for the Installation of SQL Server 2012Lesson SummaryLesson ReviewLesson 2: Installing SQL Server and Related ServicesConfiguring an Operating System DiskInstalling the SQL Server Database EngineInstalling SQL Server 2012 from the Command PromptInstalling SQL Server Integration ServicesEnabling and Disabling FeaturesInstalling SQL Server 2012 by Using a Configuration FileTesting ConnectivityPractice: Prepare for and Install SQL Server 2012Practice: Prepare for and Install SQL Server 2012Lesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Planning Deployment of SQL Server 2012Case Scenario 2: SQL Server DeploymentSuggested PracticesConfigure Additional Firewall Rules and Generate a Features Discovery ReportAdding and Removing Features and Adding Databases to SQL Server 2012 on a Computer Running a Server Core Operating SystemAnswersLesson 1Lesson 2Case Scenario 1Case Scenario 2
Before You BeginLesson 1: Configuring SQL Server InstancesInstance-Level SettingsConfiguring Memory AllocationConfiguring Processor and I/O AffinityConfiguring Fill FactorDatabase Configuration and StandardizationDistributed Transaction CoordinatorConfiguring Database MailPractice: Configure SQL Server 2012 Instance-Level SettingsPractice: Configure SQL Server 2012 Instance-Level SettingsLesson SummaryLesson ReviewLesson 2: Managing SQL Server InstancesInstalling Additional InstancesDeploying Software Updates and Patch ManagementConfiguring Resource GovernorResource PoolsWorkload GroupsResource Governor ClassificationUsing WSRM with Multiple Database Engine InstancesCycle SQL Server Error LogsPractice: Deploy and Configure Additional SQL Server 2012 InstancesPractice: Deploy and Configure Additional SQL Server 2012 InstancesLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Instance ConfigurationCase Scenario 2: Additional Instances and Error Log CyclingSuggested PracticesConfigure InstancesInstall and Manage Multiple InstancesAnswersLesson 1Lesson 2Case Scenario 1Case Scenario 2
Before You BeginLesson 1: Configuring Additional SQL Server ComponentsDeploying and Configuring Analysis ServicesDeploying and Configuring Reporting ServicesDeploying and Configuring SharePoint IntegrationConfiguring SQL Server Integration Services SecurityManaging Full-Text IndexingConfiguring FILESTREAMConfiguring FileTablesPractice: Install Analysis Services and Reporting ServicesPractice: Install Analysis Services and Reporting ServicesLesson SummaryLesson ReviewLesson 2: Managing and Configuring DatabasesDesigning and Managing FilegroupsAdding New FilegroupsMoving an Index from One Filegroup to AnotherConfiguring and Standardizing DatabasesUnderstanding Contained DatabasesUsing Data CompressionRow-Level CompressionUnicode CompressionPage-Level CompressionEstimating CompressionEncrypting Databases with Transparent Data EncryptionPartitioning Indexes and TablesManaging Log FilesUsing Database Console CommandsMaintenance StatementsInformational StatementsValidation StatementsMiscellaneous StatementsPractice: Table Partitioning, Compression, Encryption, and Log FilesLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Configuring FILESTREAM and FileTableCase Scenario 2: Deploying Transparent Data EncryptionSuggested PracticesFILESTREAM and FileTableTransparent Data Encryption and Table PartitioningAnswersLesson 1Lesson 2Case Scenario 1Case Scenario 2
Before You BeginLesson 1: Migrating to SQL Server 2012Upgrading an Instance to SQL Server 2012Upgrading from SQL Server 2005Upgrading from SQL Server 2008Upgrading from SQL Server 2008 R2Intra-Edition UpgradesUpgrade AdvisorPreparing for Upgrade with Distributed Replay UtilityPerforming an UpgradeMigrating a Database to a SQL Server 2012 InstanceDetach a DatabaseAttach a DatabaseCopying Databases to Other ServersCopy Database WizardCopying Databases with Backup and RestorePublishing a Database by Using Generate and Publish Scripts WizardMigrating SQL LoginsPractice: Migrate and Copy DatabasesPractice: Migrate and Copy DatabasesLesson SummaryLesson ReviewLesson 2: Exporting and Importing DataCopying and Exporting DataUsing the SQL Server Import and Export WizardUsing BCP to Import and Export DataImporting Data by Using BULK INSERTImporting Data by Using OPENROWSET(BULK)Using Format FilesPreparing Data for Bulk OperationsSelect IntoPractice: Bulk Export and Import of DataPractice: Bulk Export and Import of DataLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Consolidation at ContosoCase Scenario 2: Tailspin Toys Bulk DataSuggested PracticesImplement a Migration StrategyImport and Export DataAnswersLesson 1Lesson 2Case Scenario 1Case Scenario 2
Before You BeginLesson 1: Managing Logins and Server RolesSQL LoginsWindows-Authenticated SQL Server LoginsSQL Server–Authenticated LoginsCertificate AuthenticationAsymmetric Key AuthenticationAltering Existing LoginsLogin-Related Catalog ViewsRemoving LoginsDenying Server AccessServer RolesUser-Defined Server RolesCredentialsPractice: SQL Server Logins and Server RolesPractice: SQL Server Logins and Server RolesLesson SummaryLesson ReviewLesson 2: Managing Users and Database RolesDatabase UsersDatabase RolesFixed Database-Level RolesFlexible Database-Level Rolesmsdb RolesUser and Role Catalog ViewsContained UsersLeast PrivilegeApplication RolesPractice: Database Users and RolesPractice: Database Users and RolesLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Instance-Level Permissions for Contoso’s AccountantsCase Scenario 2: Contained Databases at FabrikamSuggested PracticesManage Logins and Server RolesManage Users and Database RolesAnswersLesson 1Lesson 2Case Scenario 1Case Scenario 2
Before You BeginLesson 1: Managing Database PermissionsUnderstanding SecurablesAssigning Permissions on ObjectsManaging Permissions by Using Database RolesAssigning Fixed Database RolesUsing Flexible Database RolesProtecting Objects from ModificationUsing SchemasDetermining Effective PermissionsPRACTICE: Configuring Database PermissionsPRACTICE: Configuring Database PermissionsLesson SummaryLesson ReviewLesson 2: Troubleshooting SQL Server SecurityTroubleshooting AuthenticationUsing Authentication ModesResolving Client Connection ProblemsTroubleshooting Certificates and KeysTroubleshooting EndpointsUsing Security Catalog ViewsPRACTICE: Troubleshooting SecurityPRACTICE: Troubleshooting SecurityLesson SummaryLesson ReviewLesson 3: Auditing SQL Server InstancesUsing SQL Server AuditCreating a Server AuditSQL Server Audit Action Groups and ActionsCreating a Server Audit SpecificationCreating a Database Audit SpecificationViewing SQL Server Audit Views and FunctionsConfiguring Login AuditingUsing c2 Audit ModeCommon Criteria CompliancePolicy-Based ManagementPRACTICE: Configuring SQL Server AuditPRACTICE: Configuring SQL Server AuditLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Configuring Database PermissionsCase Scenario 2: Troubleshooting SecurityCase Scenario 3: Auditing at FabrikamSuggested PracticesManage Database PermissionsTroubleshoot SecurityAudit SQL Server InstancesAnswersLesson 1Lesson 2Lesson 3Case Scenario 1Case Scenario 2Case Scenario 3

Before You BeginLesson 1: Mirroring DatabasesDatabase MirroringMirroring PrerequisitesFull Recovery ModelRecover by Using NO RECOVERYEndpoint Firewall RulesConfiguring Mirroring with Windows AuthenticationConfiguring Mirroring with Certificate AuthenticationChanging Operating ModesRole Switching and FailoverMonitoring Mirrored DatabasesDatabase Mirroring MonitorMonitoring Mirroring by Using Stored ProceduresUpgrading Mirrored DatabasesPractice Mirroring a DatabasePractice Mirroring a DatabaseLesson SummaryLesson ReviewLesson 2: Database ReplicationReplication ArchitectureReplication TypesSnapshot ReplicationConfiguring Snapshot ReplicationConfiguring a SubscriptionTransactional ReplicationPeer-to-Peer Transactional ReplicationMerge ReplicationReplication MonitorControlling Replication of Constraints, Columns, and TriggersHeterogeneous DataPractice Configuring ReplicationPractice Configuring ReplicationLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Database Mirroring at Coho VineyardCase Scenario 2: Database Replication at Tailspin ToysSuggested PracticesImplement Database MirroringImplement ReplicationAnswersLesson 1Lesson 2Case Scenario 1Case Scenario 2
Before You BeginLesson 1: Clustering SQL Server 2012Fulfilling Edition PrerequisitesWindows Server 2008 R2 as Shared StorageConnecting to the SAN by Using iSCSI InitiatorCreating a Windows Server 2008 R2 Failover ClusterInstalling a SQL Server Failover ClusterComplete the InstallationMulti-Subnet Failover ClusteringPerforming Manual FailoverTroubleshooting Failover ClustersPractice Building a SQL Server 2012 Failover ClusterPractice Building a SQL Server 2012 Failover ClusterLesson SummaryLesson ReviewLesson 2: AlwaysOn Availability GroupsWhat Are AlwaysOn Availability Groups?Meeting Availability Group PrerequisitesConfiguring Availability ModesSelecting Failover ModesConfiguring Readable Secondary ReplicasDeploying AlwaysOn Availability GroupsCreating an AlwaysOn EndpointEnabling AlwaysOn Availability GroupsCreating an Availability GroupCreating or Adding an Availability Group ListenerAdding Secondary ReplicasUsing Availability Groups on Failover Cluster InstancesPractice Deploying AlwaysOn Availability GroupsPractice Deploying AlwaysOn Availability GroupsLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Failover Cluster Instances at ContosoCase Scenario 2: AlwaysOn Availability Groups at FabrikamSuggested PracticesImplement a SQL Server Clustered InstanceImplement AlwaysOnAnswersLesson 1Lesson 2Case Scenario 1Case Scenario 2
Before You BeginLesson 1: Working with Performance MonitorGetting Started with Performance MonitorCapturing Performance Monitor DataCreating Data Collector SetsPractice Using Performance MonitorPractice Using Performance MonitorLesson SummaryLesson ReviewLesson 2: Working with SQL Server ProfilerCapturing Activity with SQL Server ProfilerTracing Output OptionsTrace EventsTrace ColumnsFiltering TracesTrace TemplatesUnderstanding SQL TraceReviewing Trace OutputCapturing Activity with Extended Events ProfilerPractice Creating TracesPractice Creating TracesLesson SummaryLesson ReviewLesson 3: Monitoring SQL ServerMonitoring ActivityDynamic Management Views and FunctionsQuerying Dynamic Management ViewsWorking with Activity MonitorPractice Using Dynamic Management ObjectsPractice Using Dynamic Management ObjectsLesson SummaryLesson ReviewLesson 4: Using the Data Collector ToolCapturing and Managing Performance DataUsing the Data CollectorDefining Data Collection SetsDesigning a Data Collector TopologyConfiguring the Data CollectorAssigning Roles to Data Collector SecurityMonitoring the Data CollectorAnalyzing Collected Performance DataServer Activity ReportDisk Usage ReportQuery Statistics ReportPractice Configuring the Management Data WarehouseLesson SummaryLesson ReviewLesson 5: Identifying BottlenecksMonitoring Disk UsageMonitoring Disk I/O and Detecting Excess PagingIsolating Disk Activity Created by SQL ServerMonitoring Memory UsageIsolating Memory Used by SQL ServerMonitoring CPU UsagePractice Configuring Memory Options by Using SQL Server Management StudioPractice Configuring Memory Options by Using SQL Server Management StudioLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Identifying Poor Query PerformanceCase Scenario 2: Deploying AuditingSuggested PracticesCreate a Trace by Using SQL Server ProfilerCapture a Performance Baseline by Using Performance MonitorAnswersLesson 1Lesson 2Lesson 3Lesson 4Lesson 5Case Scenario 1Case Scenario 2
Before You BeginLesson 1: Implementing and Maintaining IndexesUnderstanding the Anatomy of a Balanced Tree (B-Tree)Understanding Index Types and StructuresClustered IndexesNon-Clustered IndexesCovering IndexesFiltered IndexesPrimary XML and Secondary XML IndexesSpatial IndexesFull-Text IndexesColumnstore IndexesDesigning Indexes for Efficient RetrievalClustered IndexesNon-Clustered IndexesCovering IndexesFiltered IndexesPrimary XML and Secondary XML IndexesFull-Text Indexes and Semantic SearchesColumnstore IndexesUnderstanding StatisticsUsing Index InternalsMoving Row-Overflow DataCreating and Modifying IndexesChoosing Fill FactorsDetermining FragmentationRemoving Index FragmentationUpdating StatisticsTracking Missing IndexesReviewing Unused IndexesPractice Maintaining Indexes and StatisticsPractice Maintaining Indexes and StatisticsLesson SummaryLesson ReviewLesson 2: Identifying and Resolving Concurrency ProblemsDefining Transactions and Transaction ScopeUnderstanding SQL Server Lock ManagementLock ModesExplaining Lock CompatibilityUnderstanding Dynamic Lock ManagementUnderstanding Transaction Isolation LevelsChoosing the Read Committed Snapshot Isolation Database OptionLock Duration Based on Transaction Isolation LevelsMonitoring LocksIdentifying BlockingAvoiding BlockingUsing AlwaysOn Replicas to Improve ConcurrencyDetecting and Correcting DeadlocksCapturing Deadlock InformationUsing Activity MonitorDiagnosing BottlenecksUsing Reports for Performance AnalysisUsing Standard ReportsUsing Custom ReportsExecuting the KILL ProcessPractice Examining Dynamic Management ViewsLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Fabrikam Year-Query PerformanceCase Scenario 2: Analyzing Concurrency at Tailspin ToysSuggested PracticesImplement and Maintain Indexes and StatisticsIdentify and Resolve Concurrency ProblemsAnswersLesson 1Lesson 2Case Scenario 1Case Scenario 2
Before You BeginLesson 1: Managing SQL Server AgentExecuting Jobs by Using SQL Server AgentConfiguring SQL Server Agent AccountSetting SQL Server Agent SecurityConfiguring SQL Server Agent Mail ProfileSetting Up the SQL Server Agent Error LogManaging AlertsManaging JobsCreating a JobMonitoring JobsManaging OperatorsMonitoring Multi-Server EnvironmentsPractice Configuring SQL Server Agent, Jobs, Operators, and AlertsPractice Configuring SQL Server Agent, Jobs, Operators, and AlertsLesson SummaryLesson ReviewLesson 2: Configuring and Maintaining a Backup StrategyUnderstanding Backup TypesUsing Backup CompressionUnderstanding Recovery ModelsBacking Up System DatabasesBacking Up Replicated DatabasesBacking Up Mirrored DatabasesBacking Up AlwaysOn ReplicasUsing Database CheckpointsUsing Backup DevicesBacking Up Media SetsPerforming BackupsFull and Differential BackupsPerforming Transaction Log BackupsViewing Backup HistoryPractice Backing Up the AdventureWorks2012 DatabasePractice Backing Up the AdventureWorks2012 DatabaseLesson SummaryLesson ReviewLesson 3: Restoring SQL Server DatabasesRestoring DatabasesPerforming File RestoresPerforming Page RestoresRestoring a Database Protected with Transparent Data EncryptionRestoring System DatabasesRestoring Replicated DatabasesChecking Database StatusPractice Restoring the AdventureWorks2012 DatabasePractice Restoring the AdventureWorks2012 DatabaseLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: SQL Server Agent at ContosoCase Scenario 2: Fabrikam Backup StrategyCase Scenario 3: Recovery at AdatumSuggested PracticesManage SQL Server AgentConfigure and Maintain a Backup StrategyRestore DatabasesAnswersLesson 1Lesson 2Lesson 3Case Scenario 1Case Scenario 2Case Scenario 3
Case Study 1QuestionsCase Study 2QuestionsCase Study 3QuestionsCase Study 4QuestionsAnswersCase Study 1Case Study 2Case Study 3Case Study 4

Content preview from Training Kit Exam 70-462: Administering Microsoft® SQL Sever® 2012 Databases

Chapter 9. Troubleshooting SQL Server 2012

Exam objectives in this chapter:

Collect and analyze troubleshooting data.

In a perfect world, you would be able to install Microsoft SQL Server and there would be nothing more to do. However, we do not live in a perfect world and, like any system, SQL Server needs ongoing administration and management. In this chapter, you learn how to monitor your SQL Server environment and diagnose problems that are identified.

Lessons in this chapter: