DB-MS SQL-Business Continuity

DB-MS SQL-Business Continuity.docx     Blog         MS Doc

 

1         Business continuity

1.1       Backup & restore

1.1.1       Overview

1.1.1.1       Overview

1.1.1.2       Azure Blob Storage

1.1.2       Quickstarts

1.1.2.1       Backup & restore

1.1.2.2       To Azure Blob Storage

1.1.3       How-to

1.1.3.1       Backup to Azure

1.1.3.1.1        Backup to URL for Microsoft Azure Blob Storage

1.1.3.1.2        Backup to Azure Blob Storage with PowerShell

1.1.3.1.3        Delete backup log files

1.1.3.1.4        Restore

1.1.3.1.5        Managed backups

1.1.3.1.5.1       Enable

1.1.3.1.5.2       Configure advanced options

1.1.3.1.5.3       Disable

1.1.3.1.5.4       Migrate settings from SQL Server 2014

1.1.3.2       Backup to S3-compatible object storage

1.1.3.3       Backup/Restore with S3-compatible object storage

1.1.3.3.1        Backup to URL for S3-compatible object storage

1.1.3.3.2        Best practices and troubleshooting

1.1.3.4       Backup On-premises

1.1.3.4.1        Full backup

1.1.3.4.2        Files & Filegroups

1.1.3.4.3        T-SQL from snapshot

1.1.3.4.4        Differential backup

1.1.3.4.5        Transaction log

1.1.3.4.6        T-log backup for damaged database

1.1.3.4.7        To a mirrored media set

1.1.3.5       Restore

1.1.3.5.1        Use SQL Server Management Studio (SSMS)

1.1.3.5.2        Simple database

1.1.3.5.3        Differential backup

1.1.3.5.4        Before the point of failure

1.1.3.5.5        Files & filegroups

1.1.3.5.6        Restore sequences

1.1.3.5.7        Restart an interrupted restore operation (T-SQL)

1.1.3.5.7.1       To a new location

1.1.3.5.7.2       Files to a new location

1.1.3.5.7.3       From a device

1.1.3.5.7.4       A transaction log

1.1.3.5.7.5       To a point in time

1.1.3.5.7.6       To a log sequence number

1.1.3.5.7.7       To a marked transaction

1.1.3.5.7.8       Use marked transactions to recover related databases

1.1.3.5.7.9       Offline & of a filegroup

1.1.3.5.7.10   Restore pages

1.1.3.5.7.11   Without restoring data

1.1.3.5.7.12   The master database

1.1.3.5.7.13   Piecemeal restores

1.1.3.5.7.13.1  Full recovery model

1.1.3.5.7.13.2  Simple recovery model

1.1.3.5.7.13.3  Only some filegroups (Full recovery model)

1.1.3.5.7.13.4  Only some file groups (Simple recovery model)

1.1.3.5.7.14   Online restores

1.1.3.5.7.14.1  Read-write file (Full recovery model)

1.1.3.5.7.14.2  Read-only file (Full recovery model)

1.1.3.5.7.14.3  Read-only file (Simple recovery model)

1.1.3.6       Backup devices

1.1.3.6.1        Define a device for a disk file

1.1.3.6.2        Define a device for a tape drive

1.1.3.6.3        View contents of a tape or file

1.1.3.6.4        Specify a destination

1.1.3.6.5        Delete a device

1.1.3.6.6        View properties & contents of a device

1.1.3.7       Configuration

1.1.3.7.1        Modify recovery model

1.1.3.7.2        Backup compression

1.1.3.7.3        Encrypted backup

1.1.3.7.4        Limit CPU usage by backup compression (T-SQL)

1.1.3.7.5        Set expiration date

1.1.3.7.6        View data & log files in a set

1.1.3.7.7        Configure checksum

1.1.3.7.8        Behaviour on error

1.1.3.7.9        Removed defunct filegroups

