Category: Microsoft
Category Archives: Microsoft
Number definition in excel
When ever I type number in a cell For exemple 120 it is show 1.2 or 1 will be seen 0.01
how do I change it. when I go to cell proporty it doesn’t help.
Thanks for the help
Shani
When ever I type number in a cell For exemple 120 it is show 1.2 or 1 will be seen 0.01how do I change it. when I go to cell proporty it doesn’t help. Thanks for the help Shani Read More
Displaying Categories in a Group Calender
In Outlook desktop …. When a user sets up a 2nd calendar to share with team members, they are able to see the category assigned to each meeting or event, however when one sets up a group calendar (all users are owners) categories are unique to the individual user’s outlook setup.
Is there a way to assign categories to meetings so that ALL users can see them?
In Outlook desktop …. When a user sets up a 2nd calendar to share with team members, they are able to see the category assigned to each meeting or event, however when one sets up a group calendar (all users are owners) categories are unique to the individual user’s outlook setup. Is there a way to assign categories to meetings so that ALL users can see them? Read More
Ignore the Hype Surrounding the Copilot Announcement
Microsoft’s January 15 announcement reduced deployment costs and opened the possibility for Copilot for Microsoft 365 deployments to many Office 365 tenants. Reducing costs is great, but just because Copilot for Microsoft 365 is now available to many more tenants doesn’t mean that it is a silver bullet to address all IT woes.
https://office365itpros.com/2024/01/18/copilot-for-microsoft-365-deployment/
Microsoft’s January 15 announcement reduced deployment costs and opened the possibility for Copilot for Microsoft 365 deployments to many Office 365 tenants. Reducing costs is great, but just because Copilot for Microsoft 365 is now available to many more tenants doesn’t mean that it is a silver bullet to address all IT woes.
https://office365itpros.com/2024/01/18/copilot-for-microsoft-365-deployment/ Read More
Is Co Pilot Pro available as a benefit for partners? We’d like to experiment and learn more
We have developed a chat based service desk application that integrates with MS Teams, leverages a lot of services via Azure Open AI, and routes to service desk agents (https://www.chimev5.com/). We also have a lot of customers who are starting to explore, and deploy, Copilot and Copilot Pro.
We imagine that we may want to integrate with Copilot Pro and we’d like to deploy an internal instance, connect with some data providers, and generally consider integration avenues.
Is Copilot Pro an available benefit as part of the MS Partner program?
Thanks
We have developed a chat based service desk application that integrates with MS Teams, leverages a lot of services via Azure Open AI, and routes to service desk agents (https://www.chimev5.com/). We also have a lot of customers who are starting to explore, and deploy, Copilot and Copilot Pro. We imagine that we may want to integrate with Copilot Pro and we’d like to deploy an internal instance, connect with some data providers, and generally consider integration avenues. Is Copilot Pro an available benefit as part of the MS Partner program? Thanks Read More
Continued region expansion: Azure Data Factory is generally available in two more regions
Azure Data Factory is now available in two new regions:
Israel Central
Italy North
You can now provision Data Factory in the new regions in order to co-locate your Extract-Transform-Load logic with your data lake and compute.
See the full set of Azure Data Factory supported regions.
Microsoft Tech Community – Latest Blogs –Read More
Microsoft 365 Group Calendar Reminder
Hi,
My team recently created a Group calendar which we are using to establish visibility across the group for interlated meetings. I notice that when a meeting is created in this calendar, the Outlook does not send an upcoming appointment reminder as it does for my personal calendar. Can this be enabled?
Hi, My team recently created a Group calendar which we are using to establish visibility across the group for interlated meetings. I notice that when a meeting is created in this calendar, the Outlook does not send an upcoming appointment reminder as it does for my personal calendar. Can this be enabled? Read More
Announcing the availability of the new gMSA on AKS workshop
gMSA is the technology behind the support for Active Directory dependent applications to run on Kubernetes. Over time, we’ve been adding a lot of resources around it, such as PowerShell modules (to help with the configuration process), documentation, scripts, and more. The reason behind this is that on one side, gMSA is incredibly popular (of course, anyone trying to containerize a Windows app that relies on AD for authentication will use it), but also because it is somewhat complex to configure it. Setting up gMSA means you have to configure your Kubernetes cluster, the networking between the cluster and Domain Controller(s), the secret store (such as Azure Key Vault), the Domain Controller itself, and so on…
Because so many resources need to be configured, many customers have a hard time to see how gMSA actually works before doing it in production. Today, we’re glad to announce that you can now try gMSA on AKS with a guided workshop. This workshop still requires a subscription for you to use, but all the other configurations can be implemented by following the workshop’s instructions. We plan to upgrade this into a self-contained workshop in the future.
What is part of the workshop
This is an end-to-end workshop on which you can start with a net-new subscription (or an existing one, of course) and build everything from scratch. The workshop covers briefly what gMSA is and how it’s used.
The workshop has the following objectives:
Provide an overview of gMSA on AKS, necessary components, and how to set up an environment for a Windows app that requires Active Directory authentication.
Understand how the AksGMSA PowerShell module helps in the process of configuring gMSA on AKS.
Understand the flow of configuring gMSA on AKS and how the multiple resources interact with each other.
The workshop is divided into seven exercises:
Exercise 01 – Spinning up Azure environment
Here you will deploy the base services to get started. You will use a script to deploy the Resource Group, the Virtual Network and Subnet, the AKS cluster and the Windows node pool. The script will also deploy a VM to be used as domain controller.
Exercise 2 – Configure Active Directory
Here we will prepare the VM and the Active Directory domain to use gMSA. A script is provided to deploy AD into the VM.
Exercise 3 – Enable Azure Bastion to RDP into DC01 VM and take note of additional resources
To reduce costs, we will only use one VM in this workshop, so we will also use this VM (which is our Domain Controller) for other purposes – which is not recommended in production. To securely access the VM, we will enable Azure Bastion.
Exercise 4 – Configure the AksGMSA PowerShell module on the DC01 VM
This is where most of the gMSA configuration is passed to the AksGMSA PowerShell module on the DC01 VM. The provided script and module will set up gMSA on AKS later and in this exercise, we pass on the configuration we want to be used later.
Exercise 5 – Deploy gMSA on AKS and configure AD and Azure resources
This where the configuration for gMSA on AKS happens. Using the information provided before, we run the commands for the AksGMSA module and set up the gMSA on AKS, configure AD, create the Azure Key Vault, Managed Identity, and all other configurations.
Exercise 6 – Validate the deployment of gMSA on AKS
Once gMSA on AKS has been deployed and configured, you can use the AksGMSA module to validate the configuration and communication between the AKS cluster and the AD domain. This exercise uses the native commands on the module to validate everything is in place.
Exercise 7 – Deploy IIS with Windows authentication enabled
Validating that gMSA has been deployed correctly is not as cool as seeing an application working properly. In this exercise, we deployed a very simple, sample application to see the authentication actually happening. We deploy a Windows pod to the AKS cluster, with an IIS website setup to use Windows authentication and then open the website to see the authentication pop-up, provide the username and password, and see the website open.
At the end, there’s an exercise to clean up the environment and ensure you’re not being charged for this any further.
Hopefully this workshop provides a good overview of how gMSA on AKS works. Feel free to go wild and try different things, try your own app, your own set up. And let us know what you think and how we can improve not only the workshop itself but the whole gMSA on AKS experience.
Microsoft Tech Community – Latest Blogs –Read More
How to Convert Audio to .WAV for Speech Service Using MoviePy
Azure Speech Service offers robust speech recognition, translation, text-to-speech and many more capabilities, providing developers with powerful tools to integrate voice-based interactions into their applications.
However, to ensure seamless compatibility and optimal performance, Azure Speech Service requires audio files to adhere to specific standards regarding format, bitrate, sampling rate, and channel configuration. Let’s dive into how we can use Moviepy to convert your different audio files to Azure Speech Service Compatible.
Leveraging MoviePy for Audio Conversion
According to its documentation, MoviePy is a Python module for video editing, which can be used for basic operations (like cuts, concatenations, and title insertions), video compositing (a.k.a. non-linear editing), video processing, or to create advanced effects. It can read and write the most common video formats, including GIF.
MoviePy is a powerful Python module primarily designed for video editing but equally adept at manipulating audio. Let’s explore how MoviePy can help convert various audio formats to comply with Azure Speech Service requirements.
Understanding Azure Speech Service Audio Requirements
For the use case being discussed in this article, we would need to know the audio configurations. Azure Speech Service demands audio files in the WAV format with specific bitrate, sampling rate, and channel configurations. Here are the key criteria:
File Format: WAV (Microsoft PCM)
Bit Depth: 16-bit
Sampling Rate: 16 kHz or 8 kHz (some scenarios support 32 kHz or 48 kHz)
Channels: Mono or Stereo (Mono recommended for speech recognition)
Bitrate: 256 kbps (kilobits per second) for mono, 512 kbps for stereo
Codec: PCM (Pulse Code Modulation)
File Size: Generally, up to 4 GB for continuous recognition
Step-by-Step Guide
Now to convert your audio file in a different format to have these configurations, follow the steps below.
Step 1 – Install Moviepy
Begin by installing MoviePy using the command pip install moviepy in your terminal. This would install all the dependencies needed to run Moviepy in your Python environment along with Moviepy.
Step 2 – Import Required Functions
Import required attributes from MoviePy’s editor method, particularly AudioFileClip, which will help load and manipulate audio files. It has a method you can call to set the output file and format, bitrate and all configurations necessary.
from moviepy.editor import *
or
from moviepy.editor import AudioFileClip
Step 3 – Load the Audio File
Load your audio file using the AudioFileClip method. This method takes in the audio file path and loads up your audio file as a Moviepy AudioFileClip format that can be manipulated to the file format required for compatibility with Azure Speech Service API and/or SDK.
Assuming the file format is .ogg and the file is saved as audio.ogg, load the file as shown below. Ensure to use a relative path if the audio file is in a different directory from your Python script.
audioclip = AudioFileClip(“audio.ogg”)
Step 4 – Set Audio Configurations
Define the audio parameters necessary for compatibility as required for your solution.
audio_params = {
“codec”: “pcm_s16le”,
“fps”: 16000, # Set the desired sampling rate: 16000 Hz
# “fps”: 8000, # Alternatively, set the sampling rate to 8000 Hz
“nchannels”: 1, # Mono audio
“bitrate”: “16k” # Set the desired bitrate
}
Step 5 – Pass Configurations Parameters and Convert Audio
Define the file path for the output. Since we want to convert our audio file to .wav format, we would define a file with the extension .wav.
Pass the defined required audio parameters that you have defined to the write_audiofile method from AudioFileClip. This would convert the audio file to the extension you have defined in your output file path and also configure the audio to all the parameters you have set and passed into the method.
output_file = “audio.wav”
audioclip.write_audiofile(output_file, codec=audio_params[“codec”],fps=audio_params[“fps”],nbytes=2,bitrate=audio_params[“bitrate”])
Step 6 – Load Your New Audio File to Use with Azure Speech Service
Your new audio file should be located in the file path you have defined. If you followed the naming convention above, you would find your “audio.wav” file in the same directory as your python script/code. You can then load up this file with the Azure Speech Service SDK (for example) to create a simple transcription solution. A simple sample is found below:
After getting your Speech Service key and region from your Azure portal and saving them as environment variables, follow the code below to test run with your new audio file.
import os
import azure.cognitiveservices.speech as speechsdk
def recognize_from_file():
# This example requires environment variables named “SPEECH_KEY” and “SPEECH_REGION”
speech_config = speechsdk.SpeechConfig(subscription=os.environ.get(‘SPEECH_KEY’), region=os.environ.get(‘SPEECH_REGION’))
speech_config.speech_recognition_language=”en-US”
audio_config = speechsdk.audio.AudioConfig(filename=”audio.wav”)
speech_recognizer = speechsdk.SpeechRecognizer(speech_config=speech_config, audio_config=audio_config)
speech_recognition_result = speech_recognizer.recognize_once_async().get()
if speech_recognition_result.reason == speechsdk.ResultReason.RecognizedSpeech:
print(“Recognized: {}”.format(speech_recognition_result.text))
elif speech_recognition_result.reason == speechsdk.ResultReason.NoMatch:
print(“No speech could be recognized: {}”.format(speech_recognition_result.no_match_details))
elif speech_recognition_result.reason == speechsdk.ResultReason.Canceled:
cancellation_details = speech_recognition_result.cancellation_details
print(“Speech Recognition canceled: {}”.format(cancellation_details.reason))
if cancellation_details.reason == speechsdk.CancellationReason.Error:
print(“Error details: {}”.format(cancellation_details.error_details))
print(“Did you set the speech resource key and region values?”)
recognize_from_file()
This example uses the recognize_once_async operation to transcribe utterances of up to 30 seconds, or until silence is detected.
This sample code was taken from the Azure Speech Service Documentation. You can explore more on the Azure Speech Service through the documentation. You can also watch this tutorial on building transcription and translation services with the Azure Speech Service and Translator service from the video below.
<!–
%[https://youtu.be/ikNPMomeZKs?si=Bup8lJ5P-voImVqc]
–>
Conclusion
MoviePy simplifies the process of converting audio files to the precise format required by Azure Speech Service. By adhering to these guidelines and utilizing MoviePy’s flexibility, you can seamlessly prepare your audio data for optimal performance within Azure’s powerful Speech Service.
That’s it!!! We have successfully converted our audio to be compatible with Azure Speech Service.
Additional Resources
1. Recognize and convert speech to text using Python
2. Sign up for free Azure credits
3. Process and Translate Speech with Azure AI Speech Services
4. Get started with Azure Cognitive Services Speech
5. Translate speech with Azure AI Speech Service
Microsoft Tech Community – Latest Blogs –Read More
Partner Blog | Copilot for Microsoft 365: expanded partner opportunity
Written by Nicole Dezen, Chief Partner Officer and Corporate Vice President, Global Partner Solutions
Within our expansive portfolio, AI intersects with virtually every product, industry, and solution area, and we anticipate that it will bring about a fundamental shift in every software category, unlocking a new era of productivity growth. Our commitment is to empower partners to harness the potential of this innovation for the benefit of customers worldwide, assisting them in navigating through this emerging technological landscape.
Since launching Copilot for Microsoft 365 for enterprise customers in November, partners have been supporting customers to define, activate, and deliver their AI strategy. Building on this momentum, we are taking the next step, expanding access to more customers, and creating more opportunities for the partners who serve those customers.
Today, we are announcing that as of January 16, 2024, Copilot for Microsoft 365 is generally available across all sales channels including Cloud Solution Provider (CSP) new commerce.
Continue reading here
Microsoft Tech Community – Latest Blogs –Read More
Bridge the skills gap with Microsoft Learn for Organizations collections
With the best of intentions, many organizations start digital transformation initiatives that promise greater productivity, agility, and other cloud benefits, only to discover that their teams don’t have all the skills to follow through. Now Microsoft Learn for Organizations—the front door to all that Microsoft Learn offers for learners engaged in team training—provides curated collections of learning resources that can help get teams back on track. These opportunities enable organizations to unblock AI, app modernization, migration, and other strategic projects.
Close the skills gap
Too often, we hear that customers’ IT and engineering teams are midway through a strategic digital transformation—maybe they’re migrating on-premises systems to the cloud or using AI to modernize and transform apps—but then they get stuck. And they don’t have the skills to take the next steps.
Everyone is talking about the skills gap today. The shortage of skilled IT workers will block 65 percent of businesses from realizing the full value of their cloud, data, and automation investments, according to IDC’s Skills Forward: A 2023 IT Skills Shortage Survival Guide.[1] Meanwhile, 80 percent of surveyed organizations say that lack of resources and expertise is a top challenge in cloud adoption, as observed in the Flexera: 2023 State of the Cloud Report.[2]
“We wanted to help enterprises and organizations of all sizes to gain the technical expertise they need to succeed in their digital transformation plans,” explains Natalie Duryea, Director, Business Operations and Programs – Enterprise Skills Initiative at Microsoft. “Part of the challenge for learners and teams is to navigate all the steps in a learning journey. Now we have Microsoft official collections for the in-demand types of solutions that our customers are building.”
Welcome to Microsoft Learn for Organizations collections
Like the perfect playlist, Microsoft Learn for Organizations collections bring together selected resources and opportunities that you can share with learners and teams in your organization. And now, based on customer demand, we’ve created official collections to help close the skills gap for common scenarios. These collections include self-paced learning paths and modules, instructor-led courses, community resources, code samples, webpages, on-demand videos and events, gamified learning, and more. They also organize the resources needed to earn industry-recognized Microsoft Certifications and Microsoft Applied Skills credentials.
Skill up for the modern AI-enabled enterprise
To drive innovation and stay competitive, organizations are modernizing enterprise apps and taking advantage of AI and other new cloud technologies. You can make sure that your teams have what it takes to get the job done. The following Microsoft collections offer recommended resources to master AI fundamentals and related skills:
Build and modernize with AI includes the best learning paths and resources for all things AI—from the fundamentals to mastery. It also provides details on the new Microsoft AI Skills Initiative, which has free coursework developed with LinkedIn, including the first professional certificate on generative AI in the online learning market, along with resources to help learners improve their AI fluency.
Accelerate developer productivity offers virtual and interactive learning opportunities that can jump-start projects, whether learners are just starting to build cloud skills or are looking to master DevOps, GitHub, Azure solutions, and more.
Move your migration forward
One of the first steps that many organizations take on their cloud journey is to move on-premises and server-based workloads to Azure. These Microsoft collections can help you make the right choices:
Migrate and secure Windows Server and SQL Server workloads includes a wide variety of training options for this in-demand scenario, including immersive, challenge-based, gamified learning opportunities.
Migrate SAP covers everything you need to know about the collaboration between Microsoft and SAP and the many options for running SAP workloads on Azure.
Migrate enterprise apps assembles an array of resources designed to help your organization efficiently migrate enterprise applications at scale.
Build a thriving learning culture
Microsoft official collections can also help you get started building a learning culture and empowering a growth mindset across your organization:
Getting started with organizational skilling provides skill-building resources, including details on multiple integration services available, so you can more effectively use Microsoft Learn in your organization.
Transform your organization with Microsoft for business professionals is designed to empower business users and leaders with skill-building resources that can help them stretch their learning and development budget and strategically apply Microsoft solutions.
Don’t see the right collection? Build your own!
Learning isn’t one-size-fits-all. You can also design your own collections to fill the skills gaps in areas unique to your organization. If you have a Microsoft Learn profile, you can curate your own set of helpful resources to create a collection that can be shared with your learners and teams. For details, check out Create and manage Microsoft Learn collections. Whether you create your own or use the official collections—or both—be sure to make the most of Microsoft Learn for Organizations to help your teams build the skills they need to unblock critical technical projects in your organization.
Learn more
Explore all the Microsoft official collections.
Get skilled up for your AI journey in the Microsoft Learn AI learning and community hub.
Discover the latest Microsoft Applied Skills credentials.
Read 9 ways Microsoft Learn helps you with the skills-first economy.
Learn how to navigate the skills-first landscape with Microsoft Learn.
Check out Microsoft Learn: Four key features to help expand your knowledge and advance your career.
[1] IDC report, Skills Forward: A 2023 IT Skills Shortage Survival Guide, #DR2023_LL2_AL_GS, March 2023.
[2] Flexera, 2023 State of the Cloud Report, April 2023.
Microsoft Tech Community – Latest Blogs –Read More
Querying Watchlists
special thanks to @Ofer_Shezaf for showing me the new function call.
Watchlists
Watchlists are a feature of Microsoft Sentinel that provide great flexibility and useability. They allow for user-defined tables that can be used in KQL queries to provide additional data. By uploading data using CSV files, users control the data that are in the watchlists and that data can be modified and new rows added as needed.
Watchlists provide a “searchkey” field that is unique across the watchlist and can be used to reference an individual row as discussed below.
Querying Watchlists
The Microsoft Sentinel portal UI provides a way to query the watchlist in the logs watchlist. You can select the watchlist and then click on the “View in Logs” button. This will then transfer the user to Logs, or Advanced Hunting if using Unified SOC Platform, and will execute the following command:
_GetWatchlist(‘<Watchlist Name>’)
This will load the entire watchlist and return all the rows. While this works fine for most of the applications, if your watchlist is very large, it could cause a timeout issue depending on the rest of the KQL query.
For those cases, use the “_ASIM_GetWatchlistRaw” function to return specific rows. It takes two parameters: The Workbook name and a dynamic list of the values for the “searchkey” column that will be used to determine which rows to return.
The example below demonstrates how to use this function:
let Users = dynamic([“User1@contoso.com”, “user1@contoso.com”]);
_ASIM_GetWatchlistRaw(‘VIP’,Users)
| evaluate bag_unpack(WatchlistItem)
Note the call to “evaluate bag_unpack(WatchlistItem)” at the end. This is needed to expand the raw information that is stored in the watch list rows into the same format you get using “_GetWatchlist”
Microsoft Tech Community – Latest Blogs –Read More
Lesson Learned #477:NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set
In the realm of SQL Server, certain combinations of commands and functions can lead to unexpected conflicts and errors. A notable example is the conflict between the NEXT VALUE FOR function and the ROWCOUNT setting. This article aims to dissect the nature of this error, explaining why it occurs, its implications, and how to effectively capture and analyze it using Extended Events in Azure SQL Database. For example, we got the following error message: Msg 11739, Level 15, State 1, Line 11 – NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET. – NEXT VALUE FOR (Transact-SQL) – SQL Server | Microsoft Learn
Section 1: Understanding the Error
What is NEXT VALUE FOR? The NEXT VALUE FOR function in SQL Server is a crucial tool for generating sequential values from a defined sequence. It’s commonly used for auto-generating unique identifiers, like primary keys.
Conflict with ROWCOUNT: The error arises when NEXT VALUE FOR is used in conjunction with the ROWCOUNT option. ROWCOUNT, when set, limits the number of rows affected by a query. However, NEXT VALUE FOR expects to operate without such limitations, leading to a conflict. This issue can also manifest when using TOP or OFFSET clauses, which similarly restrict the result set.
Error Scenario: Imagine a scenario where a developer attempts to retrieve the next value from a sequence while ROWCOUNT is set to a specific limit. This operation triggers an error, as SQL Server cannot reconcile the sequence’s need for unbounded operation with the imposed row count restriction.
Section 2: Capturing the Error with Extended Events
Introduction to Extended Events: Extended Events are a lightweight, highly configurable system for monitoring and troubleshooting in SQL Server and Azure SQL Database.
Setting up an Extended Event Session: Guide the reader through setting up an Extended Event session to capture this specific error. Mention the need to focus on the error_reported event and how to configure the session to target the ring buffer for data collection.
Querying the Ring Buffer: Provide a detailed explanation and a sample query on how to retrieve and analyze the error information from the ring buffer. This will help in understanding the occurrence and frequency of the error in a live environment.
You could reproduce the issue following this syntax:
CREATE SEQUENCE TestSequence
AS INT
START WITH 1
INCREMENT BY 1;
SET ROWCOUNT 1;
SELECT NEXT VALUE FOR TestSequence, * FROM MyTable;
In order to capture this info we could create an extended event
CREATE EVENT SESSION [CaptureError] ON database
ADD EVENT sqlserver.error_reported(
ACTION(sqlserver.sql_text)
)
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
ALTER EVENT SESSION [CaptureError] ON database STATE = START;
SELECT
event_data.value(‘(@timestamp)[1]’, ‘DATETIME2’) AS TimeStamp,
event_data.value(‘(data[@name=”error_number”]/value)[1]’, ‘INT’) AS ErrorNumber,
event_data.value(‘(data[@name=”message”]/value)[1]’, ‘VARCHAR(MAX)’) AS ErrorMessage,
event_data.value(‘(action[@name=”sql_text”]/value)[1]’, ‘VARCHAR(MAX)’) AS SqlText
FROM
(
SELECT
CAST(target_data AS XML) AS target_data
FROM
sys.dm_xe_database_session_targets AS t
INNER JOIN
sys.dm_xe_database_sessions AS s ON t.event_session_address = s.address
WHERE
s.name = ‘CaptureError’
AND t.target_name = ‘ring_buffer’
) AS tab
CROSS APPLY
target_data.nodes(‘RingBufferTarget/event’) AS q(event_data)
Enjoy!
Microsoft Tech Community – Latest Blogs –Read More
Lesson Learned #476:Identifying Sleeping Sessions with Open Transactions in Azure SQL Database
In SQL Server environments, managing session states and transactions is key to ensuring optimal database performance. A particular challenge arises with sessions in a ‘sleeping’ state holding open transactions for extended periods. These sessions, while seemingly inactive, can hold locks on resources, leading to potential deadlocks or performance degradation.
Our focus is on a SQL query designed to pinpoint such sessions. The query utilizes SQL Server’s dynamic management views: sys.dm_exec_sessions, sys.dm_exec_requests, and sys.dm_tran_session_transactions. These views provide real-time data about active sessions, their current requests, and associated transaction details.
The heart of the query lies in its ability to filter sessions based on specific criteria: sessions must be in a ‘sleeping’ state, have an open transaction, and be inactive for over 5 minutes. This precise filtering allows database administrators to quickly identify and address sessions that might contribute to resource locking and overall performance issues…”
SELECT
ses.session_id,
ses.login_name,
req.start_time,
req.total_elapsed_time,
req.command,
req.status,
trans.transaction_id,
ses.status,
ses.total_elapsed_time,
ses.last_request_start_time,
ses.last_request_end_time,
ses.login_time,
DATEDIFF(minute, ses.last_request_end_time, GETDATE()) AS InactiveTime
FROM sys.dm_exec_sessions ses
LEFT JOIN sys.dm_exec_requests req ON req.session_id = ses.session_id
LEFT JOIN sys.dm_tran_session_transactions trans ON ses.session_id = trans.session_id
WHERE trans.transaction_id IS NOT NULL
AND DATEDIFF(minute, ses.last_request_end_time, GETDATE()) > 5
and ses.status = ‘sleeping’
Enjoy!
Microsoft Tech Community – Latest Blogs –Read More
Python Data Science Day 2024: Unleashing the Power of Python in Data Analysis
Hello, fellow data enthusiasts!
We’re excited to share that the Python Data Science Day is set to take place on March 14th, 2024, aligning with the mathematical constant Pi Day (3.14). This event is a fantastic opportunity for Python developers, entrepreneurs, data scientists, students, and researchers to come together and explore modern solutions for data pipelines and complex queries.
What to Expect?
The Python Data Science Day will feature a variety of sessions and lightning talks from experts in the field. Whether you’re interested in high-level programming topics or diving deep into specific features, there’s something for everyone.
Sessions: These 25-minute presentations, either pre-recorded or live, will be delivered by up to two people and cover a range of programming stories, approaches, and solutions.
Lightning Talks: If you’re new to public speaking or have a concise idea to share, these 5 to 7-minute talks are perfect for you. They focus on a single idea and are designed to inspire further learning.
Call for Speaker Proposals
The call for speaker proposals is open until January 25th, 2024. If you have a cool tool, product, or skill to discuss, we encourage you to submit your proposal and join the list of amazing speakers. https://aka.ms/Python/DataScienceDay/CFP
More Ways to Engage
Microsoft Fabric Global AI Hack Together: Join us from February 15th to March 4th, 2024, for live streams and real-world problem-solving with an AI-powered analytics platform.
14 Days of Python Data Science Series: Leading up to the event, we’ll release articles and recipes for using Data Science on Microsoft tools.
Data Science Cloud Skills Challenge: Participate in self-paced learning until April 15th, 2024.
Special Guest Speakers
You could be among the special guest speakers, joining the ranks of Sarah Kaiser, PhD, and Soojin Choi. Don’t miss this chance to contribute to the Python Data Science community.
Join the Conversation
Connect with us on Discord to continue the discussion and share your experiences with like-minded individuals.
We can’t wait to see you there and witness the innovative ideas and projects you’ll bring to the table. Mark your calendars for March 14th, 2024, and prepare for a day filled with Python and data science wonders! https://aka.ms/python-discord
More Data Science at Microsoft…
Data Scientist Certifications
Data Scientist Training Path
Data Science for Beginners – GitHub Repo
Microsoft Tech Community – Latest Blogs –Read More
Expanding availability of Copilot for Microsoft 365
Today, we announced that we are expanding Copilot for Microsoft 365 to a much broader set of organizations, available across more channels, and without a minimum seat required. We are also extending our data residency commitments for Copilot for Microsoft 365 and bringing Microsoft Copilot Graph-grounded chat to Copilot in Windows. Join the upcoming AMA and Tech Accelerator event and engage with experts from Microsoft to learn more about Copilot for Microsoft 365.
Expanding availability of Copilot for Microsoft 365
Starting today, we have removed the 300-seat minimum purchase for Copilot for Microsoft 365 commercial plans. We have also extended support so that Office 365 E3 and E5 customers are eligible to purchase Copilot, and we’re extending Semantic Index for Copilot to Office 365 users with a paid Copilot license. Finally, we have announced that Copilot for Microsoft 365 is generally available for businesses of all sizes, supported on Microsoft 365 Business Standard or Business Premium. This follows a successful early access program focused specifically on small and medium businesses, as well as the previously announced availability for staff and faculty of education institutions with Microsoft 365 A3 or A5 licenses. Commercial customers—including small and medium-sized businesses—can now purchase Copilot for Microsoft 365 through our network Cloud Solution Provider partners (CSPs) and you can learn more about them here.
We still recommend that customers start by giving Copilot to a critical mass of their information workers. We learned during the early access program that this creates a flywheel of interest and adoption, accelerating time to value and an organization’s ability to measure impact in a meaningful way. Copilot for Microsoft 365 licenses will be capped by the total number of eligible base licenses that a customer has. That is, customers must have a product license of one of the prerequisite base SKUs for each seat of Copilot for Microsoft 365 they purchase. You can review requirements for Copilot here.
Updating data residency commitments
We’ve heard feedback from our Enterprise customers that they need assurances that Copilot data is managed appropriately across geographically diverse teams. To support, Copilot for Microsoft 365 upholds residency commitments as outlined in the Microsoft Product Terms and Data Protection Addendum.
We’re pleased to share that later this year Copilot for Microsoft 365 will be added as a covered workload under the data residency commitments in Microsoft Product Terms and the Microsoft Advanced Data Residency (ADR) and Multi-Geo Capabilities add-ons. For additional information on Copilot for Microsoft 365 privacy and data storage please visit Data, Privacy, and Security for Microsoft Copilot for Microsoft 365. To learn more about our commitments to data residency, see Microsoft 365 Data Residency Overview and Definitions.
Microsoft Copilot capabilities coming to Windows desktop
Organizations will soon be able to experience Copilot for Microsoft 365 integrated in Windows desktop, bringing Graph-grounded chat capabilities to Copilot in Windows for users with a Copilot for Microsoft 365 license. This update will be available to organizations with Copilot for Microsoft 365 and Copilot in Windows enabled, beginning the week of February 4th. This adds a new, simple way for users to access Copilot, in addition to the current surfaces in Teams, Edge, and copilot.microsoft.com. For information on managing Copilot in Windows, review this article.
Start preparing your organization for Copilot for Microsoft 365
There are steps you can take today to get your tenant prepared for Copilot:
Prepare your data and assess all relevant data security, privacy, and compliance controls are in place. Copilot inherits your existing permissions and policies so ensuring that these are in place helps ensure seamless deployment. Conduct access reviews for SharePoint sites, documents and tenant data, employ the use of sensitivity labels to protect important data, and validate policies for data loss prevention, retention, and compliance.
Review prerequisites for Copilot by reviewing overview and requirements for Microsoft 365 Copilot to position your tenant to seamlessly deploy Copilot. This setup guide also provides a simple walkthrough of the process.
Learn more about Copilot for Microsoft 365, how it works, benefits to your organizations, how your data is handled and protected.
Familiarize yourself with the admin controls available to manage Copilot in the Microsoft 365 admin center Copilot page.
Develop your adoption strategy by leveraging the resources available on our adoption site, including this adoption kit and user onboarding toolkit.
Check readiness, measure adoption and impact through the Microsoft Copilot Dashboard (in Preview) in Viva Insights or PowerBI that helps organizations maximize the value of Copilot for Microsoft 365. It provides actionable insights to help your organization get ready to deploy AI, drive adoption based on how AI is transforming workplace behavior and measure the impact of Copilot.
To learn more about Copilot, you can review our documentation hub, requirements, setup, and information about privacy, security, and compliance. You can also watch our sessions at this past Ignite on getting ready for Copilot.
For small and medium business customers, join our discussion forum to collaborate with other Copilot for Microsoft 365 users, take part in community calls with open Q&A, hear directly from Microsoft Copilot engineers, and access exclusive resources. Also, check out the resources available on the small and medium business Copilot adoption site.
For a comprehensive introduction and deep dive into Copilot for Microsoft 365, join us during our Copilot for Microsoft 365 Tech Accelerator, February 28 and 29, right here in the Tech Community. Listen in as experts from Microsoft talk delve into preparing for Copilot with recommendations and best practices, share strategies on driving adoption, and measuring and maximizing value for your organization. There will also be plenty of opportunities to ask questions and engage with our experts.
Finally, join our next Ask Me Anything (AMA) tomorrow at 9am PT, here in the Copilot for Microsoft 365 Tech Community. Feel free to post your questions onto the event page ahead of time, and our panel of experts will answer them during the event.
Microsoft Tech Community – Latest Blogs –Read More
The Publisher failed to allocate a new set of identity ranges for the subscription
Problem:
===========
Assume that you have tables with Identity columns declared as datatype INT and you are using Auto Identity management for those articles in a Merge Publication.
This Publication has one or more subscribers and you tried to re-initialize one subscriber using a new Snapshot.
Merge agent fails with this error:
>>
Source: Merge Replication Provider
Number: -2147199417
Message: The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation. If a republishing Subscriber has run out of identity ranges, synchronize the republishing Subscriber to obtain more identity ranges before restarting the synchronization. If a Publisher runs out of identit
Cause:
============
Identity range Merge agent is trying to allocate, exceeds maximum value an INT datatype can have.
Resolution
=================
Assume that publisher database has only one Merge publication with 2 subscribers, and your merge articles have this definition:
>>>
exec sp_addmergearticle @publication = N’MergeRepl_ReproDB’, @article = N’tblCity’, @source_owner = N’dbo’, @source_object = N’tblCity’, @type = N’table’, @description = N”, @creation_script = N”, @pre_creation_cmd = N’drop’, @schema_option = 0x000000004C034FD1, @identityrangemanagementoption = N’auto’, @pub_identity_range = 1000, @identity_range = 1000, @threshold = 90, @destination_owner = N’dbo’, @force_reinit_subscription = 1, @column_tracking = N’false’, @subset_filterclause = N”, @vertical_partition = N’false’, @verify_resolver_signature = 1, @allow_interactive_resolver = N’false’, @fast_multicol_updateproc = N’true’, @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N’true’, @compensate_for_errors = N’false’, @stream_blob_columns = N’false’, @partition_options = 0
exec sp_addmergearticle @publication = N’MergeRepl_ReproDB’, @article = N’tblCity1′, @source_owner = N’dbo’, @source_object = N’tblCity1′, @type = N’table’, @description = N”, @creation_script = N”, @pre_creation_cmd = N’drop’, @schema_option = 0x000000004C034FD1, @identityrangemanagementoption = N’auto’, @pub_identity_range = 1000, @identity_range = 1000, @threshold = 90, @destination_owner = N’dbo’, @force_reinit_subscription = 1, @column_tracking = N’false’, @subset_filterclause = N”, @vertical_partition = N’false’, @verify_resolver_signature = 1, @allow_interactive_resolver = N’false’, @fast_multicol_updateproc = N’true’, @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N’true’, @compensate_for_errors = N’false’, @stream_blob_columns = N’false’, @partition_options = 0
You can run this query against the Published database to see what articles range is full or have very few values left:
>>>
select a.name,
max_used=max_used,
diff_pub_range_end_max_used=range_end – max_used, –this tells how many values are left
pub_range_begin=range_begin,
pub_range_end=range_end
from dbo.MSmerge_identity_range b ,
sysmergearticles a
where
a.artid = b.artid
and is_pub_range=1
order by max_used desc
name max_used diff_pub_range_end_max_used pub_range_begin pub_range_end
————– ————————————— ————————————— ————————————— ————-
tblCity 2147483647 0 2147477647 2147483647
tblCity1 6001 2147477646 1 2147483647
As you see from above diff_pub_range_end_max_used column is zero for tblCity.
When Merge agent runs depending on how many servers are involved it has to allocate 2 ranges for each.
In the example above we have Publisher and 2 subscribers and @identity_range is 1000. So, we will have to allocate range for 3 servers i.e., 3 * (2*1000) = 6000
Our diff_pub_range_end_max_used should be greater than 6000, only then we will be able to allocate a new range for all the servers.
To resolve the issue.
Remove tblCity table from publication.
Change the datatype from int to bigint and add this table back to publication.
Then generate a new snapshot. It will generate snapshots for all articles, but only this 1 table will be added back to the existing Subscribers.
Microsoft Tech Community – Latest Blogs –Read More