how to remove or alter a database in the primary instance?

[Ram4]

With Cloud SQL for SQL Server having HA enabled and a read replica, could you please provide guidance on how to remove or alter a database in the primary instance?

[ms4446]

You can alter or remove a database in the primary instance, even if you have High Availability (HA) and read replicas enabled.

1. Access the Google Cloud Console: Start by logging into your Google Cloud Console.

2. Navigate to the Cloud SQL Instances page: Here, you'll see a list of all your Cloud SQL instances.

3. Select the relevant instance: Click on the name of the instance where the database you want to alter or delete is located.

4. Go to the Databases tab: This tab lists all the databases in the selected instance.

5. Select the database: Click on the name of the database you wish to alter or delete.

6. Delete the database: If you want to delete the database, click the Delete button. Confirm your decision in the Delete database dialog box by clicking Delete again.

To alter a database;

1. Connect to the primary instance: You can connect to your Cloud SQL instance using various methods such as Cloud Shell, SQL Server Management Studio (SSMS), or any other SQL client. You'll need the IP address of your instance, the SQL Server port (usually 1433), and the necessary credentials.

2. Alter or remove the database: Once connected, you can use standard SQL commands to alter or remove a database.

• To rename a database: sp_renamedb @olddbname, @newdbname;
• To create a database: CREATE DATABASE @databasename;
• To delete a database: DROP DATABASE @databasename;

ALTER DATABASE your_database_name
SET NEW_OPTION = VALUE;

Please note the following:


• Only the primary instance
• allows for database alteration or deletion.
• Altering or deleting a database in use by an application may disrupt the application's functionality.
• Deleting a database with crucial data should only be done after backing up the data.
• If a database with a read replica is deleted, the read replica is also deleted.

[Ram4]

Thanks for your reply

 Database is in synchronous mode and not able to delete 

Below is the error message Kindly let me know how to resolve the issue

TITLE: Microsoft SQL Server Management Studio
------------------------------

Drop failed for Database 'dbname'. (Microsoft.SqlServer.Smo)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=15.0.18206.0+((SSMS_Rel).19102...

------------------------------
ADDITIONAL INFORMATION:

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

------------------------------

The database 'dbname' is currently joined to an availability group. Before you can drop the database, you need to remove it from the availability group. (Microsoft SQL Server, Error: 3752)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.00.4312&Evtsrc=MSSQLServ...

------------------------------
BUTTONS:

OK

[ms4446]

Thanks for the clarification. The error message you're seeing indicates that the database you're trying to delete is part of an availability group.

Before you can drop a database that is part of an availability group, you need to remove it from the availability group. Here's how you can do this:

1. Connect to the primary replica: Using SQL Server Management Studio (SSMS), connect to the primary replica of the availability group.

2. Remove the database from the availability group: Run the following T-SQL command:

ALTER AVAILABILITY GROUP [Your_Availability_Group_Name]
REMOVE DATABASE [Your_Database_Name];

     3. Replace Your_Availability_Group_Name with the name of your availability group and Your_Database_Name with the name of the database you want to remove.

    4. Drop the database: After successfully removing the database from the availability group, you should be able to drop the database using the DROP DATABASE command:

 DROP DATABASE [Your_Database_Name];

[tesh42]

You can delete the database on the primary instance using UI or gcloud tool or Cloud SQL API: 

https://cloud.google.com/sql/docs/sqlserver/create-manage-databases#delete