Upgrading a SQL FCI to Premium Storage in Azure Without Downtime

Published On: 2015-09-30By:

Recently I was asked by a client to upgrade their SQL Server Failover Cluster from standard storage to Azure premium storage with as little downtime as possible. Due to the fact that the SQL Server instance was clustered already this was actually a pretty straight forward process.

The first step was to figure out which node of the cluster was running as the active node, so we could start with the passive node. The next step was the tell cluster to not allow failovers of the SQL Cluster. Next we opened the SIOS Data Keeper Cluster Edition GUI and break the mirror for the disks that we are going to upgrade. Then I logged into the Azure portal and converted the VM from a G2 into a GS2 so that premium disks could be attached. After the VM restarted (don’t forget, this is the passive node so there’s no outage for the restart) the disks are removed from the VM and the new disks are added. The new disks were added via PowerShell like this:

get-AzureVM -name ServerName | add-AzureDataDisk -CreateNew -DiskSizeInGb 1023 -DiskLabel ServerName-T http://Something.blob.core.windows.net/vhds/ServerName-t.vhd” | update-AzureVM

After the disks are all added, they are formatted and given the correct drive letters.

Next SIOS Data Keeper Cluster Edition is told to restart the mirror. This forces it to do a full sync as there’s no data on the old drives. This takes forever as we are limited to reading data from the disk at the speed of the old standard disks (500 IOPs). Once it’s done (in this case there was about 200 Gigs of data to replicate across three disks) the cluster can be failed over (this is the only outage in the process).

We can now upgrade the second VM to support premium storage, then change out the disks and restart the replication again. It’s a long process but it works, and there’s just a single outage to the process.

Denny

Contact the Author | Contact DCAC

Connecting To SQL Server Running On An Azure VM

Published On: 2015-08-26By:

If you have setup a Windows Azure Virtual Machine running SQL Server you may have noticed that you might not be able to connect to the SQL Server running on the machine by using SQL Server Management Studio on your workstation (desktop, labtop, etc) from your home or office.

This is normal and setup this way by default.

First I’ll explain the why, then how to change this.

The Why


The reason that this is disabled is that Microsoft doesn’t want to leave the SQL Server exposed to the public Internet where people can and will try to break into the SQL Server instance, pretty much 24 hours a day, 7 days a week until they get into the server. Microsoft is doing you a favor by not letting this happen by default.

Fixing It


Now, assuming that you want this access there’s a couple of ways to set this up.

Site to Site VPN


The first (and best) is to setup a site to site VPN between your office and the Windows Azure network. Now this requires a bit of configuration and a little understanding of networking (and access to make changes to your office’s router). If you aren’t the IT guy/gal, you’ll need to talk to the IT guy/gal in order to get this done. If you are the IT guy/gal and you have access to the router, Microsoft has provided some scripts to make this much easier to complete.

The first thing you’ll want to do it connect to your Azure account and browse to the virtual networks. Select the network that you want to modify and you’ll get the properties of the network to pop out on the next blade. It’ll look something like this.

Network_pre_vpn1

You’ll want to click on the VPN Connections section on the right, select site-to-site, check the “Create gateway immediately” check box, then click on Local Site and enter a name and the public IP for your office network as well as tell Azure what all the IP Subnets for your internal office network are. It’ll look something like this, then click OK to get back to the “New VPN Connection” blade.

VPN_in_process

Click the “Optional Gateway Configuration” button and change the routing from Dynamic to Static. If you want to change the IP subnet for the private side of the VPN within Azure you can do here as well. You can also select the gateway size between Normal and High performance, but the high performance one will probably cost more money. If you are a large enterprise you probably need the high performance option, if you are a small/medium business the normal gateway will be just fine. Click OK all the way back down to the virtual network blade and wait for the network changes to be made. This can take 5-10 minutes for the VPN endpoint to be created and spun up.

After Azure is done doing it’s thing the configuration section of the portal will look something like this.

vpn_almost_ready

Click on the “VPN Connections” panel and a new blade will open. At the top you can download the script to configure the office router to use the VPN connection.

Point to Site VPN


If you don’t want to setup a site to site VPN so that all office users can access Azure, or if you don’t have access to do so, you can setup a VPN from your specific machine to the Azure cloud. Just keep in mind that this option doesn’t support any user level authentication so if you give someone else access to your Azure network this way, you won’t be able to remove their access without revoking access for all users and setting everyone up from scratch.

The first thing you’ll want to do it connect to your Azure account and browse to the virtual networks. Select the network that you want to modify and you’ll get the properties of the network to pop out on the next blade. It’ll look something like this.

Network_pre_vpn1

You’ll want to click on the VPN Connections section on the right, select Point-to-site, and tell Azure what all the IP Subnet to use for people who VPN in should be. Any private IP subnet will work as long as you haven’t used it in one of your Azure networks already. The portal will verify that the IP subnet you enter will work. Something like “192.168.5.0/24″ should work fine.

Click OK all the way back down to the virtual network blade and wait for the network changes to be made. This can take 5-10 minutes for the VPN endpoint to be created and spun up.

After Azure is done doing it’s thing the configuration section of the portal will look something like this (but with the Point to site icon in color).

vpn_almost_ready

Click on the “VPN Connections” panel and a new blade will open. At the top you can download the VPN software that you will need to connect your computer to the Azure network securely.

Opening Access to the Virtual Machine from the Public Internet


If for some reason you don’t want to VPN in, first you need to figure out why you don’t want to do this. If there’s a really good reason to not VPN in, then continue with setting up direct Internet access to the SQL Server. But keep in mind, when I talk to clients about this sort of thing, there is almost never a good reason to setup access to the VM directly from the Internet.

To open an endpoint browse to the VM in the Azure portal. Open the properties of the VM in the Azure Portal, then click the “All Settings” option. Then select “Endpoints”. It’ll look something like this.

VM_Settings

If you see a “SQL Server” endpoint with 0 ACL Rules then the work is half done (shown above). If there are ACL rules then you should be finished unless you need to add more ACL Rules.

If there is no SQL Server endpoint click the “Add” button at the top of the Endpoints blade. Name the endpoint “SQL Server”, select the protocol TCP, then set the ports to 1433 (or whatever TCP ports you want to use, but 1433 is the default). Select to setup access rules for whoever needs access and block any subnets that don’t need access and then OK back to the VM’s properties.

Endpoint

Now What


At this point you can connect to the SQL Server instance through whichever method you’ve setup. If you are using either VPN option you can just connect to the Virtual Machine’s network name. If you are going through the public endpoint (again this is REALLY NOT recommended) you’ll need to connect to the machines full DNS name.

Denny

Contact the Author | Contact DCAC

Be Careful When Starting Up Azure VMs Running SQL Server

Published On: 2015-07-08By:

So Microsoft has done something pretty dumb with the Azure VMs which are running Microsoft SQL Server. By default the front end firewall (the one that allows or blocks traffic from the public Internet to the VMs) allows traffic to the default SQL Server port 1433. At first this is fine, until you change the firewall port on the Windows firewall to allow the other VMs to connect to SQL. Now you’ve got a problem as the public firewall is open, and your Windows firewall is open, so anyone who attempts to connect to the SQL port 1433 from the outside will have direct access to the SQL Server instance.

So when creating VMs which will be running SQL Server that you are creating from the default SQL Server template you’ll need to go into the Azure portal and change the firewall endpoints. Do to this edit the properties of the VM, and edit the settings. Then edit the Endpoints.

If you see the “SQL Server” endpoint as shown below, and you’ve disabled the Windows Firewall on the VM from blocking TCP port 1433, then the entire public Internet has access to your SQL Server VM.

VM_Settings

To remove this mouse over the SQL Server endpoint and click the menu button shown below, then click “Delete” from the context menu that appears.

VM_Settings2

For each SQL Server VM that you’ve deployed using Microsoft’s SQL VM Template.

If you’ve setup SQL Server VMs in Azure within the last couple of months you’ll want to go and check the Azure Endpoints and make sure you don’t have a firewall hole that you weren’t expecting. I’ve spoken to Azure team at Microsoft about this and the default template is being fixed so that it isn’t setup this way any more, if it isn’t fixed already.

Denny

Contact the Author | Contact DCAC

Splitting Files for SQL DW

Published On: 2015-07-01By:

When doing data warehouse loads into SQL DW you’ll want to break your single large file that you extract into multiple smaller files in order to do loads into the SQL DW (via Azure Blob Storage) as fast as possible. There’s a few ways that you can do this. One way would be to handle this in your ETL that extracts the data from your source database. Depending on how you are extracting the data this can be anywhere from painful to REALLY, REALLY painful.

A much easier way is to just extract the data to a single text file, then split that single text file using a command line tool. Now you are probably asking yourself where you might get this handy command line tool. Well thankfully you can download it right here. While doing some work for a client on SQL DW we went ahead and put this tool together for you which as a command line tool you can easily build into your ETL process. The only requirements for using the tool is that you have .NET 4.0 installed on the machine which is running the tool. It runs very quickly and will create evenly sized files from your large source file. I used it recently on a ~16.7 Gig file and it was able to process the file in about 20 minutes (keep in mind I was using a slow 7200 RPM consumer hard drive. Smaller 2 Gig files processed in about a minute (again same slow disk). As time permits we’ll work on making the software run even faster.

Denny

Contact the Author | Contact DCAC
1 2

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers