Tuesday, 20 March 2018

Configuring Read-Only Routing and load-balancing across Read-Only replicas

With the arrival of AlwaysOn Availability Group in SQL Server 2012, implementing HA+DR solutions have been an easier and not expensive task in comparison to legacy architectures such as Database Mirroring for HA and Log Shipping for DR, and FCI for HA and Database Mirroring for DR. Nevertheless, at the beginning not everyone has been fully aware of all the power of this technology so that some might not have made the most out of it. Naturally, this technology has been improved over the years, for instance, load-balancing across readable secondary replicas was added, and today in this post, I am coming with a script to configure it.

To begin with, bear in mind that client connectivity to availability group databases is established through its Listener which is a Client Access Point Clustered Resource. This Listener consists of a DNS name, one or more IP address and a Port, and it makes possible that standard connections are routed to the primary replica for Read-Write operations, whereas Read-Only connections are routed to the secondary replicas (via the Read-Only Routing list). More specifically, working with a Listener has the following advantages: it connects to the primary replica (Read-Write), routes Read-Only intent connections to secondary replicas (Read-Only), and acts as a multiple subnet Listener for stretch cluster, multi-site, remote site. However, the client connection behaviour is determined by Availability Group Replica options. These options determine whether a replica is enabled for Read-Only access when in a secondary role and which clients can connect to it, for instance, we can configure secondary replicas to accept no connections at all, only Read-Only connections, or all connections.

Talking a bit more about Read-Only Routing list used by the AG Listener, Read-Only connections are routed to a readable secondary based on Read-Only Routing list which enables automatic direction of client connection to new readable secondary, and even more, it is possible for connections to go to different readable secondaries if available to balance Read-Only access (since SQL2016). In the following example, we have an Availability Group MSSQLAG1with four replicas: NODE1, NODE2, NODE3, NODE4 (inside the DBA300 domain). So, to allow applications to be routed to readable secondary replicas we must configure the Read-Only Routing list. Firtly, starting with the configuration of the Read-Only Routing URL for each replica, in other words, set the URL for each replica. This setting is only used when the local replica is acting as secondary.

ALTER AVAILABILITY GROUP MSSQLAG1
MODIFY REPLICA ON 'NODE1'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = 'TCP://NODE1.DBA300.COM:1433'))
GO
ALTER AVAILABILITY GROUP MSSQLAG1
MODIFY REPLICA ON 'NODE2'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL='TCP://NODE2.DBA300.COM:1433'))
GO
ALTER AVAILABILITY GROUP MSSQLAG1
MODIFY REPLICA ON 'NODE3'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL='TCP://NODE3.DBA300.COM:1433'))
GO
ALTER AVAILABILITY GROUP MSSQLAG1
MODIFY REPLICA ON 'NODE4'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL='TCP://NODE4.DBA300.COM:1433'))
Now we must include each replica in the Read-Only Routing List. This setting for each replica is used when the replica is acting as primary. In this case,  Read-Only Routing list will always direct traffic to the first available replica in the routing list. For instance, taking the first part of the code, we are saying that when NODE1 is a primary replica then all Read-Only intent connections must be directed to NODE2, but if NODE2 is not available then redirect them to NODE3, otherwise to NODE4.

ALTER AVAILABILITY GROUP MSSQLAG1
MODIFY REPLICA ON 'NODE1'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('NODE2', 'NODE3', 'NODE4')))
GO
ALTER AVAILABILITY GROUP MSSQLAG1
MODIFY REPLICA ON 'NODE2'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('NODE1', 'NODE3', 'NODE4')))
GO
ALTER AVAILABILITY GROUP MSSQLAG1
MODIFY REPLICA ON 'NODE3'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('NODE1', 'NODE2', 'NODE4')))
GO
ALTER AVAILABILITY GROUP MSSQLAG1
MODIFY REPLICA ON 'NODE4'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('NODE1', 'NODE2', 'NODE3')))
But if you want to have load-balancing across read-only replicas, you must execute the following code this instead of the previous one. For instance, when NODE1 is running under the primary role, all Read-Only intent connections must be directed to NODE2 and NODE3, but if these replicas are not available then these connections should be directed to NODE4. 

ALTER AVAILABILITY GROUP MSSQLAG1
MODIFY REPLICA ON 'NODE1'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('NODE2', 'NODE3'), 'NODE4')))
GO
ALTER AVAILABILITY GROUP MSSQLAG1
MODIFY REPLICA ON 'NODE2'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('NODE1', 'NODE3'), 'NODE4')))
GO
ALTER AVAILABILITY GROUP MSSQLAG1
MODIFY REPLICA ON 'NODE3'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('NODE1', 'NODE2'), 'NODE4')))
GO
ALTER AVAILABILITY GROUP MSSQLAG1
MODIFY REPLICA ON 'NODE4'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('NODE1', 'NODE2'), 'NODE3')))
Maybe you are wondering how we can configure an application to connect to secondary replicas to only read data. Well, after configuring the Read-Only Routing list, we must specifies the AG Listener Virtual Name plus "ApplicationIntent=ReadOnly" in the connection string. Furthermore, we can also try it out via SSMS by including the additional parameter “ApplicationIntent=ReadOnly” in the "Additional Connection Parameter" tab box while logging in (click on "options" button to see that box), for more info you can check my previous post about trying out Read-Only intent connections to secondary replicas. That is all for now. I hope you find this post helpful. Let me know any remarks you may have. Stay tuned.

