You don't have to write a cursor tsql script to check for new database created and schedule it to run for e.g. every minute. Instead use EVENTDATA() function in conjunction with server level trigger.
IF EXISTS (SELECT * FROM sys.server_triggers
WHERE name = 'ddl_trig_database')
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO
CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
PRINT 'Database Created.'
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO
Since you now have an automated mechanism in place that will fire up when a new database is created, you can use ALTER AVAILABILITY GROUP and ALTER DATABASE - SET HADR
basically you have to just include :
-- Move each database into the Availability Group
-- Change database name and Group as per your environment.
ALTER DATABASE Test1 SET HADR AVAILABILITY GROUP = TestAG
ALTER DATABASE Test2 SET HADR AVAILABILITY GROUP = TestAG
GO
Thinking of this a little bit more, you can be more creative to automate it --
-- create a driver table
create table AlwaysON_Candidates (
DatabaseName sysname
,createdate datetime default getdate()
,IsAlwaysOnMember bit default 0 -- 0 = Not a part of AG
) -- 1 = Is part of AG
go
-- below insert will be governed by the server level trigger
insert into AlwaysON_Candidates (DatabaseName)
values ('Test1')
--- check the values in the driver table
select *
from AlwaysON_Candidates
--- add database to AG
alter database Test1
set HADR AVAILABILITY group = TestAG
-- update the bit in the driver table AlwaysON_Candidates
update AlwaysON_Candidates
set IsAlwaysOnMember = 1
where DatabaseName = 'Test1'
some good references for setting it up using tsql can be found here and here
EDIT: Below script will help you. Obviously you have to Understand it and test it in a test environment.
/************************************************************************************
Author : Kin Shah
Version : 1.0.0 for dba.stackexchange.com
Note: This script does not have ERROR handling and is not tested.
Use at your own risk, It will print out the sql statements, but wont execute it
unless the print statements have been modified to use "exec"
UNDERSTAND the script and then test it on a TEST environment !!!!!!!!
*************************************************************************************/
-- create table
set ansi_nulls on
go
set quoted_identifier on
go
create table AlwaysON_Candidates (
ID int identity(1, 1)
,EventType nvarchar(128) null
,DatabaseName nvarchar(128) null
,LoginName nvarchar(128) null
,UserName nvarchar(128) null
,AuditDateTime datetime null
,IsAlwaysOnMember bit default 0
)
go
alter table [dbo].[AlwaysON_Candidates] add default((0))
for [IsAlwaysOnMember]
go
-- create server trigger
if exists (
select *
from master.sys.server_triggers
where parent_class_desc = 'SERVER'
and name = N'ddl_trig_database'
)
drop trigger [ddl_trig_database] on all server
go
set ansi_nulls on
go
set quoted_identifier on
go
create trigger [ddl_trig_database] on all server
for CREATE_DATABASE as
insert into NewDatabases
select EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)') as EventType
,EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)') as DatabaseName
,EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)') as LoginName
,EVENTDATA().value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(128)') as UserName
,GETDATE() as AuditDateTime
,0 as IsAlwaysOnMember
go
set ansi_nulls off
go
set quoted_identifier off
go
ENABLE trigger [ddl_trig_database] on all server
go
--- PREREQUISITE ... CREATE A LINKED SERVER FROM PRIMARY TO SECONDARY SERVER !!!
--- fill in *** CHANGE HERE values
--- test it on a TEST server
--- Not tested and not responsible for any dataloss. UNDERSTAND it and test it before implementing it.
declare @databasename varchar(max)
declare @sqlbackup varchar(max)
declare @sqlrestore varchar(max)
declare @PrimaryAG varchar(max)
declare @SecondaryAG varchar(max)
declare @backupPath varchar(max)
set @backupPath = '\\servername\sharedfolder\' --- *** CHANGE HERE
declare @group sysname
set @group = N'your_group_name' --- *** CHANGE HERE
declare @remotesql1 varchar(max)
declare @remotesql2 varchar(max)
declare @linkedserverName sysname
set @linkedserverName = 'kin_test_AG_LS' --- *** CHANGE HERE
select @databasename = min(DatabaseName)
from AlwaysON_Candidates
where IsAlwaysOnMember = 0
while @databasename is not null
begin
-- ** BACKUP HAPPENS HERE **
select @sqlbackup = N'BACKUP DATABASE ' + QUOTENAME(@databasename) + ' TO DISK = ''' + @backupPath + @databasename + '_forAG.BAK'' WITH COPY_ONLY, FORMAT, INIT, COMPRESSION;
BACKUP LOG ' + QUOTENAME(@databasename) + ' TO DISK = ''' + @backupPath + @databasename + '_forAG.TRN'' WITH INIT, COMPRESSION;'
from AlwaysON_Candidates
where IsAlwaysOnMember = 0
print @sqlbackup --- *** CHANGE HERE for EXEC master..sp_executesql @sqltext
-- ** RESTORE HAPPENS HERE **
select @sqlrestore = N'RESTORE DATABASE ' + QUOTENAME(@databasename) + ' FROM DISK = ''' + @backupPath + @databasename + '_forAG.BAK'' WITH REPLACE, NORECOVERY;
RESTORE LOG ''' + @backupPath + @databasename + '_forAG.TRN'' WITH NORECOVERY;'
print @sqlrestore
select @remotesql1 = N'EXEC ' + QUOTENAME(@linkedserverName) + '.master..sp_executesql @sqlrestore;'
print @remotesql1 --- *** CHANGE HERE for EXEC master..sp_executesql @sqltext
-- join the AG group on primary
select @PrimaryAG = N'ALTER AVAILABILITY GROUP ' + QUOTENAME(@group) + ' ADD DATABASE ' + QUOTENAME(@databasename) + ';'
print @PrimaryAG --- *** CHANGE HERE for EXEC master..sp_executesql @sqltext
-- join the AG group on secondary
select @SecondaryAG = 'ALTER DATABASE ' + QUOTENAME(@databasename) + ' SET HADR AVAILABILITY GROUP = ' + QUOTENAME(@group) + ' ;'
print @SecondaryAG
select @remotesql2 = N'EXEC ' + QUOTENAME(@linkedserverName) + '.master..sp_executesql @sqlrestore;'
print @remotesql2 --- *** CHANGE HERE for EXEC master..sp_executesql @SecondaryAG
-- finally update the table
update AlwaysON_Candidates
set IsAlwaysOnMember = 1
where DatabaseName = @databasename
-- go to another database if it is added newly
select @databasename = min(DatabaseName)
from AlwaysON_Candidates
where IsAlwaysOnMember = 0
and DatabaseName > @databasename
end
I can guess you have the AutoClose option for database set to True. This is the default behavior when you create a database with Express Editions.
The mentioned error can occur exactly in this case. Actually the complete error message 615 states: "Could not find database ID %d, name '%.*ls'. The database may be offline. Wait a few minutes and try again."
... So it points that database could be closed during dropping.
So, go to DB properties, switch it to False and try again dropping it or use below script before dropping
ALTER DATABASE [MyDB] SET AUTO_CLOSE OFF
GO
Many point out that is better to have AutoClose set to False. I found this article explaining a bit more about AutoClose: http://sqlmag.com/blog/worst-practice-allowing-autoclose-sql-server-databases
Small extension of the answer:
-- this works in standard SQL Server Editions, but NOT with Express Editions:
CREATE DATABASE [MyDB]
GO
DROP DATABASE [MyDB]
GO
-- this works in ALL SQL Server Editions
CREATE DATABASE [MyDB]
GO
ALTER DATABASE [MyDB] SET AUTO_CLOSE OFF
GO
DROP DATABASE [MyDB]
GO
Best Answer
I suspect that you are not actually creating a
SERVER ROLE
but in fact are creating aDATABASE ROLE
in the model database. I looked under server roles for a bit and could find nothing that would grant read access to a database (old or new). I can seeALTER ANY DATABASE
,CREATE ANY DATABASE
andVIEW ANY DATABASE
but nothing about read/write of the tables.If what I suspect is correct and you are creating a role in the
model
database then it would have exactly the effect you are discussing. You would see the role show up in all new databases but not the old ones. Unfortunately the only way around this is to create the role in each of the existing databases. Fortunately this is fairly easy.First thing would be to generate a script that creates the role either by using a scripting tool, creating it in the GUI and hitting the script button, or using the generate scripts option (right click on the database).
Once you have the script for the role you can either manually run it on each of your existing databases (probably ok if you only have <50 or so) or you can generate some dynamic SQL to run the script for you.