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.
Author Archives: Andrew Jackson
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 »
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:
- 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.
- 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.
- 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.
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.
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:
- 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.
- In case the SQL server database is modified during the DROP operation, there are great chances of MDF and LDF files corruption.
- Hardware issues such as problem in the hard drive sector storing the SQL database, may lead to corruption of the database.
- 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.
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.
Log shipping in SQL Server is the process of enabling an automatic backup of the complete database and transaction files of the primary server and then restoring the backup on a secondary server. Being a high-availability solution, it increases the database availability of SQL Server by creating a backup server that can replace the primary server in case of data disasters.
Log Shipping involves 3 servers:
- Primary or the source server
- Secondary or destination server
- Monitor Server
Log Shipping in SQL Server allows sending of transactional log backups from primary instances residing on the primary server to one or more secondary instances residing on the secondary server. These transactional log backups are applied to each instance of the secondary database individually.
It is to be noted that Log shipping in SQL Server can also be configured without the installation of monitor server. In such a case, the history and the status of all the backup operations are stored on the primary server and the history and restore status is stored on the secondary server. However, when the remote monitor server is installed, this information also gets stored on it. It is also responsible for raising alerts whenever the backup process is not performed as per the schedule.
In case the monitor server has not been specified, the primary and secondary servers monitor the backup and restore operations, and raise the alert when an error is encountered.
Key Note for Monitoring Log Shipping in SQL Server
For monitoring the configuration of log shipping in SQL Server, the monitor server needs to be added at the time of enabling log shipping. In case the monitor server is added later on, the existing log shipping configuration must be removed and the new setup including monitor serves must be installed. In addition to this, any alterations in the monitor server can only be enabled after removing log shipping.
History Tables in SQL Server Log Shipping
The monitoring history tables of log shipping comprise metadata that gets stored on the monitor server. Moreover, primary and secondary server-specific information is also stored locally. For monitoring the status of the monitoring sessions, these history tables are brought in use. In order to check the status of log shipping, status and history of backup job, copy and restore functions need to be viewed.
Some of the tables that need to be checked for monitoring the log shipping in SQL Server are:
One monitor record for primary database of each log shipping configuration is stored in this table. This includes the information like the details of the last backup and the last restored file.
This table stores the alert job ID.
This table stores the details of the log shipping transactions. In addition to this, the users can also access the table for accessing errors in a session.
This table gives the details of the history of the log shipping agents. Details regarding an agent session can also be viewed in this table.
This table stores one monitor record of the secondary database like the last backup file and the last restored file that are used in monitoring.
Monitoring log shipping in SQL Server enables the administrator to monitor the entire configuration of the entire environment. In addition to the history tables, additional parameters like stored procedures enable to monitor log shipping in SQL Server.
A Transaction Log is a file that contains all the records of transactions and the database modifications made by each transaction in SQL Server database. The log file plays a very important part of SQL Server database when it comes to disaster recovery and it should not be in corrupted state. There are several types of errors we came across while using SQL Server and one of the most common error is Failure in SQL Server Transaction Log or Error 22029. This page will be discussing about the same error in details.
SQL Server Transaction Log Backup Failing
Why It Occurs?
This error in associated with the maintenance plan of the transaction log back up or maintenance plan options for the database in SQL Server.
There are many reasons why the above error in SQL Server occurs-
- The SQL Server transaction log backup error 22029 may be seen if your database maintenance plan is trying to make a transaction log backup of a database that is offline.
- It may occur when you try to create transaction log backup and when the database is using the simple recovery model.
- It may occur if you select the master database and you choose backup the transaction log as part of the maintenance plan option.
- This error may also be seen if the string for the target folder (where the database backup will be stored or the one used for transaction log backup or one used for reports) in the maintenance plan contains “-S”
- It may happen if you have less disk space for the transaction log backup.
- If the multiple databases are backed up in a single maintenance plan, this error might show up.
- In addition, it may occur if you have no right access permissions or authority. Example- the SQL agent does not have write access to the file storing the database backups.
How It Can Be Resolved?
One thing that should be kept in mind while creating maintenance tasks is to check the “write report to a text file in directory” so that all the details of whatever happening during the maintenance task is seen, especially when there is any error.
- The reason why the first error occurs can be solved by connecting the database to the server. So that, the log of that database can be backed up successfully with the maintenance plan as the database is online.
- While creating the maintenance plan for transaction log backup under simple recovery model, we may find error. Why is it so?
The simple recovery gives the simplest form of backup and restore. It supports both database and file backups but does not support log backups. The absence of log backups simplifies managing backup and restore.
Tips: The simple recovery model is not appropriate for systems where loss of recent changes is unacceptable. In such cases, full recovery model is preferred.
We should never run a transaction log backup on a database in the Simple Recovery model. Even though there is option to select all databases, we can select either database that are not in Simple Recovery model or change the database to the Full or Bulk-logged Recovery models to avoid this error. This problem has been fixed in SQL Server 2005 as the databases in simple recovery are ignored automatically.
- The third error says we cannot select master database and choose backup of transaction log because only full database backups of master database are allowed. Hence, we can create a separate maintenance plan for master database and not backup the transaction log or set master database to Full recovery and do transaction log backup.
- To solve the fourth error, we will not use strings that contain “-S” for a directory path. It only occurs on named instances of SQL Server 2000.
- The problem with less storage can be solved by allocating more disk space.
- If the error occurred due to sixth reason, then we can select a single or some databases while back up and keep in mind the type of recovery models with respect to transaction log backup.
- To perform transaction log backup of the database, we need to configure MS SQL Server to run under a user account that has administrator privileges to the machine.
SQL Server Transaction Logs record the entire information about the transactions and data modifications made by the transactions in the server. In order to create backup or ensure the availability of transaction logs at the instance of any disaster, SQL offers two processes- SQL Log Shipping and SQL Log Mirroring.
In this section, we will describe both these processes and the parameters in which they differ from each other. Read the rest of this entry »