SQL 2014 AlwaysOn AG Pt. 13: SSL

As we near the end of this installation series, there are a couple of final areas to cover. Up in this installment is configuring SSL. Now you may be thinking, SQL and SSL, really? Yes, for a good number of years SQL has supported the use of SSL for database connections. This enables you to encrypt from client to server all of the SQL traffic. Because we are using AAGs and two cluster nodes, configuring SSL for all of the possible connections is tricky. I will make the assumption here that you have an internal Microsoft CA which can issue certificates with SANs (subject alternative names). If this is not your situation you can still read through the post to get a feel for the configuration steps, but you will need to modify them for your environment.

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
SQL 2014 Always-On AG Pt. 12: Kerberos
SQL 2014 Always-On AG Pt. 13: SSL

SSL Configuration

1. Open a blank MMC console and add the Certificates snap-in for the local computer store.

2. Expand the Personal node, right click and select All Tasks then Request New Certificate.

2014-11-01_11-44-22

3. Select the Active Directory Enrollment Policy.

4. Now here’s where we may run into problems. On my Microsoft CA I have a template called Server Authentication – SAN. You won’t have this, but may have other templates that work. The important factor here is that we need to enter SAN values on the certificate. This will be denoted by a yellow warning under the certificate template that more information is needed. If you don’t have any templates which need this additional information, you will need to make a new template. You can do this by duplicating the web server template and making sure the supply in request option is used for the subject name.

2014-11-01_10-32-52

2014-11-01_10-36-16

5. Check the box next to your template that needs additional information and will work as a server SSL certificate.

6. Click on the yellow warning message. A window will open up requesting certificate properties. For the subject name change the type to common name and enter the FQDN of the SQL server node.

7. In the alternative name field change the type to DNS. Enter the short name of your SQL server node, plus the short name of your AAG listener  (e.g. SDSQL03-AG1L) and the FQDN of your AAG listener.

 

2014-11-01_10-44-22

8. Process through the enrollment process, and you should now have a freshly minted SSL certificate in your computer’s certificate store.

2014-11-01_11-22-53

9. Right click on the certificate, select All Tasks, then choose Manage Private Keys. Give the SQL DBE service account read (only) permissions to the certificate.

2014-11-01_11-24-39

10. Close out the certificate MMC.

11. Open the SQL Server Configuration Manager. Open the properties of the Protocols for MSSQLSERVER as shown below.

2014-11-01_11-28-11

12. Click on the Certificates tab and choose the SSL certificate from the dropdown menu. You could also go to the Flags tab and enforce encryption. I would not recommend this unless you know 100% all of your applications support SQL SSL. Many do not.

2014-11-01_11-30-24

13. Stop and restart the SQL server and ensure it starts.

14. Repeat the entire process on the second SQL node, but use the FQDN and short name of the other node during the certificate request process. Use the same AAG listener short name and FQDN.

Summary

We have now configured both SQL nodes in our AAG cluster to be SSL enabled. So if you have applications which support encrypted SSL, then you are now set. Please do note that SSL certificates and AlwaysOn listeners have extremely limited client support due to the use of SAN certificates. See this MS article for more details. Bottom line is that even though we have fully configured SSL certs on the server side, the client side may not support the SAN certificate property needed to encrypt to the AAG listener. For non-AAG enabled databases you could still encrypt by connecting directly to the SQL server node’s name.

Also remember that if you add more AAG listeners to the cluster, then you will need to re-issue the SSL certificates on both nodes to include the additional SAN short names and FQDNs.

SQL 2014 Always-on AG Pt. 12: Kerberos

