Skip to main content

Managed Instances - Closing the gap between Azure SQL Database and Azure VM with SQL Server

In one of my previous post, I talked about different flavors of SQL inside Azure. I saw that many people were interested about Managed Instances.
In this post, I will present Managed Instances services, that are still in preview, but it might be a game changer for companies that needs to do a migration from on-premises to Azure and they have compatibility problems with Azure SQL Databases.

For this kind of scenario, the only options used to be Azure VMs, that is in the end IaaS and require extra effort to manage the VM and SQL Server cluster that is running on top of it. In one word you cannot put your database on auto-pilot and focusing on your business and not on the infrastructure.
The current flavors that are available on the market when you put SQL and Azure together are:

  • Azure SQL Databases
  • Elastic Pool
  • Azure VMs
  • … Managed Instance

Managed Instance is a new service inside Azure that is allowing us a one-to-one migration from on-premises to Azure, without worrying about many compatibilities issues that you might have when you want to do such a change.

Things like SQL Agent, Profiler, cross DB querying, CDC, CLR and service broker are supported inside this new service. I’m avoid to put the full list of all features, just remember that almost all the features available inside Azure SQL Database and Server are available also inside Managed Instances.
The compatibility between SQL Server and Managed Instances is not 100%, there are some small things that we need to be aware, but it is much better than the one that we used to have between SQL Server and Azure SQL Databases. It's more closer to 100%, but still might create issues for some edge scenarios.

New features
Features like dynamic scaling (based on our needs), point in time restoration, geo-redundancy and geo-replication are built in, together with a 99.99% SLA where automatic failover makes this service attractive to any big organization.
An interesting option that Managed Instances has is the upgrades and updates policy. It is allowing user administrators to control the role-out of it. In comparison with Azure SQL Database, where the user has no control on any updates that are happening behind the scene to the service.
The most important features related to security in my perspective are:

  • Private IP address
  • VNET native support

These two new features combined gives us the possibility to add the Managed Instance service to our private VNET, without being accessible from other Azure Services or from public internet.
Migration from SQL Server to Managed Instances is happening at SQL Server level not at database level, enabling us to migrate all our databases in one step.

The support of global temp tables together with cross database queries gives us the possibility to do things in the same way that we would do on-premises. The new CLR support enable us to run .NET native code inside Managed Instances, that was not possible inside Azure SQL Databases.

Migration capabilities 
The maximum size of a database is 35TB with the support of doing a migration with a minimum downtime (DMS support). The current migration solution that is coming with Managed Instances enable us to do a live migration without having to put the database offline, being able to do live synchronization between on-premises and target storage.
The backward compatibility between SQL Server and Managed Instances starts with SQL Server 2005 and ends with the latest version of SQL.

What we don’t have? (yet)
First of all modules like SSIS, SSRS and SSAS are not supported inside Managed Instances and needs to be deploy separately. Below you can find some alternatives to each of them:

  • SSRS – Can be deployed to an Azure VM or it can migrated to PowerBI
  • OLAP – Could run inside Azure Analysis Services 
  • SSIS – Can run inside Azure Data Factory

As we already seen, the capacities of Managed Instances are different from the one offered be Azure SQL Databases. This happens because we are a little more closer to the hardware in comparison with Azure SQL Server.
Taking this into account the pricing model is different. Even if it is not public available yet, we should keep in mind that we will be charted in a similar way as we are when we would use VMs. The service tiers that will be defined might be defined around:

  • vCore
  • Database size
  • IOs operations
  • Backups size
This new service is closing a gap between Azure SQL Server and Azure VM+SQL Server, especially for legacy and heavy systems, where you need more control of the platform.


Popular posts from this blog

ADO.NET provider with invariant name 'System.Data.SqlClient' could not be loaded

Today blog post will be started with the following error when running DB tests on the CI machine:
threw exception: System.InvalidOperationException: The Entity Framework provider type 'System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer' registered in the application config file for the ADO.NET provider with invariant name 'System.Data.SqlClient' could not be loaded. Make sure that the assembly-qualified name is used and that the assembly is available to the running application. See for more information. at System.Data.Entity.Infrastructure.DependencyResolution.ProviderServicesFactory.GetInstance(String providerTypeName, String providerInvariantName) This error happened only on the Continuous Integration machine. On the devs machines, everything has fine. The classic problem – on my machine it’s working. The CI has the following configuration:

TeamCity.NET 4.51EF 6.0.2VS2013
It seems that there …

Entity Framework (EF) TransactionScope vs Database.BeginTransaction

In today blog post we will talk a little about a new feature that is available on EF6+ related to Transactions.
Until now, when we had to use transaction we used ‘TransactionScope’. It works great and I would say that is something that is now in our blood.
using (var scope = new TransactionScope(TransactionScopeOption.Required)) { using (SqlConnection conn = new SqlConnection("...")) { conn.Open(); SqlCommand sqlCommand = new SqlCommand(); sqlCommand.Connection = conn; sqlCommand.CommandText = ... sqlCommand.ExecuteNonQuery(); ... } scope.Complete(); } Starting with EF6.0 we have a new way to work with transactions. The new approach is based on Database.BeginTransaction(), Database.Rollback(), Database.Commit(). Yes, no more TransactionScope.
In the followi…

GET call of REST API that contains '/'-slash character in the value of a parameter

Let’s assume that we have the following scenario: I have a public HTTP endpoint and I need to post some content using GET command. One of the parameters contains special characters like “\” and “/”. If the endpoint is an ApiController than you may have problems if you encode the parameter using the http encoder.
using (var httpClient = new HttpClient()) { httpClient.BaseAddress = baseUrl; Task<HttpResponseMessage> response = httpClient.GetAsync(string.Format("api/foo/{0}", "qwert/qwerqwer"))); response.Wait(); response.Result.EnsureSuccessStatusCode(); } One possible solution would be to encode the query parameter using UrlTokenEncode method of HttpServerUtility class and GetBytes method ofUTF8. In this way you would get the array of bytes of the parameter and encode them as a url token.
The following code show to you how you could write the encode and decode methods.