BUSINESS INSIGHTS

Jun 21, 2018

Upgrade Your Data Warehouse for the Final Time

Leo Furlong Posted by Leo Furlong

It’s that time again. It’s been two years and you’re starting to plan the dreaded Data Warehouse upgrade to SQL Server version next. You’re wondering if you can recycle that old project plan from two years ago, but your data volume has grown and the performance of your ETL isn’t what it used to be. You know you need to do some analysis of your hardware first to see if you need more horsepower. You’ve also been keeping up with the latest new features and know there are some you’re wanting to take advantage of. You think, how in the world are we going to migrate and verify the 200 SSIS packages that we’re up to now? All of this on top of an already demanding meeting schedule, new user requirements, and support requests. The stress and anxiety are starting to kick in.

Do you ever catch yourself:

  • Wishing you could magically upgrade your SQL Server every two years by snapping your fingers?
  • Wishing you didn’t have to think about hardware, ever?
  • Wondering if your hardware is causing your performance problems but don’t have the time, bandwidth, or access to prove it?
  • Dreading the weekend maintenance outages where IT is patching the Windows OS or installing a SQL Server Service Pack or Cumulative Update?
  • Daydreaming about the latest SQL Server feature, knowing you could benefit from it, but then sighing, because you know it will be more than year before you see it?
  • Analyzing your SQL Server options in the cloud and becoming disappointed with what you find?

Load your plutonium and turn the flux capacitor and time circuits on, because you’re heading to the future.

upgrade data warehouseOn March 7th of this year, Microsoft announced the public preview of Azure SQL Database Managed Instance (Managed Instance for short). Managed Instance is a new Azure SQL Database offering that is managed at the SQL Server Instance level in the Azure Platform as a Service cloud. Unlike Azure SQL Databases that consist of a single SQL Server database or Elastic Pool, Managed Instance comes as an entire instance of SQL Server just like on-premise SQL Server 2017. Unlike other Azure SQL Server products that might require some redesign of your solution, migration to Managed Instance is easy because it is almost 100% like SQL Server on-premise. With this service, you get:

  • Infrastructure and OS managed by Azure
  • Database patching and upgrades managed by Azure
  • Ability to scale up and down to meet performance expectations
  • The latest version of SQL Server in perpetuity
  • SQL Server Relational Database features like:
    • Integrated security
    • Common Language Runtime (CLR)
    • Global Temp Tables
    • Cross-database queries
    • Linked servers
    • Service Broker/Query Notifications
    • Native BACKUP/RESTORE statements
    • Database mail
    • SQL Agent
    • The latest security capabilities
    • And a multitude of other supported features
  • High Availability and Disaster Recovery are built in and managed by Azure
  • Some minor T-SQL differences exist, but they aren’t show stoppers

Performance Architecture

Selecting an architecture with the required performance is straightforward with Managed Instance. The architecture has four dials you can configure to obtain your optimal solution and pricing.

  • General Purpose or Business Critical: do you want budget-oriented, balanced architecture (General Purpose), or do you have high IO requirements coupled with more complex HA/DR requirements (Business Critical)? During the Public Preview phase, Business Critical is still privately reserved.
  • Pick your Processor Type: Generation 4 uses Intel E5-2673 v3 (Haswell) 2.4 GHz processors, attached SSDs, and a vCore that is equal to 1 physical core. Generation 5 uses Intel E5-2673 v4 (Broadwell) 2.3 GHz processors, fast NVMe SSDs, and a vCore that is equal to 1 logical processor.
  • Number of vCores: on Generation 4 processors, you can select 8, 16, or 24 vCores. On Generation 5 processors, you can select 8, 16, 24, 32, or 40 vCores. Generation 4 processors come with 7GB of RAM per vCore, and Generation 5 processors come with 5.5GB of RAM per vCore.
  • Storage Size: picking your storage size is as easy as moving a slider. Managed Instance is currently limited to 8TB of storage while in Public Preview.

A more thorough explanation of the architecture and vCore selection options can be found here.

Security and Networking

Managed Instance is designed with security and networking in mind. Managed Instance is deployed specifically for use through Azure Virtual Networks (VNET) and connected to on-premise via Azure Express Route or Azure VPN Gateways. Connections to Managed Instance come through a safe and private IP address on the VNET. Managed Instance also provides a single tenant experience with dedicated resources for compute and storage, combined with all the latest and greatest security enhancements with the latest version of SQL Server.

More information on security and networking for Managed Instance can be found here.

Migration

Migration to Managed Instance is easy. Because Managed Instance is so much like SQL Server, there is no need to rewrite your code. Additionally, Azure makes it easy to migrate, providing three different options.

  • Azure Database Migration Service – a fully managed service for migrating on-premise SQL Server databases (or Azure VMs) to Azure using a GUI/wizard-like experience and providing zero downtime.
  • Backup to Azure Blob and Restore – backup your current SQL Server databases to Azure Blob storage. Restore to Managed Instance by restoring your backups from Blob storage.
  • Using BACPAC file – provides the capability to create the entire database and data into a single deployable file.

More information can be found on migration here.

Closing

Managed Instance provides all the great features and capabilities of on-premise SQL Server without the hassle of managing infrastructure, configurations, or patching the OS and SQL Server. It also allows you to skip the bi-annual SQL Server upgrade because you’ll always be on the latest version and have access to the latest SQL Server features. For more information about Managed Instance, contact BlueGranite today.

Warning: please know that you might get sad or reminisce about
past SQL Server migration projects – said no one ever.

Free eBook Download
Leo Furlong

About The Author

Leo Furlong

Leo is a Solution Architect at BlueGranite. He is an experienced Data and Analytics Architect with extensive experience implementing Digital Transformation Strategy, using on-premise and Azure cloud technologies. Leo is also comfortable in Management Consulting roles around Data and Analytics or leading and contributing toward deliverables across the entire project lifecycle.

Latest Posts

New Call-to-action