SQL 2014 Always-on AG Pt. 10: AAG Setup

2014-10-12_18-34-23After a brief pause in this SQL 2014 AlwaysOn Availability Group series, we are resuming with the server configuration. In this installment we will configure the Windows firewall to allow the two SQL servers to communicate to each other, configure a shared folder for the SQL replication, and finally configure the AlwaysOn feature. Yippee!

Throughout this series I’ve maintained a standard naming convention for all of the computer objects needed for this installation. In the end we will end up with five, yes five, computer objects for this cluster. Two are the SQL server nodes themselves, one for cluster aware updating (-CAU), one for the AlwaysOn Availability group (-AG1), and one for the AlwaysOn Availability group listener (-AG1L). The listener is the name which you would use in ODBC connections to talk with the SQL AlwaysOn cluster and the clustered databases. Don’t use any of the other computer names, as they may appear to work but would result in problems when failing over between nodes.

You can host non-AAG enabled databases on these SQL servers, but would of course not be protected by AAG. In that case you would use SQL server node name in the ODBC connection name, not the AAG listener. Mix and match as you wish, depending on your business requirements.

Blog Series

SQL 2014 Always-on AG Pt. 1: Introduction
SQL 2014 Always-on AG Pt. 2: VM Deployment
SQL 2014 Always-on AG Pt. 3: Service Accounts
SQL 2014 Always-on AG Pt. 4: Node A Install
SQL 2014 Always-On AG Pt. 5: Unattended Node B
SQL 2014 Always-on AG Pt. 6: Cluster Configuration
SQL 2014 Always-on AG Pt. 7: TempDB
SQL 2014 Always-on AG Pt. 8: Max Mem & Email
SQL 2014 Always-on AG Pt. 9: SQL Maintenance
SQL 2014 Always-On AG Pt. 10: AAG Setup
SQL 2014 Always-On AG Pt. 11: File Share Witness

Windows Firewall

1. Download my basic SQL AlwaysOn firewall script from here. Open a Windows command prompt and type the following command:

SQL-Firewall-AG.cmd <AlwaysOn Partner IP>

2. Repeat the same process on the other SQL node, using the other node’s IP address.

Always-On Availability Group Preparation

1. On S drive (or another drive of your choosing) of your first SQL node create a folder called Replication. This is where SQL will stage the replication data.

2. Change the NTFS permissions on the folder and add the SQL service account with full permissions.


3. Right click on the Replication folder and select the Sharing tab. Click Share. Verify the Database engine account is listed as a read/write member. Click on Share.

4.  Launch SQL Manager, right click on Databases and select New Database. Create a new database called SeedDB and leave all other parameters at their default.

5. Expand the SQL Server Agent node in the left pane and locate the DatabaseBackup — User_Databases – Full job. Open the properties of the job, click on Steps in the left pane, and edit Step 1. Modify the path of the backups to S:\Backup.


6. If you want to use the other backup jobs, open them and modify the backup path as well.

7. Run the DatabaseBackup — User_Databases – Full job by right clicking on it and selecting Start job at step.

8. Open SQL Server Configuration Manager and locate the SQL Server service. Open the properties click on the AlwaysOn High Availability. Check the box to enable AlwaysOn.


9. Restart the SQL service.

Always-On Availability Group Configuration

1. Open Active Directory Users and Computers (ADUC) and create a new computer object using the cluster name with a suffix of –AG1 (e.g. SDSQL03-AG1). Change the description of the object to SQL Cluster <Cluster Name> Availability Group.

2. On the first SQL server cluster node in SQL Server Management Studio navigate to AlwayOn High Availability, right click and select New Availability Group Wizard.

3. For the availability group name use the format of: <Cluster Name>-AG1 (e.g. SDSQL03-AG1). This should be the same name as step 1 in this subsection.

4. On the Select Databases screen the SeedDB database should be listed, and it should meet the prerequisites. Check the box next to the database and click Next.

