T-SQL Tuesday #159 – Wrap Up

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!

T-SQL Tuesday #159 – What’s Your New Favorite Feature? by Chris Wood

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. 


Quick Track: Beginner’s Guide to Azure SQL- Azure Defender and Threat Protection

Source: Pexels

Azure Defender for SQL, once you enable it will alert you for any SQL injection attacks, brute force attacks or any breached identities trying to access the data of your database. It also provides the vulnerability assessments. Vulnerability assessments give you alerts about the configurations of your database. If your database configuration is not following the standards of Azure, you will receive the alerts in the vulnerability assessment report.

You can enable the Azure Defender at the subscription level or at the Server level or at the resource level as well. Under the recommendations in the security center in the Azure portal, check for the Remediate security configuration. This will show if the Azure defender is configured properly.

Azure defender for SQL consists of two features – Advanced Threat Protection and Vulnerability Assessment.

Advanced Threat Protection

Threat protection will send the alerts if there is any malicious activity going on against your database. You will receive alerts if there are any SQL injection attacks, any suspicious login attempts from new locations.

Vulnerability Assessment

Vulnerability assessment will check for the security misconfigurations based on the Azure standards. It will provide a baseline based on the standards which you can later accept and add these baselines based upon your environment. It will also provide the actionable steps to take and remediate the alert.

Azure defender supports Azure environments and also hybrid environments. For the complete list of where the Azure defender is supported, please check the Microsoft article here.

You can view all the defender collected information in the centralized view at Azure Security Center.

Vulnerability assessment requires the storage account for the PaaS service. This can be setup at the logical SQL Server level. For the IaaS service, you don’t have to specify the storage account separately.

Once you turn on the Azure Defender, we need to give sometime to collect the information and scan the system for any vulnerabilities. At the server level, Go to the Microsoft Defender for Cloud. You can also open at the resource level as well.

To view the details, click on the additional recommendations.

You can also see the recommendations from the resource level as well. Click on the Microsoft defender for cloud from the database level.

Scroll down to see the details

Let’s see the example of the first vulnerability assessment finding. The security check shows Database owners are as expected. Click on the finding to see more details.

You can either look into the details and add the recommendations to the baseline or ignore. This is completely based on your needs and your current environment.

Azure Sentinel

Azure sentinel will help in identifying the security incidents that we need to investigate. You need to create a log analytics workspace to add to Azure sentinel. Multiple data connectors can be used to connect and collect the information from these different connectors and analyze the data for the security incidents.

You can use different dashboards to view the collected data and alerts. For example, you can use the audit logs dashboard to view more details on the alerts. You can setup the alerts for the events. You can also create the playbooks to automate the response to specific alerts using the logic apps.

Learn how to setup the Azure Sentinel here.

In the next blog post, we will learn about the Auditing in Azure SQL.


  1. Overview of Microsoft Defender for Azure SQL
  2. Azure Defender for SQL

Thanks for reading!

Quick Track: Beginner’s Guide to Azure SQL- Data Protection

Source: Pexels

Data needs to be protected no matter where it lives, On-prem or in Azure. Data can be protected by using the encryption that Azure provides. What are the types of encryption we have in Azure?

Encryption in Transit: The data needs to be protected as it moves all the way from the database to the client application and vice-versa. The encryption is always by default enforced by TLS (Transport Layer Security). Making sure your application supports the TLS version you choose on your server is important. If for example, you chose the TLS version 1.2 on your server and your client application only supports the 1.2 TLS version, your application cannot connect to the server.

As a best practice from Microsoft, client applications should have the encryption enabled on their connection string and not trust the Server certificate.

Encryption-at-rest: Data at rest on the disks- which includes the data files, log files and backup files. These are encrypted with the technology called Transparent Data Encryption.

This option is enabled by default as well. As the data is moved in and out of the drives, the data is being protected through the transparent data encryption. By default, Microsoft is going to manage the keys known as Microsoft-managed keys but also you can choose bring your own keys (BYOK). In the default option Microsoft-managed keys, the responsibility of the key generation, key rotation and the management of these keys are taken care by the Microsoft. All these keys need to be stored in the Key vault which is a Microsoft product. In the second option BYOK, the customer is responsible for the management of the keys including the key generation, key access to the users and key rotation. The third option we have is Hold your own key (HYOK) which is a IaaS offering only where the key generation including the key management and storing the keys is totally taken care by the customer.

Double layer of encryption is where you can chose the TDE along with the infrastructure encryption (volume encryption). This is an additional layer of security for your data on disk. The second layer Infrastructure encryption is optional choice.

In the case of customer managed key, the server identity should have the access to the key vault. So, when the server wants to encrypt and decrypt the data on the drive, it will use this key. The first step to create the customer managed key is to create the Key Vault if you do not already have one.

Once you created the Key vault, choose the customer managed key. To select a key, first we need to create a key that is saved in the key vault. Here I have created two keys.

Then go to the access policies on the Key vault and give the server (Securitydemo) enough permissions to access the key. That requires Wrap and unwrap permissions.

Now go to the transparent data encryption and choose the key by clicking on the Select a key and then click on change key.

Choose the key you have created and click on select

Once you select the key, click on Save on the transparent data encryption page.

Encryption-in-use: It encrypts the data during the query processing and the technology used is always encrypted. This is the client side encryption technology. Cryptographic keys that are used to encrypt and decrypt the data are never shared with anyone. The SQL client driver in between the client application and the database will serve as the mediator to encrypt and decrypt the data as the data moves in and out of the database. Initially only the equality searches were only supported but to add more functionality in supporting the range queries, pattern matching, sorting, indexing and more operations, a new technology is being introduced in SQL Server 2019 known as Secure Enclaves.

Secure Enclave is a part of memory in the SQL Server that no one can access and this part of memory is used to process the computational operations involving the sensitive encrypted column. In this part of the memory, the secure enclave will have the data which is decrypted safely and queries can be processed here.

How do we know that the Secure enclave is secure to process the sensitive data? There is a service known as Microsoft Azure attestation which will check if the secure enclave is safe and process the queries. By using the Secure Enclaves, even the windows administrators or DBA’s cannot access the sensitive data on always encrypted columns.

Dynamic Data Masking

Dynamic Data masking is a protection of the data from the application users who should not have access to specific information. This is not the encryption where the files are encrypted.

This is limiting the access to the data where the data is masked on the fly when the data is moved from the database to the application. As the data reaches to the application, only the needed information needs to be shown to the application users. You can use the custom built functions or create the custom functions to mask the data.


Ledger is a technology which uses blockchain technology to protect the data tampering from the privileged users who have access to the sensitive data. This involves the ledger tables built in as you enable this feature on the database.

Updatable ledger tables allow to capture the data of updates and deletes. The updated rows will be stored in the history tables. The database digests using the block chain technology is saved in the trusted Azure storage where database digests are used to verify the database whether the data in it is being tampered.

Because the Ledger captures all the changes happening to the data in the database and if the tampering happens through application, these changes can be also be identified through the ledger views. This is one of the great features in Azure in protecting the data.

This blog post covered multiple data protection technologies available in Azure. In upcoming blog post, we will learn about the next security layer Threat prevention and detection.

Thanks for reading!


Azure SQL Security: Data Protection

T-SQL Tuesday #159 Invitation – What’s Your New Favorite Feature?

T-SQL Tuesday with the famous Hashtag on twitter as #tsql2sday is all about sharing your experiences on a specific topic requested through invitation from one of the SQL family members every month of the year, encouraging SQL bloggers every month to participate in this blog post party.

Readers do enjoy reading different perspectives and experiences from SQL family members and learning the quick bits of knowledge. I encourage everyone reading this post to participate in this T-SQL Tuesday Party!

This month, I am inviting everyone to blog about two topics:

  1. Blog about your new favorite feature in SQL Server 2022 or in Azure. Why is it your favorite feature and what are your experiences and learnings from exploring this feature? If you have not explored these new features yet, No worries! Blog about the features you feel interested in exploring.
  2. New year, New Resolutions. What are your new year resolutions and how do you keep the discipline doing it day after day? Here are some examples: new hobby, plan to spend more time doing physical activity, wanted to read list of books (Please mention the names so it may also inspire others to read those books), journaling or any other resolutions you plan for this year.

Here are my answers to above questions:

  1. I am looking forward to learn about my favorite feature Query Store and its advancements in the SQL Server 2022. Query Store feature now supports the read only replicas from availability groups. The other advancement in Query Store is Query Store hints. I have written a blog post about it here. The other new feature is the parameter sensitive plan optimization where multiple plans are stores in plan cache for a single stored procedure reducing the parameter sniffing problems.
  2. This year, my resolution is to include exercise to my daily routine and reading David Goggin’s book all over again “Can’t Hurt me” before I begin to read his second book “Never finished”. It is getting harder to keep the exercise discipline. I had my gaps but I know I will get into the track again. I believe it is all about doing your best when you feel the worst. I am looking forward to listen to your resolutions and your discipline in following them day in and day out.

If you are looking for the latest features in SQL Server 2022, follow this series of videos by Bob Ward and Anna Hoffman explaining the new capabilities and features for SQL Server 2022. For new features in Azure, please check Azure SQL updates here and general overall Azure updates here.

Some of the Rules as you participate in the T-SQL Tuesday:

  • Your post must be published between 00:00:00 UTC and 23:59:59 UTC on Tuesday, February 14th. Remember to only publish on February 14th for February month of T-SQL Tuesday.
  • Include the T-SQL Tuesday logo at the top of your post and link your post back to this blog post. You can do this as a comment on this post.
  • If you’re on Twitter, tweet your post using the #tsql2sday.

Feel free to share as much as you can. I am looking forward to reading all your learnings and interests.

Thanks for reading!

Quick Track: Beginner’s Guide to Azure SQL- Authentication and Access Management

Picture by George Becker

Authentication and Authorization are the two things that we need to get familiar with when we learn about the Access management.

Authentication is proving that we are the same user who we say we are. Below are the authentication methods available in Azure.

SQL Authentication: Azure SQL Database and for the Managed instance (MI) supports SQL authentication. This is just like any regular SQL Authentication account with username and the password. When you create the server, you will need to create the SQL authentication account which will become the server admin that will have database owner on all the databases. This account can create other SQL authentication accounts on the server as well.

Azure Active Directory: There is a centralized location for the identities and permissions to database users to be stored for the Azure active directory authentication. As the password is only stored at one location, it becomes easier for the administrators to manage the identities and for maintaining the passwords. This authentication is supported for Azure SQL database, Managed instance (MI) and Azure synapse analytics. The Active directory admin will become the server admin where to create other AD logins, only the Active directory admin can create the other AD accounts having permissions to Azure SQL database. Multi factor authentication option is available for this option. Authentication app can be used as the multi factor authentication where it provides phone call/text message/authentication app to confirm the identity. This authentication type will use the contained database users to connect to the database to access the database objects.

Active directory will support the users with the login id and password, Integrated (single sign-on) authentication used when there is a trust built between the Active directory and the Azure AD using windows authentication. Universal/interactive with multi factor authentication is where the password is generated dynamically. There is a token authentication where a token is generated from Azure AD and as we pass this token to the SQL. Source: here.

One of the important point to remember is, the Azure active directory admin has to create the Azure active directory login. SQL authentication logins cannot be created by Azure AD logins. The other type of users that the Azure AD supports are individual user accounts, group accounts, service principles.

To create the Azure AD account, you can create the account by running on virtual master database. please see sample code to create Azure AD login here.

Lets learn a bit about the differences in the logins and users in between the managed instance and Azure SQL database

Managed Instance: You can create the Azure AD server admin along with the SQL server admin. You can create the SQL logins even when you only enable Azure AD only authentication but you cant connect until you enable the SQL authentication. You can create the SQL or the Azure AD logins, Database users and database contained users. You can also create the Server trust group for the distribution transaction scenarios between the two instances.

