Category: T-SQL Tuesday

Thanks to BrentOzar for hosting this month of T-SQL Tuesday. Please find the original post here.

Brent asked us to share the last issue that we resolved. I recently resolved a replication issue. I completed a transactional replication which is set between 4 servers. Let’s say, A, B, C, and D servers. A replicates to B which then replicates to C and then from Server C to Server D. The setup is complicated. On a regular process, Server A needs to import the fresh data on Server B tables that are involved in replication. It is not possible to drop the tables as they are currently involved in replication to B Server which then replicates the data to Server C. To make this possible, publications have to be dropped on Server B and Server C before importing fresh data from Server A to Server B. Once the tables are created on the Server B, then the publications are recreated.

As all these servers are involved and linked through transactional replication on the same tables, and publications are created on each of these servers, it gets complicated to drop and recreate the publications on Server B and Server C. We tried automating the process by creating the drop and recreate publication SQL agent jobs and using triggers to trigger the execution of the jobs one by one.

I know we can use other technologies in SQL Server to accomplish the same goal but the requirements and the limitations from vendors made us to stick with this plan.

So, the setup is complete. Soon after, I saw one of the recreate publication jobs fail due to the replication agent account being locked, and the downstream jobs all failed as they were interlinked to each other. Though the resolution is simple to enable the locked account, it is hard to figure out that this is the reason for the replication being broken. Especially, when these jobs fail across multiple servers.

I enabled the replication agent account and rerun the job to success. Then manually executed each of those failed jobs to succeed on the rest of the Servers. I am currently in the process of figuring out why the account is locked in the first place.

I know it is challenging to maintain the replication but it gets even tougher with complicated replication setups across multiple servers.

This is the last issue that I fixed the last Saturday night at 1am, LOL. Glad the issue is resolved.

Though the setup is tough and things seem complicated, I was able to successfully migrate the servers from older versions to new versions without breaking the replication setup that is required between multiple servers.

I am looking forward to reading the other SQL family posts on this month’s T-SQL Tuesday.

Thank you for reading!

Thank you Reitse for hosting January month of T-SQL Tuesday year 2024. This is my first blog post in the year 2024. Reitse asked us to write about any lessons learned from the old projects. Please find the original invite here.

I had to work on a project years ago where there was a requirement to migrate the old version of the database (SQL 2000) to SQL 2012/2016. One of the requirements was to use the replication to replicate the data over to another server. This is an OLTP database where there were heavy transactions per second. As a part of the planning, I recommended using the Always on Availability groups instead of the replication. My manager at the time (from my previous company) was a person who trusted and supported male DBAs more than female DBAs regardless of their expertise. I had to respect the decision to replicate the data over using the availability groups for read-only reporting purposes. I didn’t even have the chance to test the scenarios, compare the results from the two technologies, and show the results in the test environment.

Once the project is completed and in production. There comes the problems with replication. There were intermittent network failures, issues with the settings in the files generated by the snapshots, and other typical issues with the replication- there was a heavy lag in transferring the data. If you worked on replication before, you surely know fixing replication issues was no joke. We had to later remove the replication for good and replace it with the always-on availability groups. This has not only improved the performance overall but also required less maintenance.

Though the management understood later that they must have taken a different approach, the cost of it was the time and resources spent on the project.

One of the other projects was about the tools we wanted to purchase for data governance. Depending on one tool is not a good case when you are still in the process of deciding which tool to purchase for your needs. We had to spend a lot of time on one tool for the Proof of concept for months only to decide it didn’t serve our needs. This took a lot of DBA and the other team resources for months. I believe understanding the requirements from the company’s perspective should be the first step in the process. Then compare the tools from the wider perspective of whether they can serve all the requirements we are looking for. Filtering this way can give us the list of tools we can try and test to decide further.

These are the two experiences I would like to share in this post. I am looking to learn more on this topic from others as well.

Thanks for reading!

Thank you my dear Friend Kay Sauter for hosting the December month of T-SQL Tuesday. Here is the invitation. I believe this is the perfect way of saying thank you and Goodbye to the year 2023.

Let’s take a moment here. Seriously, stop scrolling for a second. Can we all collectively pat ourselves on the back for a second? I mean, seriously, YOU all deserve a medal or at least a virtual hug for hanging around and being the absolute best bunch of readers of this blog!

Out of all the other wonderful things around the world to watch for, out of all the other endless distractions we have in this social media world today starting with cute little kitten videos all the way to your favorite actor movies, possibilities are endless for the entertainment. Still, you decided to spend your time chilling here reading all my ramblings. Seriously, I am speechless! Thanks for your commitment and patience with me.

Thanks for even taking my emotional side of things. I am an emotional person and a part of me from that side of my personality should have shown up in some of my blog posts. Especially, when I sit in the night and put my thoughts in here. My thoughts are unfiltered. You have braved to see me from that angle as well. I mean, my Drama! Thanks for embracing the unpredictable side of me.

So, THANK YOU!

Thank you for being my best virtual friends, and for spending your time and attention! Thank you for being so patient with me as I compose and do the posts.

You are all my real MVP’s and these little bi(y)tes of dbanuggets wouldn’t be the same without you!

So, my awesome and amazing readers! Keep being yourself! Never ever let anyone dim your light! Cause, you are born to stand out!

Embrace this life, keep being fabulous, be curious, enjoy the little things in life, cry, scream, and laugh the life. There is nothing wrong with it. After all, we just have one life and we want to be authentic. Show the emotions instead of suppressing them. Tell people in your life you love them. Do not worry about making mistakes. There is nothing bad in being wrong. There is nothing wrong with it. Keep being YOU!!

Keep that Smile, it may brighten someone’s day!

Please excuse me if this post is going to be the funny one, yes it is!

I would like to first thank my dear friend Josephine Bush for bringing up this great topic and asking us to write about how the proper job titles and descriptions should be for the job postings.

I surely have a lot of thoughts on this topic. Two years ago, I was applying for Database administrator positions everywhere. If not everywhere, almost everywhere. Linkedin, Indeed, Monster, CareerBuilder, etc. As I was looking at the Job titles and Job descriptions at the time, my blood boiled at the beginning looking at the requirements. More about this soon. I was already stressed about figuring out a way to get a job soon and on top of that stress, unreasonable job descriptions caused me more stress. After a couple of days, that stress turned out to be a stress reliever. Yes, you read that right. In the beginning, I was madly looking at the job description mentioned for a SQL database administrator role mentioning high expertise in the languages – C++, Java, PHP, Python, etc; but later as I was checking on many of these types of job descriptions made me laugh and had helped me filter out the companies that I can ignore applying.

The other funny thing I observed is the description mentions that the company is looking for a senior-level position and mentions the salary as 15 dollars per hour. The description for this kind of job posting also mentions the certifications as needed or preferred.

To take this to another level, I would like to show an example of postings from one of the best companies (Don’t want to point out the company name here) back in 2020 for the position of “Cloud Native Infrastructure Engineer” mentioning the requirement is to have 12+ years of Kubernetes experience when in fact the Kubernetes technology was released in 2014 which was just 6 years. Source

I believe many companies rely on people who do not have much experience with technology to post about these job openings and their descriptions. Due to this, great candidates who can be a great fit for the position would not even consider reading the entire description. This can ruin the reputation of the company.

One more hilarious experience of mine –

My first-ever job was with the title “Database administrator” but as I enter the company, the first day I received a tag with my name, picture, and with the title “Database Developer”

Hope you enjoyed reading this post and my request to anyone looking for a job actively ignore these type of job descriptions!

I am curious to read all other posts on this topic for this month of T-SQL Tuesday!

Thanks for reading!

This month T-SQL Tuesday is hosted by Eric Darling asking us to write about the code that made us feel a way.

I would like to mention the Query Store hints and why I really liked it. If you have a parameter-sensitive query in your stored procedure and you need to use a hint (For example, using RECOMPILE hint in this case) to fix the issue quickly without changing the actual code, using query store hints is the best option. Not only that, but you can also use other useful hints like setting up the MAXDOP, Compat level etc. For the list of supported and unsupported hints, look here.

Remember: This is the best last option to choose when you cannot change the code. It is always best to refactor the stored procedure.

It is very easy to use. You just need to collect two things. The query ID of the stored procedure and the query hint option you would like to use. There is a special stored procedure that you can use to implement this Query hints into your stored procedures.

To find the query_id of your query, run the below code by changing the Query text in the like operator:

/* Find the query ID associated with the query. Source */
SELECT query_sql_text
,q.query_id
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q ON qt.query_text_id = q.query_text_id
WHERE query_sql_text LIKE N’%query text%’
AND query_sql_text NOT LIKE N’%query_store%’;
GO

Use the Query_id to run the below-stored procedure with the hint you would like to use

EXEC sys.sp_query_store_set_hints @query_id= 1, @query_hints = N’OPTION(USE HINT(”RECOMPILE”))’;

Query store can also capture ad-hoc workloads. This can fill up your query store real quick if you have a lot of ad-hoc queries coming from your applications. If you can parameterize these queries, configuring PARAMETERIZATION = FORCED the database can be an option. Look more about Forced parameterization here. If you can’t parameterize those ad-hoc queries, you can set the Optimize for Ad hoc Workloads server option to save the cache memory on the queries that will execute only once. If you do not want to capture this kind of queries in the Query Store, set QUERY_CAPTURE_MODE to AUTO.

Remember:

1. If you enable the forced parameterization and use the query hint RECOMPILE at the same time, the query engine will ignore that query hint and proceed with using any other hints used. If you are using the Azure SQL database, you will see the error with code 12461 when the RECOMPILE query hint will be ignored. Source
2. Query Store will only store the latest hints that are active. It will not store the history of the hints that were active once. To capture that information, you can use extended events. I have written a blog post here on exactly how you can set this up so you can get the history of the hints.

I am looking forward to reading other SQL family member posts on this month’s T-SQL Tuesday post hosted by Erik Darling!

Thanks for reading!

Welcome back to reading another T-SQL Tuesday for June month. This month’s invite is from my dear friend Gethyn Ellis asking us to write about the best career advice we received. Here is the invite.

I would like to list some of the best advice I received in my life which helped me rethink life. They give me the strength to see past the pain and help me move forward in life.

Embrace your Style but wait, What is your Style?

This is the best career advice that I received from one of the best people in the community. I can’t thank them enough for this advice. This is about two years ago when I started my blogging and speaking career. As I was fairly new to speaking, I struggled to find out what my style was. Style in the sense of bringing your true authentic self onto the stage. Fear of being judged and accepted by the community stopped me from being who I am at the time. I didn’t even realize this for so long. I thought everything was normal but something inside me was always telling me that I was not good enough. Something needs to change but I didn’t know what it was until some special people from the community told me that I have my own style and it is just that I need to figure that out.

I was confused at the beginning about what this style even meant but as I tried to understand more, I finally realized what it actually mean. Your style is nothing but showing who you actually are, truly and authentically. Everyone will have their own style. In every little thing you do, you will have your own mark on it. The way you do things is your style. Are you doing your best but still feel you lack something? No matter what, the way you do things and the way you present can give a unique and add your perspective to it which in itself shows the beauty in the work you do, which is your style that no one else can do other than yourself. The moment I realized this, I started embracing the way I look and do the things in life. I added my perspective to the things I present which are unique and there will be people who love this or may not love this but you will have your own mark which will attract the right audiences to you. There are always people who wanted to learn from your perspective.

This applies to many things in my life including my blog posts, my presentations, the way I speak at conferences, and the way I act and think in life. I accepted who I am and started embracing the authenticity in me. I started speaking from the bottom of my heart. I was scared to do this before in fear of judgment but as I started speaking for what I felt, people actually started liking me even more because they saw the truth in what I speak. By choosing to live this way, we can make genuine connections in life and value the ones who stick around valuing who we are.

This is a great lesson I have learned in my life.

2. If you are troubled by external circumstances, it is not the circumstances that trouble you, but your own perception of them and they are within your power to change at any time. ~ Marcus Aurelius

In life, most of the things that happen to us are outside of our control. The problem gets bigger and bigger when you try to control what you can’t control. If we can only differentiate every event that happens to us into two categories, what is in our control and what is out of our control, most of the problems will be solved.

Focus on only what is in your control and leave the rest. Most of the time, what’s in your control is how you react to the events that happened to you. That’s the only thing that you can control. This is one of the Stoic philosophy principles that I learned recently.

3. Going within self is the only solution

What does this even mean, right? Most answers that you are searching for from outside of yourself are found within yourself. When you fail at something or you are facing a situation in life, for example, you want to resign from that job you are unhappy about but something inside you is stopping you from taking action – Going within yourself is the only solution to learn about yourself, what actually is stopping you and what can be done to remove that fear. Asking for help is a great idea when you are facing a situation in life but we need to first help and stand up for ourselves by examining ourselves what is the actual problem. Other people can suggest you but it is up to you to decide what is right for you.

I am not a philosopher to show off as if I identified something in life which was not there before. These are all the things I have been learning from the greatest philosophers in the history of mankind. I just thought of sharing them with you all as I felt it was the right time and topic for this month’s T-SQL Tuesday.

These all things I explained here apply to every part of life, professional and personal.

I would like to thank Gethyn for bringing this topic to June month of T-SQL Tuesday. I believe that I shared some of my learnings that really helped me in every aspect of my life.

Hope you liked it and thanks for reading!

By the way, What is New Year Resolution?

I am overwhelmed with gratitude by looking at the number of responses we received for my February month of T-SQL Tuesday invitation. Surely, I see so much interest and curiosity for the newly available features in Azure SQL and SQL Server 2022. Not to mention, for the new year resolutions too 😉

Welcome to all the new T-SQL Tuesday Bloggers! You are surely expanding the blogging party!

I had a great honor on hosting February month of T-SQL Tuesday 2023! Please check here for the original invite.

I had great responses to this invitation and some of them were the first timers to write the post for T-SQL Tuesday. Please see for the summary on the posts written by SQL family.

Chris Wood

Chris mentioned about how executing the view having nested views in it will impact the performance but how the new CE has improved the performance of the query a bit but with the higher estimated number of rows in SQL Server 2019 CU16. Chris was curious to know if the SQL Server 2022 latest features DOP and CE feedback options can fix the nested view performance as these features eventually add hints to the execution plan for later executions. Read the complete post by Chris Wood here.

Rob Farley

Rob explains why ‘IS [NOT] DISTINCT FROM’ is one of his favorite functionality as it solves the NULL issue. Rob says it is better to type ‘IS [NOT] DISTINCT FROM’ than dealing with the ISNULL() with amazing examples and how the indexes are used with ‘IS [NOT] DISTINCT FROM’ in the queries and for better performance of the queries. Check for the complete post here.

Rob Litjens

Rob’s favorite Azure features are Purview, Synapse integration, connecting on-prem servers to the managed instance through distributed Availability Groups. Rob mentions with SQL Server 2022 creating AG in managed instance and how you can fail back to on-prem.

Regarding the resolutions, Rob wanted to be involved more into speaking engagements and he is looking for the organizers to contact him if they are looking for ay of these topics related to platform DBA (like Group Managed Service Accounts, Automated Deployments, Desired State, Ansible, Defender for SQL, etc.). Find all of his new year fun resolutions here.

Brian Bønk

Brian describes about the Optimized Locking feature for Azure SQL Database. He explains the advantage of how only the actual rows that are being used are locked instead of many exclusive locks placed during the transactions. Brian explains the benefits with an example helping the data warehouse scenario and where this feature is currently available based on the locations.

Brain says his new resolutions for this year includes more physical exercises, meditation and being up to date with the Data platform news. Great resolutions, Brian!

Don’t forget to check some of his favorite apps he uses as the reminder for meditation Headspace and Feedly to follow the latest RSS feeds from the Microsoft Data platform. Check for the complete post here.

Magdalena Bronowska

Magda favorite new/updated T-SQL functions are DATETRUNC(), LEAST() & GREATEST(), STRING_SPLIT() with examples. Please see the complete post here with examples.

Magda loves spontaneity than planning for resolutions. She have few resolutions for this year though. Magda resolutions are to take Microsoft Certifications and Workout Wednesday, which is new to me. Read more about it here in her post.

Kay Sauter

Kay favorite feature is STRING_SPLIT() function and the second feature he liked about is the failover an on prem SQL Server 2022 into Azure Managed Instance.

