Recently, we were tasked to update existing on-premises SQL Server jobs to send data to Azure SQL databases. The goal is to make this happen with minimal cost or code impact.
After research, we decided to use SQL linked server object to establish the connection between on-premises SQL data to Azure SQL database.
Below are the steps:
1 - Create a linked server object:
- Use SSMS to connect to the on-premises SQL Server and navigate to the Linked Server node under Server Object.
- Right click on the Linked Servers node and select the New Linked Server.
- Enter the name for "Linked server" and select "Microsoft OLE DB for SQLServer" provider.
- Select "Security" on the left tree and provide Azure SQL login credential for "Be made using this security context" option
2 - Update the existing SQL job to send data to the Azure SQL database:
- Open the existing SQL Server job and update the SQL statement to use new Linked Server name instead of on-premises SQL Server name