mountainss Cloud and Datacenter Management Blog

Microsoft Hybrid Cloud blogsite about Management


Leave a comment

Take a Deep Dive with this SQL Server 2017 Administration Ebook #SQL #SQL2017 #Azure #dba

Introduction

The velocity of change for the Microsoft SQL Server DBA has increased this decade. The span
between the releases of SQL Server 2016 and 2017 was only 16 months, the fastest new release
ever. Gone are the days when DBAs had between three to five years to soak in and adjust to new
features in the engine and surrounding technologies.
This book is written and edited by SQL Server experts with two goals in mind: to deliver a solid
foundational skillset for all of the topics covered in SQL Server configuration and administration,
and also to deliver awareness and functional, practical knowledge for the dramatic number
of new features introduced in SQL Server 2016 and 2017. We haven’t avoided new content—
even content that stretched the boundaries of writing deadlines with late-breaking new releases.
You will be presented with not only the “how” of new features, but also the “why” and the
“when” for their use.

Go Deep Dive with this Awesome SQL Server 2017 Ebook 😉

Download the Custom excerpt Inside Out SQL Server 2017 Administration Ebook here

Advertisements


Leave a comment

#Microsoft Azure Hub-Spoke model by Enterprise Design 3 of 4 Data Migration #Azure #SQL

Hyper-V Clusters front tier with SQL Clusters in the Backend

SQL assessment and Data Migration to Azure

This blogpost is about SQL assessment and Data Migration to your Azure design in the Cloud in a secure way.
Before you begin with your Data assessment and getting your workloads together with Microsoft Azure ServiceMaps, I wrote these blogposts about Microsoft Azure HUB – Spoke model by Enterprise Design :

  1. Microsoft Azure Hub-Spoke model by Enterprise Design 1 of 4
  2. Microsoft Azure Policy and BluePrints Overview (Extra Blogpost)
  3. Microsoft Azure Hub-Spoke model by Enterprise Design 2 of 4 “Lift and Shift”

For Microsoft SQL databases there are different Azure Solutions in the Cloud possible, but first you need to know which versions of SQL do you have and how are they running now in your Datacenter?

SQL 2014 Virtual Guest Cluster with Shared VHDX

Here you can see a totally different SQL Cluster configuration, running on Hyper-V instead of physical Server nodes like you can see in the first picture with SQL 2008 R2 Clusters.
When you have a CMDB of your SQL versions running in your Datacenter, you can compare it with these SQL versions on this Great website.

What is also important to know, in which compatibility mode is your SQL Server running? Because you can have a recent SQL version but it’s running in a old compatibility version for the application.

SQL versions with Compatibility matrix

When you have all the insights of your SQL workload on-premises like :

Then you want to know to which Microsoft Azure SQL solution will I migrate my data ?

When you do a “Lift and Shift” first to the Azure-HUB subscription for the complete workload (Virtual Machines + SQL Databases) then you can implement SQL Always-On in Azure.

SQL Always-ON Availability Group

More information about SQL Always-On in Availability Groups in Azure

Or you can migrate to Azure SQL (PaaS) directly.
Later in this blogpost you see the Options with Microsoft Azure Data Migration Assistant (DMA)

Test & Acceptance and Production Azure Spoke

When you have “Lift and Shift” your workload to the Azure-HUB landing zone, then you can do the Optimize of your solutions included SQL to the Test & Acceptance and Production Spoke. For this it’s important where and how your SQL Backend is landing in Microsoft Azure by Design.

Microsoft Azure Data Migration Assistant (DMA)

Data Migration Assistant (DMA) enables you to upgrade to a modern data platform by detecting compatibility issues that can impact database functionality on your new version of SQL Server. It recommends performance and reliability improvements for your target environment. It allows you to not only move your schema and data, but also uncontained objects from your source server to your target server.

Azure SQL Data Migration Assistant

In the following Step-by-Step Guide we will Migrate a SQL 2016 SP2 Database to a Microsoft Azure SQL Database (PaaS):

first you have to download Microsoft Azure SQL Data Migration Assistant here

Click Next.

Click Next

