Andy Mallon (t|b) and I recently spoke about Managed Instance in our webinar Azure SQL DB Managed Instance: A New Path to the Cloud. Due to the number of great questions we received during the webinar, I have decided to do a quick follow-up blog post that addresses the questions and goes into a little more detail on some of the answers.
If you want to view the recording of the webinar, then you can do so here.
During the course of the webinar, there were several questions raised by attendees; I have cataloged them here.
Question: How can I check which time zone my MI is in? Also, is it possible to set time zone?
Answer: Currently, Managed Instance is configured in the UTC time zone. When you use the built-in functions such as GETDATE(), it will return UTC date/time values. There is no indication yet as to whether you will be able to set the time zone in the future.
Question: Where I can read more about backups? I need to know if it is possible to set up my own backup strategy and if there is any limitation.
Answer: Managed Instance makes use of an automatic backup process in the same way that Azure SQL Database does. It supports the ability for you to perform point-in-time recovery within configured backup timescales. You can find more information on the Azure SQL Database backup process here.
It is also important to understand that while it is possible to take a native backup from Managed Instance to Azure Blob Storage, you can only restore these to another Managed Instance. There are also several other restrictions around the type of backups that you can take, as well as the options available to you. This information can be found here.
Question: Is there a GA date for Business tier?
Answer: At the moment there is no information available from Microsoft about when we will see Business Critical tier go GA.
Question: In one of the “Managed Instance – Compared” slides, the column for Managed Instance showed a limit of up to 100 databases while the column for Azure SQL Database showed Single Database. What do you mean by that, as I am sure you can have multi-databases in a single Azure SQL logical server?
Answer: You can indeed have multiple Azure SQL Databases per-logical Azure SQL Server. However, the scope of operation is the singleton database. You can interact with multiple databases (such as with a cross-database query) via Elastic Query and Elastic Database Jobs. However, these require additional configuration and architecture changes. Managed Instance has the native cross-database support and instance-level scope that we are familiar with. This is why we presented the slide in this way.
Question: Is it possible to change the collation level on MI?
Question: So we are using now the Windows collation level Latin1_General_CI_AS; will this be the same as default collation?
Answer: Presently, Managed Instance uses a default collation of SQL_Latin1_General_CP1_CI_AS. It is currently not possible for us to alter the default collation. There is no indication at the moment that this will change. You can create a database with a non-default collation. However, be aware that this can result in collation conflicts for processes that use TempDB.
Question: Does Managed Instance have built-in tools like Azure SQL Database, such as Query Performance Insights?
Answer: Presently, the “Advanced Threat Detection” service is in place for use with Managed Instance. Other PaaS services such as QPI are either in the engine (Dynamic Data Masking) or not there, yet. Whether there will be a higher degree of parity, we are not sure, yet.
Question: So you can debug in SSMS with MI? I know that I can’t with just DB.
Answer: Presently, no, it does not appear that you can use the SSMS debugger with Managed Instance.
Question: If, for some reason, I want to migrate from MI back to on-prem, backup restore will not work, correct?
Answer: Yes, you are correct. There is no way we can restore a database backup from Managed Instance to retail SQL Server. You have two main methods presently for getting data out of Managed Instance:
- Script schema, transfer data via SSIS/ADF/BCP
- Setup Transactional Replication with the Subscriber on-premises
Note that you can incur a charge for data egress from Azure.
Question: Is there an equivalent of the SQL Server System Admin server role in MI?
Answer: Yes, Managed Instance has a Sysadmin role that has the same properties as the retail SQL Server server role.
Question: Can databases have multiple files (4TB each)?
Answer: Yes, databases in Managed Instance can have multiple files. The limitations on file size are in line with the maximum size of Azure Premium Storage (P10 to P50 currently) that can be used. Additionally, there is the soft limit that you configure in the Managed Instance around how much storage you want available. You can find more details about storage limits here.
Question: Can MI be a 2nd Always On cluster but primary on prem?
Answer: No, there is no way to use Managed Instance as part of an on-premises Always On Availability Group.
Question: Should we use SentryOne Unified Setup for connecting to Managed Instance DB?
Answer: The SentryOne installer supports the deployment of the SentryOne repository to Managed Instance. You will need to supply SQL credentials to the installer during the installation and for the service to connect once running. If you have an existing SentryOne deployment, it is possible to migrate the database to Managed Instance (Backup & Restore).
In addition to these questions, there were some other statements and dialogue around the use of custom user trace flags and support for MSDTC. Presently, there is not support for the MSDTC; whether that will change I am not sure. Likewise, where applications such as Dynamics AX need specific trace flags to be set, there is no support for setting these presently.
SentryOne has supported both monitoring and using Managed Instance since the public preview was announced, and we already have customers making use of this technology. The work that we did to support the new technology from Microsoft was built on customer feedback about what they need, from query tuning via Top SQL to understanding resource usage, deadlocks, and blocking.
If you already have SQL Sentry licenses, then these are fully transportable when you migrate a server from on-premises to Managed Instance. Once you move your databases, all you need to do is stop watching the old server and then watch the Managed Instance. So there are no hidden costs when using SentryOne to monitor your data platform as you migrate to Azure SQL Database Managed Instance.
If you are currently evaluating, migrating, or have already migrated to Managed Instance and want a database performance monitoring solution, then SentryOne can help. You can download a free 15-day trial of SQL Sentry from our website here.
