When you are working on the AlwaysOn feature, it is crucial to understand how the AlwaysOn commit process works. This blog will walk you through how a synchronous and Asynchronous commit process works in AlwaysOn. Data Synchronization in Synchronous mode Primary replica is generating transaction log blocks. The secondary replica establishes a valid connection to […]
Fastest Way to Find Row Count of All Tables From All Databases in SQL Server
Read more
There are many times you as DBA or Developer needs to get all row count from all tables or row count of all tables from all Databases in a SQL Server. You may need a quick way to count the number of rows contained within the tables. The following SQL code will get you the row […]
Snapshot Agent Acquires schema modification (SCH-M) lock on all the articles of a publication
Read more
As the business requirement, It is very common to see a request for adding a new article or subscription to the existing publication. After adding the new article or subscription to the existing publication, we use snapshot agent to initialize it so that data can flow from Publisher to Distributor to Subscriber(s). But, the snapshot […]
SQL Server Enterprise Edition-Core VS Server+CAL – Demo on differences
Read more
In this blog, I am NOT going to discuss the SQL Server licensing model. If you are looking for that information, I would strongly suggest you talk to your Microsoft Technical Account Manager (TAM) or follow the SQL Server 2016 licensing Datasheet. Here, I am going to discuss – Suppose, your company got a big […]
How to Change SQL Server License or Product Key?
Read more
Yesterday, I was doing SQL Server Database stress and benchmarking testing on the client side. When I started ramping-up more and more workload on the server, I found the SQL Server was using only 20 CPUs. When I checked the box to know how many CPU the box got, it surprised me because it got […]
Have you ever wonder why Log Send Rate doesn’t show against Primary Replica on the AlwaysOn Dashboard?
Read more
Recently, I was working on the AlwaysOn Availability group issue where the log records were not moving from primary to secondary replica because of the wait type “PARALLEL_REDO_TRAN_TURN”. During the troubleshooting, when I opened the AlwaysOn Dashboard to see the Log Send Rate, I found that the Log Send Rate was coming blank against the […]
Failed to join local availability replica to Availability Group – SQL Server error 41106 and 41158
Read more
When you configure SQL Server AlwaysOn Availability Group from management studio, it may fail with below error while joining secondary replica to the availability group. Today, While I was preparing some demo on AlwaysOn for the community event, I encountered the same issue. So, I thought to blog the solution here so that it can […]
Disable or Turn-off SQL Server Telemetry Service
Read more
With SQL Server 2016, Microsoft is installing SQL Server Telemetry or CEIP (Customer Experience Improvement Program) Services by default. It sends feature usage info back to Microsoft. You cannot skip the installation of these services. I find most of the DBAs even don’t aware that they have installed the service on their host. In case, […]
Adding File to Database in AlwaysOn Availability Group
Read more
When a database is part of AlwaysOn Availability Group, it means whatever commands we are executing on Primary replica database, those commons will also be sent to all the Secondary replicas database via log records. So, If you are adding a database file – data or log to the Primary Replica, the add file command will […]
Save the Connection String Parameters in SSMS – AlwaysOn
Read more
In the SQL Server AlwaysOn setup, whenever you are using a listener to connect to a secondary node, you have to provide additional connection parameters like ApplicationIntent=ReadOnly so that you can connect the secondary node. If you have to connect many times in a day to the secondary node, keying the additional connection parameters becomes […]