Set up a lab to manage and develop with SQL Server
This article describes how to set up a lab for a basic SQL Server management and development class in Azure Lab Services. Database concepts are one of the introductory courses taught in most of the Computer Science departments in college. Structured Query Language (SQL) is an international standard. SQL is the standard language for relation database management including adding, accessing, and managing content in a database. It is most noted for its quick processing, proven reliability, ease, and flexibility of use.
In this article, we'll show how to set up a virtual machine template in a lab with Visual Studio 2019, SQL Server Management Studio, and Azure Data Studio. For this lab, we will use one shared SQL Server Database for the entire lab. Azure SQL Database is Platform as a Service (PaaS) Database Engine offering from Azure.
External resource configuration
To use a shared resource in Lab Services, you first need to create the virtual network and the resources itself. To create the virtual network and connect it to the lab, follow how to create a lab with a shared resource in Azure Lab Services. Remember, any resources external to Lab Services will be billed separately and will not be included in lab cost estimates.
To use any external resources, you’ll need to Connect to your virtual network in Azure Lab Services with your lab plan
Advanced networking must be enabled during the creation of your lab plan. It can't be added later.
Now that the networking side of things is handled, lets create a SQL Server Database. We are going to create a single database as it is the quickest deployment option for Azure SQL Database. For other deployment options, create an elastic pool, managed instance, or SQL virtual machine.
- From the Azure portal menu, choose Create new resource.
- Choose SQL Database and select the Create button.
- On the Basics tab of the Create SQL database form, select the resource group for the database. We will use sqldb-rg.
- For Database name, enter classlabdb.
- Under the Server setting, select Create new to create a new server to hold the database.
- On the New server flyout, enter the Server name. We'll use classlabdbserver. The server name must be globally unique.
- Enter azureuser for the Server admin login.
- Enter a memorable password. Password must be at least eight characters in length and contain special characters.
- Choose region for the location. If possible, enter the same location as the lab account and peered vnet to minimize latency.
- Select OK to return to the Create SQL Database form.
- Select Configure database link under the Compute + storage setting.
- Modify database settings as needed for the class. You can choose between Provisioned and Serverless options. For this example, we'll use the autoscaled Serverless option with max vCores of 4, min vCores of 1. We'll keep the autopause setting at the minimum of 1 hour. Select Apply.
- Select Next: Networking button.
- On the Networking tab, choose Private endpoint for the Connectivity method.
- Under the Private endpoints section, Select Add private endpoint.
- On the Create private endpoint flyout, choose the same resource group as your virtual network peered to the lab account.
- For Location, choose the same location as the virtual network.
- For Name, enter labsql-endpt.
- Leave the Target subresource set to SqlServer.
- For Virtual network, choose the same virtual network peered to the lab account.
- For Subnet, choose subnet you want the endpoint hosted in. The IP assigned to the endpoint will be from the range assigned to that subnet.
- Set Integrate with private DNS to No. For simplicity, we'll use Azure's DNS over own private DNS zone or our own DNS servers.
- Select OK.
- Select Next: Additional settings.
- For the Use existing data setting, choose Sample. The data from the AdventureWorksLT database will be used when the database is created.
- Select Review + create.
- Select Create.
Once the SQL Database deployment successfully completes, we can create the lab and install software on the lab template machine.
To set up this lab, you need an Azure subscription to get started. If you don't have an Azure subscription, create a free account before you begin.
Lab plan settings
Once you get have Azure subscription, you can create a new lab plan in Azure Lab Services. For more information about creating a new lab plan, see the tutorial on how to set up a lab plan. You can also use an existing lab plan.
Enable your lab plan settings as described in the following table. For more information about how to enable Azure Marketplace images, see Specify the Azure Marketplace images available to lab creators.
|Lab plan setting||Instructions|
|Marketplace image||Enable the 'Visual Studio 2019 Community (latest release) on Windows 10 Enterprise N (x64)' image.|
For instructions on how to create a lab, see Tutorial: Set up a lab. Use the following settings when creating the lab.
|Virtual Machine Size||Medium. This size is best suited for relational databases, in-memory caching, and analytics.|
|Virtual Machine Image||Visual Studio 2019 Community (latest release) on Windows 10 Enterprise N (x64)|
Now that our lab is created, let's modify the template machine with the software we need.
The image chosen above includes Visual Studio 2019 Community. All workloads and tool sets are already installed on the image. Use the Visual Studio Installer to install any optional tools you may want. Sign in to Visual Studio to unlock the community edition.
Visual Studio includes the Data storage and processing tool set, which includes SQL Server Data Tools (SSDT). For more information about SSDT's capabilities, see SQL Server Data Tools overview. To verify connection to the shared SQL Server for the class will be successful, see connect to a database and browse existing objects. If prompted add the template machine IP to the list of allowed computers that can connect to your SQL Server instance.
Visual Studio supports several workloads including Web & cloud and Desktop & mobile workloads. Both of these workloads support SQL Server as a data source. For more information using ASP.NET Core to SQL Server, see build an ASP.NET Core and SQL Database app in Azure App Service tutorial. Use System.Data.SqlClient library to connect to a SQL Database from a Xamarin app.
Install Azure Data Studio
Azure Data Studio is a multi-database, cross-platform desktop environment for data professionals using the family of on-premises and cloud data platforms on Windows, macOS, and Linux.
- Download the Azure Data Studio system installer for Windows. To find installers for other supported operating systems, go to the Azure Data Studio download page.
- On the License Agreement page, select I accept the agreement. Select Next.
- On the Select Destination Location page, elect Next.
- On the Select Start Menu Folder page, select Next.
- On the Select Additional Tasks page, check Create a desktop icon if you want a desktop icon. Select Next.
- On the Ready to Install, select Next.
- Wait for the installer to run. Select Finish.
Now that we have Azure Data Studio installed, let's setup the connection to Azure SQL Database.
- On the Welcome page for Azure Data Studio, select the New Connection link.
- In the Connection Details box, fill in necessary information.
- Set Server to classlabdbserver.database.windows.net
- Set User name to azureuser
- Set Password to password used to create the database.
- Check Remember Password.
- For Database, select classlabdb.
- Select Connect.
Install SQL Server Management Studio
SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure. SSMS is a tool used by database administrators to deploy, monitor, and upgrade data infrastructure.
- Download Sql Server Management Studio. Once downloaded, start the installer.
- On the Welcome page, select Install.
- On the Setup Completed page, select Close.
- Start Sql Server Management Studio.
- On the Dependency Configuration process page, select Close.
Not that SSMS is installed, you can connect and query a SQL Server. When setting up the connection, use the following values:
- Server type: Database Engine
- Server name: classlabdbserver.database.windows.net
- Authentication: SQL Server Authentication
- Login: azureuser
- Password: password used to create the database.
Let's cover a possible cost estimate for this class. Estimate does not include the cost of running the SQL Server. See SQL Database pricing for current details on database pricing.
We'll use a class of 25 students. There are 20 hours of scheduled class time. Also, each student gets 10 hours quota for homework or assignments outside scheduled class time. The virtual machine size we chose was medium, which is 42 lab units.
Here is an example of a possible cost estimate for this class:
25 students * (20 scheduled hours + 10 quota hours) * 0.42 USD per hour = 315.00 USD
Cost estimate is for example purposes only. For current details on pricing, see Azure Lab Services Pricing.
The template image can now be published to the lab. For more information, see Publish the template VM.
As you set up your lab, see the following articles:
Submit and view feedback for