April 17, 2013 at 1:34 pm
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
April 26, 2013 at 3:23 pm
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
April 27, 2013 at 12:58 am
AllenValk66 (4/26/2013)
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