page contents

About the Post

Author Information

Configuring SQL Server 2012 Always On Lab- Part 4

In my last article I talked about setting a domain for my lab environment for a SQL Server 2012 AlwaysOn Availability Group.

This is the last article of a 4 part series that covers the entire process.

  1. Initial Setup
  2. Domain Setup
  3. Cluster (Shared nothing) Setup
  4. SQL Install

The first thing I do when creating a new SQL instance is to configure Active Directory accounts that it will use. For a lab, I tend to keep things simple and use the following accounts:

  • SQL Server Account- ssa
  • SQL Database Engine- sde
  • SQL Reporting Services- ssrs
  • SQL Integration Services- ssis
  • SQL Analysis Services- ssas

Open up Active Directory Users and Computers, then go to UsersB containers


Right click on Users containers, then select New, then User


Now fill out the form to create the User and when finished click Next


Set the password and since this is a service account, I recommend for a lab to never let the password expire


Next, I setup user rights for the accounts to allow for Instant File Initialization (and I wrote another one here) by opening gpedit.msc


Then I browse to Computer Configuration, Windows Settings, Security Settings, Local Policies, User Rights Assignment and then Perform Volume Maintenance Tasks


Then I added the user accounts to speed up backups and database file creation


Now it is time to configure the Windows Firewall settings by allowing the end points (TCP port 5022) and SQL Server Management Studio – SSMS (TCP port 1433) remote access. You can do it by command line

netsh advfirewall firewall add rule name="SSMS" dir=in localport=1433 protocol=TCP action=allow; 
netsh advfirewall firewall add rule name="HADRON" dir=in localport=5022 protocol=TCP action=allow

 

or you can find this setting in the Control Panel


Now, we will need to create a new rule for Inbound connections:


We will need to specify the end points (TCP port 5022) and SQL Server Management Studio – SSMS (TCP port 1433) for remote access


Allow the connection


Choose the profiles that this rule should apply


Provide a name that you will recognize and a description for others (to prevent the rule from being deleted)


Now, we have the two rules enabled


We are now ready to install SQL Server 2012, since this is a shared nothing cluster (with no shared storage), we will choose Stand Alone Installation


Allow the installer to include SQL Server product updates


Here are the updates that it found


Here is a progress of the updates being applied to the installer


It is very important that all nodes (of this share nothing cluster) get the same features installed


After the SQL installation is completed, it is highly recommended that you apply the latest Cumulative Update (CU) that can be found here. In this case, I am using CU9 and to get started I will need to Accept the license terms


Here is the success message when CU9 is applied and at this point I recommend that you run Windows Update for all Microsoft Products


Now, we have installed SQL Server 2012 and need to configure the service to enable AlwaysOn by opening SQL Server Configuration Manager and then navigate to SQL Services


Right click on the SQL Service for the instance, then go to properties, select AlwaysOn High Availability and then check Enable AlwaysOn Availability Groups


Notice the Warning to restart the service


I am using the AdventureWorks 2012 databases for my testing. You can just download the mdf files and then put them in the SQLK data directory and run this command to create and bring them online

 

CREATE DATABASE AdventureWorks2012 ON (FILENAME = 'H:\Program Files\MSSQL11.MSSQLSERVER\MSSQL\Data\AdventureWorks2012_Data.mdf') FOR ATTACH_REBUILD_LOG;

CREATE DATABASE AdventureWorksLT2012 ON (FILENAME = 'H:\Program Files\MSSQL11.MSSQLSERVER\MSSQL\Data\AdventureWorksLT2012_Data.mdf') FOR ATTACH_REBUILD_LOG;

CREATE DATABASE AdventureWorksDW2012 ON (FILENAME = 'H:\Program Files\MSSQL11.MSSQLSERVER\MSSQL\Data\AdventureWorksDW2012_Data.mdf') FOR ATTACH_REBUILD_LOG;

Now, you will need to ensure that your database is in full recovery mode and you can use this code below:

 

USE master;

ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;

USE master;

ALTER DATABASE AdventureWorksLT2012 SET RECOVERY FULL;

USE master;

ALTER DATABASE AdventureWorksDW2012 SET RECOVERY FULL;

Another requirement of AlwaysOn is that you will need to ensure you have taken a full backup of your database and you can use this T-SQL command to fulfill that request:

BACKUP DATABASE [AdventureWorks2012] TO DISK = N'E:\Program Files\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2012-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM

GO

declare @backupSetId as int

select @backupSetId = position from msdb..backupset where database_name=N'AdventureWorks2012' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'AdventureWorks2012' )

if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''AdventureWorks2012'' not found.', 16, 1) end

RESTORE VERIFYONLY FROM DISK = N'E:\Program Files\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

GO

BACKUP DATABASE [AdventureWorksDW2012] TO DISK = N'E:\Program Files\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorksDW2012.bak' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2012-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM

GO

declare @backupSetId as int

select @backupSetId = position from msdb..backupset where database_name=N'AdventureWorksDW2012' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'AdventureWorksDW2012' )

if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''AdventureWorksDW2012'' not found.', 16, 1) end

RESTORE VERIFYONLY FROM DISK = N'E:\Program Files\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorksDW2012.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

GO

BACKUP DATABASE [AdventureWorksLT2012] TO DISK = N'E:\Program Files\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorksLT2012.bak' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2012-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM

GO

declare @backupSetId as int

select @backupSetId = position from msdb..backupset where database_name=N'AdventureWorksLT2012' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'AdventureWorksLT2012' )

if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''AdventureWorksLT2012'' not found.', 16, 1) end

RESTORE VERIFYONLY FROM DISK = N'E:\Program Files\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorksLT2012.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

GO

The next part is the sweetest part, which is actually setting up the AlwaysOn group with the Wizard, which to me is one the better Microsoft wizards. Right click on AlwaysOn High Availability and then select New Availability Group Wizard


New Availability Group Wizard


Provide an Availability Group name


Select the Databases and ensure it meets the prerequisites

Specify the Replicas and configure them

You can choose, where and how you wish to perform backups


You can also configure a listener for applications (if needed)


You can also select where you want the data synchronization to be stored


When it is complete, it will provide the results of the installation


It give you one last attempt to verify before you finish


Another progress screen


The installation is now complete


Another cool, thing to do is check out the Availability Group Dashboard when you Right click on Availability Right click Availability Groups and select Show Dashboard


Here is the Availability Group Dashboard


At this point, you have configured an AlwaysOn Availability Group with a share nothing cluster and I highly recommend setting up OLA’s SQL maintenance jobs to ensure you have good database health and backups. As usual, I hope you found this series helpful.

Tags: , , , , , , , ,

One Response to “Configuring SQL Server 2012 Always On Lab- Part 4”

  1. Larry McPhillips #

    Hi Scott! Hey I can’t seem to get to your Twitter, the one that’s linked from this blog. The Twitter page says “account suspended”, so I just thought I’d let you know.

    – Larry McP.

    June 29, 2014 at 11:22 AM
Copy Protected by Chetan's WP-Copyprotect.