Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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  

-- specify database backup directory
SET @path = '\\atel-web-be2\backups\'  

DECLARE db_cursor CURSOR FOR  
select name from sys.databases
where group_database_id is null and replica_id is null 
    and name not in('master','model','msdb','tempdb')

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
WHILE @@FETCH_STATUS = 0   
BEGIN   
    SET @fileName = @path + @name + '.BAK'  
    BACKUP DATABASE @name TO DISK = @fileName   
    FETCH NEXT FROM db_cursor INTO @name   
END 

CLOSE db_cursor   
DEALLOCATE db_cursor
share|improve this question

migrated from stackoverflow.com Apr 17 '13 at 20:03

This question came from our site for professional and enthusiast programmers.

    
Where you do define the group(s) you want to add the database(s) to? –  Aaron Bertrand Apr 17 '13 at 19:55
    
Also do you have linked servers set up for each of the replicas? And what do you plan to do in the event that the group has failed over to a different replica? –  Aaron Bertrand Apr 17 '13 at 20:22
    
Aaron: I not yet defined the Availabilty Group that I want to add the databases to; I don't know how to put that into the script. I'm not that good with T-SQL scripts, When a failover occurs, I'd like this script to of course, still work so the databases are added to the Active replica in AlwaysOn. This is what makes this so complex; it needs to work in the event of a failover. But yet, this is so basic and essential; when a database is added, manual intervention should not be needed to insure the databases are added to AlwaysOn. –  AllenValk66 Apr 18 '13 at 15:58
    
You're talking about two very different events when you mention failover and adding a database. Also I don't know how you can add a database to an availability group when you've just created the database and no availability group exists. Are you trying to create a distinct availability group for every database? WHY? –  Aaron Bertrand Apr 18 '13 at 16:01
2  
This is a fairly advanced topic in SQL Server. I strongly suggest you stop approaching it as an "I just need a script that works" problem. You need to learn the fundamentals of how scripts works, how to create and alter stored procedures, how to call stored procedures, etc. Server admin for 20 years or not, you're going to have the same challenges if you turn to PowerShell or any other tech to solve this problem - you need to have a good grasp on the fundamentals and what is going on underneath a script you can copy, paste and run before you should ever consider running it. –  Aaron Bertrand Apr 18 '13 at 18:11
show 2 more comments

5 Answers 5

up vote 3 down vote accepted

You don't have to write a cursor tsql script to check for new database created and schedule it to run for e.g. every minute. Instead use EVENTDATA() function in conjunction with server level trigger.

IF EXISTS (SELECT * FROM sys.server_triggers
    WHERE name = 'ddl_trig_database')
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO
CREATE TRIGGER ddl_trig_database 
ON ALL SERVER 
FOR CREATE_DATABASE 
AS 
    PRINT 'Database Created.'
    SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO

Since you now have an automated mechanism in place that will fire up when a new database is created, you can use ALTER AVAILABILITY GROUP and ALTER DATABASE - SET HADR

basically you have to just include :

-- Move each database into the Availability Group
-- Change database name and Group as per your environment.
ALTER DATABASE Test1 SET HADR AVAILABILITY GROUP = TestAG
ALTER DATABASE Test2 SET HADR AVAILABILITY GROUP = TestAG
GO

Thinking of this a little bit more, you can be more creative to automate it --

-- create a driver table
create table AlwaysON_Candidates (
    DatabaseName sysname
    ,createdate datetime default getdate()
    ,IsAlwaysOnMember bit default 0 -- 0 = Not a part of AG
    ) -- 1 = Is part of AG
go
-- below insert will be governed by the server level trigger
insert into AlwaysON_Candidates (DatabaseName)
values ('Test1')
--- check the values in the driver table
select *
from AlwaysON_Candidates
--- add database to AG
alter database Test1
set HADR AVAILABILITY group = TestAG
-- update the bit in the driver table AlwaysON_Candidates
update AlwaysON_Candidates
set IsAlwaysOnMember = 1
where DatabaseName = 'Test1'

