WSFC Lost Quorum votes due to network problem

Problem:

Early in the morning I received emails requesting for support as the applications were failing to connect to one of the production servers that are configured with Always On, I asked for an specific error and the customer sent me this:

---------------------------

Error while updating the backup server list: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

---------------------------

OK  

---------------------------

After checking with one of the IT managers he confirmed that one of the database servers was going through some networking issues, however that didn't explain why the SQL server didn't automatically failover to the other node that was up and running, as both servers are part of the same WSFC as required to configure Synchronous availability groups with automatic failover configured.

So after getting into the available replica the troubleshoot began.

The first thing I noticed was:

After also checking the availability group dashboard which also confirmed the error state I went to the Failover cluster manager and noticed the following errors:

Also noticed that the node that was under the issues was showing that the Failover cluster state was stopped. Obviously if the server was not able to be reached through the network the cluster wouldn't be able to show it as healthy or running. But because lf that and the fact that there was no third witness the entire cluster state was failed and due to that the availably group and Listener were down as well, so what was needed? getting the cluster up and running even if one node is missing, but how would you do that? Well you need to start the cluster without the quorum validation, with that you will have the WSFC running and in return the availability group and listener will be up as well.

But wait, I'm a DBA that means that my experience with managing clusters at the windows level is minimal. If that is the case you have 2 options call IT to get the assistance you need or do it yourself, In my case was the second, if you choose the same, in the following URL are the required steps
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/force-a-wsfc-cluster-to-start-without-a-quorum?view=sql-server-2017

In my particular case the steps with the windows interface didn't work but the PowerShell ones did.
This got my cluster running, however my Availability group still had as primary the failed server, so I still had to failover the group to the one working, I achieved that by forcing the failover with this command:

ALTER AVAILABILITY GROUP <DAG NAME> FORCE_FAILOVER_ALLOW_DATA_LOSS

I know, it is scary when you see "Allow data loss" however, I had Synchronous commit set, so worst case scenario, I would be loosing the transactions that weren't committed at the time the primary server went offline so in the end that wasn't even written in the Primary database and it was safe to roll them back if needed.

That brought my databases online and the applications could resume working.

After fixing all the network issues and noticing that you are able to connect to your secondary replica execute this command to restart the synchronization, make sure that you update it for each of your databases.
ALTER DATABASE database_name SET HADR RESUME

And bam, you are all set, your cluster and Database availability group is back to the state it was before the failure.

As a lesson learned, add a witness to a file server, so that you don't loose your Quorum and prevent this issue to happen.

Thanks for reading!

Offline installation of SQL Server 2017 on Linux

SQL Server is now compatible with different Linux distributions, and there is a lot of excitement about it.

How ever one of the main questions when ever you say SQL Server 2017 is now supported in Linux is, Have you test it? how do you install it?

By looking in the web you can find a lot of post and videos related to it, however most of them are based with Azure VM's (one great post I found is from a good friend that you can find here) or there is also the assumption that you will have an internet connection from your server, but what if not? What if you are a financial institution that is PCI-DSS compliant and the direct connection to the web is blocked or simply your network administrator doesn't want to allow you access to the Microsoft repositories from the network your server is running or you created your VM and the connection is not shared with your host? 

Well, let me tell you there is an option, you are not deemed to fight against the network engineers to enable you the access and here I'll show you the steps.

Pre-requesites:

1. A machine (VM, physical, etc) with Linux 
1. Red Hat Enterprise Linux (7.3 or 7.4)
2. SUSE Linux Enterprise Server (v12 SP2)
3. Ubuntu (16.04)
4. Docker (1.8+)
2. The latest version of SQL Server 2017 downloaded you can get it here and copied onto your Linux machine.
3. A user to run the install (different than root)

Installation Steps

1. Connect to your Linux machine. You can use Putty or the SSH client of your preference 
2. Locate the folder where you have the installer, in my case I put it on "/home/SQLInstall/
3. execute this command: Sudo yum localinstall <name of the installer> in my case the command looked like Sudo yum localinstall mssql-server-14.0.322.28-2.x86_64.rpm .After executing the command it will ask for the password of the user you are connected with to confirm the permissions and the execution command, then it will start the examination of the media and validate all the dependencies, that will look just like this:

 

   4. If you look at the bottom line, it stops at the Question if the size of the package and the size  matches, if the software is the one you would like to install etc, if everything is correct you will then need to Write the letter "y" to confirm.

   5. After confirming and hitting enter, the process will start to deploy the files in the system, however you will need to pay attention to the following message:

It says "complete!" but there is a remark "Please run 'sudo.... to complete the setup...' " so go and execute the command, otherwise SQL Server won't be installed as it only exported the files but didn't actually installed the engine and you will see why in the next step.

   6.  After executing that command, it will ask you to choose the version you would like to install

   So make sure to enter the right version, In my case I choose 2, as it won't support a production environment and is only for demo purposes.

   7.  After selecting the version, it will ask you to agree with the license terms and you will need to write "Yes" if you accept it or "No" if you don't.

   8.  Then it will ask you to configure the "sa" password.

   9.  After configuring the password the installation will succeed. However one of the problems you will face after getting it installed, is that you can't connect from outside of the server itself, so what you need to do is to enable the port through the firewall, and you can do that by running the following commands:

                   sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent

                   sudo firewall-cmd --reload

The first command opens the port 1433 and the second command restarts the firewall service (sort of speak)

  10.  After doing all of the above steps and rebooting the server, you should be able to connect to your SQL Server instance, Congratulations!

Hope this steps lets you get up and running and in the case you run into any issue, let me know and I'll be glad to help and learn with you.

Thanks for reading!

P.D.: If by any chance you don't know how to get Red Hat installed, I'm preparing another post with those steps.

Create users in Azure AD

In this post I'll show you how to create new users in your Azure Active Directory so you can use them as service accounts or to grant other users to login to your Servers or services as well as how to enable them so you can use it, I didn't know that and the documentation was not as explicit as you would love, however I consolidate both tasks here.

1. Go to Azure Active Directory 

2. In the "Name" field type in the give name of the user
3. Type the fully qualified name name@domainname.onmicrosoft.com
4. Configure the Profile details
5. It won't let you change the details so you can leave it that way
6. Configure the Groups it will have access to
7. Select the role it will have in the active directory the options are (Regular user, Global Admin, Limited Admin)
8. Password: Make sure to copy the password as you are not able to retrieve it later this is auto generated and you are not able to change it, and the user is configure to change it at the first logon.
9. Before clicking create, your window will have to look like this

Alright, you have your account created, however it wont work until you synchronize the password, or in other words, until you enable your user by logging in to the active directory, however, how can you do that? This is something hard to find out there in the web, even when the steps are simple, reason why I'm including it with this post.

1. Navigate to this URL: http://myapps.microsoft.com/

2. When asked for the logon user, use the one you just created

3. Type in the password you copy from the creation window, then it will route you to this other page where it asks you to configure a new password, type in the new password. 

And you are done, once that you have successfully updated the password it will route you to this screen meaning that you successfully create and activate the account. Now you are ready to use it to join your machines to the domain, configure it as service accounts, etc.

Hope this and the post from last week lets you get started and working with Azure.

Thanks for reading!

Configure an Azure AD Domain Services

Problem: I was working on getting a demo on how to setup Always On with SQL Server 2017 without the need of creating a windows cluster, I had my two VM's in Azure and everything was working fine until the creation of the availability group, it failed and it was because the account under which the server was running was the default "NT Service" which is not allowed (not to mention is a concerning security issue) so instead I decided to join both VMs to a domain, as mentioned before, this were Azure VM's so the creation of a traditional domain as you know it on premises was a hard task to do (not to mention the cost for each of the servers) however by looking through I came across a service, Azure AD Domain Services that provides a seamless experience as a traditional domain controller, but as a service (PaaS) which is great because it does almost everything for you so here are the steps as to how to set that up.

All the steps are in this URL: https://docs.microsoft.com/en-us/azure/active-directory-domain-services/active-directory-ds-getting-started follow it thoroughly however, here are some annotations from my own experience:

Task 1: "configure basic settings"

DNS domain name: choose the name of your preference with the add of ".onmicrosoft.com" unless you have something else configured it won't check your name as valid.

Resource group: You can create a new resource group or use an existing one, it won't break anything that you have in your group if you choose "Use Existing"

Task 2: "Configure network Settings"

In here you have the option to either Create a new one or use an existing one, my recommendation, create a new one, in my first attempt I choose an existing one and spent 2 days cleaning everything up as in the middle of the creation it fail.

Task 3: "Configure administrative group"

In this task you create the equivalent of "Domain Administrators" group, so make sure that you add the members you want to be domain admins in your Azure AD (AAD).

After finishing this task, it will start the process on provisioning the resources, once this is done, your domain will be almost ready, before that you still need to configure your DNS. How to know when is done? When you go into the overview of the domain you are able to see the section "Update DNS Server settings for your virtual network"

Task 4: "Update DNS Server settings for your virtual network"

Make sure that you choose "custom" and copy both IPs onto the boxes as shown in the steps, this step is so that you can configure you DNS servers with the ip of the services (that will serve as Domain controller servers) Azure provided for you, take this as a verification step of the process.

With that you are done, you have successfully configured your Active directory as a service and you are good to go and work like if you were in an on premises domain, you will only need to configure your accounts to the services or if you didn't do it in the task #3, you can create an account to manage your domain (Join the machines, create other accounts to configure as Service Accounts, ETC).

In a following post I'll show you how to create and enable users in your Azure AD.

Thanks for reading!