{{ vExpose }}.Blog

Tag: copydatamanagement

  • Best Practices For Running Oracle Database On XtremIO X2

    Summary

    DEllEMC’s XtremIO has simple, easy-to-use management. The XtremIO Management Server (XMS) delivers an HTML5 user interface that is a simple and easy-to-use interface for storage administrators. XMS allows storage administrators the ability to provisions storage with very little setup and planning.

    Hope this post is helpful to all Oracle users trying to get best out of their XtremIO investments.

    XtremIO is designed and optimized for databases and for DBAs, providing the following benefits.

    Predictable Performance

    • XtremIO provides predictable and consistency low-latency performance
    • With XtremIO scale-up and scale-out architecture, year-to-year growth is easy. The initial investment is preserved and application performance is improved.
    • Performance is predictable and provides best response times regardless of the workload and environment – be it production, QA, test or development

    Incredible Simplicity

    Agility

    • The typical enterprise applications require multiple copies such as test/development, reporting or online analytics. DBAs and test/dev engineers often have to spend hours managing the DB creation and refreshing the environments while often being limited by capacity, performance, and the number of copies.
    • XtremIO’s Integrated Copy Data Management (iCDM) allows for instant XtremIO Virtual Copies (XVCs) to be created from production with no performance impact.
    • These copies can be repurposed for near real-time analytics, test/dev and any other use case- all with complete space efficiency.

    Protection

    • Protecting the database is easy with XtremIO
    • There is no need for any design covering RAID type, data file capacity, load balancing, and tuning.
    • The data is protected with a proprietary flash-optimized algorithm called XtremIO Data Protection (XDP).
    • XDP is very different from RAID in several ways. Since XDP is always working within an all-flash storage array, several criteria were important in the design of this protection scheme. XDP benefits include ultra-low capacity overhead, high levels of data protection in case of double SSD failure, rapid rebuild times, flash endurance, and of course extreme performance
    • With XtremIO virtual copies it is easy to protect and recover from any operational and logical corruption; XVC’s allow the creation of frequent point-in-time copies (according to RPO intervals – seconds, minutes, hours) and use them to recover from any data corruption
    • An XVC can be kept in the system for as long as needed. Recovery using XtremIO virtual copy is instantaneous and does not impact system performance.

    ORACLE BEST PRACTICES

    1. General Guidelines

    Irrespective of using any application/database with XtremIO storage array below listed general guidelines are common.

    • Keep consistent, duplex link speed on all paths between the host and the XtremIO cluster
    • To ensure continuous access to XtremIO storage during a cluster software upgrade, verify that minimum I/O timeout of 30 seconds is set on the HBAs of all hosts connected to the affected XtremIO cluster. Also, verify that a minimum timeout of 30 seconds is set for all applications that are using storage from the XtremIO cluster.
    • The HBA queue depth (also referred to as execution throttle) controls the amount of outstanding I/O requests per HBA port. The HBA queue depth should be set to the maximum value
    • The LUN queue depth controls the amount of outstanding I/O requests per single path. These settings are controlled in the driver module for the card at the OS level. When connecting Linux host to XtremIO, a LUN queue depth setting should retain its default values.
    • I/O scheduling controls how I/O operations are submitted to storage. Linux offers various I/O algorithms (also known as “I/O Elevators“) to accommodate the different workloads. When connecting a Linux host to XtremIO storage, set the I/O elevator to either noop or deadline. It is not recommended to use the cfq I/O elevator setting, as it’s less optimal for XtremIO storage.
    • It is HIGHLY RECOMMENDED to follow the latest “XtremIO Host Configuration Guide” for all operating system which will use storage from XtremIO
    • If DB is virtualized then make sure all Virtual Machine components are provisioned from XtremIO storage (OS, Application/Binary, Swap, Paging, etc.)

    2. Oracle ASM

    Oracle Automatic Storage Management (ASM) is Oracle’s recommended software for supporting Oracle database files.

    For more information on ASM please refer this link

    • ASM General Recommendation
      • External redundancy is recommended for XtremIO
      • The XtremIO Storage Array natively provides flash-optimized data protection
    • Database Files Location in ASM Disk Groups
      • Best practices for storing Oracle DBMS file types in ASM disk groups are mentioned as below.
    Oracle Database Files Location in ASM disk groups

    Note: The second REDO data group (DG) is applicable if REDO logs are multiplexed.

    • Number of LUNs per Disk Group
      • Excellent cluster performance is achieved using an XtremIO Storage Array with just single LUN in a single disk group. However, in order to maximize performance from a single host, parallelism and adequate utilization of device queues are required.
      • The best practice to achieve this is by using a minimum of four LUNs for the data disk group. Doing this enables the hosts to use parallelism and ensures optimal performance without any bottlenecks.
      • The best practices for Disk group configuration and data placement are outlined in the below table.
    Number of LUNs per disk group
    •  512 verses 4K Advanced Format Considerations
      • The default setting for XtremIO volumes is 512 bytes. It is recommended to keep the default setting and not use 4K Advanced Format.

    3. Multiblock I/O Request Sizes

    Oracle Database performs I/O on data files in multiples of the database block size (db_block_size), which by default is 8KB. The default Oracle Database block size is optimal on XtremIO. XtremIO supports larger block sizes as well. In the case of multiblock I/O one should tune the Oracle Database initialization parameter db_file_multiblock_read_count to limit the requests to 128KB. This is derived with the following formula:

    db_file_multiblock_read_count is db_file_multiblock_read_count = 128KB / db_block_size

    Usually, Oracle DB is optimized to perform very large transfers to mitigate the seek cost due to multiblock reads on mechanical/spinning drives. In a seek-free storage environment, such as XtremIO, there is no need for such mitigation. Also, most modern FC HBAs require OS to segment large requests into multiple requests. For example, an application IO of 1MB is fragmented by Linux block I/O layer into two 512KB transfers to suit HBA max transfer size.

    4. REDO Log Block Size

    The default block size for REDO LOG is 512 bytes. I/O requests sent to the redo log files are in increments of the redo block size. This is the blocking factor Oracle uses within REDO LOG files and has nothing to do with the on-disk format of the XtremIO LUN.

    XtremIO’s recommendation is to create REDO LOG files with 4K block size. For more details on this please check Oracle Support notes 1681266.1.

    Note: For Oracle version prior to 12.2.0.1.0, you should set the parameter _disk_sector_size_override to TRUE when creating a redo log with 4K block size in the database instance.

    Do not set the parameter _disk_sector_size_override in the ASM instance. Once the instance is running, simply add more redo logs with the BLOCKSIZE option set to 4KB and then drop any redo logs that have default 512B block size.

    5. Grid Infrastructure Files – OCR/Voting

    The block size for both Oracle Cluster Registry (OCR) and Cluster Synchronization Services (CSS) voting files are 512 bytes, hence I/O is therefore sized as multiple of 512 bytes. This is consistent with XtremIO’s best practices and hence no changes are needed in OCR/Voting files.

    6. Oracle DB Level Compression & Encryption

    XtremIO has inline data reduction (deduplication and compression) running all the time without the performance penalty and stores 100% of data in encrypted format using Data At Rest Encryption (D@RE). This makes Oracle DB level compression and encryption as redundant.

    XtremIO data services work for all the data stored on arrays, unlike being selective like Oracle. Also XtremIO no not use host CPU for data services, which is an expensive resource in the Oracle environment.

    With XtremIO it is a best practice to disable / not use application-level Compression and Encryption services.

    XtremIO Advanced Services for Oracle

    XtremIO offers a wide variety of use cases to simplify database environment workflow. XtremIO comes along with a tool called iCDM (Integrated Copy Data Management) which allows automating all Copy Data Management (CDM) tasks in DB environments.

    Few use cases are mentioned below.

    1. Modern Backup-to-Disk using XtremIO XVC

    XtremIO XVCs (XtremIO Virtual Copy) are precise point-in-time copies of source volumes which basically are a collection of metadata pointers to the source volume blocks. Therefore, XVC consumes minimal physical capacity.

    Executing XVCs is extremely fast and hence most efficient backup-to-disk methodology. The best part is this process does not utilize production/source server resources for creating backup-to-disk copy. Over time as source data is updated/changed, only unique data is stored on XtremIO in compressed format.

    2. XtremIO XVC for Manual Continuous Data Protection (CDP)

    XtremIO XVCs are so efficient that these can be used as part of a business continuity strategy. Below mentioned two options can be used for this.

    • Crash-Consistent (or “Restart-able” Image)
      • A crash-consistent or restartable image is a point-in-time image of the primary database on disk
      • This option involves taking XVC of the primary database while it is up and operational. The image that is captured is similar to the state of the primary database.
      • During the DB restart on the XVC, the DB automatically performs a recovery using the online logs.
      • All committed transactions are included and all uncommitted transactions are rolled back.
    • Application/DB-Consistent (“Recoverable” Image) –
      • A recoverable image is a point-in-time image of the primary database on disk
      • This option involves taking XVC of the primary DB while DB is in “Backup” mode
      • Highly recommended to have a backup file of the control file prior and after the completion of the XVC process.Unlike crash-consistent copy, data files can be rolled forward in time using logs – up to latest or desired SCN (captured in the control file)

    3. XtremIO XVC for Cloning Primary Database (BCV Copy)

    BCV / clones of the primary copies can be created using XtremIO XVCs. Methods for clone creation are as mentioned in “2. XtremIO XVC for Manual Continuous Data Protection (CDP)”

    operating System Best Practices

    When it comes to performance optimization operating system settings also play a major role. It is highly recommended to follow all the operating system best practices as listed in the “XtremIO Host Configuration Guide”.

    The absence of host best practices might result in host-bound performance than XtremIO bound performance. In short, the host/operating system might become the bottleneck, because of the host/operating system level queuing.

  • Microsoft SQL DWH using DellEMC iCDM

    Recently I got engaged with one of our customer who was trying to implement data warehouse using the Microsoft SQL 2017. But they were not able to implement the same into the production environment even after successful POC and testing. When we got engaged with customer DBA teams we used DellEMC’s iCDM functionality (with XtremIO X2 system) in some unique way to help them not just solve their problem but exceeding business SLA expectations. This blog covers the details around problem and solution along with learning.

    Problem Statement

    Customer has several mission-critical MS SQL 2016 databases which are running in 24×7 operation. These databases are supporting their business critical OLTP application.

    Customer’s planned DWH approach

    Business teams were pushing application and DBA teams to create singular reporting platform which will create reporting on data from multiple MSSQL source databases.

    For this project DBA teams built MS SQL 2017 platform which will consolidate data from 3 source MS SQL databases. They created the test setup for feature testing before production roll-out, which worked as per their expectations. But when they implemented this feature in production DBA team realized that this is generating massive load on their source databases and causing performance issues for their business applications, which was completely unacceptable to business teams.

    This forced DBA teams to stop fetching data from the network and restore the data from last backup instead. This was used as workaround because this meant that data in reporting server was more than 30 hours old, which was making reporting irrelevant.

    DellEMC Solution

    When DellEMC team got involved we started with collecting DB environment data collection and business expectation understanding. Once we understood the pain points and business requirement we mentioned to DBAs that traditional way of data management techniques won’t help them. We also mentioned that since they are exiting DellEMC customer they already have the solution with them, which they never implemented.

    Every DellEMC All Flash storage solutions includes bundled copy data management software called Appsync.

    On high level AppSync allows application and database administrators to create application workflows. These workflows allows them to create on demand or scheduled protection or re-purpose copies of databases. We also mentioned that using this software you can create your copies of source MS SQL 2016 DB copies and mount it to target MS SQL 2017 DB server, scheduled or on-demand. These workflows are based on DellEMC storage in back-end and hence can be executed during even the busiest times.

    As usual customer DBA teams didn’t believe us and that’s where it comes to next section of this blog – Demonstration! 🙂

    Demonstration of the Appsync

    To demonstrate the functionality of DellEMC iCDM we installed the Appsync server on of the Windows server VM in customer environment. Below is the high level architecture

    DellEMC’s proposed solution approach for DWH project

    Once Appsync was installed we configured the same to communicate with source and target SQL servers and also DellEMC XtremIO storage. This allows Appsync to discover running databases and create the end to end database mapping.

    Post initial configuration we created the SQL re-purpose copy schedule to create the MS SQL database copy every 6 hours. This schedule was then applied to all the 3 source databases.

    Once the copy of multiple source database was created we used Appsync to mount the same into target MSSQL 2017 server. In our testing we mounted 3 source copies on single target MSSQL 2017 instance. Entire mounting operation completed within couple of minutes. This process allowed customer to save multiple days and help achieve business SLA.

    Apart from solving their most critical problem in hand this solution helped them solve couple of major production issues

    • Database RPO – We reduced production DB RPO to 2 hours instead of 30+ hours
    • Agent-free backup – iCDM helped customer to mount DB production copy on off-host backup server. This helped them completely eliminate backup load from production server.

    If you’re facing similar challenges in your DB environment then highly recommended to use iCDM approach instead of traditional methods.

    I have created another blog post to elaborate more on iCDM and advantages of the same. You can go through the same using below link.

    Databases and Integrated Copy Data Management (iCDM) with DellEMC

    Below are few reference documents for further reading on iCDM

    Dell EMC AppSync Datasheet

    Best Practices for running SQL Server on DellEMC XtremIO X2