SQL Server 2014 Create a New #SQL Server Failover #Cluster (Setup) in 5 minutes Source Files   Leave a comment

When I started this blog post it was more a can I create a Fully cluster in 5 minutes and with 10 min extra a two node cluster and loaded with a two instance cluster. Well I could.

If I had better hardware SSD/fusionIO or other SMB 3.0 huge etc it would be much faster ( donations are Welcome  Winking smile ) Joking

I posted the vid on youtube and the blog and it seams it is not as common as I thought. no next next Finish Deployment.

As you already know deployments are time eating preparations.  But once you have it in place it rocks.

So I’ll place an update on the source files remember change the domain/user account server names

Old Source blog :

http://robertsmit.wordpress.com/2013/07/08/sql-server-2014-create-a-new-sql-server-failover-cluster-setup-in-5-minutes-sql2014-windows2012r2-wpc13/

Get the ini files here http://sdrv.ms/12dqaya  ( logon with your Microsoft Passport )

Watch this new video I made  http://youtu.be/UyqNY5JyE9k

 

In the source file there are image  Create SQL CSV Clustered instance and join other node to the instance.

 

image With the create cluster name IP , bind ISCSI etc and one Extra SQL install with out CSV also in 3 steps.

 

All the Files are there. just as an sample on how to do this.

http://1drv.ms/1jgOXzk

 

Have Fun!

@clusterMVP

http://robertsmit.wordpress.com

Posted July 10, 2014 by Robert Smit [MVP] in SQL, SQL 2012, SQL Server 2014

Tagged with

Microsoft Azure Hybrid deployment of RemoteApp Step by Step @Azure #Microsoft #RemoteApp #MVPBuzz #RDS #HRDAAS   Leave a comment

In this blog post I’ll show you how to create a Hybrid RemoteApp Configuration. It is still a Preview and Improvements  could be made.

If you want to run your own applications in Azure like on Citrix or with RDS till now it was not a build in services Microsoft provided.

But with RemoteApp you can easy deploy a default set as below or Create a Hybrid Environment. And there are lots of new options in a Hybrid RDS Azure Site #HRDAAS Hybrid Remote Desktop As a Service

image

The Quick Create is no fun just hit Create Smile

Now Creating a Hybrid Environment. You will need a syspreped Template with no unattend.xml in it. There For I created a fresh new template clean install with Remote Desktop Session Host installed and most important you need to set a registry key if you don’t do this all will fail!

This is not in the Microsoft Guide !

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem]
"NtfsDisableEncryption"=dword:00000001

image

After everything is Set on the Golden Image we can do a Sysprep. And keep in mind Azure support only VHD files so do not use VHDX files.

C:\Windows\System32\sysprep\sysprep.exe /generalize /oobe /shutdown

now that my golden Image is ready We can upload this image to azure.  ( I used a 50 Gb VHD file ) So If you don’t have a fast Internet connection You need to wait Winking smile a while.

 

So the next step is Create a RemoteApp With VPN. Pick a name and select create.

image

A place holder is Created and We will upload the Golden Image To Azure. ( this could take a while so we do this now )

Select the image and pick image Upload

image A popup will Come and a PowerShell script is there for download

image

Save this on place. Grab also the Command To run

Upload-AzureRemoteAppTemplateImage.ps1 -SAS "?sr=b&si=623fcaf1-63f6-406d-a749-48c2c3f0036b&sig=n%2FHLp5d1wfEgdi68hA%2FaVWOwyhDl35S1eKQ9dKxZdQg%3D" -URI https://cdvwe114758920rdcm.blob.core.windows.net/goldimages/623fcaf1-63f6-406d-a749-48c2c3f0036b.vhd

image

Running this Script a Browser will open and you can select your Golden Image.  And the Upload will start first some checking

image I was happy with my Fiber internet connection.

Writing a blog and uploading Winking smile

image

when the upload in done you will see a check and a ready state on the Image.

imageimage

Next Step is creating a network Is this also my improvement point Whay not using my Site to Site VPN that already is connected to my environment.

Therefor we create a new network that will host the Machines.

Create New network and pick a name.

imageimage

Fill in the IP networks and use the Internet address of your Router ( Same as S2S VPN )

image  image image

When the Network is created we can download the S2S Script and run this on your Gateway server. I use a Windows 2012R2 RRAS server but there are other options. image

After running the script the Gateway has an extra dail-in option On this gateway I have already a few connections but this is all configurable to your needs.

 

image

Now that the network is ready We can start building. When opening the MYMVPAPP with VPN created in the beginning you will see the Status with check marks

Now I have to create a OU and a service account in my domain on-premise

imageimage

Next step is linking the Golden Image To the App. You can do all this is now with a upload but as we already did the we need only a connection this is a faster and fail save method.

So check the Link an existing Image

imageimageimage

You will see your 50Gb uploaded golden Image. This is only available if the Image is correctly uploaded !

image

We are almost ready, while the provisioning can take up to 30 minutes. Second Improvement point. Show Some Status step 1 from 10 maybe. Now we have to wait.

image  image

Please Note** during several test I change the name but the steps are the same

the next step is assign a user to the image , this must be a default Azure directory User.

 

image  image

I created a demouser ( I deleted this user after this blog )

We assign this user to the Remote App

image

Next we assign Applications of the Image I created earlier.  I did not install special apps in this image so it looks a bit basic.

imageimage

I published all the apps that Azure Scanned for me in my own image. but suppose you have installed office and other apps it will be there in the image.

Next step is login to the RDP session image

 

image The fun part is I configured MFA ( multi factor Authentication ) Really nice option

When Logged on We see all the Apps I made a Text change in de Word app

image

However I logged on the session is still inactive

image

This is still a Preview what I really like is using your own apps in your own domain there are still some issues with the creation of the RemoteApp but if you have configured all this and it is working you can even use this On a Android and or on your Surface how Cool is that.

I installed the latest version of RDP on my android phone I opened a CMD box on my phone totally useless but you can. It works much better on my Surface.

Running Published Apps on my Devices in Azure What will ne next ?

 imageimage

#HRDAAS Hybrid Remote Desktop As a Service

Posted June 20, 2014 by Robert Smit [MVP] in Microsoft Azure

Tagged with

Microsoft Azure File Server “System error64” or “New-AzureStorageShare Cannot bind parameter Context“ #azure #cloud #MVPBuzz   2 comments

Microsoft Azure launched a new Option in the Azure Suite a cloud File server. Sounds great how does it work and how to set this up.

First you need to create a new Storage Account

image

When this account is created you will see a new option in the Dashboard of this storage Account

image

The next steps will be in Powershell, there is no GUI option here Winking smile

Before you can connect to your Azure Files network share, we have to download a special powershell package for working with the Azure Files, the package can be found http://go.microsoft.com/fwlink/?LinkID=398183

image

After Downloading this file and extracted we can import the PSD1 file.

# import module and create a context for account and key
import-module "C:\AzureStorageFile\AzureStorageFile.psd1" –Verbose

image

Next We will create the new storage contex

image
$ctx=New-AzureStorageContext ‘rsmfile01′ ‘q+35EmhlLUikunngOWvZK8ysEqWpOLaobJNbS7bUtjTDZIRUI72siY956xHEVCS8ckFq5Vo188hmFfTY1XdPeQ==’

Use the Name and the Primary key.

Next step is creating a new Share and this can be tricky why ? well you may have the preview as enabled but it can be still not activated. if so you need to make a call to the Support team from Azure.

a simple check will do the trick Can you ping the DNS name ?

image

But if the result is this :

image

You can’t create a share and will se an error

New-AzureStorageShare : Cannot bind parameter ‘Context’. Cannot convert the "Microsoft.WindowsAzure.Commands.Storage.Model.ResourceModel.AzureStorageContext" value of type
"Microsoft.WindowsAzure.Commands.Storage.Model.ResourceModel.AzureStorageContext" to type "Microsoft.WindowsAzure.Commands.Storage.File.Model.AzureStorageContext".
At line:2 char:48

image

 

But if your account is enabled and activated it will work

image

# create a new share
$s = New-AzureStorageShare ‘newshare1′ -Context $ctx
 
# create a directory in the test share just created
New-AzureStorageDirectory -Share $s -Path testdir

 

The next step is mounting the fileshare to a drive letter.

image

net use * \\rsmfile01.file.core.windows.net\newshare1 /u:rsmfile01 q+35EmhlLUikunngOWvZK8ysEqWpOLaobJNbS7bUtjTDZIRUI72siY956xHEVCS8ckFq5Vo188hmFfTY1XdPeQ==

image

But also here this will only work from a Azure VM and not from your home computer.

image

 

Again this is just a preview Just be sure to understand the limitations of Azure Files the most important are:

  • 5TB per share
  • Max file size 1TB
  • Up to 1000 IOPS (of size 8KB) per share
  • Up to 60MB/s per share of data transfer for large IOs
  • SMB 2.1 support only

But for most parts this is fine just another great @azure option !

Posted June 16, 2014 by Robert Smit [MVP] in Microsoft Azure

Tagged with

Advisor Limited Public Preview #Advisor #cloud #Scom #ch9   Leave a comment

At Teched 2014 NA a new preview was launched the Advisor Limited Public Preview’ service. If you don’t have an account you can create one here

https://preview.systemcenteradvisor.com/

More info is below on the Channel 9 site :

 