some good references for setting it up using tsql can be found here and here

EDIT: Below script will help you. Obviously you have to Understand it and test it in a test environment.

/************************************************************************************
Author  : Kin Shah
Version : 1.0.0 for dba.stackexchange.com

Note:   This script does not have ERROR handling and is not tested.
        Use at your own risk, It will print out the sql statements, but wont execute it
        unless the print statements have been modified to use "exec"

        UNDERSTAND the script and then test it on a TEST environment !!!!!!!!

*************************************************************************************/
-- create table 
set ansi_nulls on
go

set quoted_identifier on
go

create table AlwaysON_Candidates (
    ID int identity(1, 1)
    ,EventType nvarchar(128) null
    ,DatabaseName nvarchar(128) null
    ,LoginName nvarchar(128) null
    ,UserName nvarchar(128) null
    ,AuditDateTime datetime null
    ,IsAlwaysOnMember bit default 0
    )
go

alter table [dbo].[AlwaysON_Candidates] add default((0))
for [IsAlwaysOnMember]
go

-- create server trigger
if exists (
        select *
        from master.sys.server_triggers
        where parent_class_desc = 'SERVER'
            and name = N'ddl_trig_database'
        )
    drop trigger [ddl_trig_database] on all server
go

set ansi_nulls on
go

set quoted_identifier on
go

create trigger [ddl_trig_database] on all server
for CREATE_DATABASE as

insert into NewDatabases
select EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)') as EventType
    ,EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)') as DatabaseName
    ,EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)') as LoginName
    ,EVENTDATA().value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(128)') as UserName
    ,GETDATE() as AuditDateTime
    ,0 as IsAlwaysOnMember
go

set ansi_nulls off
go

set quoted_identifier off
go

ENABLE trigger [ddl_trig_database] on all server
go

--- PREREQUISITE ... CREATE A LINKED SERVER FROM PRIMARY TO SECONDARY SERVER !!!
--- fill in *** CHANGE HERE values 
--- test it on a TEST server
--- Not tested and not responsible for any dataloss. UNDERSTAND it and test it before implementing it.
declare @databasename varchar(max)
declare @sqlbackup varchar(max)
declare @sqlrestore varchar(max)
declare @PrimaryAG varchar(max)
declare @SecondaryAG varchar(max)
declare @backupPath varchar(max)

set @backupPath = '\\servername\sharedfolder\' --- *** CHANGE HERE

declare @group sysname

set @group = N'your_group_name' --- *** CHANGE HERE

declare @remotesql1 varchar(max)
declare @remotesql2 varchar(max)
declare @linkedserverName sysname

set @linkedserverName = 'kin_test_AG_LS' --- *** CHANGE HERE

select @databasename = min(DatabaseName)
from AlwaysON_Candidates
where IsAlwaysOnMember = 0

