none
Need to script/loop adding new databases to SQL 2012 AlwayOn configuration automatically.

    Question

  • Greetings! I've installed and successfully configured our SQL 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 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, and the solution is "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

    • Moved by Kalman Toth Friday, April 26, 2013 10:22 AM Not T-SQL
    Wednesday, April 17, 2013 6:58 PM

All replies

  • create a trigger on "create database", then trigger

    ALTER AVAILABILITY GROUP MyAG ADD DATABASE MyDb3;
    GO
    

    havn't tried, read http://msdn.microsoft.com/en-us/library/hh213078.aspx

    Friday, April 19, 2013 1:40 AM
  • I've gotten the script figured out and it works as expected. 

    Some caveats to the script:

    • the \backups location is not set in a variable. It is hard-coded in the script. That share name/path must be modified for the individual set of servers this is applied to.
    • This is made for a 2-server configuration- a Primary and a Secondary.
    • Be sure to keep the varchar setting of (400) as such; if it is 200 or so, the script fails as the setting is not big enough.
    • If you run this script manually in SSMS, be sure to run the command “DROP TABLE dbo.#dbs” first, otherwise it won’t run manually in SSMS. It will run perfectly as a SQL Server Agent job, however. (not sure why that is!)
    • The first 2 lines are also “hard-coded” in, (xp_cmdshell 'del /F /Q /S \\atel-web\be1\backups\*.*')  so that the old SQL-server “Initialization” backup jobs get cleared out.
    • You need to have the \backup locations established as per the AlwaysOn installation/configuration instructions. That is, the "backups" location needs to be a share, and available to both Primary and Secondary Servers. (give them full NTFS access to this shared location).
    • If you have 2 SQL instances running on the same AlwaysOn servers, the backup path of the 2nd SQL instance must be different from the first MSSQLServer instance location. Also, when sharing out this location, it gets tricky. The SQL server name is a combination of the servername\instance name. Then the \backup location gets added to this. So, the answer here is to share out the "instancename" of the "servername\instance name" here. Then create a \backups folder, so the accessible path then becomes \\servername\instancename\backups    (hope this makes sense!)

    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

    Friday, April 26, 2013 9:18 PM