SQL Server 2008 end of support

2018 is about to end. In the case you didn't know or haven't heard, the extended support for SQL Server 2008 (and R2) reaches to an end in 2019.

This beloved version (lets be honest, is like windows XP for the database world) was launched on 8/6/2008, so that means that is a 10 years old technology, with the same time as the backberry and much other devices that almost no one uses this days, which were great, but trought be told, compared with Today's gadgets they are way behind although it marked a milestone to what we have now you would preffer something better for you, isn't it?

So what does the end of support means?

Basically it means, that Microsoft wouldn't offer any type of support for that version of SQL Server, which can be translated to:

• No new Service packs will be released
• No new cumulative updates will be developed

And what does that means to me? Why should I care?

If I depend of any type of regulation or my environment holds any type of certification (PCI-DSS, HIPPA, etc.) it means that I would be flagged as "non-compliant" because it requires that any software that I use is still with support of the company that provide it.

It means, that I won't be getting updates anymore, if any new vulnerability is found for this product, it is most likely that I won't be getting a patch to remediate it. Also, all the repositories will eventually be removed, so even if the package I need was developed and for some reason I didn't applied, and then I look for it, it is most likely that I won't be able to find it, like is now the case for SQL 2005.

So what options do I have?

1. Extended support: You can pay for extended support, this applies only if you have Software Assurance or Enterprise agreement and is only if you need to raise a ticket with Microsoft asking for support but not to resolve bug fixes and this extended support is Expensive, really really expensive.
2. Azure: Microsoft can offers the extended support for up to 3 years, if you move your SQL Server as a VM in Azure, with that you can still get support without paying the extended support fee like if you stay on-premises.

3. Upgrade: The best option is that you upgrade your environment, if you really need to stay on-premises, you can plan a migration in your own data center, however you can also plan to upgrade and take the new benefits of Azure in the different flavors it has to offer (Azure SQL, Azure Managed instance or VM with SQL Server)

Does it really worths the effort?

Absolutelly, the newer versions not only run faster but it also offers more features to secure your data, it also integrates monitoring tools and auto-tunning plus now you can run it in all different platforms not only with Windows.

So if you don't have your migration planned already, what are you waiting?

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!

Script to review replications

This script is from my library and I use it to review what is going on with my transactional replications, try to remove statements that didn't get purged for whatever reason. This is my last resource before getting the need to reinitialize or drop and recreate my publications.

use distribution
go

/*Query to get your articleID*/
select top 100 * from Distribution.dbo.MSarticles


/*Use this when you have the xac_seqno that is failing or your last that was replicated*/
select * from MSrepl_commands Where xact_seqno >= 0x0005AEF70000004A000900000000

/*use this to review your errors*/
select
*
From MSrepl_errors

/*use this to list the commands to be replicated*/
sp_browsereplcmds '0x0005AEF70000004A000900000000', '0x0005AEF70000004A000900000000'

/*Once that you have the commands to be deleted, add them inside the IN*/
DELETE from MSrepl_commands where command_id=1 and xact_seqno in (0x0005ADE2000001E00009)
and [command] like '%MSins%'

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.

I've been chosen to speak at SQL Saturday Guatemala

I'm thrilled to announce that one of my proposals has been chosen to be presented in Guatemala's SQL Saturday that will be held on February 3rd at the Francisco Marroquin University (UFM).

If you will be in town and are an enthusiast of SQL Server, you should definitely plan to attend, did I mention is Free? 

You can check the agenda and register here there are a lot of great speakers, some of them are Data Platform MVP's.

The session I'll be presenting is:

"Implementing Row Level Security (RLS)"

I'll be presenting what it is, what you need and how to implement it, as well as some scenarios where you can benefit with it.

So if you haven't register, what are you waiting?

See you there!

Configure Always on AG with SQL 2017

One of the new features that we have with SQL 2017 is that you no longer need a Windows cluster to enable the AlwaysOn feature with SQL server (remember we discuss the requirements to configure that here those are valid for versions 2012 to 2016) , if you want to review what's new in SQL 2017 you can check it here.

In this post I'll be showing you how to configure an Always On availability group with SQL Server 2017, most of the steps are the same for the older versions and I'll be telling the differences on each of the steps so lets get started.
First I'll amuse you already have SQL Server installed in two servers as stand alone instances, that is a requirement for this post.

1. Enabling AlwaysOn: In order to do that, you need to go to SQL Server Configuration Manager, and on the SQL Server Services, hit properties over the MSSQLSERVER

2.   In the properties window, look for "AlwaysOn High Availability", if you are running with an older version, the checkbox "Enable AlwaysOn Availability Groups" won't be availabile until you make the machine where SQL Server part of a cluster but just part of the windows cluster, the SQL Server instance remains as an Stand Alone type. Once that you have the checkbox enabled, check it, and click apply and Ok. Enabling this feature requires a Service restart so take that in account. Also, you need to do this in all the SQL instances that will be part of the Availability Group.

