DEV Community

Goodluck Ekeoma Adiole
Goodluck Ekeoma Adiole

Posted on

Deploying Oracle-to-MSSQL Sync Script to Azure Functions via VS Code.

Step-by-Step Guide: Deploying Oracle-to-MSSQL Sync Script to Azure Functions via VS Code

Detailed information on all steps.


Prerequisites

  1. Azure Account (free tier suffices).
  2. VS Code installed.
  3. Python (3.8+ recommended).
  4. Azure Functions Core Tools (for local testing).
  5. Azure Functions VS Code Extension.
  6. Python Extension for VS Code.

Step 1: Install Required Tools

  1. Install Azure Functions Core Tools:
   npm install -g azure-functions-core-tools@4
Enter fullscreen mode Exit fullscreen mode

(If you don’t have Node.js/npm, install Node.js).

  1. Install VS Code Extensions:
    • Open VS Code → Extensions (Ctrl+Shift+X) → Search and install:
      • Azure Functions
      • Python (by Microsoft)
      • Azure Account (for authentication)

Step 2: Set Up the Project Structure

  1. Create a Project Folder:
   mkdir oracle-to-mssql-sync
   cd oracle-to-mssql-sync
   code .  # Opens VS Code in this folder
Enter fullscreen mode Exit fullscreen mode
  1. Create Virtual Environment (avoid dependency conflicts):

    • In VS Code:
      • Open terminal (Terminal → New Terminal).
      • Run:
       python -m venv .venv
    
 - Activate the environment:  
   - **Windows**: `.venv\Scripts\activate`  
   - **Linux/macOS**: `source .venv/bin/activate`  
Enter fullscreen mode Exit fullscreen mode
  1. Install Required Packages:
   pip install azure-functions pyodbc cx_Oracle
Enter fullscreen mode Exit fullscreen mode

(cx_Oracle for Oracle, pyodbc for MSSQL)

  1. 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
    
 - `local.settings.json`: For local environment variables.  
 - `function_app.py`: Azure Functions entry point (if not using `__init__.py`).  
Enter fullscreen mode Exit fullscreen mode

Step 3: Configure the Timer Trigger

  1. 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)
Enter fullscreen mode Exit fullscreen mode
  1. Add Timer Schedule:

    • Create a file named function.json in a new folder TimerTrigger (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
         }
       ]
     }
    

Step 4: Configure Environment Variables

  1. 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"
     }
   }
Enter fullscreen mode Exit fullscreen mode

Replace placeholders with your actual DB connection strings.

  1. Access Variables in Code:
   import os
   oracle_conn_str = os.environ["ORACLE_CONN_STR"]
   mssql_conn_str = os.environ["MSSQL_CONN_STR"]
Enter fullscreen mode Exit fullscreen mode

Step 5: Test Locally

  1. Start Azure Functions Emulator:

    • In VS Code terminal:
     func start
    
  • Verify output:

     TimerTrigger: [GET,POST] http://localhost:7071/api/TimerTrigger
    
  1. Check Logs:
    • You’ll see cron-like executions every 2 minutes.

Step 6: Deploy to Azure Functions

  1. Sign in to Azure in VS Code:

    • Click the Azure logo in the sidebar → Sign in to Azure.
  2. 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.
  3. Deploy Code:

    • Right-click your Function App under ResourcesDeploy to Function App.
    • Select your project folder when prompted.
    • Wait for deployment (check VS Code output terminal).

Step 7: Configure Azure Settings

  1. 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.
  2. Verify Timer Schedule:

    • In your Function App → Functions → select your timer function.
    • Click IntegrationTimer trigger → Confirm schedule is */2 * * * *.

Step 8: Test in Azure

  1. Trigger Manually:

    • In the Azure Portal → Function App → Functions → your function → Code + TestTest/Run.
    • Check Logs for output.
  2. Monitor Executions:

    • Go to MonitorLogs → 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.
  • 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 * * * * in function.json.
    • Check timezone (Azure uses UTC by default).

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)