1.1.3.7.10    Manage suspect_pages table

1.1.3.7.11    Interoperability & Coexistence

1.1.4       Concepts

1.1.4.1       Azure

1.1.4.1.1        Managed backups

1.1.4.1.2        Back up to URL best practices

1.1.4.1.3        File-snapshot backups

1.1.4.2       Backups

1.1.4.2.1        Full backups

1.1.4.2.2        Full file backups

1.1.4.2.3        Differential backups

1.1.4.2.4        Partial backups

1.1.4.2.5        Tail-log backups

1.1.4.2.6        Copy-only backups

1.1.4.2.7        Transaction log

1.1.4.2.8        System databases

1.1.4.2.9        VSS & SQL Writer

1.1.4.2.10    SQL Writer Logging

1.1.4.3       Configuration

1.1.4.3.1        Compression

1.1.4.3.2        Encryption

1.1.4.3.3        Recovery models

1.1.4.3.4        Backup devices

1.1.4.3.5        Media sets & families

1.1.4.3.6        Mirrored backup media sets

1.1.4.3.7         History & header information

1.1.4.3.8        Possible media errors

1.1.4.4       Restore & recovery

1.1.4.4.1        Simple recovery model

1.1.4.4.2        Full recovery model

1.1.4.4.3        Files & filegroups

1.1.4.4.4        Simple file restores

1.1.4.4.5        Full file restores

1.1.4.4.6        Piecemeal

1.1.4.4.7        Transaction log

1.1.4.4.8        Marked transactions

1.1.4.4.9        Online restore

1.1.4.4.10    Deferred transactions

1.1.5       Reference

1.1.5.1       Dialog boxes & pages

1.1.5.1.1        Backups

1.1.5.1.1.1       Connect to an Azure Subscription

1.1.5.1.1.2       General page

1.1.5.1.1.3       Media Options page

1.1.5.1.1.4       Backup Options page

1.1.5.1.1.5       Select Backup Destination

1.1.5.1.1.6       Create credential

1.1.5.1.2        Backup devices

1.1.5.1.2.1       Device contents

1.1.5.1.2.2       General

1.1.5.1.2.3       Media Contents

1.1.5.1.3        Restores

1.1.5.1.3.1       Backup timeline

1.1.5.1.3.2       General page

1.1.5.1.3.3       Options page

1.1.5.1.3.4       Files page

1.1.5.1.3.5       Select backup device

1.1.5.1.3.6        Connect to Azure Storage

1.1.5.2       VDI reference

1.1.5.2.1        VDI Overview

1.1.5.2.2        IClientVirtualDevice

1.1.5.2.2.1       CompleteCommand

1.1.5.2.2.2       GetCommand

1.1.5.2.3        IClientVirtualDeviceSet2

1.1.5.2.3.1       Close

1.1.5.2.3.2       CreateEx GetBufferHandle GetConfiguration MapBufferHandle OpenDevice OpenInSecondaryEx SignalAbort

1.1.5.2.4        IServerVirtualDevice

1.1.5.2.4.1       CloseDevice SendCommand

1.1.5.2.5        IServerVirtualDeviceSet2

1.1.5.2.5.1       AllocateBuffer BeginConfiguration Close

1.1.5.2.5.2       EndConfiguration ExecuteCompletionAgent FreeBuffer GetConfiguration IsSharedBuffer

1.1.5.2.5.3       Open OpenDevice RequestBuffers SignalAbort

1.1.5.2.5.4       a

 

1.2       Always On availability groups

1.2.1       Overview

1.2.1.1       What is an Always On availability group?

1.2.1.2       Availability modes

1.2.1.3       Technology & capabilities

1.2.1.4       Prerequisites, restrictions, & recommendations

1.2.1.5       Interoperability

1.2.2       Quickstart

1.2.2.1       Getting started

1.2.2.2       Creation & configuration

1.2.2.2.1        Wizard