while @databasename is not null
begin
    -- ** BACKUP HAPPENS HERE **
    select @sqlbackup = N'BACKUP DATABASE ' + QUOTENAME(@databasename) + ' TO DISK = ''' + @backupPath + @databasename + '_forAG.BAK'' WITH COPY_ONLY, FORMAT, INIT, COMPRESSION;
    BACKUP LOG ' + QUOTENAME(@databasename) + ' TO DISK = ''' + @backupPath + @databasename + '_forAG.TRN'' WITH INIT, COMPRESSION;'
    from AlwaysON_Candidates
    where IsAlwaysOnMember = 0

    print @sqlbackup --- *** CHANGE HERE for EXEC master..sp_executesql @sqltext

    -- ** RESTORE HAPPENS HERE **
    select @sqlrestore = N'RESTORE DATABASE ' + QUOTENAME(@databasename) + ' FROM DISK = ''' + @backupPath + @databasename + '_forAG.BAK'' WITH REPLACE, NORECOVERY;
    RESTORE LOG ''' + @backupPath + @databasename + '_forAG.TRN'' WITH NORECOVERY;'

    print @sqlrestore

    select @remotesql1 = N'EXEC ' + QUOTENAME(@linkedserverName) + '.master..sp_executesql @sqlrestore;'

    print @remotesql1 --- *** CHANGE HERE for EXEC master..sp_executesql @sqltext

    -- join the AG group on primary
    select @PrimaryAG = N'ALTER AVAILABILITY GROUP ' + QUOTENAME(@group) + ' ADD DATABASE ' + QUOTENAME(@databasename) + ';'

    print @PrimaryAG --- *** CHANGE HERE for EXEC master..sp_executesql @sqltext

    -- join the AG group on secondary
    select @SecondaryAG = 'ALTER DATABASE ' + QUOTENAME(@databasename) + ' SET HADR AVAILABILITY GROUP = ' + QUOTENAME(@group) + ' ;'

    print @SecondaryAG

    select @remotesql2 = N'EXEC ' + QUOTENAME(@linkedserverName) + '.master..sp_executesql @sqlrestore;'

    print @remotesql2 --- *** CHANGE HERE for EXEC master..sp_executesql @SecondaryAG

    -- finally update the table 
    update AlwaysON_Candidates
    set IsAlwaysOnMember = 1
    where DatabaseName = @databasename

    -- go to another database if it is added newly
    select @databasename = min(DatabaseName)
    from AlwaysON_Candidates
    where IsAlwaysOnMember = 0
        and DatabaseName > @databasename
end
share|improve this answer
1  
Except this doesn't show all the interim steps that are required. (For example, where is your restore sequence?) –  Aaron Bertrand Apr 17 '13 at 20:49
    
@AaronBertrand edited my answer to reflect some creative idea to consider for automation along with some references on setting up AlwaysOn using TSQL. Currently, time does not permit me to write up the script that will do what I have mentioned. But the OP will get an idea on how it can be done. –  Kin Apr 17 '13 at 21:06
1  
Kin: you wrote: the OP will get an idea on how it can be done. Who is the OP? (not sure what OP is) Not being a T-SQL/scripting master, I'm not sure how to take what you've written and expand upon it. Thoughts? –  AllenValk66 Apr 18 '13 at 15:59
    
@AllenValk66 you are the OP (original poster). –  Aaron Bertrand Apr 18 '13 at 16:02
    
@AllenValk66 Apologies for abbreviation .. OP = Original Poster (learned it from sqlserver central :-)). I meant to give a starting point to provide what can be done as currently I dont have an enviornment that I can test the script on. The references provide have some t-sql code. What I have mentioned is simple -- Create a serverlevel trigger to log the newely created databases into a table (I have given the table structure as well) and then using a while loop the databases can be added to the Availablity group (following the tsql code that I have referenced or Aaron's code as well). –  Kin Apr 18 '13 at 16:10
show 3 more comments

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.

share|improve this answer
    
The above command to "ALTER DATABASE Test1 SET HADR AVAILABILITY GROUP = TestAG" will not (and does not) work until the database and its log file is backed up and restored to the Secondary server. I've tried this command on a stand-alone database, and while it is added into the AlwaysON configuration, it is in a serious error state. This is because it's not been fully copied and restored to the Secondary, first. This process must be done for each database, in order to have a successful inclusion into AlwaysOn. –  AllenValk66 Apr 18 '13 at 15:59
    
@AllenValk66 Please look closer at the script. I do perform the backup and restore sequences necessary. As I said, I've tested this. –  Aaron Bertrand Apr 18 '13 at 16:03
    
Sorry, I didn't scroll down all the way. My bad. :-( –  AllenValk66 Apr 18 '13 at 16:07
    
@AllenValk66 well, I don't call SET HADR ... until very close to the bottom, so I don't know how you saw that without seeing the backup/restore too. shrug –  Aaron Bertrand Apr 18 '13 at 16:09
2  
Great, you've created the stored procedure. Now you need to call it, e.g. EXEC dbo.AddNewDBsToGroup;. Also note that if you want to test it you can specify @debug = 1 - you won't see all the commands that will be run but it will print out most of them. Now, don't take this the wrong way, but can I ask who put you in charge of HA/DR at your company if you can't get past these minor issues? –  Aaron Bertrand Apr 18 '13 at 17:48
show 5 more comments

It's a pretty simple exercise to do this. Here's a cut-down version of a script I have at home for doing a some cool things. Some notes:

  1. When a database is created, put it into a queue for AlwaysOn initialization. You cant do it right off a 'CREATE DATABASE' due to the implicit transaction that occurs. The backup that needs to happen is blocked by SQL otherwise.

  2. In my code the 'Availability Group' to create on is automatically detected - it's the group you were connected to when you issued CREATE DATABASE (allowing applications to self-populate AlwaysOn). If you connect direct to a server name, no automatic replication will occur since it can't find the AG name.

  3. You need a linked server on every server on the availability group pointing back to the other servers (name must be the same as the hostname) with 'RPC' and 'RPC OUT' permissions and suitable credentials. This means in most cases enabling mixed authentication and having an 'sa' type account.

  4. Call the HADR_REPLICATE_QUEUE procedure from 'master' periodically. Say, every 5-10 minutes. This means you wont instantly have HA on a database, but it'll happen shortly.

  5. The path the backups happen to on all servers must be accessible to every server in the group (i.e. a UNC share). I automatically scrape it from the default instance backup path that's set during installation.

As you can see - it's designed to be as hands-off as possible for me. I also catch CREATE LOGIN in a similar way to ensure all nodes remain consistent where possible.

Script looks like:

/**
 * AlwaysOn Self-Population Script
 * By: Steve Gray / steve@mostlyharmful.net
 * Usage: Free, but buy me a beer if you're ever in Brisbane.
 **/
USE [master]
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name='hadr_pending_replicate')
    BEGIN
        DROP TABLE hadr_pending_replicate;
    END;
GO
CREATE TABLE hadr_pending_replicate (database_name VARCHAR(512) PRIMARY KEY NOT NULL, availability_group_name VARCHAR(2048) NOT NULL);
GO
IF EXISTS (SELECT * FROM sys.server_triggers WHERE name='ddl_hadr_autoreplicate')
    BEGIN
        DROP TRIGGER ddl_hadr_autoreplicate ON ALL SERVER;
    END;
GO
CREATE TRIGGER ddl_hadr_autoreplicate ON ALL SERVER
    FOR CREATE_DATABASE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @DatabaseName NVARCHAR(2048)

    -- Find the availability group, if the CREATE DATABASE is occuring on an AG listener.
    DECLARE @AddToGroupName         VARCHAR(512);
    SELECT TOP 1
        @AddToGroupName         = [AG].[name]
    FROM
        sys.availability_groups AS [AG]
            INNER JOIN sys.availability_group_listeners AS [LS] ON [LS].[group_id] = [AG].[group_id]
                INNER JOIN sys.availability_group_listener_ip_addresses AS [IP] ON [IP].[listener_id] = [LS].[listener_id]
                    INNER JOIN sys.dm_exec_connections AS [CN] ON [CN].[local_net_address] = [IP].[ip_address] AND [CN].[local_tcp_port] = [LS].[port]
    WHERE
        [CN].[session_id] = @@SPID;

    SET @DatabaseName = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)'));

    -- We have to use a queue since initial backups cant happen during the CREATE DATABASE trigger firing.
    IF @AddToGroupName IS NOT NULL
        BEGIN
            PRINT 'Database is queueing for HADR replication';
            DELETE FROM hadr_pending_replicate WHERE database_name = @DatabaseName;
            INSERT INTO hadr_pending_replicate SELECT @DatabaseName, @AddToGroupName;
        END
END;
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE name='hadr_process_replicate')
    BEGIN
        DROP PROCEDURE hadr_process_replicate;
    END;
GO
/**
 * This script automatically performs a few tasks when a database is created via a connection
 * to a SQL Server availability group listener:
 *
 *     1) Switch the database from 'SIMPLE' to 'FULL' recovery.
 *     2) Perform a full backup to the default backup path for the server.
 *     3) Connect to other servers in the availability group and stage a WITH NO RECOVERY restore.
 *     4) Add the database to the availability group and initialize AlwaysOn.
 *
 * For this to work, you must have linked servers on all your nodes with the same name as the Windows
 * host name (i.e. SERVERNAME). If in doubt, look at replica_server_name from sys.availability_replicas.
 * Linked servers must have RPC and RPC OUT options set to true. Script assumes that the backup destination 
 * is accessible to every other server too. 
 **/
CREATE PROCEDURE hadr_process_replicate
    @AddToGroupName VARCHAR(2048),
    @DatabaseName VARCHAR(2048)
AS
BEGIN
    DECLARE @AvailabilityGroupID    UNIQUEIDENTIFIER;
    DECLARE @BackupDestination      VARCHAR(2048);
    DECLARE @BackupSuffix           VARCHAR(2048) = '_Initial.bak';

    SET @AvailabilityGroupID = (SELECT group_id FROM sys.availability_groups WHERE name=@AddToGroupName);

    -- Switch the database to FULL recovery if it was created without it.
    IF (SELECT recovery_model FROM sys.databases WHERE name=@DatabaseName) <> 1
        BEGIN
            PRINT 'Changing recovery model to FULL';
            DECLARE @ModeChange NVARCHAR(512) = 'ALTER DATABASE [' + @DatabaseName + ']  SET RECOVERY FULL WITH NO_WAIT';
            EXEC sp_executesql @ModeChange;
        END;
    ELSE
        BEGIN
            PRINT 'Database is already in FULL recovery mode.'
        END;


    -- Read the default backup path from the SQL Server configuration here. This path needs to be accessible to all the servers in
    -- the availability group.
    EXEC master.dbo.xp_instance_regread  N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', @BackupDestination OUTPUT, 'no_output';
    DECLARE @TargetFile VARCHAR(2048) = @BackupDestination + '\' + @DatabaseName + @BackupSuffix;

    -- Perform initial backup of the database - Will overwrite any existing file.
    PRINT 'Backing up initial database to ' + @TargetFile;
    DECLARE @BackupCommand NVARCHAR(2048) = 'BACKUP DATABASE [' + @DatabaseName + '] TO  DISK = N''' + @TargetFile + ''' WITH INIT, NOFORMAT, NAME = N''Initial backup for HADR seeding'', SKIP, NOREWIND, NOUNLOAD,  STATS = 100';
    PRINT '    Command: ' + @BackupCommand
    EXEC (@BackupCommand)

    PRINT 'Joining database to availability group'
    DECLARE @JoinToAG NVARCHAR(2048) = 'ALTER AVAILABILITY GROUP [' + @AddToGroupName + '] ADD DATABASE [' + @DatabaseName + ']';
    EXEC sp_executesql @JoinToAG;

    -- Loop through all availability replicas
    DECLARE @Replicas TABLE (ReplicaName VARCHAR(512))
    INSERT INTO @Replicas   -- Have to use a table, since T-SQL wasnt giving me 
                            -- all replicas when I did this straight via the cursor.... (Bug?)
        SELECT replica_server_name 
            FROM 
                sys.availability_replicas 
            WHERE group_id=CAST(@AvailabilityGroupID AS VARCHAR(512));

    DECLARE cur_Replicas CURSOR FOR SELECT ReplicaName FROM @Replicas INNER JOIN sys.servers [SV] ON [SV].[name] = [ReplicaName] AND [SV].[is_linked] = 1 ORDER BY [ReplicaName];
    OPEN cur_Replicas;
    DECLARE @CurrentReplica VARCHAR(255)
    FETCH NEXT FROM cur_Replicas INTO @CurrentReplica;
    WHILE @@FETCH_STATUS >= 0
        BEGIN
            PRINT 'Restoring initial backup to ' + @CurrentReplica;
            DECLARE @RestoreCommand VARCHAR(2048) = 'RESTORE DATABASE [' + @DatabaseName + '] FROM  DISK = N''' + @TargetFile + ''' WITH  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5;'
            -- The 'inception' moment.
            DECLARE @DoubleDynamicRestore NVARCHAR(2048) = 'EXEC (''' +  REPLACE(@RestoreCommand, '''', '''''') + ''') AT ' + @CurrentReplica;
            PRINT @DoubleDynamicRestore
            EXEC sp_executesql @DoubleDynamicRestore;

            PRINT 'Bringing replica online'     
            DECLARE @DynamicAddHADR NVARCHAR(2048) = 'EXEC (''ALTER DATABASE [' + @DatabaseName + '] SET HADR AVAILABILITY GROUP = [' + @AddToGroupName +'];'') AT ' + @CurrentReplica;
            EXEC sp_executesql @DynamicAddHADR

            FETCH NEXT FROM cur_Replicas INTO @CurrentReplica;
        END;
    CLOSE cur_Replicas;
    DEALLOCATE cur_Replicas;
