Archive for the ‘SQL’ Category

Windows 2012R2 Failover Cluster With SQL Server 2014 AlwaysOn Options #part1 #cloud #azure #winserv #SQL #msteched

With the new version of SQL server 2014 there are a lot of options possible for DR or some extra Configuration options.

In the Old days there was only a failover option in SQL active/passive or if you had multiple instances you could run a instance on every node this could be seen as active/active. en yes mirroring was also an option.

But now the naming is different and there are a lot more configuration options. Remember “ my SQL is running on bare metal much faster “ eh this is not that long ago.  Configurations with a Scale-out file server is not yet common but more and more configurations are using it. Now that SQL Server 2014 can store on CSV. In the following 3 blog post I will show you how to create all this bottom up. easy playground. A lot of terms will pass along like FCI WSFC Azure CSV, FTW SQL LOL

But the Two main options on SQL for clustering are :

AlwaysOn Failover Cluster Instances (SQL Server)

AlwaysOn Availability Groups (SQL Server)

 

AlwaysOn Failover Cluster Instances (SQL Server)

Failover cluster instance (FCI)  is in short the old active/passive configuration – Protection level SQL Server / instance

As part of the SQL Server AlwaysOn offering, AlwaysOn Failover Cluster Instances leverages Windows Server Failover Clustering functionality to provide local high availability through redundancy at the server-instance level—a failover cluster instance (FCI).

An FCI is a single instance of SQL Server that is installed across Windows Server Failover Clustering nodes and, possibly, across multiple subnets. On the network, an FCI appears to be an instance of SQL Server running on a single computer, but the FCI provides failover from one Windows Server Failover Clustering node to another if the current node becomes unavailable.

An FCI can leverage AlwaysOn Availability Groups to provide remote disaster recovery at the database level.

AlwaysOn Failover Cluster Instances (SQL Server)

As the “SQL Server (MSSQL001)” is installed on two nodes the instances and the DB are fault tolerant but needs shared storage

This is a AlwaysOn Failover Cluster Instances (SQL Server) FCI solution.

When a SQL Server instance is configured to be an FCI (instead of a standalone instance), the high availability of that SQL Server instance is protected by the presence of redundant nodes in the FCI. Only one of the nodes in the FCI owns the Windows Server Failover Clustering resource group at a time. In case of a failure (hardware failures, operating system failures, application or service failures), or a planned upgrade, the resource group ownership is moved to another Windows Server Failover Clustering node. This process is transparent to the client or application connecting to SQL Server and this minimize the downtime the application or clients experience during a failure.

The following lists some key benefits that SQL Server failover cluster instances provide:

  • Protection at the instance level through redundancy

  • Automatic failover in the event of a failure (hardware failures, operating system failures, application or service failures)

  • Zero reconfiguration of applications and clients during failovers

 

AlwaysOn Availability Groups (SQL Server)

The AlwaysOn Availability Groups feature is a high-availability and disaster recovery solution that provides an enterprise level alternative to database mirroring. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of read-write primary databases and one to four sets of corresponding secondary databases.

Deploying AlwaysOn Availability Groups requires a Windows Server Failover Cluster. To be enabled for AlwaysOn Availability Groups, an instance of SQL Server must reside on a Windows Server Failover Cluster node, and the Windows Server Failover Cluster and node must be online. Furthermore, each availability replica of a given availability group must reside on a different node of the same Windows Server Failover Cluster.

AlwaysOn Availability Groups supports cross-cluster migration of availability groups for deployments to a new Windows Server Failover Clustering. A cross-cluster migration moves one availability group or a batch of availability groups to the new, destination WSFC cluster with minimal downtime.

By implementing AlwaysOn SQL Server FCI an availability replica can be hosted by either a standalone instance of SQL Server or an FCI instance. Only one FCI partner can host a replica for a given availability group.

AlwaysOn Availability Groups does not depend on any form of shared storage. However, if you use a SQL Server failover cluster instance (FCI) to host one or more availability replicas, each of those FCIs will require shared storage as per standard SQL Server failover cluster instance installation.

You might need to configure a Windows Server Failover Clustering (WSFC) cluster to include shared disks that are not available on all nodes. For example, consider a WSFC cluster across two data centers with three nodes. Two of the nodes host a SQL Server failover clustering instance (FCI) in the primary data center and have access to the same shared disks. The third node hosts a stand-alone instance of SQL Server in a different data center and does not have access to the shared disks from the primary data center. This WSFC cluster configuration supports the deployment of an availability group if the FCI hosts the primary replica and the stand-alone instance hosts the secondary replica.

 

The following lists some key benefits that AlwaysOn Availability Groups provide ( depends on your configuration ):

  • No shared disk needed

  • Only Database protection

  • Zero reconfiguration of applications and clients during failovers

 

AlwaysOn Failover AG (SQL Server)

As the screenshot shows it hold a availability group with a listner. The configuration is only visible in the SQL server manager

This sounds great new options more but how to configure them and how about Azure In the next post I will show you how to create all this.

In the following I created a Cluster connected to azure with a Site to Site VPN. And will show you the HA options this will be in several steps else it would be a long post.

But along the choices there are a lot of options that can be a problem with your configuration or maybe not the best option. And maybe you need a 3th party product the get the job done. Like Datakeeper my fellow Cluster MVP David Bermingham is SteelEye’s Director of Product Management.

In the next part I will start with AlwaysOn Failover Cluster Instances (SQL Server) Followed By AlwaysOn availability group (SQL Server) and Azure Failovers.

Posted May 14, 2014 by Robert Smit in SQL, SQL Server 2014

Tagged with

Windows Server 2012 R2 with SQL Server 2014 Failover Clustered Instance #Step-By-Step #AlwaysOn AvailabilityGroups What can go Wrong! Part 1

 

There are a lot of good blog post about how to setup your Availability group, in two blog post I will try to break the basic setup and will show you what you should not do in your production environment. Just because you can does not mean I should do this.

This blog post will also show you most common errors and how to fix them and where to find the errors, but in the end you will have a working two node cluster and one Availability group

First how to setup an Availability group  to make things more complex there are multiple instances, see how they look Naming convention is really important when you do complex configurations. an typo is quickly made!

 

SQL Server 2014 Failover Clustered Instance (FCI)

Deploying AlwaysOn Availability Groups requires a Windows Server Failover Clustering (WSFC) cluster. To be enabled for AlwaysOn Availability Groups, an instance of SQL Server must reside on a WSFC node, and the WSFC cluster and node must be online. Furthermore, each availability replica of a given availability group must reside on a different node of the same WSFC cluster. The only exception is that while being migrated to another WSFC cluster, an availability group can temporarily straddle two clusters.

AlwaysOn Availability Groups relies on the Windows Failover Clustering (WSFC) cluster to monitor and manage the current roles of the availability replicas that belong to a given availability group and to determine how a failover event affects the availability replicas. A WSFC resource group is created for every availability group that you create. The WSFC cluster monitors this resource group to evaluate the health of the primary replica.

The quorum for AlwaysOn Availability Groups is based on all nodes in the WSFC cluster regardless of whether a given cluster node hosts any availability replicas. In contrast to database mirroring, there is no witness role in AlwaysOn Availability Groups.

The overall health of a WSFC cluster is determined by the votes of quorum of nodes in the cluster. If the WSFC cluster goes offline because of an unplanned disaster, or due to a persistent hardware or communications failure, manual administrative intervention is required. A Windows Server or WSFC cluster administrator will need to force a quorum and then bring the surviving cluster nodes back online in a non-fault-tolerant configuration.

Primary on an FCI with a replica on a different FCI

Windows Server 2012 R2 Failover Cluster with SQL Server 2014 Failover Clustered Instance (FCI) #Step-By-Step #AlwaysOn Availability Groups image

I have a lot of SQL instances and this all runs on a two node Cluster and not all instances are installed on both nodes to trick the installation and to show you the errors you can expect.

image image imageimage

Enabling the AlwaysOn and you can see the Difference the new AG Wizard is not grayed out any more .

Windows Server 2012 R2 Failover Cluster with SQL Server 2014 Failover Clustered Instance (FCI) #Step-By-Step #AlwaysOn Availability Groupsimageimage

Starting the Wizard  and on system Databases it wil not work AG will only work on your own DB !

image You must make a full backup of your DB before you start ( this is always handy )

Now we can add a new replica Check the Server and as it is a Cluster you can not set it to automatic failover.

image image

 

 

We do setup a Data share for the replication

image

Now that we have completed the wizard we do the validation and go for the finish.

image

An Error ? checking the location ?? eh what should the DB be on the same location ?? not all my SQL server Cluster are the same and are not using all the drive letters. and as I choose to do this on the same cluster ( not supported ) I can not give the other instance the same drive letter. but hat if I had an other cluster and even then I did not have the same Drive letter. Is there a wizard bypass some where.  Wizards are nice If you have a default installation. If not Plan B.

image

TITLE: Microsoft SQL Server Management Studio
——————————

Checking for compatibility of the database file location on the secondary replica resulted in an error. (Microsoft.SqlServer.Management.HadrTasks)

——————————
ADDITIONAL INFORMATION:

The following folder locations do not exist on the server instance that hosts secondary replica MVPSQL201402SQL2:
i:MSSQL11.SQL001MSSQLDATA;
(Microsoft.SqlServer.Management.HadrTasks)

So placing this on a CSV SQL server 2014

Well if drive letters is an issue SQL Server 2014 can store the DB on a CSV so no more drive letters.

And It passed the Validation that is Great.

Great thinking but.. the CSV is connected to all SQL servers So the next error is logical.

image

Yes the DB is already there.. what now ?

Manually Creating an Availability group for a SQL Server 2014 FCI

This sounds great but where to start ? should I bing It ? Let Me Bing That For You!

Well I Create it with a SQL script ( I’m no SQL master  ) So things can be different.

However you can run this in a SQL CMD but here I do this step by Step.

I have My SQL Availibility group name, My DB name,IP, Servers

 

image

CREATE AVAILABILITY GROUP SQL001AG04
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR DATABASE AG04 –, …
REPLICA ON — primary:
N’MVPSQL201401sql001′ WITH (ENDPOINT_URL = N’TCP://MVPSQL201401.mvp.local:5023′,
FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)),
— secondary:
N’MVPSQL201402SQL2′ WITH (ENDPOINT_URL = N’TCP://MVPSQL201402.mvp.local:5022′,
FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));

ALTER AVAILABILITY GROUP SQL001AG04
ADD LISTENER N’MVPLST01′
(WITH IP ((N’10.255.255.69′, N’255.255.255.0′)), PORT=1433);

BACKUP DATABASE AG04 TO DISK = ‘\mvpfsw01SQLAG04AG04.bak’
WITH INIT, COPY_ONLY, COMPRESSION;

BACKUP LOG AG04 TO DISK = ‘\mvpfsw01SQLAG04AG04.trn’
WITH INIT, COMPRESSION;

image

 

 

 

 

 

 

 

Now we go to the replica server and run the script below.

—————- Run this on The Replica Server!!!!!!!

 

ALTER AVAILABILITY GROUP SQL001AG04 JOIN;

RESTORE DATABASE AG04 FROM DISK = ‘\mvpfsw01SQLAG04AG04.bak’
WITH REPLACE, NORECOVERY, NOUNLOAD,
MOVE ‘AG04’ TO ‘E:MSSQL11.SQL001AG04.mdf’,
MOVE ‘AG04_log’  TO ‘E:MSSQL11.SQL001AG04_log.ldf’;

RESTORE LOG AG04 FROM DISK = ‘\mvpfsw01SQLAG04AG04.trn’
WITH NORECOVERY, NOUNLOAD;

ALTER DATABASE AG04 SET HADR AVAILABILITY GROUP = SQL001AG04

 

But as You can see in the screen shot it is not working the secondary server is down.

the following error is showing :  The connection to the primary replica is not active.  The command cannot be processed.

Message
A connection timeout has occurred while attempting to establish a connection to availability replica ‘MVPSQL201401sql001′ with id [F82BBD94-4F04-4B0A-8B75-28A0899F240C]. Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance.

 

Ok I did turnoff all the Firewall, checked the network set permissions now what.

Think :  I have two cluster nodes both are using SQL in the script what do they have in common.

ENDPOINT_URL = N’TCP://MVPSQL201401.mvp.local:5022’

So changed it from 5022 to 5023 and it work like a charm

In the next post I will explain how to check this and how to change thisWinking smile

 

