https://worldofeuc.com logo
Join Slack
Powered by
This message was deleted.
# _general
s
This message was deleted.
h
I will send it when I get back home
1st you need to enable Always On on the server... you can use powershell
Copy code
$SQLInstance = "<InstanceNameHere">
Enable-SqlAlwaysOn -ServerInstance "$($env:ComputerName)\$SQLInstance" -Confirm:$false -NoServiceRestart:$false -Force:$true
Copy code
CREATE AVAILABILITY GROUP group_name  
   WITH (<with_option_spec> [ ,...n ] )  
   FOR [ DATABASE database_name [ ,...n ] ]  
   REPLICA ON <add_replica_spec> [ ,...n ]  
   AVAILABILITY GROUP ON <add_availability_group_spec> [ ,...2 ]  
   [ LISTENER 'dns_name' ( <listener_option> ) ]  
[ ; ]
Copy code
--Create listener for AG
CREATE ENDPOINT Hadr_endpoint  
    STATE=STARTED   
    AS TCP (LISTENER_PORT=5022)   
    FOR DATABASE_MIRRORING (ROLE=ALL);  
GO  


--Add Replica to AVAILABILITY Group
ALTER AVAILABILITY GROUP MyAG ADD REPLICA ON 'COMPUTER04'   
   WITH (  
         ENDPOINT_URL = '<TCP://COMPUTER04.Adventure-Works.com:5022>',  
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,  
         FAILOVER_MODE = MANUAL  
         );
m
Maybe I had too many options, I was trying the full example they had but with BASIC as an option
h
Copy code
--Add new DB to AG run on primary
ALTER AVAILABILITY GROUP <SQLAG> ADD DATABASE <DB>

--Add Replica to availablity group DB run on secondaries after DB restored 
ALTER DATABASE <DB> SET HADR AVAILABILITY GROUP = <SQLAG>;
don't think you need the BASIC as an option when using T-SQL
m
Doesn't it set it as a full availability group then by default? I'm not using enterprise, I need basic
h
try just running
Copy code
CREATE AVAILABILITY GROUP group_name
m
I'll play around with it
I think I had too much in there
h
I have found that it is MUCH easier to just use the GUI than to try and use T-SQL for many things with AG's
but there are times where it is needed... especially the DB parts
these are the notes I use most frequently when dealing with AG and T-SQL
Copy code
--Add new DB to AG run on primary
ALTER AVAILABILITY GROUP <SQLAG> ADD DATABASE <DB>

--Add Replica to availablity group DB run on secondaries after DB restored 
ALTER DATABASE <DB> SET HADR AVAILABILITY GROUP = <SQLAG>; 

--Create listener for AG on Primary
CREATE ENDPOINT Hadr_endpoint  
    STATE=STARTED   
    AS TCP (LISTENER_PORT=5022)   
    FOR DATABASE_MIRRORING (ROLE=ALL);  
GO  


--Add Replica to AVAILABILITY Group
ALTER AVAILABILITY GROUP MyAG ADD REPLICA ON 'COMPUTER04'   
   WITH (  
         ENDPOINT_URL = '<TCP://COMPUTER04.Adventure-Works.com:5022>',  
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,  
         FAILOVER_MODE = MANUAL  
         );
m
It just takes so long going through the GUI, I want to speed this up
Especially as a need 3 basic groups
h
it doesn't speed it up since you have to run everything on every DB including the Backup/Restores if you are doing through T-SQL
m
Was trying to do it with powershell but it looks painful
h
powershell is very painful for it
and doesn't have a direct hook to create the AG
I spent weeks working on that.... and landed on those 4 lines are really all I need for T-SQL... everything else is powershell (enabling AG) or GUI (Creating AG)
m
It's such a pain in the ass that you have to preconfigure the DBs before you can use a BAG, it would be super useful if Citrix Studio could just do it all for you
h
after speaking with Citrix Dev, I have learned they don't have trained SQL dev teams.... they don't know SQL very well, which is why they keep messing it up
m
I have scripts to easily create the DBs and the failover cluster, just not the BAG
Yeah, it shows when you call them for support
h
the closest I have come if a full build of the SQL Server, creation of the DB's and then those 4 T-SQL commands.... hope you can get closer...
m
Not sure how much more time I want to spend on it but I need to do it in 2 more sites so would love to automate it
If I figure it out I'll share it
h
would probably be
Copy code
CREATE AVAILABILITY GROUP group_name with BASIC, CLUSTER_TYPE=WSFC
m
I'll give that a go
h
then run the Add replica on the secondary
Copy code
CREATE ENDPOINT Hadr_endpoint  
    STATE=STARTED   
    AS TCP (LISTENER_PORT=5022)   
    FOR DATABASE_MIRRORING (ROLE=ALL);  
GO  


--Add Replica to AVAILABILITY Group
ALTER AVAILABILITY GROUP MyAG ADD REPLICA ON 'COMPUTER04'   
   WITH (  
         ENDPOINT_URL = '<TCP://COMPUTER04.Adventure-Works.com:5022>',  
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,  
         FAILOVER_MODE = MANUAL  
         );
you have to create the endpoint on the secondary, but probably alter availability on the primary
good luck
m
Cheers
3 Views
Open in Slack
PreviousNext
Powered by