Friday, 2 March 2018

Installing a stand-alone SQL Server 2017 instance step by step

Today's post is going to outline a whole procedure to install a basic stand-alone SQL Server 2017 instance. This procedure pretends just to be a basic guideline and not a rule for each installation because I fully understand that every environment is different and needs a customised installation to meet very specific requeriments. It is well worth noting that SQL Server 2017 must be installed on Windows Server 2016 when it comes to production environments. Having said that, let's click on the setup.exe application to start with the installation of SQL Server.
The following page will be opened. This is the section where you can find useful documentation about Hardware and Software requeriments, security, upgrade, and also a link to install SQL documentation. What's more, some very important tools are also available like Migration Assistant (SSMA) to migrate from other technologies such Oracle and MySQL. So, it is worth checking thoroughly (when you have more time).
In that page we must choose "Installation" and then click on "New SQL Server stand-alone installation or add features to an existing installation" to install just one stand-alone SQL instance (non-clustered). From this page, it is also possible to install other tools like SSMS, SSDT, and services such as Reporting Services and Machine Learning service. Furthermore, if you are installing a SQL Server clustered instance, from here you must install the rest of nodes that will be part of the cluster ("Add node to a SQL Server failover cluster").
We will have to enter the product key. In this example, I am using a product key for a Standard Edition, you need to use the product key of the edition you are want to install.
After entering a valid product key, we must accept the licence terms and move on by clicking the "Next" button.
In the next page we will find the option to update SQL Server as part of this installation process. I suggest moving on without updating it for now, especially if there is no high-quality internet connection locally. Preferably, you should apply Service Packs after finishing this process. Honestly, no database server should have internet connection due to security reasons.
So after clicking on "Next", we will now see the validation process of rules. We must meet the rules before moving on, for example, the server in question should not be a computer domain controller. Furthermore, in this case, we will mostly see that "Windows Firewall" rule will result in "warning" because of Windows Firewall is enable, don't panic, this just means that we have to configure it properly after finishing the installation so that applications can connect to the database server. To just give you another example, we may need to create a rule to allow connections via the port number for the SQL instance, in this the port will be 1433. so, things like this need to be done as part of the configuration to enable SQL instance to be used by applications. For now, just move on. Next...
It is time to select the services and other features we want to install. In this example, we will only install the database engine, so just choose "Database Engine Services" and move on. It is highly recommendable installing only the services that are really neccessary (do not install more than you need), and also SQL Server binaries should be placed on the partition disk "C".
Because this is the first SQL instance is being installed on the server, this should be a Default instance (without a name, and listening to the default port 1433), so do not change "MSSQLSERVER". Move on.
Now we have to configure many things related to Service Account and Collation settings. Firstly, in this window, you can set the Windows Account that will be used by each service such as SQL Agent, SQL Browser, and others. Make sure that the startup type is "Automatic". Additionally, it is also possible to set in advance the windows policy "Grant Perform Volume Maintenance" to SQL Server Database Engine service (This privilege allows SQL Server to enable Instant File Initialization and run data & log growth, alter/create database, and backup/restore operations much faster).
Another important setting to set is the "Collation" for the whole SQL instance. More specifically, this collation is for the "model" database and the other system databases. By default, all new user databases will  inherit the collation of the "model" database. So, bearing in mind the importance of this setting because after installing it you cannot modify it without reinstalling again. Click on "Customize" to choose the proper collation for your environment.

It is worth noting that "SQL_Latin1_General_CP1_CI_AS" is a very common SQL collation and mostly used in Spanish and English versions of applications for backward compability reasons. However, If you are installing a SQL instance for a new system then it is highly advisable using a Windows Collation like "Latin1_General_CP1_CI_AS". More info about Collations you can check some of my posts about it.

Security is of paramount importance in SQL Server configuration. So, I recommend using Windows Authentication mode as much as possible. Nevetheless, if you had to create SQL users over time, you can enable "Mixed Mode" via SSMS. In this part, we must also create an Administrator user for the SQL instance. Just click on "Add Current User" to create that Windows login as Administrator.
Now, it is a good opportunity to set the default directory for Data, Log, Backup files of user and system databases. We must just modify the directories for user databases.
The next step is configure the TempDB database with respect to Initial Size, Autogrowth, directory for data & log files, and the number of data files. Here I show off some possible values but you can change them after the installation finishes. Check this previous post about how to configure TempDB data & files by using T-SQL. If you are more interested in having a good understanding of this database, you can check my post about Looking into some concepts about the well-known tempdb database.
Secondly, we change the directory for TempDB data & log files.
Thirdly and finally, keep in mind that TempDB data & log files should be located on a dedicated partition disk (different from the one used for user databases) and under RAID 10 or RAID 1.
Having followed all the previous steps, we have come to the last window before starting the installation. This window just shows a summary of what is going to be installed. We must ensure that everything is ok and then proceed to install.
After clicking on "Install", the process of installation will start and at this point we just have to wait.
If everything runs as expected, we will see the following end results.
That is all for the time being. Thanks for reading. Let me know any remarks you may have. Stay tuned.