Introducing database watcher for Azure SQL

Reliable, in-depth, and at-scale monitoring of database performance has been a long-standing top priority for SQL customers. Today, we are pleased to announce the public preview of database watcher for Azure SQL, a managed database monitoring solution to help our customers use Azure SQL reliably and efficiently.

 

Managed monitoring for Azure SQL

To enable database watcher, you do not need to deploy any monitoring infrastructure or install and maintain any monitoring agents. You can create a new watcher and start monitoring your Azure SQL estate in minutes.

 

Once enabled, database watcher collects detailed monitoring data from your databases, elastic pools, and managed instances into a central data store in your Azure subscription. Data is collected with minimal latency – when you open a monitoring dashboard, you see database state as of just a few seconds ago.

Read more here…

Enhanced patching for SQL Server on Azure VM with Azure Update Manager

With Azure Update Manager, unlike with the existing Automated Patching feature, you’ll be able to automatically install SQL Server Cumulative Updates (CUs), in addition to updates that are marked as Critical or Important.    

Azure Update Manager is a unified service that helps manage updates for all your machines. By enabling Azure Update Manager, customers will now be able to:   

  • Perform one-time updates (or maybe Patch on-demand): Schedule manual updates on demand 
  • Update management at scale: patch multiple VMs at the same time 
  • Configure schedules: configure robust schedules to patch groups of VMs based on your business needs:  
  • Periodic Assessments:  Automatically check for new updates every 24 hours and identify machines that may be out of compliance  

thumbnail image 1 of blog post titled 

							Announcing public preview of enhanced patching for SQL Server on Azure VM with Azure Update Manager

 

Read more here…

Create Azure Windows VM

Creating an Azure Windows VM seems pretty easy, but there are a lot of settings to be careful of before hitting the Create button. In this post, we’ll go over some of those so the next (or first) time you create an Azure VM, you’ll be ready!

Here we go….

Log into the Azure portal and click Virtual Machines and Create Virtual Machine:

Azure VM 1

Note: I’m using a free trial.

Under the Basics tab, there are a few options. Let’s start at the top.

Subscription: An Azure subscription is the agreement with Microsoft to use Azure, for which charges accrue. You can create multiple subscriptions to separate billing or management.

Resource Group: A container that holds related resources for an Azure solution. The resource group includes those resources that you want to manage as a group. You decide how to allocate resources to resource groups based on what makes the most sense for your organization.

Virtual machine Name: The identifier and host name for the VM.

Region: The region in which your VM will reside. To see more go here: https://azure.microsoft.com/en-us/global-infrastructure/regions/

Availability Options:

  • No infrastructure redundancy required: No High Availability.
  • Availability Zone: VM’s are in a different physical location within an Azure region. This offers 99.99% SLA.
  • Availability Set: A group with two or more virtual machines in the same Data Center is called Availability Set, this ensures that at least one of the virtual machines hosted on Azure will be available if something happens. This configuration offers 99.95% SLA.

Image: The base Operating System for the VM. The following Windows versions are supported. https://docs.microsoft.com/en-us/azure/azure-stack/azure-stack-supported-os

Azure VM 2Size: This is the size of the VM. Here’s a good chart to use when sizing. Remember, pricing! Another cool thing with Azure is that you can resize pretty easy by shutting down the VM, resizing it, and restarting. Of course, this depends on your region and availability options. https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sizes

Azure VM 3

There’s also a free tool on the web that will help you size your VM based on certain resources. https://www.vmchooser.com/vmchooser

Username: The administrator username for the VM

Password: The administrator password for the VM

Public inbound ports: Ports that are accessible from the public internet.

Already have a Windows license?: You can save money if you already have a Software Assurance license or an Active Windows server subscription.

 

In my example, I’m using a Free Trial, so that’s the only option for Subscription. For Resource Group, let’s create a new group (by clicking Create New), named SQLFreelancerRG.

I’ll name my VM SQLVM, put it in the East Region, with no redundancy and a fresh copy of Windows Server 2016 DataCenter.

Azure VM 4

Next, I’ll size this VM with the Standard DS1 v2, which is going to give me 1vCPU and 3.5GB RAM. I’ll create an Administrator account, allow no ports from the public internet and choose No for the Azure Hybid Benefit.

Azure VM 5

The next page is all about the Disks. https://docs.microsoft.com/en-us/azure/virtual-machines/windows/about-disks-and-vhds

OS disk type: This is type of disks we can use for the VM. Let’s look at Standard vs. Premium:

Azure VM 6

You can also create and attach a new disk or attach an existing disk (from snapshot of another disk or BLOB storage)

In my example, I’ll use a Premium SSD disk and I’ll create a second disk called SQLVM_DataDisk_1.

Azure VM 7

The next page gets into a little Networking. https://docs.microsoft.com/en-us/azure/virtual-machines/windows/network-overview

Virtual Network: A virtual network enables VM’s to communicate privately with each other, and with the internet.

Subnet: The subnet is the range of IP addresses in the Virtual Network.

Public IP: Public IP addresses allow Internet resources to communicate inbound to Azure resources. Public IP addresses also enable Azure resources to communicate outbound to Internet and public-facing Azure services with an IP address assigned to the resource. The address is dedicated to the resource, until it is unassigned by you. If a public IP address is not assigned to a resource, the resource can still communicate outbound to the Internet, but Azure dynamically assigns an available IP address that is not dedicated to the resource.

NIC network security group: Security rules in network security groups enable you to filter the type of network traffic that can flow in and out of virtual network subnets and network interfaces.

Public inbound ports: Ports that are accessible from the public internet.

Accelerated Networking: Enables low latency and high throughput on the network interface.

Load Balancing: Azure Load Balancer delivers high availability and network performance to your applications. A load balancer can be configured to balance incoming Internet traffic to VMs or balance traffic between VMs in a VNet. A load balancer can also balance traffic between on-premises computers and VMs in a cross-premises network, or forward external traffic to a specific VM.

For this post, I’m just going to use the defaults and move forward.

Azure VM 8

Next up is Monitoring. https://docs.microsoft.com/en-us/azure/virtual-machines/windows/monitor

Boot diagnostics: As VMs boot, the boot diagnostic agent captures boot output and stores it in Azure storage. This data can be used to troubleshoot VM boot issues.

OS guest diagnostics: Allows you to get metrics every minute for your VM. You can use them to create alerts and stay informed.

Diagnostics storage account: The storage account in which your diagnostics are stored.

System assigned managed identity: Managed identities for Azure resources provides Azure services with an automatically managed identity in Azure Active Directory. You can use this identity to authenticate to any service that supports Azure AD authentication, without having credentials in your code. https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/qs-configure-portal-windows-vm

Enabled auto-shutdown: Configures your VM to automatically shutdown each day.

Enable backup: Auto backup for your VM.

I’m going to keep defaults and click Next.

Azure VM 9

The next page is Guest Config where you can add additional configuration, scripts, agents, or applications via VM extensions. Click Next.

Tags are name values pairs that enable you to categorize resources and view consolidated billing. Think of this as Twitter hashtag.

Last page is Review and Create. This is the page that displays a summary of our selections including price per hour of uptime. You’ll also notice at the bottom there is a link that will allow you to download a template for automation. This will allow you to use the same specs that you created for this VM on other VM’s. Click Create to build your new VM.

Azure VM 10

If you click on the Notification Bell at the top of the portal, you’ll see that Deployment is in progress.

Azure VM 11

Once the VM is created, you can click Virtual Machines from the Favorite Bar and view your newly created VM:

Azure VM 12

If you click on the new VM it will bring up the Overview page (along with tons of other pages to choose from).

Click on the Networking tab and Add inbound port rule. Under Destination Port Range type 3389 and under Name type RDP inbound. This will allow RDP access.

Azure VM 13

Click Connect and Download RDP file to remote into the server.

Azure VM 14

TADA!

 

 

Using the Deploy Database to SQL Azure Wizard in SQL Server Management Studio to move to the Cloud

This post is branching off of another post I wrote recently, “Move an On Premise SQL Server Database to the SQL Azure Cloud” where I explained how to move an on premise database to the cloud using the Export/Import Data-tier application. After some comments on this tip, research and testing I think it would be beneficial for the SQL community to understand the different options of moving to the cloud.

If you’d like to follow along with the examples I would suggest creating a free (trial) account at http://www.windowsazure.com. Once you are logged into the Windows Azure portal a SQL Server will need to be created.

Click on SQL Databases, Servers, Create a SQL Database Server:

SQL Freelancer SQL Server Azure
Create a login name, password, and choose your region and click the check mark:

SQL Freelancer SQL Server Azure
A new SQL Server should be built in a few seconds! Click Manage at the bottom and a few firewall rules options may appear. Click Yes on all of them.

SQL Freelancer SQL Server Azure
Next, click on the server, and click Dashboard:

SQL Freelancer SQL Server Azure
On the right side of the screen you will see the full SQL Server name. In this example, the name is ns9rkrmwj9.database.windows.net. Write this name down, you’ll need it later.

SQL Freelancer SQL Server Azure
Now that we have a SQL Azure Server built, let’s move our databases.

Option 1: Using the Deploy Database to SQL Azure Wizard

In SQL Server 2012, there is a new wizard that will allow you to move an on premise database to SQL Azure. This is only available in SQL Server 2012 since SQL Azure was nonexistent in previous versions.

To use this wizard, open SQL Server Management Studio (SSMS) and connect to the server. Right click the database you want to move, click Tasks, Deploy Database to SQL Azure…

SQL Freelancer SQL Server Azure
Click here to view the rest of this post.

 

Move an On Premise SQL Server Database to the SQL Azure Cloud

Most of us have heard of SQL Azure, but a lot of people have yet to adopt the “cloud” version of SQL Server. This post will focus on how to move an on premise database to the SQL Azure and Windows Azure VM. To learn how to get started with SQL Azure visit Microsoft’s Azure site.

Most of us are used to the “Restore Database” command found by right clicking the Databases container in SQL Server Management Studio (SSMS). SQL Azure has done away with this and instead only gives you the options below:

On premise database Azure database

SQL Freelancer SQL Server Azure

SQL Freelancer SQL Server Azure

This isn’t very helpful and can make you want to deactivate your Azure subscription immediately, but hold on…there is a way.

First, you will need to create a storage account from Windows Azure. To do this, log in to Azure and click Storage, New from the left pane:

SQL Freelancer SQL Server Azure
Click Quick Create, Enter a URL and Location, and click Create Storage Account:

SQL Freelancer SQL Server Azure

Once the storage is created you should be able to view and manage it by clicking on the storage item on the left pane.

Click Manage Access Keys and take note of the Storage Account Name and Primary Access Key:

SQL Freelancer SQL Server AzureSQL Freelancer SQL Server Azure
Next click on the storage name and click Containers, Create a Container:
SQL Freelancer SQL Server AzureSQL Freelancer SQL Server Azure
Back in SSMS, right click on the database you want to move to the cloud and choose Tasks, Export Data-tier Application:

SQL Freelancer SQL Server Azure
Click Save to Windows Azure and the Connect…button.

SQL Freelancer SQL Server Azure
Click here to view the rest of this post.