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

Undoubtedly, many of us have the task of installing a new stand-alone SQL Server instance which includes the database engine service only. For instance, it can primarily be needed for dedicated and consolidated OLTP environments. Consequently, we can be asked to create a formal document for others so that they can easily follow it for future installations and standard configurations.

Today's post is going to outline the process to install a basic stand-alone SQL Server 2017 instance. This process is just a basic guideline and, surely, not a rule for each installation, because it is fully understood that every environment is different and needs a customised installation to meet very specific requirements. You can read the whole tip about it at mssqltips here https://www.mssqltips.com/sqlservertip/5616/steps-to-install-a-standalone-sql-server-2017-instance.

I hope you find it very useful and practical. That's all for now. Please let me know any remarks you may have. Stay tuned!

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 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 specify 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.

Looking deeper into the physical & logical architecture - Transaction Log File

Beyond all doubt, it is essential to have a good understanding of the Transaction Log (T-Log) so that we can diagnose unforeseen performance issues related to it and I am sure that almost everyone had at least one. The T-Log is basically a record of all transactions happening to the database. All these transactions are actually first written to the physical T-Log file, and then after a CHECKPOINT, is written to the Data File via the Lazy Writer process. Some of the uses of T-Log are: as a point in time recovery (full recovery model), to record the Start and End of each transaction, every data modification (insert, update, delete) including system SP's, DDL statements to any table including system tables, every extent and page allocation and de-allocation operation, and creation or drop of tables and indexes.

More specifically, SQL Server uses a Write-Ahead Log Algorithm whereby the Log Buffer Manager always guarantees to write the change descriptions (log records) to the T-Log on disk before it writes the changed data pages to the physical Data Files. Furthermore, Write Ahead Logging also allows SQL Server to ensure some of the ACID properties of database transactions, most significantly durability. It is of paramount importance to keep in mind that there is no performance benefit from having multiple physical log files for the T-Log because it is written sequentially and in a round-robin fashion. Sometimes, we can have many T-Log files for a database as long as there is no enough space on the partition disk, so we can add one more T-Log file on another partition disk so that SQL Server does not stop working until the final solution is provided.

Instead of having various T-Log files, it is highly advisable to work on preventive actions to optimise the T-Log file usage in terms of performance and availability, for instance, make sure only one Log File is used, use a dedicated drive/array for the T-Log file (use RAID 10 for log drives, if possible. RAID 1 is also acceptable), avoid Log Fragmentation (avoid frequent small auto-grow events, set 2GB, 4GB or 8GB auto-grows for example), Fragmented Log File can slow down the performance of operations that read the T-Log file such as Backup Tasks, Crash Recovery Process, Database start up, Transactional Replication, Database Mirroring, creation of a Database Snapshot, DBBC CHECKDB, DBCC LOGINFO, Change Data Capture, etc.

So, how can we control the size of the T-Log File? it depends on the database recovery model, in other words, with SIMPLE recovery model: SQL Server auto-truncates and reuse the space after committing the transaction whereas with either FULL or BULK_LOGGED recovery model, a Log Backup is the only action that can truncate the T-Log, nevertheless, you might have seen that after taking Log Backup, the Log space was not truncated yet, this is because some internal T-Log records may still be required by some other database operations like Open transaction, AlwaysOn AG, Replication, CDC, Mirroring, Backup, etc. It is well worth noting that we must try to keep VLF's as less as possible and each VLF of an acceptable size because the more you have the worse the performance will be. I recommend having VLFs of no more of 512MB per one, but it depends entirely on the use of the T-Log file and its growth settings.

Internally, SQL Server divides a Transaction Log file into a number of sections called Virtual Log Files (VLFs). By default, a T-Log will have 4 VLF's when created. After that, the next VLF's will get individually created with each auto grow or manual grow. Here is the base on what SQL Server starts creating the next VLF's:  < 64MB = 4VLF's //  >= 64MB < 1GB = 8 VLF's // > 1GB = 16VLF's. In SQL2014, 8MB <= 1VLF.

Finally, if we need to figure out how many VLF's are, we can use the command DBCC LOGINFO which is an undocumented command but is safe to run because it just reads the T-Log, and the most important columns to look for are the following: FileSize (VLF size in bytes), Status (0 Not in use, 2 In use), CreateLSN - it can be used to determine how many VLF’s were created in a log file growth operation, that may be 4, 8 or 16, and this also indicates the Log Sequence Number.
That is all for now, taking this insight into consideration will stand you in good stead for when you have to fix some internal T-Log issues. Thanks for reading. Let me know any remarks you may have. Stay tuned.

Checking SQL Server stale statistics

