31 reputation
16
bio website ucdenver.edu
location Denver, CO
age
visits member for 1 year, 7 months
seen Aug 30 '13 at 19:15

Sr. System Administrator for Academics-Technologies Extended Learning division for U of C.


Sep
24
awarded  Autobiographer
Aug
22
awarded  Notable Question
Nov
21
awarded  Popular Question
Apr
26
awarded  Editor
Apr
26
revised SQL Server 2012 AlwaysOn: need to automatically add databases by script- T-sql or PowerShell
I fixed the spacing and formatting of the script.
Apr
26
awarded  Commentator
Apr
26
comment SQL Server 2012 AlwaysOn: need to automatically add databases by script- T-sql or PowerShell
Thanks for the note, Kin. I tried to run your script, but kept getting the errors: Msg 1781, Level 16, State 1, Line 2 Column already has a DEFAULT bound to it. Msg 1750, Level 16, State 0, Line 2 Could not create constraint. See previous errors.
Apr
26
answered SQL Server 2012 AlwaysOn: need to automatically add databases by script- T-sql or PowerShell
Apr
22
awarded  Scholar
Apr
22
accepted SQL Server 2012 AlwaysOn: need to automatically add databases by script- T-sql or PowerShell
Apr
19
answered SQL Server 2012 AlwaysOn: need to automatically add databases by script- T-sql or PowerShell
Apr
18
comment SQL Server 2012 AlwaysOn: need to automatically add databases by script- T-sql or PowerShell
Upon changing the "Create" or "Alter" to "EXEC", so it now reads EXEC dbo.AddNewDBsToGroup. (I missed that bit of instruction earlier) Now the Messages result in: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'PROCEDURE'. Msg 137, Level 15, State 2, Line 32 Must declare the scalar variable "@group". Msg 137, Level 15, State 2, Line 40 Must declare the scalar variable "@group". Msg 137, Level 15, State 2, Line 43 Must declare the scalar variable "@debug". Msg 156, Level 15, State 1, Line 47 Incorrect syntax near the keyword 'ELSE'. Msg 137, Level 15, State 2, Line 58
Apr
18
comment SQL Server 2012 AlwaysOn: need to automatically add databases by script- T-sql or PowerShell
Thanks, Kin and Aaron; being a Server Admin for 20+ years, but with only a few months of SQL experience, I am sadly lacking in the needed & basic "variable and looping" T-SQL understanding. I do appreciate the efforts here, but without my being able to understand or translate these scripts to make them work, at this time I'll have to start reading up on basic T-SQL scripting or PowerShell lessons. The above script results in "Command(s) completed succesfully, but with nothing else occurring.
Apr
18
comment SQL Server 2012 AlwaysOn: need to automatically add databases by script- T-sql or PowerShell
Yes, I did the change from Alter to Create, and that got it running, at least it now results in "Command(s) completed successfully". However, there is no backup created, and the databases are not added to the AG. (availability group). The "@group" variable for the AG is correct, and the @path where the backup share is (with its correct permissions) is also true and correct. So, I have entered in both variables. Is there any other location that I need to enter in information or variables? My replicas are healthy, and all "green". Both servers have exact-same path locations, no illegal chactrs.
Apr
18
comment SQL Server 2012 AlwaysOn: need to automatically add databases by script- T-sql or PowerShell
To clarify: I already have the AG (availability group) created. SharePoint 2013 creates databases by the users on the fly; they need to be added to the AG at that time, or at some point in time, automatically. I'm not trying to create a distinct AG for every database. My explaination/statement of "I have not yet defined the AG that I want..." was referring to the script instructions; the script has no defined AG in it. I do have an AG, of course. I have 3 test AlwaysOn servers and one main production AlwaysOn server now running perfectly; I just want to automate the database add-in process.
Apr
18
comment SQL Server 2012 AlwaysOn: need to automatically add databases by script- T-sql or PowerShell
If I could ask, running this I get the error from the first line: Msg 208, Level 16, State 6, Procedure AddNewDBsToGroup, Line 1 Invalid object name 'dbo.AddNewDBsToGroup'. Any thoughts as to what I can do to get past that error? It doesn't seem to like the object name- I've renamed it and changed it to no avail. thoughts?
Apr
18
comment SQL Server 2012 AlwaysOn: need to automatically add databases by script- T-sql or PowerShell
Sorry, I didn't scroll down all the way. My bad. :-(
Apr
18
comment SQL Server 2012 AlwaysOn: need to automatically add databases by script- T-sql or PowerShell
Kin: you wrote: the OP will get an idea on how it can be done. Who is the OP? (not sure what OP is) Not being a T-SQL/scripting master, I'm not sure how to take what you've written and expand upon it. Thoughts?
Apr
18
comment SQL Server 2012 AlwaysOn: need to automatically add databases by script- T-sql or PowerShell
The above command to "ALTER DATABASE Test1 SET HADR AVAILABILITY GROUP = TestAG" will not (and does not) work until the database and its log file is backed up and restored to the Secondary server. I've tried this command on a stand-alone database, and while it is added into the AlwaysON configuration, it is in a serious error state. This is because it's not been fully copied and restored to the Secondary, first. This process must be done for each database, in order to have a successful inclusion into AlwaysOn.
Apr
18
comment SQL Server 2012 AlwaysOn: need to automatically add databases by script- T-sql or PowerShell
Aaron: I not yet defined the Availabilty Group that I want to add the databases to; I don't know how to put that into the script. I'm not that good with T-SQL scripts, When a failover occurs, I'd like this script to of course, still work so the databases are added to the Active replica in AlwaysOn. This is what makes this so complex; it needs to work in the event of a failover. But yet, this is so basic and essential; when a database is added, manual intervention should not be needed to insure the databases are added to AlwaysOn.