SQL Simple and Full Recovery DBs in the same subclient?

Last post 10-07-2020, 7:19 AM by ddwyer. 13 replies.
Sort Posts: Previous Next
  • SQL Simple and Full Recovery DBs in the same subclient?
    Posted: 09-30-2020, 12:21 AM
    Hi Folk,

    Just starting to setup some SQL clients, and wondering if the Simple and FULL databases should be in seperated subclients?



    It seems that CommVault can handle them being in the same subclient, even if you do logs only at intervals, but does that cause problems down the path?



    We're on V11.20



    Cheers,

    STuart

  • Re: SQL Simple and Full Recovery DBs in the same subclient?
    Posted: 09-30-2020, 7:39 PM

    Commvault skips Transaction Log backups for Simple Recovery Model databases.  As for causing problems down the path, well I am not sure what you mean. Are you referring to the possibility that DBA's may revert Full to Simple or vice versa?

    By default, databases that have the simple recovery model are skipped during a transaction log backup operation (https://documentation.commvault.com/commvault/v11/article?p=18184.htm)

  • Re: SQL Simple and Full Recovery DBs in the same subclient?
    Posted: 09-30-2020, 8:25 PM

    Thanks Anthony,

    I didn't see that article (I wasn't thinking interms of conversion) 

    I haven't had much to do with SQL backups previously, but we've got quite a few to set up to get off file level backup, so I wanted to make sure I'm not making more work for myself down the track Laughing

     

    I'm thinking combined will be fine for non-prod DBs, with full overnight and logs 4 hourly (so only an overnight for Simples) but for prod the vague definition I have is 30min logs for fulls, and a midday differential for Simples so I will need to seperate those.

     

    Cheers,

    Stuart

  • Re: SQL Simple and Full Recovery DBs in the same subclient?
    Posted: 10-01-2020, 12:01 AM

    The Schedule is smart enough to skip Transaction Logs for Simple Recovery Model in a subclient, you will however need to manually separate the databases into another subclient if you need different intervals. Also, make sure that you either set up the subclients that support auto-discover for new databases, or if you you want only protect chosen specific databases that your DBA's are aware of that decision so databases are not skipped.

  • Re: SQL Simple and Full Recovery DBs in the same subclient?
    Posted: 10-01-2020, 1:49 AM

    I wanted to have a subclient for FUlls and another for Simples using the Subclient Type and autodiscovery, but if one or the other is empty it gets an error if there are no actual DB's of that type.  I have at least one with no Full Recovery DBs in it at the moment, but I don't know what the DBAs will throw at it/them - I would rather they just autodetect any new DBs and back them up accordingly.

     

    I had wondered if there was a sneaky advanced option that would let it complete quietly if there are no DBs, but I can't see anything......

  • Re: SQL Simple and Full Recovery DBs in the same subclient?
    Posted: 10-01-2020, 1:56 AM

    That is true. Unlike VSA where you can ignore empty subclients, there is no such Additional Setting for SQL. A hack to get around this is putting a System Database in one of the subclients, but I would recommend raising a Customer Modification Request via Commvault Support to get this feature added.

  • Re: SQL Simple and Full Recovery DBs in the same subclient?
    Posted: 10-01-2020, 2:27 AM

    Good idea, I will do both Smile

     

    Where do I put in the modification request?  I can see there's a Feedback Forum (https://forum.commvault.com/forums/23/ShowForum.aspx) but I get the impression that's not what you meant...

  • Re: SQL Simple and Full Recovery DBs in the same subclient?
    Posted: 10-01-2020, 2:30 AM
    Just raise a maintenance advantage support ticket (ma.commvault.com or call them).
  • Re: SQL Simple and Full Recovery DBs in the same subclient?
    Posted: 10-01-2020, 2:38 AM

    Looks like you can do it from cloud.commvault.com too....

     


    Attachment: CMR.jpg
  • Re: SQL Simple and Full Recovery DBs in the same subclient?
    Posted: 10-02-2020, 6:37 PM

    Hi Guys,

    There is an additional setting that can be use to allow empty subclients to succeed in SQLiDA post 11.15. Doesn't look like it's published in customer facing documentation or auto-populating in the GUI however (even in FR20).

    I will see whether this can be handed out here or whether there is some reason for this not being published.

     

    David

  • Re: SQL Simple and Full Recovery DBs in the same subclient?
    Posted: 10-05-2020, 6:04 PM
    Hi Guys,

    Looks like in Job Management settings for SQL Server Job Status on Errors there's an option to mark job complete when no DBs qualify for backup.
    David
  • Re: SQL Simple and Full Recovery DBs in the same subclient?
    Posted: 10-05-2020, 6:38 PM

    Bingo!

    That's what I needed, thank you!

     

    On an unrelated note, the screen shots don't seem to be visible at first - I couldn't see yours and had to go digging, but when I reauthenticated to reply (with a screen shot of where it was) your screenshot appeared Laughing

  • Re: SQL Simple and Full Recovery DBs in the same subclient?
    Posted: 10-06-2020, 12:39 AM

    Yeah,

    I had issues with the screenshot as well. I had to hard refresh after using the attach image uploader button to insert the image. Anyway, glad this solved your issue, if you can mark the thread solved that would be great.

    David

  • Re: SQL Simple and Full Recovery DBs in the same subclient?
    Posted: 10-07-2020, 7:19 AM

    One more thing, dev just gave their blessing to publish this key here.

    If there is a usecase for setting this behaviour at a client level, you can do so with the below additonal setting created on the client. 

    Name: AllowEmptySubclientToSucceedMSSQL
    Category: MSSQLAgent
    Type: INTEGER
    Value: 1

    Description: When set to 1 this additional setting will allow backup to complete for SQL Server subclient which contains no databases, if set to 0 job will go pending.

    David

The content of the forums, threads and posts reflects the thoughts and opinions of each author, and does not represent the thoughts, opinions, plans or strategies of Commvault Systems, Inc. ("Commvault") and Commvault undertakes no obligation to update, correct or modify any statements made in this forum. Any and all third party links, statements, comments, or feedback posted to, or otherwise provided by this forum, thread or post are not affiliated with, nor endorsed by, Commvault.
Commvault, Commvault and logo, the “CV” logo, Commvault Systems, Solving Forward, SIM, Singular Information Management, Simpana, Commvault Galaxy, Unified Data Management, QiNetix, Quick Recovery, QR, CommNet, GridStor, Vault Tracker, InnerVault, QuickSnap, QSnap, Recovery Director, CommServe, CommCell, SnapProtect, ROMS, and CommValue, are trademarks or registered trademarks of Commvault Systems, Inc. All other third party brands, products, service names, trademarks, or registered service marks are the property of and used to identify the products or services of their respective owners. All specifications are subject to change without notice.
Close
Copyright © 2020 Commvault | All Rights Reserved. | Legal | Privacy Policy