Step-by-Step Guide: Deploying Oracle-to-MSSQL Sync Script to Azure Functions via VS Code
Detailed information on all steps.
Prerequisites
- Azure Account (free tier suffices).
- VS Code installed.
- Python (3.8+ recommended).
- Azure Functions Core Tools (for local testing).
- Azure Functions VS Code Extension.
- Python Extension for VS Code.
Step 1: Install Required Tools
- Install Azure Functions Core Tools:
npm install -g azure-functions-core-tools@4
(If you don’t have Node.js/npm, install Node.js).
-
Install VS Code Extensions:
- Open VS Code → Extensions (Ctrl+Shift+X) → Search and install:
- Azure Functions
- Python (by Microsoft)
- Azure Account (for authentication)
- Open VS Code → Extensions (Ctrl+Shift+X) → Search and install:
Step 2: Set Up the Project Structure
- Create a Project Folder:
mkdir oracle-to-mssql-sync
cd oracle-to-mssql-sync
code . # Opens VS Code in this folder
-
Create Virtual Environment (avoid dependency conflicts):
- In VS Code:
- Open terminal (Terminal → New Terminal).
- Run:
python -m venv .venv
- In VS Code:
- Activate the environment:
- **Windows**: `.venv\Scripts\activate`
- **Linux/macOS**: `source .venv/bin/activate`
- Install Required Packages:
pip install azure-functions pyodbc cx_Oracle
(cx_Oracle
for Oracle, pyodbc
for MSSQL)
-
Create Key Files:
- In VS Code, create:
-
__init__.py
: Your main script (copy your existing script here). -
requirements.txt
: Dependencies (auto-generate with):
-
pip freeze > requirements.txt
- In VS Code, create:
- `local.settings.json`: For local environment variables.
- `function_app.py`: Azure Functions entry point (if not using `__init__.py`).
Step 3: Configure the Timer Trigger
-
Modify
__init__.py
to use a timer trigger:
import azure.functions as func
import datetime
def main(mytimer: func.TimerRequest) -> None:
utc_timestamp = datetime.datetime.utcnow().isoformat()
if mytimer.past_due:
print("Timer is running late!")
# PASTE YOUR SCRIPT LOGIC HERE
print("Oracle to MSSQL sync ran at:", utc_timestamp)
-
Add Timer Schedule:
- Create a file named
function.json
in a new folderTimerTrigger
(if auto-generated by Azure Tools, skip this). - Contents of
function.json
:
{ "scriptFile": "__init__.py", "bindings": [ { "name": "mytimer", "type": "timerTrigger", "direction": "in", "schedule": "*/2 * * * *" # Every 2 minutes } ] }
- Create a file named
Step 4: Configure Environment Variables
-
For Local Testing (
local.settings.json
):
{
"IsEncrypted": false,
"Values": {
"FUNCTIONS_WORKER_RUNTIME": "python",
"AzureWebJobsStorage": "UseDevelopmentStorage=true", # For local storage
"ORACLE_CONN_STR": "your-oracle-connection-string",
"MSSQL_CONN_STR": "your-mssql-connection-string"
}
}
Replace placeholders with your actual DB connection strings.
- Access Variables in Code:
import os
oracle_conn_str = os.environ["ORACLE_CONN_STR"]
mssql_conn_str = os.environ["MSSQL_CONN_STR"]
Step 5: Test Locally
-
Start Azure Functions Emulator:
- In VS Code terminal:
func start
-
Verify output:
TimerTrigger: [GET,POST] http://localhost:7071/api/TimerTrigger
-
Check Logs:
- You’ll see cron-like executions every 2 minutes.
Step 6: Deploy to Azure Functions
-
Sign in to Azure in VS Code:
- Click the Azure logo in the sidebar → Sign in to Azure.
-
Create a Function App in Azure:
- In the Azure Functions sidebar:
- Click Create Function App in Azure (↑ icon).
- Follow prompts:
-
Function App Name:
oracle-mssql-sync
(globally unique). - Runtime: Python 3.9.
- Region: Pick one near you.
- In the Azure Functions sidebar:
-
Deploy Code:
- Right-click your Function App under Resources → Deploy to Function App.
- Select your project folder when prompted.
- Wait for deployment (check VS Code output terminal).
Step 7: Configure Azure Settings
-
Set Connection Strings in Azure Portal:
- Go to Azure Portal → Your Function App → Configuration.
- Under Application settings, add:
-
ORACLE_CONN_STR
→ Your Oracle connection string. -
MSSQL_CONN_STR
→ Your MSSQL connection string.
-
- Save changes.
-
Verify Timer Schedule:
- In your Function App → Functions → select your timer function.
- Click Integration → Timer trigger → Confirm schedule is
*/2 * * * *
.
Step 8: Test in Azure
-
Trigger Manually:
- In the Azure Portal → Function App → Functions → your function → Code + Test → Test/Run.
- Check Logs for output.
-
Monitor Executions:
- Go to Monitor → Logs → View real-time executions every 2 minutes.
Troubleshooting Tips
-
Deployment Failures:
- Check
requirements.txt
exists with correct dependencies. - Run
pip install -r requirements.txt
locally to validate.
- Check
-
DB Connection Issues:
- Verify connection strings in Azure Application Settings.
- Allow Azure IPs in your Oracle/MSSQL firewall.
-
Timer Not Firing:
- Confirm cron expression
*/2 * * * *
infunction.json
. - Check timezone (Azure uses UTC by default).
- Confirm cron expression
Final Notes
- Cost: Azure Functions has a generous free tier (1M executions/month).
- Security: Use Azure Key Vault for sensitive connection strings in production.
- Logs: Use Azure’s Application Insights for advanced monitoring.
By following these steps, your Oracle-to-MSSQL sync will run securely in Azure every 2 minutes! 🚀
Top comments (0)