Click Install

Ready for Assessments and Migrations.

  1. Here you can choose between the Assessment or the Migration.
  2. Here you can Choose for your Azure Target SQL Solution :
    – Azure SQL Database
    – Azure SQL Database Managed Instance
    – SQL Server on Azure Virtual Machines
    – SQL Server

Select the options for the Assessment.

In the following steps we will migrate the SQL 2016 SP2 database to Azure SQL :

Connect to the local SQL Instance and Select your Database

Connect and select your Azure SQL Database.

Select the Schema objects to migrate into Azure SQL

Here you see the Script to Deploy Schema.

Schema migration in progress

Schema Migration is Done, now you Click on Migrate Data

Select the Tables to Migrate and click on Start data Migration

Data Migration in progress

The SQL 2016 SP2 Migration from On-premisses to Azure SQL is Successful Completed 🙂

Connected to Azure SQL Database with my Data.

The SQL Query editor is a browser query tool that provides an efficient and lightweight way to execute SQL queries on your Azure SQL Database or Azure SQL Data Warehouse without leaving the Azure portal. This quickstart demonstrates how to use the Query editor to connect to a SQL database, and then use Transact-SQL statements to query, insert, update, and delete data in the database.

Here is my Data in Azure SQL with Query Editor of the Azure Portal.

This is just one Scenario with Azure SQL Data Migration Assistant. What you have learned is that you must have your Azure SQL Solution in place by Architectural Design before you do the SQL Data Migration.

Here you find more information about Data Migration to Microsoft Azure :

Microsoft Azure Data Migration Guide

 

Here you find Microsoft Azure Migration Center

Conclusion :

Microsoft Azure Architecture design like a Hub-Spoke model for example is important to have in place before you do your Data Migration to the Azure Cloud. You got different SQL Solutions in Microsoft Azure, like SQL Always-On in availability Groups and Microsoft Azure SQL Database with or without Managed Instances. Choose for the best scenario in your own Design. My next blogpost in this Serie will be on Optimize your Azure workloads
How can you make your solution smarter, more intelligent for your business and in Azure costs cheaper with Great benefits! Here we can think out of the box to get the best 😉


Leave a comment

#Microsoft Azure Hub-Spoke model by Enterprise Design 2 of 4 Lift and Shift #Azure #Hyperv #VMware

Microsoft Azure Hybrid Cloud Architecture HUB-Spoke Model

Microsoft Azure Hub-Spoke model

This blogpost about Microsoft Azure Hub-Spoke model by Enterprise Design 2 of 4 “Lift and Shift” is part of a Datacenter transition to Microsoft Azure Intelligent Cloud. It’s talking about Azure Architecture, Security, Assessment, Azure Policy, and implementation of the design. Here you find the first blogposts :

It’s important for your business to have your Azure Architectural design with Security in place before you start your “Lift and Shift” actions, think about Identity Management and Provisioning, RBAC for your Administrators and Super Users with Two-Factor Authentication. Security with Network Security Groups and Firewalls 

Azure Multi-Factor-Authentication (MFA)

Microsoft Azure Hub-Spoke model : “Lift and Shift”

 

Microsoft Azure HUB subscription for “Lift and Shift”

To “Lift and Shift” to the Azure HUB Subscription we have the following in place by Design :

  1. Azure Scaffold and Hierarchy (Governance)
  2. Virtual Networks (VNET) with the Subnets and IP-Number plan
  3. ExpressRoute VPN Connection with a backup failover Site-2-Site VPN connection to Azure.
  4. Resource Groups, like Active Directory, ADFS Farm, Authentication, SQL Backend.
  5. Resource Policies
  6. Resource Locks
  7. Network Security Groups (NSG)
  8. DNS
  9. Azure Firewall
  10. Azure internal Load Balancers.
  11. Azure Storage Accounts
  12. Azure Virtual Machine sizes
  13. Azure Virtual Machine Image
  14. Managed Disks and Encryption.
  15. Redundancy for Virtual Machines
  16. Azure Key Vault for Encryption.
  17. Azure Recovery Vault ( Backup)
  18. Azure Policy
  19. Managed Identities, Azure MFA, RBAC,ADFS
  20. Azure Monitor
  21. Azure Naming Convention
  22. Azure Tagging
  23. Azure Cost Management
  24. ARM (JSON) Deployment template (for New requests)

