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.
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.
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:
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.
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.
Select SQL Server EE and corresponding v-switch for each AZ for HA configuration;
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.
Select EE edition, and the instance size equal to or less than the host provided.
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;
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).
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.
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.
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.
You should login the remote desktop connection with the RAM account created in the Bastion host control panel.
Input your password for the RAM user;
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.
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.
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.
Now, log in into another windows host which running the secondary SQL Server instance, and repeat previous task on enabling SSIS service.
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.
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.
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.
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).
Build a Real-time Cloud Data Lake Based on Alibaba Cloud DLA and Apache Hudi
ApsaraDB - March 4, 2021
ClouderLouder - November 25, 2020
Alibaba Clouder - September 6, 2019
Alibaba Clouder - February 28, 2019
Rupal_Click2Cloud - July 27, 2021
Alibaba Clouder - August 20, 2020
Cloud Migration Solution
Secure and easy solutions for moving you workloads to the cloud
Learn More
Database Migration Solution
Migrating to fully managed cloud databases brings a host of benefits including scalability, reliability, and cost efficiency.
Learn More
Oracle Database Migration Solution
Migrate your legacy Oracle databases to Alibaba Cloud to save on long-term costs and take advantage of improved scalability, reliability, robust security, high performance, and cloud-native features.
Learn More
ADAM(Advanced Database & Application Migration)
An easy transformation for heterogeneous database.
Learn MoreMore Posts by ApsaraDB
