Tools to connect to a SQL database

Today I'm getting back to basics, whether you are new or veteran with the database engine you need a tool to get into, write queries, do some monitoring, tuning, etc.

If you have been using SQL for at least 5 years or any version older than SQL Server 2016 you will mostly only know one tool, as it was included with the installation media, however there are other tools available that you can use with which you can achieve the same goal, and at the same time offer some other benefits. In this post I will be writing about the top 3 and that are developed by Microsoft that you can use, in another posts I'll evaluate some others but for now lets use focus on this 3.

1. SQL Server Data Tools (SSDT) - SQL Server Data Tools, was introduced as an add-on with Visual Studio 2012 meaning that it has been here for a while now, it offered a lot of features that you can only find with third party tools at an extra cost such as schema and data comparison between two databases, it also offered connectivity with a Team Foundation Services Server (TFS) among other features. In later versions you begin to be able to develop Reporting Services reports with SSDT, so it removed the need of installing the Software Development Kit within your database server or sharing some installation media with other teams for them to develop or update SSRS Reports. With the latest version you are able to connect to SQL Servers from 2005, Azure SQL, Azure SQL Data Warehouse and SQL Server 2017 on Linux. It doesn't requires any license to be installed. If you are more familiar with Visual Studio, it is easier for you to adapt to this one or even better, you can add this package to your installation and work from there without the need on having a separate tool to do the database part. You can see the official documentation and download it from here: https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017

2.  SQL Operations Studio - SQL Operations Studio is a multi-platform tool you can use to connect to databases, if you are bored and tired of the limitation that SQL Server Management Studio offered or that there was no Data Tools for Linux, let me tell you that this is your tool, you can run Operations Studio on Windows, macOS and Linux. With this tool you can do almost all the administrative and development work you used to do with SSMS, there are still some items missing from SSMS, however most of the ones you used on a daily basis are here. It also includes a lot of reports you are used to see in SSMS with richer format. It doesn't requires any license to be purchased. It is a light weight file compared with the traditional management studio. You can download it for free here: https://docs.microsoft.com/en-us/sql/sql-operations-studio/download?view=sql-server-2017

3. SQL Server Management Studio (SSMS) - As I said at the beginning of this post, if you have been using SQL Server for at least 5 years, or if you have installed any SQL Server version older than SQL 2016, you might noticed that with SQL Server 2016 there was a big change with the installation media, and is that it didn't include the option of "Management tools" instead it was in a different section in the welcome screen of the setup and when you click on it, it route you to download the media, this because of a lot of different reasons (Performance, Security and starting SQL 2017 multi-platform). This tool continues to be the most utilized tool to manage and interact with databases whether you are running your database on-premises or cloud. The good thing of this change is that if you are not installing a SQL engine in your desktop you don't need the entire media that included the setup files for the SQL engine, instead you just download the latest version and there you have it, also in the past you could only download the express version for free which was very limited or download the media containing the Development version for SQL, that is no longer the case, with the current release format you will have all the features supported. It has improved a lot from the versions we used to know, as now you can develop your own monitoring reports with SSRS and import those into SSMS and have that visibility from there, for example if you have a ad-hoc query to monitor your SQL Servers you can convert it as a report and integrate it with your SSMS and you will no longer need to open a new query window and run it, instead you just run your report and presto!

You can download the latest version from here: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017

Hope that this quick review help you get a better idea and start your desire to test different tools.

Thanks for reading!

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!