Saturday, 28 March 2020

Understanding Disk Performance and IOPS in cloud platform

In the past many years, apart from working as DBA, I was working with multiple cloud technologies and actively involved in the migration process to cloud infrastructure. In between, I managed to get certified in AWS as Associate Solution Architect.

While working with database operation in the cloud,  the most challenging part was to understand the Storage/disk performance. In this post, we will discuss the IOPS and disk performance in both AWS and Azure platforms.

Before jumping into that, let us understand the size of the IO, This depends on two factors. The Allocation unit size of the disk and application requesting the IO operation. If the application IO request size is more than the size of the Allocation Unit Size (Block Size), OS split the IO request into smaller IO operations. For example, if an application requesting to open 10MB file residing in a disk with an allocation block size of 64 KB, the request will get divided into 160 requests with a size of 64KB (10 MB=10240 kb/64kb =160 IO operation). If the block size of the disk is 8 KB, this requires 1280 IO operations. To identify the Allocation Unit Size of the existing disk, run the below command from the command prompt  From the result look for Bytes Per Cluster, which is the allocation unit size of the disk. This is the value you set while formatting the drive.

C:\>fsutil fsinfo ntfsinfo D:  (Where D stands for the drive letter of your drive)

Now let us understand the concepts of IOPS and throughput. Let us assume we have a disk that supports 3000 IOPS with an allocation unit size of 64KB.  The maximum throughput of the disk is 

Throughput of the disk = 3000 IOPS X 64KB (size of the IO) = 192000KB =187.5 MB . 

As the IOPS is IO per second, throughput is  187.5 MB/sec. Whether the disk can achieve this max throughput limits depends on the application requesting for the IO operation. If the application request comes with smaller than the size of the Allocation unit size, we can't make use of the max throughput of the disk. Let us take an example of the application requesting for IO operation with the size of 8KB, then max throughput will be :

Throughput of the disk = 3000 IOPS X 4KB (size of the IO Operation) = 24000KB =23.44 MB/sec 

Let us keep these three points in mind while discussing further the performance of the disks in the cloud environment. The performance of the disks is controlled by IOPS and throughput limits. These limits are there in both disk and instance (VM) level. 

Before going into more detail, let us look into the different types of disk available on both platforms.
AWS, provide two major types of disks :
  • General Purpose SSD (gp2)
  • Provisioned IOPS SSD (io1
Apart from those, AWS provides a couple of other disk types that are not relevant for our discussion. gp2 disk comes with predefined IOPS based on the size of the disk. Amazon offers 3 IOPS per GB. A single volume can have a maximum of  16000  IOPS (16 Kib IO) and 250 Mib/s throughput based on the disk size.  To make it clear both 5.333 TB  (5333 GB * 3 IOPS=~16000) and 10 TB (10240 GB*3 IOPS=30720 =16000(max limit per volume) provides 16000 IOPS and maximum throughput od 250 Mib/s . If the maximum throughput is 250 Mib/s with 16000 IOPS, what is the IO size 

IO size = 250MB=(256000 KB) / 16000 IOPS =16 KB.

That tells us that one IO operation can read /write a maximum of 16KB. It is very important to look into the size of the IO disk supports while reading the IOPS parameter of the disk. Let us take the same example of opening a 10MB file that resides on disk with the allocation unit size of 64KB. In this case, it requires a 640 IO operation. If the same files reside in a disk with the allocation unit size of 4KB, then it requires 1280 IO Operation. So the number of IO operation requires to complete one IO request is =

                                      Size of the IO request  (in KB)                                                                       
MIN(size of the IO request, size of the IO supported by the disk, Allocation unit size of the disk)

In this :
size of the IO request: decided by the application design
size of the IO supported by the disk: decided by the manufacture (provider) of the disk
Allocation unit size of the disk: decided the by the user who is configuring the disk in VM

All these three plays a vital role in the performance of the disk.

With the provisioned IOPS (io1) disk, the user can define the IOPS of the disk. The user needs to pay an extra amount for the IOPS of the ioI disk, whereas this is included in the cost of the disk for gp2 disks. Io1 disk supports a maximum of 64000(16Kib I/O) IOPS and 1000MiB/s throughput. The maximum throughput is nothing but 64000*16=1024000KB=1000MiB.

Apart from this, instance(ec2) IOPS and throughput throttling comes into the picture and plays a role in the performance of the disk. For example, 13.xlarge support maximum throughput of 106.25Mib/s and 6000 IOPS. If you attach a gp2 disk with the capacity of 5 TB to this instance, we will not able to make use of the maximum IOPS and throughput of the disk as throttling happen at the instance level. The disk can support 5TB=5120GBX3 IOPS =15360 IOPS and maximum throughput of 15360X16KB=240MiB/s but we will not get the full benefit of this due to throttling in the instance level. To get the maximum benefit of disk performance, we need to attach this disk to i3.4xlarge which supports a maximum throughput of 437.5MiB/s and 16000 IOPS.

I have tried my level best to explain the elements that affect the performance of the disks in cloud platform . Keep these points in mind while selecting the ec2 instances and disk for your workload. We will discuss further on the azure side in another blog post.

Tuesday, 11 February 2020

SQL Server Backup and Restore Strategy Step by Step


Who is not aware of a SQL Database? It was the first business language introduced for E.F Codd’s Relational Database management system. In addition, MySQL, Oracle, Informix, Sybase, MS Access-all use SQL as the standard database query language. For such a vast application, INTEGRITY and SAFETY tasks become the crucial ones. In order to secure censorious data stored in our SQL Server databases, the SQL Server backup and restore component gives an essential safeguard for it. To lessen the risk of calamitous data loss, we need to back up our databases to preserve modifications and adaptions to our data on a regular basis. A prudent backup and restore strategy helps protecting databases against data loss caused by a variety of failures. 

Why we need Backup?

Having a backup of necessary data is the only way to keep the data protected.
  • By having a backup of our precious data, means to store copies of data in a safe, off-site location that secures us from potentially disastrous data loss.
With valid backups of a database, you may recover your data from the number of failures, such as:
  1. Encountering a Media failure.
  2. Running into user errors, for instance, dropping a table/row by mistake.
  3. Hardware failures, for instance, a damaged disk drive or permanent loss of a server.
  4. Running across natural calamities.
By applying SQL-Server Backup to Azure Blob storage service, you may make an off-site backup in a various region than your on-premises location, to use in the event of a natural disaster affecting your on-premises location.
  • Additionally, backups of a database are also convenient for routine administrative objects, such as making a copy of the database from one server to another, setting up Always On availability groups or database mirroring, and archiving. 


Backup Options:
  1. Differential-back-up, A data-backup that is based on the latest full backup of comprehension or partial database (the differential base) and that consists only the data that has elaborated since that base.
  2. Full backup A data-backup that includes all the data in a specific database and also enough log to allow for recovering that data.
  3. Log-backup, A backup of transaction logs that include all log records that were not backed-up in a previous log backup. (full recovery model)

Backup and restore strategies

A well-designed backup and restore strategy stabilize the business requirements for utmost data availability and reduced data loss, while considering the cost of maintaining and storing backups.
A backup and restore strategy hold a backup portion and a restore portion. The backup part of the approach defines:
  • The type and recurrence of backups
  • The nature & speed of the hardware that is needed for them
  • How backups are to be tested and evaluated.
  • Where and how back-up media is to be stored
The restore part of the strategy defines: 
  • Who is responsible for acting restores?
  • How restores must be executed to fulfill your dreams for database availability and lowering information loss.
  • How restores are tested and evaluated.
Scheming an effective backup and restore strategy needs careful planning, implementation, and testing. Testing is important: you do not have a backup strategy until you have efficiently restored backups in all the combinations that are included in your restore strategy and have tested the restored database for physical consistency. You must acknowledge a variety of factors. These include:

  • The goals of your organization concerning your production databases, specifically the requirements for availability and protection of information from loss or harm.
  • The nature of each database: its size, its usage patterns, the character of its content material, the requirements for its information, and so forth.
  • Constraints on resources, such as: hardware, employees, space for storing backup media, the bodily security of the stored media, and so on.
Here is the process to restore database from backup:
  • Here, we have 2 databases connected with server.

  • Right click on the DB and select Restore option as shown in figure:

  • Click Ok

Recovery Models
To start with, SQL Server comes up with three restoration models. The restoration model is a configurable database property that defines how the transactions are logged and the way the database is fall back. They include:
  1. Simple Recovery Mode: It is the simplest recovery model. Transactional log backups are not allowed and so recovery cannot be done for a point in time.
  2. Full Recovery Mode: Transactional log backups are allowed and so recovery is done for a point in time. It encourages minimal data loss and it should be preferred in production-type databases.
  3. Bulk-Logged Recovery Mode: This mode is best used when there are bulk-transactions occurring (e.g., nightly/monthly cycle) in databases. Full Recovery Mode can be directed to Bulk-Logged during the bulk-transactions and retrogressed during normal data activity.

Customizing a database-backup and healing process to the requirements of the organization may be very essential. Each organization has exclusive requirements for amount of data, acceptable data loss, and acceptable restoration time frames. Determining these for your organization will help you to choose the appropriate backup and restore options and to be prepared during any unforeseen situation that causes any system or database failures. Careful planning will help the limit data loss and reduce downtime during such occurrences.

Monday, 25 November 2019

How to Fix SQL Database Logical Consistency Based I/O Error

There can be several reasons that cause Database Logical Consistency based I/O error:

  • Unexpected system shutdown/crash or forced shutdown 
  • SQL administrator tries to query or modify SQL data

In both the circumstances, the Host instances on SQL server restart repeatedly or terminate the connection between SQL application and the database. The following error message is displayed in SQL Server error log:

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:1848; actual 0:0). It occurred during a read of page (1:1848) in database ID 10 at offset 0x00000000e70000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ABCDb.mdf’.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

When a SQL Server database experiences a logical consistency based error, the first step is to diagnose the cause of error. The following methods can help in detecting the error:

  1. Use Best Practice Analyzer (BPA) tool – BPA tool helps in detecting critical consistency error. This tool is specific to the SQL-version, meaning BPA 2008 is available with SQL 2008 and so on.
  2. Check Windows System at Event Log system level, driver or disk related errors
  3. Check file system integrity by running chkdsk command
  4. Run the diagnostics recommended by hardware manufacturers for the system or disk
  5. For SQL server 2008, run the utility SQLIOSim on the drive of the database that has reported I/O error.
  6. Contact hardware vendor or device manufacturer to verify that hardware requirements confirm with I/O requirements of SQL server. 
  7. Check and run DBCC CHECKDB in SQL server database. DBCC CHECKDB command helps to check logical and physical consistency of SQL database pages, rows, system table referential integrity, index relationships and other structural checks. In case the check fails, error is reported as a part of CHECKDB command. The following DBCC CHECKDB command can be used:


Executing above command highlights the problem in detail, and SQL database may display the I/O  error message as follows:

Msg 8906, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:1846) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).

