Automatic peer SVM selection in WFA

[Richard]

I am setting up a workflow to create a volume with cascaded protection:

• Primary volume $PrimaryVolumeName in $PrimaryVserver
• SnapVault from primary to $VaultVolumeName in $VaultVserver
• SnapMirror from vault to $MirrorVolumeName in $MirrorVserver

I know there is a canned workflow for cascading Vault+Mirror but that has the freedom to chose destination clusters and destinations SVM:s and will create cluster/SVM peering if needed. I would like to look up the existing SVM peer relationships and only display a list of those to aviod any mistakes. The information is in the vserver_peer table in WFA but I cannot figure out how to stitch it all together in an SQL query: "give me a list of all the SVM:s that are peered with $PrimaryVserver - and when I select one SVM, show me a list of aggregates in the cluster where it resides". Any advice?

Best regards

Richard

[sheelnidhig · Accepted solution]

Hello Richard,

I have used the search and define function so i first check the peer cluster of the primary cluster and then used to search the peer SVM of a primary SVM.

Query to select the peer SVM of a selected Primary SVM is:

SELECT
vserver.name,
cluster.primary_address AS 'cluster.primary_address'
FROM
cm_storage.cluster cluster,
cm_storage.vserver vserver,
(
Select
cm_storage.vserver_peer.peer_vserver_id as 'peer_svm_id'
From
cm_storage.vserver_peer,
cm_storage.vserver
Where
cm_storage.vserver_peer.vserver_id = cm_storage.vserver.id
And cm_storage.vserver.name = '${PrimarySVM}' ) as peersvmid
where
vserver.cluster_id = cluster.id
AND cluster.name = '${bkpcluster}'
AND vserver.is_repository IS NOT TRUE
AND (
vserver.type = 'clustear'
OR vserver.type = 'data'
)
AND vserver.name NOT LIKE '${bkpcluster}%'
and vserver.operational_state = 'running'
AND vserver.name NOT LIKE "%mc"
and vserver.id = peersvmid.peer_svm_id
and vserver.name not like '${PrimarySVM}'
ORDER BY
vserver.name ASC

Query to Select peer cluster of a primary cluster is:

SELECT
cluster.name, -- cluster.is_metrocluster,
cluster.primary_address AS 'primary_address'
FROM
cm_storage.cluster,
(
Select
cm_storage.cluster_peer.peer_cluster_id as 'peer_cluster_id'
From
cm_storage.cluster_peer,
cm_storage.cluster
Where
cm_storage.cluster_peer.primary_cluster_id = cm_storage.cluster.id
And (
cm_storage.cluster.name = '${cluster}'
Or cm_storage.cluster.primary_address = '${cluster}'
)) as peerid
WHERE
 cluster.id = peerid.peer_cluster_id
ORDER BY
name ASC

It has worked for me so far.

,Sheel

[MattInCO]

@Richard

It looks like Sheel has a workable option for you, but I figured I would throw in mine for reference. I am using this strictly for SnapMirror, but I think it will work for your purposes since the peering should be the same. If not, hopefully a little tweaking would get it there. My standard disclaimer: I am not a SQL query guru by any stretch, but at least this seems to work for me.

For the peered cluster:

SELECT
    dest_cluster.name AS "Destination Custer"
FROM
    cm_storage.vserver_peer,
    cm_storage.cluster src_cluster,
    cm_storage.vserver src_vserver,
    cm_storage.cluster dest_cluster,
    cm_storage.vserver dest_vserver
WHERE
    vserver_peer.peer_vserver_id = src_vserver.id
    AND vserver_peer.vserver_id = dest_vserver.id
    AND dest_vserver.cluster_id = dest_cluster.id
    AND vserver_peer.peer_state = 'peered'
    AND vserver_peer.peer_vserver_local_name = '${sourcesvm}'
    AND dest_vserver.is_repository IS NOT TRUE
    AND (
        dest_vserver.type = 'cluster'
        OR dest_vserver.type = 'data'
    )
    AND dest_vserver.admin_state='running'
GROUP BY
    dest_cluster.name ASC

For the peered SVM:

SELECT
    dest_vserver.name AS "Destination SVM"
FROM
    cm_storage.vserver_peer,
    cm_storage.cluster src_cluster,
    cm_storage.vserver src_vserver,
    cm_storage.cluster dest_cluster,
    cm_storage.vserver dest_vserver
WHERE
    vserver_peer.peer_vserver_id = src_vserver.id
    AND vserver_peer.vserver_id = dest_vserver.id
    AND vserver_peer.peer_state = 'peered'
    AND vserver_peer.peer_vserver_local_name = '${sourcesvm}'
    AND dest_vserver.is_repository IS NOT TRUE
    AND (
        dest_vserver.type = 'cluster'
        OR dest_vserver.type = 'data'
    )
    AND dest_vserver.admin_state='running'
GROUP BY
    dest_vserver.name ASC

And an aggregate selection based on those showing only what that SVM can see, which may have way more info than you need (this assumes peered cluster variable of $destcluster and peered SVM variable of $destsvm) – I normally use Search or Define steps to determine aggregates anymore:

SELECT
    aggregate.name AS 'Aggregate Name',
    node.name AS ' Node',
    aggregate.size_mb/1048576 AS 'Total Size (TB)',
    aggregate.used_size_mb/1048576 AS 'Used Size (TB)',
    aggregate.available_size_mb/1048576 AS 'Available Size (TB)',
    IFNULL(aggregate.volume_count,
    0) AS 'Volume Count',
    REPLACE(disk_info_table.disk_type,
    ',',
    ' and ') AS 'Disk Type',
    IF (aggregate.is_hybrid IS NULL,
    'Not available',
    IF (aggregate.is_hybrid,
    'True',
    'False')) AS 'Is FlashPool'
FROM
    cm_storage.cluster,
    cm_storage.node,
    cm_storage.vserver,
    (
    SELECT
        GROUP_CONCAT(DISTINCT(disk.type)) AS disk_type,
        disk_aggr.aggregate_id AS aggr_id
    FROM
        cm_storage.disk DISK,
        cm_storage.disk_aggregate disk_aggr
    WHERE
        disk_aggr.disk_id = disk.id
    GROUP BY
        disk_aggr.aggregate_id) AS disk_info_table,
    cm_storage.aggregate
WHERE
    cluster.name = '${destcluster}'
    AND node.cluster_id = cluster.id
    AND aggregate.node_id = node.id
    AND aggregate.name NOT LIKE '%aggr0%'
    AND (
        aggregate.has_local_root IS NULL || aggregate.has_local_root = 0
    )
    AND (
        aggregate.has_partner_root IS NULL || aggregate.has_partner_root = 0
    )
    AND disk_info_table.aggr_id = aggregate.id
    AND vserver.name = '${destsvm}'
    AND (
        vserver.restricted_aggregate_count = 0 || aggregate.id IN (
            SELECT
                aggregate_id
            FROM
                cm_storage.vserver_allowed_aggregate
            WHERE
                vserver_id = vserver.id
                AND vserver.cluster_id = cluster.id
        )
    )
ORDER BY
    aggregate.name ASC,
    node.name,
    aggregate.size_mb,
    aggregate.used_size_mb,
    aggregate.available_size_mb,
    disk_info_table.disk_type

Hopefully there is some useful info in there.

-Matt

[Richard]

Awesome, both solutions worked like a charm - many thanks to both of you!

Cheers / Richard