AlwaysOn: automatically add new databases to SQL 2012 AlwaysOn by script/T-SQL or PowerShell

  • 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

  • Here's the answer and AlwaysOn Script. It will (if scheduled) automatically add databases to AlwaysOn as per the settings in your SQL Server job schedule.

    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.

    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

  • AllenValk66 (4/26/2013)


    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.[/Quote]

    the temporary table should be disposed once your session has been closed, but just in case add this to your script just before the create table

    IF (OBJECT_ID('tempdb..#dbs')) IS NOT NULL

    BEGIN

    DROP TABLE #dbs

    END

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

    declare @nextdb varchar(100)

    declare @restorestring varchar(400)

    AllenValk66 (4/26/2013)


    It will run perfectly as a SQL Server Agent job, however. (not sure why that is!)

    Since at that point it will be running under a different account that is why it succeeds.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

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

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