Such error messages help us reach a conclusion that SQL Database logical consistency based I/O error is caused either due to hardware issues or due to corruption in SQL database. 
The following methods can be used to resolve logical consistency based IO error:

Method 1 – Check hardware and application connectivity 
Database inconsistency error can be resolved by establishing proper connectivity between the database and SQL application. 

Method 2 - Restore from SQL backup
The most feasible solution is to use backup for SQL database restoration. Before restoring from backup, check that: 
  1. You have a recent full backup
  2. Backup is updated, just prior to corruption, and not long ago so as to avoid critical data loss
  3. Corruption is at page level, as page level corruption issue can be resolved with the help of page-level restoration. 
Note: Page-level restoration is valid when you are dealing with large database and corruption is in only in one-page of SQL database.
Step 1: Use the following command for restoring SQL database from full backup

Backup the transaction log
BACKUP LOG PageLevelRestores TO
DISK = 'g:\PageLevelRestores_LOG1.bak'

Step 2: Perform the restoration change to reflect the changes online.

Backup the tail of the log...
BACKUP LOG PageLevelRestores TO
DISK = 'g:\PageLevelRestores_LOG_TAIL.bak'

Note: There is no need to restore full SQL server database if corruption is confined to a single page. You can restore database from the available backup for that page which is corrupted. Performing the following commands will help in restoring backup for a single page:
Restore all available log backups in the correct order
RESTORE LOG PageLevelRestores FROM
DISK = 'g:\PageLevelRestores_LOG1.bak'

-- Finally restore the tail log backup
RESTORE LOG PageLevelRestores FROM
DISK = 'g:\PageLevelRestores_LOG_TAIL.bak'

-- Finally finish with the restore sequence

Once the database backup has restored SQL database, run the query DBCC CHECKDB again to check that the select statement succeeds without SQL Database logical consistency-based I/O error. This command also checks that there is no data loss in this table. 

Limitations of SQL database backup:

  1. It is not possible to restore from SQL database backup when the available backup is obsolete.
  2. If Logical consistency based I/O error is spread across the SQL server database, then this method will not be valid.
  3. For those cases where faulty page exists in a non-clustered index, the SQL database can be fixed by dropping and recreating the index of SQL database. 

Method 3: Repair corrupt SQL database with REPAIR_ALLOW_DATA_LOSS

REPAIR_ALLOW_DATA_LOSS is the minimum repair level for the diagnosed errors. 

Notes: Before using REPAIR_ALLOW_DATA_LOSS, perform the following:
  1. Take a backup of SQL server database and save it with another name
  2. Set SQL database in Single user mode
  3. Get all Tables record count by using the following commands
SET @SQL='INSERT INTO #T_Info(T_Name,D_Count) SELECT '''+@T_Name+''',COUNT(*) FROM '+@T_Name+''

The following steps help to fix SQL database and resolve logical consistency based I/O error:
  • Run the command:
  • Once SQL database is repaired, repeat the process of ‘Table Record Count’ and compare with old record count. 
There must not be any difference between initial and final Table record count. 

REPAIR_ALLOW_DATA_LOSS may fix Database logical consistency-based IO error but there is a major issue of data loss, where an organization may experience loss of critical data. 

Alternative Solution

If above methods won't work then, give a try to a SQL Recovery Software. Stellar Repair for MS SQL repairs corrupt MDF and NDF files and restores all the database objects. It supports MS SQL 2019 and all the lower versions. 

In this post, we have highlighted the cause of SQL Database logical consistency-based I/O error and the methods to detect this error. 
Based upon the error, we have tried to find the correct resolution method. If hardware or system is responsible for the error, it is recommended to resolve the hardware-related issues, and in case DBCC CHECKDB reports consistency error, then try to restore SQL database by using an updated backup. 
If the issue is not resolved with hardware correction and backup then try to repair the database with the help of REPAIR_ALLOW_DATA_LOSS. This is the minimum level of repair to resolve all errors from CHECKDB, but this does not mean that it will actually fix the error. Furthermore, it may result in data loss. 
SQL repair tool helps in repairing corrupt MS SQL database including corrupt .mdf and .ndf files and recovers all database components – Tables, Triggers, Indexes, Keys, Rules, deleted records and Stored Procedures. It is of great help in times of crisis, as the software provides a definite repair solution and supports all SQL database versions including the recent and older ones. 

Wednesday, 9 October 2019

How to Reduce the Risk of SQL Database Corruption


Today we are going to learn how to prevent SQL database corruption. Let’s start with some facts about how database corruption can occur actually. The SQL database corruption could be completely unsystematic. There are ways from which you find the reason for corruption, but sometimes you are on the wrong track and can’t find the primary reason behind the corruption of the database. And that’s why you have CHKDSK command to figure out the reason for complications. It is much better to reduce the risk of corruption so that you don’t have to face these issues. As a user of SQL Server, you should know that your Server is sharp-witted to predict the problem and already have the solution to that issue. While there are a unit ton of articles floating around the net on the way to fix corruption in SQL database, in this article, we are going to discuss the ways to stop SQL database corruption.

In data-driven businesses, the littlest of bug among the structure information will bring entire operations down. Whereas fixing issues is Associate in nursing after-thought, preventing issues is wiser. Here is a unit the highest six ways that you'll be able to implement to stop SQL server information corruption. There are a lot of ways to detect the corruption in SQL database and remove it from the SQL server. But today I am going to explain 5 ways to reduce the risk of SQL database corruption.

  1. You should Backup your database regularly
  2.  Split your database into more than one part.
  3. You should customize Cron jobs for your database
  4. You should optimize your database regularly
  5. You should check your database file size and hard disk size sometimes all database is lost due to this issue.
  6. You should do hardware and software maintenance regularly.
  7. Alternative Solution.

  • Backup your database regularly:

You’ll have detected it some of thousand-fold already; however, this is that the best safeguard against losses. Backups are an excellent facilitate once databases develop issues. In such cases, backup files square measure won’t restore the corrupted databases, thereby preventing period. You must build a backup of your information regularly so that the possibility of SQL information corruption is scaled back. If the database corrupted, then I will suggest the SQL database repair software. Here is show you how you can back up using MS SQL Server.

After pressing the button you will see the following window:

Press the “OK” button. In this way we backup our files.

  • Split your database:

Split your information. Instead of projecting each object in one MDF file and sharing the complete factor over the network, split the data into 2 pieces: the front-end forms, reports, queries, and knowledge access pages, and also the back-end tables containing your knowledge. Place a duplicate of your front-end information on the native disc of every machine and link back to the tables that are present on a fileserver. It implies that way, less knowledge has got to be dragged around the network and exposed to corruption. Your information will run additional quickly and can enable you to create style changes to the face, whereas still, victimization lives knowledge.

  • Customization of Cron Jobs for your database:

Start Cron jobs to run at regular intervals to confirm no lag in the period.To do that, select a database and press tasks and Back Up:

On the Backup window select Script and Script Action to Job:

Go to Schedules page and press the New button:

Add the schedule of your preference:

  • Optimize your database:

Create scripts that may monitor and determine slow running queries within the info. Often, slow Associates in nested queries end in information issues and an overall lag by taking on over necessary resources. Produce scripts that may determine and optimize these queries and stabilize the SQL server. At a regular basis, do not forget to sporadically monitor and tune-in the server for making certain high performance. Certify that the connections to the server square measure restricted and optimized. The most effective way to maintain the general health of the database is to stay updated with the most recent updates, thus don’t forget to install the last service packs.

Here I show you how you can optimize your database select the last database option. After pressing the button “Database” you will see a new window look like below.

In available space section you will see an option to optimize the size of the database.

  • Checking database file size and storage

When you start employing a new SQL server database, information file size won't be a problem.  Also, the possibilities of big databases obtaining corrupt area unit are tiny. Therefore, it's a decent plan to separate databases of enormous sizes and maintain multiple smaller versions. It additionally helps in liberating up adequate space on the server. As shortly as you see that free area is slippery below the perfect price, begin engaged in liberating it up.

  • Maintenance of hardware and software

Regularly perform memory checks, network observation, hardware-error checks, etc. to avoid bugs and hardware failures. This sort of preventive measure should be distributed sporadically to track the standing of all hardware proactively, and if the requirement arises, to repair all detected problems. Virus and malware will prove very dangerous to your SQL server info. Don't fall prey to such malicious attacks and perpetually keep an eye fixed out for dead of the info. Also, from time to time, check for any traces of malware infections inside the info and use a decent code resolution to stay them cornered.

Image Ref: Hardware Repairing

  • Alternative Solution
Stellar Repair for MS SQL Server is a software that helps us to reduce risk in our databases. This SQL Recovery Software repairs .mdf and .ndf files. It also recovers our deleted records and supports MS SQL Server 2017, 2016, 2014, 2012, 2008 and older versions. After rebuild and recovery of the databases, Stellar Repair for MS SQL Server allows us to save files in multiple file extensions like HTML, XLS, CSV formats and .mdf files. If you keep your recovered file as a .mdf file extension, it will save your data as a new or live database. We will solve our multiple problems using Stellar Repair for MS SQL Server.


In this article, we studied how we can reduce the risks of MS SQL Database corruptions. We can make daily backups of our database. We will split, optimize, and customize, and schedule jobs in our database. We can also check our database size. We also studied an SQL recovery software. This software also helps us to solve multiple problems.