Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog Migrate SQL Server and SSIS Workloads with Active Directory (AD) Integration to Alibaba Cloud: Part 1

Migrate SQL Server and SSIS Workloads with Active Directory (AD) Integration to Alibaba Cloud: Part 1

This article shares the best practice on your SQL Server and SSIS's cloud migration to Alibaba Cloud.

By Chi Wai Chan, Product Development Solution Architect at Alibaba Cloud

ApsaraDB for MyBase is a dedicated service for managing databases on the cloud provided by Alibaba Cloud. MyBase is easy to use and flexible, is able to meet the requirements of database regulations and compliance, and supports high security and high performance scenarios.

This article focuses on providing the best practice on guiding your Microsoft SQL Server Cloud Migration for not just the database engine, but also with the SSIS which is the data integration pipeline (ETL workflow) and together with Active Directory (AD) user authentication integration on Alibaba Cloud.

There are two parts for Migrating your SQL Server and SSIS workload with Active Directory (AD) integration to Alibaba cloud. This article is Part 1 of the series, please read the next article for Part 2.

Introduction to ApsaraDB MyBase

ApsaraDB MyBase currently supports MySQL and SQL Server databases. It provides dedicated cloud resources, supports over-commitment of resources and autonomous operations and maintenance, and allows users to grant account permissions on databases and operating systems. You can purchase multiple hosts to form a cluster, and deploy different types of database instances in the cluster to achieve hybrid deployment. This can help you meet diverse business needs and increase resource utilization, meet your business-critical requirements for cloud database management.

Key Features of ApsaraDB for MyBase

  • Enterprise-level database kernels: ApsaraDB for MyBase uses an enhanced and optimized kernel based on an open source database kernel. For more information, see Kernel benefits.
  • Fully managed database services: ApsaraDB for MyBase provides a full range of database service capabilities, including installation, deployment, high availability, monitoring, alerting, account management, automatic optimization, and backup and restoration.
  • Open operating system (OS) systems: ApsaraDB for MyBase allows you to install custom scripts as independently and flexibly as possible to meet the requirements of large and medium-sized enterprises on database services. For more information, see Connect to a Linux host from a bastion host.
  • User-level resource isolation: You can purchase hosts so that your resources are completely isolated. Resource isolation avoids resource scrambling and ensures service stability.
  • Reduced costs ApsaraDB for MyBase meets the business architecture requirements and allows you to take advantage of different features of database resources. You can configure proper over-commitment ratios to maximize resource utilization and reduce costs. For more information, see Enable or disable instance allocation on a host.

ApsaraDB MyBase for SQL Server

MyBase for SQL Server instances support the high-availability architecture and provide the capability to restore data to a specific point in time. This allows the instances to run on various enterprise applications. MyBase for SQL Server instances also cover Microsoft licensing fees. This reduces additional costs. The following advanced features are also provided:

  • Supports full range of SQL Server services including SSAS is Microsoft SQL Server's Analysis Services, SSIS which is the SQL Server Integration Services for data transformation and migration capability (ETL), SSRS which stands for Sql Server Reporting Services for creating reports and making visualization to better understand your data. Now you can provision and configure all those services, which often used together with SQL server, on ApsaraDB for MyBase;
  • Disk encryption: This function encrypts the entire data disks of your RDS instance based on block storage. Your data cannot be accessed even if it is leaked. For more information, see Configure disk encryption for an ApsaraDB RDS for SQL Server instance. In addition, this function does not interrupt your workloads, and you do not need to modify your application.

SQL Server, SSIS, and AD Migration Guide

1

Step 1: Create a Dedicated Cluster

You must create an ApsaraDB for MyBase dedicated cluster before you use the features of ApsaraDB for MyBase. A dedicated cluster is formerly known as a host group.

2

Step 2: Purchase Host

After you create the ApsaraDB for MyBase dedicated cluster, create hosts in the ApsaraDB for MyBase dedicated cluster.

You can add multiple host to a cluster. And for ensuring high availability setup for cross availability zones (AZ), please add two hosts at least, and one for each AZ.

3

Select SQL Server EE and corresponding v-switch for each AZ for HA configuration;

4

Step 3: Deploy Database (SQL Server)