We are nearing the end of the SQL 2014 AlwaysOn Availability Group series, with just Kerberos and SSL left to configure. In this installment we tackle Kerberos. Depending on your environment, you may or may not need Kerberos configured. Kerberos is only effective when using Windows authentication to your SQL server, not SQL authentication. Should Kerberos authentication fail, it will fail back to NTLM. In case you do have a multi-tiered application that needs Kerberos, let’s get it configured. Microsoft has made some of the Kerberos configuration easy, via a nice GUI tool they created. Unfortunately it is not AAG aware, so there’s still a bit of manual configuration needed. But it helps reduce human error.

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
SQL 2014 Always-On AG Pt. 12: Kerberos
SQL 2014 Always-On AG Pt. 13: SSL

Kerberos Configuration

1. Download the Microsoft Kerberos Configuration Manager for SQL server here. Install it one of your SQL servers.

2. Navigate to C:\Program Files\Microsoft\Kerberos Configuration Manager for SQL Server and launch KerberosConfigMgr.exe

3. Since we are connecting locally you won’t need to enter any connection information.

2014-10-26_19-03-23

4. Once connected, navigate to the SPN page. All the way on the right there is a Status column. Unless it says Good with a green checkmark, your SPNs are not configured. You should see a “Missing” status.

5. Click on the two Fix buttons. You should now see a status of Good.

6. Repeat the process on the second SQL server.

7. Go to an Active Directory domain controller and locate the database engine service account. In the advanced view open the Attribute Editor tab and locate the servicePrincipalName entry. Open it, and you should see four entries.

8. We need to add two SPNs, for the AAG listener. Follow the same format as the existing entries, but use the FQDN of your listener computer object. Add a second entry with the port number. You should now have a total of six SPNs.

2014-10-26_19-11-43

9. Download my Kerberos PowerShell test script from here. Copy it to a non-SQL server and run it. Enter the FQDN of the first SQL host and the FQDN of the AAG listener. You should see two Online statements. Do not run this from the SQL server, or the authentication method will be shown as NTLM. Run this on a non-SQL AAG server, please.

2014-10-26_19-19-43

10. Download my SQL authentication script from here. Open it in SQL Studio and run it. At the bottom of the screen you should see two Kerberos entries. This corresponds to the two connections made from my PowerShell script. If they are shown as NTLM, then Kerberos is not working. Re-run the PowerShell script, but this time connect to the second SQL server and validate Kerberos is working on there as well.

SQL Auth

Summary

At this point you’ve now configured Kerberos for both SQL nodes and your AAG listener. You’ve also tested it as well to ensure the configuration works. Sometimes Kerberos can be touchy, and I’ve had situations where even with all the SPNs setup correctly the listener won’t authenticate with Kerberos. So YMMV, and you may need to open a ticket with MS should it not work for you. But at least you know the process that should work for everyone.

Next up in Part 13 is configuring SSL.

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.

2014-10-12_16-58-04

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.

2014-10-12_17-10-27

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.

2014-10-12_17-14-43

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.

2014-10-12_17-50-45

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.

2014-10-12_17-57-18

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

2014-10-12_17-58-46

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.

2014-10-12_18-22-45

 Summary

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.

SQL 2014 Always-on AG Pt. 7: TempDB

2014-09-22_17-13-13One of the most important aspects of SQL performance is TempDB. Applications can use TempDB as a scratch space, and in most cases you should not rely on just a single TempDB file. If the number of vCPUs is less than 8, then configure an equal number of TempDB files as you have assigned vCPUs. If the number of vCPUs exceeds 8, then start with 8 TempDB files and look for contention for in-memory allocation (PAGELATCH_XX). Scale up the number of TempDB files in increments of four until contention is eliminated.

Another important aspect of TempDB is their size and file growth. SQL will proportionally use the TempDB databases based on their size. So it’s important to create all TempDBs with the same size and not allow for autogrow. Size them properly up front to accommodate your application requirements. If you do need to grow them in the future, grow all of them equally. All TempDBs will use a single “centralized” log file, so you won’t see multiple log files.

