Differences between SQL Server on-premises and in a Managed Instance

When assessing the benefits of a Managed Instance, it is important to compare like servers and services. The Managed Instance benefits from always being up to date in the cloud, which means that some features in on-premises SQL Server may be either unnecessary or have alternatives. There are also specific cases when a particular feature works in a slightly different way.

Selecting a pricing tier and service objective

Managed Instance is based on the VCore purchasing model. The intent of it is to make for an easier transition from on-premises machines, or IaaS in Azure, to independently choose to scale out compute and storage. There are two different service tiers for managed instances: General Purpose and Business Critical.

1. General Purpose tiers are for balanced usage with scalable compute and storage options. High availability is built in based on Azure blob storage and Azure Service Fabric.

2. Business Critical tiers are typically for applications with high I/O requirements that offer minimal impact due to maintenance operations. It offers the highest resilience to failures by using isolated replicas. High availability is built-in and is based on a technology similar to Always On availability groups and Azure Service Fabric. It has additional read-only replicas that can be used for reporting or other read-only workloads. Additional features include In-memory OLTP and SSD storage for increased performance.

All service tiers guarantee 99.99 % availability and independently scale size and compute.

All options have hardware choices where you can choose the generation of hardware, and your options as of the writing of this book are:

• Gen4: Up to 24 logical CPUs based on Intel E5-2673 v3 (Haswell) 2.4-GHz processors, vCore = 1 PP (physical core), 7 GB per core, attached SSD.

• Gen5: Up to 80 logical CPUs based on Intel E5-2673 v4 (Broadwell) 2.3-GHz processors, vCore = 1 LP (hyper-thread), 5.1 GB per core, fast eNVM SSD.

For additional details on the different service-tiers, go to https://docs.microsoft.com/azure/sql-database/sql-database-managed-instance-resource-limits#service-tier-characteristics.

With software assurance from your on-premises offering, you can exchange existing licenses for discounted rates on a managed instance using Azure Hybrid Benefit for SQL Server at https://azure.microsoft.com/pricing/hybrid-benefit.

Provisioning a managed instance from the portal

Once the decision is made that Managed Instance is the best platform for your needs, the next step is provisioning. It is highly recommended that you use the portal for provisioning until you fully understand the experience, particularly the networking aspects because the portal automates much of it for you and limits the number of issues that can arise.

The following instructions can help you complete the task of creating a managed instance through the portal:

• From the show portal menu, choose + Create A Resource.

• In the search box type managed instance and choose the Azure SQL Instance option when displayed.

• Select the create box, displaying the steps necessary to complete the creation of the managed instance.

• The basics step includes all of the minimum required information to provision a managed instance. It requires a subscription (see previous note on available subscriptions) and a resource group. There is an option to create a new resource group.

• Further down the page are the details that are needed, including the managed instance name, region, compute and storage. The portal guides you in the requirements for naming but not in what you will need for compute and storage. If you choose Configure Managed Instance inside the compute and storage section, you will go to another screen that will allow for customization of options and additional choices. This is also where you note you have your own license, an option that can save you money as the license is automatically included in a managed instance.

• The final section of the basics is setting up the Administrator account. This can be any valid name, which is checked for in the portal, however the name “serveradmin” is a reserved server-level role and cannot be used as an administrator account here.

• The next section is networking. This section defines the level of access and connection type. You can define your virtual network and public end points here.

• Start by creating a virtual network. If you have a compliant virtual network it can be found in the list. Otherwise it is recommended to create a new one.

• Choose a connection type to accelerate application access. The default value is proxy, however, it is recommended to use redirect. With proxy all connections are proxied via the Azure SQL Database gateways, which means to enable connectivity the client must have outbound firewall rules that allow only the IP address of the Azure SQL Database gateway port 1433. Although it is easier to set up, it is recommended that the redirect option is used for improved performance. The improvement comes with extra set up. The clients establish connections directly to the node hosting the database, which requires outbound firewall rules to all Azure IP address in the region using Network Security Groups (NSG) with service tags for ports 11000-11999, not just the Azure SQL Database gateway. This allows packets to go to the database directly and reduces latency and improves throughput and therefore performance.