END;
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE name='hadr_replicate_queue')
    BEGIN
        DROP PROCEDURE hadr_replicate_queue;
    END;
GO
/** 
 * Process all pending HADR replicates.
 **/
CREATE PROCEDURE hadr_replicate_queue
AS
BEGIN
    DECLARE cur_ReplicationTask CURSOR FOR
        SELECT database_name, availability_group_name FROM hadr_pending_replicate WITH(HOLDLOCK)
    OPEN cur_ReplicationTask;

    DECLARE @DB VARCHAR(512), @AG VARCHAR(2048)
    FETCH NEXT FROM cur_ReplicationTask INTO @DB, @AG
    WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC hadr_process_replicate @AddToGroupName = @AG, @DatabaseName = @DB
            FETCH NEXT FROM cur_ReplicationTask INTO @DB, @AG
        END;

    DELETE FROM hadr_pending_replicate;

    CLOSE cur_ReplicationTask;
    DEALLOCATE cur_ReplicationTask
END;
GO

The core of the process is:

  1. Switch database to FULL recovery.
  2. Back it up to a known location.
  3. Add it to the availability group definition.
  4. Restore it to all servers and 'join' to the group.

Any questions, drop me an email.

-Steve Gray

share|improve this answer
add comment

I found the answer, in working with MSDN and modifying the script:

xp_cmdshell 'del /F /Q /S \atel-vm-test\backup*.*'

--drop table #dbs --deallocate adddbs IF OBJECT_ID('dbo.#dbs', 'U') IS NOT NULL DROP TABLE dbo.#dbs

--IF CURSOR_STATUS('global','adddbs')>=-1 --BEGIN -- DEALLOCATE adddbs --END

IF (SELECT CURSOR_STATUS('global','adddbs')) >=0 BEGIN DEALLOCATE adddbs END

create table #dbs(a int primary key identity, dbname varchar(100))

declare @nextdb varchar(100) declare @restorestring varchar(200)

--Populate temp table insert into #dbs(dbname) --select name from sys.databases where create_date between getdate()-1 and getdate()

select name from sys.databases where group_database_id is null and replica_id is null and name not in('master','model','msdb','tempdb')

--Create a cursor to declare adddbs cursor for select dbname from #dbs

open adddbs

FETCH NEXT FROM adddbs INTO @nextdb

WHILE @@FETCH_STATUS = 0 BEGIN

EXEC ('BACKUP DATABASE ' + @nextdb + ' TO DISK = ' + '''\atel-vm-test\backup\' + @nextdb + 'initialize.bak''')

EXEC ('ALTER AVAILABILITY GROUP [atel-testAG] ADD DATABASE ' + @nextdb)