Just to conserve disk space the script below creates four TempDB files at 5MB each. Increase all TempDB sizes equally for your environment. I’d probably go with 1-5GB per file, if the SQL server will be under light usage and only supporting an application like vCenter. A general TempDB sizing rule of thumb is 1% to 10% of your total database size. Your transaction log file size needs to be of sufficient size to hold all the transactions between backups. This is probably daily, unless you are running a large database with more frequent backups.

The script also renames the SA account, as a security protection measure. This is optional, but recommended. It also enables DAC, which can be used for remote administration during dire SQL server problems to possibly repair the situation and get SQL server back into production.

In addition to TempDB, the script makes other minor changes to system database sizes and auto-growth. I won’t bore you with all the details, but please consult your local DBA for system DB settings specific to your environment. In the absence of a DBA the settings below are a reasonable start for a small environment.

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

Configure TempDB and More

1. Copy the script below to your first SQL server. To run the script open SQL Studio, create a new query (click “New Query”) then paste in the code below. Or save the script to a .sql file and double click on it. Execute the script and it should run successfully. Be sure to change the file paths to match your system. The script will fail if they are incorrect.

2014-09-22_17-19-05

 

/* Configures TempDB and other settings */
/* Derek Seaman, derekseaman.com */

/* Configure TempDB */

USE master;
GO
ALTER DATABASE tempdb
ADD FILE
(
NAME = tempdev2,
FILENAME = 'J:\TempDB\tempdb2.mdf',
SIZE = 5MB,
FILEGROWTH = 0
);

ALTER DATABASE tempdb
ADD FILE
(
NAME = tempdev3,
FILENAME = 'J:\TempDB\tempdb3.mdf',
SIZE = 5MB,
FILEGROWTH = 0
);

ALTER DATABASE tempdb
ADD FILE
(
NAME = tempdev4,
FILENAME = 'J:\TempDB\tempdb4.mdf',
SIZE = 5MB,
FILEGROWTH = 0
);

ALTER DATABASE tempdb
MODIFY FILE
(
NAME = tempdev,
SIZE = 5MB,
FILEGROWTH = 0
);

ALTER DATABASE tempdb
MODIFY FILE
(
NAME = templog,
SIZE = 2048MB,
FILEGROWTH = 256MB
);

/* Configure other system databases */

GO
ALTER DATABASE model
MODIFY FILE
(
NAME = modeldev,
SIZE = 10MB,
FILEGROWTH = 10MB
);

ALTER DATABASE model
MODIFY FILE
(
NAME = modellog,
SIZE = 10MB,
FILEGROWTH = 10MB
);
GO

ALTER DATABASE master
MODIFY FILE
(
NAME = master,
SIZE = 10MB,
FILEGROWTH = 10MB
);

ALTER DATABASE master
MODIFY FILE
(
NAME = mastlog,
SIZE = 10MB,
FILEGROWTH = 10MB
);
GO

ALTER DATABASE msdb
MODIFY FILE
(
NAME = msdbdata,
SIZE = 25MB,
FILEGROWTH = 10MB
);

ALTER DATABASE msdb
MODIFY FILE
(
NAME = msdblog,
SIZE = 25MB,
FILEGROWTH = 10MB
);
GO
/* Allow advanced configuration options */

USE master;
GO
exec sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

/* Rename SA Account */

ALTER LOGIN sa WITH NAME = [Matrix];

/* Enable DAC */

EXEC sp_configure 'remote admin connections', 1
GO
RECONFIGURE
GO

2. Repeat the configuration process on the other SQL node.

Summary

TempDB may be often overlooked by non-DBA people installing SQL server. Even with VMs using just 1-2 cores I would still configure a few TempDB files. Why? If the SQL server grows and you add more DBs, vCPUs, etc. you probably won’t remember to add more TempDB files. Coming up in Part 8 we will be configuring memory and email alerting. Then yes finally configuring AlwaysOn Availability Groups.

SQL 2014 Always-On Pt. 6: Cluster Config