To help you more with your Azure Virtual Datacenter have a look here

 

Azure Hierarchy

Azure Scaffold

When creating a building, scaffolding is used to create the basis of a structure. The scaffold guides the general outline and provides anchor points for more permanent systems to be mounted. An enterprise scaffold is the same: a set of flexible controls and Azure capabilities that provide structure to the environment, and anchors for services built on the public cloud. It provides the builders (IT and business groups) a foundation to create and attach new services keeping speed of delivery in mind. Read more hereI did the “Lift and Shift” between quotes because it’s important to follow the process workflow to be successful in your Datacenter transition to the Microsoft Azure Cloud.

 

Here you find all the Microsoft Azure Migration information

 

 

App Migration to Azure: Your options explained by Jeremy Winter

The Azure Migrate service assesses on-premises workloads for migration to Azure. The service assesses the migration suitability of on-premises machines, performs performance-based sizing, and provides cost estimations for running on-premises machines in Azure. If you’re contemplating lift-and-shift migrations, or are in the early assessment stages of migration, this service is for you. After the assessment, you can use services such as Azure Site Recovery and Azure Database Migration Service, to migrate the machines to Azure.

In your datacenter you got all kind of different workloads and solutions like :

  • Hyper-V Clusters
  • VMware Clusters
  • SQL Clusters
  • Print Clusters
  • File Clusters
  • Web Farm
  • Two or three tiers solutions
  • Physical Servers
  • Different Storage solutions

When you do your Datacenter Assessment it’s important to get your workloads visible, because “Lift and Shift” with Azure Site Recovery (ASR) of a Virtual Machine is an different scenario then SQL database migration to Azure. That’s why Microsoft has different tooling like :

To get your dependencies in your Datacenter on the map, Microsoft has Azure Service Maps.

Service Map automatically discovers application components on Windows and Linux systems and maps the communication between services. With Service Map, you can view your servers in the way that you think of them: as interconnected systems that deliver critical services. Service Map shows connections between servers, processes, inbound and outbound connection latency, and ports across any TCP-connected architecture, with no configuration required other than the installation of an agent.

This is very handy to get insides of your Datacenter communication workloads.

More information on using Azure Service Maps here

Installation example of Hyper-V Virtual Machines with ASR

In the following step-by-step guide we will install the Azure Site Recovery Agent on a Hyper-V host and migrate a virtual machine to Microsoft azure in a “Lift and Shift” way.

First create a Recovery Services Vault => Click Add.

Then you go to your new created Recovery Vault and click on Getting started for Site Recovery. => Prepare infrastructure and follow the steps.

When you have selected Hyper-V VM to Azure, the next step is the ASR Deployment Planner tool kit. Here you find more information on Azure Site Recovery Deployment Planner user guide for Hyper-V-to-Azure production deployments.

Then in step 3 you will make your Hyper-V Site in Microsoft azure with the Right Hyper-V Servers.

Give your Hyper-V Site the right name, especially when you have a lot of Hyper-V Clusters with Different workloads.

Here is where the registration begins with the Azure Site Recovery (ASR) Agent installation on your Hyper-V Host.
Follow the five steps and make sure your Hyper-V Node can access Azure via secure port 443(https) via Proxy or firewall rules.

Install as Administrator the AzureSiteRecoveryProvider.exe file on the Hyper-V host.

Click on Next

Choose your Installation location and Click on Install.

The Azure Site Recovery agent is installed and need to be registered with your Azure Recovery Vault.
For this you need the key file from the Azure portal to download at step 4. Click on Register.

Browse to your downloaded key file from the Azure Portal Recovery Vault and click on Next.

When you have a proxy you can select that, otherwise select Next.

Now your Azure ASR Agent on Hyper-V is registered with your Azure Site Recovery Vault.

In the Azure Portal you will see your Hyper-V Node, in my Demo LAB it’s WAC01.MVPLAB.LOCAL.

