Optimizing Your Cloud Architecture for Azure SQL Managed Instance (Part 1)
Note: Special thanks to Navisite leaders John Rudenauer and Balaji Sundara for their continued support on this blog series.
Overview
This is part one of our application modernization blog series, dealing with optimum architecture constructs when utilizing SQL Managed Instance (SQL MI). Application modernization is a journey consisting of various stages – rehosting, refactoring, rebuilding and re-architecting. In this first blog, we design the architecture for application modernization and walk through first stage where we rehost and refactor the SQL Server to Azure PaaS-based offering using SQL Managed Instance.
Managed Instance is a deployment option of Azure SQL Database, providing near 100% compatibility with the latest SQL Server on-premises (Enterprise Edition) Database Engine, a native virtual network (VNet) implementation that addresses common security concerns, and a business model favorable for on-premises SQL Server customers with 2008 R2 which will no longer be supported by Microsoft. The Managed Instance deployment model allows existing SQL Server customers to lift and shift their on-premises applications to the cloud with minimal application and database changes.
Azure SQL Managed Instance Architecture in Azure
Throughout this blog we will reference this architecture. When it comes to SQL Managed Instance there are three connectivity options between on-premises and Azure – ExpressRoute, VPN and P2S VPN (more suited for POC/demos).
Jumpstart the build of the environment using an Azure ARM template.The subnets in green box (management and Managed Instance subnet) in the design are deployed using the ARM template:
https://github.com/Azure/azure-quickstart-templates/tree/master/201-sqlmi-new-vnet-w-jumpbox
From the Azure Portal -> Create a Resource -> Template Deployment
Validations in Azure
Validate the VNet/Subnet design
Once the deployment, validate the routing (UDR) and security (NSG) configuration
Verify the route-table
Verify the NSG
Verify VPN Connectivity Between On-Prem and Azure
Establish VPN connectivity between on-premises networks and Azure VNets.
On-Premises
PS C:\> Get-NetIPAddress | ? AddressFamily -eq IPv4 | FT –AutoSize ifIndex IPAddress PrefixLength PrefixOrigin SuffixOrigin AddressState PolicyStore ------- --------- ------------ ------------ ------------ ------------ ----------- 25 192.168.101.100 24 Dhcp Dhcp Preferred ActiveStore 4 10.10.8.2 24 Manual Manual Preferred ActiveStore 1 127.0.0.1 8 WellKnown WellKnown Preferred ActiveStore <strong>Ping the Jump Server</strong> PS C:\> tnc 10.2.2.4 -port 3389 ComputerName : 10.2.2.4 RemoteAddress : 10.2.2.4 RemotePort : 3389 InterfaceAlias : Ethernet1 SourceAddress : 192.168.101.100 TcpTestSucceeded : True <strong>Ping the SQL Managed Instance</strong> PS C:\> tnc 10.2.1.254 -port 1433 ComputerName : 10.2.1.254 RemoteAddress : 10.2.1.254 RemotePort : 1433 InterfaceAlias : Ethernet1 SourceAddress : 192.168.101.100 TcpTestSucceeded : True
Azure Side
From the Jump Server PS C:\Users\demo> Get-NetIPAddress | ? AddressFamily -eq IPv4 | FT –AutoSize ifIndex IPAddress PrefixLength PrefixOrigin SuffixOrigin AddressState PolicyStore ------- --------- ------------ ------------ ------------ ------------ ----------- 3 10.2.2.4 24 Dhcp Dhcp Preferred ActiveStore 1 127.0.0.1 8 WellKnown WellKnown Preferred ActiveStore PS C:\Users\demo> tnc 192.168.101.100 -port 1433 ComputerName : 192.168.101.100 RemoteAddress : 192.168.101.100 RemotePort : 1433 InterfaceAlias : Ethernet SourceAddress : 10.2.2.4 TcpTestSucceeded : True PS C:\Users\demo> nslookup demo99-sqlmi.7160fb2415ca.database.windows.net Server: UnKnown Address: 168.63.129.16 Non-authoritative answer: Name: tr1144.westus1-a.worker.vnet.database.windows.net Address: 10.2.1.254 Aliases: demo99-sqlmi.7160fb2415ca.database.windows.net PS C:\Users\demo> tnc demo99-sqlmi.7160fb2415ca.database.windows.net -port 1433 ComputerName : demo99-sqlmi.7160fb2415ca.database.windows.net RemoteAddress : 10.2.1.254 RemotePort : 1433 InterfaceAlias : Ethernet SourceAddress : 10.2.2.4 TcpTestSucceeded : True
Migration Tools: Data Migration Assistant (DMA)
The Data Migration Assistant (DMA) in Azure helps you upgrade to a modern data platform by detecting compatibility issues that can impact database functionality in your new version of SQL Server or Azure SQL Database. DMA recommends performance and reliability improvements for your target environment and allows you to move your schema, data, and uncontained objects from your source server to your target server.
Microsoft Documentation
https://docs.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-2017
Download and install the DMA tool using the following on the On-Prem Server
https://www.microsoft.com/download/details.aspx?id=53595
Sample Assessment Flow
Step1: Pick the Source and Target Servers
2. Establish connectivity to the SQL Server
3. Select the databases
4. Run the assessment
5. Review the assessments
Manual Backup to Azure Storage Account
From on-prem server, backup the database to Azure Storage Account Using SSMS (SQL Server Management Studio) .
Create an Azure Storage Account
Note the Access keys
Note the Blob service endpoint : https://demo99sa.blob.core.windows.net/
Using SSMS, create a backup of the database into the Azure blob storage
drop CREDENTIAL Backupcredential CREATE CREDENTIAL Backupcredential WITH IDENTITY= 'demo99sa' , SECRET = 'QiJyDj11k7ndzToZ82demVgMXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX+L+dM/K4qAoIw==' BACKUP DATABASE [DotNetTodo] TO URL = 'https://demo99sa.blob.core.windows.net/backups/DotNetTodo.bak' WITH CREDENTIAL = 'Backupcredential', STATS = 10 GO
Verify the backup in Azure
Manual Restore to SQL Managed Instance
Login to the jump server in Azure and connect to the newly created Azure SQL managed instance using SSMS.
Generate the shared access signature and note the SAS token
Restore the database to SQL MI
CREATE CREDENTIAL [https://demo99sa.blob.core.windows.net/backups]
WITH IDENTITY = ‘SHARED ACCESS SIGNATURE’
, SECRET = ‘sv=2018-03-28&ss=bfqt&srt=sco&sp=rwdlacup&se=2019-06-16T01:37:49Z&st=2019-05-15T17:37:49Z&spr=https&sig=uaaNfg%2BQabsuAEvS4exGTquhd1pAKaxZiW%2BggT7VxOQ%3D’
RESTORE FILELISTONLY FROM URL =
‘https://demo99sa.blob.core.windows.net/backups/DotNetTodo.bak’
RESTORE DATABASE [DotNetTodo] FROM URL =
‘https://demo99sa.blob.core.windows.net/backups/DotNetTodo.bak’
.
Configure the Application to Point to Azure SQL managed Instance
Update the web.config file and reset IIS:
Add a couple of rows to the todo table with SSMS in Azure
PS C:\> iisreset /noforce
Attempting stop…
Internet services successfully stopped
Attempting start…
Internet services successfully restarted
PS C:\> netstat -an | findstr 1433
TCP 192.168.101.100:1433 0.0.0.0:0 LISTENING
TCP 192.168.101.100:63215 10.2.1.254:1433 ESTABLISHED
TCP 192.168.101.100:63216 10.2.1.254:1433 ESTABLISHED
Validate the new changes by launching the application again.
Now the application is live with Azure SQL Managed Instance!!
Summary
In summary we set the framework for application modernization, used Azure Data Migration Assistant (DMA) to assess the database for feature incompatibility and did a quick test of backup/restore of database from SQL Server to Azure SQL managed instance. Coming up next in the next blog is a fully-automated Azure Database Migrated service (DMS) tool.
The Azure Database Migration Service provides clients with a comprehensive, highly-available, near-zero downtime solution. The service uses the Data Migration Assistant to generate assessment reports that provide recommendations to guide you through the changes required prior to performing a migration. End of Support (EOS) for SQL Server 2008 is imminent.
Navisite’s Professional Services can help customers plan out the optimal way to get to Azure and assist in transitioning your SQL databases to SQL MI, and our Azure Management Services can help ensure your success in your overall Azure cloud journey. Contact us today for further information, or call us at (800) 298-8222 in the US or 0800 6122933 in the UK for more information.