by Contributed | Dec 24, 2021 | Technology
This article is contributed. See the original author and article here.
Overview:
We sometimes see customers asking questions related to a discrepancy between the server storage usage and their expectations on the actual data usage. In this blog we will go through what can cause that and how to overcome from this situation.
Solution:
In this section, I am listing down some thoughtful insights and recommendations to breakdown the storage usage to some extent.
1) First and foremost, monitor the server storage usage using the available Azure MySQL Metrics:
Storage percentage |
Percent |
The percentage of storage used out of the server’s maximum. |
Storage used |
Bytes |
The amount of storage in use. The storage used by the service may include the database files, transaction logs, and the server logs. |
Server Log storage percent |
Percent |
The percentage of server log storage used out of the server’s maximum server log storage. |
Server Log storage used |
Bytes |
The amount of server log storage in use. |
Server Log storage limit |
Bytes |
The maximum server log storage for this server. |
Storage limit |
Bytes |
The maximum storage for this server. |
2) The following queries can help you to have insights upon the database storage usage:
- run below query to know each schema usage with respect to data and index space
SELECT table_schema, SUM(data_length + index_length)/1024/1024 AS total_mb, SUM(data_length)/1024/1024 AS data_mb, SUM(index_length)/1024/1024 AS index_mb, COUNT(*) AS tables, CURDATE() AS today
FROM information_schema.tables
GROUP BY table_schema ORDER BY 2 DESC;
- Leverage below query to get insights on tablespaces capacity
SELECT FILE_NAME, TABLESPACE_NAME, TABLE_NAME, ENGINE, INDEX_LENGTH, TOTAL_EXTENTS, EXTENT_SIZE, (TOTAL_EXTENTS * EXTENT_SIZE)/1024/1024 AS "size in MB"
from INFORMATION_SCHEMA.FILES
ORDER BY 8 DESC;
- Filter out to get temporary tablespaces information
SELECT FILE_NAME, TABLESPACE_NAME, TABLE_NAME, ENGINE, INDEX_LENGTH, TOTAL_EXTENTS, EXTENT_SIZE, (TOTAL_EXTENTS * EXTENT_SIZE)/1024/1024 AS "size in MB"
from INFORMATION_SCHEMA.FILES
where file_name like '%ibtmp%';
- To get the actual file size on the disk, run below query against INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES:
SELECT * FROM information_schema.INNODB_SYS_TABLESPACES order by file_size desc;
- Look for the top 10 tables using below query.
SELECT CONCAT(table_schema, '.', table_name),
CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows,
CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,
CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx,
CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
ROUND(index_length / data_length, 2) idxPct
FROM information_schema.TABLES
ORDER BY data_length + index_length DESC
LIMIT 10;
3) Examine the following server parameters which might contribute into the storage usage growth
This setting will tell InnoDB if it should store data and indexes in the shared tablespace or in a separate .ibd file for each table. Having a file per table enables the server to reclaim space when tables are dropped, truncated, or rebuilt. Databases containing a large number of tables should not use the table per file configuration. More information, see MySQL :: MySQL 5.7 Reference Manual :: 14.6.3.2 File-Per-Table Tablespaces.
In case you set the binlog_expire_logs_seconds to a higher value, then the binary logs will not get purged soon enough and can lead to increase in the storage billing. More information, see MySQL documentation. You can monitor the binary logs usage using MySQL command:
It worth to mention that when you configure slow-query-log
log_output parameter to
“File”, slow query logs will be written to both the local server storage as well as
Azure Monitor Diagnostic Logs, however, there are 7 GB storage limit for the server logs which is available free of cost and cannot be extended. More information in Azure MySQL documentation: Slow query logs – Azure Database for MySQL | Microsoft Docs.
4) Leverage MySQL OPTIMIZE TABLE or Rebuild Tables/Indexes to reclaim the unused space.
The bloated data can be cleaned by calling OPTIMIZE TABLE or Rebuild to reclaim some unused space.
Note:- OPTMIZE TABLE will trigger an exclusive table lock. it’s recommended that you DO NOT run in peak hours. |
5) Enable Storage Auto-grow and set up an alert
Last but not least, we always recommend that you enable storage auto-grow or set up an alert to notify you when your server storage is approaching the threshold so you can avoid getting into the read-only state. For more information, see the documentation on how to set up an alert.
Note:- Keep in mind that storage can only be scaled up, not down. |
I hope you find this article helpful. If you have any feedback, please do not hesitate to provide it in the comment section below.
Ahmed S. Mazrouh
by Contributed | Dec 24, 2021 | Technology
This article is contributed. See the original author and article here.
When a List Isn’t Enough: A Case for Dataverse and Model-Driven Apps
Norm Young is an Office Apps & Services MVP working as the Director of Collaborative Analytics at UnlimitedViz, the makers of tyGraph. He is focused on SharePoint and the Power Platform and shares his passion through his blog and speaking at conferences. Norm is also an active community contributor and helps to organize the Citizen Developers User Group. Follow him on Twitter @stormin_30 and visit his blog.
How to use Azure proximity placement groups #Azure #SAP #Latency
Robert Smit is a EMEA Cloud Solution Architect at Insight.de and is a current Microsoft MVP Cloud and Datacenter as of 2009. Robert has over 20 years experience in IT with experience in the educational, health-care and finance industries. Robert’s past IT experience in the trenches of IT gives him the knowledge and insight that allows him to communicate effectively with IT professionals. Follow him on Twitter at @clusterMVP
C# 10.0: Nice Little Features
Jaliya Udagedara is a Developer Technologies MVP based in Auckland, New Zealand. Originally from Sri Lanka, Jaliya is currently working as a Technical Lead for a US-based software development company. He has found his most significant interest in the world of Microsoft Software Development Technologies and Azure Stack. He likes to write blog posts and maintains his personal blog. Follow him on Twitter @JaliyaUdagedara
Building IsChristmasTree with CustomVision.ai
Lee Englestone is an innovative Dev Manager who likes to operate in the area where technology, product, people and business strategy converge. Lee, from the UK, is constantly working on side projects, building things and looking for ways to educate the .NET community in great technologies. He is the creator of Visual Studio Tips, Hackathon Tips and Xamarin Arkit. For more, see Lee’s blog and Twitter @LeeEnglestone.
Teams Real Simple with Pictures: Aligning Teams Preview with the Office Current Channel Preview
Chris Hoard is a Microsoft Certified Trainer Regional Lead (MCT RL), Educator (MCEd) and Teams MVP. With over 10 years of cloud computing experience, he is currently building an education practice for Vuzion (Tier 2 UK CSP). His focus areas are Microsoft Teams, Microsoft 365 and entry-level Azure. Follow Chris on Twitter at @Microsoft365Pro and check out his blog here.
by Contributed | Dec 24, 2021 | Technology
This article is contributed. See the original author and article here.
Call Summary
It’s the perfect time to visit the Microsoft 365 tenant – script samples gallery (123 scenarios and 168 scripts) today. Sign up and attend an AMA and other events in January-February hosted by Sharing is Caring. At the same time, sign up for the PnP Recognition Program. PnP project version releases this week include – PnP .NET Libraries – PnP Framework v1.8.0 GA, PnP Core SDK v1.5.0 GA, PnP PowerShell v1.9.0 GA and Yo teams – yoteams-build-core Next: v1.6.0-next.1. To see current releases and latest updates/nightly builds, access the Repos via the links in table below. 8 new/updated script samples, 1 Microsoft Teams sample and 5 Power Apps samples were delivered this week!
Open-source project status: (Bold indicates new this call)
* Note: While version releases are periodic, nightly releases are nightly! Subscribe to nightly releases for the latest capabilities.
The host of this call was David Warner II (Catapult Systems) | @DavidWarnerII. Q&A takes place in chat throughout the call.
Actions:
- Opt into PnP Recognition Program | https://aka.ms/m365pnp-recognition
- Join us at the next Microsoft 365 platform call 28th of December @ 8 AM PT, Social gathering for coffee/tea or your chosen drink. Got questions? – Join and let’s have a chat.
- Provide feedback to project teams or roll up your sleeves and join a project team.
- Holiday Break Challenge!!! Choose and update an SPFx sample v1.7, v1.8, v1.9 and earn a special Credly badge.
- Create a sample, i.e., for Teams visit https://aka.ms/teams-sample-wishlist
- Register for Sharing is Caring Events:
- Ask me anything (AMA) PnP Search – Tuesday, January 11th, 9am PST
- First Time Contributor Session – Tuesday, January 25th, 2pm PST
- Community Docs Session – 2022 Sessions Coming Soon
- Power Platform Samples – First Time Contributor – Tuesday, January 18th, 1pm PST
- PnP – SPFx Developer Workstation Setup – Tuesday, February 8th, 2pm PST
- PnP SPFx Samples – Solving SPFx version differences using Node Version Manager – Wednesday, January 26th, 7am PST
- First Time Presenter – Wednesday, February 9th, 9am PST
- More than Code with VSCode – Tuesday, February 15th, 2pm PST
- Maturity Model Practitioners – Tuesday, January 18th, 7am PST (every 3rd Tuesday of month, 7:00am PT)
- Getting Started with Viva Connection ACEs (2-part session) – February 202
- PnP Office Hours – 1:1 session – Register
- PnP Buddy System – Request a Buddy
- Download the recurrent invite for this call – http://aka.ms/m365-dev-sig
- Call attention to your great work by using the #PnPWeekly on Twitter.
Microsoft Teams Development Samples: (https://aka.ms/TeamsSampleBrowser)
Microsoft Power Platform Samples: (https://aka.ms/powerplatform-samples)
Script Samples: (https://aka.ms/script-samples)
5 new and 3 updated scenario samples contributed by
– Many thanks!
Together Mode!
Together here during the holiday’s because – why not? Great seeing everyone today. Happy holidays and new year to you and family.
Demos delivered in this session
Teams Meetings Apps: Emoji feedback with bot and Adaptive Card universal action model – enables meeting participants to provide feedback at the end of the meeting using a simple emoji. Action is triggered by end of meeting event in Activity Handler. The bot sends an adaptive card to the meeting’s chat with 5 emoji buttons requesting feedback. Once voted, voters see current sentiment of all voters. Uses adaptive card universal action model (UAM).
Introduction to Microsoft 365 Universal Sample Gallery – opens with a cleaver Night Before Christmas story of expectations, deadlines, and miracles. A wish granted – a single curated access point for samples from GitHub Repos encompassing the Microsoft 365 suite of products. Samples are vetted, metadata tagged/refinable by product, technology, author, compatibility…, include supporting documents, author profile, and demo video, if exist. Site is launched, instruction on how to request and/or deliver samples.
Thank you for your work. Samples are often showcased in Demos. Request a Demo spot on the call https://aka.ms/m365pnp/request/demo
Topics covered in this call
Resources:
Additional resources around the covered topics and links from the slides.
General resources:
Upcoming Calls | Recurrent Invites:
General Microsoft 365 Dev Special Interest Group bi-weekly calls are targeted at anyone who’s interested in the general Microsoft 365 development topics. This includes Microsoft Teams, Bots, Microsoft Graph, CSOM, REST, site provisioning, PnP PowerShell, PnP Sites Core, Site Designs, Microsoft Flow, PowerApps, Column Formatting, list formatting, etc. topics. More details on the Microsoft 365 community from http://aka.ms/m365pnp. We also welcome community demos, if you are interested in doing a live demo in these calls!
You can download recurrent invite from http://aka.ms/m365-dev-sig. Welcome and join in the discussion. If you have any questions, comments, or feedback, feel free to provide your input as comments to this post as well. More details on the Microsoft 365 community and options to get involved are available from http://aka.ms/m365pnp.
“Sharing is caring”
Microsoft 365 PnP team, Microsoft – 24th of December 2021
by Contributed | Dec 23, 2021 | Technology
This article is contributed. See the original author and article here.
Overview:
One of our Azure customers raised a support ticket to find out the creation dates for all their resources on their Azure subscription.
This blog shows you one of the ways to achieve that.
Step 1:
Create an Azure service principal with the az ad sp create-for-rbac command. Make sure to copy the output, as it is required in the next steps.
Input
az ad sp create-for-rbac –name serviceprincipalname –role reader
Output
Creating ‘reader’ role assignment under scope ‘/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx’
The output includes credentials that you must protect. Be sure that you do not include these credentials in your code or check the credentials into your source control. For more information, see https://aka.ms/azadsp-cli
‘name’ property in the output is deprecated and will be removed in the future. Use ‘appId’ instead.
{
“appId”: “xxxxxxx”,
“displayName”: “serviceprincipalname”,
“name”: “xxxxxxx”,
“password”: “xxxxxxx”,
“tenant”: “xxxxxxx”
}
Step 2:
Generate the bearer token using Postman client – Postman API Platform
Type in the below URL with your tenant ID for a POST call
https://login.microsoftonline.com/xxxxtenant-IDxxxxxx/oauth2/token
Click on “Body” and type in the details from the output of Step 1 as following.
Note: Client ID = App ID.
Content-Type: application/x-www-form-urlencoded
grant_type=client_credentials
client_id=xxxxxxxxxxxxxxxxxxxxxx
client_secret=xxxxxxxxxxxxxxxxxxxxxxxxxx
resource=https://management.azure.com/
Click on “Send” and you will see a JSON response as below with a bearer/access token
Copy the access token which will now be used in Step 3 for a Get call.
Step 3:
Make the get call to get the creation dates of your resources on the subscription. You may also do it for a single resource by filtering as needed in the URL.
Get URL – https://management.azure.com/subscriptions/XXXX-Your-Subscription- IDXXXX/resources?api-version=2020-06-01&$expand=createdTime&$select=name,createdTime
Select “Bearer Token” in the Authorization tab and paste the access token copied from Step 2.
Click on Send and enjoy the results you wanted!
Credits to @P V SUHAS for the guidance.
by Contributed | Dec 22, 2021 | Technology
This article is contributed. See the original author and article here.
Multi-Tier Cloud Security (MTCS) SS584:2020 Overview
MTCS, a cloud security standard, was developed by the Information Technology Standards Committee (ITSC) in Singapore and published in November 2013 for its first version. The ITSC promotes and facilitates national programs to standardize IT and communications, and Singapore’s participation in international standardization activities. Since 2014, Microsoft became one of the first cloud service providers that has received the MTCS certification, for both Microsoft Azure cloud platform and Office 365 services.
In November 2021, Microsoft again successfully attained the Multi-Tier Cloud Security (MTCS) Standard for Singapore Level-3 High Impact certification for Office 365 family of services, this time with the renewed version SS 584:2020. Office 365 services included in scope are:
- Exchange Online
- SharePoint
- Information Protection
- Microsoft Teams (including Azure Communication Services)
- Skype for Business
- Office Online
- Office Services Infrastructure
- Microsoft/Office 365 Suite user experience
- Delve/Loki
This renewed SS 584:2020 standard was approved and published in October 2020. Compared with the last SS 584:2015 standard, the renewed version has major updated requirements including:
- List of applicability and compensatory controls with justifications.
- Detailed Risk Assessment Requirements that may apply to cloud services.
- Third-party providers must receive compliance or attestations to international standards and provide access to the evidence associated.
- Security hardening requirements and service availability for Edge Node services that are used for performance enhancement.
By providing the implementation details of the management and technical controls in place along with their supporting evidence, Office 365 was able to demonstrate how its information systems can support the Level 3 confidentiality, integrity, and availability requirements from the standard. This Level 3 certification means that in-scope Office 365 cloud services can host high-impact data for regulated organizations with much stricter security requirements. It’s required for certain cloud solution implementations by the Singapore government.
Certification is valid for three years with a yearly surveillance audit conducted:
To whom does the standard apply?
It applies to businesses in Singapore that purchase cloud services requiring compliance with the MTCS standard.
What are the differences between MTCS security levels?
MTCS has a total of 535 controls that cover three levels of security:
- Level 1 is low cost with a minimum number of required baseline security controls. It is suitable for website hosting, testing and development work, simulation, and non-critical business applications.
- Level 2 addresses the needs of most organizations that are concerned about data security with a set of more stringent controls targeted at security risks and threats to data. Level 2 is applicable for most cloud usage, including mission-critical business applications.
- Level 3 is designed for regulated organizations with specific requirements and those willing to pay for stricter security requirements. Level 3 adds a set of security controls to supplement those in Levels 1 and 2. They address security risks and threats in high-impact information systems using cloud services, such as hosting applications with sensitive information and in regulated systems.
How do I get started with my organization’s own compliance effort?
The MTCS Certification Scheme provides guidance on audit controls and security requirements.
Can I use Microsoft’s compliance in my organization’s certification process?
Yes. If you have a requirement to certify your services built on these Microsoft cloud services, you can use the MTCS certification to reduce the impact of auditing your IT infrastructure. However, you are responsible for engaging an assessor to evaluate your implementation for compliance, and for the controls and processes within your own organization.
Continue the conversation by joining us in the Microsoft 365 Tech Community! Whether you have product questions or just want to stay informed with the latest updates on new releases, tools, and blogs, Microsoft 365 Tech Community is your go-to resource to stay connected
Recent Comments