edit-icon download-icon

Microsoft SQL Server on Alibaba Cloud

Last Updated: May 22, 2018

Microsoft SQL Server on Alibaba Cloud

Getting Started

Make use of Alibaba Cloud reliable and flexible cloud computing infrastructure and platform services can help you to run Microsoft SQL Server more stably and smoothly. Microsoft SQL server on Alibaba cloud Elastic Compute Service(ECS) just like when you installed on-premises, you are responsible for installation, administering the database, including backups and recovery, patching the operating system and the database, tuning of the operating system and database parameters, managing security, and configuring high availability or replication. It gives you complete control over every setting. To learn what is ECS, please refer to the detail .This article provides you with the best practice for Microsoft SQL Server instance running on ECS instance. Please be aware this article used SQL server 2016 Enterprise Edition as the example, so not every option is appropriate for every version. It also presents a simplistic procedure to guide you for running your Microsoft SQL server. If you want to choose Alibaba RDS for SQL server, please refer to Quick Start for SQL Server.

Prerequisites

Alibaba Cloud account and RAM

You must have registered to an Alibaba Cloud account. We recommend you to enable RAM to manage your account. Resource Access Management (RAM) is a cloud service that helps you manage user identities and control resources access. Using RAM, you can create and manage user accounts, and control the operation permissions that these user accounts possess for resources under your account, for example, employees, systems, and applications. For the detail information please refer to Quick Start and RAM best practices.

SQL Server Images and Version

Alibaba cloud support Bring Your Own License (BYOL) and Images.SQL Server Enterprise, Standard, and Express Editions are licensed for production use. For Enterprise and Standard Editions, contact your software vendor for the installation media. You can find purchasing information and a directory of Microsoft partners on Microsoft official purchasing website.Free editions you can find at Microsoft official website: SQL Server Downloads.

ECS instance

Elastic Compute Service (ECS) is a type of computing service that features elastic processing capabilities. ECS has a simpler and more efficient management mode than physical servers. You can create instances, change the operating system, and add or release any number of ECS instances at any time to fit your business needs.An ECS instance is a virtual computing environment that includes CPU, memory, and other basic computing components. An instance is the core component of ECS and is the actual operating entity offered by Alibaba Cloud. Other resources, such as disks, images, and snapshots, can only be used in conjunction with an ECS instance.Before create SQL server instances you have to create ECS instances first using the ECS console, about the detail information, please refer to Create ECS instances.

VPC

Virtual Private Cloud (VPC) creates an isolated network environment for you SQL Server environment. You can select an IP address range, divide networks, and configure the routing list and gateway. The interflow of VPC intranet and between VPC and physical IDC machine rooms can be realized among regions or users. About how to create VPC please refer to the detail.

Cloud Disk

Ultra Cloud Disk: When you create ECS instance, Ultra Cloud Disk as the system disk provides a high-performance location for operating system and windows page file.
SSD Cloud Disk: When you create ECS instance we recommend you choose SSD cloud disk store the database files, tempdb,log file separately. Separate SSD cloud disks provide high performance and high reliability.

  • High performance: A single SSD cloud disk provides a maximum of 20,000 random reading/writing IOPS and 300 MBps throughput of storage performance.
  • IOPS=min{1200+30*disk_size, 20000}. The base is 1200 IOPS, and each GB provides 30 random IOPS up to a maximum of 20,000.
  • Throughput=min{80+0.5*disk_size, 300} MBps. The base is 80 MBps, and each GB adds an additional 0.5 MBps up to a maximum of 300 MBps throughput performance.
  • Reliability: SSD cloud disks use Alibaba Cloud’s Apsara distributed storage technology, based on three distributed copies, which can guarantee 99.9999999% data reliability.

For how to create a cloud disk, please refer to create a cloud disk.

OSS

Alibaba Cloud Object Storage Service (OSS) is a network-based data access service. OSS enables you to store and retrieve unstructured data including text files, images, audios, and videos. We recommend you backup your SQL Server database into OSS. For how to use OSS please see Get started with Object Storage Service

Shared block storage

Shared Block Storage is designed for the high availability architecture of enterprise-class applications and provide shared access to block storage devices in a Share-everything architecture, such as the SQL Server always on with WSFC node architecture, which is common among government departments, enterprises, and financial customers, and the high availability server cluster architecture. For about shared block storage detail, please see Shared block storage FAQ

Installation

This section provides general information about how to create a SQL server instance on Elastic Compute Service (ECS). The tutorial includes the following tasks:

Create SQL Server instance

We recommend you to close the windows update setting before you create your SQL Server instance as below:
1.Click “run” button and input gpedit.msc
2.You will open the “Local Computer Policy”:

  • Select “Administrative template”
  • Select ”Windows Components”
  • Select ”Windows Update”

3.Change the “Configure Automatic Updates” option todisablestatus.

After the SQL server instance creating you can decide whether need to enable the update setting. It is the same method with on premise to create your SQL Server instance on ECS instance. You can choose installation wizard, command-line or using a configuration file. For how to create or install please refer to Microsoft website.

Windows Settings

This section provides you the settings about how to configure windows settings to better optimize your SQL Server instance. We recommend you to setting the windows server firewall rule to specify the IP addresses for your client computer. It is very important security policy when you create your SQL Server instance to be able to connect to the database from other client machines. Configure the firewall to allow incoming traffic:

  • Open your windows firewall with advanced security.

firewallsetting

  • Click right button to create a new inbound rule.

firewallrule1

  • Select your program path.

Input:%ProgramFiles%\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\sqlservr.exe

firewallrule2

  • Allow the connection.

firewallrule3

  • Name your rule,for example:mysqlserver.

firewallrule4

  • Set your remote policy.

firewallrule5

The system default network settings are usually sufficient. Alibaba cloud offers you the high capacity and performance network. When you create ECS instances you can choose the network bandwidth from 1M up to 100M,about the bandwidth, Please refer to ECS Bandwidth FAQs.Windows requires anti-virus software to be installed. Install enterprise level anti-virus software and enable virus library updating and real-time protection, however, if the antivirus software is not configured correctly, it can negatively impact your database performance. Microsoft provides advice about how to choose antivirus software.

High Availability

We recommend you to use Windows Server Failover Clustering and SQL Server AlwaysOn Availability Groups as your SQL Server high availability solution on ECS instances.
The Always On feature must be enabled for the server instance ‘sqlserver’ before you can create an availability group on this instance. To enable Always On:

  • Open the SQL Server Configuration Manager.
  • Select SQL Server Services.
  • Right-click the SQL Server instance name.
  • Select Properties,and use the Always On High Availability tab of the SQL Server Properties dialog. (ObjectExplorer).

You should create 3 ECS instances, one is for the DC(domain controller)and DNS, the other two are the cluster nodes. You need to create a shared cloud disk as the shared block storage as below:

createsharedstorage

and then choose the region and size, please pay attention you can only buy 20GB at least as the shared block storage.

createsharedstorage

For how to plan, configure your WSFC and Always on group please see Microsoft official website.

Backup

The SQL Server backup and restore component provides an essential safeguard for protecting critical data stored in your SQL Server databases. We strongly recommend place the databases, logs, backups on separate SSD Cloud disk. Placing the data and backups on SSD Cloud disk also enhances the I/O performance for both writing backups and the production use of the database. A backup and restore strategy contains a backup portion and a restore portion. Designing an effective backup and restore strategy requires careful planning, implementation, and testing. There is no difference between doing SQL Server database backup and restore from Alibaba cloud and on premise version. Backing Up and Restoring How-to Topics (SQL Server Management Studio)and Backing Up and Restoring How-to Topics (Transact-SQL)

provides best practice for how to implement a solid backup and maintenance action.
Use the Cloud SSD disk to store your backups and then copy them into OSS bucket or you can use the windows task scheduler copy them as the regular task.

Performance