• The last part of networking is the public end point option. By default for security, the public endpoint is not enabled, as it allows access to the managed instance without a VPN and there can be a security risk. Once enabled you get the option of choosing from Azure Services, Internet, and no access. Ideally you would use the no access and enable only specific endpoints as needed. See these options for scenarios that may benefit from enabling a public end point at https://docs.microsoft.com/azure/sql-database/sql-database-managed-instance-public-endpoint-securely.

• Additional Settings on the next tab is where you choose a collation if you need to change the default, change the time zone, or setup geo-replication. Geo-replication is used to create a secondary managed instance as the failover group secondary. This step allows you to choose an already setup Primary to use and will allow the new instance to join the DNS of the primary you picked. After the secondary the following steps still need to be completed. Create a VPN between the primary and secondary. Setup inbound and outbound NSG rules and create an auto failover group between the two instances.

Provisioning a managed instance using PowerShell

Creating a Managed Instance using PowerShell and the Azure Resource Manager template (ARM) requires you to have pre-created a valid v-net and subnet where you can deploy your Managed Instance. It is beneficial to use PowerShell or ARM templates if you are deploying more than one managed instance and it’s only recommended once you have some experience with it. It can take a long time for the process to complete and for you to find out you have missed a step or made a mistake. At minimum, it is highly recommended to complete at least one managed instance deployment in a test environment before attempting this to ensure you understand all the steps and pieces that are needed for the deployment.

Creating the endpoints via the portal

The recommended way to create the Public and Private endpoints shown in Figure 18-1 is to do it during the creation of the Managed instance itself. It was briefly mentioned in the section on “Provisioning a Managed Instance from the Portal” that you can define your Public and Private endpoints in the networking section. It is in that section on networking that you can define the Public endpoint under the section called “Connection type.” The default, Proxy, is recommended to use redirect mode because this enables direct connectivity to managed instance resulting in improved latency and throughput. This is ideal for peer-to-peer networking and use with other Azure services. The Public endpoint is the step directly after the connection type, allowing you to enable a public end point for the ability to connect to your managed instance from the Internet without using a VPN. It uses TDS only.

If you did not choose these during your managed instance you can still do it manually following the direction in the Microsoft docs at https://docs.microsoft.com/azure/sql-database/sql-database-managed-instance-public-endpoint-configure.

Creating the VPN Gateway via PowerShell

Native virtual network implementation and connectivity to your on-premises environment will need a VPN Gateway. To complete this Microsoft has a handy PowerShell script available at https://docs.microsoft.com/azure/sql-database/sql-database-managed-instance-configure-p2s#attach-a-vpn-gateway-to-your-managed-instance-virtual-network.

Creating a VPN Gateway to the managed instance VNet is best done from your client machine.

Ensure that you have PowerShell 5.1 and Azure PowerShell 1.4.0 or newer installed on your on-premises client. Instructions for how to install the Azure PowerShell module can be found at https://docs.microsoft.com/powershell/azure/install-az-ps#install-the-azure-powershell-module.

To create the VPN gateway, you will need the subscription Id, resource group, and a virtual network name that you used to create your managed instance. You will also need to create a certificate name prefix. The prefix is a string that you choose. These items are used in the following PowerShell script:

Click here to view code image

$scriptUrlBase = ‘https://raw.githubusercontent.com/Microsoft/sql-server-samples/master/
samples/manage/azure-sql-db-managed-instance/attach-vpn-gateway’
$parameters = @

{

  subscriptionId = ‘<subscriptionId>’
  resourceGroupName = ‘<resourceGroupName>’
  virtualNetworkName = ‘<virtualNetworkName>’
  certificateNamePrefix  = ‘<certificateNamePrefix>’
  }

