What is MYSQL, and why it is a popular choice?

MySQL is an open-source relational database management system (RDBMS) that is widely used for managing and organizing large amounts of structured data. It is one of the most popular database systems in the world and has a strong reputation for reliability, scalability, and ease of use.

Here are some reasons why MySQL is commonly used:

1. Relational Database Management System: MySQL follows the relational database model, which allows you to define relationships between different data tables. This makes it easier to organize and retrieve data efficiently.
2. Open Source: MySQL is an open-source database, which means it is freely available to use, modify, and distribute. This makes it an attractive option for both small-scale and large-scale projects, as it eliminates licensing costs.
3. Performance: MySQL is designed to be fast and efficient. It can handle large volumes of data and perform complex queries quickly. With proper indexing and optimization, MySQL can deliver high performance even with heavy workloads.
4. Scalability: MySQL offers excellent scalability options. It can handle increasing amounts of data and traffic without significant performance degradation. It supports replication, which allows you to create multiple copies of your database to distribute the load and improve availability.
5. Flexibility: MySQL supports various platforms and operating systems, making it highly flexible. It has connectors and APIs for different programming languages, allowing developers to integrate it seamlessly into their applications.
6. Wide Community Support: MySQL has a large and active community of developers and users worldwide. This means there are abundant resources, tutorials, forums, and online documentation available, making it easier to get help, find solutions, and stay updated with the latest developments.
7. Compatibility: MySQL is compatible with various operating systems, including Linux, Windows, and macOS. It also supports multiple programming languages and offers standard SQL syntax, making it accessible and familiar to developers.
8. Reliability: MySQL is known for its stability and robustness. It has undergone extensive testing and has a proven track record in handling critical data in production environments.
9. Security: MySQL provides a range of security features to protect your data. It supports user authentication, access control, encryption, and other mechanisms to ensure the confidentiality and integrity of your data.

Know More…..

A leading provider of application and server management solutions, today announced SQL Page Viewer, a free tool that allows database administrators (DBAs) to easily view page data in one click to improve troubleshooting, gain insight into how SQL Server data is stored, and more easily navigate across pages. The solution is IDERA’s latest free offering, reflecting the company’s broad support for the SQL community, which also includes the community.idera forum where users gather to discuss ideas and share feedback directly with the IDERA product development team.

“We have extensive DBA expertise across the company and are honored to give back to the SQL community with easy-to-use tools that address many of the most common database challenges,” said Josh Stephens, vice president of product strategy at IDERA. “Our library of free tools, whitepapers, community site and free educational activities are incredible resources for DBAs around the world and offer technical solutions and best practices that enable them to deliver real value to their organizations.”

All of IDERA’s free tools solve specific pain points experienced by DBAs and are easy to deploy and use at no cost. Tools like SQL Page Viewer are perfect solutions for DBAs who are new to the trade or do not yet have the budget or need for more comprehensive solutions.

In addition to supporting the SQL community with free tools and a community for professionals to share ideas, IDERA frequently sponsors SQL User Group events and SQL Saturdays. These events enable IDERA to support free technical sessions and training for thousands of SQL Server professionals worldwide and allow customers to give in-person feedback to IDERA’s product team.

Argenis Without Borders, a grassroots effort to raise money for Doctors Without Borders, is one of many organizations that have benefited from IDERA’s support of the SQL community.

“We were blown away by the enthusiasm and support from the SQL community when we began our fundraising effort for Doctors Without Borders,” said Kirsten Benzel, co-organizer of Argenis Without Borders. “Many caring individuals in the SQL Family were quick to jump in and donate and participate. When IDERA offered to support us as well, we were very excited for the additional exposure. This entire experience has been an incredible testament to how much people and corporations care about the members of this community.”

IDERA will showcase SQL Page Viewer and other SQL products at PASS Summit 2014 in Seattle, WA in booth #225. PASS Summit 2014 is being held November 4-7, 2014 at the Washington State Convention & Trade Center.

Stop digging through confusing DBCC Page output to troubleshoot SQL Server issues. With a single click, SQL Page Viewer gives you quick insight into how the SQL Server data is stored. Get the page level data you need to quickly diagnose locking, blocking or wait events caused by a given table, index or corrupted data.

• Displays page level data summary and detail information
• Elegant GUI design for fast access in 1 click
• Select any SQL server and database table
• Navigate to any page data quickly in a tree view
• Launch directly within SSMS for optimal usability

Find SQL Server page data: Displays page-level data summary and detail information. Elegant GUI design for fast access in a single click. Select any SQL Server and database table. Navigate to any page data quickly in a tree view. Launch directly within SQL Server Management Studio (SSMS) for optimal usability. Stop digging through complex database console commands (DBCC) page output to troubleshoot SQL Server issues. With a single click, SQL Page Viewer gives you quick insight into how the SQL Server data is stored. Get the page level data you need to quickly diagnose locking, blocking or wait events caused by a given table, index or corrupted data. View page level data at a glance: See page level data in both a summary and detail information view to gain insight into the internals of how the SQL Server data is stored.

Select any SQL Server and database table. Navigate to any page quickly using the tree view. Elegant design and ease of use: Download and install in a few minutes. Clean, simple UI offers the ability for users to see page data quickly in a single click to help troubleshoot issues. Find the page data you need without worrying about cryptic trace flags and database console commands (DBCC) parameters. Integrates with SQL Server Management Studio (SSMS): Designed to launch directly within SQL Server Management Studio for optimal usability and efficient workflow. Use cases: Use this tool for a variety of diagnostic and analysis purposes including: Need to look up a page number referenced in information from diagnosing a locking, blocking, or wait type event. Troubleshoot database issues where you only have page information to go on. Want to learn more about a table, index, or potentially corrupted data on a page. See what the database engine is doing with data and index pages when certain database actions occur.

Pages and Extents Architecture Guide

The page is the fundamental unit of data storage in SQL Server. An extent is a collection of eight physically contiguous pages. Extents help efficiently manage pages. This guide describes the data structures that are used to manage pages and extents in all versions of SQL Server. Understanding the architecture of pages and extents is important for designing and developing databases that perform efficiently.

Pages and Extents

The fundamental unit of data storage in SQL Server is the page. The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages numbered contiguously from 0 to n. Disk I/O operations are performed at the page level. That is, SQL Server reads or writes whole data pages.

Extents are a collection of eight physically contiguous pages and are used to efficiently manage the pages. All pages are organized into extents.

Pages

Take a regular book: all content in it is written on pages. Similar to a book, in SQL Server all the data rows are written on pages. In a book, all pages are the same physical size. Similarly, in SQL Server all data pages are the same size – 8 kilobytes. In a book most pages contain the data – the main content of the book – and some pages contain metadata about the content – for example table of contents and index. Again, SQL Server is not different: most pages contain actual rows of data which were stored by users; these are called Data pages and text/image pages (for special cases). The Index pages contain index references about where the data is and finally there are system pages that store variety of metadata about the organization of the data (PFS, GAM, SGAM, IAM, DCM, BCM pages). See table below for page types and their description.

As mentioned, in SQL Server, the page size is 8-KB. This means SQL Server databases have 128 pages per megabyte. Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.

The following table shows the page types used in the data files of a SQL Server database.

Data rows are put on the page serially, starting immediately after the header. A row offset table starts at the end of the page, and each row offset table contains one entry for each row on the page. Each row offset entry records how far the first byte of the row is from the start of the page. Thus, the function of the row offset table is to help SQL Server locate rows on a page very quickly. The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.

Large Row Support

Rows cannot span pages, however portions of the row may be moved off the row’s page so that the row can actually be very large. The maximum amount of data and overhead that is contained in a single row on a page is 8,060 bytes (8-KB). However, this does not include the data stored in the Text/Image page type.

This restriction is relaxed for tables that contain varchar, nvarchar, varbinary, or sql_variant columns. When the total row size of all fixed and variable columns in a table exceeds the 8,060-byte limitation, SQL Server dynamically moves one or more variable length columns to pages in the ROW_OVERFLOW_DATA allocation unit, starting with the column with the largest width.

This is done whenever an insert or update operation increases the total size of the row beyond the 8,060-byte limit. When a column is moved to a page in the ROW_OVERFLOW_DATA allocation unit, a 24-byte pointer on the original page in the IN_ROW_DATA allocation unit is maintained. If a subsequent operation reduces the row size, SQL Server dynamically moves the columns back to the original data page.

Row-Overflow Considerations

As mentioned earlier, a row cannot reside on multiple pages and can overflow if the combined size of variable-length data-type fields exceeds the 8060-byte limit. To illustrate, a table may be created with two columns: one varchar(7000) and another varchar (2000). Individually neither column exceeds the 8060-byte, but combined they could do so, if the entire width of each column is filled. SQL Server may dynamically move the varchar(7000) variable length column to pages in the ROW_OVERFLOW_DATA allocation unit. When you combine varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns that exceed 8,060 bytes per row, consider the following:

• Moving large records to another page occurs dynamically as records are lengthened based on update operations. Update operations that shorten records may cause records to be moved back to the original page in the IN_ROW_DATA allocation unit. Querying and performing other select operations, such as sorts or joins on large records that contain row-overflow data slows processing time, because these records are processed synchronously instead of asynchronously.
  Therefore, when you design a table with multiple varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns, consider the percentage of rows that are likely to flow over and the frequency with which this overflow data is likely to be queried. If there are likely to be frequent queries on many rows of row-overflow data, consider normalizing the table so that some columns are moved to another table. This can then be queried in an asynchronous JOIN operation.
• The length of individual columns must still fall within the limit of 8,000 bytes for varchar, nvarchar, varbinary, sql_variant, and CLR user-defined type columns. Only their combined lengths can exceed the 8,060-byte row limit of a table.
• The sum of other data type columns, including char and nchar data, must fall within the 8,060-byte row limit. Large object data is also exempt from the 8,060-byte row limit.
• The index key of a clustered index cannot contain varchar columns that have existing data in the ROW_OVERFLOW_DATA allocation unit. If a clustered index is created on a varchar column and the existing data is in the IN_ROW_DATA allocation unit, subsequent insert or update actions on the column that would push the data off-row will fail. For more information about allocation units, see Table and Index Organization.
• You can include columns that contain row-overflow data as key or nonkey columns of a nonclustered index.
• The record-size limit for tables that use sparse columns is 8,018 bytes. When the converted data plus existing record data exceeds 8,018 bytes, MSSQLSERVER ERROR 576 is returned. When columns are converted between sparse and nonsparse types, Database Engine keeps a copy of the current record data. This temporarily doubles the storage that is required for the record.
• To obtain information about tables or indexes that might contain row-overflow data, use the sys.dm_db_index_physical_stats dynamic management function.

Extents

Extents are the basic unit in which space is managed. An extent is eight physically contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.

SQL Server has two types of extents:

• Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.
• Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.

Up to, and including, SQL Server 2014 (12.x), SQL Server does not allocate whole extents to tables with small amounts of data. A new table or index generally allocates pages from mixed extents. When the table or index grows to the point that it has eight pages, it then switches to use uniform extents for subsequent allocations. If you create an index on an existing table that has enough rows to generate eight pages in the index, all allocations to the index are in uniform extents. However, starting with SQL Server 2016 (13.x), the default for all allocations in the database is uniform extents.

Managing Extent Allocations and Free Space

The SQL Server data structures that manage extent allocations and track free space have a relatively simple structure. This has the following benefits:

• The free space information is densely packed, so relatively few pages contain this information.
  This increases speed by reducing the amount of disk reads that are required to retrieve allocation information. This also increases the chance that the allocation pages will remain in memory and not require more reads.
• Most of the allocation information is not chained together. This simplifies the maintenance of the allocation information.
  Each page allocation or deallocation can be performed quickly. This decreases the contention between concurrent tasks having to allocate or deallocate pages.

Major Features of SQL Fragmentation Analyzer

• Installation Instructions For Windows

Installation Instructions For Windows

A) Click the Windows “Start” button and select “All Programs” and then point to SQL Page Viewer

B) RDP Connection: To connect to the operating system,

1) Connect to virtual machine using following RDP credentials :

• Hostname: PublicDNS  / IP of machine
• Port : 3389

Username: To connect to the operating system, use RDP and the username is Administrator.
Password : Please Click here  to know how to  get password .

C) Other Information:

1.Default installation path: will be on your root folder “C:\Program Files (x86)\Idera\SQL Page Viewer\”
2.Default ports:

• Windows Machines:  RDP Port – 3389
• Http: 80
• Https: 443

Configure custom inbound and outbound rules using this link

Installation Step by Step Screenshots