1.2.2.2.2        Dialog T-SQL

1.2.2.2.3        PowerShell

1.2.2.2.4        Azure virtual machines

1.2.3       How-to

1.2.3.1       Configure availability group Change replica availability Change replica failover mode Configure automatic seeding Configure backup on replicas Configure flexible failover policy

1.2.3.2       Configure distributed availability groups Configure for distributed transactions Configure read-scale availability group Configure read-only access replica Configure read-only routing

1.2.3.3       Create listener Remove listener

1.2.3.4       Join a secondary database Start data movement

1.2.3.5       Manually prepare secondary database

1.2.3.6       Specify the endpoint URL when adding or modifying a replica Join secondary replica

1.2.3.7        

1.2.3.8       Tune compression

1.2.3.9       Troubleshoot configuration Configure instance

1.2.3.10  Enable & disable

1.2.3.11  Create database mirroring endpoint (PowerShell) Create clustered DTC

1.2.3.12  Monitor

1.2.3.13  Always On Dashboard T-SQL

1.2.3.14  SQL Server Management Studio View availability group properties View replica properties

1.2.3.15  View listener properties Operate

1.2.3.16  Add Database Dialog Wizard

1.2.3.17  Add replica - wizard Add secondary replica

1.2.3.18  Change session-timeout period Change the HADR cluster context Fail over - wizard

1.2.3.19  Forced manual failover Planned manual failover Remove availability group Remove primary database Remove replica

1.2.3.20  Remove secondary database Resume availability database Suspend availability database Take availability group offline Troubleshoot failed add-file

1.2.3.21   

1.2.3.22  Upgrading replica instances

1.2.3.23  View health with policies Connect to listener

1.2.4       Concepts

1.2.4.1       Configuration

1.2.4.1.1        Automatic seeding for secondary replicas

1.2.4.1.2        Basic availability groups

1.2.4.1.3        Contained availability groups

1.2.4.1.4        Distributed availability groups

1.2.4.1.5        Domain-independent availability groups

1.2.4.1.6        Failover clustering & availability groups

1.2.4.1.7        Failover & failover modes

1.2.4.1.8        Read-scale only

1.2.4.1.9        Read/write connection redirection

1.2.4.2       Administration

1.2.4.2.1        Backup on Secondary Replica Readable secondary Enhanced Database Failover Database health detection

1.2.4.2.2        Lease, cluster, & health check timeouts Logins & jobs

1.2.4.2.3        Possible failures during sessions between replicas

1.2.4.3       Connectivity

1.2.4.3.1        Listener

1.2.4.3.2        Client connection types

1.2.4.3.3        Driver & client support

1.2.4.4       Interoperability

1.2.4.4.1        Replication, CT, & CDC

1.2.4.4.1.1       Configure replication

1.2.4.4.1.2       Publication database

1.2.4.4.1.3        Subscribers

1.2.4.4.2        Contained databases

1.2.4.4.3         Database snapshots

1.2.4.4.4        Distributed transactions

1.2.4.4.4.1       Cluster DTC

1.2.4.4.5        Encrypted databases

1.2.4.4.6        Failover cluster instances

1.2.4.4.7        FILESTREAM & FileTable

1.2.4.4.8        Migrating from log shipping

1.2.4.4.9        Remote blob store (RBS)

1.2.4.4.10    Reporting Services

1.2.4.4.11    Service Broker

1.2.5       Reference

1.2.5.1       System Objects

1.2.5.2       PowerShell Cmdlets

1.2.5.3       T-SQL

1.2.5.4       Dialogs & Wizards

1.2.5.4.1        Add IP Address

1.2.5.4.2        Availability Replica Properties (General Page)

1.2.5.4.3        New Availability Group Wizard

1.2.5.4.3.1       Specify Availability Group

1.2.5.4.3.2       Options Page

1.2.5.4.3.3       Select Databases Page

1.2.5.4.3.4       Specify Replicas Page

1.2.5.4.3.5       Enter Passwords Page

1.2.5.4.4        Always On Availability Group Wizard

1.2.5.4.4.1       Select Initial Data

1.2.5.4.4.2       Synchronization Page

1.2.5.4.4.3       Validation Page

1.2.5.4.4.4       Summary Page

1.2.5.4.4.5       Progress Page

1.2.5.4.4.6       Results Page

1.2.5.4.4.7       Availability Group - Connect Existing Secondary Replicas Page

1.2.5.4.5        Always On Dashboard

1.2.5.4.5.1       Options

1.2.5.4.5.2       Policy Evaluation Result

1.2.5.4.6        Availability Group Properties

1.2.5.4.6.1       General Page

1.2.5.4.6.2       Backup Preferences Page

1.2.5.5       Policies

1.2.5.5.1        Availability database is suspended

1.2.5.5.2        Availability group is offline

1.2.5.5.3        Availability group is not ready for automatic failover

1.2.5.5.4        Availability replica does not have a healthy role

1.2.5.5.5        Availability replica is disconnected

1.2.5.5.6        Availability replica is not joined

1.2.5.5.7        Data synchronization state of availability database is not healthy

1.2.5.5.8        Data synchronization state of some availability database is not healthy

1.2.5.5.9        Secondary database is not joined

1.2.5.5.10    Some availability replicas are disconnected

1.2.5.5.11    Some availability replicas are not synchronizing data Some availability replicas do not have a healthy role Some synchronous replicas are not synchronized WSFC cluster service is offline

1.2.5.6       Troubleshooting & monitoring guide

1.2.5.6.1        Monitor performance for availability groups

1.2.5.6.2        Troubleshoot: Availability group exceeded RPO

1.2.5.6.3        Troubleshoot: Availability group exceeded RTO

1.2.5.6.4        Troubleshoot: Changes on primary not reflected on secondary replica

1.2.5.6.5        Useful tools for troubleshooting

1.2.5.6.5.1       Extended events

1.2.5.6.5.2       Health diagnostics log

1.2.5.6.5.3       Policies

1.2.5.6.5.4       Ring buffers

1.2.5.6.5.5       Wait types

1.2.5.6.5.6       CLUSTER.LOG

1.2.5.6.5.7       Dynamic management & system catalog views

1.2.5.6.5.8       SQL Server error log

1.3       Always On failover cluster instance

1.3.1       Overview

1.3.1.1       What is a failover cluster instance?

1.3.1.2       Windows Server failover cluster

1.3.2       How-to

1.3.2.1       Deployment

1.3.2.1.1        Before you install

1.3.2.1.2        Installation

1.3.2.1.3        Create a cluster

1.3.2.1.4        Install client tools

1.3.2.1.5        Add or remove nodes

1.3.2.1.6        Remove a cluster

1.3.2.1.7        Upgrade a cluster

1.3.2.1.8        Migrating to Windows Server 2012 & 2016 Configuration

1.3.2.2       Rename a failover cluster Add Dependencies resource

1.3.2.3       Configure Quorum NodeWeight Settings Change the IP Address

1.3.2.4       Configure HealthCheckTimeout Configure FailureConditionLevel Administration

1.3.2.5       View Quorum NodeWeight Settings Disaster Recovery through Forced Quorum Force a cluster to start without a Quorum Troubleshooting

1.3.2.6       Troubleshoot Orphaned Users Diagnostics Log

1.3.2.7       Recover from a failure

1.3.3       Concepts

1.3.3.1       Configuration

1.3.3.1.1        Quorum Modes & Voting

1.3.3.1.2         Multi-Subnet Clustering

1.3.3.1.3        Failover Policy

1.3.3.2       Administration

1.3.3.2.1        Administration & maintenance

1.3.3.2.2        Automatic Page Repair

1.3.3.2.3        Logins & Jobs after role switching

