Innovartis DB Ghost Irritant

Posted by on May 14, 2012 No comments

As a DBA working at a large company I deal with many vendor software products. One software product that I have been spending a lot of time with lately started using DB Ghost by Innovartis to apply changes to their database. Their marketing gives the impression the software is the best thing since sliced bread. So why has it become a thorn in my side?

Normally this type of thing just comes with the territory, but it has become such an issue that I feel the need to vent my frustration. To apply changes to a database, DB Ghost needs to create new database on the fly and drop it afterwards. It does this because it uses an new empty database with a random name with a clean schema to compare with to determine what changes are needed. For the environments that I manage, creating databases on the fly is a big no-no, and an audit violation, because the application support team is the one who applies the application updates which always include database changes.

Also, I have a bunch of scripts that are in place on every SQL Server instance that automate backups and management. So the moment a database is created my scripts automatically kick in, run a backup and start tracking the database. This automation is in place because we do not allow users to create databases without our explicit consent and knowledge. Even worse, we do not allow them to be created, and then dropped a few minutes later. This causes extra manual work on my end just to keep things cleaned up from the constant creation and deletion of these randomly named databases.

But, the biggest issues is, in order to an update, an application support person needs full SA access rights on the database server. One might say that they “should” only need the ability to create databases, or db_creator role membership, and db_owner role membership in the database being upgraded. And I would say that also, but no, full SA access is needed. When DB Ghost creates a database is sets two options on the database, TRUSTWORTHY and DB_CHANING to off. Changing both these options for a database requires SA access. I wouldn’t be that annoyed with this, but since the default values are already off, the statements are completely redundant and not needed. And to top this off, the vendor’s documentation on this is completely wrong.

So, for me, DB Ghost is a pain. With a couple changes though it could be more friendly in a larger corporate environment with limited security. The best thing would be to change the comparison to use an existing empty database. After the compare, it can simply just drop all the objects in this database, and leave it empty for the next time. This would then no longer require SA access to change those database properties because it would no longer need the ability to create databases.

To any vendor out there. Do not architect a solution that requires the application to create its own database (I am looking at you SharePoint), and never require the ability to create databases on the fly. Always have a way to simply use an empty database. I suspect this would make many DBA’s lives easier, including my own.

Symmetrix VMAX Training Summary (4 of 4)

Posted by on March 2, 2012 No comments

A few months back I attended a Symmetrix VMAX training class. Afterwards I wrote up a document summarizing some of the training and adding a bit of research into things that weren’t explained in depth as I would have liked.

Fully Automated Storage Tiering

Fully Automated Storage Tiering (FAST) is a feature where the Symmetix automatically tiers data based on performance analysis. The feature is divided up as FAST or FAST DP (Disk Provisioning) and FAST VP (Virtual Provisioning). FAST DP is used for traditional “thick” devices, and FAST VP is used for thin devices. FAST DP moves a whole LUN between tiers and FAST VP allows the relocation of thin extents of a thin device. Therefore an individual thin device can be spread across multiple thin pools. FAST DP supports both FBA and CKD devices. FAST VP supports only FBA devices.

There are three components of FAST DP/VP, a tier, policy, and storage group. For FAST DP, static tiers are formed from disk groups, for FAST VP static tiers are formed from thin pools. Dynamic Symmetrix tiers can only be used for FAST DP. The dynamic tiers automatically include all disk groups that match the tier technology, for example FC, SATA, or SSD. In some cases dynamic tiers involve less management but reduce performance predictability. The “symtier list” command will list all the tiers. An example is below. Since Kimberly Clark only uses FAST for thin devices we only see tiers of type VP.

symtier list -sid 1687

Symmetrix ID          : 000192601687

----------------------------------------------
                                             I
                                Target       n
Tier Name             Type Tech Protection   c
--------------------- ---- ---- ------------ -

FC                    VP   FC   RAID-5(7+1)  S
FLASH                 VP   EFD  RAID-5(7+1)  S
SATA                  VP   SATA RAID-5(7+1)  S

The “symtier show” command will show how each thin pool is assigned to each tier. In the case of FAST DP this will show the disk groups that are assigned to the tier. In this example we see a FAST VP tier using a thin pool.

symtier show -tier_name FLASH -sid 1687

