Building Perfect SQL Servers, Every Time

Tomorrow I’ll be presenting to the DBA Fundamentals VC on Building Perfect SQL Servers, Every Time. This is a session I’ve been wanting to build for a while—as many of you know SQL Server does not install with best practices configured out of the box; in my role as a consultant I see a lot of installations where the installer simply clicked next. What I really wanted to call this presentation was “Don’t Just #$%^ing Click Next When You Install SQL Server”

Additionally, you can grab the script files here.

In tomorrow’s session I will cover the following:

  • General best practices for configuring SQL Server
  • What you should change after install
  • How to manage a larger environment
  • How you can automate this process, so you get the same server every time.

We will talk about how to automate physical builds, and a little bit about building your own private SQL Server cloud—what it takes, and what your next steps are.


Getting Started with Azure Hybrid Models—Point to Site Networking

I’ve done several presentations on hybrid disaster recovery (DR) using SQL Server and Windows Azure, and configuring the network has been a headache every time. Some SQL DR methods do not require a direct network connection (log shipping, mirroring, backups) and mat be secured using certificates for encyption, more advanced configurations require a VPN connection. Also, if domain services are required (either to connect back to an on-premises Windows Domain Controller (DC) (good) or to replicate your on-premises Domain Controller to a ReadOnly DC (best)) you will need a VPN connection.

If you would like a little background on networking in Azure, I highly recommend Mark Russinovich’s (b|t) session from TechEd last year on Windows Azure internals.

There are three types of direct connections to Windows Azure:

  1. Site to Site Network—an approved VPN device in your DMZ connects via VPN (encrypted, but not dedicated) to a network gateway Windows Azure. Best scenario for low volume, but regularly used scenarios
  2. Point to Site Network—the focus of this post, a certificate based VPN allowing individual machines to connect directly to the network gateway in Windows Azure, using a certificate and a VPN client. Each machine must have the certificate. This is designed for demos, and admin users who may need to connect directly to Azure virtual networks
  3. ExpressRoute—this is the true Enterprise class solution for connecting to Windows Azure. You can connect either at your ISP (participating ISPS on this list) or via a dedicated MPLS VPN from your WAN to Windows Azure. This guarantees a fixed amount of bandwidth and performance.

Figure 1 ExpressRoute Networking —

Configuring Point to Site Networking

So if you are reading this, I am going to assume you have a Windows Azure account—if you don’t, get one—there’s lots of interesting things to play with. Connect to the Azure management portal and scroll down to networks.

Figure 2 Add new network in Windows Azure

From that menu select Virtual Network > Custom Create. You will be prompted to give your network a name and select an existing or create a new affinity group. I’m not going to write about affinity groups today, but they are an important part of Windows Azure—you can read more about them here.

Optionally, you can add a DNS server name and address. If you do not, Windows Azure will provide DNS services for you (in your VMs.) In my experience with this, if you want your VMs to connect to the internet, do not setup a DNS server (this mainly applies for point to site demo work—if you are doing this in prod, setup a DNS server.) There is an also an option to configure point to site networking—check this box and if you have a real VPN, you may also check site-to-site.

Figure 4 Configuring Point to Site VPN in Network

The next screen will just cover your IP address space, for limited demos you can just accept the default and click next. Finally click the “Add Gateway Subnet” option.

Figure 5 Add Gateway Subnet

You now have a new network, but with no VPN gateway to connect to. From the network dashboard click the Create Gateway button on the bottom of the screen. This will take about 20-25 minutes—don’t worry, there is other stuff to do.

Figure 6 Network without gateway

You have two other tasks—create an Azure VM, that is on this network (See below screenshot—you can choose network on the third screen of the VM creation process)

Figure 7 Creating a new Azure VM

While your VM is being created, you will need to create your certificates, to both upload to your Azure gateway and to install on the client machine. In my opinion, this isn’t clear in the Microsoft documentation, so I’ll draw a picture.

So you will create the root certificate on the main machine you want to connect to the VPN (in my case it is my on-premises Domain Controller) and then upload it to your Azure network.

Figure 8 Cert uploaded to network

You will also create a client certificate using this root certificate. You will install the client certificate on any machine you want connect to your VPN—do not recreate and upload a new root certificate for each machine that you want to connect. In order to create the certificates, you will need makecert.exe—you can get it as part of the Windows SDK. For the detailed instructions on creating the certificates, go to this MSDN page and find “Create a self-signed root certificate.” Execute those instructions all the way through “Install the client certificate.”

Now, you are ready to connect—on your gateway page (it’s probably finished creating by now) on the right side of the screen you will see “Download the 64-bit (You aren’t really using a 32 bit OS, are you?) Client VPN Package” click that link. It’s an exe that is not signed by Microsoft, so Windows will yell at you for installing it. That will install quickly—if you expand the network pane in Windows, you will see a new connection.

Figure 9 Connect to Azure VPN

Click connect, and then you will see…

Figure 10 Azure VPN Connection

If you run an IPConfig—you will see the connection to Azure.

Figure 11 IP Record from Azure VPN

One warning here—I’ve had this happen with a client, and it happened to me in the office. When I connect the VPN, I can’t get to the internet on any of my connections. Oddly, this has only happened on corporate networks, and hasn’t happened when I’ve been on the same connection through a virtual machine on my laptop. More to come later.

Update: This behavior is tied to Cisco firewall equipment. I will address in a follow up post.

Now (if you’ve turned Windows Firewall off) you can ping your VMs, and get started using SQL Server HA and DR functions with them.

Into the Blue—Building DR in Windows Azure

Tomorrow (at 1300 EDT/1800 GMT), I’ll be presenting to the SQL Pass High Availability and Disaster Recovery VC on one my favorite topics of late—building disaster recovery from your data center into Windows Azure Infrastructure as a Service. You can register for the session here. While I’m not screaming “cloud, cloud, cloud” from the rooftops, I do feel like DR is a really great point of entry to the cloud for many smaller organizations, who may lack the resource to have a second data center, or perhaps only need their applications to be highly available during certain parts of year. Additionally, the ability to backup SQL Server databases directly to Azure Blob Storage also can meet your offsite backup requirements with very little headaches.

The cloud isn’t all unicorns and rainbows however—there are some definite challenges in getting these solutions to work properly. I’ll discuss that and the following in this session:

  • Choosing the right DR solution
  • Networking and the cloud
  • Economics of cloud DR
  • Implementation concerns for AlwaysOn Availability Groups

I hope you can join me on Tuesday.

%d bloggers like this: