Setting Up Azure SQL Database for Development Work
Azure SQL Database is Microsoft's managed relational database service built on the SQL Server engine. It runs in the cloud and handles patching, backups, and basic scaling for you. That means less time managing infrastructure and more time writing queries. The service has gone through several major revisions since it launched around 2010, and the current iteration is significantly different from what early adopters dealt with. Getting started involves a few concrete steps that are straightforward once you know the sequence. I'll walk through the process from creating the resource to getting a working connection string. First, log into the Azure portal at portal.azure.com and create a new SQL Database resource. Navigate to the left sidebar, click "Create a resource," search for "SQL Database," and select the Azure SQL Database option. You'll need to choose a resource group or create a new one. Give your database a name. Under compute + storage, pick the right pricing tier for development work — Basic or S0 will handle most learning scenarios without breaking the bank. For anything approaching production load, you'll want at least an S1 or the vCore-based model.
Next, configure the server settings. This includes choosing an admin username and password. Microsoft requires a strong password that meets their criteria — at least eight characters, uppercase, lowercase, and a number. Don't skip this. The portal won't let you proceed otherwise, and you'll waste time cycling through attempts. Set the firewall rules to allow your IP address, or use "Allow Azure services" if you're connecting from multiple locations. The latter is convenient during development but shouldn't stay enabled in production. Once the deployment finishes, navigate to your database and find the connection strings under Settings. You'll get a few options depending on your driver. Copy the ADO.NET string and keep it handy. You'll need it for your application code or for testing with SSMS. For actual development, I usually connect using SQL Server Management Studio version 19 or later. Connect to the server name you created — it looks like yourserver.database.windows.net — and authenticate with the SQL auth credentials you set up. SSMS works fine here, but there are some gotchas worth knowing about.
One thing that catches people off guard is that Azure SQL Database doesn't support every feature available in on-premises SQL Server. You can't create linked servers, you don't have access to xp_cmdshell, and SQL Agent jobs don't exist in the same way. If your application relies on any of those, you'll need to restructure it. I learned this the hard way when a migration project stalled for three weeks because someone had used CLR assemblies that simply aren't supported in the Azure environment. The workaround was rewriting those routines in managed code or moving the logic to an application layer function instead. Another edge case I ran into recently involves connection pooling and transient faults. Azure SQL Database enforces idle connection timeouts after about 30 minutes of inactivity. If your application uses a singleton connection pattern and goes quiet, the next query could fail with a timeout error. The fix isn't just reconnecting — it's implementing retry logic with exponential backoff. In practice, I wrapped the connection logic in a simple retry handler that attempts the operation three times with increasing delays between tries. This alone prevented roughly 80 percent of the connection-related errors we were seeing in our staging environment. When you're ready to deploy schema changes, the standard approach is to use either DACPACK files through Visual Studio or the newer Azure Data Studio for interactive development. DACPACK deployment is the most common path for teams already working in the .NET ecosystem. You create a project, define your tables and stored procedures in the model, and publish to the target database. The deployment tool compares the model against the live database and generates a migration script automatically.
If you're doing this manually, be aware that direct ALTER operations on production databases in Azure SQL can lock tables for longer than you'd expect on an on-premises system. Azure manages the underlying storage differently, and certain schema changes trigger background operations that hold locks across partitions. I've seen a simple ADD COLUMN operation on a heavily used table take over four minutes because of this. The safer approach is to use online schema migration tools or Microsoft's built-in online index operations where available. For authentication, I strongly recommend using Azure Active Directory authentication instead of SQL Server authentication for any serious project. It gives you centralized identity management, conditional access policies, and the ability to rotate credentials without touching the database. Set it up by going to your SQL server settings, clicking "Active Directory Admin," and designating an admin user. Then you can use AAD tokens in your application instead of storing passwords in config files. Performance monitoring in Azure SQL Database is handled through the portal's built-in metrics and Query Performance Insight. These tools are decent for basic troubleshooting but have limitations. Query Performance Insight, for example, only shows aggregated data from the last 24 hours by default and can miss intermittent slow queries that happen rarely. I ended up supplementing it with extended events in most cases, capturing specific deadlock or blocking events that the portal dashboard wouldn't surface.
Backup and restore work differently than you might expect from on-premises SQL Server. Point-in-time restore is automatic and built in — you can restore to any second within the retention window, which you configure at the server level. The default retention is seven days for standard tier, but you can extend it to 35 days or more. Geo-redundant backups replicate to a secondary region automatically if you enable that. However, manual backups using sqlcmd or SSMS don't work the same way. You'd use the export feature to create a BACPAC file instead, which is a full logical backup you can import into another database or download locally. There's a cost trap people fall into frequently. Auto-pause is a feature that stops charging for compute when a database is idle for a set period. It sounds great, but if your development environment occasionally spurious connects to check health or run maintenance, auto-pause won't trigger, and you'll pay for uptime you aren't using. I disabled auto-pause in my test environments and instead set a schedule to shut down the database during non-working hours using automation runbooks. This cut our monthly compute costs by about 60 percent on dev databases that sat idle 70 percent of the time. The pricing model also changed with the vCore-based deployment option. If you're evaluating costs, the DTU-based model is simpler to understand but less flexible. The vCore model lets you choose core count, memory ratio, and storage independently. For development workloads with variable patterns, vCore gives you better control but makes cost estimation slightly more complex. Use the Azure pricing calculator, but factor in egress costs if you're pulling data out of the database regularly — those add up faster than most people expect.
For source control integration, Visual Studio's database projects combined with Azure DevOps pipelines is the most established path. You can set up automated deployments that run tests against a staging database before promoting to production. The alternative is using migration frameworks like FluentMigrator or DbUp, which give you more control over versioned schema changes and can run against any SQL Server version. I prefer these for smaller teams because they live in code and deploy the same way every time, without needing specialized tooling in the CI/CD pipeline. One practical tip that isn't obvious: set your database compatibility level appropriately. Azure SQL Database defaults to 150, which enables many modern SQL Server features. But if you're migrating an older database, the compatibility level determines which query optimizer behavior you get. Setting it to 120 or 130 can sometimes improve performance for legacy queries that the newer optimizer handles poorly, though you should benchmark this rather than guessing. I had a stored procedure that ran in 200 milliseconds at compatibility 130 and degraded to over 12 seconds when upgraded to 150 without any code changes. Rolling the compatibility level back fixed it immediately. Ultimately, Azure SQL Database is a solid choice for most application backends, but it's not a drop-in replacement for on-premises SQL Server. The feature gaps are real, the cost model requires attention, and connection handling needs to be designed thoughtfully. Factor those into your planning from the start, and the experience is generally smooth. If your workload depends heavily on SQL Server-specific features like CLR, replication, or complex agent jobs, you might be better off running SQL Server on a VM in Azure or evaluating Azure Arc for SQL Server, which gives you more of the traditional feature set while still leveraging some cloud management capabilities.
