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.

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.
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

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
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

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

Configure Firewall Rules for your Azure SQL Database Server

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
In the blade for your SQL server, under Essentials, click Show firewall settings.
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

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

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

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

-- Get sales revenue by company and contact using CTE
WITH CustomerSales (
	CompanyContact
	,SalesAmount
	)
AS (
	SELECT CONCAT (
			c.CompanyName
			,CONCAT (
				' (' + c.FirstName + ' '
				,c.LastName + ')'
				)
			)
		,SOH.TotalDue
	FROM SalesLT.SalesOrderHeader AS SOH
	JOIN SalesLT.Customer AS c ON SOH.CustomerID = c.CustomerID
	)
SELECT CompanyContact
	,SUM(SalesAmount) AS Revenue
FROM CustomerSales
GROUP BY CompanyContact
ORDER BY CompanyContact;

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