Archives for September 2014

SQL 2014 Always-on AG Pt. 9: SQL Maintenance

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 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. 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. After we configure the Always-On Availability group we can run it again, for thorough coverage.

As always, defer to your professional SQL DBAs when configuring the following settings. Workloads vary wildly, so the settings below may not be the best for your environment. These are just a baseline configuration for simple low intensity databases.

Remember to run all the steps below on each of your two SQL nodes. They aren’t in a traditional failover cluster, so each node is independently configured. Just doing it once won’t cut it.

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 configured and scheduled maintenance jobs to preserve DB performance and validate their integrity. As I said before, defer to your in-house professional DBA for the recommended settings in your environment. These are just a starting point for a small environment. Next up in Part 10 we configure the Windows firewall, prep for AAG setup, then run the AAG setup wizard.

SQL 2014 Always-on AG Pt. 8: Max Mem & Email

In this installment of the SQL 2014 Always-on Availability how-to 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. Even if you are using a tool like SCOM or vCOPS to monitor your SQL server, I’d recommend still configuring the SQL alerting as a backup source. This way if your primary monitoring solution goes off line you have a second line of defense for getting SQL alerts.

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 Server Memory

SQL server is a database server 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 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. Remember if you increase the VM’s memory at the VMware layer, go back and adjust the SQL Max memory size or SQL won’t use it.

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. Next up in Part 9 is SQL maintenance tasks.

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.

SQL 2014 Always-On AG Pt. 5: Unattended Node B

In this fifth installment of the SQL 2015 AAG series, we do an unattended installation of Node B, using a modified INI file produced from our Node A installation. This streamlines the installation process, reduces human error, and makes your installation repeatable. This will probably be the shortest installment, since it’s dead easy!

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

Unattended Node B Installation

1. Copy the ConfigurationFile.ini file from Node A to the C drive on Node B. Open the file in your favorite editor. Near the top of the file you will see some UI and Quiet parameters. Comment out the UIMODE and QUIET mode options, and modify QUIETSIMPLE to be TRUE. Save the file. This will enable a quiet installation with a GUI status so you can monitor the progress.

2014-09-16_19-47-32

2. Mount the SQL 2014 ISO and open a command prompt. Type the following command on a single line:

setup /configurationfile=c:\ConfigurationFile.ini /iacceptsqlserverlicenseterms /SQLSVCPASSWORD =”ChangeMe

2014-09-16_20-03-10

3. Sit back for a few minutes and wait for the installation to complete. SQL will now be installed with the exact same settings as Node A.  After the installation is complete you can verify the services are using the proper service accounts. Yes, all is well.

2014-09-16_19-54-31

Summary

Using the SQL 2014 unattended installation is dead easy. There’s a lot of parameters you can configure, and you can check out the full reference here. This example used a template made from node A, and the minimum number of switches needed for a quiet install. Next up in Part 6 we will be applying best practices configuration to each node, and then later in the series actually configuring an AlwaysOn Availability group with a test database.

 

vCenter 5.5 U2 does not upport SQL AlwaysOn AGs afterall

VMware has just published a KB article (KB 2086946) [Temporally down] on using Microsoft failover clustering services to support high availability of vCenter 5.5 U2. My assumption for this support is the demise of the vCenter Heartbeat product. I appreciate VMware giving customers a new choice for vCenter high availability.

Slightly buried in this gem of a KB article, in the original version, was the new support for using SQL 2012 Always-On Availability Groups for the vCenter database! However, the KB was since pulled and I’m told by VMware it was published in error. In fact, AAGs have not been qualified and are not supported for the vCenter database. If you are a customer that wants vCenter DB SQL AAG support, then escalate to your TAM and make your opinion heard. In case you missed it, vCenter 5.5 does support the ‘traditional’ shared storage SQL cluster configuration for vCenter.

SQL AAGs are still supported on vSphere in the general sense. This new KB article was for the specific instance of the vCenter database. If you want to see the full support matrix for Microsoft high availability solutions, check out this article.

