RSS

Overview On SQL Server Database Integrity Check

Problem

Sometimes users may have numerous of databases in their SQL Server. However, while working on database they may face a database integrity issue, as there can be integrity on several parts of a database i.e. Table, database, etc.

Solution

In general, the integrity of SQL Server database task includes the complete checking allocation as well as structure of integrity of the entire object in the stated database. It contains various checking database indexes as discussed below.

Determine Requirements

Firstly, check all the databases at high level periodically for the SQL Server database consistency errors. If there, is a large database then, check it table by table. Moreover, running all these on a set of schedule, it is important to run, as there are the chances of system failure, disk drive failures, etc.

If the database is small then, it is easy to run all the checks across the whole database. In SQL Server 2000 and 2005, all the maintenance plans are the only way for performing DBCC CHECKDB that makes easy in checking the complete database. As it is already discussed above that, it is a part of normal database maintenance process. If the user is not running the checks for SQL Server database integrity or if they are not sure then, take a time for implementation of all these procedures.

Useful Commands

There are mainly four different commands, which can be used for checking different levels of consistency that are there in the database as mentioned:

  • DBCC CHECKALLOC– Check the consistency of structures of disk space allocation, which are there for specified database.
  • DBCC CHECKCATALOG– Check for consistency of catalog, which is therein specified database.
  • DBCC CHECKDB– Check for consistency of catalog that is in specified database.
  • DBCC CHECKTABLE– Check for the integrity of all pages as well as structures, which makes indexed and table view.

DBCC CHECKDB command is widely used by almost all of the users as it checks the complete database. Whereas all the other options are useful for the quick spot-checking. All these options can be used for checking the consistency of particular portion of data and repairing any sort of corruption, which is found.

Running

Ad Hoc at the most basic level of all the commands can be executed interactively against a database. It is always suggested to run these commands during the off hours as these commands issue some problem while executing.

Maintenance Plans as discussed above that SQL Server 2000 and 2005 both can set the maintenance plans for running DBCC CHECKDB command. This is the only way to have the base options of maintenance plans.

Custom Jobs can be created on its own SQL Server Agent jobs for running the desired commands against the database.

Scheduling

The most beneficial time to run these commands is during the low usage times. SQL Server Agent is the best way to schedule jobs during any time. If the hardware is stable then, there is not any issue for running these commands. If user is facing regular hardware problems then, it is the best way for running all these checks frequently.

Output

There are many installations where the plans of maintenance are setup and SQL Server database integrity check is run. The problem is that no one checks the output that results in the corruption issues. For this, users need to check for the output from the commands that make sure that there are no issues. This can be done by reporting option with maintenance plans and by checking SQL Server logs error. Every time, one DBCC command is run and an entry level is made in SQL Server error log.

Handling Issues

If there is corruption in the database then, users want to have the correct action for the elimination for these issues. This is done by utilizing the option with DBCC CHECKLOC, DBCC CHECKDB, and DBCC CHECKTABLE.

Conclusion

After understanding, the users issue that they face while checking for the SQL Server data integrity. In the above discussion, we have discussed the way for SQL Server database integrity check that makes easy for users in resolving this issue.

 
Leave a comment

Posted by on January 9, 2017 in General topics

 

Tags:

Concept and basics of Temporal tables in SQL Server 2016

In this article I’ll cover all aspects of a new SQL Server 2016 feature, Temporal Tables (System-Versioned), including:

  • Introduction
  • What is a temporal table?
  • Why Temporal table?
  • How does temporal table work?
  • Consideration and limitation
  • Temporal tables vs CDC
  • Creation and configuration
  • Clean up and removal
  • References

Introduction

AS we know, Microsoft released SQL Server 2016 RTM version (13.00.1601.5) and in November of 2016 updated it by the latest CU (Security Bulletin MS16-136 (CU) KB #3194717) (13.0.2186.0) you can check this update from here.

To complete this artcile please check it here in SQLShack 

 
Leave a comment

Posted by on November 25, 2016 in SQL Server 2016

 

Tags: , , , ,

SQL Injection

sqli

SQL Injection (SQLi) refers to an injection attack wherein an attacker can execute malicious SQLstatements (also commonly referred to as a malicious payload) that control a web application’s database server. Such attack affects any website or web application.

An attacker can bypass a web application’s authentication and authorization mechanisms and retrieve the contents of an entire database. SQL injection can also be used to add, modify and delete records in a database.

In a 2012 study, it was observed that the average web application received 4 attack campaigns per month, and retailers received twice as many attacks as other industries.

A SQL injection needs two conditions to exists :

  • A relational database that uses SQL
  • A user controllable input which is directly used in an SQL query.

Subclasses of SQLi –

  1. Classic SQLi
  2. Blind or Inference SQLi
  3. Database management system-specific SQLi
  4. Compounded SQLi

Example—

sql-example-1

Here user need to provide user name and password, if attacker provides ‘or 0=0’ as the username and password then the query will be like this.

sql-example-3

Since the inputs provided by the attacker are valid in all circumstances, the query will return all records in the database.

And by this way an attacker will be able to view the sensitive information.

How to prevent SQLi—

  • Adopt an input validation technique where user input is checked against a set of rules.
  • Users should have least privileges on the database.
  • Don’t use ‘SA’ accounts for web applications.
  • Need to have application specific database user accounts.
  • Remove all stored procedures which are not in use.

————–

 
 

Troubleshooting “SQL Server Database Is In Recovery Pending State”

Overview

Users of SQL Server Database must be aware of Recovery states, that occurs when there is unintentional dropped SQL table or any other components that needs to be restored. The SQL Server Database is in recovery pending state at situations like restart of SQL Server, offline & online state of database or while restoring database from a backup. However, if there is any issue during this recovery process, error can be Read the rest of this entry »

 
Leave a comment

Posted by on August 30, 2016 in backups, problems

 

Tags:

SQL Server 2016 Row Level Security and its Implementation

Security has been always one the main concern by MS SQL Server. All the releases of SQL Server have some new security feature or enhancement of an existing feature. Similarly, the latest edition of SQL Server 2016 has many security features such as Always Encrypted, enhancement of Transparent Data Encryption, Dynamic Data Masking, and Row-Level Security are added.

Read the rest of this entry »

 
Leave a comment

Posted by on July 18, 2016 in SQL Server 2016

 

Tags:

SQL Server Management Studio July 2016 Hotfix Update

If you download the July release of SQL Server Management Studio 2016 Please go and download the SQL Server Management Studio July 2016 Hotfix update from here https://msdn.microsoft.com/en-us/library/mt238290.aspx
It is a very important fix that resolve issues produced in the July update that causes you miss important commands in the right-click menu on tables and stored procedures
Here are the Linked customer bug requests:

https://connect.microsoft.com/SQLServer/feedback/details/2883440/lost-table-design-and-edit-top-n-rows-in-tables-context-menu

 

 
Leave a comment

Posted by on July 14, 2016 in General topics

 

Alwayson Availability Groups for Disaster Recovery Solutions

Introduction

There are many users, who are not aware about disaster recovery planning with always-on availability groups. Even they do not know the terms that come in the utilization of Always-On Availability groups for disaster recovery planning. In the following section, we will discuss about the always-on availability groups for resolving the disaster Read the rest of this entry »

 
 

Tags: