The operation cannot be performed since the database ‘JBDB’ is in a replication relationship. (Microsoft SQL Server, Error: 40680)

-> I tried renaming an Azure SQL database JBDB and got below error,

TITLE: Microsoft SQL Server Management Studio
Unable to rename JBDB_old. (ObjectExplorer)

ADDITIONAL INFORMATION:
Rename failed for Database ‘JBDB’. (Microsoft.SqlServer.Smo)
For help, …..

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The operation cannot be performed since the database ‘JBDB’ is in a replication relationship. (Microsoft SQL Server, Error: 40680)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.2148&EvtSrc=MSSQLServer&EvtID=40680&LinkId=20476

-> Error explains that this database is in a replication relationship. Let’s check if it is part of failover group,

-> Database JBDB is part of failover group. Lets remove this database from failover group and try renaming it.

-> Once removed from failover group. Open Azure SQL Database and click on database JBDB. Open “Geo-Replication” and stop it for JBDB database.

-> Try renaming the database and it will complete this time.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Start and Stop Azure Virtual Machine

-> Requirement is to start and stop Azure Virtual Machine as per given schedule. I will be using “Tasks (preview)” within Azure Virtual machine to perform this. This feature utilizes Logic Apps behind the scene to accomplish the tasks.

Stop Virtual Machine using option “Tasks (preview)” – Option 1

-> From Azure portal, open the required virtual machine.

-> Select “Tasks (preview)” under “Automation” and click “Add”,

-> Select “Power off Virtual Machine” and click on “Next : Authentication”,

-> Click on Create and Sign in with the required credentials,

-> You will see below screen once signed in,

-> I have provided a schedule in such a way that the Virtual Machine will be stopped every day at 9:00 PM.

-> Click Create.

Stop Virtual Machine using option “Auto-shutdown” – Option 2

-> From Azure portal, open the required virtual machine.

-> Select “Auto-shutdown” under “Operations”. Click on Save once appropriate details are provided as in below screenshot,

-> You can use either Option 1 or 2 as defined above to automatically stop your Azure Virtual Machine.

Start Virtual Machine using option “Tasks (preview)”

-> From Azure portal, open the required virtual machine.

This image has an empty alt attribute; its file name is image-17-1024x250.png

-> Select “Tasks (preview)” under “Automation” and click “Add”,

-> Select “Start Virtual Machine” and click on “Next : Authentication”.

-> Connections are all connected already, you may have to create it if this is done for the first time,

-> I have created a schedule in such a way that the virtual machine will be started on every weekday from Monday to Friday at 8:45 AM.

-> Lets try opening “Jbswiki-Startup” in Logic Apps and check,

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Zero RPO or 0 RPO with Microsoft SQL Server

Environment

-> Environment has two (2) standalone database servers JBSAG1 and JBSAG2 with SQL server instances installed on it. Database Servers JBSAG1 and JBSAG2 are part of a failover cluster without shared storage and are configured as replicas on an Asynchronous Always On Availability group. Database Server JBSAG1 acts as primary replica and JBSAG2 as secondary replica.

-> Database Server JBSAG1 is on primary datacenter and JBSAG2 is in Secondary datacenter. Distance between database servers JBSAG1 and JBSAG2 is 1500 Kilometers.

-> Zero RPO or 0 RPO may be achieved using below procedures. But not sure if this is supported.

-> Transaction log backups (*.trn) and LDF file for each database on database server JBSAG1 is replicated to a storage that is available on a third datacenter using Synchronous storage replication. Synchronous storage replication will move the changes of LDF file to secondary storage first and upon confirmation from secondary, primary server JBSAG1 LDF will have that change hardened. This is outside of SQL Server and is taken care at the storage level. Distance between “Primary Datacenter” to “Tertiary Datacenter” is 5 kilometers.

-> Lets assume we have only one (1) database called JBFinance on Database server JBSAG1 and that is part of Availability group to database server JBSAG2. Storage replication will ensure database JBFinance database’s log backups and LDF files are replicated to the storage in “Tertiary Datacentre” using Storage replication.

Scenario 1

-> Consider primary replica JBSAG1 goes down and is expected to come online after 12 hours.

-> At this stage a failover to JBSAG2 will result in data loss. In order to avoid any data loss we can utilize the LDF file that are backed up on the Tertiary datacenter using below steps.

-> Storage replication between JBSAG1 to storage in “Tertiary Datacenter” should be stopped by storage team. Storage team will make this storage available. Platforms team will provision this as a drive onto a temporary database server JBSAG4 that already has the same SQL Server version as JBSAG1 on “Tertiary Datacenter”.

-> On temporary database server JBSAG4 we will create a dummy database called JBFinance.

-> Once the dummy database is created, stop the SQL Server on JBSAG4 and replace the dummy database JBFinance’s LDF file with the LDF file that is replicated from Primary database Server JBSAG1 to storage in Tertiary datacenter.

-> We will start the SQL Services on JBSAG4 and the temporary database JBFinance on SQL Server instance JBSAG4 will be in “recovery pending” state.

-> Execute below query on JBSAG4 to take a tail log backup,

Backup log JBFinance to disk = '\\JBSDC\Backup\JBFinance_taillog_13May2021.trn' with NO_TRUNCATE

-> Lets remove the database JBFinance from Availability group on JBSAG2 and leave it in restoring state,

-> Perform a restore on JBFinance database using the tail log backup.

RESTORE LOG [JBFinance] FROM  
DISK = N'\\JBSDC\Backup\JBFinance_taillog_13May2021.trn' 
WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

Scenario 2

-> Status of Availability group JBSwiki,

-> Consider Secondary Replica JBSAG2 goes down.

-> In the meantime, Primary replica receives write workload from application while Secondary Replica JBSAG2 is down.

-> Primary replica JBSAG1 goes down and is expected to be online after 12 hours.

-> Secondary replica JBSAG2 comes online now and below is the status,

-> At this stage a failover to JBSAG2 will result in data loss. In order to avoid any data loss we can utilize the LDF file that are backed up on the Tertiary datacenter using below steps.

-> Storage replication between JBSAG1 to storage in “Tertiary Datacenter” should be stopped by storage team. Storage team will make this storage available. Platforms team will provision this as a drive onto a temporary database server JBSAG4 that already has the same SQL Server version as JBSAG1 on “Tertiary Datacenter”.

-> On temporary database server JBSAG4 we will create a dummy database called JBFinance.

-> Once the dummy database is created, stop the SQL Server on JBSAG4 and replace the dummy database JBFinance’s LDF file with the LDF file that is replicated from Primary database Server JBSAG1 to storage in Tertiary datacenter.

-> We will start the SQL Services on JBSAG4 and the temporary database JBFinance on SQL Server instance JBSAG4 will be in “recovery pending” state.

-> Execute below query on JBSAG4 to take a tail log backup,

Backup log JBFinance to disk = '\\JBSDC\Backup\JBFinance_taillog_13May2021.trn' with NO_TRUNCATE

-> Lets remove the database JBFinance from Availability group on JBSAG2 and leave it in restoring state,

-> Perform a restore on JBFinance database using the tail log backup.

RESTORE LOG [JBFinance] FROM  
DISK = N'\\JBSDC\Backup\JBFinance_taillog_13May2021.trn' 
WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

-> We could see the changes that were made on primary replica JBSAG1 available on JBSAG2. We can use above approach to achieve 0 RPO.

-> Same approach can be used for Logshipping also. Please find the details below,

Environment

-> Environment hosts two (2) standalone database server JBSAG1 and JBSAG2 with SQL server instances installed on it. JBSAG1 and JBSAG2 utilizes Logshipping with JBSAG1 configured as primary. Log backups as part of Logshipping happens every 5 minutes.

-> Database Server JBSAG1 is on primary datacentre and JBSAG2 is in Secondary datacentre. Distance between database servers JBSAG1 and JBSAG2 is 1500 Kilometers.

-> Transaction log backups (*.trn) and LDF file for each database on database server JBSAG1 is replicated to a storage that is available on a third datacenter using Synchronous storage replication. Synchronous storage replication will move the changes of LDF file to secondary storage first and upon confirmation from secondary, primary server JBSAG1 LDF will have that change hardened. This is outside of SQL Server and is taken care at the storage level. Distance between “Primary Datacenter” to “Tertiary Datacenter” is 5 kilometers.

-> Lets assume we have only one (1) database called JBFinance on Database server JBSAG1 and that is part of logshipping to database server JBSAG2. Storage replication will ensure database JBFinance database’s log backups and LDF files are replicated to the storage in “Tertiary Datacentre” using Storage replication.

-> In an event where database server JBSAG1 goes down, storage replication between JBSAG1 to storage in “Tertiary Datacentre” should be stopped by storage team. Storage team will make this storage available. Platforms team will provision this drive onto a temporary database server JBSAG4 that already has the same SQL Server version as JBSAG1 on “Tertiary Datacente” as a separate drive.

-> On temporary database server JBSAG4 we will create a dummy database called JBFinance. Once the dummy database is created, stop the SQL Server on JBSAG4 and replace the dummy database JBFinance’s LDF file with the LDF file that is replicated from Primary database Server JBSAG1 to storage in Tertiary datacenter.

-> Start the SQL Services on JBSAG4 and the temporary database JBFinance on SQL Server instance JBSAG4 will be in “recovery pending” state.

-> Execute below query on JBSAG4 to take a tail log backup.

Backup log JBFinance to disk = '\\JBSDC\Backup\JBFinance_taillog_13May2021.trn' with NO_TRUNCATE

-> We will then check the last log backup that is restored on secondary database server JBSAG2. With that info we will move all required log backup and tail log backup from JBSAG4 to JBSAG2 and perform the restore. With this approach we will ensure that there will be no data loss.

Below are the PRO’s and CON’s for this approach,

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.