SQL SERVER

Step-By-Step: Creating a SQL Server 2012 AlwaysOn Availability Group

One of the most talked about (and now frequently requested) feature in SQL Server 2012 is AlwaysOn Availability Groups. It brings SQL Server high availability and disaster recovery to a whole new level by allowing multiple copies of the database be highly available and potentially using them for read-only workloads and offloading management tasks such as backups. AlwaysOn Availability Groups allow you to fail over a group of databases as a single entity, unlike database mirroring where you can only do so one database at a time. This is very useful for applications that access multiple databases in a single SQL Server instance like SharePoint Server 2013. In fact, very recently, one of my customers had requested to configure SQL Server 2012 AlwaysOn Availability Groups for their SharePoint 2013 farm. I am also seeing more and more SharePoint 2013 farms leveraging on the SQL Server 2012 AlwaysOn Availability Groups for both high availability and disaster recovery.

This step-by-step has been created to help you get started in creating a SQL Server 2012 AlwaysOn Availability Group for your mission-critical databases.

Prerequisites

  • Windows Server Failover Cluster (WSFC). AlwaysOn Availability Groups rely on the Windows Server Failover Cluster for failure detection and management of the Availability Group replicas. This is where a lot of customers get confused because of their previous knowledge of Microsoft Cluster Services (MSCS.) In previous versions of Windows Server, you need shared storage to create a failover cluster for the quorum disk. Windows Server 2008 and higher provided the option to use a file share witness as a quorum configuration. Therefore, you DO NOTneed shared storage to create a Windows Server Failover Cluster for AlwaysOn Availability Groups. This, of course, does not change the requirement if you intend to use a SQL Server Failover Clustered Instance (FCI) as a replica in your Availability Group. For this step-by-step, we will only be working with standalone SQL Server 2012 default instances.
  • Download SQL Server 2012 Enterprise Edition. AlwaysOn Availability Group is an Enterprise Edition feature. Before deciding to implement this feature, take stock of your SQL Server licenses to make sure you have enough to get you covered. If you intend to use the other replicas for read-only workloads or offloading your backups, you would need licenses for those SQL Server instances as well. This is also another one of those items that customers get confused with because in previous versions of SQL Server, database mirroring can be configured with Standard Edition.
  • Same SQL Server collation for all replicas. I usually don’t recommend running databases with different collation requirements in the same SQL Server instance due to potential issues caused by applications using temporary tables. This is one of the reasons for keeping the database collation the same for a single instance (SharePoint 2013 also requires a specific collation for the content databases.) If you want to configure AlwaysOn Availability Groups for your databases, they should all be running the same collation on all of the SQL Server instances acting as replicas.
  • Two to Five SQL Server Instances acting as replicas. SQL Server instances that will be used as a standby for high availability and/or disaster recovery are called replicas. Unlike database mirroring where you can only have one extra copy of the database, AlwaysOn Availability Groups allow you to have up to five copies of the database running on five replicas – three of which can be configured for synchronous-commit modeand two in asynchronous-commit mode. 

Windows Failover Cluster Feature Installation

Since AlwaysOn Availability Groups require a Windows Server Failover Cluster, we first need to add the Windows Failover Cluster Feature to all the machines running the SQL Server instances that we will configure as replicas. For the operating system, we will be using Windows Server 2012. To add the Failover Clustering feature:

  1. Open the Server Manager console and select Add roles and features. This will launch the Add Roles Features Wizard
  1. Click Next until you reach the Select Features dialog box. Select the Failover Clustering checkbox. When prompted with the Add features that are required for Failover Clustering dialog box, click Add Features. Click Next.
  2. Click Install to install the Failover Clustering feature.

Windows Failover Clustering Configuration for SQL Server 2012 AlwaysOn Availability Groups

Prior to configuring the Windows Server Failover Cluster, it is assumed that you have the appropriate rights in Active Directory. For a complete listing of the different Active Directory permissions to create a Windows Server Failover Cluster, see Failover Cluster Step-by-Step Guide: Configuring Accounts in Active Directory. To configure Windows Failover Clustering,

  1. Launch Failover Cluster Manager from within the Server Manager console.
  2. Within Failover Cluster Manager, click the Validate Configuration… link.
  3. In the Validate a Configuration Wizard dialog box, click Next.
  4. In the Select Servers or a Cluster dialog box, add the server hostnames of the SQL Server instances that you want to configure as replicas in your Availability Group. Click Next.
  5. In the Testing Options dialog box, make sure that the option Run all tests (recommended) is selected. Click Next.
  6. In the Confirmation dialog box, click Next.
  7. In the Summary dialog box, click Finish to create the Windows Failover Cluster.NOTE: The Failover Cluster Validation Wizard is expected to return several Warning messages, especially if you will not be using shared storage. As we mentioned earlier, there is no need to use shared storage to create the Windows Server Failover Cluster that we will use for our Availability Group. Just be aware of these Warning messages as we will configure a file share witness for our cluster quorum configuration. However, if you see any Error messages, you need to fix those first prior to creating the Windows Server Failover Cluster.
  8. In the Access Point for Administering the Cluster dialog box, enter the virtual server name and virtual IP address of your Windows Server Failover Cluster.
  9. In the Confirmation dialog box, click Next. This will create the Windows Failover Cluster using the servers as nodes of the cluster, add DNS and Active Directory entries for the cluster hostname.
  10. In the Summary dialog box, verify that the configuration is successful.
  11. To configure the cluster quorum configuration to use a file share, right-click on the cluster name, select More Actions and click Configure Cluster Quorum Settings… We will be configuring a file share witness for our cluster quorum setting. By default, the wizard will configure the cluster to use Node Majority.
  12. Click Next.
  13. In the Select Quorum Configuration page, select the Add or change the quorum witness option. Click Next.
  14. In the Select Quorum Witness page, select the Configure a file share witness (recommended for special configuration) option. ClickNext.
  15. In the Configure File Share Witness page, type path of the file share that you want to use in the File Share Path: text box. Click Next.
  16. In the Confirmation page, click Next.
  17. In the Summary page, click Finish.

Enable SQL Server 2012 AlwaysOn Availability Groups Feature

Once the Windows Server Failover Cluster has been created, we can now proceed with enabling the AlwaysOn Availability Groups feature in SQL Server 2012.  This needs to be done on all of the SQL Server instances that you will configure as replicas in your Availability Group. To enable the SQL Server 2012 AlwaysOn Availability Groups feature,

  1. Open SQL Server Configuration Manager. Double-click the SQLServer (MSSQLSERVER) service to open the Properties dialog box.
  2. In the Properties dialog box, select the AlwaysOn High Availability tab. Check the Enable AlwaysOn Availability Groups check box. This will prompt you to restart the SQL Server service. Click OK.
  3. Restart the SQL Server service.

Create and Configure SQL Server 2012 AlwaysOn Availability Groups

Availability Groups can be created on existing databases or even a temporary one in preparation for application installation. If you intend to create an Availability Group for a new SharePoint 2013 farm, you will need to create a temporary database. This is so that the SharePoint 2013 farm will use the AlwaysOn Availability Group when creating the farm configuration and the admin content databases. After the SharePoint 2013 farm has been created, this database can be removed from the Availability Group configuration and deleted from the instance.

To create and configure a SQL Server 2012 AlwaysOn Availability Group,

  1. Open SQL Server Management Studio. Connect to the SQL Server instance
  2. In Object Exporer, 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.
  3. In the Introduction page, click Next.
  4. In the Specify Availability Group Name page, enter the name of the Availability Group in the Availability group name: field. Click Next.
  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.
  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
    • Automatic Failover (Up to 2) :          Checked
    • Synchronous Commit (Up to 3) :      Checked
    • Readable Secondary:                      No 
  7. In the Endpoints tab, verify that the port number value is 5022.
  8. In the Listener tab, select the Create an availability group listener option. Enter the following details.
    • Listener DNS name: Name that you will use in your application connection string
    • Port: 1433
  9. Click the Add… button to provide an IP address. In the Add IP Address dialog box, enter your preferred virtual IP address in the IPv4 Address field. Click OK. Click Next.
  10. In the Select Initial Data Synchronization page, select the Full option. Provide a shared folder that is accessible the replicas and that the SQL Server service account used by both replicas has Write permissions to. This is just a temporary file share to store the database backups that will be used to initialize the databases in an Availability group. If you are dealing with large databases, it is recommended that you manually initialize the databases prior to configuring them as your network bandwidth may not be able to accommodate the size of the database backups. Click Next.
  11. In the Validation page, verify that all validation checks return successful results. Click Next.
  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.
  13. In the Results page, verify that all tasks have been completed successfully.

Congratulations! You have just created a SQL Server 2012 AlwaysOn Availability Groups. You can now use the Availability Groups listener name in your application connection string. Keep in mind that you need to manually add new databases in the Availability Group even though your application has already been using the listener name. So, be sure to monitor the replicas in your Availability Groups to be alerted when new databases are created.

*******************************************************************************

Configure a SQL 2014 Always On Availability Group Cluster

Table of Contents

Introduction

Before jumping into this tutorial you will want to have first completed the following pre-requisite tutorials:

Setting Up Windows 2012 R2 Active Directory Using the DCD

Setup DCD for a Two Node Cluster

Create a Windows 2012 R2 Failover Cluster on ProfitBricks

We will be leveraging the Active Directory domain you created in the first tutorial and the DCD cluster you brought online in the second. By the end of this tutorial you will have a SQL cluster deployed in your datacenter. We will be using AlwasyOn Availability Groups with non-shared storage.

SQL 2014 Edition Differences

Microsoft provides a great overview of the differences between SQL editions here. This page breaks down the features in SQL and the edition in which they are available.

For the features we’re using in this tutorial you will need to use Enterprise Edition. We plan on covering how to setup a cluster using AlwaysOn Failover Cluster Instances, which is available for Enterprise, Business Intelligence, and Standard editions, in a later tutorial.

AlwaysOn Availability Groups

One of the more exciting additions to SQL in the past few versions has been the introduction of AlwaysOn Availability Groups. An AlwaysOn Availability Group cluster uses and requires Windows Server Failover Clustering (WSFC). The concept is very similar to Exchange’s DAG technology in that an Availability Group encompasses and supports failover of a discrete set of databases. These databases are known as your availability databases. What this means is that in the event of a failure all databases associated with a given availability group will failover to a secondary replica.

One node in a cluster will act as a primary replica with support for one to eight secondary replicas. Connections to the cluster are managed by WSFC. Clients connect to an availability group listener which determines the correct replica to send the request.

Networking

You did most of the network setup in the tutorial on how to create the environment within the DCD. The remaining steps that you will need to do before moving forward with installing SQL and configuring the cluster are:

  1. Configure the private interfaces on both SQL nodes to use the Domain Controllers as the DNS servers.
  2. Clear the DNS server settings for the public interface to ensure you can resolve the AD domain without issue. Any public lookups would happen via referrals on the AD DNS servers.
  3. Join the AD domain. This can be done by using the following PowerShell command:

You would run:

Add-Computer -DomainName YOURDOMAIN

This will prompt you for credentials. Use the username and password for the domain administrator. Once finished you should get a response from PowerShell that indicates the changes won’t take affect until you reboot.

Let’s not reboot at the moment, but continue onto Windows Update which might require a reboot depending on the type of updates that are outstanding.

Windows Update

As a best practice — and especially with cloud server instances — I always like to run Windows Update before starting anything major. Once we’re done with the SQL installation we will re-run Windows Update to ensure we capture any additional SQL updates that may need to be installed.

Go ahead and run Windows Update at this time if you want. Reboot once all updates have been installed. If there are no updates to install go ahead and reboot to commit the domain join change.

Disk Volumes

Log onto both nodes and initialize the volumes you will be using for storing your databases and transaction logs. If you followed our tutorial on provisioning a two-node cluster in the DCD then you should have three volumes total:

  • a volume for the system
  • a volume for the SQL databases
  • a volume for the SQL transaction logs

This would be done by bringing up the Disk Management interface.

Setup SQL User and Groups

First, we need to create some Managed Service Accounts. These will be used for each SQL node we add to the cluster. We create three accounts per node in the cluster and associate those service accounts to the node. This can be simplified using PowerShell like so:

New-ADServiceAccount -Name SQL01SVCACC -Description "SQL01 Service Account" -Path "OU=ClusterObjects,DC=yourdomain,DC=local"
New-ADServiceAccount -Name SQL01AGTACC -Description "SQL01 Agent Account" -Path "OU=ClusterObjects,DC=yourdomain,DC=local"
New-ADServiceAccount -Name SQL01SBWACC -Description "SQL01 Browser Service Account" -Path "OU=ClusterObjects,DC=yourdomain,DC=local"
New-ADServiceAccount -Name SQL02SVCACC -Description "SQL02 Service Account" -Path "OU=ClusterObjects,DC=yourdomain,DC=local"
New-ADServiceAccount -Name SQL02AGTACC -Description "SQL02 Agent Account" -Path "OU=ClusterObjects,DC=yourdomain,DC=local"
New-ADServiceAccount -Name SQL02BWACC -Description "SQL02 Browser Service Account" -Path "OU=ClusterObjects,DC=yourdomain,DC=local"

You will next need to associate each account with the SQL node to which it belongs.

Add-ADComputerServiceAccount -Identity SQL01 -ServiceAccount SQL01SVCACC,SQL01AGTACC,SQL01SBWACC
Add-ADComputerServiceAccount -Identity SQL02 -ServiceAccount SQL02SVCACC,SQL012AGTACC,SQL02SBWACC

The above should be ran from a server that has the AD PowerShell tools installed. We recommend installing these tools on each of the nodes using the following command.

Install-WindowsFeature RSAT-AD-PowerShell

Now it’s time to install these newly created service accounts on each node. It is important that you are logged into the node when running these.

Get-ADComputerServiceAccount -Identity SQL01 | Install-ADServiceAccount

From the second node:

Get-ADComputerServiceAccount -Identity SQL02 | Install-ADServiceAccount

It is also handy to create a SqlServerAdministrators group at this time. This will be assigned during the SQL installation. Ensure that the user you are going to use to enable AlwaysOn and configure a DB is a member of this group. This includes your domain administrator if you’re using that user.

Install SQL on the First Node

Since we’re installing a test cluster we will be using the evaluation version of SQL 2014. You will need to ensure you have the appropriate license for the version of SQL you will be using.

You can download the evaluation version here. This gives you a 180-day license of SQL 2014. This is sufficient to get a feel for AlwaysOn Availability Groups. You will also need to download the latest cumulative update here and extract it to a location on disk.

Ensure that the Windows Firewall is not protecting the heartbeat or private networks.

Log onto the first node and either mount or download your SQL binaries.

You will then need to run this at the command line:

C:\sql\setup\file\path\setup.exe /action=install /updateenabled=true /updatesource="C:\path\to\update"

Once the wizard comes up:

  1. Input your product key and click next.
  2. Accept the terms, next.
  3. Choose SQL Server Feature Installation
  4. You will be installing these features:
  5. Database Engine Services
  6. Full-Text Search
  7. Management Tools — Complete

5. Choose named instance and set its name to something other than the default. You can also change your instance path at this time.

6. Set the Account Name for each of the service accounts.

| Service | Account Name | Startup Type |
| --- | --- | --- |
| SQL Server Agent | YOURDOMAIN\SQL01AgtAcc$ | Default |
| SQL Server Database Engine | YOURDOMAIN\SQL01SvcAcc$ | Default |

7. Choose your authentication mode. Best practices say you’d leave the default, but turning on mixed mode gives you a way into SQL when all else fails. You will also need to add the SqlServerAdministrators group as an defined SQL Server Administrator.

8. You will then want to update your directories so that your databases are on the second volume you’ve attached to your server and the transaction logs are on the third. At this time, if you choose to, you can create a fourth volume and use that as your backup location.

It is also very important to note that to use Availability Groups the database and transaction log paths need to remain identical. You will need to adjust the default paths to reflect the same path on both nodes.

9. Click through to complete the setup.

At this point your first node should be online.

Install SQL on the Second Node

You will want to repeat the above steps on each node of your cluster. It might be handy to generate an INI or script out the installation if you’re going to be doing this numerous times.

Configure SQL Networking

SQL needs to be configured for static ports. The following steps will need to be done on each cluster.

Bring up the Sql Server Configuration Manager.

You will want to drill down to SQL Server Network Configuration –> Protocols for YOURINSTANCENAME.

You will need to remove the “TCP Dynamic Ports” value 0 for all IP nodes (IP1, IP2, IP3, etc.). This can be done by simply highlighting and deleting it.

Clear the TCP Dynamic Ports value under IPALL.

Enter your desired static port in the TCP Port field. This will need to be the same port across all nodes. You can make this whatever you want. In our example we’re using 2020.

Restart SQL services.

Enabling AlwaysOn

Finally, you’re ready to enable AlwaysOn using PowerShell:

Enable-SqlAlwaysOn -ServerInstance "SQLSERVER\SQLINSTANCE" -Force

Replace SQLSERVER with the name of your server and SQLINSTANCE with the instance named you used. Perform this step on each node. You will also need to restart your the SQL Server service.

Configure AlwaysOn for Your Database

Now that your nodes are built out, networking is configured, and AlwaysOn is enabled it is time to configure a database for redundancy. This can be done from with the SQL Management Studio.

Before you start you will already need to either create a database or attach a database to the SQL instance. You will also need to perform a full backup of the database.

We will only create a single AlwaysOn Availability Group. We will name this availability group: AODEMO.

Log onto SQL01 and bring up the SQL Management Studio.

Right-click on Availability Groups and select New Availability Group Wizard.

Name your AG.

Choose your database.

Click “Add Replica”.

Connect to your second node. In our case this is SQL02.

You will want to enable the following:

  • Automatic Failover
  • Synchronous Commit
  • Readable Secondary

Click the Endpoints tab and configure your endpoints. We will be using the heartbeat network we created for node to node communication. This will serve as our replica network, too.

Click the Listener tab and configure the listener. You’ll notice we’re using the port we defined when configuring the networking.

You will need to configure a data synchronization method. We recommend using full. You will need to ensure that your nodes are setup identically and that the database and log path are the same on all nodes. We used the file paths:

  • D:\databases\
  • E:\transaction_logs\

You can obviously make these paths different. The only requirement is they must be identical across all nodes in the cluster so the default paths will not work.

The default will attempt to use \SQL01 (the node you’re currently on). One option is to create a DFS location for your shared content. This goes beyond the scope of this article but will be covered in the future.

You’ll notice that we’re using a share available on our node to node communication network, what we called the heartbeat network.

Ensure all validation checks are green.

Click next and then finish. This will configure endpoints and perform various other tasks. You should see a results page like this.

Validation

Exploring the new Availability Group in SQL Server Management Studio you should see that there’s an Availability Group named AODEMO. Under Availability Replicas you should see your nodes with your first node being the primary and the other node as your secondary. There should be a single Availability Database named, in our case, AGDEMODB. Finally you should see your listener. If you were to check AD DNS this listener will be registered with the internal IP you provided.

Log onto your secondary node and bring up the SQL Server Management Studio. You should see that you have the database there as well.

Conclusion

Congratulations! At this point you should now have a database using AlwaysOn Availability Groups.

Reference:

https://devops.profitbricks.com/tutorials/configure-a-sql-2014-alwayson-availability-group-cluster/

https://blogs.technet.microsoft.com/canitpro/2013/08/19/step-by-step-creating-a-sql-server-2012-alwayson-availability-group/

https://www.mssqltips.com/sqlservertip/2519/sql-server-alwayson-availability-groups–part-1-configuration/

***********************************************************************************

Configure a SQL Server Alias for Sharepoint (SQL Server 2008)

A SQL Server alias is a tidy way to protect yourself from configuration changes in a SharePoint farm. Anybody who works with SharePoint on a regular basis knows that it is very difficult to move SharePoint to a different SQL Server. When disaster strikes wouldn’t it be nice if you could simply change SQL Server that SharePoint references? To do so, install the SQL client access tools on each server and create a SQL Alias in SQL Server Configuration Manager, from there your SharePoint Server can be re-homed with little effort.

Note, there are two methods to create a SQL Server alias, one for machines that already have SQL Server client tools installed, and another for machines that have no SQL components at all.  If your machine does not have any SL software installed and you’d like to creat an alias, follow the steps diagrammed below after running this in the server’s run dialog: cliconfg.exe.

For a server with NO SQL Server Tools Installed

From the command line run cliconfg.exe

clip_image001[1]

Enable TCP/IP

clip_image002[1]

Add a new alias as follows

clip_image003[1]

Once your alias is entered, launch SharePoint Products and technologies wizard and build your SharePoint farm.

Verify on your SQL server that TCP/IP communication is allowed in the sql server configuration manager

clip_image005[1]

For a server that has SQL Server Tools Installed

Open SQL Server Configuration Manager

clip_image001

Enable TIP/IP if you haven’t done so already

clip_image002

Under the 32 bit Client 10.0 Configuration (32 Bit)

** yes, one would think you need to do it under the 64 bit but it looks like enterprise manager thunks down to 32 bit

clip_image003

Repeat the steps above for SQL Native Client 10.0 Configuration

** SharePoint uses the 64 Bit Native Client

clip_image004

Test your connection using enterprise manager

clip_image005

In the event of a SQL failure, or if you simply want to migrate to a different SQL Server, all you have to do is update the SQL Server Alias in each client machine to reference the new SQL Server. The example below shows how to connect using a named instance on a new server

clip_image006

Or just connect to another server’s default instance

clip_image007

 

*************************************************************************************

Configuring SQL Server 2014 AlwaysOn Availability Group using PowerShell

Introduction

Earlier on I was trying to search for some scenarios to implement AlwaysOn Availability Groups using PowerShell, although I was able to find some interesting post however non of them represented a complete scenario starting from a fresh windows server installation (using PowerShell), so I decided to write this blog as quick walkthrough the PowerShell scripts needed.

Sample Scenario

In this scenario I will be using a 2 Nodes setup with the following software already installed:

  • Windows Server 2012 R2
  • Both Servers are joined to the domain

Configuration Steps:

To make the scenario simple I be using a Domain Account that has a Local Administrator Permission on both nodes. In addition, all scripts below will be running using PowerShell (Run as Admin) so Lets get started:

1- Install SQL Server

We need to install a standalone setup on each node. I will do that using the below sample unattended SQL Setup Script:

Setup.exe /q /ACTION=Install /FEATURES=SQL /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="<DomainName\UserName>" /SQLSVCPASSWORD="<StrongPassword>" /SQLSYSADMINACCOUNTS="<DomainName\UserName>" /AGTSVCACCOUNT="<DomainName\UserName>" /AGTSVCPASSWORD="<StrongPassword>"   /IACCEPTSQLSERVERLICENSETERMS

2- Add Windows Failover Cluster

We need to install it on each node. I will do that using the below script:

Import-Module ServerManager

Add-WindowsFeature Failover-Clustering –IncludeAllSubFeature

Install-WindowsFeature -Name Failover-Clustering –IncludeManagementTools

3- Configure Windows Failover Cluster

Run the below script on the 1st Node and make sure to update the configuration parameters with your values:

#—————————————————-
# Configuration Parameters
#—————————————————-
$Server1 = “Server1”
$Server2 = “Server2”
$IPAddress = “X.X.X.X”
$AlwaysOnClusterName = “ClusetrName”
$QuorumFileSharePath = “\\FileSharePath\”

#—————————————————-
# Create Cluster Service
# Create a new cluster ‘AlwaysOnCluster’ with nodes.
#—————————————————-
Import-Module FailoverClusters
New-Cluster –Name $AlwaysOnClusterName –Node $Server1,$Server2 -StaticAddress $IPAddress -NoStorage
# Add Quorum
Set-ClusterQuorum -NodeAndFileShareMajority $QuorumFileSharePath

4- Configure AlwaysOn Failover Cluster

Now for the fun part, I have configured the whole AlwaysOn Configuration in one script that needed to be ran on the 1st Node. In addition, I have added the proper comments so take your time to review it  and make sure to update the configuration parameters with your values:

#Set execution policy
Set-ExecutionPolicy unrestricted

# Import SQLPS Module
Import-Module “sqlps” -DisableNameChecking

#—————————————————-
# Configuration Parameters
#—————————————————-
#Server Names
$Server1 = “Server1”
$Server2 = “Server2”
$ServerInstance1 = ‘$Server1\Instance’
$ServerInstance2 = ‘$Server2\Instance’

#AlwaysOn TempDB Names
$tmpDB1 = “AG1-TempDB”

#Availability Group Names
$AG1Name=”AG-1″

#Availability Group Listener
$AGListner1Name = “Listener1”
$Listner1IP_Mask = “X.X.X.X/X.X.X.X”
$ListnerPort= “1433”
$FileSharePath = “\\FileSharePath\”

#Service Accounts
$SQLSVCAccount = “Domain\SVCAccount”
$AGDomain = “.contoso.com” #Keep the ‘.’ before domain name

#AlwaysOn EndPoints
$AOEndpointName = “AlwaysOnEndpoint”
$AOPort = “5022”
$AOEncryptionAlgorithm = “AES”

#—————————————————-
# Enable AlwaysOn on Servers
#—————————————————-
Enable-SqlAlwaysOn –ServerInstance $Server1
Enable-SqlAlwaysOn –ServerInstance $Server2

#—————————————————-
# Create Endpoints
#—————————————————-
#####1st Server
$AOtmpPath = “SQLSERVER:\SQL\$Server1\default”
New-SqlHadrEndpoint -Path $AOtmpPath -Name $AOEndpointName -Port $AOPort -EncryptionAlgorithm $AOEncryptionAlgorithm

# start the endpoint
$AOtmpPath = “SQLSERVER:\SQL\$Server1\default\endpoints\AlwaysOnEndpoint”
Set-SqlHadrEndpoint –Path $AOtmpPath –State “Started”;

####2nd Server
$AOtmpPath = “SQLSERVER:\SQL\$Server2\default”
New-SqlHadrEndpoint -Path $AOtmpPath -Name $AOEndpointName -Port $AOPort -EncryptionAlgorithm $AOEncryptionAlgorithm

# start the endpoint
$AOtmpPath = “SQLSERVER:\SQL\$Server2\default\endpoints\AlwaysOnEndpoint”
Set-SqlHadrEndpoint –Path $AOtmpPath –State “Started”;

#—————————————————-
# Grant Permissions for EndPoints
#—————————————————-
$SQLPermissionQry = ”
USE [master]
GO
CREATE LOGIN $SQLSVCAccount FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
GRANT ALTER ANY AVAILABILITY GROUP TO $SQLSVCAccount
GO
GRANT CONNECT SQL TO $SQLSVCAccount
GO
GRANT VIEW SERVER STATE TO $SQLSVCAccount
GO

Invoke-Sqlcmd -Query $SQLPermissionQry -ServerInstance $ServerInstance1
Invoke-Sqlcmd -Query $SQLPermissionQry -ServerInstance $ServerInstance2