In the next step you can choose an existing Storage account, or a new one with different specifications.

Check also after storage your network in azure.

In this step we create the replication policy.

Set your own settings.

The Replication policy is added to the configuration.

When you click on OK the Infrastructure is done.

We are now going to enable the replication :

Select your Source and location.

here you select your target Storage account, Resource Group and Network.

The connections are made between Hyper-V, ASR Vault and Storage.

Select the Virtual Machine(s) from the Hyper-V host to replicate for migration with ASR

Configure the properties.

Click on OK

From here the Replication will begin from Hyper-V Host to Azure  🙂

Azure Sire Recovery Replication Job status.

Replicated item(s)

To make your recovery plan and do the failover for migration to azure, you have to wait until the first replication is done for 100%.

Azure Site Recovery Plan for failover (Migration)

Make recovery Plan.

Click OK

The Target in the recovery plan can only be selected when the first replication is done.

Overview of the Azure Site Recovery Migration failover.

From the Hyper-V Host you can pause or see the replication health status.

Hyper-V Health Status

Azure Migrate Virtual Machines using Azure Site Recovery video with Microsoft Jeff Woolsey

Microsoft Azure Data Migration Assistant

To migrate your SQL Backend to Microsoft Azure, use this step-by-step instructions help you perform your first assessment for migrating to on-premises SQL Server, SQL Server running on an Azure VM, or Azure SQL Database, by using Data Migration Assistant.

Conclusion :

“Lift and Shift” Migration of your complete datacenter exists of different scenarios for your workloads to Microsoft Azure. With that said, Microsoft has for each scenario tooling available to get the job done. It’s all about a good Architectural Design, Security in place, People and process to get your Intelligent Azure Cloud up and running for your Business.

Next Blogpost Microsoft Azure Hub-Spoke model by Enterprise Design 3 of 4 :
SQL assessment and Data Migration to Azure


Leave a comment

#Microsoft SQL Server 2019 Preview Overview #SQL #SQL2019 #Linux #Containers #MSIgnite

Microsoft SQL Server 2019 Preview

What’s New in Microsoft SQL Server 2019 Preview

• Big Data Clusters
o Deploy a Big Data cluster with SQL and Spark Linux containers on Kubernetes
o Access your big data from HDFS
o Run Advanced analytics and machine learning with Spark
o Use Spark streaming to data to SQL data pools
o Use Azure Data Studio to run Query books that provide a notebook experience

• Database engine
o UTF-8 support
o Resumable online index create allows index create to resume after interruption
o Clustered columnstore online index build and rebuild
o Always Encrypted with secure enclaves
o Intelligent query processing
o Java language programmability extension
o SQL Graph features
o Database scoped configuration setting for online and resumable DDL operations
o Always On Availability Groups – secondary replica connection redirection
o Data discovery and classification – natively built into SQL Server
o Expanded support for persistent memory devices
o Support for columnstore statistics in DBCC CLONEDATABASE
o New options added to sp_estimate_data_compression_savings
o SQL Server Machine Learning Services failover clusters
o Lightweight query profiling infrastructure enabled by default
o New Polybase connectors
o New sys.dm_db_page_info system function returns page information

• SQL Server on Linux
o Replication support
o Support for the Microsoft Distributed Transaction Coordinator (MSDTC)
o Always On Availability Group on Docker containers with Kubernetes
o OpenLDAP support for third-party AD providers
o Machine Learning on Linux
o New container registry
o New RHEL-based container images
o Memory pressure notification

• Master Data Services
o Silverlight controls replaced

• Security
o Certificate management in SQL Server Configuration Manager

• Tools
o SQL Server Management Studio (SSMS) 18.0 (preview)
o Azure Data Studio

Introducing Microsoft SQL Server 2019 Big Data Clusters

SQL Server 2019 big data clusters make it easier for big data sets to be joined to the dimensional data typically stored in the enterprise relational database, enabling people and apps that use SQL Server to query big data more easily. The value of the big data greatly increases when it is not just in the hands of the data scientists and big data engineers but is also included in reports, dashboards, and applications. At the same time, the data scientists can continue to use big data ecosystem tools while also utilizing easy, real-time access to the high-value data in SQL Server because it is all part of one integrated, complete system.

