Post reply

Automatically detect new databases , add to availability group and assign user permissions

  • May 23, 2018 at 9:04 am

    #339719

    HI There,

    i have a SQL server setup whereby always on is configured but an application is to create new databases on the fly. rather than have an email come in highlightring a new database has been created i would like to setup a job or powershell tin run continuosly and Automatically detect new databases , add to availability group and assign user permissions. has anyone had any experience of similar issues and if so how did you implement it? any information would be great.

    Thanks in advance

  • May 23, 2018 at 11:22 am

    #1991666

    niall5098 - Wednesday, May 23, 2018 9:04 AM

    HI There,

    i have a SQL server setup whereby always on is configured but an application is to create new databases on the fly. rather than have an email come in highlightring a new database has been created i would like to setup a job or powershell tin run continuosly and Automatically detect new databases , add to availability group and assign user permissions. has anyone had any experience of similar issues and if so how did you implement it? any information would be great.

    Thanks in advance

    Instead of a continuously running process, you may want to look at using a DDL trigger for the create_database event. It would fire in response to creating a database. There is an example of a create database trigger in the documentation - it doesn't do what you are looking for but should give you the basic idea:
    DDL Triggers

    Sue

  • May 25, 2018 at 1:44 pm

    #1992032

    You could do a DDL trigger on create database as Sue has mentioned, but be sure to test that thoroughly because if the trigger fails the create database will fail.  I don't think you want that to happen.  I might do a DDL trigger that enters the database name in queue table instead of adding it to the AG.  Then have a process that runs and uses that table to add to the AG and then uses DBATools.io to copy permissions to the secondary(s) which is what I assume you mean by assigns user permissions.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply