Microsoft Azure SQL Database

Use Microsoft Azure for Ad-Hoc Testing

Microsoft Azure provides a rich set of features which can be used and setup very easy and very quick. Therefore it’s the recommended way for doing ad-hoc tests and try out quick some things. In this post I will show how to use Microsoft Azure SQL Database to quick test some Transact-SQL statements.

All interaction done with a relational database is done in SQL (Structured Query Language). SQL is a standard of both the International Organization for Standards (ISO) and the American National Standards Institute (ANSI). Microsoft’s dialect of the SQL standard, which is used to interact with Microsoft’s SQL Server and Microsoft Azure SQL Database, is called Transact-SQL (T-SQL).

T-SQL is the main language used to manage and manipulate data in Microsoft’s main relational database management system, SQL Server, whether on premise or in the cloud (Microsoft Azure SQL Database).

If you don’t have a Microsoft Azure subscription until now, you can make use of 250 CHF voucher business subscription of Microsoft Azure. Have a look at Trial Offer for Microsoft Azure for more information.

Create an Azure SQL Database

Now that you hopefully have an Azure subscription, you can create an Azure SQL Database instance to use for this post.

    1. Browse to http://portal.azure.com. If you are prompted to sign in, do so with the Microsoft account that is associated with your Azure subscription.
    2. At the bottom of the Hub menu (the vertical bar on the left), click New (represented by a + symbol if the menu is minimized), and then in the New blade that appears, click Databases, and then click SQL Database.
Create Azure SQL Database

Create Azure SQL Database

  1. In the SQL Database blade:
      1. Enter the name AdventureWorksLT
      2. In the Subscription box, ensure that your subscription is listed.
      3. In the Resource group section, ensure that New is selected, and enter TSQL_Quick_Try as the new resource group name.
      4. In the Select Source list, select Sample.
      5. In the Select sample section, ensure that AdventureWorksLT[V12] is selected.
      6. Click Server. Then click Create a new server and enter the following details and click OK.
        • A unique Server name for your server (a red exclamation mark will be displayed if the name you have entered is invalid or already in use, otherwise a green tick is shown).
        • A user name you want to assign to the Server admin login. This can be your
          name or some other name you’ll remember easily – however, you cannot use
          “Administrator”.
        • A Password for your server administrator account. This must meet the password
          complexity rules for Azure SQL Database, so for example it cannot be blank or
          “password”.
        • The Location where your server should be hosted. Choose the location nearest
          to you.
        • Leave the option to allow Azure services to access the server selected (this
          opens an internal firewall port in the Azure datacenter to allow other Azure
          services to use the database).

        New SQL Server

        New SQL Server

      7. In the Pricing Tier section, select Basic.
      8. Ensure that your selections are similar to those below, and click Create.

    SQL Server Pricing Tier

    SQL Server Pricing Tier

  2. After a short time, your SQL Database will be created, and a notification is displayed on the
    dashboard. To view the blade for the database, click Resources Groups and then click on TSQL_Quick_Try Resource Group.

    TSQL_Quick_Try Resource Group Essentials Blade

    TSQL_Quick_Try Resource Group Essentials Blade

Configure Firewall Rules for your Azure SQL Database Server

  1. In the TSQL_Quick_Try blade, under Essentials, click the server name for your database
    server (which should be in the format server_name.database.windows.net). In my case that is tsqlquicktry042.database.windows.net

    Azure SQL Server Show Firewall Settings

    Azure SQL Server Show Firewall Settings

  2. In the blade for your SQL server, under Essentials, click Show firewall settings.
  3. In the Firewall settings blade, click the Add client IP icon to create a firewall rule for your client
    computer, and then click Save.

    Azure SQL Srver Firewall Add Client IP

    Azure SQL Srver Firewall Add Client IP

Note: Azure SQL Database uses firewall rules to control access to
your database. If your computer’s public-facing IP address
changes (or you want to use a different computer), you’ll need
to repeat this step to allow access. Alternatively, you can modify
the firewall settings for your Azure SQL Database server
to allow a range of IP addresses – see the Azure SQL Database
documentation for details of how to do this.

Installing and Connecting from a Client Tool

SQL Server Management Studio is the primary management tool for Microsoft SQL Server, and you can also use it to manage and query Azure SQL Database. If you do not already have SQL Server Management Studio installed, you can download it from Download SQL Server Management Studio (16.5). When the download is complete, run the executable file to install SQL Server management Studio.

After installing SQL Server Management Studio, you can start it and connect to your Azure SQL Database server by selecting the option to use SQL Server authentication, specifying the fully-qualified name of your Azure SQL Database server (<your_server_name>.database.windows.net), and entering your user name in the format <your_user_name>@<your_server_name> and password, as shown here:

Connect to Azure SQL Database

Connect to Azure SQL Database

After connecting, you can create a new query and run it by clicking Execute, and you can save and open Transact-SQL scripts. Be sure to select the AdventureWorksLT database when running your queries as shown here:

Run Query in MS SQL Server Management Studio

Run Query in MS SQL Server Management Studio

Here is also the T-SQL Statement I tried. You can copy it and try it in your Azure SQL Database:

Original Post: https://www.redtoo.com/ch/blog/use-microsoft-azure-for-ad-hoc-testing/

Cloud Computing

Where can be the location of a Azure Resource Group?

When you create a new Resource Group, i.e. with the PowerShell Cmdlet New-AzureRmResourceGroup, there is a mandatory parameter called “Location”. This specifies in which Azure data center the resource group will be placed. A resource group can be placed in any location and does not have to be in the same location as the Azure subscription. Resource groups can contain resources from different locations.

To find out more use:

Get-Help New-AzureRmResourceGroup

.

To determine which location supports each resource type, use the Cmdlet:

Get-AzureRmResourceProvider -ListAvailable

.
You must be logged in you Azure Subscription for that.

To find out which Azure Data Center exists and with that, which values the -Location parameter can have (like “West Europe”, “East US” etc.) look at https://azure.microsoft.com/en-us/regions/.

Azure Resource Manager Portal

What is Microsoft Azure Resource Manager?

Cloud solutions are a combination of many single cloud services, like Azure Active Directory, Virtual Machines, Web Services, Databases and Storage Containers and more. With Azure Resource Manager this solutions can be handled as a group. This means, that the resources for the solution can be deployed, updated or deleted in a single, transactional coordinated operation.

All related resources for a solution are hold in a container and that is called a resource group. The resource group could include all of the resources for a solution, or only those resources that are logically grouped together. How to allocate resources to resource groups is a decision based on what makes the most sense for a organization. Those resource groups are the new concept in Azure that serves as the life-cycle boundary for all of its resources.

Azure Resource Manager Templates can be used for different environments such as testing, staging and production. After the deployment of the resources, Azure Resource Manager provides security, auditing, and tagging features to help with the management of the deployed resources.

If you want to find out more of Azure Resource Manager you can have a look at the Azure Resource Manager Overview by Tom FitzMacken here: Azure Resource Manager Overview

The new Azure Resource Manager Portal, or short the Azure Portal, is that all-in-one work-anywhere experience where to manage the above explained resource groups. It was built from the ground up and some time ago know as the Azure Preview Portal and puts now the applications at the center of the experience.

This unified hub radically simplifies building, deploying and managing cloud resources. Imagine a single, easy-to-use console built just for the user, teams and projects. It brings together all of the cloud resources, team members, and life-cycle stages of applications and provides the user with a centralized place to plan, develop, test, provision, deploy, scale, and monitor those applications. This approach can help teams embrace a BizDevOps culture by bringing all three, business, development and operations capabilities and perspectives together in a meaningful way.

The new portal allows each user to transform the portal home page (called the Startboard) into their own customized dashboard. Stay on top of the things that matter most by pinning them to the Startboard. Resize parts to show more or less data. Drill in for all the details. And see insights (and opportunities) across apps and resources.

oops

How to handle reboot and resume or continue in PowerShell with PowerShell Workflow

While developing a Azure Resource Manager Template with a virtual machine resource and custom script extension, I got the problem, that I have to install a software that needs a reboot and after the reboot, the script should continue to configure these software and install other applications.

The recommended way with custom script extensions is, to have one script, that acts like a start script calling other scripts doing the work. This sounds like a workflow. So I thought I can use PowerShell Workflow to handle this and I gave it a try. With PowerShell Workflow we have all mechanism to handle reboot and resume or continue in a PowerShell script, but it’s anyway a little bit tricky, because we have to use a scheduled task which will be triggered “At startup”.

It was not successful to use it in the Azure Resource Manager virtual machine extension because of other reasons (finally I solved it with Windows PowerShell Desired State Configuration (DSC)), but in general is PowerShell Workflow a fine technology for task which needs a state, because they might be suspended and resumed and could run in parallel. So I will share my experience with you.

First, lets think about the workflow and the single steps called “Activities”. What must be known in advance is, like I wrote already above, is, that PowerShell Workflow is designed to run activities in parallel and that each activity has it’s own workspace. That means, that results i.e. returned to variables cannot be used from the next activity. Each PowerShell command that runs within a workflow is a single, standalone activity. To run activities parallel, the parallel{} keyword must be used and when activites inside the parallel block should run in a defined order the sequence{} keyword must be defined.

For our purpose, following script snipped can be used:

Workflow New-ServerSetup
{
    parallel {
    "1. activity?"
    "2. activity?"
    "3. activity?"
    "4. activity?"
    "..."
    }
    Restart-Computer -Wait 
    "Last activity"
    "or more activities..."
}
# Run the workflow
New-ServerSetup

When this would be executed, the "Last activity" and "or more activities..." would not be processed, because with the Restart-Computer -Wait activity, the New-ServerSetup job will be suspended and will stay in that state after reboot. This can be checked after the server rebooted with

Get-Job
Check status of current jobs

Check status of current jobs

To manually resume the Job, just type

# In our case the job Id is 3. Check if you put the right Id
Resume-Job -Id 3
PowerShell Workflow Suspended, Running, Completed

PowerShell Workflow Suspended, Running, Completed

But, of course, we don’t have the possibility to start the job manually, when it’s executed from the Azure Resource Manager Template virtual machine extension. So, we have to define a scheduled task, resuming the job “at startup”:

Workflow New-ServerSetup
{
    "First activity"
    "Second activity"
    "..."
    Restart-Computer -Wait 
    "Last activity"
    "or more activities..."
    Unregister-ScheduledJob -Name NewServerSetupResume
}
# -------------------------------------------------------------------------
# Use the New-JobTrigger cmdlet to create an "At startup" trigger
# to resume the suspended job.
# Replace <Password> with a password of a administrator
# on the local machine. 
# -------------------------------------------------------------------------
$adm = "Administrator"
$pwd = ConvertTo-SecureString -String "<Password>" -AsPlainText -Force
$cred = New-Object System.Management.Automation.PSCredential($adm, $pwd)
$AtStartup = New-JobTrigger -AtStartup
Register-ScheduledJob -Name NewServerSetupResume `
                      -Credential $cred `
                      -Trigger $AtStartup `
                      -ScriptBlock {Import-Module PSWorkflow; `
                          Get-Job -Name NewSrvSetup -State Suspended `
                          | Resume-Job}

# Run the workflow. It is suspended when the computer restarts.
# We give a defined name for the job, to be able to use the name
# in the scheduled task, otherwise the name would be "Job<n>"
New-ServerSetup -JobName NewSrvSetup

To find out more about Windows PowerShell Workflows use:

Get-Help about_workflows
troubleshooting

Troubleshooting Azure Resource Manager Virtual Machine Extensions

Troubleshooting can be difficult, specially when one don’t know where to find corresponding log files. While I developed a Azure Resource Manager Template, I want to add a Virtual Machine Desired State Configuration Extension and a Virtual Machine Custom Script Extension.

When I tried to deploy the template I got that error:

 
Resource Microsoft.Compute/virtualMachines/extensions 'vm01/script4configvm' failed with message 'The resource operation completed with terminal provisioning state 'Failed'.'

Now was the question, where to find more Infos about that. While searching in Azure Resource Manager Portal, I found it:

Troubleshooting VM extensions in Azure Resource Manager Portal

Troubleshooting VM extensions in Azure Resource Manager Portal

At the Azure Resource Manager Portal select the virtual machine resource to troubleshoot and click on “All settings”. On the “Settings” panel click on “Extensions”. In “Extensions” select the extension to troubleshoot and then in the upcoming pane on the right side click “View detailed status”.

This will open a JSON file containing the complete log of the provisioning process.