Read the complete Awesome blogpost from Travis Wright about SQL Server 2019 Big Data Cluster here

Starting in SQL Server 2017 with support for Linux and containers, Microsoft has been on a journey of platform and operating system choice. With SQL Server 2019 preview, we are making it easier to adopt SQL Server in containers by enabling new HA scenarios and adding supported Red Hat Enterprise Linux container images. Today we are happy to announce the availability of SQL Server 2019 preview Linux-based container images on Microsoft Container Registry, Red Hat-Certified Container Images, and the SQL Server operator for Kubernetes, which makes it easy to deploy an Availability Group.

SQL Server 2019 preview containers now available

Microsoft Azure Data Studio

Azure Data Studio is a new cross-platform desktop environment for data professionals using the family of on-premises and cloud data platforms on Windows, MacOS, and Linux. Previously released under the preview name SQL Operations Studio, Azure Data Studio offers a modern editor experience with lightning fast IntelliSense, code snippets, source control integration, and an integrated terminal. It is engineered with the data platform user in mind, with built-in charting of query resultsets and customizable dashboards.

Read the Complete Blogpost About Microsoft Azure Data Studio for SQL Server here

SQL Server 2019: Celebrating 25 years of SQL Server Database Engine and the path forward

Awesome work Microsoft SQL Team and Congrats on your 25th Anniversary !


Leave a comment

Use the Azure Cosmos DB Emulator for local development and testing #Azure #CosmosDB #DevOps #Docker

The Azure Cosmos DB Emulator provides a local environment that emulates the Azure Cosmos DB service for development purposes. Using the Azure Cosmos DB Emulator, you can develop and test your application locally, without creating an Azure subscription or incurring any costs. When you’re satisfied with how your application is working in the Azure Cosmos DB Emulator, you can switch to using an Azure Cosmos DB account in the cloud.

The Azure Cosmos DB Emulator for DevOps is easy to install :

Download de MSI

Docker Hub

GitHub

 

You can Attach Azure CosmosDB Emulator in Visual Studio Code 😉

When your development and testing in Azure CosmosDB Emulator is Done and Ready
for the Cloud, you can Move it to CosmosDB in Azure.

More information on Microsoft Azure CosmosDB Emulator on Docs.

Here you see What’s New in Azure Cosmos DB Video :

Lot of Success with Azure CosmosDB !


Leave a comment

#Microsoft Azure SQL Query Editor Preview in the #Cloud #Azure #SQL

This quick start tutorial walks through how to create a SQL database in Azure. Azure SQL Database is a “Database-as-a-Service” offering that enables you to run and scale highly available SQL Server databases in the cloud. This quick start shows you how to get started by creating a SQL database using the Azure portal.

The SQL Query Editor is a browser query tool that provides an efficient and lightweight way to execute SQL queries on your Azure SQL Database or Azure SQL Data Warehouse without leaving the Azure portal. This quickstart demonstrates how to use the Query Editor to connect to a SQL database, and then use Transact-SQL statements to query, insert, update, and delete data in the database.

Query Editor Login

When you select a table you can edit the Data

You can edit the data here (preview)

Or delete a row.

This Feature in Microsoft azure is still in preview !


Leave a comment

#Microsoft Assessment and Planning Toolkit version 9.7 Available for Download #Azure #SQL2017

Microsoft is announcing the availability of version 9.7 of the Microsoft Assessment and Planning (MAP) Toolkit. This release of the MAP Toolkit helps increase the agility and cost effectiveness of deploying the latest Microsoft technologies. MAP 9.7 is updated to inventory, assess and report the SQL Server 2017 and components in the SQL Server assessment. It also helps organizations assess their environment for Windows 10, Windows Server 2016, Office 2016 and Office 365, track usage of Windows Server 2012, SharePoint Server 2013, and Exchange Server 2013, and preparing a migration to Windows Azure virtual machines.

Click here to download the latest version.