1. Limitations
1.1. Some limitations of Managed Instance includes no Windows Authentication, no built in Business Intelligence stack, Log Shipping
1.1.1. It's not that all these features are not available in some form within Azure, it just means that if you're moving from on-prem and have current dependencies on certain features, there is additional work to do in order to transition away from those dependencies
1.1.1.1. Examples:
1.1.1.1.1. SQL Authentication and Azure Active Directory can be used, and with Azure AD Connect you can auto sync Windows AD with Azure AD
1.1.1.1.2. SSIS is available via Azure Data Factory
1.1.1.1.3. SSAS is available via Azure Analysis Services
1.1.1.1.4. SSRS reports can be published directly to Power BI Premium
1.1.1.1.5. Linked servers can be to other Azure SQL managed instances, so if your linked servers are all on-prem SQL Servers, you can migrate multiple servers to Azure SQL Managed Instance
2. Pricing and sizing
2.1. When provisioning SQL Managed Instance, you have a choice of two service tiers:
2.1.1. General Purpose tier
2.1.1.1. Uses SSD storage (fast I/O)
2.1.1.1.1. IOPS is scalable between 500 and 12,000
2.1.1.2. Budget oriented balance between scale and compute
2.1.1.3. Storage cost about half of Business Critical
2.1.2. Business Critical tier
2.1.2.1. Uses NVMe storage (super fast I/O)
2.1.2.1.1. NVMe is actually a storage protocol that operates on SSD and is based on high speed PCIe lanes directly to the CPU
2.1.2.2. For high IO requirements and multiple replicas
2.1.2.2.1. IOPS is scalable between 10,000 and 200,000
2.1.2.3. Supports In-Memory OLTP
2.1.2.3.1. This is the OLTP equivalent of columnstore indexes for OLAP workloads
2.1.2.4. vCore cost is about x2.5 more expensive than General Purpose
2.1.2.4.1. You can convert on-prem SQL Server license (with software assurance) to gain savings of up to 55%
2.1.2.4.2. Actually, looking at pricing calculator in Azure Portal in Oct 2020, the price looks to be closer to x4.5
2.1.2.5. Always On Availability Groups with readable replicas avilable
2.2. Hardware generations are referred to by number
2.2.1. Gen4 is older hardware technology, now considered legacy and may not be available any more in many regions
2.2.2. Gen5 is currently (as of Oct-2020) the latest hardware generation for the physical servers running the infrastructure under the hood
2.3. Compute is scalable between 4 and 80 vCores
2.3.1. Only General Purpose tier supports 4 vCores, Business Critical starts at 8
2.4. Memory is tied to vCore selection, where you get 5.1GB per vCore
2.5. Storage is generally scalable from 32GB all the way up to 8TB
2.5.1. 4 vCore General Purpose is limited to 2TB max
2.5.2. Business Critical requires you to ramp up the vCores in order to increase max storage, and the current max even at 80 vCores is 4TB
2.5.2.1. 8-16 vCores = 1TB storage max
2.5.2.2. 24 vCores = 2TB storage max
2.5.2.3. 32-80 vCores = 4TB storage max
2.6. Throughput (IOPS) is tied to database file size for General Purpose tier and vCores for Business Critical tier
2.6.1. IOPS is a standardised measure for a storage device or storage network
2.7. vCore pricing model is based on compute + storage + backup storage
2.7.1. Note that total compute price consolidates both the physical cost of the compute hardware and the SQL software licensing
3. Networking
3.1. See attached for typical architecture involving Azure SQL Managed Instance as part of a hybrid cloud configuration
3.1.1. VPN option to connect on-premises network to Azure virtual network means that network traffic is routed through a secure tunnel that traverses the public Internet
3.1.1.1. There are two options for VPN: Point-to-Site and Site-to-Site
3.1.1.1.1. Point-to-Site is easy to set up and is done by installing software on the individual on-premises Windows machine
3.1.1.1.2. Site-ot-Site typically involves using a dedicated IPSec VPN device hosted on the edge of you on-premises network
3.1.2. ExpressRoute enables private network connections between your on-premise network and Azure data centres, where network traffic does not traverse the public Internet
3.1.2.1. ExpressRoute connectivity is considered more reliable, faster and more secure than VPN for hybrid cloud
3.1.3. Each Azure virtual network can be divided into one or more subnets
3.1.4. Individual VNets can be connected within Azure using a feature called VNet Peering
3.2. Networking pre-requisites for provisioning new Azure SQL Managed Instance
3.2.1. Provision a virtual network (to contain all your managed instances within a subnet of this VNet)
3.2.1.1. You may want to create a new resource group - e.g. argento-managed-instance
3.2.1.1.1. Create a new Virtual Network in resource group
3.2.2. Provision a dedicated subnet within the VNet exclusively for managed instances
3.2.2.1. You need to ensure the IP range is large enough to accommodate future needs
3.2.2.1.1. Range of 16 IP addresses is considered bare minimum
3.2.2.1.2. Note: 5 IP addresses are always reserved for Azure
3.2.2.1.3. Addresses are reserved for primary and secondary (high availability architecture), network gateways and scaling operations
3.2.2.2. Subnet must be empty and cannot be a gateway subnet
3.2.2.3. Must be no service endpoints on the subnet
3.2.3. Provision a Route Table
3.2.3.1. There are lots of routes that need to be set up to enable SQL Managed Instance to communicate successfully with various parts of Azure network
3.2.3.2. Rather than provisioning this manually there is an official PowerShell script from Microsoft to do this for us, which saves a lot of time and greatly reduces risk of human error/omission
3.2.3.2.1. See link for overview of SQL Managed Instance networking requirements and scroll down to the Network Requirements section to see the very long list of required entries for the Route table
3.2.3.3. See link for the PowerShell script that will set up the Route table for us
3.2.3.3.1. We can run the script once the VNet and dedicated subnet are provisioned
3.2.4. Provision a Network Security Group (NSG)
3.2.4.1. Rather than provision this manually, we can let the SQL Managed Instance provisioning process create this for us, which will automatically set up the required inbound/outbound rules
3.2.4.2. Note that the PowerShell script will also create and auto-configure an NSG for us, but we may opt to delete that later and allow the SQL Managed Instance provisioning process to create and auto-configure an NSG for us
3.2.5. What the networking components (VNet, dedicated subnet, Route table and NSG) provide us with:
3.2.5.1. Secure private IP addresses
3.2.5.2. On-prem connectivity
3.2.5.2.1. But requires VPN or ExpressRoute set up
3.2.5.3. Connectivity to other Azure resources
3.2.5.3.1. This means resources either inside the same VNet as the managed instances or to other VNets using VNet Peering
3.2.6. We have two connectivity options for connecting to SQL Managed Instance: Proxy and Redirect
3.2.6.1. Proxy uses a service gateway that listens on port 1433 and acts as an intermediary for all traffic passing between SQL Managed Instance and the client
3.2.6.1.1. Higher latency than Redirect option but simpler client side firewall config as only outbound port 1433 needs to be opened up
3.2.6.2. Redirect involves client initially contacting the service gateway and the gateway giving the client a specific port in range of 11000 to 11999 for communicating directly with SQL Managed Instance
3.2.6.2.1. Lower latency than Proxy but requires whole range of ports to be open
3.3. When provisioning SQL Managed Instance, we have an option to create a public end-point for it
3.3.1. A public end-point will allow connectivity to Managed Instance from a non-VNet source
3.3.1.1. Note: I don't believe that on-prem connectivity requires a public endpoint as long as the requisite VPN or ExpressRoute configuration is in place and the VNet Peering is configured to allow the on-prem traffic to pass through to the dedicated Managed Instance VNet/subnet
3.3.2. Port 3342 will need to be opened manually by configuring a rule in the NSG linked to our Managed Instance subnet
3.3.2.1. See attached for example of adding the inbound rule to NSG for public endpoints
3.3.2.2. Note that the priority needs to be set to a value higher than any of the deny rules already present
4. Backup and Restore
4.1. Automated backups
4.1.1. All databases automatically backed up
4.1.2. All backups encrypted with TDE
4.1.3. Full, differential and transaction log backups made
4.1.4. Long-term retention policies available
4.1.5. Backups are compressed
4.1.6. Frequency:
4.1.6.1. All managed by the service (not user controllable)
4.1.6.2. Full backup after initial database creation
4.1.6.3. Full backups made weekly
4.1.6.4. Multiple daily differential backups every few hours
4.1.6.5. Transaction log backups every 5-10 minutes
4.1.7. Retention:
4.1.7.1. User controllable
4.1.7.2. Retention default is 7 days
4.1.7.2.1. We can configure up to 35 days
4.1.7.3. Increasing retention costs more in storage
4.1.7.4. Reducing retention results in backups being lost
4.2. Restoring
4.2.1. You can restore a database backup to the same Managed Instance using Azure Portal
4.2.1.1. T-SQL cannot be used to restore automated backups
4.2.2. If you want to restore a database backup to another Managed Instance, you can do this using PowerShell
4.2.3. Deleted databases can be recovered using PowerShell
4.2.4. The Azure Portal shows the earliest available restore point
4.3. Manual backup & restore using .bak files
4.3.1. Backup and restore is done using T-SQL in SSMS
4.3.1.1. Cannot be done using Azure Portal
4.3.2. T-SQL backups must use the COPY_ONLY option
4.3.3. T-SQL backup target will be URL, pointing to Azure Blob Storage
4.3.3.1. To access this, you will need a Shared Access Signature
4.3.4. Note: if you create a .bak file using T-SQL backup on Managed Instance, this file CANNOT be restored to on-prem SQL Server
4.3.4.1. However, you can go the other direction and restore .bak files created from on-prem SQL Server to Managed Instance
4.3.4.1.1. Any .bak file based on SQL Server 2005+ is supported
4.3.5. Before making a .bak T-SQL backup in Managed Instance, you must ensure that TDE is either disabled or you are using BYOK (Bring Your Own Key)
4.3.5.1. It won't be restorable if you make the backup from a database with managed TDE enabled
4.3.6. Multiple log files must be consolidated into one before making a T-SQL backup
4.3.7. Example: restore a small on-prem SQL Server database to Azure SQL Managed Instance
4.3.7.1. Upload .bak file to an appropriate Blob container using Azure Storage Explorer
4.3.7.2. Use Azure Storage Explorer to get Shared Access Signature (SAS)
4.3.7.2.1. Set the SAS options to include Read + Write and click Create
4.3.7.3. Run T-SQL to create credential
4.3.7.3.1. You have to add Azure storage account name, container name and SAS secret into the T-SQL
4.3.7.3.2. After creating credential, you can check it has been added in sys.credentials
4.3.7.4. Run T-SQL to check that .bak file is accessible
4.3.7.4.1. You have to add Azure storage account, container name and .bak file name into the T-SQL
4.3.7.5. Run T-SQL to restore database from .bak file in Azure storage
4.3.7.5.1. You have to add Azure storage account, container name and .bak file name into the T-SQL
4.3.8. Example: backup a database from Azure SQL Managed Instance to Azure blob storage
4.3.8.1. As with restore, you'll need get the SAS secret via Azure Storage Explorer
4.3.8.2. You'll also need to make sure the credential is created and bound to the SAS secret credential
4.3.8.3. Run T-SQL to create backup
4.3.8.3.1. You have to add database name, Azure storage account, container name and .bak file name into the T-SQL
4.3.8.3.2. Note that the WITH COPY_ONLY option for the BACKUP DATABASE statement is essential
5. Reasons to use
5.1. More than 90% compatibility with on-prem SQL Server
5.2. Very popular first step from on-prem to the cloud for companies used to SQL Server on-prem but nervous about the loss of features when going to cloud
5.3. Built on same technology as Azure SQL Database but gives you many more features that you are used to using on-prem
6. Features
6.1. PaaS built in features include 3 replicas for high availability, automated backups with point-in-time restore and automatic database tuning
6.2. Managed Instance features (missing from Azure SQL Database) include change data capture, database mail, SQL Agent , cross database queries
7. Provisioning SQL Managed Instance
7.1. Remember that you need the network pre-requisites set up before starting this process
7.2. Noteworthy deployment info
7.2.1. Managed Instance cannot be moved post deployment
7.2.1.1. It's tied to the particular VNet subnet that you select during deployment
7.2.2. Deployment time is very lengthy
7.2.2.1. Typically 3-4 hours
7.2.2.1.1. The first ever time I deployed a SQL Managed Instance it took 3h3m to complete
7.2.2.2. After initial deployment it creates a new VM cluster, which reduces time to add new managed instances, but even then it's typically 1-2 hours to deploy
7.2.3. It's always running with no means to pause, so billing is continuous
7.2.4. Backup retention is managed per managed database (which get added to managed instances post deployment)
7.2.5. Max number of SQL managed instances you can provision for a regular "Pay-as-you-go" subscription is 3, and if you have an Enterprise Agreement, the max is 8
7.2.5.1. You can reach out directly to Microsoft for potentially extending these limits
7.2.6. If you want to set up a failover cluster, you need to provision the secondary first (which is an option under "Additional settings") and then when you provision your primary, there will be an option to pair it with an existing secondary
7.3. When you enable the public endpoint, you will need to manually add an inbound TCP port allow rule for port 3342 on the NSG tied to your Managed Instance VNet
7.3.1. In order to make the connection to managed instance from SSMS running outside of Azure (e.g. my home laptop), you need to copy the public endpoint address from Azure Portal
7.3.1.1. Paste in the public endpoint address in the SSMS connection dialog and append port 3342 with a comma separator
7.3.1.1.1. i.e. <public_endpoint_address>,3342
7.4. Once our managed instance is provisioned, we can scale up or down via the Azure Portal
7.4.1. Scaling happens in a more or less seamless way via a background process
7.4.1.1. What happens is that managed instance runs on a cluster and a separate node is re-provisioned with the new scaled up/down config and then a failover process occurs automatically once it's ready
7.4.1.1.1. There can be a small blip in client connectivity during the failover process
7.4.1.1.2. Note that SQL Managed Instance always runs on a cluster even when you only provision a single instance as a primary, which is because it needs a couple of secondaries in order to manage the automated backups that are part of the service
7.4.2. Pricing impact of our scaling decisions is visible here too
7.5. To connect to SQL Managed Instance via its private endpoint, we can do so via a VM in a separate subnet of the same VNet that hosts the dedicated Managed Instance subnet
7.5.1. In my case I provisioned an off-the-shelf VM named Data Science Virtual Machine
7.5.1.1. This VM includes a bunch of pre-installed tools, including SSMS
7.5.1.2. Once you make a remote desktop connection to the VM, you are ready to make an SSMS connection to the Managed Instance private endpoint
7.5.1.2.1. Firstly, copy the private endpoint address in Azure Portal
7.5.1.2.2. Make the connect via SSMS running on your VM inside a subnet that belongs to same VNet as Managed Instance subnet
7.6. After a database is added, either via a CREATE DATABASE operation or via a RESTORE DATABASE, those databases appear in Azure Portal under Managed databases
7.6.1. Like SQL databases are linked to SQL servers (for Azure SQL Database), Managed databases are linked to SQL managed instances (for SQL Managed Instance)
7.6.1.1. Unlike SQL Managed Instance, SQL servers are just logical containers for Azure SQL Databases and do not attract any costs on their own
7.7. Deprovisioning
7.7.1. When you want to deprovision Azure SQL Managed Instance, you should delete the individual managed instances first
7.7.1.1. If you delete the resource group that holds the managed instance(s) before deleting the managed instances themselves this can mean the cluster that backs the managed instance service will remain for 12 hours
7.7.2. Sequence for faster deletion:
7.7.2.1. 1. Delete each SQL Managed Instance
7.7.2.2. 2. Delete the virtual cluster
7.7.2.3. 3. Delete the resource group that contained the managed instance(s)
8. Database Migration
8.1. Microsoft has a site dedicated to the data migration process as you plan your journey from on-prem to to Azure cloud
8.1.1. It provides assistance with the whole journey from discovery and assessment, through to migration, cutover, etc.
8.2. SQL Managed Instance is especially attractive for customers that want to make a reasonably pain free transition from on-prem SQL Server to PaaS
8.3. Database Migration Tools
8.3.1. Export to BACPAC
8.3.1.1. Only suitable for small databases (e.g. 2GB)
8.3.2. Azure Database Migration Service
8.3.2.1. Builds on info provided by DMA
8.3.2.2. Supports online or offline migration
8.3.2.3. Create the service in same region as Managed Instance target
8.3.2.4. Requires modification to Managed Instance subnet NSG
8.3.2.5. Supports SQL Server, RDS and Oracle as sources
8.3.3. Data Migration Assistant (DMA)
8.3.3.1. Perform assessments
8.3.3.1.1. Identifies blocking issues, unsupported/partially supported features, suggested workarounds, etc.
8.3.3.2. Migration option steers you to Azure Database Migration Service
8.3.3.3. The DMA is a tool we download
8.3.4. Manual schema and data copy
8.3.5. Backup and restore
8.3.6. Transactional replication
8.3.7. Third party tools
8.4. Example: migration of ArgentoTraining (16MB) database from local SQL Server to Azure SQL Managed Instance
8.4.1. Run DMA assessment
8.4.1.1. Ideally there will be no compatibility issues with migrating from on-prem SQL Server to Azure SQL Managed Instance, but you will need to look into any issues and plan workarounds if reqired
8.4.1.2. Check SQL Server feature parity results for server level issues
8.4.1.3. Check Compatibility issues for database level
8.4.2. Although the DMA supports creating Migration projects as well as Assessments, it recommends for us to use the free Azure SQL Database Migration Service
8.4.3. Go to Azure Portal to provision a new Data Migration Service (DMS)
8.4.3.1. DMS Basics
8.4.3.1.1. DMS Networking
8.4.4. Once provisioned, create a new migration project
8.4.4.1. Configure DMS project, starting with source (on-prem SQL Server)
8.4.4.1.1. At this point I realised I could not progress because there is no VPN or ExpressRoute configuration to connect my home laptop (hosting SQL Server) to my Azure subscription, so entering a source server domain name or IP address would not work as it would not be reachable by the DMS