RDBMS HUB

Syspolicy Purge History job getting failed

2021-09-14T15:35:00.003+05:30

Hi Friends,

This is a very simple post where I will provide a solution for the failure of the job "syspolicy_purge_history".

In my environment we had SQL 2017 on one of the machines and later I was asked to install SQL 2012. After the installation as you know, this job would get created automatically.

This job contains 3 steps out of which the job is getting failed at the 3rd step with the below error message

The error information returned by PowerShell is: 'Could not load file or assembly 'file:///D:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSSnapins.dll' or one of its dependencies.

It can be fixed quite easily by just changing the environment variables. When it comes to root cause this happened as the newly installed SQL Version (lower) PowerShell module being sit to the bottom of the PowerShell Module list. As a result, it looks for higher version when executing the Powershell Script.

select the PSModulepath and click on Edit button

As you can see from the above image the 2017 version is sitting on top of 2012 hence we just need to move up 2012 ahead of 2017 to overcome this error.

I believe sometimes small tips will save lot of time hence sharing this.

Thanks for Reading.

A Bizarre scenario of connecting to Database Engine with different tools

2021-05-16T01:31:00.003+05:30

Hi Friends,

I wish all of you are doing safe and my sincere prayers to all of them who lost their lives during this battle with the on-going Pandemic. I am not able to focus on my work as many of my families got affected and I am trying my best to come out of this.

This post is more of a questionnaire rather than a solution. I got a mail last week saying the application team are getting time out Errors when they are trying to connect to Secondary replicas for reporting purpose.

As we know my initial checks are with Always ON Routing and couldn't find any issues in that.

Later I started checking from my end and surprisingly I didn't find any issues with SQL Server Management Studio from the Jump Server.

Then I tested the same with SQLCMD and I got the below Error.

I am really not sure what went wrong as the same is working from SSMS then I tried from UDL by selecting SQL Server Native Client as the driver and I got the below error

As mentioned I am not getting the error messages from SSMS however from SQLCMD or UDL file it it not working. I don't have Azure Data Studio to test this out unfortunately.

Login time out Expired

Unable to complete login process due to delay in opening the server connection

Later I tested the same from my Monitoring server & Reporting server instead of the jump server and from both the servers the results are fine.

I understood now there lies some problem from the Jump server but how to prove as said in the title this is really weird as there are no issues from SSMS.

So I started thinking bit further and made use of -l parameter to my SQLCMD usage

This time I am able to connect to the Listener as you can see from the above image if I don't use that I got the error

I thought of doing the same for my UDL test connection too

As you can see we have an option to set the connection time out property as well as Application Intent there. And the connection went fine only if I made use of "MICROSOFT OLEDB PROVIDER FOR SQL SERVER" but not with "SQL SERVER NATIVE CLIENT 11.0".

Now the questions left with me are

1) How come SSMS is working fine?

2) Why there are no issues from Monitoring server and Reporting server but only from Jump server?

3) why only with OLEDB provider but not with Native client?

To debunk this I tried further and got the below solutions but I am not sure if these are the right answers.

1)How come from SSMS?

Because SSMS uses .Net SqlClient Data Provider below is the image which confirms the same. Also I made use of below powershell script to see if I am getting the proper server names with & with out Application Intent.

[string] $Server= "ListenerName"

[string] $Database = "DBNAME"

[string] $SqlQuery= $("SELECT @@servername")

$Command = New-Object System.Data.SQLClient.SQLCommand

$Command.Connection = $Connection

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlConnection.ConnectionString = "Server = $Server; Database = $Database; Integrated Security = True;"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlCmd.CommandText = $SqlQuery

$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet

$SqlAdapter.Fill($DataSet)

$DataSet.Tables[0] | out-file "C:\jump\powershell.txt"

2) Why there are no issues from Monitoring server and Reporting server but only from Jump server?

For this I ran tracert and from the jump server and from the monitoring server below is the output . The picture says it all. Form the jump server as expected I got multiple hops and some times time outs as well.

3) why only with OLEDB provider but not with Native client?

Well I don't have an answer for this but as per the below link there are no updates being shipped with SQL Server Native Client.

https://docs.microsoft.com/en-us/sql/connect/connect-history?view=sql-server-ver15

Conclusion: To Mitigate the issue I made use of -l parameter and the issue got resolved to certain extent and this is how I worked to answer my self to the questions pose while connecting to DB Engine from different tools.

Hope you Learned something new and Happy Reading. Stay Safe.

How to make use of SP_BlitzCache to resolve performance issues

2021-03-27T11:54:00.001+05:30

Hi Mates,

It has been almost 2 months since I posted something in my blog. In this post I would like to share one of the performance issues of what we had in my Environment.

I wonder how we will work without our beloved SQL giants say (Paul Randal & his Team, Brent Ozar and many more). Their contribution to SQLServer is immense and it's helping DBA's to achieve their tasks.

Coming to the problem I got an invitation to join a performance issue and they observed the transactions are getting timed out. Eventually everyone feels it's Database issue.

Unfortunately, I do not know much about the Environment and we got the access to the databases recently.

When checked I could see blocking however it did not last for more than 5 seconds. I came to know that environment handles card processing which means every transaction has to happen with in 15 seconds.

So Back to title how SP_BlitzCache helped me? When I ran the SP I could see one of the stored procedures being prone to Deadlocks and Blocking.

Upon Looking further this SP has some few 100 lines of code and one of the table has got only one clustered index.

After running SP_BlitzCache I could see this SP is going for trivial optimization instead of full.

If you are aware of trivial plans then you can read further if not below is the link which explains about trivial plans

https://www.erikdarlingdata.com/tasting-menu/the-sql-server-performance-tasting-menu-trivial-plans/

That SP is getting executed quite frequently (57 executions per minute) and there are too many Lock Escalations too.

What is the problem with trivial plans? It will not give us missing index recommendation.

My initial suggestion is to make use of Trace flag 8757 OPTION(QUERYTRACEON 8757).

I made use of this in lower environment and re-run the query and as expected the execution plan come up with Missing Index recommendation.

so now the question is should we make use of trace flag all the time to achieve Full optimization? Well, not really once you create that missing non clustered index you don't need that trace flag any more as SQL Engine will go for FULL optimization automatically.

Before the index creation we used to get Deadlock and Blocking alerts quite frequently after that all the alerts got hassle free.

Once again I would like to thanks Brent Ozar for his great help to the SQL Community.

How to restore Database from one Managed instance to other

2021-01-29T19:45:00.002+05:30

Hi Mates,

Today I would like to share about restoring a database from one Managed Instance to other. We have 2 methods to achieve this.

1) Traditional way of using Azure Storage Explorer

2) Azure CLI

unfortunately as of now we can't perform this using Azure portal

When it comes to Traditional way we need to backup the database to blob storage container. you can use command or GUI to achieve this.

Before doing this we need to configure that and in our environment we don't have permissions hence informed to Hosting team to create one. upon getting the information we need to create credentials under security in Management Studio

After that you can trigger the backup using TSQL command or GUI like below. Keep in mind we can trigger copy-only backups as the backups will be taken care automatically in MI

BACKUP DATABASE [XXXXXXX]
TO URL = 'https://XXXXXX.blob.core.windows.net/XXXXX/XXXX_20200424.bak'
WITH COPY_ONLY

once done you can check your backups in Azure storage Explorer where we have option of upload/Download

In this case we need to download the backup to our local server and then need to upload the backup file to another Managed instance blob container.

After Uploading the file we need to perform the restore.

restore database [XXXXXX] from URL='https://XXXXX.blob.core.windows.net/XXXX/XXX_backup_2021_01_29_130404.bak'

Unlike in on-premises we can't run with stats as it throws an error. Interestingly when I tried to perform restore from GUI I got the below error. Probably it might be due to permission issue

We can go with first method provided if we don't have permissions to carry out using AZ az sql midb restore.

In my Environment we don't have permissions to make use of that and I got the below error.

How to make use Azure CLI? At first install AZ CLI on your jump server. Then Launch PowerShell and run the command AZ Login which will redirect to your browser popping for user name and password.

Next we need to set subscription by running the below command. you can get the subscription details using portal or using commands

az account set --subscription "Enter Subscription ID"

After that you need to run the below command

az sql midb restore -g Source_Resource_group --mi Source_instance_name

-n Source_database_name --dest-name Destination_database_name

--time "2021-01-29T11:30:00" --dest-mi Dest_instance_name

--dest-resource-group dest_resource_group

Just to keep in Mind that Managed instance is making use of UTC time kindly adjust to your needs.

"TIME" - managed instance is UTC time

This way we can restore the database faster .

Alerting for Deadlocks in SQL Azure and Monitoring using Azure SQL Analytics

2021-01-11T02:46:00.001+05:30

Hi Mates,

I always remember my childhood where my parents used to tell me it is only up to 17 years we need to study and thereafter we can enjoy. However after stepping into the IT industry I realized this is not true at all .

After the cloud expanded her wings at a rapid pace we just can't refrain ourselves.

Coming back to the topic although this is not too technical I just want to share how we can generate alerts for common scenarios like Deadlocks for Azure SQL Database.

I don't have a subscription to carry out testing even though I am very eager; however we can do a few things with a free subscription.

We just need to select alerts section under Azure database

Click on Alerts and select new alert we nearly have 33 signals and out of which I selected Deadlocks as shown below.

upon selecting we need to specify the condition just like performance counter alert in perfmon. We do have static and Dynamic alerting mechanism.

A Dynamic threshold uses advanced machine learning to automatically determine the threshold value using the metric's historical values and the sensitivity has been classified in to High, Medium and Low.

For a counter like Deadlocks we can go with Static and set the threshold value accordingly.

Post that under Action group we need to select notification type where I opted for Email and passed my Gmail details(For Testing)

After completing the above information finally under Alert rule details select the severity based on your requirement. This is how it looks finally

Now we can generate a deadlock scenario and can verify if we are getting Alerts.

In my previous Article I explained how can we achieve Monitoring using Free tools.

https://www.chiranjeevivamsydba.com/2021/01/make-use-of-free-monitoring-tools-for.html

This time I wanted to test native method of Azure SQL Analytics and it has some cool features where we just need to create

1) Log Analytics workspace

2)Diagnostic setting

3) Azure SQL Analytics

I could have explained this however it has already been clearly projected in the below links

https://eitanblumin.com/2020/03/17/azure-sql-analytics-monitoring/

https://www.sqlshack.com/getting-started-with-azure-sql-analytics/

For testing this time I created a Blocking scenario and this is how it looks for my workspace.

I believe if we make use of Azure SQL Analytics and Free monitoring tools together then we can have everything in place.

Make use of Free Monitoring Tools for SQL Server on-premises and Azure Managed Instance

2021-01-01T21:47:00.003+05:30

Hi Friends,

I wish you a very warm New Year. As we know perhaps if there is any year that we would have waited eagerly it will be 2021 for sure. Personally, for me it is a mixed year however my blog last year had 40K views which for me is a huge achievement.

I would like to thank all of you for going through my posts and posting your valuable comments.

Coming back to our discussion being a DBA the most important thing is to have Monitoring in Place so that we can go back to History and see what drove us to failure.

If you are working in bigger firms obviously there will be Monitoring tools in place. However, how about companies that cannot afford money.

These days with the growing technology we need to keep ourselves updated on a regular Basis. Today I am going to discuss 3 free tools which will be very handy if you can make use of them.

InfluxDB is an open-source time series database, developed in Go by InfluxData. InfluxDB is optimized for fast, high-availability storage and retrieval of time series data for metrics analysis. This can be installed on a single server or as a clustered instance (Enterprise edition only).

Telegraf is an agent, written in Go, for collecting, processing, aggregating, and writing metrics. It must be installed on all devices that need to be monitored, and all metrics collected by Telegraf are pushed and stored in InfluxDB.

Grafana is an open source, feature rich metrics dashboard and graph editor for Graphite, Elasticsearch, OpenTSDB, Prometheus, and InfluxDB. Data stored on InfluxDB will be visualized using Grafana.

Before explaining them, I would really like to thank Tracyboggiano She completely explained how to make use of them. But in my blog, I will explain how to make use of them by installing those tools in Windows Environment and even for Azure Managed instance.

I would like to proceed with installation of Grafana first and then I will just show case an example.

you can make use of the below link and just look into the instructions of Grafana. you can ignore the rest

http://sqltouch.blogspot.com/2017/03/using-perfcollector-with-influxdb-and.html

In the above blog the author made use of perf collector which I am not making use of.

Once you are finished with the installation you can open the link http://localhost:3000

By default, Grafana listens on port 3000 so ensure that port is being opened on the firewall.

Coming back as said I will just share an example on how to make use of only Grafana. Many of you might already aware of Ring Buffers in SQL server. Using that we can capture 4 hours CPU history. Imagine you want a pictorial representation of the usage on all your servers then you can make use of Grafana.

How can we do this you can make use of the below links to understand how it works for sample Data.

https://www.sqlshack.com/overview-of-the-grafana-dashboard-with-sql/

https://www.mssqltips.com/sqlservertip/6008/sql-server-time-series-data-visualization-with-grafana/

For Visualizing CPU History against all your servers you can make use of the below query as an example

SELECT

$__timeEpoch([Event Time] ),

[server Name] as "metric",

[SQL Server Process CPU Utilization] as SQL_UT,

[Other Process CPU Utilization] as other_UT

FROM

[CPU_History]

WHERE

$__timeFilter([Event Time])

ORDER BY

[Event Time] ASC

If you have data already being collected and it is time series based and you need graphical representation then there is no better than Grafana.

Coming back to other tools (Influx and Telegraf) you can make use of the above links for the installation.

you need to install telegraf on any one of your test servers and in the configuration file to collect the data you just need to make the below changes in the output plugins and input plugins

urls = ["http://XXX.XX.XXX.XXX:8086"]# required

## The target database for metrics (telegraf will create it if not exists).

database = "telegraf" # required

On-premises

[[inputs.sqlserver]]

servers = [

"Server=localhost;Port=1433;User Id=telegraf;Password=XXXXX;app name=telegraf;log=1;",

Azure MI

[[inputs.sqlserver]]

servers = [

"Server=XXXXXXX.database.windows.net;Port=1433;User Id=telegraf;Password=XXXXX$;app name=telegraf;log=1;",

]

once you are done below are some of the reports what you can see

As you can see you really get the feel of the Monitoring tool also if you are running on SQL Server 2016 you can make use of the Query store to work from a query standpoint as well. This is just my idea and you can go through my views and implement them as per your needs.

Change of TDE from Service Managed Key to Customer Managed Key in Azure Managed Instance

2020-12-23T02:33:00.002+05:30

Hi Friends,

This post is just to share my Experience and findings with respect to TDE in Azure.

In our Environment we just have Azure Managed instance and I am still awaiting for my chance to work on Azure Virtual machines ,Azure SQL and Of course AWS.

Okay coming back to topic in Azure MI we have 2 options related to Transparent Data Encryption

1) Service Managed Key

2) Customer Managed Key

I don't have full permissions on Azure portal to carry out the testing on this however below links help you to understand on those.

https://www.mssqltips.com/sqlservertip/6641/azure-sql-database-tde-customer-managed-keys/

once your MI gets created by default TDE will operate in SMK Model.

I created a database by name TEST and it is encrypted by SMK however we have our own requirements and later Windows Team changed that to Customer Managed Key.

If you worked on MI by this time you know that we can initiate only copy-only full backups.

Msg 41904, Level 16, State 2, Line 3

BACKUP DATABASE failed. SQL Database Managed Instance supports only COPY_ONLY full database backups which are initiated by user.

Msg 3013, Level 16, State 1, Line 3

BACKUP LOG is terminating abnormally.

If you try to perform any other backups you will get the above message. As said above TEST database got encrypted by SMK initially and later it got changed to Custom Managed key.

In the meanwhile I created Storage account to carry out the manual backups.

When I started taking the backup I got the below error

Msg 41937, Level 16, State 1, Line 1

BACKUP WITH COPY_ONLY cannot be performed until after the next automatic BACKUP LOG operation

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

Later I created one more database by name Ireland and triggered the copy only backups and no issues were identified with that. In Azure MI we have automated backups in place and for every 5 minutes we do have log backups but despite of waiting for 30 minutes the error still persisted.

I Later realized it could be because of encryption so I turned off the encryption using the below command

alter database TEST set encryption off

But when cross checked the state of the database it lasted in the below stage "protection change in progress"

Because of the above reason I could see the below messages being recorded inside the Error logs.

2020-12-16 16:32:57.950 Backup BACKUP failed to complete the command BACKUP DATABASE TEST. Check the backup application log for detailed messages.

2020-12-20 11:19:45.950 Backup BACKUP failed to complete the command BACKUP DATABASE TEST. Check the backup application log for detailed messages.

2020-12-20 14:17:25.880 Backup BACKUP failed to complete the command BACKUP DATABASE TEST. Check the backup application log for detailed messages.

How to overcome this? I really don't know the answer for this and I tried the below commands

alter database test set encryption resume

alter database test set encryption suspend

Later I ran the backup but still the error persisted and finally I ran Checkdb for that database thinking by any chance if there were any errors but no errors were reported and to my surprise the backup worked.

2020-12-20 14:12:50.360 spid84 DBCC CHECKDB for database id 5 found 0 errors and repaired 0 errors.

Even now I am not clear the reason behind this. However if you carefully observe the messages I ran checkdb at 14:12 and the last backup failure message was on 14:17 so I guess there is some co-relation.

I would like to add few more points of what I observed

1) we can't track the history of the automated backups using Msdb backupset

2) By Default compression is not enabled for Managed instance however if you turn on it will helps only for Manual copy only backups . Again I am not sure if Automated backups will have them in place.

Automatic seeding of availability database in availability group failed with a transient error. The operation will be retried.

2020-12-15T23:26:00.005+05:30

Hi Mates,

This post helps you to solve the Error message that has been mentioned in the Title of the post.

We have 3 servers in One Data Center and other 3 in Second Data center. Most of the servers which are running on 2012/2014 are in Multi Subnet. However from SQL 2016 we started making use of Distributed Availability Groups.

Coming back to the issue my colleague told me that few of the Databases were not appearing in Forwarded Server (means primary replica of secondary Availability Group). When I checked the Error logs on the primary Replica I could find the below message

Automatic seeding of availability database 'TDEtesting' in availability group 'TEST_DAG' failed with a transient error. The operation will be retried.

My initial impression was there might be something wrong related to Seeding settings but in both of the Availability Groups they are in Automatic mode. To my surprise the database is synchronized state in secondary replica on the primary Availability Group.

what went wrong on primary(Forwarder) and secondary replicas of Secondary Availability Groups?

This time I checked the logs on the Forwarder server and below are the messages

2020-12-15 14:27:04.830 spid52s Error: 911, Severity: 16, State: 1.

2020-12-15 14:27:04.830 spid52s Database 'TDEtesting' does not exist. Make sure that the name is entered correctly.

2020-12-15 14:27:05.090 spid52s Error: 33111, Severity: 16, State: 3.

2020-12-15 14:27:05.090 spid52s Cannot find server certificate with thumbprint '0x3428858AF24F0D37387E7F5613AC5069E319D1F1'.

2020-12-15 14:27:05.090 spid52s Error: 3013, Severity: 16, State: 1.

2020-12-15 14:27:05.090 spid52s RESTORE DATABASE is terminating abnormally.

2020-12-15 14:27:05.090 spid52s Automatic seeding of availability database 'TDEtesting' in availability group 'TEST_DAG' failed with a transient error. The operation will be retried.

By looking at the logs I clearly understood the issue. The reason is that the problematic databases were part of Transparent Data Encryption.

My colleague added the database only on to Secondary replicas of the primary availability group by copying the certificate and private keys. By the way we can't add a database that is part of TDE through GUI. It has to be done manually using join option.

Also keep in mind at the time of creating the Distributed Availability Groups we need to ensure that there are no user databases in the availability group on the forwarded server.

One more Tip till today I was under impression that we can enable TDE on a database only by using server certificate but we can do this using asymmetric key too.

I tried doing this but this requires EKM and it has been very well described in the below link

Msg 33120, Level 16, State 1, Line 5

In order to encrypt the database encryption key with an asymmetric key, please use an asymmetric key that resides on an extensible key management provider.

https://www.sqlshack.com/configure-tde-database-alwayson-using-azure-key-vault-sql-server-2016/

Query Timeouts from Website

2020-12-02T17:24:00.003+05:30

Hi Friends,

In this post I would like to share my experience with respect to time out Error from a website with SQL Server Database as Backend.

As you can see from the above picture the website is not able to come up with results and it popped up with Message Query timeout.

From the Message it is very clear that ExecuteReader(.NET method) is not able to fetch the results within the default connection timeout.

In general for any .NET Application the default time out is 30 seconds however in my case it has been setup with 15 seconds.

When I asked the developer he said they have 5 webpages related to Logging (Audit, Error, Trace, Event and Episode). They were able to obtain the results for all the web pages with in 2 seconds however for Trace log it took 17 seconds for its completion. As the timeout has been set for 15 seconds the page is getting timed out.

As a DBA what will be our next steps?

I started with initial checks on the tables(statistics and Fragmentation) and they all are performing good.

Next I started checking the code and the indexes for the Logging tables and from index standpoint they look one at the same.

when verified the code I identified the problem and it is happening because of type conversion from DATETIME2 to varchar.

The Datecreated column inside the table(TransactionTraceLOG) is having DATETIME2 where as in the code they are changing that to varchar(106)

SELECT

@PageCount = (COUNT([TransactionTraceLogId]) / @PageSize),

@PageMod = (COUNT([TransactionTraceLogId]) % @PageSize)

FROM [dbo].[TransactionTraceLog] WITH (NOLOCK)

WHERE

(

[Source] = @Source or

@Source = '%%'

)

AND

(

@Date IS NULL OR

--CONVERT(VARCHAR, @Date, 106) LIKE CONVERT(VARCHAR, [DateCreated], 106)

I told them to avoid conversion in where clause and asked them to pre-populate but the developer told me they are using multi search functionality and they have 3 parameters to search for like below.

exec [dbo].[Transactions.TransactionTraceLog.RetrievePageBySource_test] @Source = '%%', @Page = 100, @Date = '11/01/2020' WITH RECOMPILE;

When it comes to Dynamic search conditions there are so many things that we need to consider and it requires lot of efforts. http://www.sommarskog.se/dyn-search-2008.html this will help you to understand completely.

Now back to the problem as said above no issues with stats/Fragmentation/Indexes and the code definition is same for all other stored procedures.

I always never rule out row count of the tables when it comes to performance issues. In this case the problem lies here the table size and the row count for other tables are too small hence the results are lightening fast however when it comes to this table the size is 60 GB and as they can't change the code I told them to purge. As expected after purging the issue got resolved.

As said we can change the column definition from datetime to varchar but again this is an architectural decision.

Do we need to eliminate Sort operator all the time in execution plan?

2020-11-29T05:08:00.008+05:30

Hi Friends,

As we know Performance is one area where the scope for learning will never end. Today I am going to share an article which emphasizes on Seek/Scan and Sort operators. There are many blogs and my article is nothing in front of them but I am just sharing the knowledge of what I could.

Let me start up with the demo first. We have 2 tables one containing LogID and other some random Dates. When it comes to second table as you can see it has PK/FK relationship on the column LogID and a userid column which has only 3 users (1,2,3).

CREATE TABLE [dbo].[LogTable]

(

[LogID] [int] NOT NULL IDENTITY(1, 1) ,

[DateSent] [datetime] NULL,

)

ON [PRIMARY]

ALTER TABLE [dbo].[LogTable] ADD CONSTRAINT [PK_LogTable] PRIMARY KEY CLUSTERED ([LogID]) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_LogTable_DateSent] ON [dbo].[LogTable] ([DateSent] DESC) ON [PRIMARY]

CREATE TABLE [dbo].[LogTable_Cross]

(

[LogID] [int] NOT NULL ,

[UserID] [int] NOT NULL

)

ON [PRIMARY]

ALTER TABLE [dbo].[LogTable_Cross] WITH NOCHECK ADD CONSTRAINT [FK_LogTable_Cross_LogTable] FOREIGN KEY ([LogID]) REFERENCES [dbo].[LogTable] ([LogID])

CREATE NONCLUSTERED INDEX [IX_LogTable_Cross_UserID_LogID]

ON [dbo].[LogTable_Cross] ([UserID])

INCLUDE ([LogID])

INSERT INTO [LogTable]

SELECT TOP 100000

DATEADD(day, ( ABS(CHECKSUM(NEWID())) % 65530 ), 0)

FROM sys.sysobjects

CROSS JOIN sys.all_columns

INSERT INTO [LogTable_Cross] SELECT [LogID] ,1 FROM [LogTable] ORDER BY NEWID()

INSERT INTO [LogTable_Cross] SELECT [LogID] ,2 FROM [LogTable] ORDER BY NEWID()

INSERT INTO [LogTable_Cross] SELECT [LogID] ,3 FROM [LogTable] ORDER BY NEWID()

I will show you top 5 rows (in descending order) in each of the tables

As you can see from the script above we have clustered index on logID column and non clustered index on Datesent column for the Logtable. when it comes to other table we have non clustered index on LogID with included column as userid.

Now I want to select all those logs (from LogTable) which has given userid (user id will be checked from cross table LogTable_Cross) with datesent desc.

SELECT DI.LogID

FROM LogTable DI

INNER JOIN LogTable_Cross DP ON DP.LogID = DI.LogID

WHERE DP.UserID = 1

ORDER BY DateSent DESC

If you see from the execution plan it went for Clustered Index Scan on LogTable and seek on LogTable_cross. It went for parallel execution plan and when it comes to Cost it is Sort operation which is consuming 45 %.

Let's execute the same query but this time with MAXDOP 1 and see if there is any change with respect to sort operation.

Now if you see above the execution plan we do have sort operation in place. But Now why it went with Hash Join? Why not with Merge join? Because the ordered property is false for the clustered index scan operator

When it comes to scans the database engine has 2 options.

1)Allocation Ordered Scans

2)Index Order Scan

When the plan shows a Table Scan operator, the storage engine has only one option: to use an allocation order scan. When the plan shows an Index Scan operator (clustered or nonclustered) with the property Ordered: True, the storage engine can use only an index order scan.

When the plan shows an Index Scan operator with Ordered: False, the relational engine doesn’t care in what order the rows are returned. In this case, there are two options to scan the data: allocation order scan and index order scan. It is up to the storage engine to determine which to employ.

In the above execution plan it opted for False which means it went for allocation ordered scan. As the order is false it has to go with Hash join. If it would have been true then definitely we will have Merge join operator.

Okay now in both plans (parallel and Serial) it is suggesting an missing index so let's create one and see if that eliminates sort operator.

We have an index with userid as the leading column and the missing index is suggesting to create leading column as LogID and then userID.

After creating the index I tried running the same query and I got the below execution plans for parallel and serial(maxdop 1)

So the top execution plan is for serial execution and the bottom for parallel. If you notice there is no change in the plan when it comes to parallel execution and for serial as explained above this time we got Merge join operator instead of Hash as now ordered property is true. Also it made use of the index what missing index has requested and it went with Index scan instead of seek.

Okay if you compare the costs the one with parallel outcomes the serial as it is just 19%.

Now back to my question the sort operator is there even now and it is the one which is contributing to the cost so how to get rid of this?

Well when I forced the optimizer to make use of nested joins then the sort operator got disappeared.

SELECT DI.LogID

FROM LogTable DI

INNER JOIN LogTable_Cross DP ON DP.LogID = DI.LogID

WHERE DP.UserID = 1

ORDER BY DateSent DESC

OPTION (MAXDOP 1, LOOP JOIN);

As you can see the parallel and the select both have no sort operators. Wow finally great to see the execution plan with out sort operator but wait let us compare the cost with the ones where we forced Nested loop join to the default one's of what Engine thinks as better plan.

The top execution plan is what Engine has opted and the bottom is where I forced as you can see Engine's selection outperformed mine. Let's do the same with Serial plan.

Just like parallel plan even the serial plan of Engine thinks that Nested loop join is not the better plan.

Now as my question points do we need to eliminate sort all the time? Well, it depends but certainly not in this case.

In fact we can eliminate sort here by creating Materialized view but I am not going to discuss this here.

I know this is bit lengthy and even few might feel it as clumsy but I am ready to help in case if you have any queries on the same.

Why my Differential backups are not running on Primary Replica

2020-11-27T02:49:00.004+05:30

Hi Mates,

One of my friends contacted me saying that she was not able to perform Differential backups on Primary Replica.

I was little surprised to hear this however I quickly asked her to check if they were part of Distributed Availability Groups.

So why you were not able to achieve this? If you understand DAG properly it is nothing but Group of Availability Groups . We can configure this across different domains and clusters.

The Fundamental difference between Always ON AG and DAG is in traditional AG(Single or Multi) there will be only one replica sending log records across all the replicas where as in DAG the primary will send the records to all the replicas in one Data center and to primary replica in another Data Center. In short we call this as forwarder replica and this replica will send the log records to other replicas in the cluster.

Above is the image what I got from her even though it is primary replica in her case as this replica is being part of DAG it is technically secondary replica.

when it comes to Log Backups I wrote an article which helps you in configuring them.

https://www.chiranjeevivamsydba.com/2020/06/ola-hallengren-script-for-distributed.html

In case of full backups if you make use of Ola Hallengren scripts and left with default settings of Backup preference (prefer Secondary) then you will see databases being backed up on both Data centers.

Now the question is Do you want the Full backups to happen on Both Datacenters? If yes then you can live with your defaults. Otherwise you need to add a logic to your jobs so that the full backups only on one Data center.

I am using the same logic of what I made use for Log Backups. First check if DAG is primary or not and if it is then check whether the replica is secondary inside the primary DAG. If so then run the Full backup.

DECLARE

@DAG VARCHAR(MAX), @AG varchar(50), @primary varchar(50)

DECLARE cursor_product CURSOR

FOR

select s.primary_replica

from sys.dm_hadr_availability_group_states s

inner join sys.availability_groups g

on s.group_id=g.group_id

where g.name like '%-DAG%'

and s.primary_replica in (select g.name

from sys.dm_hadr_availability_group_states s

inner join sys.availability_groups g

on s.group_id=g.group_id

inner join sys.availability_replicas r

on s.group_id =r.group_id

where r.replica_server_name in ('NODE1','NODE2','NODE3'))

OPEN cursor_product;

FETCH NEXT FROM cursor_product INTO @DAG

WHILE @@FETCH_STATUS = 0

BEGIN

select @primary = s.primary_replica

from sys.dm_hadr_availability_group_states s

inner join sys.availability_groups g

on s.group_id =g.group_id

where g.name=@DAG

BEGIN

IF @primary != @@SERVERNAME

EXECUTE [dbo].[DatabaseBackup]

@AvailabilityGroups = @DAG,

@Directory = N'\\XXXXXXXXX',

@BackupType = 'FULL',

@Verify = 'Y',

@CleanupTime = 72,

@CopyOnly='Y',

@CheckSum = 'Y',

@LogToTable = 'Y'

END

FETCH NEXT FROM cursor_product INTO

@DAG

END

CLOSE cursor_product;

DEALLOCATE cursor_product;

If you see the above script I am searching for string DAG which means in your environment it can be different. As always you need to follow proper naming conventions to be hassle free.

How SQL Server 2019 helped me in solving Performance issue

2020-11-26T05:15:00.000+05:30

Hi Friends,

I would like to share an interesting scenario with you all. The issue started on Azure Managed instance where my lower Environment is residing (DEV/QA/UAT) and the production is hosted on on-premises.

Application Team raised a ticket stating the performance is way too bad on Azure MI. When cross checked the query was taking 25 minutes and on production it was just taking 1 minute.

Now the ball has come to my court and below are the things what I verified.

Resource comparison in Azure MI(RAM:20.4 GB CPU's:4) and On-premises (RAM: 32 GB CPU's :8)

In Azure MI for every 1 core we will have 5.1 GB of RAM

  4 vCores  =   20.4 GB
  8 vCores  =   40.8 GB
 16 vCores  =   81.6 GB
 24 vCores  =  122.4 GB
 32 vCores  =  163.2 GB
 40 vCores  =  204.0 GB
 64 vCores  =  326.4 GB
 80 vCores  =  408.0 GB

Then I went with Index definition ,compatibility level and statistics and they all look one at the same.

By the way Azure MI is hosted in General purpose tier so obviously the storage will not be faster.

Forgot to mention I verified whether the application server is hosted in Azure or not and it is.

My initial suspect was Resources shortage(Ram & CPU) on MI. However anything in Azure is cost based. Now when I asked the developer to run the query most of times it is in suspended state with waitstats as "PAGEIOLATACH". This again made me to think from Storage perspective again we can't change that from General to Business critical.

After a day developer told me that it was working fine on one of the databases. However the no of rows are almost Zero for many of the tables related to problematic query.

As you can see from above image hence we can expect that to be faster.

Now I am back to square one but this time I thought of changing the CL from 110 to 150 and it did worked.

Before providing the explanation I made use of https://statisticsparser.com/ to see the number of reads before and after the CL change.

Before Change:

After change:

As you can see the logical reads has come down a lot and also the query which was running for 25 min got reduced to 5 min.

The query was not able make use of parallelism as it has inhibitors when you right click and open the execution plan as XML you will very well find the reason. In my case it was below

<QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="TSQLUserDefinedFunctionsNotParallelizable"

of course I could have changed that but this being vendor application I can't change the code. Now finally how did SQL Server 2019 helped me because it was making use of new feature batch-mode-on-rowstore.

In my query they were making use of Windows partitions like below

SELECT ROW_NUMBER() OVER (PARTITION BY IDMASTransaction ORDER BY txfe.ID DESC) As RowNum

Usually batch mode are super good with Colum Store Indexes which were introduced in SQL Server 2012. However from 2019 there is no need for the table to have Column Store Indexes and it can work very well with Analytical Queries.

This is Database Scoped configuration

1	ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = ON

That being said kindly don't turn it on until proper testing has been done. In my case it was fruitful

Below link will further help you to understand this concept

https://techcommunity.microsoft.com/t5/azure-sql/introducing-batch-mode-on-rowstore/ba-p/386256

I hope you will explore this in your Environment as well. Thanks for Reading.

Database not in Synchronized state in Distributed Availability Group

2020-11-13T21:26:00.000+05:30

Hi All,

This Again is a simple post which explains about the below Error

Local availability replica for availability group 'XXX-DAG' has not been granted permission to create databases, but has a SEEDING_MODE of AUTOMATIC.

How to avoid this error all together? When we have the above error we just need to run the below command

--Allow the Availability Group to create databases on behalf of the primary replica

ALTER AVAILABILITY GROUP [XXXX] GRANT CREATE ANY DATABASE

Also Make sure there are no databases when creating Distributed availability Group. My approach would be to restore on all replicas with no recovery and then bring online on primary replica of primary Availability group and then perform join so that the database would be part of DAG automatically.

How to remove database from Always On in Distributed Availability Group

2020-11-12T05:07:00.002+05:30

Hi Mates,

This is very simple post and it helps you in removing Database from Distributed AG.

We have 3 replicas in one Data center and the other 3 in Different DC. As you know in Multi-subnet there will be one windows cluster that will be spanned across different networks. But in DAG we can have 2 different windows clusters and each of them will have it's own AG's .

I added a database to DAG and now my requirement is to remove it and then restore Database with fresh copy there after adding it back again to DAG.

Steps will differ here as we have the concept of forwarder replica in DAG.

1) On the forwarder replica we need to run the below command

ALTER DATABASE [DBNAME] SET HADR OFF

This will leave the database in restoring state on 3 replicas (forwarder and the secondary replicas)

2) Now connect to primary Replica of primary Availability Group and run the command

ALTER AVAILABILITY GROUP [XXX] REMOVE DATABASE [XXX]

This will work in similar norms to that of traditional AG which means online state on Primary and restoring on Secondary replicas

Finally we will have database in online state on primary replica of (primary AG) and remaining all(5 in my case) of them will be in restoring state

When I was adding the database to DAG I got the below error

Msg 1408, Level 16, State 211, Line 3

The remote copy of database "XXXXXXX" is not recovered far enough to enable database mirroring or to join it to the availability group.

You need to apply missing log records to the remote database by restoring the current log backups from the principal/primary database.

This is a straight forward error and many people would like to go with log backups however just to let you know we can bridge the gap even with Differential as well.

In case if you want to know how the log backups works on DAG below is the link for the same.

https://www.chiranjeevivamsydba.com/2020/06/ola-hallengren-script-for-distributed.html

Thanks for Reading.

Linked Server Collation Issue

2020-11-05T19:24:00.000+05:30

Hi Mates,

Today I would like to share an issue that has been posted in of the groups. It's actually is a simple issue however I was not able to answer it properly. Below is the message that has been shared

Msg 468, Level 16, State 9, Line 5

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_CI_AS_KS_WS" in the equal to operation.

I thought of reproducing the issue and here are the details

Instance 1 : Collation is Latin1_General_CI_AS

Instance 2: Collation is Latin1_General_CI_AS_KS_WS

I created Linked server on Instance1 that connects to Instance2.

Below is the query what I ran to reproduce the issue on Instance1

select * from [instance2].[msdb].[dbo].[backupset]

where collation_name in (select collation_name from msdb..backupset )

My initial solution was it is not possible unless we use the keyword collate. But later I realized there lies an option in Linked server properties

By default the remote collation property is set to true once we change that to false we will no more have the issue

How to troubleshoot connectivity issues due to firewall

2020-10-31T16:09:00.003+05:30

Hi Friends,

Today I will share an issue which ideally started at higher level however after troubleshooting it landed on Basics.

Let me start of the issue we configured Distributed Availability Groups and as part of it we requested the application team to make use of ApplicationIntent=Readonly in their reporting services Data Source.

When they checked they said it is not working and below is the error message

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: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)

The message is not popping up when we removed the Application Intent from the connection String.

At first Glance it was little surprising and I thought there lies some issue with Read only Routing.

I wrote an article on how to configure Routing from GUI below is the link

https://www.chiranjeevivamsydba.com/2019/03/always-on-read-only-routing-from-sql.html

when checked the routing was working perfectly fine. Now where lies the problem is it something with reporting services? Not at all whenever I have such issues I always make use of UDL file to test the connectivity.

By Default UDL file will have it's Application Intent as READWRITE but I changed that to READONLY as the issue is happening only with that. I created this UDL file from my jump server and it is working fine.

Again I got struck and this time I created the same file on the server where Reporting services are deployed(Installed) but this time the UDL file has thrown the below error

Now I can say it is not anything with reporting services. So why it is working with Readwrite why not with Readonly? Because at the time of requesting my colleague has requested with Listener Name and port no but he has not provided the server names hence we have this issue

When I just passed the primary Replica name instead of Listener name it too is getting failed with the same error message.

My Servers have sensitive information hence they are placed behind the firewall so to overcome this we raised change requested this time by passing the server names as well with port no's.

The same can be done with SQLCMD too but on reporting servers we don't have that. Hence I made use of UDL to test the connectivity.

I always feel that most of the solutions can be obtained easily if we have our basics in place.

Configure-Multiple-subnet-Always on-Availability-Group in Linux(CENTOS)

2020-10-27T06:33:00.011+05:30

Hi Friends,

This post helps you in creating Multi Subnet Always ON in Linux Environment. Frankly speaking I had so many hurdles however I achieved this.

The problem is I don't have much knowledge on Linux unlike Windows where I do know how thinks workout.

As this being multi subnet the problem is all about how to accomplish connectivity between nodes .

The ideas that popped in my mind are

1) Achieving Routing between the 2 nodes using Router (another VM with Linux )-->It didn't work

2) using Azure VM's by configuring VNET to VNET (This will work but I don't have subscription)

3) Adding Linux machines to Windows Active Directory and then configuring Routing (It did work)

In the past I wrote a post on how to perform multi subnet in Windows and below is the link

https://www.chiranjeevivamsydba.com/2018/11/multi-subnet-always-on-scenarios.html

My post is truly inspired from Edwin Sarmiento 5 part series (MSSQLTIPS.COM). This is however single subnet Always ON.

The Master (Edwin) has given almost all the details hence I don't want to go through all the details. I will share just the extra steps that are required to perform Multi Subnet.

As said I am not an Linux Expert hence I installed 3 VM's on my laptop

First VM: Windows Domain server with 2 network cards (192.168.81.1) and (192.168.82.1)

Second VM: Linux CENTOS 7 with GUI support

Third VM: Linux CENTOS 7 with GUI support

One problem is we need to configure Manual IP Address to get this work. But before doing this we need to download below features.

1) SQL Server 2019

2) SQL Server HA

3) pacemaker software

4) install Active directory related software's (As we need to add our Linux Machines to Windows Domain server

5) Oracle Virtual Box Guest Additions (As we need to copy the commands between our Host and virtual machines)

Also I would like to warn you on one more thing at the time of creation of virtual machines (Linux) please make sure you create bigger partitions as I ran out space on root folder.

As the master(Edwin) explained everything in detail I will not reinvent the wheel .

I will just share my Environment details and the extra steps for Multi Subnet

As you can see I am on SQL server 2019 and when it comes to my IP configuration I have

LINNODE1 with (192.168.81.10) and LINNODE2 with (192.168.82.10)

After you have configured all the steps mentioned by Edwin in his 5 part series we need to export Cluster Information Base in to a text file using the below command and

sudo pcs cluster cib cib.txt

Then you need to make the changes highlighted(in red) and push it back by running

sudo pcs cluster cib-push cib.txt

<instance_attributes id="vip1-instance_attributes1">

<rule id="Subnet1-IP" score="INFINITY">

<expression id="Subnet1-Node1" attribute="#uname" operation="eq" value="linnode1"/>

</rule>

<nvpair id="vip1-instance_attributes-ip" name="ip" value="192.168.81.20"/>

</instance_attributes>

<instance_attributes id="vip1-instance_attributes2">

<rule id="Subnet2-IP" score="INFINITY">

<expression id="Subnet2-Node1" attribute="#uname" operation="eq" value="linnode2"/>

</rule>

<nvpair id="vip1-instance_attributes-ip2" name="ip" value="192.168.82.20"/>

</instance_attributes>

</operations>

</primitive>

I will show you where my resources are running before the failover

Now perform a manual failover using the below command

As you can see now I got my resources from NODE2 to NODE1

Any time we can connect to only 1 IP Address just like windows. Below are the IP Addresses for my Listener

192.168.81.20 (on LINNODE1)

192.168.82.20 (on LINNODE2

I felt really happy with this as it is perhaps first of it's kind( I have not seen any blog related to this apart from the official MS Documentation).

If any one is interested in getting this done I will help you for sure. Do reach me on my mobile +353894316824 or WhatsApp (+919739093905)

Always ON Connection Time-out Errors

2020-10-25T04:01:00.002+05:30

Hi Mates,

Today I want to share my Analysis with respect to connection Time out Errors in ALWAYS ON.

In my previous post I mentioned about how to go through Windows Cluster Logs

https://www.chiranjeevivamsydba.com/2020/10/my-availability-groups-got-affected-by.html

Always ON is such a topic where we need to examine different logs and need to analyze them properly before coming to any conclusions.

Okay, Let me go through the issue. I got an incident from Application Team indicating they experienced some issues yesterday. When I enquired about the time they said it is at early Morning 3 AM.

At first, I checked if there were any Maintenance jobs running at that time as usually these are times when they run. However there were nothing running at that time.

My replicas are in a Multi-Subnet with 3 nodes in Each Data Center

When checked the Error Logs I could see below messages inside them and all of them were with 3 replicas in another DC.

2020-10-23 03:02:56.780 spid207s A connection timeout has occurred on a previously established connection to availability replica 'XXXXXXX'

2020-10-23 03:02:56.870 spid295s A connection timeout has occurred on a previously established connection to availability replica 'XXXXXXX'

2020-10-23 03:02:56.910 spid295s A connection timeout has occurred on a previously established connection to availability replica 'XXXXXX'

2020-10-23 03:02:56.780 spid207s Always On Availability Groups connection with secondary database terminated for primary database 'ReportServerTempDB' on the availability replica 'XXXXXX' with Replica ID: {12769114-d890-4e2d-b4fa-7c346244be1e}.

2020-10-23 03:02:56.780 spid299s Always On Availability Groups connection with secondary database terminated for primary database 'ReportServer144' on the availability replica 'XXXX' with Replica ID: {12769114-d890-4e2d-b4fa-7c346244be1e}.

So Now how will we troubleshoot this.

1) Error Logs: Those are the Messages what I can see

2) Always ON Health Sessions(Extended Events): They too have the same messages of what I could see inside Error Logs.

3) SQL Diagnostic Extended Event files: These are available only on Primary Replica and I use them a lot for Investigation Purposes.

There are 2 things I would like to discuss here firstly at the highlighted part under component state we see it as Warning. Also it says we have something related to Blocking. I got an impression that Blocking was the culprit however the database is not on ReportServer or ReportServerTEMPDB which are the ones that got affected.

4) System Health Session (Extended Events): This is kind of redundant to that of SQL Diagnostic in this case even it says the same information to that of above.

5) Windows Cluster Logs: We didn't have any Failover hence there is not much here apart from the below message. (I understood a lot what are the messages that gets logged for Connection time out Errors but I can't post here as it deviates from the topic)

00001d1c.00002af8::2020/10/23-03:02:56.370 INFO [RES] SQL Server Availability Group: [hadrag] SQL Server component 'query_processing' health state has been changed from 'clean' to 'warning' at 2020-10-23 03:02:56.360

Frankly Speaking none of the logs helped me to understand what is the real contributor for these Connection time outs. Also there were no snapshots(VSS) or Antivirus related stuff running at that time so we can rule out them too.

I then thought of reproducing the Errors on my Test machine and I did that using Networking Tools.

As you can see I simulated the stuff and at the same time I could see the message

A connection timeout has occurred while attempting to establish a connection to availability replica 'XXXXXX' with id [B4DF270A-D29D-451A-B2BD-4727323D7234]. Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance.

So How will I troubleshoot this? My Answer is try to run a light weight ping test from the source (primary replica) to Secondary servers and see if you are seeing any time outs

Below is the command what I made use of

ping.exe -t 192.168.58.68|Foreach{"{0} - {1}" -f (Get-Date),$_} > D:\backups\192.168.58.68.txt

As you can see from the above image I got the output in to Notepad file. I would only recommend this if you are seeing connection time out messages quite frequently.

Hope you have Learned Something with this.

My Availability Groups got Affected by Covid-19

2020-10-04T04:20:00.004+05:30

Hi Friends,

By looking at the title one might feel bit funny however eventually down the line you will feel it apt. This will be a lengthy post as I will show you how I performed detailed Investigation hence be little patient but I promise you will learn something new for sure.

Let me start the issue on Sunday early Morning I got a call at 6:30 stating that availability groups got affected but after 2 minutes I didn't receive any other call. Being Sunday I was little relaxed soon after 30 minutes I started getting calls on the same server this time I didn't took that easily and connected to the servers to see what's the issue.

When I opened Cluster administrator I could see below state for one of the nodes

By looking at this I was surprised to see one of my nodes (Primary Replica) went in to Quarantine state. My first impression was that My AG got affected (of course not by Covid-19). I read the concept of Windows nodes getting quarantined and it has been introduced from windows 2016. I ran the command Start-ClusterNode –ClearQuarantine and after few seconds everything went normal.

So what is Quarantine?

It is not desirable to have a node constantly falling in and out of cluster membership, which is likely caused by some underlying configuration or hardware issues. This “flapping” of membership can cause resources to move constantly between nodes, resulting in service interruption. Thus Windows Server 2016 introduces the Quarantine logic to stop this from happening.

Quarantine is triggered for a node if the node ungracefully leaves the cluster three times within an hour. By default, that node will stay in Quarantine state for 2 hours, during which time it is not allowed to rejoin the cluster nor host cluster resources

Not only Humans even the nodes are supposed to go in to Quarantine state.

Now let me share the Environment details, we have 3 nodes in one Data Center and 3 in other Data Center and File share witness as we have even number of nodes.

Below is the Log information for SQL Server:

2020-09-27 09:23:29.410 spid29s Failed to update Replica status within the local Windows Server Failover Clustering (WSFC) due to exception 41034.

2020-09-27 09:23:29.410 spid29s Failed to update Replica status within the local Windows Server Failover Clustering (WSFC) due to exception 41005.

2020-09-27 09:23:29.900 Server Always On Availability Groups: Local Windows Server Failover Clustering node is no longer online. This is an informational message only. No user action is required.

2020-09-27 09:23:29.910 spid19s Always On: The availability replica manager is going offline because the local Windows Server Failover Clustering (WSFC) node has lost quorum. This is an informational message only. No user action is required.

2020-09-27 09:23:29.910 Server The availability group 'XXXX' is being asked to stop the lease renewal because the availability group is going offline. This is an informational message only. No user action is required.

2020-09-27 09:23:29.910 spid19s Always On: The local replica of availability group 'XXXX' is stopping. This is an informational message only. No user action is required.

2020-09-27 09:23:29.910 spid19s The state of the local availability replica in availability group 'UAE-AG' has changed from 'PRIMARY_NORMAL' to 'RESOLVING_NORMAL'. The state changed because the local instance of SQL Server is shutting down.

After some time( perhaps few seconds) the replica came back online without my intervention. Post that I could see below information inside my logs.

2020-09-27 09:24:45.060 spid19s Always On Availability Groups: Local Windows Server Failover Clustering node is online. This is an informational message only. No user action is required.

2020-09-27 09:24:45.970 Logon Error: 983, Severity: 14, State: 1.

2020-09-27 09:24:45.970 Logon Unable to access availability database 'XXXXXX' because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the

2020-09-27 09:24:47.050 spid52 The state of the local availability replica in availability group 'UAE-AG' has changed from 'RESOLVING_NORMAL' to 'PRIMARY_PENDING'. The state changed because the availability group is coming online.

2020-09-27 09:24:47.080 Server The state of the local availability replica in availability group 'XXXXX' has changed from 'PRIMARY_PENDING' to 'PRIMARY_NORMAL'.

As you can see the Availability Group went offline due to issues at Windows clustering level but it didn't failover to any other replica. you can find the Always ON Failover History using the below link

https://www.chiranjeevivamsydba.com/2020/01/always-on-failover-history.html

Let me show you the Windows Cluster logs at the same time. Interpreting the Cluster logs is not that simple as it is not our comfort Zone.

By looking at this we can say this is not the right Voting Mechanism as this being multi subnet the best recommendation is not to set votes for other site of the cluster as if there are any interruptions this will cause impact to the Availability Groups on the primary site.

This being Even number of nodes(6) we do have file share witness as a tie breaker.

As you can see from above in total we have 7 Votes so to have quorum intact we should have 4 votes.

When you are interpreting cluster logs it has it's own language my first task is get the node names in the order what it thinks

As you can see it thinks primary replica as NODE id=1 and then it skipped number 6 and other names lasted up to NODE id=7. With this in place we can easily break down the logs

Also if possible get the IPV6 address of each node as the cluster communicates with them

NODE4->fe80::9471:eaf1:16b8:6f66(10.8.52.4)

NODE3->fe80::8c93:1035:3a4f:89b0(10.8.52.5)

NODE7->fe80::a12c:fd7f:95b3:ec7b(10.8.52.14)

NODE5>fe80::c83f:5276:425d:626f(10.7.52.14)

NODE2->fe80::a1bd:2adc:3102:5cf4(10.7.52.4)

NODE1->fe80::f9ff:6084:a429:4df8(10.7.52.5)

000026c4.00000068::2020/09/27-09:23:00.742 INFO [IM] got event: LocalEndpoint 10.7.52.5:~3343~ has missed two-fifth consecutive heartbeats from 10.8.52.4:~3343~

000026c4.00000068::2020/09/27-09:23:00.757 INFO [IM] got event: LocalEndpoint 10.7.52.5:~3343~ has missed two-fifth consecutive heartbeats from 10.8.52.5:~3343~

000026c4.00000068::2020/09/27-09:23:00.773 INFO [IM] got event: LocalEndpoint 10.7.52.5:~3343~ has missed two-fifth consecutive heartbeats from 10.8.52.14:~3343~

The Cluster would communicate on port 3343 with the other nodes. In my case the primary replica (10.7.52.5) is not able to communicate with other side of the cluster nodes(10.8.52.4,10.8.52.5 and 10.8.52.14)

000026c4.000023f4::2020/09/27-09:23:02.008 ERR [NODE] Node 1: Connection to Node 4 is broken. Reason (10060)' because of 'channel to remote endpoint fe80::9471:eaf1:16b8:6f66%4:~3343~ has failed with status 10060'

000026c4.000030b8::2020/09/27-09:23:02.009 ERR [NODE] Node 1: Connection to Node 3 is broken. Reason (10060)' because of 'channel to remote endpoint fe80::8c93:1035:3a4f:89b0%4:~3343~ has failed with status 10060'

000026c4.00003094::2020/09/27-09:23:02.040 ERR [NODE] Node 1: Connection to Node 7 is broken. Reason (10060)' because of 'channel to remote endpoint fe80::a12c:fd7f:95b3:ec7b%4:~3343~ has failed with status 10060'

so it says the connection is broken due to missing heartbeats. Then it tries to establish the connection with other dynamic ports but it was not able to make it.

000026c4.00001774::2020/09/27-09:23:09.847 INFO [SV] New real route: local (10.7.52.5:~3343~) to remote (10.8.52.4:~57315~).

000026c4.00001774::2020/09/27-09:23:09.847 INFO [SV] Got a new incoming stream from 10.8.52.4:~57315~

000026c4.00001774::2020/09/27-09:23:09.597 INFO [SV] New real route: local (10.7.52.5:~3343~) to remote (10.8.52.5:~53800~).

000026c4.00001774::2020/09/27-09:23:09.597 INFO [SV] Got a new incoming stream from 10.8.52.5:~53800~

000026c4.00001874::2020/09/27-09:23:09.566 INFO [SV] New real route: local (10.7.52.5:~3343~) to remote (10.8.52.14:~63184~).

000026c4.00001874::2020/09/27-09:23:09.566 INFO [SV] Got a new incoming stream from 10.8.52.14:~63184~

So you can see that it tried to establish the communication but eventually it failed and came up with below messages

000026c4.00000068::2020/09/27-09:23:12.732 WARN [NDP] All routes for route (virtual) local fe80::f9ff:6084:a429:4df8:~0~ to remote fe80::9471:eaf1:16b8:6f66:~0~ are down

000026c4.00000068::2020/09/27-09:23:12.748 WARN [NDP] All routes for route (virtual) local fe80::f9ff:6084:a429:4df8:~0~ to remote fe80::8c93:1035:3a4f:89b0:~0~ are down

000026c4.00000068::2020/09/27-09:23:12.764 WARN [NDP] All routes for route (virtual) local fe80::f9ff:6084:a429:4df8:~0~ to remote fe80::a12c:fd7f:95b3:ec7b:~0~ are down

So it tried her best to establish but after trying all the routes it finally says there is no route as all the routes got exhausted which means one side of the cluster is not able to communicate with other side.

000026c4.000011dc::2020/09/27-09:23:18.019 INFO [CORE] Node 1: Proposed View is <ViewChanged joiners=() downers=(3 4 7) inExtendedGrace=() newView=22505(1 2 5) oldView=22302(1 2 3 4 5 7) joiner=false form=false/>

After that it re-arranges her self with downers and joiners information as you can see in my case the other side of the cluster(3,4,7) are down and only left were (1,2,5). Now as I told you earlier we do have file share witness in place which should help us in these kind of scenarios. Remember this being ungraceful we will not have the concept of last man standing.

Now this is the most critical part

00001644.00001fc4::2020/09/27-09:23:28.331 INFO [RES] File Share Witness <File Share Witness>: Releasing temporary lock on witness file share.

00000c20.00002518::2020/09/27-09:23:28.332 INFO [QUORUM] Node 5 CompareAndSetWitnessTag:

Attempting to update witness tag (2020/04/03-08`23`05.525_146:2020/04/03-08`23`05.525_146:34575) to 2020/04/03-08`23`05.525_145:2020/04/03-08`23`05.525_145:34576.

00000c20.00002518::2020/09/27-09:23:28.332 WARN [QUORUM] Node 5 CompareAndSetWitnessTag:

witness tag (2020/04/03-08`23`05.525_146:2020/04/03-08`23`05.525_146:34575) is better than proposed tag (2020/04/03-08`23`05.525_145:2020/04/03-08`23`05.525_145:34576).

00000c20.00002518::2020/09/27-09:23:28.332 INFO [QUORUM] Node 5 CompareAndSetWitnessTag: releasing witness share lock

00001644.00001fc4::2020/09/27-09:23:28.332 INFO [RES] File Share Witness <File Share Witness>: Ignoring request to release witness share because it is not currently locked.

My Quorum is there on primary site however as per above logs it failed to obtain the lock as other node was successful in getting the lock which resulted in loss of Quorum.

As you can see from the above screen shot my file share witness doesn't have the latest copy if this would have been disk witness we could have definitely avoided the issue.

00000c20.000014f4::2020/09/27-09:23:28.332 INFO [QUORUM] Node 5: death timer is already running, 79 seconds left.

00000c20.00002518::2020/09/27-09:23:28.332 WARN Arbitration failed with error code 995, not emitting QuorumArbitratred message

00000c20.00002518::2020/09/27-09:23:28.332 ERR Shutting down because arbitration failed (status = 5925)

000007d8.00002f28::2020/09/27-09:23:28.332 WARN [RHS] Cluster service has terminated. Cluster.Service.Running.Event got signaled.

000026c4.00000718::2020/09/27-09:23:28.366 ERR [NODE] Node 1: Connection to Node 5 is broken. Reason (10054)' because of 'channel to remote endpoint fe80::c83f:5276:425d:626f%4:~3343~ has failed with status 10054'

000026c4.000011dc::2020/09/27-09:23:29.391 ERR [QUORUM] Node 1: Lost quorum (1 2)

000026c4.000011dc::2020/09/27-09:23:29.391 ERR [QUORUM] Node 1: goingAway: 0, core.IsServiceShutdown: 0

Now when it comes to voting we already lost 3 votes due to missing heart beats on other site followed by file share witness and one of the nodes in same site means we are left with just 2 votes

We can see from the above messages that file share witness was obtained by another site as a result we lost our quorum and there after to avoid split brain one of the nodes terminated the cluster service.

soon After it got recovered on it's own. After 30 minutes again I got the below messages inside SQL logs

2020-09-27 09:55:44.940 spid26s Failed to update Replica status within the local Windows Server Failover Clustering (WSFC) due to exception 41034.

2020-09-27 10:01:22.340 spid208s Failed to update Replica status within the local Windows Server Failover Clustering (WSFC) due to exception 41034.

As I mentioned above if a node ungracefully leaves 3 times in an hour which in my case (9:23, 9:55 and 10:01) the node will go in to Quarantine state.

Now I hope you will agree with the Title. Also the solution what I ran i.e.Start-ClusterNode –ClearQuarantine might not work in all cases. Hence I would suggest you to try to this as a last resort.

In case if you have any queries or comments do reach me on +353894316824/+919739093905(WhatsApp)

Are Our Secondary Replicas Really Synchronous?

2020-09-14T20:20:00.007+05:30

Hi Friends,

This is very simple post and explores the new setting of what we have on SQL Server 2017. I would like to thank David Fowler as it is inspiration from his blog

Okay, let us begin by understanding what Synchronous really means?

Hi Friends,

This is very simple post and explore the new setting of what we have on SQL Server 2017.

Okay, let us begin by understanding what Synchronous really means?

Normally, if we have got a secondary replica in synchronous commit mode, our primary cannot commit any transactions until that transaction has been hardened to disk on all the synchronous secondaries.

When you commit a transaction on the primary, SQL Server will send all changes over to its secondary nodes. It will then wait for confirmation from the synchronous nodes that the changes have been hardened before it can commit locally.

This ensures that those synchronous secondaries are always up to date and will mean that in the event of a fail over, there is no risk of data loss. Any delay in the secondary writing down those changes will mean a delay on committing on the primary (you will see this in the form of HADR_SYNC_COMMIT waits on the primary).

The primary replica is constantly monitoring the state of its secondaries. With the use of continuous ping, the primary node always knows if the secondaries are up or down.

It is when SQL detects that one of its synchronous replicas goes offline is when interesting things can happen.

So, here is the discussion that came up, if a synchronous replica goes offline for whatever reason, SQL won’t be able to commit any transactions and that means we can be confident that the secondary is up to date, right?

Well, not quite. Let us try running the following code to simply update a table with a timestamp every second.

We have 3 replicas running on Microsoft SQL Server 2017 (RTM-CU18) and all of them are in synchronous Mode.

I started testing

create table synctest(sname datetime)

TRUNCATE TABLE SyncTest

WHILE 1=1

BEGIN

INSERT INTO synctest VALUES (GETDATE())

WAITFOR DELAY '00:00:01'

END

select * from synctest

After a short while I Stopped SQL Services on one of the secondary replicas and here are the results

There were no records for 10 seconds

Interesting, we can see that there was a delay when the synchronous secondary went offline as we would expect. But then after a short while, it looks like SQL Server decided to continue committing transactions. Is that right?

Session-Timeout

And here is where the misinterpretation came in. Availability groups have a setting, session-timeout. This timeout controls how long a primary replica will wait to receive a ping from a secondary (by default, this is 10 seconds). Once that timeout period is exceeded, the primary will no longer wait for that replica when committing a transaction, effectively switching it to asynchronous commit.

This is the reason why, after 10 seconds of the secondary going down, we start to see transactions being committed again.

So, My Question is how will we ensure that we can avoid this situation?

SQL Server 2017 introduced REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT cluster resource setting.

This setting guarantees the specified number of secondary replicas write the transaction data to log before the primary replica commits each transaction

The default setting is Zero and I changed to one and carried out the same exercise as above and still the results are same where we got 10 seconds lag and the transactions continued post that.

Finally, after changing this setting to 2 as expected the transactions didn't run as the primary can't continue because the transactions can't get hardened on to secondary. Below is the error message

(1 row affected)

Remote harden of transaction 'INSERT' (ID 0x0000000001677d4d 0000:0000081c) started at Sep 14 2020 2:03PM in database 'india' at LSN (36:2213:3) failed.

(1 row affected)

Msg 596, Level 21, State 1, Line 3

Cannot continue the execution because the session is in the kill state.

Msg 0, Level 20, State 0, Line 3

A severe error occurred on the current command. The results, if any, should be discarded.

So Now think about this setting and implement it as per your needs. Below is the link which explains about the setting

REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT

Adding Distribution Database to Always ON

2020-09-11T18:48:00.000+05:30

Hi Friends,

Today I thought of checking how to add Distribution Database to Availability Group. Although we have Microsoft Document where we can get all the steps I tried this just to get my hands Dirty

By the way to configure this we need to have SQL Server 2017 CU6 or SQL Server 2016 SP2-CU3.

In my case we have 4 servers 2 of them are Distributor Servers one publisher and one Subscriber.

As I am majorly interested in testing I took the advantage of having same SQL Server Service Account and provided sysadmin permissions (which is not all required).

I have configured replication with minimal permissions and below is the link for the same.

https://www.chiranjeevivamsydba.com/2019/01/configuring-transactional-replication.html

Some Important points to keep in Mind:

1) Publisher and distributor need to be on separate SQL Server instances

2)If the listener for the availability group hosting the distribution database is configured to use a non-default port, then its required to setup an alias for the listener and the non-default port.

3)SSMS does not show Distribution Database as synchronizing/synchronized, when distribution database is added to an availability group.

4)Configuring distributor on the publisher needs to be done with scripts

5)Configuring the AG for distribution databases can only be done through scripts.

6)Switching an existing distribution database to an AG is not supported

There are many more but I just highlighted few of them. Okay enough of Theory let's chip in to Action.

Here are the Steps: (you need to run by enabling SQLCMD MODE in SSMS)

:Connect NODE1

sp_adddistributor @distributor = @@ServerName, @password = 'XXXXXXXX'

:Connect NODE2

sp_adddistributor @distributor = @@ServerName, @password = 'XXXXXXX'

-- Step2 - Configure the Distribution Database

:Connect NODE1

USE master

EXEC sp_adddistributiondb @database = 'DistributionDB', @security_mode = 1;

Alter Database [DistributionDB] Set Recovery Full

Backup Database [DistributionDB] to Disk = 'Nul'

-- Step 3 - Create AG for the Distribution DB.

:Connect NODE1

USE [master]

CREATE ENDPOINT [Hadr_endpoint]

STATE=STARTED

AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE

, ENCRYPTION = REQUIRED ALGORITHM AES)

:Connect NODE2

USE [master]

CREATE ENDPOINT [Hadr_endpoint]

STATE=STARTED

AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE

, ENCRYPTION = REQUIRED ALGORITHM AES)

:Connect NODE1

-- Create the Availability Group

CREATE AVAILABILITY GROUP [DistributionDB_AG]

FOR DATABASE [DistributionDB]

REPLICA ON 'INODE1'

WITH (ENDPOINT_URL = N'TCP://NODE1.contoso.com:5022',

FAILOVER_MODE = AUTOMATIC,

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

BACKUP_PRIORITY = 50,

SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL),

SEEDING_MODE = AUTOMATIC),

N'NODE2' WITH (ENDPOINT_URL = N'TCP://NODE2.contoso.com:5022',

FAILOVER_MODE = AUTOMATIC,

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

BACKUP_PRIORITY = 50,

SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL),

SEEDING_MODE = AUTOMATIC);

:Connect NODE2

ALTER AVAILABILITY GROUP [DistributionDB_AG] JOIN

ALTER AVAILABILITY GROUP [DistributionDB_AG] GRANT CREATE ANY DATABASE

--STEP4 - Create the Listener for the Availability Group. This is very important.

:Connect NODE1

USE [master]

ALTER AVAILABILITY GROUP [DistributionDB_AG]

ADD LISTENER N'LSNRTTU-DBA' (

WITH IP

((N'192.168.58.16', N'255.255.255.0')) , PORT=1433);

-- STEP 5 - Enable SQLNode2 also as a Distributor

:CONNECT NODE2

EXEC sp_adddistributiondb @database = 'DistributionDB', @security_mode = 1;

--STEP 6 - On all Distributor Nodes Configure the Publisher Details

:CONNECT NODE1

EXEC sp_addDistPublisher @publisher = 'NODE3', @distribution_db = 'DistributionDB',

@working_directory = '\\NODE1\repldata\'

:CONNECT NODE2

EXEC sp_addDistPublisher @publisher = 'NODE3', @distribution_db = 'DistributionDB',

@working_directory ='\\NODE1\repldata\'

-- SECTION 2 ---- CONFIGURE THE PUBLISHER SERVER

:CONNECT NODE3

EXEC sp_addDistributor @distributor = 'LSNRTTU-DBA', -- Listener for the Distribution DB.

@password = 'XXXXX'

-- SECTION 3 ---- CONFIGURE THE SUBSCRIBERS

-- On Publisher, create the publication as one would normally do.

-- On the Secondary replicas of the Distribution DB, add the Subscriber as a linked server.

:CONNECT NODE2

EXEC master.dbo.sp_addlinkedserver @server = N'NODE4', @srvproduct=N'SQL Server'

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'NODE4',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

As you can see from the above steps we configured Always ON for Distribution database using scripts.

We added publisher server using Listener Name instead of Server Name.

once you are done you can go ahead with traditional way of creating publication and subscription there is no change in this process.

After doing this I added rows to my article in publication and the rows got transmitted to subscriber .

Here are few things what I noted before fail over below are the jobs that got created

As per Microsoft Article the jobs should get created with Listener Name but in my case I could see them with server Names (I am not sure if I went Wrong some where)

After I initiated the fail-over the jobs got created on the secondary replica of the distribution Database

If you see the above image all the replication jobs got created at 13:16 instead of 12:25 on Primary replica. There is a new job by name "Monitor and sync replication agent jobs" that helps us in failing over the jobs accordingly.

when we fail-over I got the below error however it ran on it's own after few seconds

In my next post I will try to come up with configuring HA for Distribution, Publisher and Subscriber Databases. You can reach me on +91-9739093905 or +353894316824 for any help.

Mysterious SQL Backups causing Blocking

2020-08-25T20:31:00.000+05:30

Hi Friends,

I am sharing this post as it come across my Environment. From yesterday Morning we found our Backups are getting blocked. We always have Ola scripts in place and the backups are being done to File share.

At the same time we have Veeam File system backups too. I was serving the on-call from Yesterday night and I got calls multiple times and all of them are related to backups getting blocked.

As you can see this is how it looks the user name being NT Authority. By the way we don't have any SQL Servers running with Local system as Service Accounts.

Then from where these are getting triggered and after checking the logs I found something suspicious at the same time

As you can see from the above images it is indicating that something related to Shadow copy and VSSAudit(Volume Shadow Services) and later after checking with Windows Team I came to know that the antivirus software(SentinelOne) is causing this as it is trying to create VSS Snapshot for Databases.

I always wonder why Windows team will not update DBA's team for this kind of activities. Anyways it is good learning lesson for me.

Things to Remember on SQL Server Azure Managed Instance

2020-08-21T19:26:00.001+05:30

Hi Mates,

This is my first article on Azure Managed Instance. Till now I didn't get the opportunity to work on Cloud however by God's grace finally I started working on it.

In this blog I would like to share my few of my Observations

Just like in on-premises if we don't configure properly we will have some negative impacts.

1) Backup compression is not enabled by default

2) Cost threshold for parallelism is 5

3)optimize for Adhoc Workloads is not enabled

4)MaxDop is Zero.

As many of you know we can't stop/start/restart/Pause SQL Server instance and also we can't set max/min Memory settings.

One thing that surprised me a lot is Instant file initialization is not enabled in MI. As I could see the below messages inside my error logs. If you have configured IFI then we will see Zeroing messages only for Log files but not for Data files.

Zeroing C:\WFRoot\DB24C.3\Fabric\work\Applications\Worker.CL_App15\work\data\7fb2af3e-4a45-4eed-9e7a-fcea65576681.mdf from page 33716224 to 33718272 (0x404f000000 to 0x4050000000)

Zeroing completed on C:\WFRoot\DB24C.3\Fabric\work\Applications\Worker.CL_App15\work\data\7fb2af3e-4a45-4eed-9e7a-fcea65576681.mdf (elapsed = 4 ms)

We can check if IFI is enabled or not by running the DMV or searching for the string in error logs like below

When we run sys.dm_server_services on MI it will not give you any output which is Expected. I will try to figure out if there are any DMV'S which runs only in on-premises but not on MI.

Until then Happy Reading.

The Error that we should be cautious of while doing partitioning switch in

2020-08-21T03:46:00.001+05:30

Hi Friends,

This post is important if at all if you worked on Partitioning especially while performing Switch in.

I have a scenario where I need to partition an existing table which has data from 2007.

Below are the high level steps what I did

1) Adding filegroups

2) Adding files to each of the filegroups

3) create temporary staging tables on the respective filegroups

4)Load the data from main table in to staging tables

5) truncate main table

6) create partition function and partition scheme

7) create check constraints on temporary staging tables

8) Disable NON Clustered index and Foreign keys if there are any on the source table

9) Switch in Data from Staging tables to main table

while performing the final step I missed to create check constraint on one of the temporary staging tables and I didn't expect that will cause havoc

If you want you can play with the below demo (Thanks to Kendra Little)

CREATE DATABASE [PartitionThis]

ALTER DATABASE [PartitionThis]

MODIFY FILE ( NAME = N'PartitionThis', SIZE = 256MB , MAXSIZE = 10GB , FILEGROWTH = 512MB );

ALTER DATABASE [PartitionThis]

MODIFY FILE ( NAME = N'PartitionThis_log', SIZE = 128MB , FILEGROWTH = 128MB );

USE PartitionThis;

;WITH Pass0 AS ( SELECT 1 AS C UNION ALL SELECT 1),

Pass1 AS ( SELECT 1 AS C FROM Pass0 AS A , Pass0 AS B),

Pass2 AS ( SELECT 1 AS C FROM Pass1 AS A , Pass1 AS B),

Pass3 AS ( SELECT 1 AS C FROM Pass2 AS A , Pass2 AS B),

Pass4 AS ( SELECT 1 AS C FROM Pass3 AS A , Pass3 AS B),

Pass5 AS ( SELECT 1 AS C FROM Pass4 AS A , Pass4 AS B),

tally AS ( SELECT row_number() OVER ( Order BY C ) AS N FROM Pass5 )

SELECT N

INTO ph.tally

FROM tally

WHERE N <= 100000;

--Create the partition function: dailyPF

DECLARE @StartDay DATE=DATEADD(dd,-3,CAST(SYSDATETIME() AS DATE));

CREATE PARTITION FUNCTION DailyPF (DATETIME2(0))

AS RANGE RIGHT FOR VALUES

(@StartDay, DATEADD(dd,1,@StartDay), DATEADD(dd,2,@StartDay),

DATEADD(dd,3,@StartDay), DATEADD(dd,4,@StartDay) );

--Add filegroups.

--Number of filegroups = 1 + Number of boundary points

--defined in partition function

ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG1

ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG2

ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG3

ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG4

ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG5

ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG6

--Add files to the filegroups

DECLARE @path NVARCHAR(256), @i TINYINT=1, @sql NVARCHAR(4000);

SELECT TOP 1 @path=LEFT(physical_name,LEN(physical_name)-4)

FROM sys.database_files WHERE name='PartitionThis';

WHILE @i <= 6

BEGIN

SET @sql=N'ALTER DATABASE PartitionThis ADD FILE (name=DailyF' + CAST(@i AS NCHAR(1))+',

filename=''' + @path + N'F'+ CAST(@i AS NCHAR(1))+'.ndf' + ''',

size=128MB, filegrowth=256MB) TO FILEGROUP DailyFG'+CAST(@i AS NCHAR(1))

--show the command we're running

RAISERROR (@sql,0,0)

--run it

EXEC sp_executesql @sql;

SET @i+=1;

END

--Create the partition scheme: dailyPS

CREATE PARTITION SCHEME DailyPS

AS PARTITION DailyPF

TO (DailyFG1, DailyFG2, DailyFG3, DailyFG4, DailyFG5, DailyFG6);

This is how the Data looks after we create partitioning with above Scripts.

Now I created temporary tables like below so that I can dump the data from main table to below staging tables

CREATE TABLE OrdersDaily_2 (

OrderDate DATETIME2(0) NOT NULL,

OrderId int IDENTITY NOT NULL,

OrderName nvarchar(256) NOT NULL)

on [DailyFG2]

CREATE TABLE OrdersDaily_3 (

OrderDate DATETIME2(0) NOT NULL,

OrderId int IDENTITY NOT NULL,

OrderName nvarchar(256) NOT NULL)

on [DailyFG3]

CREATE TABLE OrdersDaily_4 (

OrderDate DATETIME2(0) NOT NULL,

OrderId int IDENTITY NOT NULL,

OrderName nvarchar(256) NOT NULL)

on [DailyFG4]

CREATE TABLE OrdersDaily_5 (

OrderDate DATETIME2(0) NOT NULL,

OrderId int IDENTITY NOT NULL,

OrderName nvarchar(256) NOT NULL)

on [DailyFG5]

set identity_insert ordersdaily_2 on

insert into OrdersDaily_2(orderdate,orderid,ordername) select * from OrdersDaily where OrderDate>='2020-08-17' and OrderDate<'2020-08-18'

set identity_insert ordersdaily_2 off

set identity_insert ordersdaily_3 on

insert into OrdersDaily_3(orderdate,orderid,ordername) select * from OrdersDaily where OrderDate>='2020-08-18' and OrderDate<'2020-08-19'

set identity_insert ordersdaily_3 off

set identity_insert ordersdaily_4 on

insert into OrdersDaily_4(orderdate,orderid,ordername) select * from OrdersDaily where OrderDate>='2020-08-19' and OrderDate<'2020-08-20'

set identity_insert ordersdaily_4 off

set identity_insert ordersdaily_5 on

insert into OrdersDaily_5(orderdate,orderid,ordername) select * from OrdersDaily where OrderDate>='2020-08-20' and OrderDate<'2020-08-21'

set identity_insert ordersdaily_5 off

After creating the temporary tables I truncated the data in the original table and we need to populate the data from the above staging tables. As said above if you want to perform this we need to create check constraints on staging tables otherwise it won't allow you to perform switch in.

you can create them using the below script

ALTER TABLE ordersdaily_2 WITH CHECK ADD CONSTRAINT CKordersdaily_2 CHECK (OrderDate>='2020-08-17' and OrderDate<'2020-08-18')

ALTER TABLE ordersdaily_3 WITH CHECK ADD CONSTRAINT CKordersdaily_3 CHECK (OrderDate>='2020-08-18' and OrderDate<'2020-08-19')

ALTER TABLE ordersdaily_4 WITH CHECK ADD CONSTRAINT CKordersdaily_4 CHECK (OrderDate>='2020-08-19' and OrderDate<'2020-08-20')

ALTER TABLE ordersdaily_5 WITH CHECK ADD CONSTRAINT CKordersdaily_5 CHECK (OrderDate>='2020-08-20' and OrderDate<'2020-08-21')

Finally you can perform switch in

ALTER TABLE ordersdaily_2 SWITCH TO OrdersDaily PARTITION 2

ALTER TABLE ordersdaily_3 SWITCH TO OrdersDaily PARTITION 3

ALTER TABLE ordersdaily_4 SWITCH TO OrdersDaily PARTITION 4

ALTER TABLE ordersdaily_5 SWITCH TO OrdersDaily PARTITION 5

To re-produce the error I deleted the last constraint using the below statement and ran the above 4 switch in statements.

alter table ordersdaily_5 drop constraint CKordersdaily_5

As we missed the last constraint it will pop up the below error

Msg 4904, Level 16, State 1, Line 6

ALTER TABLE SWITCH statement failed. The specified partition 2 of target table 'PartitionThis.dbo.OrdersDaily' must be empty.

Based on this error we will definitely think that something went wrong and we will truncate the main table thinking that table must be empty. But if we run truncate on main table by the time you will see there will be no data even on temporary staging tables. It means we lost the data altogether.

Conclusion: When you perform switch in and if you get any errors don't take any violent decisions just check what is the data that is present in your main table and if you check that you will find the data despite of errors.

Restore backup from Linux to windows

2020-08-18T00:42:00.004+05:30

Hi Friends,

This is very simple post and I am writing this as I got this question from one of the SQL Groups.

We all know that we can restore the backup from Windows to SQL Linux but how about vice versa.

Can we restore a backup of the database from Linux to Windows?

Initially I thought because of different file system formats it is not possible but my assumption is completely wrong.

we can very well do it. Below is the version of my Linux(source: oracle virtual box Guest)

I restored it on to my local laptop that is running on Windows 10 with SQL Server 2019

This clearly indicates that there will not be any issues I know this is not a great post but I just wanted to share this to make people aware as I didn't find any documentation.

It's been long time for me to post something on SQL Linux after all my blog started with SQL Clustering on Linux(ubuntu) below is the link in case if needed.

https://www.chiranjeevivamsydba.com/2018/01/sql-server-clustering-on-linuxubuntu.html