clip_image001VIDEOS

Advisor Preview 2min Overview Video: http://aka.ms/unrpst

Advisor Preview Onboarding Steps Video: http://aka.ms/Lgt2zu

Advisor Preview TechEd announcement Video: http://aka.ms/Aulpqc

image

There are some great new features to test these are not default there you need to add them

image      image

Below is a nice overview of the rules that you can switch on or off.

image  

Go for the preview

image

Posted June 3, 2014 by Robert Smit [MVP] in System Center Advisor

Tagged with

AlwaysOn Availability Groups (SQL Server) Connecting To #Azure #part3 #AlwaysOn #winserv #SQL #msteched #mvpbuzz   Leave a comment

As we did AlwaysOn FCI we make a step into the AlwaysOn AG. The Configuration options are divided with a lot of options. But the methods are the same. Pardon I did already a post http://robertsmit.wordpress.com/2013/09/12/windows-server-2012-r2-with-sql-server-2014-failover-clustered-instance-step-by-step-alwayson-availabilitygroups-what-can-go-wrong-part-1/

As there are a lot of extra options to extend your SQL server and give your DB the HA feeling. I hope the next post will give you insight in a how to get there. In a follow up post I will explain the Azure and extra options of SQL Server 2014.

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.

 

AlwaysOn Availability Groups

I already had my cluster in place with the SQL AlwaysOn FCI and I have also installed a Second Cluster and a Second Instance on the cluster and already extended the SQL site to Azure and with several standalone server.

Before we start we need to enable the AlwaysOn HA option in on the server this is only done on the running server and is cluster aware. One setting for all the nodes for the same instance!

When we tried to enable the AG it is grayed out. in the SQL management.

image

Go to the SQL Server Configuration Manager

image image

When you are connecting to the passive node on the cluster you will see this, on a standalone install you can only connect to the active node.

Go to the other node and Set this setting. You can only change this setting on the running node that hold the SQL server

image  image

Now that we enabled the AlwaysOn Availability Groups we can start the wizard in SQL

image Pick a name for the AG

image

I just created a dummy DB just to set this up and I will later Add the real DB.

The dummy DB needs to have a full Backup ! So If your DB is as large as a TB a full backup is needed.

image

This is a interesting Screen Lots of Options and also Connections To Azure.

First we do a on premise connection and build a Replica to Azure.

and make a choice “ add Replica “  When we select the add replica a SQL login screen will popup.

 imageRemenber you need to connect to the replica SQL server.

This server is my standalone instance but installed on a failover cluster.

and as you can see I connected My Cluster SQL Server with the CSV installation now to a local SQL instance installed on Cluster Node 4

Some basics you need to know when connecting :

  • All the cluster nodes must be in the same Active Directory Domain Services (AD DS) domain.
  • Each availability replica in an availability group must reside on a different node of the same Windows Server Failover Clustering (WSFC) cluster.

  • The cluster creator must have the following accounts and permissions:

 

image

The Chosen Server is selected and added to secondary. In a cluster there is no automatically failover!

Readable secondary: No
In the secondary role, this availability replica will not allow any connections. I’ll use this pure as a backup and no changes will be made in the backup location. If the cluster is failing I have more problems than a not working Application.

All the options can be set but If you have multiple instances (AlwaysOn FCI ) and installed a local standalone Instance You may need to change the Endpoint Port! the default is 5022. I changed the port to 5023 just to make sure that there is no problem on my server.

image

Changing the port is easy “ SELECT * FROM sys.tcp_endpoints “  will show you the ports.

image

With “ ALTER ENDPOINT [hadr_endpoint] AS TCP (listener_port = 5023) “ you change the port to a better one.

imageimage

imageimageimageimage

Normally If you run this wizard and doing this steps you are fine, but in my demo site I had already a connection to Azure and therefor my listener want not only a local IP but also an Azure IP as described in the error message.

But this error is not saying he you need to do this again no simple add an IP address to your listener  You can Add this by hand or create listener in SQL

 

imageimage 

image

As you could see I needed to add a second IP for my listener that is I already setup a failover to azure.

In the fist step we could choose Azure Replica or a replica

image  And I dis the Azure Replica and If you are not already connected and added the thumbprint to your SQL server then you need to do this.

Just click Download and the Azure Login will popup and you need to login with the Azure Admin account that can create the Azure VM

 

image  When check the down the Azure login screen will pop up

 

imageimage

a quick connection screen will popup and does fill in your subscriptions. If you have multiple just pick the right one.

So after connecting and downloading you will have the following. Reminder there is only creating NEW VM available ! If you want to use an existing VM then use the add replica just as in a normal situation.

image

