Demo Script

Connecting to SQL Azure

Lab version:1.0.3

Last updated:11/29/2018

Contents

Overview

Key Messages

Key Technologies

Time Estimates

Setup and Configuration

Demo Flow

Opening Statement

Step-by-Step Walkthrough

Connect to the Master Database

Create a New Database

Connect to the New Database

Summary

Overview

This document provides setup documentation, step-by-step instructions, and a written script for showing a demo of SQL Azure. This document can also serve as a tutorial or walkthrough of the technology. In this demo, you will look at connecting to SQL Azure using SQL Server Management Studio. You will see how to create new databases and connect to those databases. For additional demos of the Windows Azure platform, please visit

Note: In order to run through this demo, you must have a SQL Azure developer account. To create the accounts, you will first need to sign-up for the invitation code at

Key Messages

In this demo you will see two key things:

  1. SQL Azure is able to use the existing toolset used with SQL Server
  2. With SQL Server 2008 R2 Management Studio is easily move from SQL Server to SQL Azure.

Key Technologies

This demo uses the following technologies:

  1. SQL Azure
  2. SQL Server 2008 R2 Management Studio

Time Estimates

  • Estimated time for setting up and configuring the demo: 1 min
  • Estimated time to complete the demo: 3 min

Setup and Configuration

This demo does not have any advanced configuration requirements. You simply need to have the prerequisites installed and have an account for SQL Azure. For more information on how to purchase an account, visit the SQL Azure Portal at

The following steps describe how to run the Dependency Checker utility included with the demo to verify that you have the prerequisite components. You can skip this exercise if you are confident that the prerequisites are properly installed and configured.

  1. Open a Windows Explorer window and browse to the demo’sSource\Scriptfolder.
  2. Double-click theDependencies.depfile in this folder to launch the Dependency Checkertool and install any missing prerequisites.
  3. If the User Account Control dialog is shown, confirm the action to proceed.
  4. Ensure that the HolTestDB and HoLTestUserare created and configured.

Note:This process may require elevation.The .dep extension is associated with the Dependency Checker tool during its installation. For additional information about thesetupprocedure and how to install the Dependency Checker tool, refer to theSetup.docxdocument in theAssetsfolder of the training kit.

Demo Flow

The step-by-step guide in this document follows the following demo flow/outline:

Figure 1

Diagram

Opening Statement

In this demo, see how to connect to SQL Azure from SQL Server 2008 R2 Management Studio.

  1. First you will connect to the Master Database using your Server Administrator account
  2. Second you will create a new database in SQL Azure
  3. Finally, you will reconnect to SQL Azure in the conext of your newly created database.

Step-by-Step Walkthrough

This demo is composed of the following segments:

  1. Connect to the Master Database
  2. Create a new Database
  3. Connect to the new Database

Note: All of the queries for this demo are stored in the Assets/queries subfolder.

Connect to the Master Database

In this first segment, we will connect to the master database using our server administrator credentials.

Action / Script / Screenshot
  1. Open SQL Server Management Studio (SSMS)Start > All Programs > Microsoft SQL Server 2008 R2 November CTP > SQL Server Management Studio
/
  • We’re going to be using SQL Server Management studio to work with SQL Azure today.

  1. Complete the dialog as follows
  2. Server name is the name of your server that is found I the SQL Azure portal. (See the Demo - Preparing your SQL Azure Account)
  3. SQL Server Authentication
  4. Username: Your Server administrator username
  5. Password: Your server administrator password.
  6. Click Connect.
/
  • SQL Azure assigns our Server Name to us. It’s always listed in the SQL Azure portal.
  • We need to choose SQL Server authentication. SQL Azure does not support Windows Authentication.
  • We’re going to connect to the server using the Server admin credentials we created when we redeemed our SQL Azure token.
/
  1. Click New Query
  2. Execute the query
SELECT @@version /
  • We’ve not got a query window open. Let’s execute a very simple query against SQL Azure.
  • We’ll just query the version of SQL Server
  • Note that it returns an indication that SQL Azure is derived from Microsoft SQL Server 2008.
/
  1. Execute the query
SELECT * FROM sys.databases /
  • We’re running in the context of the Master database on the server at the moment.
  • Let’s run a query against the sys.databasesview. This will allow us to enumerate all of the databases we have created on our server.
  • You’ll see we have the Master database listed.
  • You’ll see that I do not have any other databases created already.
/

Create a New Database

In this segment, we will create a new database in SQL Azure.

Action / Script / Screenshot
  1. Execute the query
CREATE DATABASE HoLTestDB /
  • Creating our own database is very simple.
  • Because SQL Azure takes care of all of our physical storage options we do not need to specify things like filegroups as we might with SQL Server.
  • In SQL Azure we can simple execute the query CREATE DATABASE and provide our database name and we’ll immediately have a new database
/
  1. Execute the query
SELECT * FROM sys.databases /
  • Let’s execute the query against sys.databases again.
  • Note we’ve now got the new database we just created listed in sys.databases.
/

Connect to the New Database

In this segment, we will connect to our newly created HoLTestDB database.

Action / Script / Screenshot
  1. Close the existing query tab.
  2. Click Change Connection
/
  • In order to change context to our new database we’ll need to disconnect and reconnect.
  • In SQL Azure our databases do not necessarily live on the same physical server. While in SQL Server we can use the USE statement to change database context in SQL Azure we need to do a full reconnect to ensure the client is reattached to the correct physical server node in the SQL Azure cluster.
  • Let’s close this query window and reconnect.
/
  1. Complete the connection details as above.
  2. Press the Options button
/
  • We’ll use our Server admin credentials again.
  • Because we want to attach to a specific named database rather than the default database for our admin account we need to click the options button.
/
  1. Type HoLTestDB into the Connect to database combo box.
  2. Click Connect
/
  • We need to type the name of the database we want to connect to here.
  • And then we can connect to our database.
/
  1. Execute the query
SELECT DB_NAME() /
  • Let’s call the DB_NAME() function.
  • Note that the function result shows that we are on the HoLTestDB database.
/

Summary

In this demo, you saw how simple it is to connect to SQL Azure using existing tools such as SQL Server Management Studio. You examined the Master database in SQL Azure and looked at the sys.databasesview that allows you to query all the databases created in a given SQL Azure server.Finally, you created and connected to a new database within SQL Azure.