Certainly! Let’s continue our comprehensive guide on using Azure CLI with SQL. In this section, we’ll delve into advanced topics such as managing SQL databases, configuring firewall rules, and automating tasks using scripts.
🔧 Managing SQL Databases with Azure CLI
1. Listing Existing Databases
To view all databases on a specific server:
az sql db list --server <server-name> --resource-group <resource-group-name> --output table
This command provides a tabular list of all databases, including their names, editions, and service objectives.
2. Updating Database Settings
To modify properties of an existing database, such as changing its service objective:
az sql db update --name <database-name> --server <server-name> --resource-group <resource-group-name> --service-objective <new-service-objective>
This command allows you to adjust performance levels and other settings to meet changing requirements.
3. Renaming a Database
To rename a database:
az sql db rename --name <old-database-name> --server <server-name> --resource-group <resource-group-name> --new-name <new-database-name>
Renaming can be useful during rebranding or restructuring efforts.
4. Deleting a Database
To remove a database:
az sql db delete --name <database-name> --server <server-name> --resource-group <resource-group-name> --yes
Use this command with caution, as it permanently deletes the database.
🔐 Configuring Firewall Rules
1. Creating a Firewall Rule
To allow a specific IP address range to access your SQL server:
az sql server firewall-rule create --resource-group <resource-group-name> --server <server-name> --name <rule-name> --start-ip-address <start-ip> --end-ip-address <end-ip>
This ensures that only trusted IP ranges can connect to your server.
2. Listing Firewall Rules
To view all firewall rules:
az sql server firewall-rule list --resource-group <resource-group-name> --server <server-name> --output table
This command helps in auditing and managing access controls.
3. Deleting a Firewall Rule
To remove a specific firewall rule:
az sql server firewall-rule delete --resource-group <resource-group-name> --server <server-name> --name <rule-name>
Deleting obsolete or unnecessary rules enhances security.
📜 Automating Tasks with Scripts
1. Creating a Database with a Script
You can automate the creation of a database using a script:
#!/bin/bash
location="East US"
resource_group="myResourceGroup"
server_name="myServer"
database_name="myDatabase"
admin_user="adminUser"
admin_password="adminPassword"
az group create --name $resource_group --location $location
az sql server create --name $server_name --resource-group $resource_group --location $location --admin-user $admin_user --admin-password $admin_password
az sql db create --resource-group $resource_group --server $server_name --name $database_name --service-objective S1
This script sets up a resource group, creates a SQL server, and provisions a database with a specified service objective.
2. Scheduling Backups
While Azure SQL Database provides automated backups, you can script additional backup tasks:
#!/bin/bash
server_name="myServer"
database_name="myDatabase"
backup_name="backup_$(date +%Y%m%d%H%M%S)"
az sql db export --resource-group $resource_group --server $server_name --name $database_name --storage-key $storage_key --storage-key-type StorageAccessKey --storage-uri $storage_uri/$backup_name.bacpac
This script exports the database to a BACPAC file stored in Azure Blob Storage.
🧪 Advanced Topics
1. Long-Term Retention (LTR) Backups
Azure SQL Database supports LTR backups for compliance and regulatory requirements. To manage LTR backups:
- List LTR backups:
az sql db ltr-backup list --resource-group <resource-group-name> --server <server-name> --database <database-name>
- Restore an LTR backup:
az sql db ltr-backup restore --resource-group <resource-group-name> --server <server-name> --database <database-name> --backup-name <backup-name>
These commands help in maintaining compliance with data retention policies.
2. Transparent Data Encryption (TDE)
TDE helps protect your data at rest. To manage TDE settings:
- Enable TDE:
az sql db tde set --resource-group <resource-group-name> --server <server-name> --database <database-name> --status Enabled
- Disable TDE:
az sql db tde set --resource-group <resource-group-name> --server <server-name> --database <database-name> --status Disabled
Managing TDE ensures that your data is encrypted and secure.
📚 Additional Resources
For more detailed information and advanced configurations, refer to the official Azure CLI documentation:
These resources provide comprehensive guides and examples to enhance your proficiency with Azure CLI and SQL.
If you have specific scenarios or advanced configurations you’d like to explore further, feel free to ask!