Module 14: Scalability and High Availability

1. Module 14: Scalability and High Availability

2. Overview • Key high availability features available in Oracle and SQL Server • Key scalability features available in Oracle and SQL Server

3. 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 • Indexes • SQL Server partitioning option

4. 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

5. Demonstration 1: Clustering In this demonstration we will whiteboard how SQL Server Failover Clustering works

6. Standby Database • Both offer standby database which can be used for availability and disaster recovery • In Oracle, Data Guard • In SQL Server, 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

7. Demonstration 2: Log Shipping In this demonstration you will learn to: • Implement Log Shipping between two systems

8. 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

9. 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

10. Demonstration 3: 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

11. 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

12. Demonstration 4: 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

13. 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 2008 supports NUMA and Hot Add CPU

14. 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 10g 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

15. Demonstration 5: Service Broker In this demonstration you will learn to: • Create Databases to Converse Between Two Instances • Create the Conversation Objects Needed to Define a Dialog • Apply Routing to Remote Instances • Implement Dialog and Send Messages

16. Sync Services • Microsoft Sync Framework: platform to integrate any application data, from any store, using any protocol over any network to support collaboration and offline access • Microsoft Sync Framework provides: • Metadata Storage Services – Synchronize metadata in SQL Server Compact Edition • Sync Services for File System – Synchronize files and folders in a file system • Sync Services for FeedSync – Synchronize RSS and Atom feeds with data in a local store • Sync Services for ADO.NET supports SQL Server database and other devices and applications

17. 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