Clearly, it is of paramount importance to monitor the state of statistics because they always play a significant role on the performance of the whole database. When statistics are not up to date the performance will indeed decrease tremendously over time and I am sure nobody wants to have a sluggish system. How many times did you find yourself in a big problem related to unforeseen performance issues even some days after having carried out dutifully the respective database maintenance tasks? did you wonder why rebuilding index and statistics update tasks are not being good enough? if everything was working smoothly, why did the performance start going down unexpectedly? Why are the indexes not being used as usual? do I need to create more indexes? the answer might be found by digging deeper into the stale statistics. It is worth noting that Statistics contain all the information that SQL Optimizer needs to generate the best possible execution plans for your queries. If they are not up to date then the impact might be disastrous. So, having the right indexes are not good enough when the statistics went stale.

Generally, it is advisable to update statistics if it is verified that at least 20 per cent of the data changed (as long as they have more than five hundred rows). But can we know how many rows changed for a table so that we can just update its Index Statistics? In SQL Server almost everything can be checked and this is not an exception. Today I an coming with a script to check which Index Statistics have gone stale so that we can focus our work on them and keep expected performance going as well. The script is built on the base of STATS_DATE function which retuns the date of the most recent update for statistics on a table or indexed view.

SELECT DB_NAME() AS DatabaseName,

SCHEMA_NAME(t.[schema_id]) AS SchemaName,

t.name AS TableName,

ix.name AS IndexName,

STATS_DATE(ix.id,ix.indid) AS 'StatsLastUpdate', -- using STATS_DATE function

ix.rowcnt AS 'RowCount',

ix.rowmodctr AS '#RowsChanged',

CAST((CAST(ix.rowmodctr AS DECIMAL(20,8))/CAST(ix.rowcnt AS DECIMAL(20,2)) * 100.0) AS DECIMAL(20,2)) AS '%RowsChanged'

FROM sys.sysindexes ix

INNER JOIN sys.tables t ON t.[object_id] = ix.[id]

WHERE ix.id > 100 -- excluding system object statistics

AND ix.indid > 0 -- excluding heaps or tables that do not any indexes

AND ix.rowcnt >= 500 -- only indexes with more than 500 rows

ORDER BY  [%RowsChanged] DESC

Bearing in mind there are two types of statistics: Column Statistics and Index Statistics. The first type are statistics linked to columns and created automatically as result of querying the columns, so SQL Server normally creates them unless you have AUTO_CREATE_STATISTICS database option disable, whereas the second one are statistics linked to indexes and these statistics are created at the same time when the indexes are. Likewise, Index Statistics are always updated with FULLSCAN while indexes are being rebuilt. So, there is no need to update Index Statistics explicitly after rebuilding the indexes, if so, it might be harmful because UPDATE STATISTICS command without pamareters is based on default sampling and it is not the best option for the vast majority of cases.
Talking of a little more of Column Statistics, after rebuilding indexes we just have to update Column Statistics and for this case it might be good enough to use default sampling, however, it is also advisable to use FULLSCAN if your environment is worthy of it.
Many of us may be wondering how many times the statistics need to be updated per month? the answer depends entirely on how frequently your data is being changed (updated, inserted, deleted). Updating statistics once a week might be enough for highly transactional environments whereas for others once a month would be more than good. That is all for now, I hope you find the script helpful. Le me know any remarks you may have. Stay tuned.

Avoid changing default ANSI database options

Not having another way of fixing some specific errors, at times some people may consider turning off any ANSI database options as a final solution. To be perfectly honest, I do recommend getting to the bottom of each problem and then fixing it at that level instead of changing default ANSI settings (unless it is just a quick fix or is truly necessary because you verified the benefits are significant). For instance, it is by no means uncommon to turn ANSI_WARRINGS off to fix the following error:

Msg 8152, Level 16, State 14, Line 5
String or binary data would be truncated.

Surprisingly, it may be misunderstood that there is no more to do. First of all, this error occurs because the new data to be inserted or updated cannnot be placed into the column due to the data size is beyond the column size. This situation may only be a consequence of a poor database design, so should it be altered the size of the column? if so, are there indexes for this column? what happens with the statistics linked to the column? many things to take into consideration if we wanted to alter the column to give a final solution to this issue, and obviously, that is why the impact on the performance is of paramount importance. By and large, this is not highly advisable turning off ANSI options as it may cause RECOMPILATION for each stored procedure where it was turned off, therefore, it may impact on the performance detrimentally and the knock-on effect might be regrettable. So, we should avoid turning off not only ANSI_WARNINGS but also the following ones which will cause the same effect.

• SET ANSI_DEFAULTS
• SET ANSI_NULLS
• SET ANSI_PADDING
• SET CONCAT_NULL_YIELDS_NULL

Furthermore, recompilation may sometimes be used as a way to 'optimise' specific stored procedures or Ad-Hoc queries because of parameter sniffing issues or bad-written code, but this should be done explicitly and concientiously by using WITH RECOMPILE option,  RECOMPILE query hint, sp_recompile system stored procedure or another optimisation strategy. That is all for now, let me know any remarks you may have. Thanks for reading. Stay tuned.