Skip to main content

Zone Reporting Replicated SQL Database (rSQL)

Overview

Using your NetSuite data, Zone Reporting maintains a Data Warehouse (ZoneDW) for your Power BI reporting. The ZoneDW is an Azure SQL database and rSQL is a feature that replicates the ZoneDW into your Azure environment.

During the rSQL update cycle, your copy of the ZoneDW is replaced with the latest copy of Zone’s ZoneDW. Your replicated ZoneDW can be used as a data source for various systems, including Power BI, Tableau, Excel, Access, corporate databases, and other Data Warehouses.

rSQL Databases

  • rSQL Enhanced – ZoneDW and Access2ZoneDW (A2Z) databases
    • Default installation.
    • The A2Z database allows for the creation and use of custom views, tables, etc.
      • Acts as a source database for users and corporate systems.
      • Contains references (External Tables) to ZoneDW database tables.  
      • These references are updated automatically during the rSQL refresh cycle as new tables or columns are added.

Required Information

  • Your local time zone.
  • Preferred timing of rSQL update cycle (typically early morning, but flexible based on your setup).
  • In some cases, the rSQL update cycle can align with your NetSuite file processing schedule.

Expectations

  • Your ZoneDW copy (clientname_ZoneDW) will be replaced daily.
  • Our ZoneDW will be copied to your Azure SQL Server and will overwrite the existing one.
  • Any modifications you make to your ZoneDW copy (tables, views, stored procedures, etc.) will be overwritten.
  • Microsoft’s documentation on the COPY command used in this process: Microsoft COPY Command

Service Tier and Database Size

  • clientname_ZoneDW (ZoneDW)
    • Minimum Service Tier: DTU Standard, 100 DTUs - S3
    • Database Size: < 250 GB
  • Access2ZoneDW
    • Minimum Service Tier: DTU Standard, 10 DTUs - S0
    • Database Size: < 250 GB

**We have also provided some additional documentation that allows you to convert and use the vCore-based model see: Migrate Azure SQL Database from the DTU-based model to the vCore-based model

Access Options

rSQL Setup Overview – ZoneDW and Access2ZoneDW Databases

  • Requires an additional database (Access2ZoneDW) created by your Azure IT team.
  • Preferred access method: Microsoft Entra ID groups:
    • ZoneDW SQL Admins
    • ZoneDW SQL Developers
    • ZoneDW SQL Read-Only Users
  • The Access2ZoneDW database will contain External Tables referencing ZoneDW tables.
  • External Tables are updated automatically to reflect changes in ZoneDW tables or columns.
  • Users can create their own tables/views in this database, which remain unaffected during rSQL updates.
  • All users and systems will use the Access2ZoneDW database for queries via elastic queries (SELECT-only).

 

Client Setup Steps

1. Create Microsoft Entra ID Groups

  • ZoneDW SQL Admins
  • ZoneDW SQL Developers
  • ZoneDW SQL Read-Only Users

2. Create an Azure SQL Server to host the rSQL databases

Should be dedicated to rSQL.

SQL deployment Option page

  • Option: SQL Databases
  • Resource Type: Database Server

Azure SQL Server Setup Pages:

  • SQL Deployment Option
    • Option: SQL Databases
    • Resource Type: Database Server
  • Basics
    • Subscription: Choose your Azure Subscription
    • Resource Group: Needs to be dedicated for rSQL
    • Server Name: Your choice
    • Location: Determined by the existing ZoneDW database region
    • Authentication Method: Use both SQL and Microsoft Entra authentication
    • Microsoft Entra Admin: ZoneDW SQL Admins
    • Server Admin Login: Your choice
    • Password: Your choice
  • Networking 
    • Firewall rules: "Allow Azure services and resources to access this server": YES
  • Security
    • Use default settings
  • Additional Settings
    • Use default settings
  • Tags
    • Set as needed
  • Click “CREATE” to finalize

  • Important: After setup, we will need to whitelist a specific IP for initial setup and troubleshooting
  • You will control any additional IP whitelisting

 

3. Create the Access2ZoneDW Database

Azure SQL Database Setup Pages:

  • Basic
    • Subscription: Select your Azure Subscription
    • Resource Group: Should be a group dedicated for rSQL
    • Database Name: Access2ZoneDW (customizable upon request)
    • Server: Use the previously created SQL Server
    • “Want to use SQL Elastic Pool?”: No
    • Workload Environment: Production
    • Compute + Storage: 
      • Click “Configure database”
      • Minimum Requirements
        • DTU Standard
        • 10 DTUs - S0
      • Backup Storage Redundancy: Locally-redundant backup storage
  • Networking 
    • Firewall rules: "Allow Azure services and resources to access this server": YES
  • Security
    • Use default settings
  • Additional Settings
    • Collation: Latin1_General_100_CI_AI_SC_UTF8 (Critical Setting!!!)
  • Tags
    • Set as needed

4. Account & Role Creation 

We will provide scripts to:

  • Create an account for database replication:
    • Member of db_manager in master DB
    • Member of db_owner in Access2ZoneDW
  • Create a read-only account (bi_readonly)
    • Read-only access to the replicated ZoneDW database
    • Used by External Tables in Access2ZoneDW to query ZoneDW tables
  • Create new schema (repl)
    • Stores rSQL metadata:
      • repl.DatabaseInfo:
        Contains: 
        • Date/time of last rSQL update 
        • Database size/tier
      • repl.ExternalTables: 
        • Stores scripts for managing External Tables (create, delete, test SELECTs)
  • Define Access Roles in Access2ZoneDW
    • r_Developers:
      • Grants permission to:
        • Create objects (tables, views, stored procedures, etc.) in the dbo schema
        • Read access to all tables/views in dbo and repl schemas
    • r_ReadOnlyRole:

Grants read access to all tables/views in dbo and repl schemas

Was this article helpful?