SQL Server 2012 AlwaysOn: need to automatically add databases by script- T-sql or PowerShell

I've installed and successfully configured our SQL Server 2012 AlwaysOn 2-node servers for our new "Intranet" that is coming out. I've gotten AlwaysOn working great, and our Front End servers for the Intranet will be using SharePoint 2013. The glitch is that SharePoint 2013 is configured to add databases automatically to our SQL Server 2012 back end, but NOT to AlwaysOn. In reading about this and in contacting Microsoft MSDN support, the default answer is "you must manually find, select, back-up and then add those new databases individually to get them into AlwaysOn."

But wait; that can be quite a task, constantly checking the SQL Server back-end servers to see what databases were created, then having to add them into AlwaysOn, 7/24! I'm looking for a script or process that will check for new databases, back those new databases up in FULL mode, (for being added to AlwaysOn, of course) then add those databases to AlwaysOn, all automatically. Or have this run every...1-2 hours? (without user intervention)

What I've come up with so far is this script that actually identifies the newly-added databases, (not yet in AlwaysOn), and then backs them up to a shared location. My next task is to find those newly-added databases and through the various processes needed, get them added to AlwaysOn. This will involve some sort of looping action, I imagine. I'm not a T-SQL/scripting guru; is there any solution or script that I might access that would do this? (add databases to AlwaysOn automatically)?

Please advise, I'm sure I'm not the first person to have this issue. I have seen previous posts on various Internet Sites (including this one!) , and the solution is either incorrect, or states something like "sure, go ahead and just script that!". Thanks, but I need just a little more detail there.

Thanks again,

-Allen

```
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup

Lots and lots of caveats here. I've tested this in a very limited way in a scenario where the data/log paths are congruent across all replicas, haven't added error handling, etc. You can call this stored procedure from the DDL trigger if you decide to go that way, as Kin suggested, or from a job, or what have you.

See additional comments inline.

CREATE PROCEDURE dbo.AddNewDBsToGroup
  @group SYSNAME = N'your_group_name', -- *** SPECIFY YOUR GROUP NAME HERE ***
  @path  SYSNAME = N'\\atel-web-be2\backups\',
  @debug BIT = 1
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE 
    @sql        NVARCHAR(MAX) = N'',
    @remote_sql NVARCHAR(MAX) = N'';

  DECLARE @t TABLE(db SYSNAME);

  INSERT @t SELECT name FROM sys.databases 
  WHERE replica_id IS NULL AND database_id > 4;

  DECLARE @r TABLE(s NVARCHAR(512));

  -- get the *healthy* replicas available for this group
  -- you'll need error handling to handle cases where any
  -- of the replicas is currently *not* healthy. This 
  -- script does not tell you this happened.

  INSERT @r SELECT r.replica_server_name
  FROM sys.availability_groups AS g
  INNER JOIN sys.dm_hadr_availability_group_states AS s
  ON g.group_id = s.group_id
  INNER JOIN sys.availability_replicas AS r
  ON g.group_id = r.group_id
  AND r.replica_server_name <> @@SERVERNAME
  WHERE g.name = @group
  AND s.primary_replica = @@SERVERNAME
  AND s.primary_recovery_health_desc = 'ONLINE'
  AND s.synchronization_health_desc = 'HEALTHY';

  -- add the database to the group on the primary:

  SELECT @sql += N'ALTER AVAILABILITY GROUP ' 
    + QUOTENAME(@group) + ' ADD DATABASE ' + QUOTENAME(db) + ';'
  FROM @t;

  IF @debug = 1
  BEGIN
    PRINT @sql;
  END
  ELSE
  BEGIN
    EXEC master..sp_executesql @sql;
  END

  -- back up the database locally:
  -- this assumes your database names don't have characters illegal for paths

  SET @sql = N'';

  SELECT @sql += N'BACKUP DATABASE ' + QUOTENAME(db) -- ** BACKUP HAPPENS HERE **
    + ' TO DISK = ''' + @path + db + '.BAK'' WITH COPY_ONLY, FORMAT, INIT, COMPRESSION;
    BACKUP LOG ' + QUOTENAME(db) +
    ' TO DISK = ''' + @path + db + '.TRN'' WITH INIT, COMPRESSION;'
  FROM @t;

  IF @debug = 1
  BEGIN
    PRINT @sql;
  END
  ELSE
  BEGIN
    EXEC master..sp_executesql @sql;
  END

  -- restore the database remotely:
  -- this assumes linked servers match replica names, security works, etc.
  -- it also assumes that each replica has the exact sime data/log paths
  -- (in other words, your restore doesn't need WITH MOVE)

  SET @sql = N'';

  SELECT @sql += N'RESTORE DATABASE ' + QUOTENAME(db) -- ** RESTORE HAPPENS HERE **
    + ' FROM DISK = ''' + @path + db + '.BAK'' WITH REPLACE, NORECOVERY;
    RESTORE LOG ''' + @path + db + '.TRN'' WITH NORECOVERY;
    ALTER DATABASE ' + QUOTENAME(db) + ' SET HADR AVAILABILITY GROUP = '
    + QUOTENAME(@group) + ';'
  FROM @t; 

  SET @remote_sql = N'';

  SELECT @remote_sql += N'EXEC ' + QUOTENAME(s) + '.master..sp_executesql @sql;'
    FROM @r;

  IF @debug = 1
  BEGIN
    PRINT @sql;
    PRINT @remote_sql;
  END
  ELSE
  BEGIN
    EXEC sp_executesql @remote_sql, N'@sql NVARCHAR(MAX)', N'SELECT @@SERVERNAME;';
  END
END
GO

Once you've created the stored procedure, you can call it this way and look at the messages pane to see if it has identified the right group, databases, and servers before ever running it:

EXEC dbo.AddNewDBsToGroup @debug = 1;

When you are confident it is going to do the right thing (and you fully understand what 'the right thing" is), then change that to:

EXEC dbo.AddNewDBsToGroup @debug = 0;

If it fails, don't worry, it will tell you.

StackExchange Database Administrators Q#40272, answer score: 8