Module 14:
Scalability and High
Availability
Overview
Key high availability features available in Oracle and SQL Server
Key scalability features available in Oracle and SQL Server
High Availability
High availability — ensuring database and data are accessible for the maximum amount of time
Initialization parameters or configuration options in Oracle and SQL Server
Logical (add, remove, rename columns) and physical (rebuild, move, change storage parameters) structure of tables
Online index operations
SQL Server partitioning option
Clustering
Both Oracle and SQL Server offer Active/Passive (single instance) and Active/Active (multi-instance failover clustering) options in clustering. In Oracle, it is called Failsafe and Real Application Clusters (RAC); SQL Server refers to it as Failover Clustering
For Active/Passive clustering, Failsafe and Failover clustering are both based on Microsoft Cluster Services (MSCS) containing a single set of non-shared disks
For Active/Active clustering, Failsafe and Failover clustering utilize a multi-node configuration where each node has its own instance
connecting to shared disks. However, the database can be opened by only one node at a time
Oracle’s RAC can also provide Active/Active clustering in a shared- everything architecture where multiple instances can
simultaneously access data from the same database
Standby Database
Both offer standby database which can be used for availability and disaster recovery
• In Oracle, Data Guard
• In SQL Server, AlwaysOn, Database Mirroring, and Log Shipping
Standby databases are updated using archive logs in Oracle and transaction logs in SQL Server
Shipping of logs to a standby server and their
application can be controlled to provide scope for recovery from user errors in the primary
Snapshots of mirrored database in SQL Server and
logical standby database in Oracle can be used for read- only purposes such as reporting, analysis, DSS
Database Mirroring
Software solution for increasing the availability of a specific database
Consists of principal server, a mirror server, and optionally a witness server
Witness server enables automatic failover
Can operate in either Synchronous or Asynchronous mode
Use views such as sys.database_mirroring,
sys.dm_db_mirroring_connections to monitor database mirroring
Demonstration: Database Mirroring
In this demonstration you will learn to:
Prepare Database on Instances Configure Database Mirroring
Execute Failovers Between Instances Utilize the Mirror for Read-Only
Processing with Database Snapshots Monitor Activity Between Instances
SQL Server 2012 AlwaysOn
NEW high availability and disaster recovery solution Achieving required 9’s
Availability Groups on multiple databases
Multi-subnet clustering support
Hardware utilization and performance
Active secondaries (reporting, backup) Reduced planned downtime
Windows Server Core support
Rolling upgrade support
SQL Server 2012 AlwaysOn Details
Failover Cluster Instances:
Flexible failover policy for cluster health detection
Indirect checkpoints Availability Groups:
Asynchronous or synchronous modes
Listeners for fast application failover
Flexible failover policy
Automatic page repair against page corruption
Encryption and compression for security and high performance transport
SQL Server 2012 AlwaysOn Tools
T-SQL DDL statements Wizards
New Availability Group
Add database to Availability Group
Add replica to Availability Group
Failover Availability Group
AlwaysOn Dashboard for Availability Group health view Object Explorer Details for Availability Group info
PowerShell cmdlets for automation
Demonstration: AlwaysOn
In this demonstration you will learn to:
Implement AlwaysOn Availability Groups
Replication – Objects
Replication is a specialization of the distributed database concept where copies of an object can be kept in multiple databases
Unlike clustering and standby databases, replication encourages sharing and distribution of individual objects rather than entire databases
Both Oracle and SQL Server provide multiple types of replication based on whether the replicas are read-only or updatable
Replicas are refreshed in full or updated using discrete transactions either synchronously (multi-master) or asynchronously (all others) Oracle Streams offers replication in three forms:
• Multi-master replication — multiple peer-to-peer updatable replicas
• Materialized views — each replica has a master site with various policies on updating the replicas
• Hybrids of replication and materialized views
Replication – Types
SQL Server offers the following types of replication:
• Snapshot replication — used mostly as read-only copies which are completely refreshed periodically
• Transactional replication — kept up-to-date with immediate or deferred updates from the master site. Copies are used mostly as read-only and occasionally updated. Updatable subscribers
(immediate and queued), and peer-to-peer transactional replication support updates at subscribers.
• Merge replication — similar to Oracle’s multi-master replication where the autonomous sites are synchronized at regular intervals Oracle and SQL Server offer sophisticated algorithms for conflict resolution among updatable replicas
Demonstration: Replication
In this demonstration you will learn to:
Define the Roles of Replication
Define the Publication and Articles Implement Peer-to-Peer Replication Use the new Configure Peer-to-Peer Topology Tool
Scalability
Scalability is the ability to support very large databases (VLDB) and/or large volume OLTP
Memory areas in Oracle and SQL Server are dynamically resized without restarting. The process is performed either automatically or by the DBA in Oracle, where it is automatically performed by the DBMS in SQL Server.
Oracle uses parallel query slaves and SQL Server uses worker
threads to provide interoperation and intra-operation parallelism in the execution of DML and DDL. Both optimizers are parallel-aware.
Oracle and SQL Server scale well in the handling of user connections through the use of shared server processes
Oracle and SQL Server offer many types and features for speedy access to large volumes of data. Examples are many types of indexes, advanced I/O techniques and caching.
SQL Server supports NUMA and Hot Add CPU
Scalability and HA in Backup and Recovery
MTTR targets can be set for instance recovery by controlling the checkpoint process in Oracle using FAST_START MTTR_TARGET and in SQL Server using ‘recovery interval’ option
The RMAN features available in SQL Server backup and restore utilities are:
• Parallelism in backup and recovery operations
• Incremental backups
• Multiple granularities in backup and restore to reduce the amount of recovery needed
• Point in time recovery
• Choice of backup media and devices
• Duplicating or cloning databases
Moving of data in logical units of tablespaces using transportable tablespaces across multiple platforms in Oracle is found in SQL Server through the attach and detach features for databases
Service Broker and Advanced Queuing
Service Broker provides messages, queues and services to provide loosely coupled, asynchronous applications for better scalability and improved performance
SB provides the infrastructure for reliable and secure message routing, Conversations, Queues, Services and Activation
Oracle has Oracle Streams Advanced Queuing (AQ) Within an Oracle Streams AQ, producers enqueue a message, and consumer applications dequeue
messages
Messages may undergo transformations during enqueue/dequeue
Review
We defined high availability and the role it plays in modern day businesses
We covered all the important high availability features in Oracle and SQL Server. The major ones being failover clustering, standby databases and replication.
We learned the meaning of scalability with respect to VLDB and OLTP environments
We examined several scalability features found in Oracle and SQL Server such as dynamic memory management, parallel SQL, query rewrite
