RSS

Category Archives: General topics

General topics in SQL Server

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.

Quick Solution to Repair Corrupt File:

If your database files is severely corrupted, then you can go for an automated solution to repair corrupted database files. SQL Repair Tool repair corrupted MDF/NDF file of SQL version 2017, 2016 & all its below versions. It also help you in recovering deleted database records.

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:

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.

————–

 
 

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

 

How to Find the Last Inserted Record in SQL Server

Overview

When the users of SQL Server stores data in table of their database, they use an identity column as primary key. The identity column will increase its value automatically whenever new row is added. However, in some cases users may need to determine the last inserted record in database. The blog will be explaining some of the possible ways on how to find the last inserted record in SQL Server.

Determine Last Inserted Record in SQL Server

While we work with the table in SQL Server database, we set identity column that act as an auto increment column in table to increase column ID value whenever new record is inserted. Suppose we want to insert a name of the employee in the table ‘Employees’, we will do that using the below command:

INSERT INTO Employees (FirstName) VALUES (‘Mellisa’)

Now, in order to get the lasted inserted record ID, we can use the following options:

  1. SELECT @@IDENTITY
    • It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value and of the scope of the statement that produced the value.
    • It is used to give the last identity value generated by the statement.
    • If the statement did not affect any tables with identity columns, this command returns NULL
    • If the table contains multiple rows generating multiple identity values, @@IDENTITY returns the last identity value generated.
    • Though @@IDENTITY is limited to current session, it is not limited to current scope. Even if trigger on the table caused identity to be created, you will get identity that was last created, even if it is a trigger.
  2. SELECT SCOPE_IDENTITY()
    • As the name suggests, it will return the last identity produced on a connection and by statement in same scope, regardless of the table that produced the value.
    • It is limited to the current scope and in current session as well.
    • It will return the last identity that was explicitly created, rather than any identity created by trigger or user-defined function.
  3. SELECT IDENT_CURRENT(‘TableName’)
    • It returns the last identity value produced in a table, regardless of the connection and the scope of the statement that created the value.
    • It is not limited by scope and session but is limited to a specified table.
    • It will return the identity value generated for the specific table in any session or any scope.

Conclusion

In the blog, we have discussed about some of the approaches through which we can find the last inserted record in SQL Server database. Among the methods SCOPE_IDENTITY() is recommended as it avoids the potential issues associated with addition of trigger while returning identity of the recently inserted record. The manual methods of determining last inserted record using these commands may sometimes be time taking and difficult for non-technical users. One of the easy alternative for the same purpose is to use a third party tool to view SQL Server transaction log that is used to read and analyze SQL Server Log File transactions that will give detailed analysis of all transactions like insert, delete, update etc.

 
1 Comment

Posted by on May 9, 2016 in General topics

 

Tags:

SQL GULF #3 RIYADH 2016

Welcomes you to the first SQL Server 2016 event in the Middle East at AlFaisal University on 23/4/2016

sql-gulf-3We are so glad to announce SQL Gulf #3 event , Riyadh 2016 which will be held at Al-Faisal University on 23th April where top notch MVP, MCM and MCA experts are coming from different countries worldwide Australia , US, UK and Germany to speak to you about SQL Server 2016 ONLY…! It is the first event ever in the middle east to reveal about the latest Microsoft SQL Server 2016 technologies and techniques ,the event is for both male and female ,come to register here ASAP http://waja.com.sa/SQLGulf3/

12924351_1141298549213799_4742778383078550246_n

 

Speakers 

Read the rest of this entry »

 
4 Comments

Posted by on April 9, 2016 in General topics

 

How to get orphaned login for all databases?

Following script gives you all user database users, that do not have a server login mapped or where database user name does not match server login name. This is follow up post to How get all users and their role mappings from all databases? I posted few days ago.

IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name like '#Output%')
   DROP TABLE #Output
GO

CREATE TABLE #Output (DatabaseName VARCHAR(255), UserLoginSID varbinary(128), ServerLoginName VARCHAR(255), DatabaseUserName VARCHAR(255), UserType VARCHAR(50))
GO

sp_msforeachdb '
INSERT INTO #Output
SELECT  ''?''
      , DP.sid
      , SP.name
      , DP.name
      , DP.type_desc
  FROM sys.database_principals DP
  LEFT JOIN sys.server_principals SP
    ON DP.sid = SP.sid
 WHERE DP.type_desc IN (''SQL_USER'',''WINDOWS_USER'')
   AND DP.sid IS NOT NULL'
GO

  SELECT *
    FROM #Output
   WHERE ServerLoginName IS NULL
      OR ServerLoginName <> DatabaseUserName
ORDER BY DatabaseName, ServerLoginName
GO

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.

 
Leave a comment

Posted by on April 5, 2016 in General topics

 

Methods to Repair Corrupted MDF and LDF Files

MS SQL Server is a relational database management system that is used for storing and retrieval of data when requested by other applications. The SQL database mainly comprises of three files- MDF, NDF and LDF. The first two files from the primary and secondary database of SQL Server and the latter stores the log database of SQL server database transactions. While the data files and the log file are accessed by a common server, these files do not share the same location for their storage. Both the data files and the log file are associated with SQL server; in this blog however, we will restrict our discussion to how to repair corrupted .mdf and .ldf files.

Primary & Log Database of SQL Server

Primary database aka Main Database File (MDF) as the name suggests is the main database file of SQL Server that are associated with other files. There exists only a single MDF file for every SQL Server database, which stores data items like triggers, views, stored procedures, etc. It would not be wrong to say that MDF file is the primary element for administration of SQL database.

The LDF (Log Database File) store the entire log information of the transactions or activities carried out in the SQL Server. They play a very crucial role in the restoration of SQL server data in cases of data disaster. The size limitation of LDF is decided at the time of the database designing. Information related to records updation, record deletion, insertion of new record, etc.

Corruption in MDF and LDF Files

Maintaining the database integrity of SQL server is the top priority of the users as any mishap can lead to loss of data that at times cannot be recovered easily. There can be umpteen reasons, which can end the MDF and LDF files in a corrupted state. Some of the reasons for MDF and LDF corruption are:

  1. The biggest reason behind the corruption of MDF and LDF files is the storage size. In case the data exceeds the limit of MDF and LDF file, corruption is likely to occur.
  2. In case the SQL server database is modified during the DROP operation, there are great chances of MDF and LDF files corruption.
  3. Hardware issues such as problem in the hard drive sector storing the SQL database, may lead to corruption of the database.
  4. Virus intrusion is also an important factor when it comes to corruption of MDF and LDF files.

Manual Solutions to Repair Corrupted MDF and LDF Files

In order to repair a corrupt MDF and LDF file, DBCC CHECKDB command can be used. It recovers the data from a corrupt SQL database file that has been damaged due reason such as logical corruption. This involves running an executable code in the SQL server in order to repair corrupted MDF and LDF files.

dbcc-checkdb

Note: Before running the DBCC CHCKDB command, the backup of the existing database should be taken.

In case the repair process fails and the backup of the MDF and LDF files does not exist, the user will not be able to restore the database back to its original state and hence will end up in having a corrupt MDF and LDF file.

Alternate Solution to Repair Corrupted MDF and LDF Files

Regarding the drawbacks of the manual procedure to repair corrupted MDF and LDF files, third party solutions are preferred over them. One such commercial utility that can be deployed for repair corrupted MDF and LDF files is SQL Server Database Recovery Tool. The software recovers the entire data from corrupt MDF and LDF files and save it in SQL compatible scripts or directly in SQL server database.

 
2 Comments

Posted by on February 3, 2016 in General topics