After you create hosts in the ApsaraDB for MyBase dedicated cluster, create SQL Server instances on the hosts. The system allocates the created instances to the hosts in the ApsaraDB for MyBase dedicated cluster based on the specified resource allocation policy.

5

Select EE edition, and the instance size equal to or less than the host provided.

6

Step 4: Manage Database (Configuration)

After you create instances in the ApsaraDB for MyBase dedicated cluster, you can set up and configure the database, SSIS, and related Active Directory setup;

4.1 Access host OS with Bastion Host

Alibaba Cloud database MyBase cluster allows you to obtain more independent and controllable permissions by opening OS permissions, giving full play to the value of DBA and solving database problems in a timely manner. Create a Bastionhost, which is a system operations and maintenance (O&M) and security audit platform, according to below steps; Please take note on the account username and password (e.g. osadmin) you have put for the bastion host account, this is the account information for you to login the host OS (windows server).

7

4.2 Setup your Bastion Host

You have to setup the bastion host for RAM account login (e.g. bastionhostuser1), by clicking on the "Bastion Host Instance ID/Name" of your newly created instance.

8

9

Please also verify that the host account information for two hosts created, make sure that the OS accounts are correctly assigned by inputting required information (osadmin) and click verify.

10

4.3 Access your host OS via bastion host

You can access the bastion host on windows with Remote Desktop Connection within the VPC with the private endpoint, or from the public network with public endpoints on port 63389 or the one specified under the "port" columns of your bastion host information panel.

11

You should login the remote desktop connection with the RAM account created in the Bastion host control panel.

12

Input your password for the RAM user;

13

Select the primary instance. For checking which one is the primary instance, you can go back to MyBase home page, select instances, and then there would be information showing which instance is primary and which is secondary if you have provisioned high availability SQL Server setup. And after you double click the hostname, you should be redirected to RDP login on windows server.

14

4.4 Windows Server OS login for SSIS setup

Select the SQL server edition you have chosen in instance creation (e.g. SQL Server 2016 in this demo), select SQL Server 2016 Configuration, right click, select more, and then run it with administrator.

15

Then select "SQL Server Integration Service 13.0", double click, and select the "Service" tab, and then change the "start mode" to "automatic". Then go back to the "Logon" tab, and click "start", and then "apply" the settings. Wait a few seconds until the state changed to running, then your SSIS is up and running.

16

Now, log in into another windows host which running the secondary SQL Server instance, and repeat previous task on enabling SSIS service.

Summary

Now you have successfully deployed SQL Server and SSIS on MyBase cluster, and you can go to the MyBase homepage, select the instance tab, then by clicking the "instance ID/Name", you would be redirected to the RDS homepage.

17

In the RDS homepage, you can be able to manage the database instance as usual, create accounts, create database, and the database connection information is shown in the "database connection" tab.

Note: Don't forget to setup your security group / whitelist for access control.

18

Now login the primary RDS SQL Server instance, and you can access the database with Microsoft SQL Server Management Studio with local windows authentication, where you can manage / operate the database as well as deploy the SSIS package.

19

20

Congratulations! You have now successfully deployed your SQL Server into ApsaraDB MyBase on Alibaba Cloud. And now you can use follow this DTS data migration guide to migrate the data from your existing SQL server to Alibaba cloud. At this moment, you can login your SQL Server using SQL Authentication and deploy the SSIS package locally with file upload in the host OS.

In the next section Part 2, it would talk about integrating with Active Directory (AD) for domain login, and as a result, you can use windows authentication for cross machines login to SQL Server and have remote deployment of your SSIS package via visual studio SQL Server Data Tools (SSDT).

Source: Migrating your SQL Server and SSIS workload with Active Directory (AD) integration to Alibaba cloud: Part A

MySQL Database SQL Server Cloud migration Data Analytics Active Directory Data Warehouses ApsaraDB for MyBase Data Modernization Data Mangement SSIS
0 0 0
Share on

Read previous post:

Build a Real-time Cloud Data Lake Based on Alibaba Cloud DLA and Apache Hudi

Read next post:

Migrate SQL Server and SSIS Workloads with Active Directory (AD) Integration to Alibaba Cloud: Part 2

ApsaraDB

443 posts | 93 followers

You may also like

Comments

ApsaraDB

443 posts | 93 followers

Related Products

More Posts by ApsaraDB

See All