5. On the Specify Replicas screen add the second SQL server cluster node. Configure the automatic failover and synchronous commit options as shown below. Make the second node a readable secondary.


6. On the Listener tab create an availability group listener. For the DNS name, use the same name as the AG (e.g. SDSQL03-AG1) but with an “L” suffix added (e.g. SDSQL03-AG1L), using port 1433. Assign an IP address on the same subnet as the SQL server. If you don’t see the Add button under the network mode box expand the size of the window.

2014-10-12_17-56-247. Configure the data replication synchronization using the Replication file share on the first SQL node, as shown below.


8. Review the validation results and verify all are successes.


9. Proceed through the rest of the wizard and validate all steps are successful.

10. In SQL Server Management Studio right click on the AG and select Show Dashboard. Verify all objects are in a healthy state, as shown below.



Now that we have a SQL AlwaysOn Availability group up and running, there’s still a bit of configuration left to do. So future installments will cover configuring a file share witness, Kerberos, and SSL certificates. Yes, the all important SSL! Don’t quit now and think that you are done. While we are almost there, you will want to follow through the rest of the configuration. Check out Part 11 here.

vSphere 5.5 Install Pt. 1: Introduction

9-29-2013 5-00-14 PMAt VMworld 2013 in San Francisco VMware unveiled vSphere 5.5, the successor to vSphere 5.1. Customers are now chomping at the bit for vSphere 5.5 upgrade information. Of great anticipation was what VMware did to the vSphere 5.1 SSO service to address common complaints.

If you’ve been living under a rock, or haven’t tried vSphere 5.1, you’ve been missing out. While vSphere 5.1 brought a great number of new features to the table, it also brought a wee bit of frustration surrounding the new SSO (Single Sign-On) service and SSL certificates.

Exactly one year later we now have vSphere 5.5 dropped in our lap. Last year I put countless hours into writing a 15-part vCenter 5.1 install series, which earned me the nickname “SSL Guy” at VMworld. The amount of traffic that series continues to get floors me, and I’m glad the community has found it useful.

This year I will endeavor to one up myself, and do a better job with 5.5. Now that SSO and SSL are bit better understood (by myself and VMware), and massively improved, I shouldn’t have to revise the articles on a weekly basis like I did for the 5.1 series. I hope this series proves even clearer, more accurate, and fills in the gaps that VMware documentation sometimes has.

I’ve created a shortened permalink that you can use for quick reference: vexpert.me/Derek55 for this series. Feel free to use however you like…PowerPoint slides, email, etc. If you find this series helpful, please spread the word.

UPDATE November 3, 2013: VMware released vCenter 5.5a, to correct some bugs with SSO and other services. Please use the 5.5a media and not the 5.5 GA media. Find out more here.

Series Agenda

The exact number of installments and what I’ll cover is a bit fluid at the moment. But at a minimum it will cover the following topics:

  • Upgrade or fresh install?
  • Deep dive on what’s new in SSO 5.5
  • vCenter upgrade best practices and tips
  • ESXi upgrade best practices and tips
  • Right sizing your WS2012 vCenter VM (think big)
  • Creating vCenter SSL certificates (remember the 5.1 days)
  • VMware vCenter Certificate Automation Tool (hair loss reduction tool)
  • Manual SSL certificate replacement process
  • Using a SQL 2012 AlwaysOn Failover Cluster for the vCenter database
  • Installing the full vCenter stack of software on Windows Server 2012 (not R2)
  • Configuring VUM
  • ESXi host SSL certificate replacement
  • Deploying the vCenter Server Appliance (VCSA)
  • ..and possibly more…such as VSAN or vFlash Read Cache

I am fully anticipating the vSphere 5.5 installation will be easier, quicker, and cause less hair loss. If you are lucky it might even re-grow hair lost during your vSphere 5.1 deployment. I shall report my results after this series is complete.

