By the end of this build sheet you have a running Azure SQL Database holding a real line-of-business database migrated off a SQL Server 2014 instance: reachable only over a private endpoint, authenticating Entra identities exclusively with no SQL administrator account in existence, with its logins re-founded rather than copied, its overnight reconciliation job rebuilt as an elastic job, and a validation pass that proves all four of those rather than assuming them. That is the database side of the work, end to end; the application-side companion, giving the App Service its managed identity and integrating it with the virtual network, is narrated in the worked example rather than built here command by command.
Prerequisites: an Azure subscription with Contributor on the target resource group and the ability to create a private DNS zone; an Entra group to hold the database administrators, created in advance; a virtual network with a subnet for private endpoints and working DNS resolution from wherever the application runs; the source SQL Server 2014 instance reachable from an operator workstation with sysadmin or equivalent on it; SqlPackage installed on that workstation; the Azure CLI signed in; and a confirmed answer to the question this whole series turns on, namely that the database has no Agent jobs that cannot be rebuilt, no cross-database queries, no linked servers, no CLR assemblies and no Windows logins that cannot become Entra identities. If any of those exist, stop and read the Managed Instance article instead; this is the wrong destination and no amount of build sheet will fix that.
Naming convention
Decide this before you create anything, because the logical server name becomes a public DNS label you cannot change afterwards. The pattern used throughout: sql-<org>-<env>-<region> for the logical server, sqldb-<workload>-<env> for the database, pe-<server> for the private endpoint, eja-<org>-<env> for the elastic job agent, and sqldb-jobs-<env> for the job database. The worked example runs as sql-vcio-prod-eus, sqldb-crm-prod, pe-sql-vcio-prod-eus, eja-vcio-prod and sqldb-jobs-prod. Keep the environment token in every name; the first time somebody runs a job against the wrong tier you will be glad it was visible in the resource name.
Step 1: Choose the tier deliberately
Make this decision on paper first. The defaults have changed and the tier drives cost, availability and the shape of the licensing conversation.
| Setting | Value | Why |
|---|---|---|
| Purchasing model | vCore | The DTU model has no hybrid benefit, cannot be reserved, and is positioned as legacy. There is no reason to start there in 2026. |
| Service tier | Hyperscale for anything expected to grow or matter; General Purpose for a small fixed workload where existing Enterprise licenses need somewhere to land | Microsoft’s current comparison names Hyperscale the recommended and default tier for new and modernizing workloads. The counter-argument is licensing: hybrid benefit is not available for new Hyperscale databases at all, so an estate rich in Enterprise cores with Software Assurance gets no credit there. General Purpose accepts the benefit at four vCores per Enterprise core. |
| Hardware | standard-series (Gen5) | The general-purpose default. Do not select Fsv2-series: it can no longer be created and retires 1 October 2026. |
| Compute | Provisioned, 2 vCores to start | Scale up after you have a Query Store baseline, not before. Choose serverless instead only if the workload is genuinely intermittent, remembering it excludes hybrid benefit and reservations. |
| High availability replicas | 1 or more on Hyperscale | Zone redundancy on Hyperscale requires at least one HA replica, and Hyperscale zone redundancy can only be chosen at creation time. Getting this wrong means recreating the database. |
| Zone redundancy | Enabled for production | Raises the availability commitment. Budget for it honestly: on General Purpose it adds roughly 60 percent to compute and doubles the storage rate. On Business Critical on this service it adds no compute cost. |
| Backup storage redundancy | Zone or Geo, chosen now | Geo-restore is available only on geo-redundant or geo-zone-redundant backup storage, so choosing Local or Zone switches it off entirely. On Hyperscale this is a creation-time decision and cannot be changed later. |
| Authentication | Microsoft Entra-only | Set at creation so no SQL administrator login ever exists. Retrofitting this means disabling an account somebody has already put in a connection string. |
Step 2: Create the logical server with no SQL admin
In the portal this is the Azure SQL blade, Create, SQL database, and on the Basics tab the Server field’s Create new link. Set Authentication method to Microsoft Entra-only authentication and choose your pre-created Entra group as the Entra admin. There is no password to invent, which is the point.
The consolidated equivalent, which is what I would actually run:
ADMIN_GROUP_OID=$(az ad group show --group "SG-SQL-Admins-Prod" --query id -o tsv)
az sql server create \
--name sql-vcio-prod-eus \
--resource-group rg-vcio-data-prod \
--location eastus \
--enable-ad-only-auth \
--external-admin-principal-type Group \
--external-admin-name "SG-SQL-Admins-Prod" \
--external-admin-sid "$ADMIN_GROUP_OID"Expected result: the command returns the server object with "administratorLogin": null. That null is the thing to check, and it is the proof that no SQL authentication account exists. Failure mode: omitting --enable-ad-only-auth silently creates a server that still accepts SQL logins, and because everything else works nobody notices until an audit does. If you inherit a server in that state, the fix is az sql server ad-only-auth enable, and it will fail while any SQL login is still in use, which is a useful forcing function.
Step 3: Create the database
az sql db create \
--name sqldb-crm-prod \
--server sql-vcio-prod-eus \
--resource-group rg-vcio-data-prod \
--edition Hyperscale \
--family Gen5 \
--capacity 2 \
--ha-replicas 1 \
--zone-redundant true \
--backup-storage-redundancy ZoneExpected result: a database in state Online within a couple of minutes. Failure mode worth knowing: --zone-redundant and --backup-storage-redundancy are both creation-time choices on Hyperscale, and a later attempt to change either returns an error rather than performing a migration. If this is production, get them right here or plan to recreate. Note what --backup-storage-redundancy Zone costs you: geo-restore requires geo-redundant or geo-zone-redundant backup storage, so this database has no geo-restore path at all.
Step 4: Take the database off the internet
A private endpoint does not by itself stop the public endpoint answering. These are two separate controls and you need both. In the portal they live on the server’s Networking page: Private access creates the endpoint, and Public network access set to Disable closes the front door.
SQL_ID=$(az sql server show -g rg-vcio-data-prod -n sql-vcio-prod-eus --query id -o tsv)
az network private-endpoint create \
--name pe-sql-vcio-prod-eus \
--resource-group rg-vcio-data-prod \
--vnet-name vnet-vcio-prod-eus \
--subnet snet-privateendpoints \
--private-connection-resource-id "$SQL_ID" \
--group-id sqlServer \
--connection-name pec-sql-vcio-prod-eus
az network private-dns zone create \
-g rg-vcio-data-prod -n privatelink.database.windows.net
az network private-dns link vnet create \
--resource-group rg-vcio-data-prod \
--zone-name privatelink.database.windows.net \
--name link-vnet-vcio-prod-eus \
--virtual-network vnet-vcio-prod-eus \
--registration-enabled false
az network private-endpoint dns-zone-group create \
--resource-group rg-vcio-data-prod \
--endpoint-name pe-sql-vcio-prod-eus \
--name default \
--private-dns-zone privatelink.database.windows.net \
--zone-name sql
az sql server update \
-g rg-vcio-data-prod -n sql-vcio-prod-eus \
--enable-public-network falseThose last two commands are the part that gets left out. The private endpoint created above does no DNS integration of its own: linking the zone to the virtual network is what makes the zone resolvable in there, and the zone group is what writes and then maintains the endpoint’s A record. Expected result: from inside the virtual network, nslookup sql-vcio-prod-eus.database.windows.net resolves to a private address in your endpoint subnet.
Two failure modes here, and both are common enough to check for deliberately. First, applications must keep connecting to the ordinary server name. The privatelink name exists for DNS plumbing and is not a connection string; using it directly produces certificate errors that look like a networking fault. Second, if the zone is not linked to the virtual network the name still resolves, to the public address, and everything keeps working over the internet while the design document says otherwise. Verify by the resolved address, never by the fact that the application connected.
If anything on-premises needs to reach this database, know that a VNet-linked private zone is not resolvable from on-premises. Your own DNS servers cannot see it. You need a resolution path into the virtual network, either an Azure DNS Private Resolver inbound endpoint or a forwarder virtual machine sitting in the network, and then a conditional forwarder on the on-premises DNS servers pointing at that. Target the conditional forwarder at the public database.windows.net zone, not at privatelink.database.windows.net. The public name is the one the application asks for; the privatelink zone is the plumbing that answers it once the query is inside Azure, and forwarding the privatelink zone directly is a common way to build something that resolves nothing.
Step 5: Export the source database
There is no native restore into this service, in either direction, so the migration is an export and an import. For a database of this size SqlPackage is the tool; the Database Migration Service is the alternative for larger work and, to this target specifically, it runs offline only. Either way the source is quiet during the move, so agree the window first.
Quiesce the application, then take the export from the source instance:
SqlPackage /Action:Export `
/SourceServerName:"SQL2014-CRM01" `
/SourceDatabaseName:"CRM" `
/SourceTrustServerCertificate:True `
/TargetFile:"D:\migration\CRM-20260721.bacpac" `
/p:VerifyExtraction=TrueExpected result: a bacpac file and a clean extraction report. Failure modes worth budgeting for: export time scales with object count rather than data volume, so a database with tens of thousands of objects takes far longer than its size suggests, and an export will fail outright on objects the target does not support. That failure is not an obstacle, it is the assessment doing its job at the last possible moment. If it fires here, you skipped the dependency check in the prerequisites.
One thing about the artefact itself: a bacpac is compressed, not encrypted. Anyone who picks the file up has the database. Write it somewhere with restricted permissions, encrypt it if it is going to travel or sit still for long, and delete it once the migration is signed off rather than leaving a copy of production in a migration folder for the next three years.
Step 6: Import into the target
SqlPackage /Action:Import `
/SourceFile:"D:\migration\CRM-20260721.bacpac" `
/TargetServerName:"sql-vcio-prod-eus.database.windows.net" `
/TargetDatabaseName:"sqldb-crm-prod" `
/ua:TrueThe /ua:True switch drives interactive Entra authentication, which is what you want on a server with no SQL logins. Note what is absent: there is no /TargetTrustServerCertificate:True here, because a database.windows.net endpoint presents a certificate from a public authority the machine already trusts, and setting the switch would skip chain validation for nothing. The equivalent switch on the export in step 5 is there because the 2014 box presents a self-signed certificate, which is a different and legitimate case. Expected result: the import completes and the database contains the source objects. Failure mode: run this from outside the virtual network with public access disabled and it fails to connect, which is correct behaviour and confuses people who did steps 4 and 6 on different machines. Run the import from a host inside the network, or from a jump box with the private DNS zone in scope.
Then check what compatibility level you actually landed on, because this is the lever the whole migration doctrine depends on:
SELECT name, compatibility_level
FROM sys.databases
WHERE name = 'sqldb-crm-prod';Whatever it reports, leave it alone for now. New databases on this service default to level 170, and the supported range runs from 100 to 170. Be aware that Microsoft’s own feature comparison page still prints the old ceiling of 160 and has done for months; the language reference for ALTER DATABASE is the page that is current. Take your Query Store baseline at the level you arrived at, run the workload for a representative period, and raise the level as a separate scheduled change afterwards.
Step 7: Re-found the logins on Entra
The bacpac carried the users. It did not carry the logins, and on this service the Windows logins from the old instance cannot exist at all. This step is where the actual work of the migration lives.
One prerequisite before any of it resolves. The logical server needs a managed identity, system-assigned or user-assigned, and that identity needs to be able to read the directory: the Microsoft Graph application permissions User.Read.All, GroupMember.Read.All and Application.Read.All, or the broader Directory Readers role. Only a Privileged Role Administrator or higher can grant them, so ask early. It is the item most likely to be waiting on somebody else on the day.
Connect to the target database as the Entra administrator group and create contained users directly from the directory:
-- An Entra group replacing a Windows group
CREATE USER [SG-CRM-ReadWrite] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [SG-CRM-ReadWrite];
ALTER ROLE db_datawriter ADD MEMBER [SG-CRM-ReadWrite];
-- The application, as a managed identity rather than a secret
CREATE USER [mi-crm-app-prod] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [mi-crm-app-prod];
ALTER ROLE db_datawriter ADD MEMBER [mi-crm-app-prod];
GRANT EXECUTE ON SCHEMA::dbo TO [mi-crm-app-prod];
-- Then find what the bacpac left behind
SELECT name, type_desc, authentication_type_desc
FROM sys.database_principals
WHERE type NOT IN ('R') AND name NOT LIKE '##%';Expected result: the new principals show an authentication type of EXTERNAL, and any user carried in from the source shows as INSTANCE or SQL with no matching login. Those orphans are dead weight and should be dropped once their replacement is in place. Failure mode: CREATE USER ... FROM EXTERNAL PROVIDER fails if the directory name is ambiguous or if whoever is running it cannot read the directory, and the error does not say which. Be clear about who that is, because there are two paths and they fail for different reasons. When a human Entra administrator runs the statement, which is what is happening here, SQL queries the directory using that administrator’s own delegated permissions, so the usual culprit is a Conditional Access policy blocking access to the Graph API rather than anything on the server. The server identity and its Graph permissions are what the other path depends on, where a service principal or a managed identity creates the user. Both need to be in place; just do not spend an afternoon on the server identity when the failure is sitting in a Conditional Access policy.
Move the application to a managed identity while you are here. You are already changing the connection string, the secret you would otherwise create is the thing you will be asked about in a year, and this is the cheapest moment in the project to do it.
Step 8: Rebuild the overnight job as an elastic job
There is no SQL Server Agent here. Scheduled work runs as elastic jobs, generally available since April 2024, and it is a different product with its own database rather than a feature you switch on.
Create a dedicated Azure SQL database to act as the job database, then an elastic job agent bound to it. Two constraints are absolute rather than advisory: a serverless database with auto-pause enabled is not supported as a job database, and neither is Hyperscale. Microsoft’s recommended service objective for the job database is DTU S1 or higher. A paused job database does not run jobs, which is exactly the kind of failure that is invisible until month end.
Before the agent exists, create the identity it will authenticate as. The job agent connects to its targets as a user-assigned managed identity, and a system-assigned identity is not supported. Database-scoped credentials remain a supported alternative if you would rather not introduce a managed identity at all, but the identity is the recommended path and the one this sheet takes.
# The identity the agent will run as.
az identity create \
-g rg-vcio-data-prod -n id-eja-vcio-prod
# Job database: the documented recommendation is DTU S1 or higher.
# Hyperscale is not supported as a job database, and neither is a
# serverless database with auto-pause enabled.
az sql db create \
-g rg-vcio-data-prod -s sql-vcio-prod-eus \
-n sqldb-jobs-prod --service-objective S1Then the agent itself, with that identity attached. There is no az sql elastic-jobs command group; the documented paths are the Azure portal, PowerShell and the REST API.
$umi = Get-AzUserAssignedIdentity `
-ResourceGroupName "rg-vcio-data-prod" `
-Name "id-eja-vcio-prod"
New-AzSqlElasticJobAgent `
-ResourceGroupName "rg-vcio-data-prod" `
-ServerName "sql-vcio-prod-eus" `
-DatabaseName "sqldb-jobs-prod" `
-Name "eja-vcio-prod" `
-IdentityType "UserAssigned" `
-UserAssignedIdentityId $umi.IdThat identity means nothing to the target database until the target database has heard of it. Connected to sqldb-crm-prod as the Entra administrator group, create it as a contained user and grant it exactly what the job runs and nothing more:
CREATE USER [id-eja-vcio-prod] FROM EXTERNAL PROVIDER;
GRANT EXECUTE ON dbo.usp_NightlyReconcile TO [id-eja-vcio-prod];Now the part step 4 made necessary, and the one most likely to be missing from a build that otherwise looks finished. The server has no public endpoint and the job agent does not live in your virtual network, so as things stand it cannot reach the database at all. Elastic jobs solve this with their own service-managed private endpoint, created on the agent and then approved on the target server. In the portal: on the elastic job agent, under Security, Private endpoints, Add a server and create private endpoint. In PowerShell:
$serverId = (Get-AzSqlServer `
-ResourceGroupName "rg-vcio-data-prod" `
-ServerName "sql-vcio-prod-eus").ResourceId
New-AzSqlElasticJobPrivateEndpoint `
-ResourceGroupName "rg-vcio-data-prod" `
-ServerName "sql-vcio-prod-eus" `
-AgentName "eja-vcio-prod" `
-Name "pe-eja-to-sql-vcio-prod" `
-TargetServerAzureResourceId $serverIdThe request lands in Pending and sits there until somebody approves it. Approve it on the target server: the SQL server blade, Security, Networking, Private access, then approve the pending request. Expected result: the connection status on the agent’s Private endpoints page reads Approved. Until it does, every execution fails. Note that public network access does not need to be turned back on for any of this; that is the whole point of the mechanism.
Then, connected to the job database, define the target and the job itself:
EXEC jobs.sp_add_target_group 'CRM-Prod';
EXEC jobs.sp_add_target_group_member
@target_group_name = 'CRM-Prod',
@target_type = 'SqlDatabase',
@server_name = 'sql-vcio-prod-eus.database.windows.net',
@database_name = 'sqldb-crm-prod';
-- Every time in elastic jobs is UTC. The old Agent job ran at 02:00
-- Pacific, which is 09:00:00 here, not 02:00:00. Set a start time in
-- the FUTURE: a start time in the past fires the job immediately.
EXEC jobs.sp_add_job
@job_name = 'CRM-Nightly-Reconcile',
@description = 'Replaces Agent job of the same name from SQL2014-CRM01',
@enabled = 1,
@schedule_interval_type = 'Days',
@schedule_interval_count = 1,
@schedule_start_time = '<first-run-UTC>';
-- Omit @credential_name entirely when the agent uses Entra ID
-- with a user-assigned managed identity.
EXEC jobs.sp_add_jobstep
@job_name = 'CRM-Nightly-Reconcile',
@command = N'EXEC dbo.usp_NightlyReconcile;',
@target_group_name = 'CRM-Prod';Do not wait until tomorrow morning to learn whether any of that worked. Start it by hand and read the outcome:
EXEC jobs.sp_start_job 'CRM-Nightly-Reconcile';
SELECT job_name, lifecycle, start_time, end_time, last_message
FROM jobs.job_executions
ORDER BY start_time DESC;Expected result: a row in jobs.jobs, and an execution in jobs.job_executions that reaches lifecycle Succeeded. Failure mode to watch, and it has four causes that all present identically. The agent must use a user-assigned managed identity, because a system-assigned one is not supported. That identity must exist as a database user in every target database. It must hold explicitly what the job procedure needs and nothing broader. And its private endpoint to the target server must be approved, not merely created. Miss any one of them and the execution fails with a message that reads like a connectivity fault, so verify the identity and the endpoint approval before you go looking at the network.
Worked example: the nightly reconciliation off a 2014 box
The concrete case this sheet was written against. A forty gigabyte CRM database on a SQL Server 2014 instance, compatibility level 110, one Agent job running a stored procedure at two in the morning, three Windows groups holding permissions, and an application authenticating with a SQL login whose password is in a configuration file two people know about.
The choices made: Hyperscale at 2 vCores with one HA replica and zone redundancy. General Purpose was the genuine alternative and it was rejected on the numbers rather than by default. At forty gigabytes it would have been the right size, and it is the tier that still accepts Azure Hybrid Benefit, which new Hyperscale databases forfeit outright. But the benefit only pays if there are Enterprise licenses with Software Assurance to bring to it, and this client holds none, so the one real advantage General Purpose had here was worth nothing to them. Set that aside and the growth forecast and the production zone-redundancy requirement both point the other way. Absent the growth expectation, General Purpose would have been the answer, and on an estate with Software Assurance it would have been the answer anyway. Entra-only authentication from creation. Private endpoint with public access disabled. Zone-redundant backup storage, which accepts the loss of geo-restore knowingly: recovering this database into another region would mean active geo-replication, a failover group, or a database copy, and none of those were warranted for a forty gigabyte CRM database whose recovery objective is measured in hours. Export and import via SqlPackage inside a three-hour Saturday window, measured at fifty minutes in rehearsal and budgeted at three hours because the object count was high. The three Windows groups became three Entra groups created ahead of time and mapped with CREATE USER ... FROM EXTERNAL PROVIDER; the SQL login became a user-assigned managed identity on the application’s App Service and the configuration file lost its password entirely. The Agent job became one elastic job with a single step calling the same procedure, pointed at a target group holding exactly one database, which looks like overkill for one database and is the structure that lets the second and third databases join without a redesign.
What actually consumed the time was none of the above. It was discovering during rehearsal that the stored procedure referenced a second database by three-part name for a lookup table nobody had mentioned. That is the dependency check from the prerequisites, arriving late. The fix was to copy the lookup table into the migrated database as a scheduled refresh, which was acceptable here and would not have been if it were transactional. Budget a rehearsal specifically to find that class of surprise.
Step 9: Validate end to end
Before any of this, and strictly before the export rather than after it, run DBCC CHECKDB on the source. A bacpac taken from a corrupt database is a corrupt database that now lives in two places, and you want to know that while the source is still the system of record.
Then six checks, and all six have to pass before anyone points production at this. The first four prove the platform is what you think it is; the last two prove the data actually arrived, which is the question nobody asks until a month end goes wrong.
| Check | How | Expected result |
|---|---|---|
| The public endpoint is genuinely closed | From a host outside the virtual network, attempt a connection to sql-vcio-prod-eus.database.windows.net | The connection is refused. If it succeeds, public network access is still enabled and step 4 did not take. |
| Private resolution is real | nslookup sql-vcio-prod-eus.database.windows.net from the application host | A private address in the endpoint subnet. A public address means the DNS zone is not linked to the virtual network. |
| Entra-only is enforced | Attempt a connection with any SQL username and password | Login fails. Combined with administratorLogin being null, this proves no password path into the database exists. |
| The application works as itself | Run the application’s own health check against the new database using its managed identity, then confirm the job | Application transactions succeed, and jobs.job_executions shows the nightly job at lifecycle Succeeded with the expected row count from the procedure. |
| The rows all arrived | Row counts on the key tables, source against target, taken after the source went read-only | Identical counts. A short target means a partial or failed import; a long one means the source was still taking writes and the window was not as quiet as you were told. |
| The schema all arrived | Object counts by type from sys.objects on both sides, plus the orphaned-user review from step 7 closed out | Matching counts of tables, views, procedures, functions and indexes. Anything short on the target was refused by the export because the service does not support it, which the export should have told you at the time. |
The end-to-end test that ties it together, and the one I would run in front of the client: from the application host, sign in as the application’s managed identity, insert a row through the application, force the elastic job to run immediately rather than waiting for its schedule, and confirm the job’s execution row and the effect of the procedure on the row you just inserted. That single sequence exercises private networking, Entra authentication, the migrated schema, and the replacement for the Agent, which is every substantive thing this build sheet built.
Step 10: Cut over, and decide the rollback in advance
The window is the easy thing to plan and the easy thing to get wrong, because the plan usually stops at the point where it works. What it needs is a decision point with a time against it, agreed before anybody starts, so that at hour two nobody is improvising an argument about whether to keep going.
- Quiesce the application and set the source read-only, so there is one authoritative copy and nothing arriving behind you.
- Take a final recovery point on the source: a full backup, retained, and not on the same box.
- Export, import, then run step 9 in full, including the row and object counts.
- Cut the connection string to the new server and the managed identity.
- Smoke test with the application’s own health check and one real transaction, watched by somebody who knows what the answer is supposed to look like.
Then the part that is usually missing. Set the rollback deadline at roughly two thirds of the window, so a three-hour window makes the call at two hours, and write down what trips it: validation failing on data completeness, the import not finished by the deadline, or a smoke test that does not pass cleanly. Slower than expected is not a trigger on its own. Wrong is.
The rollback itself is why the order above matters. The source is untouched. It is read-only, not gone, and nothing in this build sheet has modified it. Rolling back means setting it back to read-write and pointing the connection string at it again, which is minutes rather than a restore. Leave it in that state, running and current but unused, until the new database has been through a full business cycle including a month end. Retiring the old instance is a separate and deliberate decision taken later, not something to do on the night while everybody is relieved.
What comes next
Leave the compatibility level where it landed until you have a Query Store baseline across a full business cycle, then raise it deliberately as its own change. If this database’s siblings have dependencies that ruled them out of this destination, the next build sheet takes the same estate into a Managed Instance, where the Agent, the cross-database queries and the linked servers all still work.
Azure SQL
‹ Previous: [SQL 2] Azure SQL Database: The Database Without the Instance
Next: [SQL 3] Managed Instance: The Instance That Survived the Move ›
