SQL 2012 Failover Cluster Pt. 12: Kerberos n SSL

KerberosSo here we are, at the end of a somewhat long journey. Yes, this is the last installment in the SQL 2012 Failover cluster series. However, two very important settings are configured. The first is Kerberos, which is the preferred authentication protocol (vice NTLM). In fact, some software will break if Kerberos is not functioning properly. Thankfully there’s a new Microsoft tool that automates that process for the most part, reducing human error.

The second major item is SSL. This is a bit tricky on a Failover cluster, since the exact same SSL certificate must be used on both nodes. To make things more difficult you need to hack the registry to get the certificate to work. And there’s a weird Unicode issue with Notepad that can fowl up your registry hack. So pay attention to the instructions, even if they seem a bit wacky. Last up is installing the latest cumulative update. This is always recommended, so that you have swatted the latest set of bugs.

Blog Series

SQL 2012 Failover Cluster Pt. 1: Introduction
SQL 2012 Failover Cluster Pt. 2: VM Deployment
SQL 2012 Failover Cluster Pt. 3: iSCSI Configuration
SQL 2012 Failover Cluster Pt. 4: Cluster Creation
SQL 2012 Failover Cluster Pt. 5: Service Accounts
SQL 2012 Failover Cluster Pt. 6: Node A SQL Install
SQL 2012 Failover Cluster Pt. 7: Node B SQL Install

SQL 2012 Failover Cluster Pt. 8: Windows Firewall
SQL 2012 Failover Cluster Pt. 9: TempDB
SQL 2012 Failover Cluster Pt. 10: Email & RAM
SQL 2012 Failover Cluster Pt. 11: Jobs n More
SQL 2012 Failover Cluster Pt. 12: Kerberos n SSL

Kerberos

One of the potentially troublesome areas of SQL is properly configuring Kerberos. Kerberos is much more secure than NTLM or SQL authentication. Some applications, such as SharePoint, require it to properly function. Other applications such as vCenter could operate with NTLM, but will take advantage of Kerberos if its properly configured.

1. Download the Microsoft Kerberos Configuration Manager from here. Be sure to get the 64-bit version.

2. Run through the installer using all default values.

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

4. On the connect page don’t enter any information.

5. Once it connects it should show both cluster nodes. Click on the SPN tab. It should list both cluster nodes.

9-28-2013 2-52-13 PM

6. If you scroll all the way to the right it will show the Status, and probably shows the required SPN is missing. If you want ignorant bliss, then just click on Fix. If you want to see actual fix click on the Generate button. The “script” is just the setspn command with the proper arguments. Nothing fancy here. Fix both nodes.

9-28-2013 2-54-21 PM

7. Open up Active Directory Users and Computers. Locate the database engine service account. Open the properties of the account and click on the Delegation tab. Select the Use Kerberos Only option, as shown below. Add a service then enter the database engine service account name. It should then return a list of two SPNs. Highlight both SPNs click OK.

9-28-2013 2-59-45 PM

8. If everything went as planned, you should now have two SPNs listed on the main Delegation tab. Close the ADUC window and return back to your SQL server.

9-28-2013 3-01-29 PM

9. In the Kerberos Configuration Manager, click on the Delegation tab. Select Refresh from the menu. You should now see Constrained to Kerberos delegation.

9-28-2013 3-08-34 PM

SSL Configuration

Normally when services connect to the SQL server they pass all data, except authentication, in the clear. Generally databases contain sensitive data, so the clear text could be a security risk. SQL server supports the usage of SSL encryption for database connections. This is simple to configure, once you get the right SSL certificate installed on each node.

Not all applications support SQL SSL connections. One culprit that comes to mind is the vCenter 5.1 SSO service. On the SQL side you can either require encryption, or make it optional. The setting is up to you. Obviously test your applications, and also see if there’s any performance hit. On modern processors with the AES-NI instruction set the overhead should be quite minimal.

As with all SSL certificates, there’s a number of ways to get them. In my environment I’m assuming a Windows Server root CA is online and configured with an appropriate template. A generic “web server” template would work, as long as it allows you to customize the certificate request (unlike the ‘computer’ template) AND allows the exporting of the private key. Both nodes MUST have the exact same certificate, or you will have a smoking hole. The SQL service checks the thumbprint to validate it is using the same certificate. You can NOT trick it.

1. Open a blank MMC and add the Certificates snap-in. Choose the Computer account. Expand the Personal object and right click on Certificates. Select Request New Certificate, as shown below.

9-28-2013 3-19-38 PM

2. Click through the wizard until you get to the Request Certificates page. What is listed here is entirely dependent on your CA’s configuration. The important fact to keep in mind here is that we need to create a certificate for the cluster name, not the SQL server’s real hostname. Applications connect to the cluster name, not an individual node. So the template you choose must allow you to customize the properties.

You should see a yellow warning under the templates that need more information, which is the kind of template you want.

9-28-2013 3-26-08 PM

3. On the Subject tab use the common name for the subject and enter the FQDN of the cluster name (e.g. D001SQL03-DB.contoso.net). If you wish to possibly connect to the server via a short name, add a DNS alternative name with the short name.

9-28-2013 3-24-08 PM

On the Private Key tab check the box to allow you to export the private key. Note that your CA might over-ride this setting, so just because you set it here doesn’t guarantee you can export it.

9-28-2013 4-12-00 PM

4. Click on the enroll button, and you should see a new certificate in your store. Do NOT close out the certificate MMC, we will use it later.

9-28-2013 3-28-50 PM

5. Right click on the certificate, select All Tasks, then Manage Private Keys. Give the database engine service account READ (only) access to the private key.

9-28-2013 3-29-59 PM

9-28-2013 3-30-59 PM

6. Now you need to export the certificate WITH the private key. Right click on the Certificate and from the All Tasks menu select Export. Check the box to export the private key. Leave the default file format. Secure the key with a password or select your AD account (new to WS2012). Save it to a file and copy the file to the other node.

7. Open the MMC snap-in and connect to the computer’s certificate store. In the Personal node right click on the Certificates node, select All Tasks, then Import. Run through the import wizard, select your key file, and you should get the key imported, and probably your root CA as well. You can delete the root CA certificate, assuming the machine already trusts your CA.

9-28-2013 4-20-29 PM

8. Like you did on the first node, give the SQL database engine service account read permissions to the SSL certificate.

9. Open the SQL Server Configuration Manager and expand the SQL Server Network Configuration container. Right click on Protocols for MSSQLSERVER. If you want to force encryption set the option shown below.

9-28-2013 3-39-15 PM

10. Unfortunately for a clustered SQL instance the Certificate tab will NOT list your certificate issued to the cluster name. We will need to hack the registry to input the proper thumbprint. Back in the certificate MMC double click on your cluster certificate and scroll down to the Thumbprint field. Copy the thumbprint to the clipboard and paste it into an empty notepad window. Remove all spaces.

This is VERY important: Save the notepad file as a .txt file. Click OK when you get the Unicode error. CLOSE Notepad and re-open your thumbprint file. Remove the question mark at the beginning of the file. Copy the remaining characters to the clipboard.

If your registry key has the hidden character the SQL services will fail with the following error:

TDSSNIClient initialization failed with error 0xd, status code 0x38. Reason: An error occurred while obtaining or using the certificate for SSL.

9-28-2013 3-48-45 PM

11. Navigate to HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<instance>\MSSQLServer\SuperSocketNetLib\Certificate. Paste the thumb print into the registry key, as shown below.

9-28-2013 3-53-06 PM

12. Repeat the registry modification on the second node, using the same “clean” thumbprint.

13. In the Failover Cluster Manager locate the SQL role, then change the node SQL is running on. Move the service back and fourth twice, making sure it starts without errors on both nodes.

 9-28-2013 4-43-44 PM

14. Now that SQL SSL is configured you can change the Connect to Server options to use encryption. Click on Options then check the Encrypt connection box. If you do that, then you need to connect to the SQL server using the FQDN. If you use the short name then you will probably get the following error:

A connection was successfully established with the server, but then an error occurred during the login process. Provider: SSL Provider, error: 0 – The target principal name is incorrect.

While you may think that’s a Kerberos problem, it’s really a certificate issue. Connect with the FQDN, as shown below.

9-29-2013 8-15-16 PM

Cumulative Updates

Cumulative updates are roll-up bundles of fixes between service packs. They are tested, and production ready. In come cases software will require you to be at a certain CU level, or later. As of this writing CU #6 was available for SQL 2012 SP1. The easiest way to find the latest CU is just to Google. You can download CU6 here.

Summary

This is the last installment in the SQL 2012 Failover cluster series. There’s a few more things you could do, such as configuring cluster aware Windows updates (a cool new WS2012 feature). But I’m chomping at the bit to start on the vCenter 5.5 installation series, so that’s all for SQL for now.

As a reminder, I’m NOT a DBA, and these instructions are aimed at your average administrator that doesn’t have access to a DBA, yet wants to setup a SQL failover cluster. I’m sure a professional DBA would configure the system differently, and tailored for the environment. Don’t expect this setup to support your enterprise ERP system, as it would NOT. But, it should be a good starting point for vCenter 5.5 or other similar applications. Memory and disk performance are key, in addition to thorough testing. Documenting performance baselines is very important, should users complain the system is “slow” down the road. You then have a good baseline to compare to, which can help pinpoint performance issues.

SQL 2012 Failover Cluster Pt. 11: Jobs n More

9-26-2013 8-57-52 PMThe SQL 2012 failover cluster series is finally starting to wind down. While this is not the last installment, it’s pretty darn close. In this session we configure some critical SQL maintenance checks and run a configuration check script to see how badly our SQL server is configured. A big shout out to Brent Ozar and Ola Hallengren for their outstanding SQL Server scripts and blog resources. These scripts are must-haves for all SQL installations.

Blog Series

SQL 2012 Failover Cluster Pt. 1: Introduction
SQL 2012 Failover Cluster Pt. 2: VM Deployment
SQL 2012 Failover Cluster Pt. 3: iSCSI Configuration
SQL 2012 Failover Cluster Pt. 4: Cluster Creation
SQL 2012 Failover Cluster Pt. 5: Service Accounts
SQL 2012 Failover Cluster Pt. 6: Node A SQL Install
SQL 2012 Failover Cluster Pt. 7: Node B SQL Install
SQL 2012 Failover Cluster Pt. 8: Windows Firewall
SQL 2012 Failover Cluster Pt. 9: TempDB
SQL 2012 Failover Cluster Pt. 10: Email & RAM
SQL 2012 Failover Cluster Pt. 11: Jobs n More
SQL 2012 Failover Cluster Pt. 12: Kerberos n SSL

SQL Maintenance Script

Many people, it seems, just install SQL server and forget it about. They don’t realize that SQL server is just like a race car. If you leave it alone and don’t do routine maintenance it will fall apart. To that end, Ola Hallengren has the de-facto database maintenance scripts, that I’ve been told Microsoft uses extensively internally.

Download the MaintenanceSolutions.SQL script from here. Just run the script in SQL studio and it will create a number of maintenance jobs for you. It gets regular updates, so check back every few months for improvements. After you run the script you should see the following list of jobs under the SQL Agent.

9-26-2013 7-53-54 PM

SQL Blitz Script

I first heard Brent Ozar at TechEd a couple of years ago in a SQL performance session. Besides being able to double as a stand up comedian, he’s one of the world’s few Microsoft Certified SQL Masters and an outstanding speaker. Much of the material for this series came from his site. He has some really funny tweets.

Besides having a blog full of great material, his team has developed the sp_BLITZ script. It performs dozens of SQL configuration checks and warns you about potential issues. This script is also regularly updated to add new checks and support new SQL versions. Plus his site has links to many common issues that the script highlights and provide remediation guidance. You can download the script here.

After you download the script, execute it in SQL studio. You won’t see much happen, since this execution just installs the stored procedure it doesn’t actually run it. We will execute the checks a bit later.

Database Integrity Checks

1. In SQL Server Management Studio locate the SQL Server Agent node. Right click on DatabaseIntegrityCheck – SYSTEM_DATABASES and select Properties.

2. On the Schedules page configure the daily job as shown below, and then click OK.

9-26-2013 8-13-26 PM

And the end result is….

9-26-2013 8-14-22 PM

Click on the notifications page and enable email and application log events. I suggest ‘when the job completes’ email alerting.

9-26-2013 8-16-19 PM

3. Repeat the same configuration process for DatabaseIntegrityCheck – USER_DATABASES but stagger the job run, say an hour later.

4. Run both jobs.

Index Optimizations

1. In SQL Server Management Studio locate the SQL Server Agent node. Right click on IndexOptimize – USER_DATABASES and select Properties.

2. On the Schedules page configure the weekly job and then click OK.

9-26-2013 8-21-42 PM

Configure the same notification settings…

9-26-2013 8-22-54 PM

Database Backups

1. Turning on backup compression is recommended. Do this by running the following SQL query.

USE master;
GO
EXEC sp_configure "backup compression default", "1";
RECONFIGURE WITH OVERRIDE;

2. In SQL Server Management Studio locate the SQL Server Agent node. Right click on DatabaseBackup – SYSTEM_DATABASES – Full and edit the job steps. Open step 1 and modify the backup path as needed. In my case I changed it to S:\Backup. This should be on the S drive, so that backups are available to each node.

9-26-2013 8-34-10 PM3. Modify the other backup scripts to change the path as well.

4. Execute the DatabaseBackup – SYSTEM_DATABASES – Full job and validate that it runs successfully.

SP_Blitz

Now that we’ve performed backups and integrity checks, its time to run Brent’s store procedure. Type exec sp_blitz and review the output at the bottom of the screen. Not all issues need to be corrected. Some are informational. Brent has a lot of detail for some issues the script finds here. I would run this on a periodic basis, and make sure you check his site for the latest version.

9-26-2013 8-49-26 PM

Summary

In this section we have installed scripts from two of the foremost SQL authorities in the world. We also configured scheduled maintenance jobs to kick off to preserve DB performance and validate their integrity. In the last installment of this series we will configure Kerberos and SSL before calling it a day. Check out Part 12 here.

SQL 2012 Failover Cluster Pt. 10: Email & RAM

9-26-2013 5-26-56 PMIn this installment of the SQL 2012 failover cluster guide we are configuring two very important parameters: SQL server max memory and email alert notifications. Both settings are often overlooked, yet are critical to a properly performing and healthy SQL server cluster.

Blog Series

SQL 2012 Failover Cluster Pt. 1: Introduction
SQL 2012 Failover Cluster Pt. 2: VM Deployment
SQL 2012 Failover Cluster Pt. 3: iSCSI Configuration
SQL 2012 Failover Cluster Pt. 4: Cluster Creation
SQL 2012 Failover Cluster Pt. 5: Service Accounts
SQL 2012 Failover Cluster Pt. 6: Node A SQL Install
SQL 2012 Failover Cluster Pt. 7: Node B SQL Install
SQL 2012 Failover Cluster Pt. 8: Windows Firewall
SQL 2012 Failover Cluster Pt. 9: TempDB
SQL 2012 Failover Cluster Pt. 10: Email & RAM
SQL 2012 Failover Cluster Pt. 11: Jobs n More
SQL 2012 Failover Cluster Pt. 12: Kerberos n SSL

SQL Server Memory

SQL server is a database server (duh), and as such can use considerable memory. The more memory the SQL server process has, the more it can cache and thus improve performance and reduce IOPS. By default when SQL is installed it places no memory cap on itself and could use up nearly all the system memory, leaving precious little for the OS or other apps. This is even more of a problem if you enable large page tables (a best practice), since it could cause more memory starvation problems.

What’s the solution? You should always configure the SQL server’s max memory setting. This sets the upper limit on how much buffer memory the SQL server will use. By configuring this properly you ensure the OS or other local apps are not starved for memory. Set this too small and you could be “wasting” the extra memory. If you are running other services like full text search you will want to assign less memory to SQL.

When provisioning a new SQL server VM you need to be mindful of three memory settings: The guest VM’s memory size you configure in vCenter, SQL server’s maximum memory setting, and any VMware reservations that you configure. For all the gory details on SQL server memory, Brent Ozar has an outstanding post here. But to suffice it to say, if you reserve 100% of the VM’s memory at the VMware layer, then really just need to worry about the SQL maximum memory size. For a barebones SQL server (no other services like full text index, reporting services, etc.) the following table a decent starting place. I like to leave 2-4GB for the OS, and provision the rest to SQL server.

9-26-2013 5-24-58 PM

Configuring the maximum is easy through the GUI. Open Microsoft SQL Server Management Studio and open the server properties. Go to the Memory configuration page and then enter the appropriate value.

9-26-2013 5-29-28 PM

E-Mail Alerting

Monitoring a SQL server is extremely important. While there are many tools to do so, you can easily configure free email alerting. In conjunction with a database maintenance script that I’ll cover later, you can easily configure daily job alerts and make sure your SQL server is not misbehaving. In this section we will merely configure SQL server to send alerts, and the next installment we will discuss DB maintenance plans.

1. In SQL Server Management Studio Expand SQL Server Agent. Right click on Operators and select New Operator.

9-26-2013 6-06-04 PM

2. Use a Name like DBA Team, and configure the E-mail name to the distribution list for your SQL server team. An example is shown below. Close the New Operator page. Note: The script at the end of this post assumes an operator name of “DBA Team”, but you can search and replace inside the script if you wish to use a different name.

9-26-2013 6-26-58 PM

3. Expand the root Management node and right click on Database Mail. Select Configure Database mail.

9-26-2013 6-09-13 PM

4. Click through the wizard until you get to the New Profile screen (you may get a warning database mail is not enabled. Just click Yes to enable it). Enter a profile name, for example Exchange. Click Add to add the SMTP configuration.

9-26-2013 6-12-31 PM

5. Configure the SMTP properties as appropriate for your network. The preferred configuration is to use SMTP over SSL with Windows integrated credentials for SMTP authentication. However, that configuration may not be possible depending on the mail server configuration. If a SQL alerts distribution does not exist, create one, and add the appropriate people to the DL. An example configuration is below. I suggest configuring the account and display name as the SQL server name, so you can readily tell which server is sending the alert. Click Next.

9-26-2013 6-14-27 PM

6. Configure the Profile Security as shown below, making your profile the default profile.

9-26-2013 6-16-56 PM

7. Click through the rest of the wizard accepting all defaults.

8. Right click on Database Mail and select Send Test E-Mail. For the recipient enter the DBA distribution list, and verify the mail was received.

9. Right click on SQL Server Agent and open the properties. Click on Alert System and configure the mail profile as shown below. Make sure you enable the profile, select the right mail profile, then enable the fail-safe operator for e-mail alerts.

9-26-2013 6-19-23 PM

10. Cut and paste the following code into SQL server management studio and execute the query. You should not get any errors. If you didn’t use “DBA Team” for the operator name, search and replace before you run the script to avoid errors. If you do goof it up you will need to delete all of the Alerts it creates (under the Alerts node in SQL Server Agent) and re-run it.

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 016',
@message_id=0,
@severity=16,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 016', @operator_name=N'DBA Team', @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 017',
@message_id=0,
@severity=17,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 017', @operator_name=N'DBA Team', @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 018',
@message_id=0,
@severity=18,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 018', @operator_name=N'DBA Team', @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 019',
@message_id=0,
@severity=19,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 019', @operator_name=N'DBA Team', @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 020',
@message_id=0,
@severity=20,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 020', @operator_name=N'DBA Team', @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 021',
@message_id=0,
@severity=21,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 021', @operator_name=N'DBA Team', @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 022',
@message_id=0,
@severity=22,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 022', @operator_name=N'DBA Team', @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 023',
@message_id=0,
@severity=23,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 023', @operator_name=N'DBA Team', @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 024',
@message_id=0,
@severity=24,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 024', @operator_name=N'DBA Team', @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 025',
@message_id=0,
@severity=25,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 025', @operator_name=N'DBA Team', @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error Number 823',
@message_id=823,
@severity=0,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error Number 823', @operator_name=N'DBA Team', @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error Number 824',
@message_id=824,
@severity=0,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error Number 824', @operator_name=N'DBA Team', @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error Number 825',
@message_id=825,
@severity=0,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error Number 825', @operator_name=N'DBA Team', @notification_method = 7;
GO

Summary

This is a pretty long post, but the actual steps are not that difficult. Properly configuring the SQL server max memory is very important. Likewise, you should always configure email alerts for your SQL server. The alerts in the script are best practices, but your DBA may want to tweak by adding more. Even if you are using SCOM or some other system monitoring tool, I would still urge configuring server email alerts. That way if your central monitoring tool dies or gets misconfigured, you can still know if your SQL server is having problems.

Check out Part 11, which will cover backups and critical maintenance jobs.

SQL 2012 Failover Cluster Pt. 9: TempDB

9-24-2013 7-40-46 PMOne 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. Current best practices is to create 1/4-1/2 the number of TempDB files that you have processor cores. You don’t really want to go above 8 files, in most cases.

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.

The script 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 2012 Failover Cluster Pt. 1: Introduction
SQL 2012 Failover Cluster Pt. 2: VM Deployment
SQL 2012 Failover Cluster Pt. 3: iSCSI Configuration
SQL 2012 Failover Cluster Pt. 4: Cluster Creation
SQL 2012 Failover Cluster Pt. 5: Service Accounts
SQL 2012 Failover Cluster Pt. 6: Node A SQL Install
SQL 2012 Failover Cluster Pt. 7: Node B SQL Install
SQL 2012 Failover Cluster Pt. 8: Windows Firewall
SQL 2012 Failover Cluster Pt. 9: TempDB
SQL 2012 Failover Cluster Pt. 10: Email & RAM
SQL 2012 Failover Cluster Pt. 11: Jobs n More
SQL 2012 Failover Cluster Pt. 12: Kerberos n SSL

Configure TempDB and More

1. 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.

9-24-2013 8-03-18 PM

9-24-2013 8-05-57 PM

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

/* Configure TempDB */

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

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

ALTER DATABASE tempdb
ADD FILE
(
 NAME = tempdev4,
 FILENAME = 'F:\TempDBData\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

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.

What’s next? Configuring SQL RAM and setting up email notifications in Part 10.

SQL 2012 Failover Cluster Pt. 8: Firewall

9-22-2013 9-10-20 PMI’m a big proponent of using the Windows firewall on all computers, be they clients or servers. It may not be perfect, but it’s better than nothing. Layered defenses are key in the enterprise, so protecting each host may save your bacon some day. Many programs automatically add Windows firewall rules, so it’s not a huge burden to enable.

If you have the Windows firewall enabled (which I strongly recommend), then you will need to allow both SQL servers to communicate with each other so that you can launch SQL Server Studio on either node and connect to the active node. If you don’t do this, you may get a timeout error. I like to make firewall rules as specific as possible, so I include the path to the program and require specific remote IP(s) that needs access. No Any-Any here!

For ease of installation, I’ve also added a line to the script that configures the SQL large pages trace flag. If you wish to use large pages and your VM has 8GB or more of RAM, you can leave the script as is. If you don’t want to use large pages, then just comment out the line. The SQL services will need to be restarted to take advantage of the added trace flag.

Blog Series

SQL 2012 Failover Cluster Pt. 1: Introduction
SQL 2012 Failover Cluster Pt. 2: VM Deployment
SQL 2012 Failover Cluster Pt. 3: iSCSI Configuration
SQL 2012 Failover Cluster Pt. 4: Cluster Creation
SQL 2012 Failover Cluster Pt. 5: Service Accounts
SQL 2012 Failover Cluster Pt. 6: Node A SQL Install
SQL 2012 Failover Cluster Pt. 7: Node B SQL Install
SQL 2012 Failover Cluster Pt. 8: Windows Firewall
SQL 2012 Failover Cluster Pt. 9: TempDB
SQL 2012 Failover Cluster Pt. 10: Email & RAM
SQL 2012 Failover Cluster Pt. 11: Jobs n More
SQL 2012 Failover Cluster Pt. 12: Kerberos n SSL

1. Run the script on both nodes, using the IP address of the opposite node as the argument.

# Configures Windows Server firewall for SQL 2012
# Requires a single argument, the IP address of the other cluster node
# Usage: SQL-Firewall.ps1 10.10.10.10
$RemoteIP = $args[0]

New-NetFirewallRule -DisplayName “SQL Server (TCP-in)” -Direction Inbound –Protocol TCP –LocalPort 1433 -Action allow -profile Domain -Program "D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -RemoteAddress $RemoteIP -description "Allows inbound Microsoft SQL connections."

New-NetFirewallRule -DisplayName “SQL Server Browser (UDP-in)” -Direction Inbound –Protocol UDP –LocalPort 1434 -Action allow -profile Domain -Program "D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -RemoteAddress $RemoteIP -Description "Allows inbound Microsoft SQL browser connections."

# Configures trace flag for enabling SQL large pages
set-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQLServer\Parameters" -name SQLArg3 -value "-T834"

9-22-2013 8-44-13 AM
2. If all went well you will now have two new rules in the Windows Advanced Firewall, as shown below. Since the rule only allowed the partner node access, you will need to modify the Remote IP Address on the Scope tab to allow your application servers (such as vCenter) to access the SQL service. Again, I don’t like wide open firewall rules, so try and use IPs vice whole subnets or “any”.

9-22-2013 8-29-52 AM
9-22-2013 8-30-52 AM

3. Now on both nodes fire up the SQL Server Studio and connect to the cluster name (e.g. D001SQL03-DB) and verify it can connect. If you get a timeout error then your firewall rules are botched up. They take effect immediately, so no need to reboot.

Summary

Now that the firewall is configured on both nodes, next up we need to do things like configure TempDB, max memory, email alerting, and DB maintenance plans. So check out Part 9 here.

SQL 2012 Failover Cluster Pt. 7: Node B SQL Install

9-22-2013 8-38-17 PMYes, we are finally at Part 7 of the SQL 2012 failover cluster install. Now that your first node is healthy, we can join the second node to the cluster. The installer process is fairly different, since it will detect your existing cluster and ask you far fewer questions. It also produces a .INI file of your settings, in case you need to re-install or want it for DR purposes.

Don’t stop here, and think all of your work is done. Yes the cluster will be up and running at the end of this post, but there’s still a lot of best practices left to configure such as email error alerts, Kerberos, SSL certificate, etc. Stay tuned for several more installments to make your SQL cluster more enterprise-grade.

Blog Series

SQL 2012 Failover Cluster Pt. 1: Introduction
SQL 2012 Failover Cluster Pt. 2: VM Deployment
SQL 2012 Failover Cluster Pt. 3: iSCSI Configuration
SQL 2012 Failover Cluster Pt. 4: Cluster Creation
SQL 2012 Failover Cluster Pt. 5: Service Accounts
SQL 2012 Failover Cluster Pt. 6: Node A SQL Install
SQL 2012 Failover Cluster Pt. 7: Node B SQL Install
SQL 2012 Failover Cluster Pt. 8: Windows Firewall
SQL 2012 Failover Cluster Pt. 9: TempDB
SQL 2012 Failover Cluster Pt. 11: Jobs n More
SQL 2012 Failover Cluster Pt. 12: Kerberos n SSL

Node B SQL Installation

1. Mount the SQL server ISO to your second VM. Launch the SQL installer page and select Add node to a SQL server failover cluster. After the installer bootstrap files install (download any updates from the internet if prompted), you should get to a Cluster Node Configuration screen. I actually did my first install on node B, so that’s why you see node A listed below as “Name of this node”. Your entries will likely be reversed. Do not panic.

9-22-2013 7-31-12 AM

2. It should automatically detect your network configuration and it won’t let you change it, so just click Next.

9-22-2013 7-34-10 AM

3. You will need to re-enter the service account passwords, so do that here.

9-22-2013 7-35-04 AM

4. Click through the rest of the wizard and on the Ready to Add Node it will give you the path to the .INI configuration file. I would recommend you save that file to a safe place, such as your D: drive. It’s not required, but should you have a failure you can re-run the install with those settings. It can also help for DR purposes, to rebuild your cluster with the same config.

9-22-2013 7-39-13 AM

5. After a few nail biting minutes, you should get a screen with a long list of green checks. Your second node is now up!

9-22-2013 7-48-54 AM

Summary

Congratulations! However, we are far from done. So don’t just call it a day and think that your SQL server is done. We need to configure firewall rules, email alerting, database maintenance jobs, and do other tweaking along the way. Those steps and more will be covered in Part 8 and beyond.

SQL 2012 Failover Cluster Pt. 6: Node A SQL Install

9-22-2013 7-53-10 PMThis is Part 6 of the SQL failover cluster installation, where we finally get to install the SQL Server database engine and related tools. It’s been a tedious road, and you likely ran into some cluster creation issues. This is not for the faint of heart, and should not be done casually. But yet we plow forward and get to finally laying down the SQL binaries to disk. Technically you can have a cluster with one node, so SQL Server Manager and even applications could connect once you finish this installment. Obviously you don’t want to yet start hosting app data, but testing out connectivity with some SQL tools is a very good idea.

Blog Series

SQL 2012 Failover Cluster Pt. 1: Introduction
SQL 2012 Failover Cluster Pt. 2: VM Deployment
SQL 2012 Failover Cluster Pt. 3: iSCSI Configuration
SQL 2012 Failover Cluster Pt. 4: Cluster Creation
SQL 2012 Failover Cluster Pt. 5: Service Accounts
SQL 2012 Failover Cluster Pt. 6: Node A SQL Install
SQL 2012 Failover Cluster Pt. 7: Node B SQL Install
SQL 2012 Failover Cluster Pt. 8: Windows Firewall
SQL 2012 Failover Cluster Pt. 9: TempDB 
SQL 2012 Failover Cluster Pt. 10: Email & RAM
SQL 2012 Failover Cluster Pt. 11: Jobs n More
SQL 2012 Failover Cluster Pt. 12: Kerberos n SSL

Node A SQL Installation

1. Mount the SQL Server SP1 ISO and start the installer process. Click on Installation in the left pane then select New SQL Server failover cluster installation.

9-21-2013 5-59-24 PM

2. The SQL setup routine will install some files. It may also prompt you to download some product updates (which I would do). Once that completes you eventually get to the Setup Support Rules screen. Review all warnings and failures. Warnings for MSDTC appear to be normal, and if you had any cluster validation warnings that will trigger another warning message.

9-21-2013 6-36-18 PM

3. Enter/Accept the product key and thoroughly read the license terms. When you get to the Setup Role select SQL Sever Feature Installation.

4. Select the SQL instance features that you want to install. Shown below I’ve selected the minimum feautures that I’ve found most applications need. YMMV, so add additional components as needed. Don’t just click everything, as that will waste disk space, increase attack surface area, and bloat your system. Remember to change the default drive path for the binaries. I always put them on the D drive, NOT your C drive. That’s for your OS.

SQL 2012 features

5. Configure the SQL server network name and again change the instance root directory to the D drive. For the SQL server network name use the name of the computer object you created earlier in this series. It must be the exact same name, or the cluster build process will fail. Verify the SQL server directory is also on the D drive, which it will be if you configured it in the last step.

6. After the disk space validation screen you will see a resource group screen. Don’t panic if there are no qualified groups. In my experience this is normal. Click Next.

9-21-2013 6-47-35 PM

7. If you followed my disk layout exactly, then you should have two cluster disks available in the upper pane for selection. These should be the F and S drives. The other drives are mount points on the F drive, and thus you can’t select them. Check the two available disks.

SQL disk selection

8. Configure the network settings for the SQL network name. Enter an IP address and ensure the proper Network is listed. Make 100% sure the IP is not already in use.

9-21-2013 7-40-30 PM

9. The service accounts as required for your environment. If you want to change the database collation (e.g. you don’t live in the US), you can do that on the second tab.

9-21-2013 7-44-09 PM

10. On the next screen you have to choose the SQL server administrators. Don’t be lazy and just select the current user option. Add your AD SQL group that we created earlier.

9-21-2013 7-50-22 PM

11. You need to configure the data directories as all of them must be located on your shared LUNs. Following my mount point design I setup the directories as shown in the screenshot below.

9-21-2013 8-02-43 PM

12. Click through the wizard until you get to the Ready to install summary screen. You will see a long path to an .INI file. This is essentially your answer file that captured all of your settings, minus passwords. Copy the .INI to a safe location in case you have an install failure, or for DR purposes. You can’t use it on the second node, since the config steps are very different. Click Install and wait a few minutes.

9-21-2013 8-04-15 PM

13. If you are following this guide exactly and using mount points, then you will likely run into an obscure permissions issue. A scary window should pop up with an error: updating permissions setting for the file resumekeyfilter.store failed. Do NOT, I repeat, do NOT click Cancel. We can fix this problem and the installer will resume.
resumekeyfilter.store

14. If the permissions problem does appear, then navigate to your mount path and change the folder options to show protected operating system files.

9-22-2013 6-23-44 AM

Not so magically you should now see the System Volume Information folder in each of your mount point folders. Open the properties of the file and seize ownership. In my lab I changed the owner to the domain administrator account. A less powerful account could possibly be used, so if you aren’t a domain admin then try a local administrator account.

9-22-2013 6-19-15 AM

After you change the ownership then System was the only entry (ACE) in the access control list. Repeat this process for each of the System Volume Information mount point folders. Click Retry in the SQL dialog box. Pay careful attention to the path in the error, as you may have correctly fixed one file but botched up another, so don’t get frustrated “fixing” the wrong file permissions. I had to fix all four mount point files.

9-22-2013 7-06-23 AM

15. If all goes well the installation should complete with no further errors or warnings. You should now be able to launch the SQL Server Management Studio and login to your instance. If you get an access denied message, make sure your account is a member of the SQL administrator group you made in AD, and that you’ve logged off and back on the SQL server since that change to refresh your group membership.

Now that Node A is up and running, it’s time to install SQL server on node B to complete the cluster. That is covered in Part 7.

SQL 2012 Failover Cluster Pt. 4: Cluster Creation

SQL ClusterThis is part four of the SQL 2012 failover cluster installation series. In this installment we will create the Microsoft cluster and add the storage resources. If you are using two VM NICs then we will also tweak their configuration. While this process is fairly straight forward, it can be fraught with problems. If everything goes well you are golden, and if not, then you may be pulling out some hair.

SQL 2012 AlwaysOn Availability Groups are much easier to configure, so I really hope VMware certifies AlwaysOn AGs for the vCenter database in the near future. I’ll have to amend this SQL series, but that’s a small price to pay for the support.

If you do run into problems with forming the cluster, carefully review all logs for clues to what is broken. Generally they will point you in the right direction.

Blog Series

SQL 2012 Failover Cluster Pt. 1: Introduction
SQL 2012 Failover Cluster Pt. 2: VM Deployment
SQL 2012 Failover Cluster Pt. 3: iSCSI Configuration
SQL 2012 Failover Cluster Pt. 4: Cluster Creation
SQL 2012 Failover Cluster Pt. 5: Service Accounts
SQL 2012 Failover Cluster Pt. 6: Node A SQL Install
SQL 2012 Failover Cluster Pt. 7: Node B SQL Install 
SQL 2012 Failover Cluster Pt. 8: Windows Firewall
SQL 2012 Failover Cluster Pt. 9: TempDB
SQL 2012 Failover Cluster Pt. 10: Email & RAM
SQL 2012 Failover Cluster Pt. 11: Jobs n More
SQL 2012 Failover Cluster Pt. 12: Kerberos n SSL

Cluster Creation

1. Download the hotfix described in this Microsoft KB article. Install it on both nodes and reboot them. This prevents the cluster manager MMC from crashing on you. The comical error is: A weak event was created and it lives on the wrong object, there is a very high chance this will fail, please review and make changes on your code to prevent this issue.

2. From the Server Manager dashboard launch the Failover Cluster Manager tool. Right click on the Failover Cluster Manager in the left pane and select Validate Configuration. Enter the hostnames of your two SQL servers and click through the rest of the wizard.

9-21-2013 2-25-12 PM

3. After a few minutes of your heart pounding and wondering if you did everything right you will get the validation results. A perfect score would be all green, in which case you win a gold star for the day.

Unfortunately my config had three warnings. Two were due to iSCSI IP addressing because I haven’t yet separated my iSCSI traffic on a dedicated VLAN so both VM NICs were on the same subnet. The last one was a MPIO warning stating that the LUNs had a single path. Yes, I knew that, thank you.

Those are not show stopper issues so I was pleased that everything else checked out. Review ALL warnings/errors and resolve them if they really apply to you.

9-21-2013 2-34-33 PM

4. If all went well then the Create Cluster Wizard window will appear before your very eyes. You will need to configure a cluster name and an IP address. Here I choose D001SQL03 for my cluster name (see why I appended the “A” and “B” to my hostnames?).

9-21-2013 2-37-17 PM
5. In the final step you will see your configuration settings and a box that is checked which will add all applicable storage to the cluster resources. DO NOT check the box that automatically adds storage to the cluster. It will want to add the local D drive as a cluster storage resource, which clearly causes issues.

9-21-2013 2-39-31 PMA

6. In a few minutes, if the stars are aligned, your cluster is now formed. I got two warnings, but that was due to checking the add storage box in the previous step, so don’t do it.

9-21-2013 2-45-57 PM

7. Expand the Storage node of the Failover Cluster Manager, click on Disks, then select Add Disk. Add all of your shared iSCSI volumes. If all goes well you should see an online status for all volumes.

9-21-2013 3-18-44 PM

8. If you configured your VM with two NICs (that are on different subnets), then you should see TWO network resources in the left pane under Networks. This screenshot was taken after I did some reconfiguration.

9-21-2013 7-21-30 PM

Open your first network, and if that is associated with your iSCSI network, then change the name and do not allow cluster network communications on that network.

cluster network

If the network is your routed production network then make sure the allow clients to connect option is chosen. Rename the network as well.

9-21-2013 7-18-00 PM

After your reconfiguration one network should be enabled for cluster use and the other one disabled.

9-21-2013 7-25-23 PM

8. Poke around in the various areas within the Failover Cluster Manager and validate that everything is healthy and looks OK. If the MMC crashes on you, you probably skipped step 1 in this post.

9. To help head off cluster problems when SQL is being installed I’ve found it most beneficial to pre-create the SQL server cluster computer object and set the proper permissions. Otherwise the MSCS computer object needs AD permissions such as creating computer objects, which can be forgotten or hard to delegate in the enterprise. This will most certainly cause your cluster to die.

In Active Directory create a new computer object. The name should be that of the SQL cluster name, which applications will be connecting to. For example, I called mine D001SQL03-DB. After you create the new computer object, you need to change the permissions on the computer and give the MSCS cluster name (e.g. D001SQL03) full control. The first screenshot shows the cluster name (which is the same as the cluster computer name) that needs to be delegated the permissions.

9-22-2013 5-01-46 PM

9-22-2013 4-56-37 PM

Now that the cluster is formed, and in a healthy state, it’s time to create the service accounts and then start the actual SQL installation. Want more? Check out Part 5.

SQL 2012 Failover Cluster Pt. 3: iSCSI Config

QNAP

This is Part 3 of the SQL 2012 failover clustering series we will configure the required LUNs and iSCSI components, so that Windows Server 2012 can mount the shared LUNs. As I mentioned before, I’m choosing to do in-guest iSCSI for the shared LUNs vice RDMs. RDMs are acceptable as well, and there’s no one right or wrong answer here.

On larger SQL servers you will quickly run out of drive letters, so we will be using mount points for the majority of the volumes we create. This minimizes drive letter usage and allows seamless future expansion without worrying about what letter to use, or running out. CSVs (cluster shared volumes) also appear to be an option, but honestly I’ve never used them.

On a security note, iSCSI supports various kinds of CHAP authentication. Depending on your array vendor, if may or may not support CHAP. If you value security then I would encourage you to enable mutual chap, so both your target and initiator can authenticate each other. Some vendors also limit the CHAP password complexity, so if you have problems with CHAP authentication, try simpler passwords like limiting them to all upper case letters and numbers (yes, I’m speaking to you QNAP).

Blog Series

SQL 2012 Failover Cluster Pt. 1: Introduction
SQL 2012 Failover Cluster Pt. 2: VM Deployment
SQL 2012 Failover Cluster Pt. 3: iSCSI Configuration
SQL 2012 Failover Cluster Pt. 4: Cluster Creation
SQL 2012 Failover Cluster Pt. 5: Service Accounts
SQL 2012 Failover Cluster Pt. 6: Node A SQL Install
SQL 2012 Failover Cluster Pt. 7: Node B SQL Install 
SQL 2012 Failover Cluster Pt. 8: Windows Firewall
SQL 2012 Failover Cluster Pt. 9: TempDB
SQL 2012 Failover Cluster Pt. 10: Email & RAM
SQL 2012 Failover Cluster Pt. 11: Jobs N More
SQL 2012 Failover Cluster Pt. 12: Kerberos n SSL

LUN Design Considerations

First let me say that SQL LUN design is HIGHLY depending on your workload, number of SQL instances, database size, number of databases, etc. You must understand the business and performance requirements, or you are setting yourself up for failure.

I don’t claim to be a DBA, so consult your nearest DBA for configuration details specific to your environment. However, what I have found that works well for smallish SQL servers that have a single instance are the following volumes/LUNs:

  • Quorum – Needed for MS failover clustering
  • Database – Database files
  • Database Logs – Database log files
  • TempDB – SQL temporary database (may see high I/O depending on apps)
  • TempDFB Logs – Temporary database long files (may see high I/O depending on apps)
  • Backup – Working space for SQL-native backup files

If you are building a true enterprise SQL server with multiple instances, you will very likely have a lot more volumes. For the purposes of my small lab and supporting vCenter 5.5, I kept the LUNs small. One trick that I’ve learned over the years to help keep your LUNs straight is making them all slightly different sizes. This is particularly true if you have multiple SQL instances on a server that use different LUNs. If you look in the Windows storage manager and see 10 LUNs of the same size, but they are backed by different storage QoS, it’s hard to match them all up. So I vary the sizes by 1-2 GB to keep them unique.

For my home lab I configured the following LUNs on my QNAP array. As you can I see I varied the size of all the LUNs so I can quickly match them up with their intended purpose inside Windows. Same logic would apply to RDMs. As with any block storage, you should configure array-side LUN masking so that only the authorized systems can access your LUNs.

9-21-2013 12-02-41 PM

I won’t go into depth about the RAID levels and number of IOPS you should design for. Modern storage arrays are complex beasts, IOPS requirements wildly vary, and you must know your application requirements. Generally RAID-1 for the SQL logs and RAID-5 for the databases is recommended. Again, consult your DBA and storage architect for the right answers.

New fangled storage technology like PernixData and VMware Virsto can do wonders for I/O performance (though they won’t help with in-guest iSCSI). That’s another reason why I’m a fan of SQL AlwaysOn replication, since you can fully virtualize all disks and use these exciting new storage technologies to increase performance. Dare I say software defined storage?

Windows Disk Configuration

1. Now that you’ve carved up your iSCSI LUNs we need to install a couple of Windows features to proceed. Run through the Add Roles and Features Wizard and add the following two Features: Failover Clustering and Multipath I/O. Do this on both of your SQL VMs.

Windows failover clustering

2. Since iSCSI networks should be non-routed for security and performance reasons, I’m going to add a second NIC to each SQL VM. If you are using the VMware distributed virtual switch (with NIOC) or Cisco UCS where you can configure QoS for various pNICs or traffic types, then I would suggest putting some thought into what makes sense for your environment. Even if you aren’t using the DVS, configure an iSCSI port group on your vSwitch and configure it for your non-routed iSCSI VLAN. Don’t configure a vmkernel iSCSI port, since we are using in-guest iSCSI.

Once you add your second NIC to the VM, we need to make some configuration changes. First up, I would strongly recommend you rename the NICs so you don’t get them confused. I called mine Production (regular network traffic) and iSCSI. Do NOT put both NICs on the same IP subnet. Cluster services will only recognize one NIC per subnet. I faked it out by using pseudo IPs for my iSCSI network (and iSCSI actually routed through production).

9-21-2013 8-47-09 AM

3. Open the iSCSI adapter properties and un-bind the File and Printer Sharing protocol.

9-21-2013 8-44-03 AM

Configure the adapter with a static IP address and do not configure a gateway or DNS info.

9-21-2013 8-45-56 AM

Un-check the box to register the connection details in DNS. Why? You don’t want a private non-routable IP address in DNS, or clients will try to connect to the unreachable address. Clearly this would cause bizarre issues.

9-21-2013 8-45-42 AM

Do a DNS lookup of your SQL server hostname to ensure that only a single IP is returned.

4. Press the Windows key then type iSCSI. You should get a warning message that the iSCSI service is not running. Click Yes to make sure it starts every time Windows does.

9-21-2013 8-27-41 AM

5. When the iSCSI Initiator Properties window appears, enter the IP address of your iSCSI target. In my case I entered the IP address of my baby QNAP. Click Quick Connect and your array should now be listed in the discovered targets.

9-21-2013 8-57-10 AM

6. Click on the Volumes and Devices tab, and click Auto Configure. A list of LUNs should appear, that is equal to the number you exported from your array.

iSCSI LUN

7. One common “mistake” that I see people new to Windows Server 2012 is using the legacy Disk Management MMC snap-in that’s been around for 15 years. Wrong answer these days for most storage tasks. Instead, from Server Manager click on File and Storage Services, then open Disks.

Disk Manager

8. From here you should now see all of your LUNs, regardless of their state. Notice that the “number” does not correspond to the LUN IDs from your array (unless you get lucky), hence my propensity for unique LUN sizes.

9-21-2013 11-37-59 AM

Don’t fall into the “click next” trap here and bring all of the disks online with their default values. No no! Another performance optimization is choosing the proper NTFS allocation size based on the LUN’s intended usage. For our D drive, where the SQL binaries are installed, we will select the default size.

However, for all other LUNs (database, logs, and tempdb) we will use 64K allocation size. This better matches the SQL server I/O size, and is generally more efficient. The new 2012 disk wizard also initializes all disks as GPT, which is required to go beyond 2TB. Even for smaller LUNs I now always use GPT, instead of the legacy MBR format. The only LUN configured as MBR is the boot volume. Short name generation is also disabled in the 2012 wizard, and I leave that off as well for a slight performance tweak.

A summary of my proposed disk configuration is in the table below. F is a tiny partition that only serves as a mount point holder for the bigger database and log volumes. While I’m still using up a fair number of letters here, as you add more DB and log volumes those would get mounted under F.

9-21-2013 12-05-50 PM

Now go ahead and on ONE node (ONLY!!!) bring all of the volumes online, format, and mount as shown in the table above.

9. Assuming that you followed my example, if you look at the Volumes tab you should see something similar to the screenshot below.

SQL Volumes

10. Go over to your second SQL node and format your D volume (ONLY!). Do a disk rescan and validate that all of the shared volumes are OFFLINE. Do NOT bring them online. If you did bring them online on the second node, you may have corrupted the NTFS file system. So to be safe I’d unmount the volume(s) from both nodes, reset the disk config, and reformat on one node.

9-21-2013 1-39-59 PM

Multi-Pathing

Built into Windows 2012 is a multi-pathing plug-in that works with a variety of storage vendors. Most commonly this would be for Fibre Channel SANs, or physical Windows servers that have multiple NICs and storage arrays with multiple iSCSI IPs. Depending on your iSCSI infrastructure you may in fact have two paths to your LUNs. If you do, then I’ll show you how to enable MPIO and make sure it has claimed your iSCSI LUNs.

In my home lab MPIO won’t do anything for me since I have a single NIC in my VM and my QNAP has a single iSCSI target IP. I don’t like to configure unnecessary services, so if you can’t take advantage of true multi-pathing, then I would not suggest activating iSCSI MPIO.

1. In the Server Manager dashboard select the MPIO tool. Open the Discover Multi-Paths tab, check the iSCSI box, then click Add. You will then need to reboot.

9-21-2013 1-56-05 PM

2. Your reboot make take longer, so don’t worry if things seem to hang for a couple of minutes. Open Computer Management then find one of your iSCSI devices and you should see a MPIO tab. In there you can find some geeky stats and settings (which I would NOT tweak).

9-21-2013 2-00-11 PM

Summary

Ok, so that was a lot of disk configuration and it was somewhat tedious. I can certainly see why some people would just throw up their arms, install SQL on the C drive, put everything else on the D drive and call it a day. And they then wonder why SQL is slow as a snail in winter and blame it on VMware overhead. Blame yourself, not VMware. 🙂

Next up in Part 4 is configuring Microsoft failover cluster services.

© 2017 - Sitemap