You are here:   Blog
Register   |  Login

Blog Archive:

* Can be used in order to search for older blogs Entries

Search in blogs

Blog Categories:

* Can be used in order to search for blogs Entries by Categories

Blog Tags:

* Can be used in order to search for blogs by keywords


Awared MVP


Microsoft® Community Contributor 

Microsoft® Community Contributor

 Read this before you use the blog! Maximize

Recent Entries

By ronen ariely on 27/07/2022 06:02
Without any announcement (which I know about), quiet as a mouse, the Microsoft Ignite site came to life with new information about Microsoft Ignite conference 2022 – Mark the date!
By ronen ariely on 27/07/2022 03:44
During the last year I got reports from people that joined meetings that they do not see the chat button when they are in the meeting. In this post I will discuss the issue and present optional solutions which other people reported that solved it in their case.
By ronen ariely on 25/07/2022 22:41
לפני יומיים מיקרוסופט הכריזה על האפשרות להשתמש בהצפנת נתונים עם מפתחות מנוהלים על ידי לקוח (CMK) עבור Azure Database for MySQL! האפשרות להביא מפתח משלך (BYOK) להגנה על נתונים במצב מנוחה, יכולה להעלות את האבטחה ולאפשר מיגרציה וסינכרון בין השרתים המקומיים לביןם השרתים בענן. אתה יכול להשתמש בתכונה זו כדי ליישם הפרדת חובות לניהול מפתחות ונתונים. בנוסף, ניתן לנהל ולארגן מפתחות באופן מרכזי באמצעות Azure Key Vault. השימוש בהצפנה מנוהלת על ידי לקוח ניתנת לך את הכוח והחראיות על מחזור החיים של מפתח האבטחה, הרשאות שימוש במפתח ופעולות ביקורת על מפתחות.
By ronen ariely on 15/07/2022 10:22
I am writing this post following a question and the discussion over Facebook about the question what is the different between JOIN and UNION. There are many common mistake which even experts are doing in defining these two task and some of these appeared in the mentioned discussion. I prefer to write the answer here since Facebook is not the best interface to write a long message - drive me nuts when in the middle of the writing Facebook raise and error and I lose all I wrote.
By ronen ariely on 14/07/2022 00:38
Following a question in the forum, I want to present the issue of using a variable in the a VIEW and how we can solve it as well as the right way to solve such request (hint: think if you are stuck in an "XY problem").

This discussion is only theoretical and only focus on the question.

By ronen ariely on 20/04/2022 04:36
By default when we add a new external user to a team on Teams using his email, then the user gets a default name based on the email address. This means that if the person uses an email address which is not based on his real full name. then other members on the team might not be able to find the person in the team or recognize his user name when he will participate in activities. IN this short post I will present the issue and the "Best practice" which is configure the name of the user while we adding him to the team. I will also discussion how to fix the user information if he was already added without the right displayed name.
By ronen ariely on 29/03/2022 10:03
When I wrote this post I had a dilemma regarding the title, since I want to bring here 2 different aspects. The first aspect is technical and related to presenting a proven solution in terms of performance over the use of internal queries. The second aspect is more of a psychological discussion related to the choices that users make while completely ignoring performance in small systems. This post discuss using the function LAG and LEAD vs using sub-queries.
By ronen ariely on 18/03/2022 17:32
In the last several days there are reports regarding a strange behavior of SSMS which seems like started with version 18.11 and continue in version 18.11.1: A Beeps sound can is played each time we close the Query Editor Window. My tests suggests that this is a bug related to not disconnecting the server...
By ronen ariely on 26/02/2022 08:21
SQL Server Management Studio version 18.11 was released a few hours ago! What's new in 18.11?
By ronen ariely on 19/02/2022 18:27
Explanation, workaround and optional solution for the error: This operation has been cancelled due to restrictions in effect on this computer.
By ronen ariely on 01/02/2022 11:41

In the last several weeks I see many questions regarding Microsoft released of the new support to manage backups of SQL Server on premises in the Azure storage. This post is not intended to replace the official documentation, but only to simplify operations in a practical way. This post present step by step tutorial on backup to URL, backup to Azure Storage, and restore back to on-premises.

By ronen ariely on 20/01/2022 10:09
If you get the error: "The subscription is not registered to use <provider name>" then this post might help you understand and solve the issue. This can be related to change which was done in your subscription type.
By ronen ariely on 13/01/2022 00:42
step by step creating UBUNTU Azure VM and install SQL Server
By ronen ariely on 01/01/2022 13:40
The function GREATEST was added to Azure SQL but it not yet supported on SQL Server on premises (or synapse dedicated sql pool). In this short post I'll present a few options to implement the same result and discuss performance a bit.
By ronen ariely on 27/12/2021 15:13
This is a simple demo of using FILESTREAM and FILETABLE, following a question in the Microsoft QnA forum
By ronen ariely on 07/12/2021 18:07
Create table permission and Provide permission on the table only to the user which created the table, is not a recommended action, but if this is your requirement then this post would help in the task. It is more important to read this post in order to understand why this action is not recommended than to use it to actually implement such scenario.
By ronen ariely on 01/12/2021 21:07
It is quite common, especially when we are using free subscriptions, to have an issue to create Azure service in specific region, creating service using specific tier, or even creating the same service multiple times. These issues related to limitation in the Azure Subscription resource quotas. This post follows such issue which was raised in the Microsoft QnA forum. In this post I will explain the issue and present optional solution or clarify the limitation.
By ronen ariely on 23/11/2021 11:47
yesterday Microsoft Released the new Cumulative Update #14 for SQL Server 2019. Read more...
By ronen ariely on 19/11/2021 11:39

SQL Server 2022 is almost here. During the Microsoft ignite 2021 conference, on November 3, Microsoft announced the new version and started to present the new features and abilities. If you are working with SQL Server then it is now time to focus on SQL Server 2022. Read more on how to Get start with SQL Server 2022 - list of links

By ronen ariely on 05/11/2021 00:00
New as of today! Microsoft improved the function STRING_SPLIT and added a new optional parameter name: enable_ordinal, which allows us to get the returned SET in an order guaranteed, according to each substring's position in the input string 
By ronen ariely on 31/10/2021 09:44
Several days ago, I publish a riddle in the SQL Server product family on Facebook. The short version of the question is this: When we are executing a CREATE TABLE then the get the error that the table already exists, but when ew are exploring the SSMS Object explorer, then we cannot see any such such table. Can you explain why and how to reproduce this station?
By ronen ariely on 22/10/2021 05:36
Once again, we will dive into the internals of the SQL Server, since someone asked in the forum "How to find who created a database"....
By ronen ariely on 04/10/2021 01:02
This is an answer to a question in the Microsoft's QnA forum. As a result of a bug in the forum, I could not publish the answer
By ronen ariely on 22/09/2021 12:28
Line breaking, also known as word wrapping, is breaking the displayed of a section of text into lines so that the text will fit into the available width of the editor. When writing queries this feature is not so useful as breaking the script line may make the query less readable, but when writing long comments this feature become one of the most useful feature. This post simply shows you how to use word-wrap by default or add a command button to Toggle Word Wrap - it's a built-in feature which is less known and if you did not used it yet, then it is time to use the power of word wrap
By ronen ariely on 09/08/2021 16:32

Happy (Hijri) New Year! Today millions of Muslims celebrating the new year according to the Hijri calendar and this is a great opportunity to lean how SQL Server support (or not) working with Hijri dates. I dedicate this post to all my Muslim friends. This post is in Hebrew and is addressed to members in Israel who work with the Muslim calendar. Unfortunately many Muslims in Israel do not speak English and I do not speak Arabic, but we all here speak Hebrew. I hope this post will helpful and useful for you.

שנה טובה (Hijri)! כיום מיליוני מוסלמים חוגגים את השנה החדשה על פי לוח השנה ההיג'רי וזו הזדמנות מצוינת לבחון כיצד SQL Server תומך (או לא) בעבודה עם תאריכי ההיג'רי. אני מקדיש את הפוסט הזה לכל חבריי המוסלמים. פוסט זה הוא בעברית ופונה לחברים בישראל שעובדים עם לוח השנה המוסלמי. לצערי הרבה מוסלמים בישראל לא מדברים אנגלית ואני לא דובר ערבית, אבל כולנו כאן מדברים עברית. אני מקווה שהפוסט הזה יהיה מועיל ושימושי עבורך.

By ronen ariely on 07/08/2021 03:25
הנושא של רשיונות לשרתי SQL, מורכב מאוד ומעלה הרבה שאלות. בפוסט זה אני אנסה להסביר ולענות על שאלות נפוצות אשר עולות בפורומים. יש לזכור שמודל הרשיונות משתנה בין גרסאות שונות של השרת. בכל מקרה תמיד מומלץ לפנות למסמכים הרשמיים של מיקרוסופט או לקבל תמיכה בנושא ממנהל חשבון טכני של מיקרוסופט שזה תפקידו ומומחיות שלו - Microsoft Technical Account Manager (TAM).
By ronen ariely on 23/05/2021 11:40

A few years ago the GitHub organization set up a new service which operates under the github.io domain owned by GitHub. This service allows us to host websites for free. Site files are stored in a project's repository under a GitHub account. This article combines information from a number of official documentation and posts on the web and provides a first look at the GitHub Pages service, in a simple step-by-step guide. All the information in this article can be found online, and it is recommended that you follow the links in the "More Information" section. This post is essentially a translation of my article that I posted on TechNet Wiki on February 28, 2019.

לפני כמה שנים הקים ארגון GitHub שירות חדש אשר פועל תחת דומיין github.io הנצא בבעלות GitHub. שירות זה מאפשר לנו לארח אתרי אינטרנט בחינם. קבצי האתר מאוחסנים במאגר (repository) של פרוייקט תחת חשבון GitHub. מאמר זה משלב מידע ממספר תיעודים רשמיים ופוסטים ברשת ומספק הצצה ראשונה לשירות GitHub Pages, במדריך פשוט בסגנון צעד-אחרי-צעד. את כל המידע במאמר זה ניתן למצוא באינטרנט, ומומלץ לעקוב אחר הקישורים בפיסקה של "מידע נוסף". פוסט זה הוא בעיקרו תרגום של מאמר שלי אשר פרסמתי ב-TechNet Wiki ב-28 בפברואר 2019.

By ronen ariely on 18/05/2021 21:00
his post follows the discussion or actually my answer in this old MSDN forum thread. It present a few strange behavior of SQL Server when dealing with Hijri dates, explain the issues and end with a short recommendation for these working with Hijri dates.
By ronen ariely on 23/04/2021 11:39
This post come to answer the question how to learn SQL, which raise in the forum from time to time. It brings my personal insights about self-learning and a few sentences on how to start learning SQL. It is not exactly a regular post like I usually write but since the question was asked once more yesterday then I simply copy the answer which I gave in the forum so I will have it documented for the next time it will be asked
By ronen ariely on 11/04/2021 08:02

Azure Files (שירות קבצים משותף) הוא שירות בענן של מיקרוסופט, אשר משתמש בקבצים השמורים Azure File Storage. הקבצים משמשים כתיקיות שיתופיות וירטואליות. כל קובץ מוגבל ל-5TB אך אתה יכול להפעיל מספר שיתופים באמצעות קבצים מרובים. ניתן להשתמש ב-Azure Files כדי להחליף אחסון מחובר ברשת (NAS) או מערכות קבצים מקומיות. השירות מבוסס על פרוטוקול Server Message Block (SMB) ומאפשר להעביר יישומים ונתונים "כפי שהם" באמצעות פרוטוקול זה. ניתן לגשת לקבצים באמצעות API תוך שימוש בתקשורת מוצפנת, או לנהל את הקבצים באמצעות ממשק המשתמש המובנה בפורטל, דרך Azure CLI או PowerShell.

על ידי שימוש בשירות של Azure File Sync, אתה יכול לסנכרן ו/או לשכפל את הקבצים בין מספר שירותים בענן ואחסון מקומי לטובת גיבוי או שיפור ביצועים. השירות מאפשר ליצור מאגר מרכזי עבור קוד או כלי עזר המשמשים במהלך בדיקות ופיתוח.

שירות קבצים משותף מאפשר לרכז מדדים וקובצי יומן להטמעה על ידי כלי ניטור וניתוח. השירות מספק יתירות לניטור ופתרון תקלות נתונים.

By ronen ariely on 25/03/2021 21:04
Structured Query Language (SQL) commands can be categorize into DCL (Data Control Language), TCL (Transaction Control Language), DDL (Data Definition Language), DML (Data Manipulation Language), DQL (Data Query Language). So, what these terms actually means and how this is related to the way you should raise questions when you ask for help?
By ronen ariely on 17/03/2021 21:29
This issue was reported in the Microsoft QnA forum! Using nested inline User-defined Scalar Functions in SQL Server version 15.0.4102.2 returns an error. I confirmed this issue in several machines.
By ronen ariely on 10/03/2021 09:33
Once more, I got the request to help in cleaning the database from unused tables, and once more I find it important to clarify that we should not be reckless to drop a table or any other entity if you are not familiar with your database structure and you have no understanding what this object is used for or why it was created. It can come back to you to bite you in the ass in a month or maybe a few years. This post is a reply to specific request and present my general point-of-view and approach. It is NOT a full solution.
By ronen ariely on 05/01/2021 10:24
Just for me - shortcuts (will fill it in time)
By ronen ariely on 23/12/2020 11:41
SQL Server Configuration Manager and SQL Server Management Studio display the status of services by using the different icons. In this post I will summarize the different options. Part of the information in  this post was taken from the official documentation while several scenario are not documented and the information is based on my tests and research.
By ronen ariely on 07/11/2020 16:56

There is a great support for Teams in PowerShell, which allow you to get all a list of all the users in a specific team. Here is the procedure in steps