#—————————————————-
#Create Temp DB for AG
#—————————————————-
$AOtmpPath = “SQLSERVER:\SQL\$Server1\default”
$svr = Get-Item $AOtmpPath
$db1 = New-Object Microsoft.SqlServer.Management.Smo.Database($svr, $tmpDB1);
$db1.Create();

#—————————————————-
#Initial Backup for the DB
#—————————————————-
cd “SQLSERVER:\SQL\$Server1\default\databases”
Backup-SqlDatabase –ServerInstance $Server1 –Database $tmpDB1;

#————————————————
# Backup & Restore TempDBs to prepare for AlwaysOn
#————————————————
#Backup
Backup-SqlDatabase –ServerInstance $Server1 –Database $tmpDB1 –BackupFile “$FileSharePath$tmpDB1.bak”;
Backup-SqlDatabase –ServerInstance $Server1 –Database $tmpDB1 –BackupAction Log –BackupFile “$FileSharePath$tmpDB1.trn”;

# Restore
cd “SQLSERVER:\SQL\$Server1\default”
Restore-SqlDatabase –ServerInstance $Server2 –Database $tmpDB1 –BackupFile “$FileSharePath$tmpDB1.bak” –NoRecovery;
Restore-SqlDatabase –ServerInstance $Server2 –Database $tmpDB1 –RestoreAction Log –BackupFile “$FileSharePath$tmpDB1.trn” –NoRecovery;

#———————————————
#Create AG Replica
#It assumes SynchronousCommit + Automatic Failover
#———————————————
$PrimaryRepTCP = “TCP://$Server1$AGDomain” + ‘:’ + “$AOPort”
$SecondaryRepTCP = “TCP://$Server2$AGDomain” + ‘:’ + “$AOPort”

$Primary = new-sqlavailabilityreplica -Name $Server1 -EndpointUrl $PrimaryRepTCP -ConnectionModeInPrimaryRole “AllowAllConnections” -ConnectionModeInSecondaryRole “AllowAllConnections” –AvailabilityMode “SynchronousCommit” –FailoverMode “Automatic” -AsTemplate -Version 11;
$Secondary = new-sqlavailabilityreplica -Name $Server2 -EndpointUrl $SecondaryRepTCP -ConnectionModeInSecondaryRole “AllowAllConnections” –AvailabilityMode “SynchronousCommit” –FailoverMode “Automatic” -AsTemplate -Version 11;

#———————————————
#Create a new AG
#———————————————
$ag = New-SqlAvailabilityGroup -Name $AG1Name -AvailabilityReplica ($Primary, $Secondary) -Database $tmpDB1
#———————————————
#Join Availability Replica
#———————————————

$AOtmpPath = “SQLSERVER:\SQL\$Server2\default”
Join-SqlAvailabilityGroup –Path $AOtmpPath –Name $AG1Name;

#———————————————
#Join Replica Database on a Secondary replica
#———————————————
$agpath1 = “SQLSERVER:\SQL\$Server2\default\AvailabilityGroups\$AG1Name”
Add-SqlAvailabilityDatabase –Path $agpath1 –Database $tmpDB1

#———————————————
#Create a Listener using Static IPs
#———————————————
$agpath1 = “SQLSERVER:\SQL\$Server1\default\AvailabilityGroups\$AG1Name”
$ag = Get-Item $agpath1; #Validate AG Path
New-SqlAvailabilityGroupListener -Name $AGListner1Name –Path $agpath1 –StaticIp $Listner1IP_Mask –Port $ListnerPort;

And that’s all folks … hope you find it useful

Ref :https://blogs.technet.microsoft.com/meamcs/2015/03/12/configuring-sql-server-2014-alwayson-availability-group-using-powershell/

***********************************************************************************

Adding SQL Server Availability Group (AG) to an Exiting Failover Cluster Instance (FCI) – Part1 of 2

Introduction

Microsoft SQL Server 2012/2014 AlwaysOn provides flexible design choices for selecting an appropriate high availability (HA) and disaster recovery (DR) solution for your application. Building on that fact I will be providing a posts that provides a step-by-step configuration for extending a 2 Node SQL Server Failover Cluster Instance with a 3rd Node that host AlwaysOn Availability Group (This scenario is also known as FCI+AG). For more information about SQL Server 2012 AlwaysOn high availability and disaster recovery design patterns, see SQL Server 2012 AlwaysOn High Availability and Disaster Recovery Design Patterns.

Prerequisites

This posts assumes a basic knowledge of failover cluster instances (FCIs), availability groups, high availability, and disaster recovery concepts. For more information about the full AlwaysOn solution feature set, see the Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery white paper.

Scenario