3. Go to SQL Management studio, connect to any of the SQL instances that will be part of the group, look for the "Always On High Availability" folder, expand it and you will see a folder with the name "Availability Groups" do a right click on it and select "New Availability Group Wizard…" 

4. In the availability group wizard, configure the name you want for your availability group.

a. Cluster Type (New with SQL 2017): you have 2 options here (the official documentation shows 3),          External or None, if you choose External, it means that you will joining this availability group to an external cluster, E.g. if you have a windows cluster on your primary datacenter that is on a different network or you want to add it to a Linux cluster.
b. Database health level detection (New with SQL 2017), this will enable a constant validation of the databases that are part of the availability group that if anything goes wrong with any of the databases it will trigger an automatic failover
c. Per Database DTC support (New with 2016 SP1) Allows the Distributed Transaction Coordinator through the availability group, feature that was not available in older versions of SQL (2012 and 2014)

If you don't have a cluster created and just like this scenario, choose NONE



5. Select the databases that will be part of the availability group. Requisites for a database to be considered:
     a. Be on Full recovery model
     b. Have a full backup
 

6. In here you will be choosing the replicas that you want to add to your group. Important features here:

1. Initial Role: You will set the role that each replica will have once that the group becomes available
2. Failover Mode: Manual or Automatic
3. Availability Mode: Synchronous commit or Asynchronous commit
4. Readable Secondary: No, Yes - Read Intent Only and Yes. If you choose No or Yes - Read Intent Only, you won't be available to query your replicas, the Read Intent is when you enable that feature that routes the read operations to your available replicas. With the Yes option you will be able to query the databases in your replica.
5. The endpoints tab will show you the URL and ports each replica is set to.
6. Backup preferences tab: in here you can configure if you want to take the backups from the primary or the secondary servers.
7. Listener tab: In this window it lets you set the parameters to configure the availability group listener, however my advice is to do it later, configure your group first and once that is done, configure it later.
8. Read-Only Routing: tab This lets you configure your read only routing for the read-intent setup, this allows you to load balance the queries so you have only the queries that will Insert, Delete or Update on the primary and all the Select queries routed to your secondary's so they don't consume resources the Primary will use (I'll explain it in another post) 

7. 

1. Automatic seeding (Starting SQL 2016): With this type, SQL Server will do everything for you, it will use the default folders configured so make sure you have enough space available on it.
2. Full database and log backup: Same as the Automatic seeding, however in this case it lets you choose where do you want to generate the backups
3. Join Only: With this one, you do prepare everything in your replica, important notice, you do a restore with norecovery in your replica and apply at least one log backup. This option is useful with really large databases.
4. Skip initial data synchronization: Same as before, however in this particular one, you need to restore everything just at the time before initializing the synchronization otherwise it will tell you that there are items pending to be restored.
5. Failover Mode: Manual or Automatic
6. Availability Mode: Synchronous commit or Asynchronous commit
7. Readable Secondary: No, Yes - Read Intent Only and Yes. If you choose No or Yes - Read Intent Only, you won't be available to query your replicas, the Read Intent is when you enable that feature that routes the read operations to your available replicas. With the Yes option you will be able to query the databases in your replica.
8. The endpoints tab will show you the URL and ports each replica is set to.
9. Backup preferences tab: in here you can configure if you want to take the backups from the primary or the secondary servers.
10. Listener tab: In this window it lets you set the parameters to configure the availability group listener, however my advice is to do it later, configure your group first and once that is done, configure it later.
11. Read-Only Routing: tab This lets you configure your read only routing for the read-intent setup, this allows you to load balance the queries so you have only the queries that will Insert, Delete or Update on the primary and all the Select queries routed to your secondary's so they don't consume resources the Primary will use (I'll explain it in another post) 

8. Verify that the validation runs successfully, this warning is because I didn't setup the listener in step 6, but that is fine, every time that I've tried to configure it from the very first page it fails, so my advice is that you configure it later (part of this post).

9.  After hitting finish and if none of the steps have failed you will see the screen just like this.

Setup of the listener

1. The listener will be your single point of entry for your availability group, no matter which server is primary and which secondary, you will only need to configure your applications to use the Listener fqn or the ip and that will give you that High Availability you are looking with Always On.
   1. Go to the "Always On High Availability" folder, expand your availability group and look for the "Availability group listener" and right click on it.
   
   
   2. In the Configuration screen, Configure the domain name you want your listener to respond onto, this will be like another computer in the domain, so be sure that you have permissions to create objects in the active directory, if you don't ask your domain admin to provision that name and grant you permissions over it so you can enable it.
   1. Configure the port you want the listener to listen to, this needs to be different than the endpoints and than the usual 1433 that the SQL instance will be listen to.
   2. Configure the IP: make sure that its an available IP in the domain, also make sure that you choose the ip from the same subnet that one of your replicas is running.
    
   
   
   
   You are all set, you can start configuring your applications to the listener and taking the advantages always On provides.