By ronen ariely on 28/10/2020 18:07
Always Encrypted - demo in a nutshell (using PowerShell and C# as clients). This is pure demo code from start to end
By ronen ariely on 25/10/2020 08:53

 Before the release of SQL Server 2016, one of the most required solution was for a function which split string. There are many solutions you can find online. Most of these are based on loops that provide poor performance, using XML, or using SQLCLR which in my test provides the best option.

After many years of requests for such a function, Microsoft has finally provided us with a new function called STRING_SPLIT in SQL Server 2016, which should solve our need to split a string. Unfortunately this solution is a half solution at best!

In most cases we need a solution which guarantee the order of the result set. The STRING_SPLIT does not provide this. Thus from 2016, the requests to provide a function for splitting string, has been replaced by shouts to improve the existing function by adding column which present the order of the text.

With that being said, there is a perfect solution using a different approach using another function which was presented in SQL Server 2016. Let's show how simple it is... 

By ronen ariely on 14/09/2020 10:20
In order to open a support ticket we need to login to Microsoft services. This is true in the Azure as well as in other services like Visual Studio Subscriptions(formally called MSDN subscriptions). So this leads direct to the chicken or the egg dilemma in the case our issue is with login to the account. We cannot login so we cannot open a support ticket. This VERY SHORT post simply provides the way to bypass this issue regarding the Azure (in other services you might find other solutions fit better)
By ronen ariely on 31/08/2020 10:57
Convert Azure Virtual Machine with SQL Server installed into Azure SQL Server Virtual Machine
By ronen ariely on 18/08/2020 21:04
From time to time I come across a question in a forums from someone who claim that SQL Server Execution Plan shows that the cost of a specific operator or the sum of operators is more than 100 percentage. The discussions usually get answers that this is related to the SQL Server Execution Plan, while the truth is that this has nothing to do with the server side but the client tool which calculate the percentages of the cost. In this post I will summarize a bit more information and explain how we can be sure that this issue is indeed in the client side and not a result of information which come from the server (at least the cases I am familiar with).
By ronen ariely on 02/07/2020 17:32
Simple working with Primes numbers
By ronen ariely on 14/06/2020 01:51

When we are using Static Public IP, then there is no need to update the DNS records each time that the IP address changed. This ensures us that there will be no downtime to our services, while the DNS servers are not updated (a process that can take few hours).

The Azure public IP is a separate service, which we can associate to a new or existing Network Interface or to a load Balance. The Network Interface is associated to Virtual Machine. Therefore, if we are using a Static IP, then we can migrate IP Address from one machine to another. This is a powerful option in cases that we need to migrate services between virtual machines.

In this post I go over the theory of networking is a glance in order to cover the main terms which we all must know, before we start to play with the powerful options that Azure Static Public IP provide us. Next I present several practical scenarios of using static public IP (create IP, associate to existing VM, Migrate between VM, and so on).

By ronen ariely on 08/06/2020 05:02
Let's "play" with some data and let me show how to use SQL Server to Analyze the Coronavirus Covid-19 data. The source of the data was taken from the CSSEGISandData GitHub project. In this post I will add some useful queries and explain how to use the data and what can we do with it.
By ronen ariely on 06/06/2020 12:53
Multiple reports arrives in the forums starting 9 hours ago but Microsoft Azure subscription Service Health only alert me about one hour ago! My conclusions: follow the forums and report in the forums for others to get the information fast
By ronen ariely on 29/05/2020 21:07

When we create DSN to the Azure SQL Database using Microsoft tool odbcad32.exe, then the password is not saved. Therefore, each time we use the DSN we need to fill the password. Can we skip this task and store the password in the DSN cata?

Note! This is a pure answer to a question in Microsoft new forum interface Q&A. The forum does not allow me to publish the answer raising the absurd error "Exceeded the Normal request".

By ronen ariely on 28/05/2020 02:32

This is a simple step-by-step tutorial to demonstrate how we can create auditing of logout event using Extended events, on Azure SQL Database. It basically present an answer to a question in the MSDN forum.

By ronen ariely on 18/05/2020 18:07

When executing the powershell command Get-AzSqlDatabaseGeoBackup I am getting an error which claim that the Azure logical server does not exists in the current subscription, while the server is alive and I can execute the command Get-AzSqlDatabase and get all the databases in the server!

Get-AzSqlDatabaseGeoBackup : 40647: Subscription '9be5b21d-3676-40f1-baec-16c10ae909ec' does not have the server 'sponsor01'.

By ronen ariely on 08/05/2020 23:29
Does COLLATE important when we use the function UPPER on the text? Just a small sample related to a forum question, since I cannot add images in the comments
By ronen ariely on 07/05/2020 03:07
Application Change Analysis service is monitoring tool that supports viewing changes for all resources across Azure subscription on Azure portal. Sound awesome! So let's review what works and what not so much...