Symmetrix ID          : 000192601687

Tier Name             : FLASH
Tier Type             : VP
Technology            : EFD
Target Protection     : RAID-5(7+1)
Include Type          : Static

Thin Pools(1)
    {
    --------------------------------------------------
                         Logical Capacities (GB)
                 Dev   -------------------------- Full
    Pool Name    Emul   Enabled     Free     Used  (%)
    ------------ ----- -------- -------- -------- ----
    FLASH200     FBA       4810     2518     2292   47
                       -------- -------- --------
    Total                  4810     2518     2292
    }

A FAST policy is used to assign upper limits for how much each data can exist on each tier that the policy is used to manage. Typically multiple policies exist allowing different amounts of data to exist on the different tiers. The command symfast list with a -fp for fast policy, will show the policies.

symfast list -sid 1687 -fp

Symmetrix ID          : 000192601687

---------------------------------------------
Policy Name                      Tiers Assocs
-------------------------------- ----- ------
Bronze                               2      4
Gold                                 3     74
Platinum                             2      4
Silver                               2     19

Running symfast show for an individual policy will show the details for the policy.

symfast show -sid 1687 -fp_name Gold

Symmetrix ID          : 000192601687

Policy Name           : Gold

Tiers(3)
    {
    ------------------------------------------------------------------
                                            Max SG       Target
    Tier Name                        Type  Percent Tech  Protection
    -------------------------------- ---- -------- ----- -------------
    FLASH                            VP         10 EFD   RAID-5(7+1)
    FC                               VP         90 FC    RAID-5(7+1)
    SATA                             VP        100 SATA  RAID-5(7+1)
    }

Storage Groups(74)
    {
    ------------------------------------
    Storage Group Name               Pri
    -------------------------------- ---
    IN00AAP010                       2
    IN00SQP001                       2
    USTWVM480                        2
    USTWVM660                        2
    }

Storage groups are the same storage groups that are used for provisioning. They are simply a logical collection of devices that are managed together. Storage groups are then associated with a single FAST policy. When storage groups are associated, they are also given a priority which is used when deciding which devices need to be relocated. You can see a list of all the storage groups and their associated policy by using the “symfast list” command with -association parameter.

symfast list -sid 1687 -association

Symmetrix ID          : 000192601687

---------------------------------------------------------------------
Storage Group Name               Policy Name                      Pri
-------------------------------- -------------------------------- ---
IN00AAP010                       Gold                             2
INAAP05400                       Gold                             2
INSQP00100                       Gold                             2
INUAP739                         Silver                           2
KCAPPS                           Silver                           2
USTCA554                         Bronze                           2
USTCA584                         Silver                           2
USTCA587                         Platinum                         2
USTCA592                         Platinum                         2

For Kimberly Clark there are 4 different FAST VP tiers configured with the percentages for each tier below.

Platinum Gold Silver Bronze
FLASH 10% 10%
FC 100% 90% 90% 15%
SATA 100% 100% 100%

There are many different options to control how FAST DP/VP behave and how each decides what to do and when to do it. It can also run in “user approve” mode where migration recommendations are given but wait on a human to approve before it is executed.

Symmetrix VMAX Training Summary (3 of 4)

Posted by on March 2, 2012 1 comment

A few months back I attended a Symmetrix VMAX training class. Afterwards I wrote up a document summarizing some of the training and adding a bit of research into things that weren’t explained in depth as I would have liked.

Virtual Provisioning

The Symmetrix supports a feature called Virtual Provisioning. Virtual provisioning is a feature that exposes a device to a host that doesn’t actually consume any physical disk space until data is written to by the host. For example, a server may see a 500 GB disk. The sum of all the files on the disk may only be 100 GB. That device in the storage array then only consumes 100 GB of physical disk, not the 500 GB it is allocated. Virtual provisioning is also known as thin provisioning in the storage indusrry.

With virtual provisioning it is possible to over provision your physical storage. For example, the sum of all the thin devices is greater than the physical capacity of the thin pools. When new disks are added to the array, the DATA devices created on those disks can be dynamically added to the thin pools to add more capacity. After the devices are added, a pool rebalance operation can be run to restripe data on to the new devices so that each device is within a target variance of utilization. Rebalancing can also reallocate thin extents if anything gets out of balance though normal usage. At Kimberly Clark, pool rebalancing is scheduled to run automatically on Sunday mornings.