Azure SQL Database: You can create the Azure AD Server admin along with SQL logins. Additionally you can have roles like loginmanager and dbmanager for limited server admins. You can create the users associated with the SQL logins, create contained users including Azure AD. To create these contained users, the user must be login using the Azure AD server admin.

There are two configuration options for the authentication:

Cloud only option- where you can create the AD azure accounts with out having them connected to the on-prem servers or you can federate services with single sign-in option, Azure AD storing password as a Hash and Azure SQL passthrough with single sign-on is where the password stored in on-prem.

Azure SQL Authentication supports Azure AD authentication or SQL authentication or Azure AD only authentication (disables the SQL authentication automatically).

Azure Role-Based Access control (RBAC) is what can and cannot be done as a user. We have built in roles in azure where we can assign access to the users and groups permissions all the way from the subscription to the individual resource groups and resources. These permissions can be applied to each level of the hierarchy of resources.

There are three RBAC built in roles that we need to remember:

Owner: Owner can do everything and assign other users as well.

Contributor: Can only modify but cant assign other userd

Reader: Can only read the assigned resource

Custom roles can also be created. Once these roles are created, you can assign the users to these groups.

For the SQL permissions, you can use the built in roles or the custom roles making sure you give the least privileges needed. To provide the least privileges, for the azure SQL database the AD user will be assigned to the database user which will have permissions to the database role connecting to the database. With the Azure SQL managed instance, the login at the server level will be associated and connected to the database user which is assigned to the database roles.

The other important thing to remember is that when the owner of the tables and the views objects are the same, we can only grant the user permissions to access the views instead of granting the permissions to tables for security reasons but the user can now access the tables through the views without having to have the access to the tables. This is called Ownership chaining.

Row level security: Securing the data by only providing access to the rows that the users are allowed to see. This comes under the least privilege access as well.

In order to implement the row level security, we need to create the SQL object known as security policy which is assigned to the table where it will filter the rows and provide only the information that is allowed to share.

I did not show any code examples here in this post because we already have several resources in place showing the examples of how to create the logins and users. Please check here and here

In the coming blog post, I will be writing about threat protection.

Thanks for reading!


Azure SQL Security: Understanding Access and Authentication (Ep. 2) | Data Exposed

Quick Track: Beginner’s Guide to Azure SQL- Network Security Concepts

Let’s learn a bit about Security in this post.

Securing something you built is as important as creating it. Any resource that you create needs to have in depth layers of protection to save it from the bad eyes.

Let’s take the analogy of the Bank systems where you save your money and other valuables. Starting from the parking lot of the bank till the door of the bank vault where the actual money is stored, there is multi layer of security. Cameras, digital locks, padlocks and more locks to reach the money. This is in depth security system. Breaking any layer can only result in more effort to crack an another layer which is even more difficult.

Same goes with the Data. No matter you are on-prem or in the cloud, Data needs to be protected and there are no exceptions.

Securing data in Azure is an important part and there are different security layers available in Azure. Below diagram shows you the different layers of Security we have in Azure to reach the customer data.

In this post, let’s focus on the Network security.

Source: https://docs.microsoft.com/en-us/azure/azure-sql/database/security-overview?view=azuresql

Network Security

The below table will give an idea about different Network security options available and the differences among them.

Allow Access to Azure ServicesFirewall rulesVirtual network rulesPrivate link
Least secureSecure with firewallPrivate IP address of the Azure VMMost secure
Anything in Azure can connectServices with added Firewall rules can connectAny resource in that VN can access Azure SQLBlock all public access
Outside of Azure cannot connectAllow on-prem connectionsApply virtual network technologies to connect on-premPrivate IP address within a specific virtual network
Public IP addressPublic IP addressPrivate IP address within the VNPrivate IP address
DNS hierarchy is publicDNS hierarchy is public (region, cr)DNS hierarchy is still public (region, cr)DNS hierarchy shows private endpoint