This section provides you with how to tuning your SQL server instance performance on ECS instances. Running SQL Server on ECS environment we recommend you continue using the same database performance tuning options that are applicable to SQL Server in on-premises server environment SQL Server Enterprise Edition has a long list of added capabilities over Standard Edition. If you are migrating an existing license to ECS, there are some performance options that you might consider implementing.

Separate Cloud Disk

We recommend you to place the databases, logs, backups on separate SSD Cloud disk during you create your SQL Server instances

Table Compression

Generally, data compression reduces the space occupied by the data. It can help improve performance of I/O intensive workloads because the data is stored in fewer pages and queries need to read fewer pages from disk. Data compression can be performed for a table, clustered index, non-clustered index. We recommend you to enable table and index compression. It might seem counter-intuitive that compressing tables could make your system perform faster, but in most cases, that’s what happens. The tradeoff is using a small amount of CPU cycles to compress the data and eliminate the extra disk IO required to read and write the bigger blocks. Generally, the less disk IO your system uses, the better its performance will be. Instructions for estimating and enabling table and index compression please refer to Micosoft website

Enable buffer pool extension (BPE)

We recommend you to use the buffer pool extension to speed data access. The buffer pool extension feature enables you to push clean pages to the SSD Cloud disk, instead of dropping them. This works along the same lines as virtual memory, which is to say by swapping, and gives you access to the clean pages on the SSD Cloud disk, which is faster than you would get by going to the regular disk to fetch the data. This technique is not nearly as fast as having enough memory, but it can give you a modest increase in throughput when your available memory is low. For how to enable BPE and the technology detail please refer to Microsoft website

Max degree of parallelism setting

We recommend you to configure the max degree of parallelism option to 8. When your SQL Server instance runs on ECS that has more than one processor, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution.
This value is set using sp_configure system procedure or you use SQL Server Management Studio. The default value is 0 which means there is no upper limit and SQL Server can use all available processors. If you set Max Degree of Parallelism to 1 then all queries will execute serially. This setting is ignored on servers with a single processor. Occasionally you might find that parallelism actually hinders performance of some queries. In this case the cost of initializing and synchronizing parallel plans might exceed the benefit of running portions of the query on multiple threads. If you feel that serial execution of a particular query can provide better performance you can override this setting using MAXDOP option within an individual query. For how to configure please refer to Microsoft website

Monitor

We recommend you to use CloudMonitor to monitor your ECS instances. Make sure that your ECS monitoring agents are functional to collect metric data. Otherwise, you must install the agent manually. For more information, see How to install CloudMonitor agent.

firewallrules2

Monitoring capability

CloudMonitor allows more than 30 metrics covering CPU, memory, disk, and network to meet the basic monitoring and O&M requirements of the servers. Click here to view the full list of metrics the switch.

Alarm capability

CloudMonitor provides alarm service for all metrics, allowing you to set alarm rules for individual servers, application groups, and all the other resources. You can use the alarm service as per your business requirements. CloudMonitor provides Host monitoring metrics to set alarm rules for individual servers, application groups, and all the other resources. You can use the alarm services as per your business requirements. You can use the alarm service directly in the host monitoring list, or use it in your application group once you add servers to the group. You can add the alarm rules directly in the host monitoring list, or use it in your application group once you add servers to the group. For how to create an alarm service, please see here

Management Studio

You can use SQL Server Management Studio to perform most administrative tasks. This section provides you with how to manage your SQL server instance on ECS instances.

Remote SQL Server Management Studio

Microsoft offered SQL Server Management Studio to configure SQL Server databases. You can download and installed it on your desktop, connect to database remotely.

Default SQL Server Management Studio

You also can use the default SQL Server Management Studio which running on the instance itself. With this method you should connect to your SQL Server instance through RDP.SQL Server 2012 and SQL Server 2014 both include the SQL Server Management Studio by default. For SQL Server 2016, you must download the SQL Server Management Studio from the Microsoft website and install it on the instance.By default, SQL Server uses Windows Authentication mode to control remote access to SQL Server itself. If you need to use SQL Server Authentication mode, change the authentication mode.

Thank you! We've received your feedback.