2014-09-20_21-10-48 We’ve now come to the point where we need to install and configure the Windows cluster role. Even though SQL AAGs don’t use shared disks like a traditional failover cluster, AAGs do rely on the Windows cluster service. We will also be implementing the Cluster Aware updating feature. This is a feature new to Windows Server 2012 that does rolling Windows patch upgrades to each node, while keeping the clustered service up. This is a very handy feature. If you have an enterprise patch management tool which is cluster aware, then you could skip that section and rely on that tool instead.

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

Cluster Role Installation

1. From the Server Manager dashboard launch Add Roles and Features.

2. Select Role-Based or Feature-Based Installation.

3. Select the local server when prompted.

4. Skip the Server Roles page and on the Features page check Failover Clustering. When prompted, add the required features.

6. Continue through the rest of the wizard and wait for the installation process to complete.

7. Install Windows Failover Clustering on the second SQL node.

Cluster Validation

1. On Node A launch the Failover Cluster Manager, and in the left pane right click on Failover Cluster Manager and select Validate Configuration.

2. Enter the two hostnames of the SQL servers you are configuring.  Run all of the tests. Review the cluster report for any errors. Some warnings are normal, such as:

  • Only one pair of network interfaces
  • No disks to perform cluster validation on (and all associated disk tests)

3. Download and install Windows8-RT-KB2803748.x64.msu on both nodes if you are running Windows Server 2012 (not 2012 R2).

Cluster Configuration

1. Launch the Failover Cluster Manager. Right click on Failover Cluster Manager and select Create Cluster.

2. Add the two nodes of the cluster and click Next.

3. Enter a cluster name (e.g. SDSQL03) and the IP address of the cluster name. Click Next.

2014-09-20_20-36-42

4. Validate the cluster information is correct and click Next. Un-check the box to add all storage devices. Wait for the cluster to be created.

5. Verify the cluster was successfully formed. You can safely ignore warnings about no disks being found and regarding an even number of nodes.

6. Verify all of your drives are still visible. If they have disappeared, then go into the cluster manager and remove all drives as a cluster resource. Then open the Computer Manager and online each of the drives. Reboot the SQL server if you had to do this procedure, so SQL can startup normally.

6. Open the DNS Manager console, locate the cluster hostname (e.g. SDSQL03) in the forward lookup zone, open the record, and uncheck the box to update the pointer record. Re-check the box to create a pointer record. Verify a pointer record was created.

Cluster Aware Updating Configuration

1. In ADUC create a computer object with cluster name with a –CAU suffix (e.g. SDSQL03-CAU), in the appropriate server OU. Change the description on the object to SQL Cluster SDSQL03 Cluster Aware Updating Object.

2. Modify the security permissions on the CAU computer object to give the cluster object (e.g. SDSQL03) full control.

2014-09-20_20-55-48

3. On each node open the Windows Firewall manager and enable the two remote shutdown rules shown below.

2014-09-20_21-00-32

4. In Server Manager from the Tools menu select Cluster-Aware Updating.

5. Enter the cluster name (e.g. SDSQL03) and connect.

6. In the right pane select Analyze cluster update readiness.

7. Verify that all results show Passed, except for the machine proxy and the CAU role, which can be ignored. Close the results windows.

8. In the right pane select Configure cluster self-updating options.

9. Check the box to add the CAU role.

10. Check the box next to I have prestaged computer object for the CAU clustered role. Enter the CAU computer object name you created (e.g. SDSQL03-CAU). Click Next.

2014-09-20_21-05-25

11. Configure the schedule as determined by your environment.

12. On the Advanced Options screen check the box to require all nodes online.

13. If desired, on the Additional Options screen check the box to install recommended updates.

14. Click through the remainder of the wizard and wait for the installation to complete.

15. Re-run the Analyze cluster updating readiness. Verify everything passes, except for the proxy warning.

Summary

In this installment we’ve installed and configured the Windows failover clustering role. We’ve also optionally configured Windows cluster aware updating, which makes patching life easier. In the Part 7 we will configure TempDB. Click here for that installment.