When you create a thin device, called a TDEV in the array, you must bind it to a pool before it can be used. This can be done by using symconfigure commands or the SMC. A thin device can only be bound to one pool at a time. As data is written to the thin device, thin extents are allocated in 768 KB chunks on the DATA devices that make up the pool. There is a minor performance penalty that is paid once when allocating new thin extents. A round robin mechanism is used to balance allocations of thin extents across all DATA devices in the pool. When data is read from blocks that have not previously been written to, or have not had a thin extent allocated for, the Symmetrix array returns data blocks that contain all zeros. To delete a thing device, you must unbind the device before you can delete it. When you unbind a thin device the extents that have been allocated from the thin pool are freed and the data is lost.

When it comes to replication, thin devices act the same way normal devices do. There are some minor restrictions in what with what types of devices you can replicate to but it is fairly open and even works with Open Replicator.

The DATA devices that make up a thin pool must be RAID protected. They are never visible to a host, and thin pools can only contain DATA devices of the same type, emulation, and protection type. It is recommended that the DATA devices be the same size and speed.

You can “rebind” thin devices to different pools. This causes new extent allocations to come from the new pool. It does not move existing thin extents from the old pool to the new pool. A different feature called Virtual LUN VP Migration allows thin devices to be moved between pools non-disruptively. Another feature called FAST (Fully Automated Storage Tiering) can automatically move regions of data to different pools of different technology based on usage to optimize storage and resource usage.

To remove a DATA device from a pool it must be “drained” first. To drain the DATA device you simply disable it. As long as there is enough space on the other DATA devices, the array then begins to “drain” the device by moving thin extents to other devices in the pool. Once the device is drained it can be removed from the pool.

Over time a host will likely write and delete data. Typically, when a file system deletes a file, it simply deletes the file from its file allocation table. The blocks that made up that file are then just marked for reuse. The storage array still sees these blocks as holding data. Various methods can be used to actually zero out empty space that was once used on a file system. A Symmetrix storage array can then reclaim those thin extents and reuse them elsewhere. A reclaim operation will de-allocate thin extents that contain all zeros.

The same commands to create and manage meta devices work for thin devices. You cannot mix and match thin and thick devices in a meta device. Thin meta devices must be created before they are bound to a pool. EMC advises that because each thin device is already striped across multiple DATA devices, there is no need to create striped meta devices from thin devices, and you can just create concatenated meta thin devices. The one exception is with synchronous replication. In this case it is recommended to use striped meta thin devices.

Given the architecture of virtual provisioning there is always a risk that a pool may fill up due to over subscription. There are a few recommendations for how large to make pools and when to expand them, but the most important thing is to actively monitor the size of the pools and anticipate any needed expansion. Once a pool is full no new writes are allowed. Different operating systems would handle this situation differently. To deal with a full pool you can add new DATA devices, run a reclaim operation, or unbind unused devices.

At Kimberly Clark most thin devices are pre-created and left unbound to speed up meta device creation. Right now on one of the VMAX arrays, there are about 2006 unbound thin devices. The command to see the list is symdev list with the -tdev and -unbound options. An example is shown below.

symdev list -sid 1687 -tdev -unbound

Symmetrix ID: 000192601687

        Device Name           Directors                  Device
--------------------------- ------------- -------------------------------------
                                                                           Cap
Sym  Physical               SA :P DA :IT  Config        Attribute    Sts   (MB)
--------------------------- ------------- -------------------------------------

020D Not Visible            ???:?  NA:NA  TDEV          N/Grp'd      NR    2048
020E Not Visible            ???:?  NA:NA  TDEV          N/Grp'd      NR    2048
020F Not Visible            ???:?  NA:NA  TDEV          N/Grp'd      NR    2048
0210 Not Visible            ???:?  NA:NA  TDEV          N/Grp'd      NR    2048
0211 Not Visible            ???:?  NA:NA  TDEV          N/Grp'd      NR    2048
0212 Not Visible            ???:?  NA:NA  TDEV          N/Grp'd      NR    2048

Right now on that same array there are 822 DATA devices. Each one is 246,251 MB, which is the maximum size. The command to list DATA devices is the very similar.

symdev list -sid 1687 -datadev

Symmetrix ID: 000192601687

        Device Name           Directors                  Device
--------------------------- ------------- -------------------------------------
                                                                           Cap
Sym  Physical               SA :P DA :IT  Config        Attribute    Sts   (MB)
--------------------------- ------------- -------------------------------------

016D Not Visible            ???:? 08A:C11 RAID-5        N/A     (DT) RW  246251
016E Not Visible            ???:? 07A:C18 RAID-5        N/A     (DT) RW  246251
0171 Not Visible            ???:? 08A:C19 RAID-5        N/A     (DT) RW  246251
0172 Not Visible            ???:? 07A:CA  RAID-5        N/A     (DT) RW  246251

Creating a thin pool and adding DATA devices to it is done via the symconfigure command. To view the details of an individual pool you can use the symcfg command. An example of the output is below. In it you can see the pool, named FC300 contains 592 DATA devices all of which are configured as RAID 5 (7+1). You can see each DATA device in the pool is 90% full, leaving the whole pool at 90% full. You can add a -detail to list the thin devices bound to the pool also. For each thin device you can see how much of it is actually allocated space. In this example, all the thin devices are only 45% allocated. This means that out of all the devices presented to hosts, only 45% of space is actually being used.

symcfg show -sid 1687 -pool FC300 -thin -detail

Symmetrix ID: 000192601687

Symmetrix ID                  : 000192601687
Pool Name                     : FC300
Pool Type                     : Thin
Technology                    : FC
Dev Emulation                 : FBA
Dev Configuration             : RAID-5(7+1)
Pool State                    : Enabled
# of Devices in Pool          : 592
# of Enabled Devices in Pool  : 592

Enabled Devices(592):
  {
   --------------------------------------------------------------
   Sym        Total      Used      Free  Full  Device
   Dev       Tracks    Tracks    Tracks   (%)  State
   --------------------------------------------------------------
   016D     3939984   3556716    383268    90  Enabled
   016E     3939984   3551280    388704    90  Enabled
   1453     3939984   3564948    375036    90  Enabled
   1454     3939984   3564252    375732    90  Enabled
            --------  --------  --------  ----
   Tracks  22690427  20567332  21230953    90
  }

Pool Bound Thin Devices(991):
  {
   -----------------------------------------------------------------------
                       Pool          Pool             Total
   Sym          Total  Subs        Allocated         Written
   Dev         Tracks   (%)     Tracks   (%)     Tracks   (%)  Status
   -----------------------------------------------------------------------
   0231        163845     0      46680    28     130875    80  Bound
   024A       4194360     0    2931972    70    3206055    76  Bound
   2281      33554400     1   20359164    61   20413888    61  Bound
   2301       1048680     0     663456    63     747601    71  Bound
           ---------- ----- ---------- ----- ---------- -----
   Tracks  4450560690   196 2006205744    45 2781458960    62
  }

Virtual LUN Migration is a feature of Symmetrix that moves data between different storage tiers with no outage to the host. Common examples are to move from one class of RAID protection to another, or as data ages it can be moved to less expensive disk. The feature is split into two areas, one for disk provisioning and one for virtual provisioning. Virtual LUN Migration relies on a feature of the Enginuity operating system called RAID Virtual Architecture. This architecture handles the device mirror positions in a way that doesn’t consume all four positions.

Migrations are submitted and managed as sessions with the symconfigure command. At Kimberly Clark almost all migrations are virtual provisioned migrations because nearly all devices used by hosts are thin devices. When a migration for a thin device is started all extents associated with the thin device are moved, and when complete, the thin device is bound to the new pool. Manual migration will take precedence over any FAST VP operations.

Symmetrix VMAX Training Summary (2 of 4)

Posted by on March 2, 2012 2 comments

A few months back I attended a Symmetrix VMAX training class. Afterwards I wrote up a document summarizing some of the training and adding a bit of research into things that weren’t explained in depth as I would have liked.

Configuration

Most configuration changes to the VMAX are made through the SYMCLI (Symmetrix Command Line Interface). There is a GUI interface called SMC, or Symmetrix Management Console.  Unfortunately, the SMC is extremely slow especially because of KC’s larger size. It is fine for some things, but horrible at other things.

