Is Your App Screaming 'Stop Azure SQL'? Follow | Yashaswi Jain

Azure SQL PaaS might not support all the features your legacy app relies on, such as cross-database references, linked servers, SQL Server Agent jobs, and CLR integration. This article highlights the challenges of migrating to Azure SQL PaaS and suggests that IaaS might be a safer bet for apps dependent on legacy features.

You Should Know:

1. Cross-Database References:

In traditional SQL Server, you can use three-part naming to reference objects across databases.
Example: `SELECT * FROM [DatabaseName].[SchemaName].[TableName]`
– Azure SQL PaaS does not support this feature. Instead, you may need to use Elastic Queries or refactor your database design.

2. Linked Servers:

Linked servers allow you to execute distributed queries across different SQL Server instances.
Example: `SELECT * FROM [LinkedServerName].[DatabaseName].[SchemaName].[TableName]`
– Azure SQL PaaS does not support linked servers. Consider using Azure SQL Database Elastic Query or Azure Data Factory for similar functionality.

3. SQL Server Agent:

SQL Server Agent is used for scheduling jobs and tasks.
Example: `EXEC msdb.dbo.sp_add_job @job_name = ‘MyJob’;`
– Azure SQL PaaS does not have SQL Server Agent. Instead, use Azure Automation or Logic Apps for job scheduling.

4. CLR Integration:

CLR integration allows you to run .NET code within SQL Server.
Example: `CREATE ASSEMBLY MyAssembly FROM ‘C:\MyAssembly.dll’;`
– Azure SQL PaaS does not support CLR integration. You may need to move this logic to an external application or service.

What Undercode Say:

Migrating to Azure SQL PaaS can offer many benefits, but it’s crucial to evaluate whether your application relies on features that are not supported. If your app depends on legacy features like cross-database queries, linked servers, SQL Server Agent, or CLR integration, consider using Azure SQL IaaS instead. This approach allows you to maintain the same functionality while still leveraging the cloud.

Additional Commands and Steps:

Check SQL Server Features:
```
SELECT * FROM sys.dm_db_persisted_sku_features;
```
This command helps you identify features that are not supported in Azure SQL PaaS.

Migrate to Azure SQL IaaS:

New-AzureRmVM -ResourceGroupName "MyResourceGroup" -Name "MyVM" -Location "East US" -Image "MicrosoftSQLServer:SQL2017:Enterprise:latest"

Use this PowerShell command to create a new Azure VM with SQL Server installed.

Set Up Elastic Queries:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword';
CREATE DATABASE SCOPED CREDENTIAL MyCredential WITH IDENTITY = 'MyUser', SECRET = 'MyPassword';
CREATE EXTERNAL DATA SOURCE MyDataSource WITH (TYPE = RDBMS, LOCATION = 'myserver.database.windows.net', DATABASE_NAME = 'MyDatabase', CREDENTIAL = MyCredential);

These commands set up an external data source for Elastic Queries in Azure SQL Database.

Azure Automation for Job Scheduling:

New-AzureRmAutomationAccount -ResourceGroupName "MyResourceGroup" -Name "MyAutomationAccount" -Location "East US"

Use this command to create an Azure Automation account for scheduling jobs.

By carefully evaluating your application’s needs and choosing the right Azure SQL deployment model, you can avoid potential pitfalls and ensure a smooth migration to the cloud.

Relevant URLs:

References:

Reported By: Jainyashaswi Azuresql – Hackers Feeds
Extra Hub: Undercode MoN
Basic Verification: Pass ✅

Join Our Cyber World:

💬 Whatsapp | 💬 Telegram

Listen to this Post