In this scenario I will be using a preconfigured FCI with 2 Nodes, and will be walking through the activities needed for adding a 3rd Node as a DR Server with the following software setup:

  • Windows Server 2012 R2
  • All servers are joined to the domain

image

To make the scenario simple I will be using a Domain Account that has a Local Administrator Permission on All Nodes. In addition, the configuration below will be using a combination of PowerShell Scripts (that will be running as Admin) and GUI so let’s get started:

1. Installing SQL Server on DR Node

We need to install a New Standalone Instance on the DR Node. I will do that using the below sample unattended SQL Setup Script:

Setup.exe /q /ACTION=Install /FEATURES=SQL /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT=”<DomainName\UserName>” /SQLSVCPASSWORD=”<StrongPassword>” /SQLSYSADMINACCOUNTS=”<DomainName\UserName>” /AGTSVCACCOUNT=”<DomainName\UserName>” /AGTSVCPASSWORD=”<StrongPassword>” /IACCEPTSQLSERVERLICENSETERMS

2. Installing Windows Failover Cluster on DR Node

Login to the DR Node and run the below script using PowerShell:
Import-Module ServerManager 
Add-WindowsFeature Failover-Clustering –IncludeAllSubFeature 
Install-WindowsFeature -Name Failover-Clustering –IncludeManagementTools

3. Add DR Node to Existing Windows Failover Cluster

Login to the Active Node on the FCI >> Open “Failover Cluster Manager” and Right click on “Nodes”, Then click on “Add Node”

image

Click on “Next” in “Add Node Wizard”

image

User keyboard to Enter server name: in “Add Node Wizard” then click on “Add” in “Add Node Wizard”

image

Click on “Next” in “Add Node Wizard”

image

Select “No” for Validating Cluster and Click on “Next” in “Add Node Wizard”

image

Unselect “Add all eligible storage to the cluster. (check box)” in “Add Node Wizard”

image

Click on “Next” in “Add Node Wizard”

image

Make sure that the new node appears in the list of nodes.

image

4. Validate Windows Failover Cluster Owners

Click on “Roles in “Failover Cluster Manager” then Right Click on “SQL Server” and Select “Properties”

image

Make sure Preferred Owners are set to the 2 FCI Nodes and clear the check from the DR Node then click “OK”

image

Scroll down and select “SQL Server” from “Other Resources”

image

Right Click and select “Properties” then go to “Advanced Policies (tab item)”. Validate that the possible owners as set to the 2 FCI Nodes then Click “OK”

image

5. Activate SQL Server AlwaysOn

We need to activate the SQL Server AlwaysOn Feature on All Servers (On the FCI you can activate it on the active instance only and it will reflect on the other node). The below script must be ran from the Active FCI Node:

Set-ExecutionPolicy unrestricted
Import-Module “sqlps” -DisableNameChecking
$Server1 = “FCI”
$Server2 = “DR Server”
Enable-SqlAlwaysOn –ServerInstance $Server1
Enable-SqlAlwaysOn –ServerInstance $Server2

6. Configure SQL Server AlwaysOn Availability Group

To configure the AlwaysOn Availability Group we need to Login to the Active FCI Node and do the following steps:

· Create a new Database that will be used to prestige the Availability Group.

· Take a full backup of the new Database.

Notes:

· Creating the Database is straight forward and will not be covered within this post for simplicity.

· This database will be deleted after the completion of AlwaysOn Configuration

· In my scenario I create a new database called “TempDB1”.

Open SQL Server Management Studio >> Connect to the FCI >> Right click and select “New Availability Group Wizard… (menu item)”

image

Click on “Next” in “New Availability Group”

image

Enter “Availability group name:” in “New Availability Group” then click on “Next” in “New Availability Group”

image

Select the “TempDB1” we created earlier and click “Next”.

image

Click on “Add Replica” and “Connect” to the DR Instance

image

After adding the DR as a Replica, validate the Availability Group Settings and Click on “Next”.

image

Select “Full” and enter the “Shared Network Path” then Click “Next”

image

Click “Next” on “Validation Screen”.

image

Click on “Finish” in “New Availability Group” and make sure that the wizard completes successfully.

image

Expand the Availably Group Listeners and click on “Add Listener… (menu item)”

image

Fill the listener information and click OK to complete the Listener Configuration.

image

After following these steps the configuration Failover Cluster Instance is extended to use the Availability Group in DR and the scenario is completed, hope you find it useful.

Ref :https://blogs.technet.microsoft.com/meamcs/2015/03/19/adding-sql-server-availability-group-ag-to-an-exiting-failover-cluster-instance-fci-part2-of-2/

Advertisements