Steps for Installing SQL Server AlwaysOn Availablity Groups

An availability group supports a fail over  environment for a discrete set of users databases, know as availability databases, that fail over together. An availability group supports a set of primary databases and one to eight sets of corresponding secondary databases. Secondary databases are not backups. Continue to backup your databases and their transaction logs on a regular basis.

Prerequisites required to enable SQL Server 2012 AlwaysOn Availability Groups Features

1. Get the OS installed, patched and configured on all participating node.
2. See either Windows Update or an internal Windows Server update Services (WSUS) server to get all the required Windows Updates downloaded and installed.
3. Dedicated domain user account be created for use by the SQL Server service. This should just be a regular or domain account.
4. Having separate accounts for SQL Agent service, SSAS, SSIS & SSRS. Having separate account is more secure and resilient, since a problem with one account won't affect all the SQL Server services.
5. Ensure password is not temporary and there is complex password.
6. Both SQL & OS editions, versions should be at same level on all participating nodes.
7. All replicas in your AlwaysOn group must be in the same windows domain. They must be able to communicate with each other.
8. Always on availability groups is only supported in Enterprise edition starting from SQL Server 2012 (except SQL Server 2016 it supports basic availability group in standard edition)
9. Recommend to have same collation on all replicas
10. SQL Server ports must be opened at firewall level for communication between replicas
11. Create shared network share on all participating node
12. Need to install .NET framework 3.5.1 or greater on all participating node
13. Make sure the databases are in FULL recovery mode
14. Databases included in AlwaysOn group must be user databases. System databases cannot participate in AlwaysOn Availability group
15. Read only databases cannot belong to an AlwaysOn group
16. Databases may only belong to one availability group at a time
17. Make sure full backups of each databases are made prior installing AlwaysOn
18. No cluster shared volume is required for Always On, it can be configured in local disks
19. Make sure to have a separate NIC's for public and private communication
20. Additional NIC is required if want to isolate always on replication traffic to dedicated NIC
21. Make sure to have two free IP's each for windows cluster IP and Always on listener IP

AlwaysOn Availability Groups require a Windows Server Failover Cluster, we first need to add the Windows Failover Cluster Feature to all the nodes running the SQL Server instances that we will configure as replicas

We have two node windows failover cluster SQL1 & SQL2 already setup as shown in the below screenshot




Once you have installed failover cluster we can now proceed with enabling the AlwaysOn Availability Group features in SQL Server 2012. This needs to be done on all the SQL Server instance that you will configure as replicas in your Availablity Group

 How to Enable SQL Server 2012 AlwaysOn Availability Groups Feature

Default standalone SQL instances installed on nodes SQL1 & SQL2

Step 1

Open SQL Server Configuration Manager. Double click the SQLServer (MSSQLSERVER) service to open the properties dialog box

In below screenshot, AONTESTWFCS01is the windows cluster name. SQL1 and SQL2 are nodes.

 

Step 2

 Restart the SQL Server service

Step 3

To create and configure a SQL Server 2012 AlwaysOn Availability Group, Open SQL Server Management Studio. Connect to the SQL Server instance

In Object Explorer, expand the AlwaysOn High Availability folder, Right click on the Availability Groups folder and select the New Availability Group Wizard... option. This will launch the New Availability Group Wizard

Step 4

In the Introduction page, click Next.

In the Specify Availability Group Name page, enter the name of the Availability Group in the Availability group name: field. Click Next

 

Step 5

In the Select Databases page, select the checkbox beside the database that you want to include in your Availability Group. The databases have to be in Full recovery model prior to joining them in the Availability group. Click Next.

 

 Step 6

In the Specify Replicas page, under the Replicas tab, click the Add Replicas button and connect to the other SQL Server instances that you joined as nodes in your Windows Server Failover Cluster. 

 Configure the following options:

1. Automatic Failover
2. Synchornous commit
3. Readable secondary

In the below screenshot, specified replicas SQL1 & SQL2.



Step 7
In the Endpoints tab, verify that the port number value is 5022 and endpoint name Hadr_endpoint



Step 8
 In the below screenshot, Select backup preferences








Step 9
In the Listener tab, select the Create an availability group listener option. Enter the following details.

Listener DNS name: AGLIS01
Port: 1633
Listener IP Address: 192.168.35.18

Click the Add... button to provide an IP address. In the Add IP Address dialog box, enter the preferred virtual IP address in the IPv4  Address field. Click OK. Click Next.


Step 11
In the Validation page, verify that all validation checks return successful results. Click Next.










Step 12
In the Summary page, verify all configuration settings and click Finish. This will create and configure the AlwaysOn Availability Group and join the databases. 

Installing Microsoft SQL Server 2008 R2 - Standalone Installation

Applies to: SQL Server 2008 R2 November CTP.

An evaluation copy of SQL Server 2008 R2 can be downloaded from here.

If you are upgrading to SQL Server 2008 R2, consider these resources and recommendations:

• Read about supported upgrade scenarios here.
• Download and run SQL Server Upgrade Advisor.
• Upgrade to the same language version as the existing instance of SQL Server.

The following are some recommendations before installing SQL Server 2008 R2:

• Use NTFS file system,
• Do not try to install SQL Server 2008 R2 on a compressed drive, because setup will block the installation.
• Do not install SQL Server on a Domain Controller. Click here for more information.
• Configure your firewall to allow SQL Server access. Click here for more information about protocols and port numbers.
• The user account that is running SQL Server setup must have administrative privileges on the computer.
• Verify Windows Management Instrumentation service (Control Panel -> Administrative Tools -> Services) is running.
• The computer where you are planning to install should be connected to the Internet.
• Do not install SQL Server 2008 R2 side-by-side with SQL Server 7, because it is not supported.

The following are software requirements:

• .Net Framework 3.5 SP1 is needed.
• Windows Installer 4.5 or later is required.
• Internet Explorer 6 SP1 is required.
• For operating system requirements, please click here. Windows Web Server and Windows Server 2008 Standard do not have the failover cluster feature available.
• PowerShell is required for SQL Server Express with Advance Series.
• If you have Visual Studio 2008 installed on the computer where you are planning to install SQL Server 2008 R2, pleas install Visual Studio 2008 SP1 prior to run SQL Server setup.

The hardware requirements are:

• At least 2.0 GB of available hard disk space.
• Supported Operating System: Windows Server 2008, Windows Server 2003 Service Pack 2, Windows Server 2003 Small Business Server R2, Windows Vista, Windows XP Service Pack 2.
• 1024 x 768 video resolution.
• For processor and memory requirements, please click here.

Run SQL Server 2008 R2 setup. First, setup checks to determine software requirements are installed. If not, you will be prompted to install.

As mentioned above, SQL Server 2008 R2 requires Windows Installer 4.5 and .Net Framework 3.5 SP1 to be installed. Click OK button to install .Net Framework.

After installing .Net Framework .5 SP1, I was prompted to install Windows Installer (KB942288)

After installing the hotfox, the Installation Center will launch.

On the screen above, make a click on the "Installation" hyperlink in the left side of the screen.

This screen shows the types of installation available. Let's select the first type available "New SQL Server stand-alone installation or add features to an existing installation".

Above you will find the first set of Setup Support Rules. The Next button is available only if all check results are successful, or if failed checks do not block installation.

On the page above, specify the edition of SQL Server to install. We chose the Enterprise Evaluation Edition.

The above is the License Term page. Read the license agreement and if you agree with it, check the "I accept the license terms" check box, and then click the Next button.

On the following page, setup is installing a required component.

The following is a new page on SQL Server setup, its name is Setup Role. Here you have three options.

• Choose "SQL Server feature installation" to install SQL Server database Engine, Analysis Services, Integration Services, Reporting Services.
• Choose "Analysis Services with Sharepoint Integration" if you want SQL Server setup to add PowerPoint for SharePoint to a SharePoint farm.
• Choose "All Features With Defaults" to install all features of SQL Server in one box.

On the Feature Selection page, select the features for your installation.

The page below belongs to the second set of Setup Support Rules.

On the Instance Configuration page, select a default or named instance for you installation, and select the directory where you want to install the features you have chosen.

This page is just for the purpose of reviewing disk usage. Click on the "Next" button to continue setup.

It is recommended to create a local user or domain user account to start SQLServer services, the next page setup will display is all about SQL Server service accounts.

If the computer is not part of a domain, use a local user account without Windows administrator permissions. You can create this account using Control Panel -> User Accounts.

Use a Domain User Account if the service must interact with network services or access domain resources. This account should be pre-created by a domain administration in your environment.

Whether you create a local user or a domain user account, the password you use to create the account must be  a strong password.

You can also use built-in accounts as the NetworkService account, but it is not recommend to use Local Service Account for SQL Server (MSSQLSERVER). Use Local System if you need a very high-privileged account. It has extensive privileges on the local system.

Microsoft recommends you to specify an individual account for each service. The service accounts used for this configuration must have a strong password as mentioned above, otherwise you will et the error "Invalid or missing username or password. To continue, provide valid credentials for the SQL Server Agent Service". A strong password is one that has a combination of letters in uppercase, lowercase, numbers and special characters.

On the Collation tab, specify the collation for your SQL Server instance.

On the Database Engine Configuration page, choose the authentication mode to use for your SQL Server installation.

Enter and confirm the password for the (sa) login whne you choose Mixed Mode Authentication

If you are planning to use FileStream with SQL Server 2008 R2, you may want to enable it on this tab or do it later.

On this page, provide user account that will be Analysis Services administrators.

If you selected Reporting Services as a feature to install, the Report Services Configuration page is displayed. Here we chose to install Reporting Services on Native mode.

On the Error and Usage Reporting page, optionally clear each check box to disable error reporting.

The above screens belong to the Installation Configuration Rules.

On the Ready to Install page, review the summary of features and components for your SQL Server installation. To proceed, click Install.

On the Completing the Microsoft SQL Server Installation Wizard page, you can view the Setup summary log by clicking the link provided on this page. To exit the SQL Server Installation Wizard, click Close.