EXEC ('BACKUP DATABASE ' + @nextdb + ' TO DISK = ' + '''\atel-vm-test\backup\' + @nextdb + '.bak''')

EXEC ('BACKUP LOG ' + @nextdb + ' TO DISK = ' + '''\atel-vm-test\backup\' + @nextdb + '_log.bak''')

set @restorestring='sqlcmd -S atel-vm-test2 -E -Q"RESTORE DATABASE ' + @nextdb + ' FROM DISK = ' + '''\atel-vm-test\backup\' + @nextdb + '.bak''' + ' WITH NORECOVERY, NOUNLOAD, STATS = 5"' exec xp_cmdshell @restorestring print 'sqlcmd -S [atel-vm-test2] -E -Q"RESTORE DATABASE ' + 'agd2' + ' FROM DISK = ' + '''\atel-vm-test\backup\' + 'agd2' + '.bak''' + ' WITH NORECOVERY, NOUNLOAD, STATS = 5"' set @restorestring='sqlcmd -S atel-vm-test2 -E -Q"RESTORE LOG ' + @nextdb + ' FROM DISK = ' + '''\atel-vm-test\backup\' + @nextdb + '_log.bak''' + ' WITH NORECOVERY, NOUNLOAD, STATS = 5"' exec xp_cmdshell @restorestring