Devices are “mapped” to specific front end ports to allow a host server access to a device. Traditionally this is known as LUN masking, and on the VMAX it is performed using “autoprovisioning groups.” In practice, there is nothing automatic about this process and it should really just be called “provisioning groups” because it allows you to apply changes to a group, rather each specific initiator, port, or device, which does save a lot of time. There are three different types of autoprovisioning groups, initiator groups, port groups, and storage groups.

An initiator group is a collection of the WWNs (World Wide Names) from the HBA in the host.  A WWN is just number that uniquely identifies an HBA port. An HBA card usually has two physical ports in it, so the autoprovisioning group usually has two WWNs. Initiator groups can be nested, in what is technically called cascaded initiator groups, so one group can be a member of another group. A port group is a collection of front end ports on the VMAX directors, and each front end port can belong to more than one group. A storage group is a collection of Symmetrix devices, and you can also have a single device in more than one storage group. You can have up to 4,096 devices in a storage group, and there is a limit of 8,192 total storage groups.

Physical disks in Symmetrix are grouped together in disk groups. Each disk group generally contains disks of the same type, for example 15k RPM fibre channel, 10k RPM fibre channel, SATA, or solid state disks. At Kimberly Clark, as new disks are purchased and added to the storage array, they have been put into new disk groups. That is why there are multiple “FC” groups.  The command to view disk groups is symdisk list with the -dskgrp_summary parameter, shown below along with the output for one of the arrays here at KC.

symdisk list -sid 1687 -dskgrp_summary

      Disk Group                Disk                       Capacity
---------------------- ----------------------- --------------------------------
                                 Speed  Size      Total      Free      Actual
Num Name                Cnt Tech (RPM)  (MB)      (MB)       (MB)       (MB)
---------------------- ----------------------- --------------------------------

  1 DISK_GROUP_001      184 FC   15000  279140   51361818       4668   51361818
  2 DISK_GROUP_002       64 SATA  7200  953870   61047661    1316479   74093084
  3 DISK_GROUP_003       32 EFD      0  190782    6105031     194416    6105031
  4 DISK_GROUP_004      120 FC   15000  279140   33496838       3010   33496838
  5 DISK_GROUP_005      112 FC   15000  279140   31263715       2782   31263715
  6 DISK_GROUP_006      120 FC   15000  279140   33496838       3356   33496838
  7 DISK_GROUP_007       56 FC   15000  279140   15631858       1470   15631858
                                               ---------- ---------- ----------
Total                                           232403760    1526182  245449182

After a Symmetrix device is created it must be mapped to a front end port to which the receiving host is connected.  This is done using the symconfigure command. Unmapping can be done through the symaccess command on VMAX arrays.

Special flags can be set on individual front end ports to make the port usable by certain types of devices. For example, the SCSI-3 command set flag must be set on ports that are used for Windows cluster disks. The SCSI-3 persistent reservation feature is important because Window Server 2008 does not support SCSI-2 command set which does not have support for persistent reservations. The persistent reservations are used to allow multiple nodes to access the same device while blocking out other nodes. Also in SCSI-2 reservations were not persistent which means they do not survive reboots. The Volume Set Addressing (VSA) port flag must be set for HP-UX hosts. Volume Set Addressing is used by HP-UX hosts to address the total number of available LUN addresses supported by the Fibre Channel Protocol.

The following command shows details for port 0 on director 7E. You can see the Volume Set Addressing flag is enabled and SCSI-3 support is disabled for this port which means it is used by HP-UX hosts.

symcfg list -sid 1687 -fa 7e -p 0 -v

          SCSI Flags
            {
              Negotiate_Reset(N)           : Disabled
              Soft_Reset(S)                : Disabled
              Environ_Set(E)               : Disabled
              HP3000_Mode(B)               : Disabled
              Common_Serial_Number(C)      : Enabled
              Disable_Q_Reset_on_UA(D)     : Disabled
              Sunapee(SCL)                 : Disabled
              Siemens(S)                   : Disabled
              Sequent(SEQ)                 : Disabled
              Avoid_Reset_Broadcast(ARB)   : Disabled
              Server_On_AS400(A4S)         : Disabled
              SCSI_3(SC3)                  : Disabled
              SPC2_Protocol_Version(SPC2)  : Enabled
              SCSI_Support1(OS2007)        : Enabled
            }

          Fibre Specific Flags
            {
              Volume_Set_Addressing(V)     : Enabled
              Non_Participating(NP)        : Disabled
              Init_Point_to_Point(PP)      : Enabled
              Unique_WWN(UWN)              : Enabled
              Access_Logix(ACLX)           : Enabled
              OpenVMS(OVMS)                : Disabled
              AS400(AS4)                   : Disabled
              Auto_Negotiate(EAN)          : Enabled
            }

Dynamic RDF groups are used to logically group RDF devices from different storage arrays that are participating in replication. The symrdf command is used to create and delete these groups. An individual source device, R1, and the target device, R2, form a RDF pair. Devices can be created as “RDF1 capable,” “RDF2 capable,” or “RDF1 or RDF2 capable.” All devices that we create at KC are “RDF1 or RDF2 capable.” A device that is “RDF1 or RDF2 capable” allows the devices to switch which direction they are replicating.

Symmetrix devices can be grouped together in pools for use with other things like virtual provisioning. There are three types of pools, snap, rdfa_dse, and thin. The first two pools contain SAVE devices, which the later, used for virtual provisioning, contains DATA devices. At KC we only use thin pools. You can not create pools with other device types. The following command lists the thin pools that are setup on one of KC’s VMAX arrays. This output was touched up a bit because the number of tracks is larger than what the table has room for. One of the important pieces of information in this table is how full each pool is.

symcfg list -sid 1687 -pool -thin -gb

                         S Y M M E T R I X   P O O L S
-------------------------------------------------------------------------------
             T T                                                        F   S
             y e                                                        u   t
 Pool        p c Dev   Dev             Total  Enabled     Used     Free ll  a
 Name        e h Emul  Config            GBs      GBs      GBs      GBs (%) te
------------ - - ----- ------------ -------- -------- -------- -------- --- ---
FC300        T F FBA   RAID-5(7+1)  138491.1 138491.1 131199.4   7292.0  94 Ena
FLASH200     T E FBA   RAID-5(7+1)    4809.6   4809.6   2410.2   2399.3  50 Ena
SATA1000     T S FBA   RAID-5(7+1)   50019.2  50019.2  46974.5   3044.9  93 Ena

Total                               -------- -------- -------- -------- ---
GBs                                 193319.9 193319.9 180584.1  12736.2  93

Autoprovisioning which was mentioned earlier is performed using the symaccess command. There is also a symsg (sg for storage group) command that simplifies operations on storage groups. One additional benefit of these groups is that initiators, ports, and devices can all be dynamically added to existing groups to give hosts access to new storage. At KC initiator and storage groups are generally named with the server name. An example command to show storage group information is shown below.

symaccess list -sid 1687 -type storage -name USTCAS72 -v

Symmetrix ID          : 000192601687

Storage Group Name    : USTCAS72
Device Count          : 8
Masking View Count    : 1
Last updated at       : 04:12:49 PM on Tue Nov 08,2011
Masking View Names    : USTCAS72

From these groups, a masking view is created that contains an initiator group, port group, and storage group. When you create the masking view the devices in the storage group become visible to the host. The command symaccess list view can be used to show the list of masking views. Below is an example.

symaccess list view -sid 1687

Symmetrix ID          : 000192601687

Masking View Name   Initiator Group     Port Group          Storage Group
------------------- ------------------- ------------------- -------------------
USTCA554            USTCA554            VSAN_200_300_2      USTCA554
USTWA753            USTWA753            VSAN_600_700        USTWA753
USTWVM450           USTWVM450_Cluster   VSAN_600_700        USTWVM450
USTCCA051           USTCCA051A          VSAN_600_700_2      USTCCA051
USTCCA016           USTCCA016           VSAN_600_700_2      USTCCA016
USTCBK00            USTCBK00            VSAN_600_700_2      USTCBK00
USTCAS32            USTCAS32            VSAN_600_700        USTCAS32

The symaccess command will automatically map devices to a port if needed when the masking view is created. These groups can save lots of time when setting up devices for large VMWare clusters where 25 servers all need access to 100s of devices each with 8 different paths to the array.