In this tutorial we will talk about basics of SQL Server, its history, versions and many more things that will be beneficial for a newbie. The aim of this tutorial is to give you an overview of SQL server in the simplest terms.

What is SQL?

SQL stands for Structured Query Language. It is a programming language designed to access and manipulates data in database (RDBMS).

What is SQL Server?

SQL Server is a RDBMS (Relational Database Management System) designed by Microsoft for enterprise environment. It is mostly used as back-end system and supports thousand of users simultaneously.

SQL Server History

The first version of SQL Server was released in 1989 by Microsoft. In 1990, Microsoft developed a new version of SQL Server for NT platform and in 1993 SQL Server 4.2 was released. After that Microsoft was recognized as second most popular vendor of relation database software.

SQL Server Versions:

Why SQL Server is better than other databases?

1. It has a very userfriendly interface.

2. It has integrationwith most popular and familiar database MS Excel.

3. Supports highavailability solutions like Log Shipping, Replication, Mirroring,Always On etc.

4. Maintenance plans caneasily be created.

5. It is the most securedatabase among all other.

6. Its Always On featureminimize downtime by 0.

7. It is very easyto restore database after corruption orany other disaster.

8. SQL Server ManagementStudio is great tool for DBA and developers.

9. It has easyinstallation process.

10. It is useful for bothsmall and large organization.

11. It can perform 1million commands per second.

Some Popular SQL Server commands

Select statement: It is used to select data.

Select column_name from table_name;

Create database: It is used to create a database.

Create database [database_name];

Create table: It is used to create a table.

Create table [table_name]

(

column_name1 data_type,

column_name1 data_type,

....

)

Insert into: It is used to insert new records.

Insert into table name Values (value1, value2...);

Update Statement: It is used to update existing records.

Update table name

set column1 = value1, column2 = value2

where some-column = some_value;

Delete statement: It is used to delete records.

Delete from table_name

where some_column = some_value;

Corruption in SQL server database

Like all other database SQL database also got corrupted due to various reasons. The main reason behind SQL database corruption (90% cases) is problem at I/O level i.e. problem with drivers, controllers etc. To prevent this type of corruption you should know the use of CHKDSK command that helps in scanning bad sectors, incorrect entries and other storage issues. Other reasons of corruption might be hardware malfunction, power failure, firmware bug etc. If you can't handle this type of corruption yourself then call your hardware vendor immediately. Hardware vendor should check firmware’s, drives versions to make sure that latest and recommended version is being running.

What are the consequences of SQL database corruption?

To check database for corruption first you need to run DBCC CHECKDB command and if it finds corruption in your database then go for its repair options. In case of damage, the response of SQL Server will depends on the level of corruption in database. If the level of damage is high then it might be possible that SQL Sever won't be able to load your database. In that case you may need to restore the whole database.

If the level of corruption is not that high and only one or two pages are affected then SQL Server might be able to load your database. These types of corruption can be handled easily with the help of DBCC CHECKDB repair options.

DBCC CHECKDB Repair Options

1. REPAIR_ALLOW_DATA_LOSS: It repairs & recoversSQL server database fromcorruption. This operation may cause some data loss.

2. REPAIR_FAST: Just for backward compatibility. There isno repair operation performed in this option.

3. REPAIR_REBUILD: It tries to repair the corruption andrebuild the database. There is no any loss of data in thisoperation.

How to prevent corruption?

The simple answer of this question is that you can't actually prevent database corruption. As I told you above, most of the time corruption occurs at disk subsystem level so you really can't prevent it. But that does not mean you can't do anything about corruption. SQL Server provides a number of great tools that you can use to detect and repair corruption. One more thing, the best way to deal with corruption in SQL database is to detect it early. Sometime it happens that corruption issue goes unnoticed for a long time and later it becomes very difficult to deal with.

What to do when corruption happens?

1. Backup: Take regular backup of your database including system database. Youcan take backup according to your database needs like Full DatabaseBackup, Differential Backup, Transaction Log Backup etc. I wouldalso suggest making a backup strategy for your database.

2. Don't everReboot your server when corruption happens: Rebooting server will make the matter worst because it can put yourdatabase in suspect mode and then it will become more difficult todeal with it.

3. Don't detachyour database: Detaching database atthe time of corruption make recovery of database even moredifficult.

4. Use of highavailability solutions: Microsoftprovides many high availability solutions like: Database Mirroring,Log Shipping, Replication to create a duplicate copy of database.You can use that duplicate copy in case of database corruption orfailure.

5. Run DBCCCHECKDB command: Runthis command to check the integrity of your database. If it detectscorruption then again run this command with its repair options. Thiscommand takes time in checking error so it is recommended to run itduring non production hours.

6. HavePatience: Let DBCC CHECKDB command finish their operations properly, don’t just run in toconclusion instantly. Some DBA destroy their database unnecessarilyby performing action before the completion of DBCC CHECKDB command.It might be possible that the errors shown by DBCC CHECKDB commandare caused by some non clustered index and you can remove theseerrors by simply recreating it.

7. Avoid usingREPAIR_ALLOW_DATA_LOSS option: As thename suggests this command results in some data loss. So thiscommand should be the last option to repair your corrupt databaseand make sure to take full backup before using this option.

8. Try 3rd Party tool: There are so many thirdparty SQL repair tools available on the Internet. The most populartools are Stellar Phoenix SQL Recovery,Kernal SQL, & Systools SQL. These toolsrepair damaged SQLServer database. All above tools provide free demo version of thesoftware that shows the preview of all recoverable objects.