Kay’s resolutions is to blog more and to present more at the conferences in person. Kay is looking forward to the second edition of the DATA BASH. Please look at the full post of Kay here.

Kevin Chant

Kevin explains and did a lot of blogging about the feature Azure Synapse Link for SQL Server 2022 already. He did the file test inserting one hundred million rows while Azure Synapse link for SQL Server 2022 is running. He have explained in this post about the file test. He also provided the GitHub repo for the same.

Kevin resolutions this year is all about certifications. He wanted to take more certifications, renew the existing ones and most importantly, this community super hero wanted to educate people about certifications through his blog posts. Look for the complete blog post here. Keep going, Kevin!

Deborah Melkin

Deborah explains about how the DOP configuration settings are important and how it is not always possible to change these settings due to many limitations like client handling the hardware. In SQL Server 2022, the engine will automatically look for the efficient ways to use the MAXDOP and stabilize the DOP for queries as needed. You can read Deborah full post here.

Deborah mentions that she is looking forward to getting some projects with all of us. This is her year resolution. I am really looking forward to this Deborah.

Reitse Eskens

Reitse favorite feature is Synapse Link and how the On-prem SQL Server 2022 can connect to Azure synapse and how the data changes can automatically be captured by synapse and propagate to the cloud data stores in Azure.

Reitse handles the notifications through parking page. He also mentioned the importance of mental health and how the community members can help each other and he advises to reach out to the employer for any help needed. He suggests to attend as many mental health events as possible. You can find the complete post of Reitse here.

Chris Johnson

Chris mentioned that the last post he have written was TSQLTuesday #138 and my invite has bought an interest in writing the post again. Thanks for taking your time Chris in writing this post. Chris is looking forward to learn about the new features in SQL Server 2022 in the coming months and is really excited to learn more about the latest features.

I am very much impressed on how much Chris is willing to work on his personal development. You can read the list of the things Chris wants to accomplish in this year. Great list Chris. Good luck on your goals. Don’t miss reading the Chris working list for this year here.

Jiri Dolezalek

This is the first ever T-SQL Tuesday post from Jiri. Welcome to the party, Jiri. Jiri wrote about my favorite feature, Query Store and looking forward to know how the Parameter Sensitive Plan optimization works.

Jiri believes in the consistency in doing any tasks and doesn’t like resolutions. Jiri says if you wanted to do anything, there is no specific time and place to get started. It can be any day and any time to make the necessary change and improve. Find the full post of Jiri here.

Andy Yun

Andy had attended the private SQL Server 2022 workshop taught by Bob Ward few weeks ago and as Bob Ward presented the last slide showing Purvi’s list, Andy was impressed with the feature added- Instant File Initialization (IFI) for Transaction Logs. Know about this feature in his full post here. Thanks Andy for sharing your learnings.

Chad Callihan

Chad favorite feature is optimized plan forcing by Query Store feature. Chad mentions that this feature optimized plan forcing is on by default for any databases created on SQL Server 2022 and shows how to configure it using T-SQL.

Regarding the resolutions, Chad wanted to more focus on reading the technical books this year. For anyone who have hard time getting up early in the morning, Chad recommends Sunrise Alarm Clock. Great recommendation Chad. This will help people like me who have hard time waking up early in the mornings.

I learned something new today as I read all of these amazing blog posts from SQL Family. I learned that most of them doesn’t embrace the idea of so called “new year resolutions” but they believe setting the goal and working for the goal consistently is the best way to look at it. Doesn’t matter what time you set a goal and when you get it started, working for it is important.

Hope you enjoyed reading the learnings from the SQL family members about their favorite features in Azure SQL and SQL Server 2022. Also, the new year resolution ideas!

Thanks for reading!

This below post is written and authored by my dear friend Chris Wood. I am very happy to post his writings here on my blog. Credits of this post all goes to Chris Wood. I am just publishing it here.

Thanks to Deepthi Goguri for the idea behind this edition of T-SQL Tuesday. The official title is “What’s your new favorite feature”. 

When I saw the topic, I just knew I needed to tell others about a situation I had experienced. I don’t blog and I’m now retired but I would like others to understand my experience. 

In my last gig I experienced several situations that used nested views. This approach may make some situations easier as you get to call one piece of already written code rather possibly copy in bad code. On the downside is performance. We were running SQL2019 at the CU16 security fix build with the databases at 2016 DB compatibility level and the Legacy CE set to ON. When a query was executed that went down 3 or 4 levels of nested views it would take a long time to actually create the execution plan and start returning rows. If I changed to the newest CE rows get returned much quicker but the estimated number of rows to be returned is higher with a small performance improvement. 

So my most looked for feature would actually be 2 new features. SQL 2022 brings both DOP and CE feedback options that can eventually add hints to the execution plan for later executions. I had seen Grant Fritchey mentioned Cardinality Feedback recently Monitor Cardinality Feedback in SQL Server 2022 – Grant Fritchey (scarydba.com) 

Both of these are controlled by running at the compat level of 160 and by using ALTER DATABASE SCOPED CONFIGURATION options. The options are SET DOP_FEEDBACK = ON and SET CE_FEEDBACK = ON. The CE feedback can also be affected by the query having a coded hint or a query store hint or the execution plan is forced.  

I am reading this from Grant’s Sixth Edition of his SQL Server 2022 Query Performance Tuning and checking against Databases – SQL Server | Microsoft Learn 

As I mentioned earlier I am now retired, this happened just after SQL 2022 RTM was released so I have no idea what could happen with the nested views. 

Chris

Happy new year to everyone. This year January T-SQL Tuesday is hosted by Raul Gonzalez asking us to write about the scenarios which are not necessarily the best practices but scenarios where we had to implement the practices which are supposed to be avoided. Thanks to Raul for bringing this topic as I have some interesting scenario related to replication where I had to replicate the data from SQL Server 2012 to SQL Server 2000. I know, its the old server SQL 2000 which is unsupported.

According to the best practices from Microsoft, it is recommended that the transactional replication can be compatible and supported in going two versions higher including the same version (SQL Server 2012/2014/2016) or two versions lower (SQL Server 2008 R2/2008).

The scenario that I faced was little challenging. We had SQL Server 2012 production server replicating data to a Server 2000 which is used for reporting purposes. Subscriber SQL Server 2000 used by the reporting team were not ready to upgrade the Server as they need to rewrite their entire application as it was using vb6 code. They need a strategy where the data can still be replicated without upgrading the Server.

As I researched, I found that it is not compatible version but planned to test the replication to see if somehow it works. I tested the replication between SQL Server 2012 as a publisher and SQL Server 2000 as subscriber. I was able to setup the transactional replication between the servers for the database but found during the initial initialization snapshot, the ANSI_PADDING setting in the snapshot generated .sch files caused the issue while the distribution job runs. The setting was OFF for the .sch files which I need to turn back on. Please see the below one of the errors I have faced in my situation, took the error reference from here.

“SELECT failed because the following SET options have incorrect settings: ‘ANSI_PADDING’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. (Source: MSSQLServer, Error number: 1934)
Get help: http://help/1934″

It is easy to fix the .sch file manually if it is just one file that needs to be changed for the setting ANSI_PADDING from OFF to ON but there are many .sch files with the same setting “OFF”. The most tedious part is that the error only shows one .sch file at a time. I need to fix the .sch file digging into the details of the error and then run the distribution job again to see the next error for the .sch file.  I found a very handy PowerShell script answered by Asaf Mohammad in Microsoft forum where you can place this script as the last step in the snapshot job. Once the snapshot of the database is created and the .sch files are generated, this script will search for all the .sch files with ANSI_PADDING setting and edit the files having the settings from OFF to ON. This process happens automatically when ever the new snapshot is generated or the reinitialization of the subscription happens as it forces to create the new snapshot of the database.

Once I fixed this error, I was able to replicate multiple databases from SQL Server 2012 to SQL Server 2000 implementing the job step for each database snapshot job. I was able to solve the problem by using the PowerShell script. Since then, I did not see any specific file errors related to this error.

This was one of my real time experiences where I had to walk against the recommended best practices and found success in setting up the replication with some issues which I was able to resolve. I am looking forward to reading the experiences of other SQL family members in seeing successful results in such scenarios.

Thanks for reading!