Prior to the KB being pulled it stated this:

2014-09-16_11-19-39

SQL 2014 AlwaysOn AG Pt. 4: Node A Install

2014-09-14_19-49-30This is Part 4 of the SQL Always-on Availability Group installation, where we finally get to install the SQL Server database engine and related tools. This first SQL installation will be a fully manual process where we walk through the GUI to get SQL installed. For our second node we will use an unattended installation, based on the answers captured during this install. Let’s roll!

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

Node A SQL Installation

1. Mount the SQL 2014 installation ISO to your first SQL server and launch the installer.

2. Since Always-On AGs does NOT use the traditional clustering technology or clustering services, select the SQL server stand-alone installation option.

 

2014-09-14_19-55-35

3. On the license key page make sure you have a production-quality license key. Click Next.

2014-09-14_19-57-51

4.  On the next page accept the license key and if you want, enable the customer experience improvement program. Click Next.

5. One neat feature of the SQL installer is the ability to download and apply updates as part of the installation process. In a highly managed SQL environment you probably don’t want to do this. But this is a home lab, so I’m good with any MS provided updates. Click Next.

2014-09-14_20-01-14

6.  As part of the installation the installer conducts a series of system checks. Make sure all is well here. Click Next.

2014-09-14_20-03-34

7. Next up we want to select SQL server feature installation.

2014-09-14_20-04-42

8. Next up we have to decide what SQL features we want installed. This will be very dependent on your environment. For this case I’ll just do the minimum, which is the SQL database engine and the full set of management tool. Add other services as needed. Be sure to change the installation path to the D drive, if you are following my drive letter configuration.

2014-09-14_20-06-07

9. Now we need to decide on the SQL instance details. Here I’m just using the default instance, since this is a fresh VM. Click Next.

2014-09-14_20-08-38

10. Here we need to select our service accounts. The GUI is intelligent enough to blank out the password field for the gMSA account. From the picker select your two service accounts. On the second tab you can change the collation. I’m leaving it the default, so won’t show a screenshot.

2014-09-14_20-11-48

11. Next up choosing the authentication option (Windows is the most secure), and also selecting the security group you created earlier as the SQL administrator. Click on the Data Directories tab.

2014-09-14_20-18-39

12. Assuming you are using my suggested drive configuration, we need to customize most of the install paths as shown below. Click Next.

2014-09-14_20-23-38

13. Review all of the configuration settings. Note at the bottom of the window is the path to the automatically generated .INI file. Browse to this location and copy the .INI file to a safe place. We will use this as the basis of our unattended install on the second node. Click Install and wait for it to complete.

2014-09-14_20-25-59

 Summary

In this installment we have installed the first node of our two-node SQL 2014 AlwaysOn Availability group. We’ve also captured the resulting INI file, such that we can use it to replicate our settings on the second node. This reduces human error, and speeds up the installation process. In the fifth installment we will install SQL 2014 on the second node.

 

SQL 2014 Always-on AG Pt. 3: Service Accounts

This is Part 3 of the SQL 2014 Always-On Availability group series where we setup two service accounts and a security group. One account is for the database engine and the other is for the SQL agent. In order for Kerberos to work properly the database engine account must be Active Directory based. We will also be observing the rule of least privilege. The less privileges the accounts have the more secure you are. So these accounts won’t be given local administrator rights. You don’t want your SQL service running with admin rights!

New to my SQL install series is the usage of a gMSAs (group managed service account) which are new to Windows Server 2012.  For AlwaysOn Availability Groups I use a ‘regular’ service account for the database engine and a gMSA for the SQL agent. Using regular service accounts would work for the SQL Agent, but I like using new OS features. Plus this means you have one less password to change since the gMSA automatically updates the password on a periodic basis. For those of you not familiar with gMSAs, they are special type of account which Windows automatically manages the password for and changes on a periodic basis. You need at least one 2012 domain controller, and use 2012 or 2012 R2 member servers.

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

Database Engine Account

1. Using ADUC create a service account using your naming convention. I’ll call mine SVC-D002SQL03-DBE. Set a complex password and make sure it does not expire.

2014-09-13_8-25-42

2. Security is always at the top of the list, so let’s enable AES encryption for Kerberos. I’d also set an account description, so that two years from now you know what the service account is for.

2014-09-13_8-27-07

Agent gMSA Configuration

1. On your SQL server enter the following PowerShell command:

Add-WindowsFeature -Name “RSAT-AD-PowerShell” –IncludeAllSubFeature

2. Download the free Managed Service Account GUI from here. Install it using all defaults.

3. If you have never used a gMSA before, enter the following PowerShell command on your 2012 domain controller:

Add-KdsRootKey –EffectiveTime ((get-date).addhours(-10))

3. Create a new service account. The account name MUST be equal to or less than 14 characters long. Select “Group MSA.”

2014-09-13_8-38-20

4. When prompted to assign the new service account, say Yes. Enter the two new server names of what will be your SQL AlwaysOn Group.

2014-09-13_8-47-28

User Rights

1. In order to support large page tables and instant database initialization we need to configure a couple of user rights on each node configured in the Local Security Policy. Add to the Lock Pages in Memory and Perform Volume maintenance tasks user rights the database engine service account. If you want SQL to write to the windows security log, then also add the DBE account to Generate Security Audits and Manage auditing and security log.

The lock pages in memory option is somewhat controversial. Some people to say only use it for VMs with 16GB+ RAM, others say don’t use it at all. Later on you will see that I configure the SQL Max memory parameter, which greatly minimizes the potential problems from SQL page locking. The trend seems to be you SHOULD configure it (along with SQL max memory), thus my recommendation to do so. The combination of large pages and locking pages in memory prevents the SQL server buffer pool from being paged out by Windows. This also inhibits the balloon driver, which we don’t want activated since it can tank SQL performance.

You can go ahead and configure this user right now, since large page tables have to be enabled in SQL via a trace flag.

 

2014-09-14_19

3. Reboot both nodes after you configure the user rights, or they will not take effect.

4. Do NOT, I repeat, do NOT add either service account to the local administrators group on the SQL server. This is a security risk, and the SQL service should not be elevated. The SQL server will make the needed permission changes.

5. Lastly, we will create an AD security group that will be assigned SQL administrator rights. This is far better than assigning a specific user account. I called my group SQL_Sysadmin. Add your administrator account to this group.

Summary

This is a very short and simple installment in the SQL 2014 AlwaysOn Availability group series. We created two service accounts, gave them the proper user rights, and created a security group.  Next up in Part 4 we finally get to start installing the SQL server services on the first node.

vSphere 5.5 Update 2 Released

Hot off the digital presses is vSphere 5.5 Update 2. This is a minor update, but with some important supported database updates. It’s great to see SQL Server 2014 now supported. I’m in fact surprised they supported SQL 2014 so fast, so kudos to VMware. Now if we can only get SQL AlwaysOn availability groups supported..maybe someday. You should also take note that SRM 5.8 requires vCenter 5.5 Update 2, so whenever SRM 5.8 comes out be sure to upgrade your vCenter prior to deployment. vSphere 5.5 update 2 also allows the “legacy” C# vSphere client to modify some properties (RAM, Change network port group, Remove devices, vCPU, Mount ISO, Increase disk space, reservations, Edit advanced settings) of HW v10 VMs. Thanks VMware! Full vCenter release notes are here.

What’s New

vCenter Server database support: vCenter Server now supports the following external databases:

  • Oracle 12c. Important: For pre-requisite requirements, see KB 2079443.
  • Microsoft SQL Server 2012 Service Pack 1
  • Microsoft SQL Server 2014

vCloud Hybrid Service: The vCloud Hybrid Service (vCHS) introduces a new container, Hybrid Cloud Service, on the vSphere Web Client home page. The Hybrid Cloud Service container contains the vCHS installer and the new vCloud Connector installer.

Customer Experience Improvement Program: The vSphere customer experience improvement program is introduced to collect configuration data for vSphere and transmit weekly to VMware for analysis in understanding the usage and improving the product.

And as expected, ESXi 5.5 Update 2 also came out today. No surprise here. The biggest new feature here, and which was revealed at VMworld, is the support for 6TB of RAM in an ESXi host. Not too many people will be running hosts that big, but nice to know VMware has fully tested such monster hosts. For the full ESXi release notes go here.

Each of the release notes has a very long list of resolved issues. So if you are experiencing a particular bug, be sure to see if it has been resolved in 5.5 Update 2. You may just get lucky. These updates include security patches, so better start testing them in your pre-production environments and planning prod updates in the near future. As always, test, test, test before pushing this to production hosts.

SQL 2014 Always-on AG Pt. 2: VM Deployment

First up in this series is deploying your two Windows Server 2012 R2 VMs, which will be the SQL 2014 Always-On availability group nodes. I have a standard VM template that is built in a nearly fully automated manner with Microsoft MDT 2012 Update 1. If you are still making your VM templates by hand, or some cobbled together semi-automated scripts, then check out Microsoft MDT. It’s free, lots of info on the web, and once I started using it I never looked back. Really, it’s that good (not perfect, but good).

Remember with vSphere 5.5 you should use the web client to fully configure the VM properties, including hardware version 10. In vSphere 5.5 U2 they have allowed the C# client to modify some HW v10 properties, which is a good step forward. And like the Energizer bunny, the C# client will be there in vSphere 6.0, so all is not lost. Pick your favorite client.

SQL 2014 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

Virtual Hardware

Selecting the right virtual hardware is important for the best performance. Here the major food groups are memory, compute, networking, and storage. Remember that installing SQL server inside a virtual machine should still follow many of the best practices in a physical work. Pick your best operating system, patch, make sure your storage array has sufficient IOPS, validate processor speeds, ensure physical RAM is available, etc. Virtualization doesn’t magically make these issues go away. Also remember in a virtualized world that the SQL server is sharing resources with other VMs, so don’t be a hog and overprovision vCPUs and memory, as this can negatively impact other running VMs. Be a team player!

Memory

Memory configuration is very dependent on the SQL workload and your applications. In order for SQL server to use large page tables (can help performance) the VM needs at least 8GB of RAM. In general I don’t like memory reservations, but for SQL servers I DO reserve 100% amount of the RAM, so we have guaranteed memory performance. In my small lab I configured 4GB of RAM, but for production I’d do 8GB or more.  In my VM template I also enable hot-add of memory, so that you have operational flexibility to add more RAM should your databases need it.

2014-09-08_19-19-22

vCPUs

vCPUs is also very dependent on the workload, and remember not to over allocate vCPUs as it can negatively impact performance. For this lab I did one vCPU, but in production I’d do two minimum then monitor. I do like configuring hot add of RAM and CPU, so that I can dynamically add more resources with zero downtime. So let’s turn that on now, to head off future downtime to turn that feature on. Update: As two comments have mentioned, enabling hot vCPU add disables vNUMA. While this may not be a big deal on 2 vCPU VMs, for larger SQL VMs this may negatively impact performance. So for all but the smallest SQL VMs, right size your vCPUs and don’t enable CPU hot add.

2014-09-08_19-18-19

Networking

Networking is pretty straight forward, and I always, always use the VMXNET3 adapter for Windows VMs. ‘Nuff said. Just do it. This is a virtualization 101 “tip” that everyone should follow.

Storage

I’m also a pvscsi fan, and only for VDI do I use the SAS controller. Since SQL servers can have heavy IO, I always use the pvscsi controller. I have a blog article on how to inject the VMware drivers into your Windows install ISO, here. Or you can use MDT and inject them that way, which is my new preferred method. The pvscsi controller can yield a 10% improvement in IOPS and up to 30% less CPU utilization. The default queue depth for the pvscsi controller is 256. You should not need to manually adjust the pvscsi queue depth.

The boot drive size may be a slight point of contention. My philosophy is that the C drive is for the OS, patches, and the generally the swap file. It should stay lean, not grow very much, and usually be in the 40-50GB range, at most. SQL binaries should all be installed on a separate drive, so about the only thing using up C space are patches, OS logs, and OS temp files. Those do not exponentially grow, so it’s silly to overprovision the C drive.

Another important factor to keep in mind is spreading the virtual disks across multiple pvscsi controllers. If you try and shove all your I/Os through a single virtual pvscsi card you will likely be disappointed. You can have up to four pvscsi cards per VM. In an AlwaysOn cluster I distribute the multiple disks (database, log, tempDB, etc.) across all four controllers. As an example let’s use the following disks:

C: 40GB (OS Drive) – Controller 0
D: 20GB (SQL Binaries) – Controller 0
E: 10GB (Data files 1) – Controller 1
F: 10GB (Data files 2) – Controller 1
J: 10GB (TempDB) – Controller 3
L: 10GB (DB log files) – Controller 2
T: 10GB (TempDB log files) – Controller 3
S: 15GB (Backup) – Controller 0

I recommend using the KISS principal here. Look at all of the database servers you will be deploying and see if patterns emerge around the number of disks you need and their sizes. Build a template that mirrors the most common configuration. Clearly the drive sizes above need to be adjusted for YOUR environment. 100% chance they will not be the same as I’ve listed above. Right size so that only 80% of the disk capacity is used. You want some breathing room folks. Also, when formatting these disks inside of the guest OS, use a 64KB NTFS allocation file size for all drives but C and D.

Since we are using Windows Server 2012 R2, we do NOT need to worry about partition alignment. The OS automatically aligns partitions on a 1MB boundary. This was true starting in Windows Server 2008..back in the dark ages. Finally, let’s talk about disk provisioning options. You can choose from thin, thick lazy zero, and thick eager zero. There’s almost no performance difference between thin and thick lazy zero. My preference is thick eager zeroed disks, and let your array handle compacting/eliminating the zeros. This will slow down the initial disk creation process, but after they are created no more VMFS metadata updates are needed due to an expanding VMDK if you are using thin provisioning. Metadata updates aren’t a big deal today with arrays supporting VAAI, but still does add a tiny bit of overhead. I’ve seen block arrays initialize EZT disks at greater than 10GB (yes, bytes) per second, so you may not be waiting long for your fresh EZT disks.

 2014-09-08_19-14-10

 

Hardware Summary

Here’s a quick recap of the decision highlights:

  • Use hardware version 10, when deploying on vSphere 5.5
  • Allocate 8GB or more to the VM so you can use large page tables
  • Reserve all VM memory to prevent unexpected swapping and eliminate the swap file
  • Don’t over-allocate vCPUs – Use what you will actually need
  • Configure hot add/plug of vCPUs and memory for future non-disruptive expansion
  • Use VMXNET3 NIC
  • Use four pvscsi controllers, distributing all your disks across the four controllers
  • Use eager zero thick disks
  • Remove unnecessary hardware like floppy drives and serial ports

Summary

Creating a hardware baseline for your SQL servers is very important. Understanding how the virtualized hardware behaves is key to extracting the best performance, and helping ensure you can meet your SLAs. Build a baseline SQL server VM template, which suffices for the majority of your SQL deployments. Keep it simple, and don’t go overboard with drives. Remember you have to manage this beast, and mirror the settings on your other AlwaysOn AG node.

Now that you’ve configured one node of your cluster, duplicate all of the settings (or clone the VM) for your second node. Once you have two running VMs, all drives formatted, joined to the domain, patched, and ready to go then proceed to the third installment here. I’d also recommend starting the performance counters on the server, so you can monitor basic stats like CPU utilization.

2014-09-08_21-31-21a

 

 

© 2017 - Sitemap