In any specific region you have hosting the Azure SQL Databases, your client can connect to Azure SQL database using the Gateways having the public ip addresses. Gateways are the intermediate layer in between the Azure SQL database and the client. Client can connect to Azure SQL database through these gateways in two ways.

Proxy: We can understand this by simple analogy. You want to visit your friend’s apartment frequently but at the security gate, the watch man asks you several questions where you need to prove your authenticity to enter into your friend’s home every time you want to visit him. That is the proxy mode. Here, the DNS will resolve from one of the multiple gateways through port 1433. Each time the client want to connect, the gateway will act as a proxy in between to connect to the database. Proxy mode is the default mode and if you are trying to connect from outside Azure.

Redirection: You want to visit your friend’s apartment frequently and this time, you will receive additional key to the apartment at the entrance as you prove your authentication. Once you receive the key, you can directly go to the apartment with out any intermediate layer of security check at the gate. In the redirection mode, a redirect token is assigned when the client connects to the gateway and all the connections made to the database next doesn’t have to pass through the gateway. The redirect token contains the ip address of the database and the port in between the range 11000 to 11999. Redirection mode is default if you are trying to connect to the database within azure.

These gateways are known as data slices when they are logically grouped together. These data slices are used for the load balancing.

Let’s learn about each of the network security layer:

1. Allow access to Azure Services: Anything in azure can connect to your database if you enable this option. Any azure resource from any subscription and from any region. On-prem Servers cannot connect using this option. This option is a kind of the firewall rules where the start and end ip addresses were all zeros.

2. Firewall rules: These are the firewall rules that you create like any other on-prem Servers. By adding the client ip addresses here in the firewall rules, the client can connect to the Azure SQL database. On-prem servers can connect to Azure by using this option.

3. Virtual network rules: Clients from the specific virtual network subnets can connect to the Azure SQL through the private IP address.

4. Private Link: Private link is creating a private ip address from the virtual network which can connect to your database privately. Internet will be disconnected here. To create a private endpoint, we need to know what resource type you are creating the private endpoint for. It can be Azure SQL Database or the storage account, then on which virtual network and subnet we are going to create this endpoint.

Here is the youtube link you can follow to create a Private link for your database.

In the next article, we will be learning about the next security later, Access Management.

Thanks for reading!

T-SQL Tuesday #158, That One Time Implementation!

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!

Reflecting Back on Year 2022: My Highs and Lows

Reflecting back at 2022, it was a great year with challenges but had learned many things. This year 2022 has bought some significant changes to my life as a person. This blog post is a quick overview of my year 2022.


I started my speaking journey back in 2020 with my debut session for New Stars of Data. Since then, I continued speaking at different events, both local and international events. I continued my speaking in year 2022 as well but most of my presentations were virtual. I was honored to be selected for the PASS Summit 2022 and decided to go in person to give my presentation for the first time ever.

To be very sincere, I was scared and worried of how I would be presenting in person for the first time at that big conference but I am blessed to have wonderful attendees who patiently listened to me during my talk. The first ever presentation I gave in person was on the topic “Women in Technology: How to become an Ally” where I decided to be vulnerable in sharing my story starting from my primary school days. Special thanks to Rie Merritt for reviewing my session and for giving your valuable feedback. Never in my dream I thought my story would connect to audience so well that it would made them shed the tears throughout the session. First few minutes were nerve-wracking and could easily felt in my voice but as time pass by, I felt more and more comfortable in sharing the story. In addition to that, some of my friends, mentors and well wishers attended the session to support me during my presentation. Thanks to Chris Wood, Jeff lannucci, Alpa Buddhabhatti , Kevin Chant and Martha Osorio for attending my session and supporting me with your presence. Your presence had a positive impact on delivering my presentation successfully. Thank you so much.

Me presenting at “Women in Technology: Becoming an Ally” Session at PASS Summit 2022

I received a very good feedback from audience for this session. Later, I presented a technical session along with my dear friend Surbhi Pokharna on the topic “Performance Monitoring, Tuning and Scaling Azure SQL Workloads” which was successful as well. We received great feedback on this session too. I also participated in the panel discussion for Diversity, Equity and Inclusion along with my friend Jean Joseph. I also gave a pre-recorded lightening talk presentation on “10 minutes in to SQL Notebooks”.

For the year 2023, I am honored to be selected as one of the speakers for SQLBits 2023. I will be presenting one technical “A to Z Azure SQL Security Concepts” and a non-technical session “Can an Introvert become an International Speaker? If so, How?” with my dear friend Alpa Buddhabhatti. I am going to present both the sessions virtually.

I will continue to speak on the technical and non-technical sessions in the coming year 2023.

Microsoft MVP Award

I have been awarded the Microsoft Most Valuable Professional Award on October 1, 2022 by Microsoft. It is a great honor to be recognized as an MVP. This is a prestigious Award from Microsoft. If you are not familiar with the details of the award, please look here.

Redgate 100

Really honored to be recognized in this year’s Redgate 100 top online influencers in ones to watch category for creating impact in the database community. I am really honored to be listed among other famous celebrities from SQL Family.

PASS Summit 2022 Committee member

Honored to be on the Committee list for PASS Summit 2022 in improving the Diversity, Equity and Inclusion at the Summit. It was a real great experience in working with other committee members and with Redgate as they continuously take actions and make an effort to improve the DEI at the Summit.


From the past year, it has been a huge honor in mentoring other women coming from the underrepresented countries. It helps them in being vulnerable, comfortable in sharing their story as I come from the same background as well. Sharing and moving ahead in the environments where you can succeed but also support and help other woman thrive in the same environments is the most beautiful thing I can ever imagine. Mentoring is always a two way street. I learn from my Mentees and it has been a great learning from them. I would like to continue to mentor in the coming years as well.


I have been blogging about the Query Store and on Azure fundamentals in the past year. You can find the in depth series of posts on Query Store, please find here and blogs related to Azure here. I have been participating in the T-SQL Tuesdays. Its been a while I continued with my posts on Azure. I will have to be consistent on posting them. In 2023, I hope I will be writing more of the posts related to Azure.

Women in Technology Volunteer and Co-Organizing data Platform DEI user group

I feel blessed to be able to volunteer WIT user group in maintaining the Women speaker page “Webinars by Women“. I usually gather the details of women speakers along with the events they are speaking in the near future and upload the information into the portal. This will help the audience to look at the page for attending their sessions, helps event organizers who are looking for women speakers. This page is like a central location where you can find most of the women speakers from the data platform world and the event details of where and when they are speaking. I consider this as a great opportunity to serve the WIT community which is close to my heart. I highly suggest you to follow WIT page and DEI user group page on twitter to receive new upcoming session updates. If you follow these twitter handles, it will help you in being notified with updates and also show a kind gesture of you supporting women and diverse set of groups from the data platform world.

I am also serving as a Co-organizer for Data platform DEI virtual user group with amazing Tracy Boggiano and Jess Pomfret. I am so happy to work with both of them to organize the events and meetings for DEI user group.

Co-Organizer for Microsoft Data and AI South Florida User Group

I am happy to serve as a co-organizer for Microsoft Data and AI South Florida user group along with my dear friend Adriano.

Talking about my lows

Alright, so all the above information is related to highs. Everyone has to go through both highs and lows at certain point in their lives. I would like to share about my lows here. As you can see in one of my previous blog post, I have gone through lot of challenges and gaining back self confidence is not an easy few step processes. It is a continuous journey and if you fall in the trap of being low for long period of time, it will consume you and your time in complete. I had several times where I felt low for a good amount of time. During those times, I do not feel like learning anything, write any blogs or even engage in speaking. I am sure everyone has these periods in their lives where they get depressed or let the past struggles drive their present lives.

To gain back my power, I had to listen and watch a lot of motivational videos on YouTube. I read “Can’t Hurt Me” by David Goggins which helped me come out of the tough times in my life. I recently started to listen to the audio version of the book again because I felt like I am falling back again. I also purchased David Goggins second book “Never Finished” and I am going to purchase the audio book as well. David Goggins straight forward approach and his sincere suggestions (though he uses cuss words, I don’t mind them at all as it helps me realize where I am in my life in a raw way with out using soft words, babying me telling its okay to be a down in life).

