Azure SQL Managed Instance

Azure SQL managed instance is a relatively new Azure offering. It has nearly 100% feature compatibility with the latest on-premises SQL Server Enterprise Edition Database Engine and allows relatively easy lift and shift of on-premises applications to the cloud with minimal application and database changes. At the same time, Platform as a service capabilities such as automatic patching, version updates, backups, and high-availability drastically reduce required management overhead and total cost of ownership.

For customers running older versions of SQL Server, I'm happy to report that the Azure SQL managed instance supports backward compatibility to SQL 2008 databases. It supports direct migration from SQL 2005 database servers, however migrated SQL 2005 databases will have their compatibility level updated to SQL 2008.

A great benefit of choosing the managed instance deployment option is that it will always be up-to-date because patches and upgrades are automatically applied. Also, you don't have to purchase, manage or secure physical hardware.

Managed instance has High-availability built in and pre-configured using a technology similar to Always On Availability Groups, providing 99.99% uptime SLA. It also has automated backups and point in time restore.

With the managed instance you will spend less time on administrative tasks because the service either performs these tasks automatically for you or greatly simplifies them.

It supports Azure AD authentication as cloud alternative to Windows authentication, automatically manages XTP filegroup and files for databases containing In-Memory OLTP objects and supports SQL Server Integration Services (SSIS) and can host the SSIS catalog (SSISDB) that stores the packages. SSIS packages must be executed on a managed Azure-SSIS Integration Runtime (IR) in Azure Data Factory (ADF). Unlike Azure SQL Database, it allows cross-database queries.

There are two service tiers for Managed Instance: General Purpose and Business Critical.

General purpose: Designed for applications with typical performance and IO latency requirements.
Business critical: Designed for applications with low IO latency requirements and minimal impact of underlying maintenance operations on the workload.

Server sizes range from 8 to 80 vCores depending upon service tier. Memory is 7 GB per vCore.
Maximum storage is 8 TB for general purpose and up to 4 TB for Business Critical

NEW Stuff in Azure SQL Managed Instance!

As of 11 April 2019, managed instance now has a public endpoint. (YAY!)
Here's a link to the official Microsoft article on it - with full instructions for implementing it via Powershell

Azure Data Studio - now with Notebooks

Azure Data Studio is a light-weight cross-platform database tool based upon Visual Studio Code. It runs on Windows, Mac, and Linux. It includes a SQL editor with IntelliSense, keyword completion, code snippets, code navigation and Git source control integration. Although SQL Server Management Studio still remains the champion for database administration tasks, I believe that for developers and Mac and Linux users, Azure Data Studio is the best choice. I also like the fact that Azure Data Studio uses far fewer resources on my machine than SSMS.

As of March 2019, Azure Data Studio connects to:

• SQL server 2014+
• Azure SQL database
• Azure SQL Data Warehouse
• Azure SQL Managed Instance
• PostgreSQL servers 

One of the newest and I think more notable features that has been added to Azure Data is its ability to create a SQL notebook.

Notebooks are type of document that combine formatted text with live code elements, images, and query results. You may have heard of them as "Python Jupyter Notebooks". Well, now they can be used with SQL in addition to other programming languages such as Python, Scala, and R.

To create a notebook, simply open Azure Data Studio, then select New Notebook from the file menu. Add a code type cell (or section), check that the Kernel is "SQL", set the connection to your favorite database, enter some SQL code and execute it.

The IntelliSense in Azure Data Studio simplifies code entry, and the right-click menu offers some excellent options, including formatting the document, changing all occurrences of the selected word, and taking a "Peek" at the definition of the selected SQL object (table or stored procedure name).

Just think of all the possibilities!

These notebooks will be great for technical presentations. I can prepare formatted text to describe the concepts presented in my session, and include executable code within the context of the document which the attendees can run at home on their own systems after they download the demos.

Another great scenario would be for troubleshooting or baselining a server. Because Notebooks allow you to save the query results inside the file, you can run diagnostic queries and save the results all in one neat package. Then, later on when you wish to compare to the original baseline, make a copy of the notebook, clear the results and re-run all the queries to see the differences.

For problem solving,  you could create a Notebook containing instructions and a series of queries, ask your client to run the queries and return it to you with the results populated.

I think these new features really make Azure Data Studio outshine old SSMS. I may not remove SSMS from my machine just yet, but I'm pretty certain it's not going to see very much use in the future.

I'm Speaking at Phoenix SQL Saturday!

Just a quick post to let you know that I'll be speaking at the Phoenix SQL Saturday on May 4, 2019.

SQLSaturday is a free training event for Microsoft Data Platform professionals and those wanting to learn about SQL Server, Business Intelligence and Analytics. This event will be held on May 04 2019 at Chandler-Gilbert Community College Pecos Campus, 2626 East Pecos Road, Chandler, Arizona, 85225, United States

Hope to see you there!!

---

Shoe lovers: I'm a DSW ambassador, so please use this link when going to DSW Shoes

---

Meet Azure Key Vault

What is Azure Key Vault? Azure Key Vault is a cloud hosted service offering secure storage and access for certificates, encryption keys, connection strings and other secrets. It streamlines the key management process and provides full control of keys for accessing and encrypting your data. Administrators can grant and revoke access to keys as needed. Key vaults also control the access to anything stored within them.

Applications and Azure resources authenticate to Key Vault to retrieve the secrets. The best authentication method for Azure services is to use a managed identity because it allows Azure services to authenticate to Key Vault or any service that supports Azure AD authentication, without having to include credentials in your code. With Azure Key Vault, applications never have direct access to keys.

Administrators can monitor and audit key use with Azure logging.

Key vault allows the separation of security maintenance from application development. For example, Key Vault secrets can be used to store connection strings for various resources accessed by ADF. Those connection strings can be updated by Administrators without affecting the ADF pipelines or having to send the new passwords out to developers.

It only takes a minute or two to create a Key Vault. In the Azure Portal, click on the Create a Resource, search for Key Vault, then click create.

Next, provide a name for the Key Vault, select subscription, resource group and location. Finally, select pricing tier.  Refer to https://azure.microsoft.com/en-us/pricing/details/key-vault/ for details on current pricing. Most users will select standard tier unless their security policy requires the premium features.

Once the key vault has been created keys, secrets, and certificates may be imported or manually created.

To create a secret, click on "Secrets", then Generate/Import.

This will open the "create a secret" dialog. Choose a name for the secret, such as dev-Adventureworks-Connection for the connection string for Adventureworks database in Development environment. Enter the full connection string in the Value field, write a brief description of the secret in the content type box and click "Create" to complete the creation process.

Once you have created your key vault and some secrets or keys, they may be used in any Azure resource which has been granted access to the Azure Key Vault. That will be covered in another post.