Distributed AG setup with 4 Nodes

DAG SETUP:

Below links very useful to configure DAG setup.

https://www.mssqltips.com/sqlservertip/6987/distributed-availability-groups-for-sql-server-disaster-recovery/


Issues:

ALTER AVAILABILITY GROUP [AGNAME] GRANT CREATE ANY DATABASE


we should enable the permission before configuring the Always on setup. 


If you are facing any issue with secondary cluster for dropping the database need to remove the DAG setup.

Then remove the replica from secondary cluster.


  • Datafile and Log file folder should be same in all four nodes

Alter availibility group group_name REMOVE DATABASE availability_database_name

Comments

Post a Comment

Popular posts from this blog

SQL Server – How to re-initialize just a single article in transaction replication

  SQL Server – How to re-initialize just a single article in transaction replication -- Upskill 2020 .  1. Turn off @allow_anonymous and @immediate_sync on the publication. use PUBLICATION_DB_NAME go EXEC sp_changepublication @publication = 'Upskill2020_Test', @property = N'allow_anonymous', @value = 'false' GO   EXEC sp_changepublication @publication = 'Upskill2020_Test', @property = N'immediate_sync', @value = 'false' GO The reason we must disable @immediate_sync is because every time you add a new article, and if @immediate_sync is  enabled, it will cause the entire snapshot to be applied. Our objective is to only apply a particular article. 2. Add new article. EXEC sp_addarticle @publication = 'Upskill2020_Test', @article = 'Employee', @source_object = 'Employee', @force_invalidate_snapshot = 1 3. Refresh the subscription EXEC sp_refreshsubscriptions @publication = 'Repl_RBD' GO 4. Check the current sna...
Read more

SQL Deadlock Report - Extended Events

  SQL Deadlock Report - Extended Events Event Creation create EVENT SESSION EE_Deadlock ON SERVER     ADD EVENT sqlserver.xml_deadlock_report     ADD TARGET package0.ring_buffer     (SET max_memory = 4096) WITH (MAX_DISPATCH_LATENCY = 30 SECONDS); create Procedure  DeadlockReport as Begin alter event session EE_Deadlock ON SERVER state=Stop; if not exists (select * from Archival.sys.objects where name like '%Deadlock_Data%') Begin Create TABLE Archival..Deadlock_Data (     Rowid  INT IDENTITY PRIMARY KEY,     event_data XML) End Truncate Table Archival..Deadlock_Data insert into Archival..Deadlock_Data(event_data) ( select cast(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as xml) as DeadlockGraph FROM (select CAST(target_data as xml) as TargetData from sys.dm_xe_session_targets st join sys.dm_xe_sessions s on s.address = st.event_session_address where name = 'EE_Deadlock') AS Data CROSS APPLY TargetData.nodes...
Read more

DBA Daily Commands in Handy

Image
sp_who2 active:  View details of active processes on SQL Server Engine DBCC INPUTBUFFER(session id):  View the current query being executed by a specific proc ess Check the open transaction in SQL Server: DBCC OPENTRAN Check the blocked process: SELECT * FROM sysprocesses where blocked > 0 and spid = 23; select * from sys.sysprocesses where blocked != 0; Check the lock information: SELECT * from sys.dm_tran_lock Check the connection information from view in SQL Server: SELECT * FROM sys.dm_exec_sessions; Check the database files location, size, and max size in SQL Server SELECT * FROM sysaltfiles Check the collation Setting on the SQL Server SP_HELPSORT Check the log Usage on SQL Server databases select * from sys.databases where name like '%log_reuse_wait_desc%'
Read more