nerdexam
Microsoft

70-465 · Question #146

Drag and Drop Question The business requires a satellite office to have a local copy of the data to report against. You want to implement a solution to support the requirements. You need to…

The correct answer is EXEC master..dbo.sp_addlinkedserver @server = N'SATELLITE_SERVER', @srvproduct=N'SQL Server; CREATE ENDPOINT [Hadr_endpoint] STATE = STARTED AS TCP (LISTENER_PORT = 5022) FOR DATA_MIRRORING (ROLE = ALL); CREATE AVAILABILITY GROUP [ProdDB_AG] FOR DATABASE [ProdDB] REPLICA ON N'HQ_Server' WITH (ENDPOINT_URL = N'TCP:// HQ_Server.OurDomain.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)), N'Satellite_Server' WITH (ENDPOINT_URL = N'TCP:// Satellite_Server.OurDomain.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));; ALTER AVAILABILITY GROUP [ProdDB_AG] JOIN;; BACKUP LOG [ProdDB] TO DISK = N'\\FILESERVER\\SQLBackups \\ProdDB.trn' WITH NORECOVERY; RESTORE DATABASE [ProdDB] FROM DISK = N'\\FILESERVER\\SQLBackups\\ProdDB.bak'; ALTER DATABASE [ProdDB] SET HADR AVAILABILITY GROUP = [ProdDB_AG]. Setting up an Always On Availability Group requires a specific sequence of steps executed on both the primary (HQ) and secondary (Satellite) servers. First, a database mirroring endpoint must be created on each server to enable communication (CREATE ENDPOINT), then the…

Submitted by helene.fr· Mar 5, 2026Implement High Availability and Disaster Recovery Solutions - specifically configuring SQL Server Always On Availability Groups including endpoint creation, AG definition, and secondary replica joining procedures.

Question

Drag and Drop Question The business requires a satellite office to have a local copy of the data to report against. You want to implement a solution to support the requirements. You need to establish a new Availability Group between the two servers. Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks. Answer:

Exhibit

70-465 question #146 exhibit

Answer Area

Drag items

EXEC master..dbo.sp_addlinkedserver @server = N'SATELLITE_SERVER', @srvproduct=N'SQL ServerCREATE ENDPOINT [Hadr_endpoint] STATE = STARTED AS TCP (LISTENER_PORT = 5022) FOR DATA_MIRRORING (ROLE = ALL)ALTER AVAILABILITY GROUP [ProdDB_AG] JOIN;CREATE AVAILABILITY GROUP [ProdDB_AG] FOR DATABASE [ProdDB] REPLICA ON N'HQ_Server' WITH (ENDPOINT_URL = N'TCP:// HQ_Server.OurDomain.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)), N'Satellite_Server' WITH (ENDPOINT_URL = N'TCP:// Satellite_Server.OurDomain.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));CREATE ENDPOINT sql_endpoint STATE = STARTED AS TCP (LISTENER_PORT = 5022) FOR DATABASE_MIRRORING (ROLE = ALL) WITH (AUTHENTICATION = (CERTIFICATE [Mirroring]), ENCRYPTION = SUPPORTED ALGORITHM AES)CREATE AVAILABILITY GROUP [ProdDB_AG] FOR DATABASE [ProdDB] REPLICA ON N'HQ_Server' WITH (ENDPOINT_URL = N'TCP:// HQ_Server.OurDomain.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)), N'Satellite_Server' WITH (ENDPOINT_URL = N'TCP:// Satellite_Server.OurDomain.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));ALTER AVAILABILITY GROUP [ProdDB_AG] ADD LISTENER N'ProdDB_AG_Listener' WITH PORT=1433;ALTER DATABASE [ProdDB] SET HADR AVAILABILITY GROUP = [ProdDB_AG];RESTORE DATABASE [ProdDB] FROM DISK = N'\\FILESERVER\\SQLBackups\\ProdDB.bak'BACKUP LOG [ProdDB] TO DISK = N'\\FILESERVER\\SQLBackups \\ProdDB.trn' WITH NORECOVERY

Correct arrangement

  • EXEC master..dbo.sp_addlinkedserver @server = N'SATELLITE_SERVER', @srvproduct=N'SQL Server
  • CREATE ENDPOINT [Hadr_endpoint] STATE = STARTED AS TCP (LISTENER_PORT = 5022) FOR DATA_MIRRORING (ROLE = ALL)
  • CREATE AVAILABILITY GROUP [ProdDB_AG] FOR DATABASE [ProdDB] REPLICA ON N'HQ_Server' WITH (ENDPOINT_URL = N'TCP:// HQ_Server.OurDomain.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)), N'Satellite_Server' WITH (ENDPOINT_URL = N'TCP:// Satellite_Server.OurDomain.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
  • ALTER AVAILABILITY GROUP [ProdDB_AG] JOIN;
  • BACKUP LOG [ProdDB] TO DISK = N'\FILESERVER\SQLBackups \ProdDB.trn' WITH NORECOVERY
  • RESTORE DATABASE [ProdDB] FROM DISK = N'\FILESERVER\SQLBackups\ProdDB.bak'
  • ALTER DATABASE [ProdDB] SET HADR AVAILABILITY GROUP = [ProdDB_AG];

Explanation

Setting up an Always On Availability Group requires a specific sequence of steps executed on both the primary (HQ) and secondary (Satellite) servers. First, a database mirroring endpoint must be created on each server to enable communication (CREATE ENDPOINT), then the Availability Group is created on the primary with replica definitions (CREATE AVAILABILITY GROUP), and finally the secondary server must join the Availability Group (ALTER AVAILABILITY GROUP ... JOIN). This ordered sequence ensures the transport layer is established before the AG is defined, and the secondary can only join after the AG exists on the primary.

Topics

#Always On Availability Groups#High Availability#SQL Server Endpoints#Database Replication

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-465 Practice