Some common reasons why the transaction log cannot be reused automatically

Managing SQL Server databases may sometimes be challenging especially when it comes to dealing with transaction log file internal space usage. More specifically, today I am going to talk about SQL Server wait types that may prevent SQL Server from automatically reusing transaction log internal space and therefore resulting in running out of space and affecting detrimentally on the availability of the database. For instance, if the recovery model of the database is either Full or Bulk-Logged then you may see a LOG_BACKUP wait type while the next backup log is waited to be run. Consequently, if those databases do not have Log Backups tasks, their transaction log files will be growing without control and when they take all the disk space available the databases will stop working until the internal space of the transaction logs is truncated by executing log backups manually.

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.

New Features in SQL Server 2022 for DBAs

SQL Server 2022 brings a wealth of new features that empower DBAs to enhance database performance, security, and usability. By leveraging these capabilities, organizations can optimise their data management strategies, ensuring better compliance, improved efficiency, and advanced analytics capabilities. The introduction of intelligent query processing, enhanced security features, and support for serverless architectures positions SQL Server 2022 as a robust choice for modern data solutions.

Here’s a summary of the key features and their benefits:

1. Intelligent Query Processing Enhancements

Overview: SQL Server 2022 expands on the Intelligent Query Processing (IQP) capabilities introduced in SQL Server 2019. New features include:

• Parameter Sensitive Plan Optimization: This feature allows SQL Server to create multiple execution plans for a single query based on the specific parameter values used during execution, leading to better performance.

Case Scenario: In a scenario where a stored procedure is called with highly variable input parameters (e.g., a sales report for different regions), the parameter-sensitive optimisation ensures that SQL Server selects the best execution plan tailored to the input, reducing execution time and resource consumption.

2. SQL Server Ledger

Overview: The SQL Server Ledger feature provides blockchain-like capabilities, ensuring data integrity and immutability through ledger tables. This feature uses cryptographic methods to verify data integrity, making it ideal for applications requiring audit trails.

Case Scenario: For financial institutions that need to maintain accurate records of transactions, SQL Server Ledger enables the creation of a ledger table where each transaction is logged. Any modifications are tracked, ensuring an immutable record that can be audited for compliance purposes.

3. Enhanced Security Features

Overview: SQL Server 2022 includes several security enhancements:

• Always Encrypted with Secure Enclaves: This allows more operations to be performed on encrypted data without exposing it in plaintext, enhancing security for sensitive information.
• Dynamic Data Masking Enhancements: Provides more flexible masking options, allowing DBAs to control how sensitive data is displayed to different users.

Case Scenario: In a healthcare application, patient data can be stored securely using Always Encrypted. The application can perform queries and calculations on encrypted data without revealing sensitive information to unauthorised users.

4. Query Store Enhancements

Overview: The Query Store feature has been enhanced to provide deeper insights into query performance over time. It now includes capabilities for identifying and managing query performance regressions.

Case Scenario: DBAs can utilise the enhanced Query Store to track performance changes after application deployments. If a new release introduces slow-running queries, the Query Store can help identify regressions, allowing quick remediation by reverting to previously optimised execution plans.

5. Built-in Machine Learning Services

Overview: SQL Server 2022 integrates built-in support for machine learning services, allowing DBAs to run R and Python scripts directly in the database engine. This feature enables data scientists and analysts to perform advanced analytics without moving data outside the SQL Server environment.

Case Scenario: A retail company can analyse customer purchasing patterns using machine learning algorithms stored in SQL Server. DBAs can schedule these analyses to run during off-peak hours, optimizing resource usage while delivering timely insights.

6. Serverless SQL Database

Overview: SQL Server 2022 introduces serverless capabilities for databases, allowing automatic scaling and cost-effective resource usage based on demand. This feature is ideal for workloads that experience variable usage patterns.

Case Scenario: A startup that experiences fluctuating traffic on its web application can benefit from serverless SQL databases, which automatically scale up during peak traffic and scale down during low usage periods, ensuring cost-efficiency.

7. Enhanced Availability Groups

Overview: New features for Always On Availability Groups improve failover performance and management. This includes automatic failover for databases in availability groups without the need for manual intervention.

Case Scenario: In a mission-critical application, the DBA can configure availability groups with automatic failover capabilities, ensuring minimal downtime during server maintenance or unexpected failures, thus enhancing application reliability.

8. Improved Performance Insights and Monitoring Tools

Overview: SQL Server 2022 provides enhanced monitoring tools with better insights into system performance. Features like the Database Health Monitor offer real-time health checks and performance tuning recommendations.

Case Scenario: DBAs can utilize the Database Health Monitor to receive alerts and recommendations for performance tuning based on workload analysis, enabling proactive management and optimization of database performance.

9. Support for JSON and XML Enhancements

Overview: SQL Server 2022 introduces improvements to support for JSON and XML data types, including better indexing and querying capabilities.

Case Scenario: A content management system that relies on JSON documents can leverage the new indexing features to improve query performance on JSON fields, allowing faster data retrieval and reporting.

That's all for now.

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.

Always On: Trying out Read-Only intent connections to secondary replicas

SQL Server Always On Availability Group enables us to connect Read-Only workloads to secondary replicas automatically by using differents ways. For instance, to connect .NET applications to secondary replicas, we must also include "ApplicationIntent=ReadOnly" besides the AG Listener Virtual Name in the connection string. More specifically, after configuring the Read-Only Routing list, this would be the connection string if we had an Availability Group with a Listener called "AGLISTENER" (in these examples it is used Windows Authentication).

Server=tcp:AGLISTENER,1433;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True

But that is not all, it is also possible to establish connection to secondary replicas via SQLCMD tool by using the new parameter -K (and optionally -M for faster detection of and connection to the active server). If -K is not specified, the sqlcmd utility will not support connectivity to a secondary replica. So, this would be a simple example of how to use SQLCMD with -K and -M parameters. I couldn't have been simpler.

Sqlcmd -S AGLISTENER  -E  -K ReadOnly -M

And last but not least, we can also do it by using SSMS Connection Dialog window by using “ApplicationIntent=ReadOnly” in the "Additional Connection Parameter" tab. I also wrote another post about it with more details, take a look at it.

To sum up, these are the basic ways to try out Read-Only intent connections to secondary replicas, the first way is ideal for developers whereas the second and third if you are working as a DBA.
That is all for now. Let me know any remarks you may have. Stay tuned.

How to create new Logins and Users for an AlwaysOn Availability Group

(This tip also applies for Log Shipping and Database Mirroring)

Surely, after setting up an AlwaysOn Availability Group (AG) we are going to need to create additional Logins and Users who must have access and the same permissions across the replica servers so that users can work on each server properly. At times it may end up being a confusing task as SQL Logins may not have matched to the correct SQL Users and therefore users will only access to the SQL instance but not databases in the Secondary Replicas. Do not forget that we cannot create Users directly in Secondary Replicas as they are only read-only, read intent or no access.

To begin with, we need to understand some basic concepts. Logins and Users in SQL Server are different things. Logins allow to access only to the SQL instance and perform tasks at that level such as create/alter/drop databases, alter configurations of the SQL instance, etc. whereas Users allow to get into the databases and work inside them doing delete/update/insert data, create/alter/drop tables, stored procedures, triggers, etc. So, traditionally we always need to have a Login and a User matched each other so as to have access to databases.(Since SQL Server 2012 it is possible to create SQL User without a SQL Login but this aspect is not covered in this post.)

In this context, when it comes to creating new Logins and Users not only will we need to create them in the Primary Replica, but also we have to make sure they also are created in each Secondary Replica so that Users and Applications can log on successfully with the same permissions as they have in the Primary Replica. So we might get some big issues if they are not matched correctly.

Obviously, we need to create the new Login at Primary Replica level and then give the permissions inside the databases. Consequently, ONLY the user will be replicated immediately to each Secondary Replica. So, because the Login is NOT a database object it will not be replicated (it is a server object), which means that we need to create the Login in each Secondary Replica server manually. If the Login is a Windows Login then all what we need to do is to create that Windows Login in each Secondary Replica. A Windows login only has a SID across the whole domain so it will be automatically matched to its Windows User with the same name in each database in the Secondary Replica server. We do not need to know its SID before creating it.

USE [master]
GO
CREATE LOGIN [DOMAIN\percy.reyes] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

Now when it comes to working with SQL Logins and SQL Users we must figure out and take the correct SID of the SQL Login in order to use it to create the same SQL Login in each Secondary Replica server. Therefore, we can query the SID of the SQL Login we need by executing the following code like this.

Having got it, we can use it to create the SQL Login by indicating the correct SID. (Do not forget to use the same password and other properties too.)

USE [master]
GO
CREATE LOGIN [preyes] WITH  PASSWORD=N'P4s$Word&1', SID= 0x8AA780458D0F9E4F95D4204B8E022C83, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

In conclusion, only after successfully following this technique will the SQL Login match automatically to the correct user inside the database. It also means that the user will be able to log on any Secondary Replica server with the right permissions inside the databases. That is all for now. Let me know any remarks you may have. Thanks for reading.

Creating alerts for monitoring proactively SQL Server AlwaysOn Availability Groups

When it comes to monitoring databases we have to do it proactively by using alerts implemented natively or via T-SQL code inside SQL Jobs running recurrently (I know we do have the AlwaysOn Dashboard, but personally I do not have time to check it all the time). As a result of this situation, so rapidly do we have databases working on production environment that we may need to have alerts working as well. In this post I am going to show how to have the most important AlwaysOn Availability Group alerts implemented. To begin with, I would basically suggest creating alerts only for these error messages.



Undoubtedly, we may need more alerts. Therefore, we will need to look into ‘sys.messages’ system object and look for error messages closely related to AlwaysOn Availability Group feature. Only after identifying them will we be able to create particular alerts taking the ‘message_id’ number.  To illustrate, I will query ‘sys.messages’ by using keywords like ‘Availability’, ‘suspended’, ‘failed’, ‘disconnected’, etc.

SELECT * FROM sys.messages  
WHERE (  [text] LIKE '%Availability%Disconnected%' OR   [text] LIKE '%Availability%not%synchroni%'
OR   [text] LIKE  '%Availability%suspended%'    OR   [text] LIKE '%Availability%failed%' OR   [text] LIKE '%Availability%chang%role%'
OR   [text] LIKE '%Availability%resumed%' )  AND language_id=1033

As you have seen, they are about 63 messages (depending on the SQL Server version you are working on). I do believe that these messages are the be-all and end-all so we might not need to implement additional alerts. Put differently, they are enough. Nevertheless, we might filter out some messages we do not consider important and include others to monitor AlwaysOn Failover Cluster instead.

SELECT * FROM sys.messages  
WHERE (  [text] LIKE '%Availabiliy%replica%' OR   [text] LIKE '%Database%replica%' OR   [text] LIKE '%primary%role%'
    OR   [text] LIKE '%secondry%role%' OR   [text] LIKE '%availability%group%' OR   [text] LIKE '%WSFC%'
    OR   [text] LIKE '%primary%replica%' OR   [text] LIKE '%secondary%replica%' OR   [text] LIKE '%alwayson%')
    AND    [text] NOT LIKE '%No user action is required.%'
    AND severity<>10
    AND language_id=1033

After having got the error number, the next step is to create the alert for the error by using ‘sp_add_alert’ system stored procedure which accepts basic parameters like the name for the alert and ‘message_id’ (taken from ‘sys.messages’). The rest of parameters will be the same for each alert. So, you can create the other alerts by replacing the name and ‘message_id’, for instance, the following example is based on the error number 41418. (We also create the notification to send an email to us when the alert is raised. Obviously, not until having successfully configured SQL Agent service to use Database Mail will you be able to receive the email.)

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'41418 HADR Alert', 
        @message_id=41418, 
        @severity=0, 
        @enabled=1, 
        @delay_between_responses=0, 
        @include_event_description_in=1, 
        @notification_message=N'Contact your Database Administrator urgently.', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'41418 HADR Alert', @operator_name=N'DBA', @notification_method = 1

To try out to see whether or not the alert is working, we will need to raise the error manually via RAISERROR command with ‘LOG’ parameter (to see the message logged in the SQL Server error log).

RAISERROR(41418,16,1) WITH LOG;

Having done that, we can see the following message in error log:

Likewise, you will get the email.

To sum up, I am convinced that these sorts of alert will always work for monitoring Availability Groups and others. You just need to spend some time on figuring out about the error messages and then go ahead and create the right alert for you. I hope you enjoy this post and let me know any remark you may have. Thanks for reading again.

How to move the files of database which has Replication, Mirroring, Log Shipping or AlwaysOn Settings

One of the challenging tasks in the life of a DBA is definitely moving all or some of the files of a database from one physical location to another one because of performance issues, maintenance requirements, disk space issues, etc. We usually move database files to another location by using Backup/Restore or Detach/Attach procedures. They are the most proper methods for most of the business cases but not for all. Let me expand on what I mean, for instance, those methods will not work with databases which have Replication, Mirroring, Log Shipping or AlwaysOn Settings because you will have to remove these settings before move them and then you should set up every setting again which could waste your time and have your database service stopped further than necessary. In this situation Backup/Restore or Detach/Attach simply is NOT an option because we need to make the database available as soon as possible. So, what we must do in order to move files of this type of database is by modifying the physical name of each database file we want to move. For instance, in the following code I will move 4 files (3 Data Files and 1 Log File):

ALTER DATABASE SalesDB MODIFY FILE (NAME=N'SalesDB_Data01', FILENAME= N'D:\SQLData\SalesDB\SalesDB_Data01.mdf')

ALTER DATABASE SalesDB MODIFY FILE (NAME=N'SalesDB_Data02', FILENAME= N'D:\SQLData\SalesDB\SalesDB_Data02.ndf')

ALTER DATABASE SalesDB MODIFY FILE (NAME=N'SalesDB_Data03', FILENAME= N'D:\SQLData\SalesDB\SalesDB_Data03.ndf')

ALTER DATABASE SalesDB MODIFY FILE (NAME=N'SalesDB_Log', FILENAME= N'E:\SQLLog\SalesDB\SalesDB_Log.ldf')

It is very important to verify that new database file folders already exist, if so, this should be the output results:

The file “SalesDB_Data01” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “SalesDB_Data02” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “SalesDB_Data03” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “SalesDB_Log” has been modified in the system catalog. The new path will be used the next time the database is started.

What’s next? We must stop the SQL Engine Service and then manually move every database file to the new location we indicated in the code above. Finally, we have to start the SQL Engine service which will load the files from the new location. With this method you do not need to remove any setting mentioned before. This is extremely effective and there is no doubt that it will work. Having these files moved to the new location, the database will start without any problem. If not, you should make sure that the SQL Service account has Full Control permission on database files from the new location.

I hope this tip helps you to save time and it will ensure that your database will be available quickly. I will be pleased to answer any question you may have. Thanks for reading!.

How to test Read-Only Intent Connection from SQL Management Studio

As part of some SQL Server AlwaysOn Availability implementations, we may need to test Read-Only Intent Connection in someway to make sure that it is working well at SQL Server level. We can verify it by using SQL Management Studio. Let me expand on what I mean, AlwaysOn Technology give us an option to implement Read-Only Intent mode to enable SQL Server to redirect read-only connections to secondary replicas, it means off-loading Read-Only workloads to secondary replicas. Undoubtedly, this is a gripping feature, at first glance, it drew my attention to test it and I verified that is truly useful for business cases where we need to have Read-Only Intent feature working properly and automatically. It couldn’t have been better when I learned that I could use SQL Management Studio to achieve my purpose.
Keep it in mind that we need to have set up Read-Only Routing List before going to test it, obviously. Now carrying on this tip, let me show you some explicit pictures where you will see which parameters you should consider. First of all,  you need to go on 'Login' tab and write the Listener Name of your AlwaysOn Availability Group. In this example, my Listener Name is SRV1LIDBVB which represents the Virtual Server Name.



What’s next? going to 'Additional Connection Parameters' tab, you will see two parameters. You must write the database name for 'Database' which is in your AlwaysOn Availability Group and 'ReadOnly' for 'ApplicationIntent'. The both parameters are separated by a command.



Finally, click on 'Connect' and you will be connected to any secondary replica, which one replica? it depends on what you have set up in your Read-Only Routing List. This is all for now. I hope you find this post practical, effective and easy to put in practice and include it in your testing plan. Let me know any remark you may have. Thanks for reading!.

AlwaysOn AG Listener: The attempt to create the network name and IP address for the listener failed

While working on a heap of High Availability and Disaster Recovery solutions, I was challenged to deal with some complex errors that I had to overcome as fast as possible. Today’s post is going to show how we may solve one of them. I am speaking about the following error which is raised when we have to set up the AlwaysOn AG Listener:

The Windows Server Failover Clustering (WSFC) resource control API returned error code 5057.  The WSFC service may not be running or may not be accessible in its current state, or the specified arguments are invalid. 
The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator. (Microsoft SQL Server, Error: 41009)

At times it can be quite easy to fix it, but it may become complicated as we do not have more details of the root cause, therefore, it does not give us any clue. In my experience working on this, the most common cause has to be about lacking of permission for the cluster name account so we have to make sure that this account has the 'Create Computer' and 'Read' permissions:



Once you have given right permissions and if the error is still there then you must check whether the IP Address is available to be assigned to the AlwaysOn AG Listener. It is simple to verify by making ping to IP Address which should be free. If not, ask your Administrator a new IP Address and try again.
I hope this practical post helps you. Let me know any remarks you may have. Until next post, thanks for reading!

Error: “The local node is not able to communicate with the WSFC cluster” – AlwaysOn Availabiliy Group

At times, after having an AlwaysOn solution implemented, not only may the WSFC service run down, but also it may be corrupted for whatever reason. As a result of this, we may need to reinstall everything. The big question would be now how to uninstall and delete the Availability Group correctly if WFC service is not running. In this case, there is no other option than deleting forcibly the Availability Groups and resulting in a dirty and incomplete uninstallation.
After deleting the Availability Group, we will need to remove the nodes from the WSFC Cluster by uninstalling WSFC Service (instead of destroying the Windows Cluster which cannot be possible in this case). Having accomplished that, we have to create the Windows Cluster again and then add each node. Finally, we are going to be able to set up the AlwaysOn Availability Group. Ideally, it is the normal procedure for it.


Nevertheless, we cannot play by the procedure as each case is particular (and this case really is). It might not be a great surprise if we get this error when setting up the AlwaysOn Availability Group:



Obviously, this error is due to an incorrect uninstallation. Luckily, this can be fixed by disabling the AlwaysOn Availability Groups feature at a database engine service level.



We now have to enable it again and then restart the database engine service.



Not until you have done these all steps will you be able to create the Availability Groups again. Thanks for reading again!