One of the features I love about Microsoft Azure and using it a POC is that many of the initial upfront “battles” you have to face in an on-prem solution are already done for you. I’m going to be honest here: I really don’t like having to explain why databases need to be on separate disks, with log files broken out and tempdb on its own for the 200th time. It’s much easier to just go to Azure, create your machine and start loading it up with data. It removes much of the complexity and time delays from trying to stand it up yourself. These images have existed in Azure for awhile, but they recently got a much needed revamp and are truly best practice configured. A high-level of some of the great benefits of this machine are:
Instant File Initialization Enabled
15 total disks – 12 data disks in one data pool, 3 log disks in another separate log pool
data_warehouse only: Recovery model set to “SIMPLE” for MODEL database using ALTER DATABASE
So, how do take advantage of this awesome sauce you ask? Don’t worry, I hear you. The rest of today’s post is going to be all about the initial configuration and stand up of one of these VM’s.
Let’s start by logging in to the Azure portal, and selecting VIrtual Machines from the category selection on the left hand side, and selecting “Create a virtual machine”
On the New dialogue page, select “From Gallery”
Then scroll down to the image you want to use. Here we are going to use the SQL Server 2014 enterprise optimized for data warehouse workloads image.
Then on the next page, we’ll configure the box by giving it a name, selecting the instance size, and creating a new user.
This next page is very important, as it’s where we’re going to configure external connectivity to the machine. Select “create a new cloud service”, and give it a name. Select the region, and your storage account. For the availability set, select whether you’d like to configure this VM to be a part of it. If you’re going to have more than one machine (which typically in a data warehouse we ALWAYS do), this is a very important property. Essentially, Availability sets guarantee redundancy in the event of a failure (think of that 99.95% uptime you keep hearing about in Azure marketing). The only way to guarantee this is to use an availability set. Finally, down at the bottom are you endpoints. This is the connections that can be made into the machine. By default, it’s RDP and Powershell. I’m going to want to use my local SQL Server Management Studio to connect, so I’m going to add MSSQL to the endpoints list by selecting it from the dropdown in the list.
On the last page, select whether you’d like to install any additional configurations on the machine. I’m going to leave mine on the default of just the VM agent, and select Finish.
Once you’re done, the VM will go begin it’s provisioning process:
Depending on the size and features you selected, this may take some time. Once it’s done we need to finish our setup so we can connect to our new awesome data warehouse from other SSMS instances. Remote to the machine by clicking Connect, and logging it with the username/password combo you provided earlier. Once it’s open, we need to open the firewall to allow the connections between our machine and the Azure environment. Under start, opens windows firewall (wf.msc) and create a new inbound rule. It will be Port, and the port is the port in the endpoint you specified in the setup (1433 in this case).
Select Next, and then allow the connection. CLick Next again and select if it should apply to only domain, private, or public. Ideally, yours should be in a domain in your environment. For this post, I’m just going to select Public and click Next. Public opens it up to the internet though, so you want to be really careful with this setting. Click Next, give it a name and description, and click Finish. Now, while still remoted into the box, open up SQL Server Management Studio and connect to the instance. Go to properties:
and select the security tab, Change the server authentication mode to SQL Server and Windows Authentication instead of just Windows auth, and click Ok. Right click on the server again and select Restart to restart the SQL Server service on the machine. Expand Security, and create a new SQL login for a user, Now, back on your local machine, open up management studio. On the connection string, pass in the DNS name of your new Azure VM (myserver.cloudapp.net), and select SQL User Authentication. Login will be the user name/password combo you just created.