1.4       Database mirroring

1.4.1       Overview

1.4.1.1       Prerequisites, Restrictions, & Recommendations

1.4.1.2       Mirroring States

1.4.1.3       Operating Modes

1.4.1.4       Witness

1.4.2       How-to

1.4.2.1       Configuration

1.4.2.1.1        Deployment

1.4.2.1.1.1       Set up

1.4.2.1.1.2       Prepare a database

1.4.2.1.1.3       Set up encryption

1.4.2.1.1.4       Use the Trustworthy Property

1.4.2.1.1.5       Specify a server network address

1.4.2.1.1.6       Establish a session

1.4.2.1.1.7       Upgrade

1.4.2.1.1.8       Remove mirroring

1.4.2.1.2        Witness

1.4.2.1.2.1       Add using Windows authentication

1.4.2.1.2.2       Add or replace a witness

1.4.2.1.2.3       Remove the witness

1.4.2.2       Administration

1.4.2.2.1        View states

1.4.2.2.2        Pause or resume

1.4.2.2.3        Establish session

1.4.2.2.4        Connect clients

1.4.2.2.5        Warning thresholds on performance

1.4.2.2.6        Change transaction safety

1.4.2.2.7        Estimate interruption during role switching

1.4.2.2.8        Manually fail over (T-SQL)

1.4.2.2.9        Manually fail over (SSMS)

1.4.2.2.10    Force service to fail over

1.4.2.2.11    Troubleshoot Database Mirroring Configuration

1.4.2.3       Security

1.4.2.3.1        Configure accounts

1.4.2.3.2        Create an endpoint

1.4.2.3.3        Allow access to an endpoint

1.4.2.3.4        Use certificates for an endpoint

1.4.2.3.4.1       For outbound connections

1.4.2.3.4.2       For inbound connections

1.4.3       Concepts

1.4.3.1       Interoperability & Coexistence

1.4.3.1.1        Failover cluster instances

1.4.3.1.2        Log Shipping

1.4.3.1.3        Full-Text Catalogs

1.4.3.1.4        Database Snapshots

1.4.3.1.5        Replication

1.4.3.2       Administration

1.4.3.2.1        Monitoring

1.4.3.2.2        Role Switching

1.4.3.2.3        Quorum: How a Witness Affects Database Availability Possible cause of failures

1.4.3.2.4        Pausing & resuming Remove Mirroring

1.4.3.3       Security

1.4.3.3.1        Endpoints

1.4.3.3.2        Transport  security

1.4.4       Reference

1.4.4.1        System objects

1.4.4.2       Security Wizard

1.4.4.2.1        Include Witness Server

1.4.4.2.2        Choose Servers to Configure

1.4.4.2.3        Principal Server Instance

1.4.4.2.4        Mirror Server Instance

1.4.4.2.5        Witness Server Instance

1.4.4.2.6        Service Accounts

1.4.4.3       Database Mirroring Monitor

1.4.4.3.1        Database Mirroring Monitor Overview

1.4.4.3.2        Register Mirrored Database

1.4.4.3.3        Status Page

1.4.4.3.4        History Page

1.4.4.3.5        Warnings Page

1.4.4.3.6        Set Warning Thresholds

1.5       Log shipping

1.5.1       Overview

1.5.2       How-to

1.5.2.1       Configure

1.5.2.2       Add a secondary database Remove a secondary database Remove

1.5.2.3       View report Monitor

1.5.2.4       Failover to secondary

1.5.2.5       Change roles between servers

1.5.3       Concepts

1.5.3.1       Log Shipping & Replication

1.5.3.2       Tables & Stored Procedures

1.5.3.3       Upgrading Log Shipping to SQL Server 2016

1.5.4       Reference

1.5.4.1       Transaction Log Backup Settings

1.5.4.2        Secondary Database Settings

1.5.4.3       Log Shipping Monitor Settings