While I have two entire blog posts dedicated to upgrade best practices and tips, the step-by-step instructions will assume a fresh install. This is the VMware recommended approach, but doesn’t work for everyone. Upgrade how-to’s are not very valuable, IMHO, since customer configurations will wildly vary. This is particularly true with SSO 5.1 and the many deployment options, coupled with little VMware best practices around SSO 5.1.

Database Support

VMware now officially supports SQL 2012 AlwaysOn failover clusters (using shared storage) for the vCenter database. It does NOT support AlwaysOn Availability groups or database mirroring. To that end I recently wrote a soup to nuts guide (12 parts) on how to install a SQL 2012 Failover Cluster on Windows Server 2012. If that’s something you want to do, you can dive head first into that while waiting on me to post the next vCenter installation installments. Many of you may not be clustering experts, so it should be enough to get you all the way up, with a ton of best practices incorporated. Here’s a quick reference chart for all of the SQL 2012 HA/DR options.

9-29-2013 5-44-04 PM

Best Practices Video

You can also check out this 20 minute video from VMware on vCenter 5.5 best practices. There’s also an accompanying KB article that has some good details as well. You can find that here.

Derek’s Toolkit Script

This year I’m doing something a little different to hopefully make life easier for you all. Last year in the vCenter 5.1 series I had numerous scripts and configuration files to help you through the SSL replacement process. While those worked, it still wasn’t as clear cut and easy to use as I would have liked.

So this year I’ve written a PowerShell script that I cover in-depth in Part 8, which takes most of the pain away in creating your certificate requests and making the files the VMware certificate automation tool needs. As I go through the series it will also do tasks like creating your ODBC connectors. The script will be updated on a regular basis. If there are any PowerShell experts out there that have ideas for making it better, I’m all ears. A sample screenshot of v1.56 is below.

1-11-2014 2-27-28 PM

You can also download the latest version at: vexpert.me/toolkit55


As I add new installments to the series this landing page will be updated with links to each part. The flow will be somewhat different than last year, due to the re-ordering of components and some SSL lessons learned. If you have other areas that you think I should cover, please leave a comment or Tweet me. I’m very active on Twitter, so if you use that medium, be sure to follow me @vDerekS. I’ll tweet about new posts as they get published. If you like the posts, sharing on social media and with co-workers is appreciated.

Feedback is always welcome, so leave comments about your experiences. This can help other people that may have the same problem. The 5.1 series literally had hundreds of reader comments. Some of which had to be censored due to expressed high frustration with 5.1.

One last comment…and I can’t stress this enough. You must, must, must read the vSphere 5.5 release notes. You can find the long document here.

Blog Series

SQL 2012 AlwaysOn Failover Cluster for vCenter
vSphere 5.5 Install Pt. 1: Introduction
vSphere 5.5 Install Pt. 2: SSO Reborn
vSphere 5.5 Install Pt. 3: vCenter Upgrade Best Practices and Tips
vSphere 5.5 Install Pt. 4: ESXi Upgrade Best Practices and Tips
vSphere 5.5 Install Pt. 5: SSL Deep Dive
vSphere 5.5 Install Pt. 6: SSL Certificate Template
vSphere 5.5 Install Pt. 7: Install SSO
vSphere 5.5 Install Pt. 8: Online SSL Minting
vSphere 5.5 Install Pt. 9: Offline SSL Minting
vSphere 5.5 Install Pt. 10: Update SSO Certificate
vSphere 5.5 Install Pt. 11: Install Web Client 
vSphere 5.5 Install Pt. 12: Configure SSO
vSphere 5.5 Install Pt. 13: Install Inventory Service
vSphere 5.5 Install Pt. 14: Create Databases
vSphere 5.5 Install Pt. 15: Install vCenter
vSphere 5.5 Install Pt. 16: vCenter SSL
vSphere 5.5 Install Pt. 17: Install VUM
vSphere 5.5 Install Pt. 18: VUM SSL
vSphere 5.5 Install Pt. 19: ESXi SSL Certificate

Permalink to this series: vexpert.me/Derek55
Permalink to the Toolkit script: vexpert.me/toolkit55

VMworld 2013: Virtualizing HA SQL Servers

Twitter #VAPP5932; Presenter: Scott Salyer (VMware)

This session was focused on the various Microsoft SQL server high availability options and how they mesh with vSphere and its HA options. Unlike Exchange 2013, SQL 2012 has several HA architectures to choose from. Some applications may only support one or two SQL HA models, so don’t jump on a particular bandwagon without doing a requirements analysis and product compatibility research. For example, only recently have applications started to support SQL 2012 AlwaysOn AGs, and even then, they may not support them using SSL encryption. Also, don’t just build a SQL cluster for the hell of it. Carefully consider your requirements, since clustering is somewhat complex. Do you really need 99.999% availability?  Do you have the skillset to manage it?

Finally, some DBAs may be stuck in a rut and think that physical SQL clusters are better than virtualizing them. With today’s hypervisors and best practices, there’s no reason why tier-1 SQL databases can’t be fully virtualized. However, that requires careful planning, sizing, and following best practices. Don’t think that SQL will run inherently slower on vSphere, because it’s not vSphere that maybe impacting performance. It’s one or more subsystems that were not properly configured or tuned which is making it run slow. As we move towards the fully SDDC (software defined datacenter) virtualizing all workloads is important to realizing all of the benefits of moving away from physical instances of services.


  • Why virtualize
  • Causes of downtime and planning
  • Baseline HA
  • AlwayOn AGs
  • SQL Server failover cluster
  • Rolling Upgrades
  • DR and Backup


Causes of Downtime

  • Planned downtime – Software upgrade, HW/BIOS upgrades
  • Unplanned downtime – Datacenter failure, server failure, I/O failure, software data corruption, user error

Native Availability Features

  • Failover Clustering – Local redundancy, instance failover, zero data loss. Requires RDMs; can’t use VMDKs. Not the current preferred option.
  • Database mirroring – Local server and storage redundancy, DR, DB failover, zero data loss with high safety mode
  • Log Shipping – Multiple DR sites, manual failover required, app/user error recovery
  • AlwaysOn – New in 2012, multiple secondary copies
  • DB mirroring, log shipping and AlwaysOn are fully supported by HA, DRS

Planning a Strategy

  • Requirements – RTO and RPOs
  • Evaluating a technology
  • What’s the cost for implementing and expertise?
  • What’s the downtime potential?
  • What’s the data loss exposure?

VMware Availability Features

  • HA protections against host or OS system failure
  • What is your SLA for hardware failures? Re-host your cluster on VMware for faster node recovery
  • VM Mobility (DRS) – Valid for all SQL HA options except failover clustering
  • Storage vMotion



AlwaysOn High Availability

  • No shared storage required
  • Database replication over IP
  • Leverage ALL vSphere HA features, including DRS and HA
  • Readable secondary
  • Compatible with SRM
  • Protects against HW, SW and DB corruption
  • Compatible with FC, iSCSi, NFS, FCoE
  • RTO in a few seconds

Deploying AlwaysOn

  • Ensure disks are thick eagered zeroed disks
  • Create DRS anti-affinity to avoid running VMs on the same host
  • Create Windows Failover cluster – use node and file share majority
  • Create AG for database
  • Create database listener for the AG
  • Monitor AG on the SQL dashboard

SQL Server Failover Clustering

  • Provides application high-availability through a shared disk architecture
  • Must use RDMs and FC or iSCSI
  • No protection from database corruption
  • Good for legacy app support that are not mirror aware
  • DRS and vMotion are not available
  • KB article 1037959 for support matrix

Rolling Upgrades

  • Build up a standby SQL server, patch, then move DBs to standby server and change VM name/IP to production name
  • Think about using vCenter orchestrator for automating the rolling patch upgrade process

Disaster Recovery and Backup

  • VMware vCenter SRM
  • Use AlwaysOn to provide local recovery
  • Use SRM to replicate to a recovery site
  • Backup – In guest backup can increase CPU utilization