How to add newly created Database to Alwayson Availability Group (AAG) automatically

This script help you to add newly created Database to Alwayson Availability Group (AAG) automatically , as soon as passible.

In the first step, the following lines should be executed only once in all nodes.

use master
go
-- Rename the avgShayan to your desired name
ALTER AVAILABILITY GROUP [avgShayan] GRANT CREATE ANY DATABASE;
go

After performing the above change, you can now add the database to your AAG with the following script.

This script must be scheduled as a job in all nodes to run automatically in a period of time (every 15 min).

Use master
go


DECLARE @dbid int, @dbname nvarchar(50),  
    @sqlrecovermodel varchar(300),@sqlbackup varchar(300),  
	@sqlavgmodrep varchar(300),@sqlavgadddb varchar(300),
	@avgname varchar(200),@replicaname varchar(200),
	@replicastate varchar(100);


select @avgname=name from sys.availability_groups_cluster


select @replicastate=role_desc from sys.dm_hadr_availability_replica_states
where is_local=1


if (@replicastate='PRIMARY')
Begin
	
	DECLARE dblist CURSOR FOR   
	select d.database_id as dbid ,d.name as dbname from sys.databases d
	where 
	d.name not in ('master','tempdb','model','msdb') and
	d.name not in (
	SELECT db.name FROM master.sys.dm_hadr_database_replica_states rs
	JOIN master.sys.databases db ON rs.database_id = db.database_id
	WHERE is_local = 1 AND is_primary_replica = 1)
	  
	OPEN dblist  
	  
	FETCH NEXT FROM dblist   
	INTO @dbid, @dbname  
	  
	WHILE @@FETCH_STATUS = 0  
	BEGIN  
	    
		set @sqlrecovermodel='ALTER DATABASE ['+@dbname+'] SET RECOVERY FULL WITH NO_WAIT;';
		Execute (@sqlrecovermodel);
		
		set @sqlbackup='backup database ['+@dbname+'] to disk=''nul'' with compression;';
		Execute (@sqlbackup);
	  
			DECLARE replicalist CURSOR FOR   
			select rc.replica_server_name from sys.dm_hadr_availability_replica_cluster_states rc join sys.dm_hadr_availability_replica_states rs
			on rc.replica_id=rs.replica_id
			where rs.is_local=0
			
			OPEN replicalist  
			
			FETCH NEXT FROM replicalist   
			INTO @replicaname 
			
			WHILE @@FETCH_STATUS = 0  
			BEGIN  
			
				set @sqlavgmodrep='ALTER AVAILABILITY GROUP ['+@avgname+'] MODIFY REPLICA ON N'''+@replicaname+''' WITH (SEEDING_MODE = AUTOMATIC);';
				Execute (@sqlavgmodrep);
				
				FETCH NEXT FROM replicalist   
				INTO @replicaname 
			END   
			CLOSE replicalist;  
			DEALLOCATE replicalist;
	  
		set @sqlavgadddb='ALTER AVAILABILITY GROUP ['+@avgname+'] ADD DATABASE ['+@dbname+'];';
		Execute (@sqlavgadddb);
	  
		
		FETCH NEXT FROM dblist   
	    INTO @dbid, @dbname  
	END   
	CLOSE dblist;  
	DEALLOCATE dblist;


END;


go

Bahram Shayan @990605