Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

AlwaysOn: automatically add new databases to SQL 2012 AlwaysOn by script/T-SQL or PowerShell Expand / Collapse
Author
Message
Posted Wednesday, April 17, 2013 1:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 1:33 PM
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
Post #1443475
Posted Friday, April 26, 2013 3:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 1:33 PM
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
Post #1447179
Posted Saturday, April 27, 2013 12:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:15 AM
Points: 6,763, Visits: 14,424
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"
Post #1447232
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse