SQL Server Azure SQL Solutions

💡 Azure SQL Database 因為沒有 Instance，有替代 Instance 管理需求的專門角色 dbmanager 以及 loginmanager。

dbmanager 可以用於建立與刪除資料庫；loginmanager 可以用於建立與刪除 virtual master 的 logins

💡 Azure AD Server Principal default 權限包含 database backup & restore operations 以及 Set up service broker & DB mail。

💡 評估 Managed Instance 支援使用 Alert 的 metrics: IO Bytes read, Average % CPU utilizatoin over time & Storage space used by instances and databases。

💡 評估使用 DTU 的資源量，可以藉由:

• total storage space for all databases
• total number of databases × average DTU utilization per database
• number of concurrently peaking database × peak DTU utilization per database

💡 Azure SQL Database Replication 僅支援 Standard Transactional 以及 Snapshot。

💡 Invoke-AzSqlInstanceFailover with "Resource Group Name" & "Managed Instance for fail over"

💡 SQL Server Agetn Jobs 使用 SSMS 來進行管理，無法透過 Azure Portal, ARM Template。

💡 Set-AzVMSqlServerExtension for PowerShell cmdlet

💡 Automatic tuning CREATE_INDEX & DROP_INDEX 只有在 Azure SQL Database 支援，不支援 Azure SQL Managed Instance。

💡 可以藉由使用ㄒsys.database_files 來觀察資料庫 data & log files 的可用容量。

💡 Change Data Capture

1. sys.sp_cdc_enable_db
2. sys.sp_cdc_enable_table

💡 Azure Key Vault to store encryption keys with SQL Server PowerShell cmdlets

💡 Striped Virtual Disk

1. Attach disks to vm
2. Create storage pool
3. Striped virtual disk
4. Create a volume

💡 Database Mail

1. Enable database mail
2. Create mail account
3. Create profile
4. Set profile as default

💡 Failver Configuration

1. Disable application
2. Configuration to secondary
3. Initial failover from secondary
4. Configure database after recovery
5. Verify application integrity

💡 Alaways Encrypted

1. Create a column master key
2. Create a column encryption key
3. Encrypt column using randomized encryption

💡 Azure AD Authentication

1. Create an Azure AD Administrator for instance
2. Create a contained database user to mapped to Azure AD identity
3. Connect to database using Azure AD identity

💡 ARM Template

1. Export Resources template from Azure Portal
2. Revise configuration parameters in the template
3. Deploy the template from Azure Portal

💡 Restore Database

1. Restore the most recent full backup
2. Restore the diff backup from specific Date
3. Restore the log backups after diff backup

💡 Move Secondary Server to another region

1. Active-Geo replication to create secondaries of Server A to C
2. Delete Failover Group
3. Create new Failover Group, same name between A and C
4. Add all primary databases on Server A to the new Failover Group
5. Drop Server B

💡 Resource Governor

1. Create Resource Pool
2. Create Workload Group
3. Create User Defined classification function
4. Modify Resource Governor