Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
AllenValk66
AllenValk66
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 9
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
AllenValk66
AllenValk66
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 9
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
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8784 Visits: 16558
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.

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" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search