David Goggins is the sole reason for where I am today in my life. If not his book, I do not know if I even would have realized what I was going through. I was waiting for someone to save me and felt like a victim of abuse for many years. His book gave me strength to face my own fears. I stood up for myself after long time.

So, am good after reading the book, right? I should be in a place where I never turned back to the past and as I now gained self confidence, I should be good, right? Well, that is not the case. I need to constantly remind myself that I am capable of doing something when self doubt creeps in and tells me where I was in the past and yells at me to never forget the past. It is a tape record that plays in my mind which pulls the past back and disturb my present time. We all face these situations sometime in our lives. When we do, we need to remember that we cannot change the past but we still have a choice to make things better in the future. We need to understand that several things in life are out of our control and no matter how many times we think about it, we can change nothing. Figuring out how those exact negative circumstances can fuel us for making a better future for ourselves is important. I am still in the process of knowing myself and fighting my own fears. It is a life long process.

If you are going through rough times in your life and are looking for someone outside of yourself to save you, I am going to tell you the hard truth that no one is coming to save you. You need to save yourself by doing the work needed. I only learned this from David Goggins book. I highly suggest you read the books to gain back your power. If you think I am doing publicity of these books, Yes, I am 🙂 and I am very proud to do so as they are truly self help books which changed my life.

As we are close to end of the year, I wish you all the best and Good luck for the year 2023! Thanks for reading!

T-SQL Tuesday #157 – Monthly DBA Checklist

Thank you Garry Bargsley for hosting the December month of T-SQL Tuesday!

Garry wanted us to write about the year end checklist for the Database administrators. I would like to write about the checklist I prefer to follow every month of the year.

Some of the important monthly checklist are as follows:

  1. Backup validation: Making sure your database backups are valid. You can do that by adding CHECKSUM while taking the backups, verify the backups regularly using VERIFYONLY option and also test the restore if it works properly by actually restoring the backup on different environment. Make sure you run the DBCC CHECKDB to be extra safe in making sure your backups are in consistent state.
  2. Database monitoring: Making sure the monitoring tool you use are not missing any of the instances. Making sure the alerts are triggered properly and email filters are in place to receive those alerts.
  3. SQL Server Security updates: Making sure your Servers are patched properly and all the services are working properly after the patching. I mention this because we recently found an issue with Master data services unable to connect to the database after SQL Server Security patching on either SQL Server Server where MDS database is hosted or on the MDS application server. We need to manually go ahead and update the database from the MDS configuration manager. To run this step of updating the database after patching, the user who runs this update needs to have super user permissions to MDS. If you are planning to take the vacation, make sure these servers specific to MDS are patched and properly updated. If you are in vacation and other DBA’s doesn’t have enough permissions to update the database or if they don’t have proper documentation to the procedures, it would be a disaster.
  4. Documentation, please: No matter how small the task and the resolution steps might be, having proper documentation of steps for troubleshooting any issue helps the DBA team members have peace of mind while resolving the issue. Make sure documentation is in place and the location to these documents are shared with all team members.
  5. Job failure notifications and please no success notifications: Make sure you only receive failure notifications and disable if you have any success notifications setup on any of the servers you built. Make sure the job owners are valid.
  6. Check for any password expirations: If any of the AD accounts are the owners of any jobs from task scheduler at OS level and if those accounts has password expired, you will get continuous notifications for those failed jobs due to password expiration. Making sure changing those passwords before going to vacation helps. I mentioned this example because I have seen cases in my past company where emails are bombarded with the failure alerts due to password expiration.
  7. On-Call: If you plan to take vacation and if your DBA team is small, make sure there is a backup for the On-Call person just in case. Having the updated contact information in outlook teams where everyone can access your contact information is crucial.

These are some of my monthly activities to check. I am looking forward to read the posts from other SQL family members on the same topic. I am sure there will be plenty of activities to take a note of.

Thanks for reading!