Convert a Microsoft SQL Server to Oracle Autonomous Data Warehouse in Oracle Cloud Infrastructure (OCI) to take advantage of the expanded capabilities offered with Oracle PL/SQL and an autonomous database. An autonomous database scales elastically and delivers fast query performance without high-overhead database administration.
In this architecture, Oracle Cloud Infrastructure File Storage is used to migrate data from an on-premises Microsft SQL Server deployment to an Oracle Autonomous Data Warehouse. You can use Oracle Cloud Infrastructure FastConnect or IPSec VPN for private connectivity between your on-premises data center and Oracle Cloud Infrastructure (OCI).
After migrating the data to OCI, you can use the built-in machine learning, analytics, and AI tools to run complex queries across multiple data types, and build sophisticated analytical models.
- Data scientists can use Oracle Cloud Infrastructure Data Catalog to explore, discover, and analyze data.
- Business analysts can access and visualize information.
- Developers can build data-driven applications.
The following diagram illustrates this reference architecture.
Description of the illustration mssql-adb.png
The on-premises data center shown in this architecture has the following components:
- Customer-premises equipment (CPE)
CPE is the on-premises endpoint for the VPN Connect or Oracle Cloud Infrastructure FastConnect interconnection between the on-premises data center and the virtual cloud network (VCN) in Oracle Cloud Infrastructure.
- Microsoft SQL Server 2016
Microsoft SQL Server 2016 is a third-party RDBMS used to build large-scale data warehousing applications.
- Oracle SQL Developer
Oracle SQL Developer is a development environment that simplifies the development and management of Oracle Database. It offers a complete end-to-end development of your PL/SQL applications, a worksheet for running queries and scripts, a DBA console for managing the database, a reports interface, a complete data modeling solution, intergrated Oracle REST Data Services, and a migration platform for moving your 3rd party databases to Oracle, such as MS SQL Server to Oracle.
Oracle SQL Developer is certified to run on all supported Oracle Databases.
- SQL Loader
SQL*Loader loads data from external files into tables of an Oracle Database. SQL*Loader uses the field specifications in the control file to interpret the format of the datafile, parse the input data, and populate the bind arrays that correspond to a SQL INSERT statement using that data. The Oracle Database accepts the data and executes the INSERT statement to store the data in the database.
- Bulk Copy Program (bcp)
The bulk copy program utility (bcp) bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files.
The Oracle Cloud Infrastructure side of the architecture has the following components:
An Oracle Cloud Infrastructure region is a localized geographic area that contains one or more data centers, called availability domains. Regions are independent of other regions, and vast distances can separate them (across countries or even continents).
Compartments are cross-region logical partitions within an Oracle Cloud Infrastructure tenancy. Use compartments to organize your resources in Oracle Cloud, control access to the resources, and set usage quotas. To control access to the resources in a given compartment, you define policies that specify who can access the resources and what actions they can perform.
- Cloud Guard
You can use Oracle Cloud Guard to monitor and maintain the security of your resources in Oracle Cloud Infrastructure. Cloud Guard uses detector recipes that you can define to examine your resources for security weaknesses and to monitor operators and users for risky activities. When any misconfiguration or insecure activity is detected, Cloud Guard recommends corrective actions and assists with taking those actions, based on responder recipes that you can define.
- Availability domain
Availability domains are standalone, independent data centers within a region. The physical resources in each availability domain are isolated from the resources in the other availability domains, which provides fault tolerance. Availability domains don’t share infrastructure such as power or cooling, or the internal availability domain network. So, a failure at one availability domain is unlikely to affect the other availability domains in the region.
- Virtual cloud network (VCN) and subnets
A VCN is a customizable, software-defined network that you set up in an Oracle Cloud Infrastructure region. Like traditional data center networks, VCNs give you complete control over your network environment. A VCN can have multiple non-overlapping CIDR blocks that you can change after you create the VCN. You can segment a VCN into subnets, which can be scoped to a region or to an availability domain. Each subnet consists of a contiguous range of addresses that don't overlap with the other subnets in the VCN. You can change the size of a subnet after creation. A subnet can be public or private.
Every Compute instance is deployed in a VCN that can be segmented into subnets.
- Dynamic routing gateway (DRG)
The DRG is a virtual router that provides a path for private network traffic between a VCN and a network outside the region, such as a VCN in another Oracle Cloud Infrastructure region, an on-premises network, or a network in another cloud provider.
- Service gateway
The service gateway provides access from a VCN to other services, such as Oracle Cloud Infrastructure Object Storage. The traffic from the VCN to the Oracle service travels over the Oracle network fabric and never traverses the internet.
- Route table
Virtual route tables contain rules to route traffic from subnets to destinations outside a VCN, typically through gateways.
- Security list
For each subnet, you can create security rules that specify the source, destination, and type of traffic that must be allowed in and out of the subnet.
- Bastion host
The bastion host is a compute instance that serves as a secure, controlled entry point to the topology from outside the cloud. The bastion host is provisioned typically in a demilitarized zone (DMZ). It enables you to protect sensitive resources by placing them in private networks that can't be accessed directly from outside the cloud. The topology has a single, known entry point that you can monitor and audit regularly. So, you can avoid exposing the more sensitive components of the topology without compromising access to them.
- Autonomous database
Oracle Cloud Infrastructure autonomous databases are fully managed, preconfigured database environments that you can use for transaction processing and data warehousing workloads. You do not need to configure or manage any hardware, or install any software. Oracle Cloud Infrastructure handles creating the database, as well as backing up, patching, upgrading, and tuning the database.
- File storage
The Oracle Cloud Infrastructure File Storage service provides a durable, scalable, secure, enterprise-grade network file system. You can connect to a File Storage service file system from any bare metal, virtual machine, or container instance in a VCN. You can also access a file system from outside the VCN by using Oracle Cloud Infrastructure FastConnect and IPSec VPN.
- Oracle Autonomous Data Warehouse
Start with two OCPUs and one TB of storage, and enable autoscaling. This configuration ensures optimum price and performance.
- Oracle Cloud Infrastructure File
Oracle Cloud Infrastructure File Storage service provides a durable, scalable, secure, enterprise-grade network file system. For SQL Server database size in tera bytes (TB), it's recommended to connect to a File Storage service file system on the source MS SQL Server host as an NFS mount.
- Cloud Guard
Clone and customize the default recipes provided by Oracle to create custom detector and responder recipes. These recipes enable you to specify what type of security violations generate a warning and what actions are allowed to be performed on them. For example, you might want to detect Object Storage buckets that have visibility set to public.
Apply Cloud Guard at the tenancy level to cover the broadest scope and to reduce the administrative burden of maintaining multiple configurations.
You can also use the Managed List feature to apply certain configurations to detectors.
When you create a VCN, determine the number of CIDR blocks required and the size of each block based on the number of resources that you plan to attach to subnets in the VCN. Use CIDR blocks that are within the standard private IP address space.
Select CIDR blocks that don't overlap with any other network (in Oracle Cloud Infrastructure, your on-premises data center, or another cloud provider) to which you intend to set up private connections.
After you create a VCN, you can change, add, and remove its CIDR blocks.
When you design the subnets, consider your traffic flow and security requirements. Attach all the resources within a specific tier or role to the same subnet, which can serve as a security boundary.
- Security Zones
For resources that require maximum security, Oracle recommends that you use security zones. A security zone is a compartment associated with an Oracle-defined recipe of security policies that are based on best practices. For example, the resources in a security zone must not be accessible from the public internet and they must be encrypted using customer-managed keys. When you create and update resources in a security zone, Oracle Cloud Infrastructure validates the operations against the policies in the security-zone recipe, and denies operations that violate any of the policies.
Consider the following points when deploying this reference architecture.
- Scalability and Cost
As long as autoscaling is enabled, you don't need to manage scaling manually for your autonomous data warehouse. Autoscaling also ensures optimum price and performance.
Your autonomous database is backed up automatically, and the backups are retained for 60 days. You can also create manual backups to supplement the automatic backups. Manual backups are stored in a bucket that you create in Oracle Cloud Infrastructure Object Storage. You can restore and recover the database to any point-in-time during the retention period. When you initiate a point-in-time recovery, Oracle Autonomous Data Warehouse determines and uses the backup that enables faster recovery.
- Access Control
Define suitable policies in Oracle Cloud Infrastructure Identity and Access Management (IAM) to control who can access your resources in the cloud and the actions that they can perform.
Review these additional resources:
- Cloud Guard
- To download Oracle Analytics Cloud Data Sync and for information about getting started, see the "Oracle Analytics Cloud Data Sync" section on the Oracle Analytics Cloud downloads page.
- To connect to a File Storage service file system on the source MS SQL Server host as an NFS mount, see Mounting File Systems From Windows Instances.