set @restorestring='sqlcmd -S atel-vm-test2 -E -Q"ALTER DATABASE ' + @nextdb + ' SET HADR AVAILABILITY GROUP = [atel-testag]"' exec xp_cmdshell @restorestring

FETCH NEXT FROM adddbs INTO @nextdb END

CLOSE adddbs DEALLOCATE adddbs

--end Substitute atel-testAG with your own Availability Group name. Substitute \atel-vm-test\backup with the shared backup location of your server. Substitute \atel-vm-test2 with the name of your secondary server. This job is scheduled to be run from the Primary server. (I don't have it set as yet to run from the variable "Primary Server", just for now it's running on my primary server called atel-vm-test.

Make sure you have the "xp_cmdshell" function permissions to run on your SQL server.

I have gotten this to run with zero errors, and it now is a scheduled SQL Job to run every hour. It works as expected. thanks for the assistance.

share|improve this answer
    
The above script will only take care of taking backup, restoring it to secondary and joining the AG group. If you see the server trigger that I have mentioned, it will fire when a new database is created and then you can run your script (which you have tested as working). This way, you don't have to schedule a job that will check for new databases. –  Kin Apr 19 '13 at 21:34
    
Thanks for the note, Kin. I tried to run your script, but kept getting the errors: Msg 1781, Level 16, State 1, Line 2 Column already has a DEFAULT bound to it. Msg 1750, Level 16, State 0, Line 2 Could not create constraint. See previous errors. –  AllenValk66 Apr 26 '13 at 18:06
    
try removing the default IsAlwaysOnMember bit default 0 to IsAlwaysOnMember bit in the create table. You dont need it. Let me know how it goes –  Kin Apr 26 '13 at 18:39
add comment

Below is a completed and now-working script that I have to run as a SQL Server Agent job every 20 minutes. It will: -Automatically determine the Primary and the Secondary server name. -Automatically determine the Availabilty Group name. -Convert the database(s) being added from Simple mode to Full mode. (necessary for any database to be successfully added to AlwaysOn). work on a SQL server with a single SQL instance or on the second SQL instance. If a 2nd SQL instance is being engaged with this script, you MUST share out the second name of the backup path.

If this script is being run manually in the SSMS console, you must first run the command "DROP TABLE dbo.#dbs" (if this script was previously run). For some reason, this command doesn't get read in the manual running of this script in SSMS, but it does work when run as a SQL Server Agent job. (????)

The \backups location is hard-coded in this script; change this as needed. You must also enable the command function "xp_cmdshell" in your SQL server SSMS console. Google that to find out; it's an easy command call. This is designed to be run on a 2-SQL server configuration, with one Primary and one Secondary.

xp_cmdshell 'del /F /Q /S \atel-web\be1\backups*.*'

exec xp_cmdshell 'del /F /Q /S \atel-web\be2\backups*.*'

USE master

DECLARE @secondaryservername nvarchar(50)

DECLARE @primaryservername nvarchar(50)

DECLARE @availabilitygroup nvarchar(50)

SET @secondaryservername = (select replica_server_name AS Servername from sys.dm_hadr_availability_replica_states
, sys.dm_hadr_availability_replica_cluster_states where role_desc = 'SECONDARY' AND sys.dm_hadr_availability_replica_states.replica_id = sys.dm_hadr_availability_replica_cluster_states.replica_id)

SET @primaryservername = (select replica_server_name AS Servername from sys.dm_hadr_availability_replica_states
, sys.dm_hadr_availability_replica_cluster_states where role_desc = 'PRIMARY' AND sys.dm_hadr_availability_replica_states.replica_id = sys.dm_hadr_availability_replica_cluster_states.replica_id)

SET @availabilitygroup = (SELECT name FROM [sys].[availability_groups])

IF OBJECT_ID('dbo.#dbs', 'U') IS NOT NULL DROP TABLE dbo.#dbs

IF (SELECT CURSOR_STATUS('global','adddbs')) >=0 BEGIN DEALLOCATE adddbs END

create table #dbs(a int primary key identity, dbname varchar(100)) declare @nextdb varchar(100) declare @restorestring varchar(400)

--Populate temp table insert into #dbs(dbname)

select name from sys.databases where group_database_id is null and replica_id is null and name not in('master','model','msdb','tempdb')

--Create a cursor to declare adddbs cursor for select dbname from #dbs

open adddbs

FETCH NEXT FROM adddbs INTO @nextdb

WHILE @@FETCH_STATUS = 0 BEGIN EXEC ('ALTER DATABASE' + '[' + @nextdb + ']' + 'set RECOVERY FULL') EXEC ('BACKUP DATABASE ' + '[' + @nextdb + ']' + ' TO DISK = ' + '''\' +@primaryservername+'\backups\' + '[' + @nextdb + ']' + 'initialize.bak''')

EXEC ('ALTER AVAILABILITY GROUP ['+ @availabilitygroup +'] ADD DATABASE ' + '[' + @nextdb + ']')

EXEC ('BACKUP DATABASE ' + '[' + @nextdb + ']' + ' TO DISK = ' + '''\'@primaryservername+'\backups\' + '[' + @nextdb + ']' + '.bak''')

EXEC ('BACKUP LOG ' + '[' + @nextdb + ']' + ' TO DISK = ' + '''\'@primaryservername+'\backups\' + '[' + @nextdb + ']' + '_log.bak''')

set @restorestring='sqlcmd -S ' +@secondaryservername+' -E -Q"RESTORE DATABASE ' + '[' + @nextdb + ']' + ' FROM DISK = ' + '''\' +@primaryservername +'\backups\' + '[' + @nextdb + ']' + '.bak''' + ' WITH NORECOVERY, NOUNLOAD, STATS = 5"' exec xp_cmdshell @restorestring

set @restorestring='sqlcmd -S ' +@secondaryservername+' -E -Q"RESTORE LOG ' + '[' + @nextdb + ']' + ' FROM DISK = ' + '''\' +@primaryservername+'\backups\' + '[' + @nextdb + ']' + '_log.bak''' + ' WITH NORECOVERY, NOUNLOAD, STATS = 5"' exec xp_cmdshell @restorestring

set @restorestring='sqlcmd -S ' +@secondaryservername+' -E -Q"ALTER DATABASE ' + '[' + @nextdb + ']' + ' SET HADR AVAILABILITY GROUP = [' + @availabilitygroup +']"' exec xp_cmdshell @restorestring

FETCH NEXT FROM adddbs INTO @nextdb END

CLOSE adddbs DEALLOCATE adddbs

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.