image

 

 

 

 

 

 

 

 

SO basically it is better to use the script that the wizard well it depends For now in the demo environment running on two different disks it is better and It would be better if the wizard ask you about drive letters or storage locations.

But manually you have more control about the setup and if something fails you can fixit before you go further.  But also you have to think about a lot of issues Winking smile

Next will be Part 2

More Errors and more fixes on   SQL Server 2014 Failover Clustered Instance (FCI) with Step-By-Step #AlwaysOn Availability Groups #winserv #FCI

Use Cluster Shared Volumes / StoragePools in a Windows Server 2012 For #SQL 2014 DB Storage. #TEE13 #WPC13

 

In my previous Blog in SQL Server 2014 Create a New #SQL Server Failover #Cluster (Setup) in 5 minutes #SQL2014 #Windows2012R2  http://tinyurl.com/l7oe8sa

I created an unattended installation of a SQL server 2014 cluster but one of the new features is that you can also use CSV.

A new feature in SQL Server 2014 is that you can now use a Cluster Shared Volume (CSV) in addition to the other methods you already could use for database and backup storage (drive letter, mount point, SMB share, local tempdb – the last two were introduced in SQL Server 2012). The big benefit here is that if one node of the SQL Server cluster loses connectivity to the storage, it can still read and write data over the network to a different node’s SAN connection. If you are using Ini files just replacing the value in the ini file with the cluster storage volume or storage pool.

; The Database Engine root data directory.

INSTALLSQLDATADIR="C:\ClusterStorage\Volume1"

In the next setup that is based on clusters I have two CSV volumes one is a normal CSV and one is a storage pool CSV.

Storage pools give you extra redundancy and if used by a scale out file server more IOPS or at least better iops than when you use only one server.

imageimage

As you can see no disk in the Cluster Resource if you are using CSV!

What is a CSV ? Well, it’s a way to configure a single shared disk that is presented to and can be used by all nodes of a Windows Server failover cluster.CSVs have been used with Hyper-V and virtual machines (VMs) in windows server 2008/2012.

image                    image

Looking at the setup the advanced cluster preparation is the same as on any other installation. The cluster completion is also the same but on the disks

just replacing the value in the ini file with the cluster storage volume or storage pool

FOR CSV

; The Database Engine root data directory.

INSTALLSQLDATADIR="C:\ClusterStorage\Volume1"

FOR Shared Disks

; The Database Engine root data directory.

INSTALLSQLDATADIR="E:"

 

The big benefit here is that if one node of the SQL Server cluster loses connectivity to the storage, it can still read and write data over the network to a different node’s SAN connection.

  • Greater scalability of compute, networking and storage with Windows Server 2012 R2 – SQL Server 2014 CTP1 supports Windows Server 2012 R2 CTP, so you can start to test the scalability gains Windows Server 2012 R2 provides for SQL Server:
    • Increased compute scale – Continue to benefit from scale for up to 640 logical processors and 4TB of memory in a physical environment and up to 64 virtual processors and 1TB of memory per VM.

    • Network virtualization and NIC teaming – Abstracts networking layer so you can easily migrate SQL Server from one datacenter to another. Increase network throughput for your mission critical SQL Server workloads by allowing those workloads to access more than one network card.

    • Storage virtualization with storage spaces – Create pools of storage and storage tiers allowing your hot data to access the premium storage and cold data to access standard storage improving resilience, performance and predictability.

 

image

But the best part is SQL is now supporting Clustered shared Volumes

SQL Server 2014 is that you can now use a Cluster Shared Volume (CSV) in addition to the other methods you already could use for database and backup storage (drive letter, mount point, SMB share, local tempdb – the last two were introduced in SQL Server 2012). The big benefit here is that if one node of the SQL Server cluster loses connectivity to the storage, it can still read and write data over the network to a different node’s SAN connection.

just replacing the value in the ini file with the cluster storage volume or storage pool

; The Database Engine root data directory.

INSTALLSQLDATADIR="C:\ClusterStorage\Volume1"

; The Database Engine root data directory.

INSTALLSQLDATADIR="E:"

More about this in my next blogs.

http://tinyurl.com/l7oe8sa

Posted July 9, 2013 by Robert Smit in SQL

  • Tag