Invoke- -ScriptBlock ([Scriptblock]::Create((iwr ($scriptUrlBase+’/attachVPNGateway.
ps1?t=’+ [DateTime]::Now.Ticks)).Content)) -ArgumentList $parameters, $scriptUrlBase

This code will create and install the required certificates on the client machine. It will calculate the IP subnet range needed for the gateway and then create the gateway. Finally, the script will deploy the Azure Resource Manager template that attaches the VPN Gateway to the VPN subnet.

If you prefer to create the VPN using the portal you can find those instructions in the Microsoft docs at https://docs.microsoft.com/azure/sql-database/sql-database-managed-instance-configure-p2s#create-a-vpn-connection-to-your-managed-instance.

Once you have completed the VPN connection and your TDS endpoints are ready to use, there are three main ways to connect to it.

1. Connect from an on-premises computer, sometimes called a point-to-site connection.

2. Connect from a Virtual Machine (VM); the process is different if your VM is an Azure VM or an on-premises VM.

3. Connect applications from any location.

Connect from an on-premises computer

Connecting from an on-premises computer is sometimes called a point-to-site connection. To connect using SSMS (most recent version) start with making a connection to the VPN from your on-premises machine. From the network and Internet go to VPN, then choose your managed instance Vnet to choose the connection. If prompted for an elevated privilege, choose to elevate and continue to make the connection.

When connecting using SSMS remember to use the fully qualified host name of the Managed Instance in the Server name box (see Figure 18-2).

Connect from a Virtual Machine (VM)

When connecting to a Managed Instance from a Virtual Machine (VM), the process is different if your VM is an Azure VM or an on-premises VM.

Data Migration Service

The Azure Database Migration Service is a managed service designed to enable migrations to Azure Data platforms with minimal downtime. This includes managed instance, but is not limited to it, so it can also be used for third party databases, and SQL Server databases to be moved to Azure SQL Database (single databases, or pooled databases in elastic pools) and SQL Server in an Azure VM. The third party databases that are supported for the online migration method are RDS SQL and Oracle.

There are online and offline migrations, depending on your business’ tolerance for down time. It is recommended you do a trial run with the offline migration to determine what your downtime will be before you decide on which method to use. Keep in mind that using the Azure Database Migration Service for online migrations requires using the Premium pricing tier.

Whether you use online or offline migration, be sure to create your Azure Database Migration Service in the same region as the Managed Instance you are migrating to. This will prevent errors, cut down the down time or migration time, and limit any movement of data across regions or geographies.

To do an offline migration you can follow the steps at https://docs.microsoft.com/azure/dms/tutorial-sql-server-to-managed-instance.

To do an online migration you can follow these steps at https://docs.microsoft.com/azure/dms/tutorial-sql-server-managed-instance-online.

There are several prerequisites. It is recommended that you review them before you begin. They can be found within the tutorial or directly at https://docs.microsoft.com/azure/dms/tutorial-sql-server-managed-instance-online#prerequisites.

• Chapter 19, “Migrating to SQL Server solutions in Azure” has a section on Data Migration Service.

Backup and restore

The backup and restore method of migration leverages the simplicity of moving SQL backups to Azure Blob storage or backup directly to it. Backups in Azure blob storage can be directly restored into a managed instance using the traditional T-SQL restore command. The important part to remember is that the backup file must have been previously uploaded and secured with a Shared access signature (SAS) key. Details on how to do this are covered in Chapter 10, “Developing, deploying, and managing data recovery.”

Click here to view code image

RESTORE DATABASE { database_name | @database_name_var }
FROM URL = { ‘physical_device_name’ | @physical_device_name_var } [ ,...n ] ;

Here n can be up to 64 backup devices separated in a comma delimited list.

SELECT * FROM sys.dm_operation_status   
             WHERE major_resource_id = ‘myddb’   
 ORDER BY start_time DESC;

The version of SQL Server you take your backup from is important as well. Backups from SQL 2012 SP1 CU2 and prior can only be directly uploaded as .bak files. For those greater than SQL 2012 SP1 CU2 a direct backup using the depreciated “WITH CREDENTIAL” syntax will work, and for 2016 and above you will want to backup direct using the “WITH SAS CREDENTIAL.”

If you are restoring a Transparent Data Encryption database using native restore, make sure you migrate the certificate first before you do the restore. This is done the same way should you have to move your certificate on-premises.

Subnet

Managed instances need a dedicated subnet that is not a gateway subnet. The subnet can only house managed instances. The subnet cannot be shared with other resources.

Network Security Group

The Network Security Group (NSG) that is associated with the managed instance must define the inbound and outbound security rules before any other rules. If you are doing transactional replication in your managed instance and have a publisher or distributor, you will need to open port 445 (outbound) as well to allow access to the Azure file share.

User Defined Route table

A User Defined Route (UDR) table that’s associated with the virtual network must include specific entries at https://docs.microsoft.com/azure/sql-database/sql-database-managed-instance-connectivity-architecture#user-defined-routes.

Endpoints

Managed instances should not have service end points. Make sure this option is disabled when you create the virtual network.

IP addresses

Managed instances require 16 IP addresses, and a minimum (does not allow for scale out) of 32 are recommended. As Managed Instance VNet can use up to 256 IP addresses, the number of Managed Instances that can be deployed in a single subnet depends on the subnet range. A subnet with the prefix /27 or below is recommended.

High Availability

Managed Instance offers 99.99% up time without concern for maintenance and upgrade outages. This is accomplished by using something called retry logic in your apps. Retry logic essentially is code that can retry the call when a transient fault occurs. A transient fault occurs when Azure dynamically reconfigures servers for a heavy workload and can cause clients to lose their connection. It is recommended that the client program have retry logic so that it can reestablish the connection (ideally with a 5 second delay, growing up to 60 with each retry). This delay is called back-off logic and is to ensure that the database does not get overwhelmed in a retry situation with many connections.

High Availability for managed instance is achieved by employing two models for price options based on your tolerance for degradation during maintenance. If you can tolerate some degradation, then there is a model that uses a simple separation of compute and storage. For workloads that can not have that type of impact there is a cluster model that uses a quorum of engine nodes and can guarantee very minimal impact during maintenance. As with many PaaS instances there are continuous upgrades, and this potential impact is something to strongly consider when choosing tiers when installing the managed instance.

Replication

Managed Instance uses transactional replication to replicate data to another instance database, a SQL Server, database, a single database in Azure, or a pooled database in an Azure SQL database elastic pool. A managed instance can host a publisher, distributor and subscriber database. Common configurations can be found at https://docs.microsoft.com/azure/sql-database/sql-database-managed-instance-transactional-replication#common-configurations.

For the Managed Instance to be a publisher and or distributor there are a few requirements.

• The instance cannot participate in geo-replication.

• The publisher, distributor, and subscriber all must be on the same virtual network or have VNet peering set up between all three networks.

• The authentication used between all parties must be SQL Authentication and the replication working directory must be an Azure Storage Account share and set up using TCP outbound port 445 in the security rules of NSG.

• Bidirectional or one-way replication are both supported; however, updatable subscriptions are not.

Scaling up or down

Manage Instances use vCores that allow you to define the CPU cores and configure the storage capacity you need for your instance within each tier. All databases in the managed instance share these resources. The storage and CPU can be scaled up or down as needed within the limits of the service tier, however, it does cause a downtime, and for this reason it is important to scale your resources appropriately. Keep in mind that managed instances cannot be turned off and on like other resources at will, and over provisioning will be an added cost.

Note that as you change tiers there are differences between the tiers that can affect the change. For example, if downgrading from critical to standard, the backup retention period is different. If a database exceeds the threshold database size, then extra storage costs will apply. If you are upgrading to a higher tier, you must explicitly increase the size.

Automated backups

Managed Instance uses automated backups. This service creates full backups every week, differential backups every 12 hours, and transaction log backups every 5-10 minutes. The specific frequency is based on the compute size and amount of database activity. The backups are stored in storage blogs and replicated to another data center for protection against data center outages or disasters. The default backup retention is 7 days for the general-purpose tier and up to 35 days for Business Critical.

The options to restore are:

Restore to a point in time. This can be a copy in the same or a different managed instance, but must be under the same subscription.

Restore a deleted database. This must be restored to the managed instance the backup was taken from.

Restore to a new region. This creates a new database in any existing server anywhere in the world and is used in case of a geographic disaster.

Restore from a specific long-term backup. This works only if a long-term policy has been set.

Azure Active Directory (Azure AD)

Azure AD enables Microsoft services to integrate with centrally managed identities and permissions to enhance security. Combined with Multi-factor authentication you can increase data security and still support a single sign-on process for easy use. For details on how to set up Multi-factor authentication see this link at https://docs.microsoft.com/azure/sql-database/sql-database-ssms-mfa-authentication-configure. If using a hybrid option or you need to connect to legacy or on-premises applications, Azure AD can also be an on-premises Active Directory Domain Service that is federated with the Azure AD. The benefits of this centralized authentication include:

1. Provides an alternative to native SQL Server authentication.

2. Discourages the creation of multiple user identities across database servers.

3. Allows centralized and simple password changes.

4. Allows for external (Azure AD) groups.

5. Enables integrated Windows authentication and other forms of authentication supported by Azure Active Directory.

6. Azure AD authentication uses contained database users to authenticate identities at the database level.

7. Azure AD supports token-based authentication for applications connecting to Azure SQL Database.

8. Azure AD authentication supports ADFS (domain federation) or native user/password authentication for a local Azure Active Directory without domain synchronization.

9. Azure AD supports Multi-Factor Authentication (MFA) when using SSMS.

10. Azure AD supports Active Directory Interactive Authentication when using SQL Server Data Tools (SSDT). Active Directory Integrated Authentication connects to the Managed Instance by using identities. This is similar to the connection from a federated domain.

Isolation

Security Isolation is achieved in Managed Instance by the use of VNet implementations and using VPNs and express gateways to connect to your on-premises machines. The only endpoints exposed are through private IP addresses. The underlying infrastructure is always dedicated ensuring a single-tenant infrastructure completes the trifecta of isolation security.

Auditing

Managed Instance auditing is used to track events in the audit log file of the Azure storage account. The audit file is used to maintain regulatory compliance, gain insight into discrepancies, or review for suspected security violations using threat detection. Threat detection is built-in and used to detect unusual attempts to access databases. There are alerts regarding suspicious activities, potential vulnerabilities, SQL injection attracts, and anomalous database access patterns. These alerts can be viewed from Azure Security Center and provide details of suspicious activity, offering recommendations on how to resolve the issues.

Data encryption

Data encryption is provided in motion using Transport Layer Security (TLS). Always Encrypted is offered for protection of data in flight, at rest, and during query processing. Although not strictly encryption, Dynamic data masking is used to limit exposure by masking the data. This allows an added layer of protection and the ability to determine who has access to sensitive data. This allows the data to look different to specific users without actually changing the underlaying data. Data at rest is encrypted with Transparent data encryption (TDE). TDE encrypts the data and log files, using real-time I/O encryption and decryption when it is accessed.

Row-level security

Row-level security is used to control the access to specific rows of data in a table based on the user executing a query. Row-level security simplifies design and coding by enabling you to implement restrictions on the user and not on other factors. Combined with an Azure Active Directory, this can be a powerful security feature.