The bad thing is here you can not pick a SQL server that already is build. But in the Screenshots you will see this is much easier. But it would be nice to tweak this a bit. It Would be handy if you could also pick an existing VM.

After filling in my name and version Size We can go to the next step. keep in mind you can always lower the size of the VM but now it is faster and the setup process will be quicker.

imageimage

As you can see the Azure Replica server is added.

image

As I connected the Azure SQL with a Azure Gateway to my LAB environment we can share files thru the domain.

image

The wizard kick in and we have to wait until it is done. I did not create a listener for this, I just want to replicate the data to Azure.

image

Real Pity that there is no export to script I would like to see the script that created my azure SQL VM

image

The progress screen an this can take a while. With a quick peek in Azure We can see this.

image

This is a Critical Point I have done this now several times and sometimes it fails in a time out , and I found out that I used most of the time a small server and then the script will fail with “Error” so a quick tip use the default size and adjust this after the creation.

Checking the VM 4 cores and with an extra disk from 1TB holding my DB

image 

My Lessons learned

As you can see there are multiple disks and the Wizard has run successfully.

My source was clustered and the DB is running on a CSV. Witch s a bad choice for running a Replica. The reason is the Replica wizard want to see the same disk and placing the DB files on C is no problem but a CSV volume placed  C:\ClusterStorage\Volume1\MSSQL12.MSSQL001\MSSQL\DATA

and this path is available for every cluster node and therefore also in the Azure cloud. and the “ normal” wizard tells me he the DB is already there. but now this step is skipped.

Second mistake I used a sample DB. there is no way I can add a second DB because of the CSV problem “ Database is already there “ and this is the Source DB Winking smile I think this will be better in the next version. Or not using CSV with AlwaysOn AG

image

Now that the wizard is done and a lot of scripting is passed the line to azure. But what is changed and does it work ?

imageimage

no votes and an extra node image

The replica is created and as shown in the dashboard replicated.

Note:

The Following Issue can happen when you use CSV and or you want to create a replica from FCI to FCI. The reason is the disk letter need to be the same on source and destination, as the CSV volume is mounted to every node and therefor the DB is already there and the setup will fail.

imageimage

image image

imageimage

Right I use CSV but is the CSV replicated to Azure Yes the cluster does this! But there is no disk mounted in azure and all the files will be placed on the c drive! and the replica can not be created on the location because the source DB is there. If you create the replica by hand you can do this but not by default with the wizard.  just a reminder when you playing with this.

imageimageimage

There are some options when you enable AlwaysOn the easiest way is having standalone SQL server running on a cluster node. More advanced is using AlwaysOn FCI. But all this can be done just test everything before you go in production . So that you know how your configuration is working.

And just because you can will not say this is your best solution or design. There are many options and will grow are products evolve.

 

SQL Server AlwaysOn Availability Group concepts


A SQL Server Availability Group enables you to specify a set of databases that you want to fail over together as a single entity. When an availability group fails over to a target instance or target server, all the databases in the group fail over also. Because SQL Server 2012 can host multiple availability groups on a single server, you can configure AlwaysOn to fail over to SQL Server instances on different servers. This reduces the need to have idle high performance standby servers to handle the full load of the primary server, which is one of the many benefits of using availability groups.

An availability group consists of the following components:

  • Replicas, which are a discrete set of user databases called availability databases that fail over together as a single unit. Every availability group supports one primary replica and up to four secondary replicas.

  • A specific instance of SQL Server to host each replica and to maintain a local copy of each database that belongs to the availability group.

Replicas and failover

The primary replica makes the availability databases available for read-write connections from clients and sends transaction log records for each primary database to every secondary replica. Each secondary replica applies transaction log records to its secondary databases.

All replicas can run under asynchronous-commit mode, or up to three of them can run under synchronous-commit mode. For more information about synchronous and asynchronous commit mode, see Availability Modes (AlwaysOn Availability Groups).

NoteNote:

Database issues, such as a database becoming suspect due to a loss of a data file, deletion of a database, or corruption of a transaction log do not cause failovers.

Read the following articles to learn required and important concepts about SQL Server AlwaysOn technology:

  • For details about the benefits of AlwaysOn Availability Groups and an overview of AlwaysOn Availability Groups terminology, see AlwaysOn Availability Groups (SQL Server).

  • For detailed information about prerequisites, see Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server). This article contains the following information:

    • Windows Server system requirements and recommendations

    • SQL Server instance prerequisites and restrictions

    • Prerequisites and restrictions for using a SQL Server Failover Cluster Instance (FCI) to host an availability replica

  • Availability group prerequisites and restrictions

  • Availability database prerequisites and restrictions

  • Now Reading

    Microsoft Windows Server vNext
  • Follow

    Get every new post delivered to your Inbox.

    Join 1,207 other followers

    %d bloggers like this: