SQL PowerShell to check and repair Ad-hoc distributed queries Vulnerability


Ad hoc distributed queries use the OPENROWSET and OPENDATASOURCE functions to connect to remote data sources that use OLE DB. As the name suggests, ad-hoc distributed queries should only be used with infrequent access to data sources. For the datasources that has to be accessed frequently Linked server would be a better option.

An ad-hoc distributed query would look like this.

SELECT a.*  
FROM OPENROWSET('SQLNCLI', 'Server=SQLinfo1;Trusted_Connection=yes;',  
     'SELECT Department, Name, DepartmentID  
      FROM AdventureWorks2012.HumanResources.Department  
      ') AS a;  

By default, Microsoft turns off this option. However, we can enable the same using the below query.

exec sp_configure 'show advanced options', 1;
exec sp_configure 'Ad Hoc Distributed Queries', 1;

The argument 1 at the end of the queries means ‘ON’, if you want to disable the ad-hoc distributed queries simply change the argument to 0.

exec sp_configure 'show advanced options', 1;
exec sp_configure 'Ad Hoc Distributed Queries', 0;

In a strict and secured environment, we could see ad-hoc distributed queries referred to as a vulnerability and often turned off. However, some stored procedures may use this ability to reference OLEDB and pull data. Turning it off would cause the SP to fail.

SQL Statement to find the SPs using ad-hoc feature

The below T-SQL code can be used to check whether any stored procedures are using OPENROWSET,OPENDATASOURCE functions.

DECLARE @sql nvarchar(max);

SET @sql = '
      @@SERVERNAME as ServerName,
      DB_NAME() as DatabaseName,
      Object_ID = (id), 
      OBJECT_NAME (id) as StoredProc,
    from syscomments 
    where text like ''%%OPENROWSET%%'' or text like ''%OPENDATASOURCE%''
CREATE TABLE #results (
    [ServerName] [nvarchar](128) NULL,
    [DatabaseName] [nvarchar](128) NULL,
    [Object_ID] [int] NOT NULL,
    [StoredProc] [nvarchar](128) NULL,
    [Text] [nvarchar](4000) NULL
DECLARE @statement nvarchar(max);

SET @statement = (
SELECT 'EXEC ' + QUOTENAME(name) + '.sys.sp_executesql @sql; '
FROM sys.databases
WHERE DATABASEPROPERTY(name, 'IsSingleUser') = 0
      AND HAS_DBACCESS(name) = 1
      AND state_desc = 'ONLINE'

INSERT #results
EXEC sp_executesql @statement, N'@sql nvarchar(max)', @sql;

FROM #results;

The output will show the SPs using the ad-hoc distributed queries option and we might have to look at the code and change it before disabling the ad-hoc distributed queries.

Using PowerShell to get data from multiple servers

By taking the help of PowerShell, we can run this query on multiple instances and return the output.

##Get the list of servers
$Servers =  Get-content -path " your file path "
## for each loop to execute the code in given set of servers
## T-SQL command 
  $cmd = "
DECLARE @sql nvarchar(max);

SET @sql = '
      @@SERVERNAME as ServerName,
      DB_NAME() as DatabaseName,
      Object_ID = (id), 
      OBJECT_NAME (id) as StoredProc,
    from syscomments 
    where text like ''%%OPENROWSET%%'' or text like ''%OPENDATASOURCE%''

CREATE TABLE #results (
    [ServerName] [nvarchar](128) NULL,
    [DatabaseName] [nvarchar](128) NULL,
    [Object_ID] [int] NOT NULL,
    [StoredProc] [nvarchar](128) NULL,
    [Text] [nvarchar](4000) NULL

DECLARE @statement nvarchar(max);

SET @statement = (
SELECT 'EXEC ' + QUOTENAME(name) + '.sys.sp_executesql @sql; '
FROM sys.databases
WHERE DATABASEPROPERTY(name, 'IsSingleUser') = 0
      AND HAS_DBACCESS(name) = 1
      AND state_desc = 'ONLINE'

INSERT #results
EXEC sp_executesql @statement, N'@sql nvarchar(max)', @sql;

SELECT @@servername Servername,databasename,count(Object_ID) count,[StoredProc]
FROM #results
group by DatabaseName,Object_ID,[StoredProc]
having Object_ID>0

Invoke-Sqlcmd -ServerInstance $_ -Query $cmd | select * |Format-Table -AutoSize


The output of the code should be like:


Ad-hoc distributed queries can be termed as a critical vulnerability in secured environments. Consider other ways such as linked servers to get the data because they are a lot safer and robust.

Posted in PowerShell, Security, SQL, T-SQL, Uncategorized, Vulnerability | Tagged , , , , , | Leave a comment

Hadoop Workflow Automation using Apache spark and Oozie


 The real-time data representation often requires quality content. In most cases, users flooded with multiple options or methodologies that may overwhelm the user to opt for the right techniques.

The quick notes from many geeks over the internet is a good start and at some instances, the conciseness of the article might meet our requirement; some other cases, it may direct with some pointers.

I believe that knowledge sharing is an effective method to grow with mutual terms. Over time, I realized how important to contribute and document the experience in a way to contribute to the communities.

This is an effort to recollect my 14 years of IT experience in the areas of data, data integration, data warehouse, and derive various data solutions through Hadoop eco-systems.

In this article, I will walk-through:

  1. Discuss the pre-requisites
  2. Migration from traditional data warehouse to Hadoop system
  3. Tools and techniques
  4. More…

 Note:I touch-base some of tools or technology over period. In this series, I will walk-through Hadoop, HDFS, Sqoop, scala, python, Hive, Hbase, Spark2, mapreduce, kafka, flumes, Oozie, Unix, Shell, SSH, sql server, oracle, Teradata, informatica power center, Informatica Data Quality, Syncsort, Salseforce, Data bricks, Dremio, beeline, API, execution plan, spark physical joins, automation and best practices.

Get started

In this section, you will see how to build a data product:

  1. Integrate data that are available in existing Data warehouses, legacy applications and near real time sources.
  2. Implement Data standardization
  3. Discuss Business logic and reporting needs
  4. Prepare data for rendering reports in Hadoop platform

Note: We will keep infrastructure needs, planning and platform setup out of scope from this article.


In this section, let us discuss the steps:

  1. Setup a data lake and Ingest data
  2. Data processing using Apache Spark
  3. Automate using Apache Oozie

Dissect the details:

Let us discuss the aforementioned steps:

Data Ingestion

The enterprise data can be of structured, semi-structured, and unstructured data. In our case, we will consider structured and semi-structured data. To perform the data ingestion, we would need to build two types of the process;

  1. Sqoop import for handling structured data; sources could be relational or delimited files
  2. Spark streaming by reading a kafka topic

To start with, you can perform sqoop import using sqoop command from unix installed with a release of a hadoop installed. In order to support variety of sources like RDBMS, flat files and complex files you can build a framework with any of scripting or programming languages like shell scripting or advanced java based programs. With no bias decision will rely on the availability of resources and the amount of time you are ready to spend on building the reusable component and you are the right to one to decide what is right for you. In either case you will realize its benefits as short time versus long term respectively.

Here is the sample sqoop commands for relational source;

# sqoop import –connect “jdbc:oracle:thin:@<hostname:datasource>” –username BIGDATA_USER_T –password-file /user/tbdingd01/sqoop/BIGDATA_USER_T –query ‘select 1 from dual where $CONDITIONS’ -m1 –target-dir /tmp/kiran/test_ora.txt

Once the sqoop import runs successfully you can either create an externa table on hive with required table configurations or perform Hadoop file operations. By default sqoop imports file in text file format with comma as delimiter.

Data processing using Apache Spark

In the process of building a data product one would end-up applying many resource-intensive analytical operations on a medium to large data-set in an efficient way. Apache Spark is the bet in this scenario to perform faster job execution by caching data in memory and enabling parallelism in a distributed data environments.

Components involved in Spark implementation:

  1. Initialize spark session using scala program
  2. Ingest data from data lake through hive queries
  3. Apply business logic using scala constructs or hive queries
  4. Load data into HDFS or Hive targets
  5. Execute spark programs through spark submit

We will learn the basics of implementation using Scala. Please refer this git branch click here

Let us see the simple spark submit and meaning of each configuration items

spark-submit –class org.apache.sparksample.sample \

    –master yarn-client \

    –num-executors 1 \

    –driver-memory 512m \

    –executor-memory 512m \

    –executor-cores 1 \

    examples/jars/spark-examples*.jar 10

Please refer the link ​Running Sample Spark 2.x Applications for apache documentation.


Automate using oozie workflow

There are different ways to automate the data pipelines. In this section, we will see the required components to configure Oozie:

  1. Sample Oozie property file
  2. Sample Oozie workflow
  3. Nodes in Oozie workflow
  4. Starting Oozie Workflow

Note: As Oozie do not support spark2, we will try to Customize Oozie Workflow to support Spark2 and submit the workflow through SSH.

Please refer my git oozie sample branch for the xml and configuration files to build your oozie workflow. You can build the workflow using xml file or the oozie workflow manager through ambari url. Once you build the workflow, I prefer to validate once using workflow import from hdfs path. You should be able to see the workflow view as below as long as your workflow.xml file is valid.

The simple oozie workflow job submit will look as below:


oozie job -oozie $OozieURL -config $jobPropertiesName -run -DnameNode=$nameNode -DjobTracker=$jobTracker -DresourceManager=$resourceManager -DjdbcUrl=$JDBCUrl -Dhs2Principal=$hs2Principal -DqueueName=$QueueName  -DhdpVersion=$HdpVersion -DzeroCountTblVar=$ZeroCountTblVar -DsshHost=$SSHHost -DmyFolder=$myScriptFolder`


oozie job -oozie http://hostname:11000/oozie -config /appl1/jobs/oozie_sample.properties -run -DnameNode=hdfs://hostname-DjobTracker=hostname:8050 -DresourceManager=hostname:8050 -DjdbcUrl=jdbc:hive2://hostname:2181,hostname2:2181,hostname3:2181/default;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;transportMode=http;httpPath=cliservice; -Dhs2Principal=hive/_HOST@hostname -DqueueName=default  -DhdpVersion= -DsshHost=hostname -DmyFolder=oozie_sample

Oozie does support re-run of failed workflows and below is a sample:

oozie job -oozie http://hostname:11000/oozie -config /appl1/jobs/script/rerun/wf_oozie_sample.xml -rerun 0000123-19234432643631-oozie-oozi-W

0000123-19234432643631-oozie-oozi-W is the job id you can find it on the failed workflow on the oozie monitor info.

We have variety of ways to get things done, I have opted simplest way may be there are better ways to do build Hadoop data pipelines, enable logging and schedule the jobs. I have tried best to keep the scripts simple, clean and follow some basic standards for easier quality learning exchange.

If you find it interesting or wish to leave a comment, please feel free to provide your inputs to improve the quality of my content.

Posted in Uncategorized | 1 Comment

Import Local SQL Server Database to Azure – Pick the Best Method

It is not unknown to the SQL users that some of the SQL Server versions have become outdated. This is the main reason why most of the SQL Server users are migrating SQL server database to Azure. Along with this, many are also moving to Azure due to its unique features. If you are switching the platform, you may also need to import local SQL Server database to Azure. In this post, we will discuss, two different approaches for the migration from on-premises SQL Server to Azure SQL database.

“Hello, all. I am writing here as I am in need to know the method to import data from SQL server to Azure. I have tried moving the database using the manual method, but that was unsuccessful as various issues came up. Please suggest me some effective method to perform this database migration smoothly and successfully.”

Techniques for Migrating SQL Server Database to Azure

There are different methods that will allow users to move their database to Azure. But not all of them are equally fruitful. To help our readers, we will talk about two methods in this post. One is manual and another one is automated. Let us not waste more time and go straight to the methods.

Manual Method to Import Local SQL Server Database to Azure

If users want to move their database manually, they can use the Deployment Wizard of SQL Server Management Studio (SSMS). This method should be used when the cloud migration process is not being hampered by any code compatibility problems. To start the migration in this method, run SQL Database Deployment Wizard on SSMS of any SQL Server instance.

Working Steps for migrating SQL server database to Azure

1. Select the database name from SSMS.

2. Right-click on it and select Tasks >> Deploy Database to Microsoft Azure SQL Database.

3. Fill in required Deployment Settings like Server connection, database name, the maximum size of the database, Azure SQL version, etc. Click Next.

4. Review the summary of the Settings and click on Finish.

5. Wait till the wizard finishes database validation, BACPAC file creation, and use Azure SQL for database creation.

6. When the process is completed, the new database has been created in the SQL Azure server.

Automated Method to Move Local SQL Database to Azure

If you think the manual method is time-consuming and requires a lot of attention, you can choose to use SQL Server to Azure Database Migrator Software. This application will allow you to seamlessly import local SQL Server database to Azure. Here are the complete working steps for the migration from on-premises SQL Server to Azure SQL database:

Step 1: Download and install the software on the Windows system.

Step 2: Launch the tool and click on the Open button.

Step 3: If the database is corrupt, choose Advance Scan mode. Otherwise, choose Quick Scan.

Step 4: Select the SQL Server version of the database. If you do not know the version, choose the Auto-detect option as pointing out the wrong version will cause trouble.

Step 5: For adding NDF file of the database, select NDF Options tab. Either manually select the NDF file or let the tool choose automatically. Make sure the NDF file is associated with the chosen MDF file.

Step 6: When file addition is done, users will be given a summary of the MDF file. If they want, they can also save the scan result for the future.

Step 7: Complete database records of MDF file including Tables, Triggers, Views, Stored Procedures, Keys, Indexes, Columns can be previewed. Click on Migrate button to import the database in Azure.

Step 8: Add Azure Database details and login credentials. Click on Connect and wait for the green tick mark.

Step 9: Select the destination database name from the list.

Step 10: On the left side of the tool, mark the database components you wish to move to Azure SQL Database.

Step 11: Also, select whether to transfer “With schema” or “With schema & data.” If required, users can also migrate deleted records.

Step 12: Click on Migrate and the software will successfully import local SQL Server database to Azure SQL Server database.

Final Words

Azure is gradually becoming a popular platform for SQL Database. Therefore, many people are migrating SQL server database to Azure. To meet their requirement, we have shared guidelines for two different techniques to perform this database migration. Users can go through the steps and choose that one they find useful. Since the software can import local SQL Server database to Azure in a short and simple manner, most of the users prefer this one over the manual method.

Posted in SQL | Tagged , | Leave a comment

Microsoft SQL Server Error 5243 – Top 3 Ways To Fix This Issue

Maintaining the SQL server database is always the first priority of the SQL administrators. But sometimes SQL database users have to face Microsoft SQL server error 5243 and error 5242. These are common errors. Due to this problem, the SQL users won’t be able to access their databases. Let us understand the situation with the help of an example.

“Please help! I was working with my SQL database and doing some test. Everything was fine. But suddenly when checked my error log I received an error like this. Can anyone help me to fix this Microsoft SQL Server Error 5243? Thanks!

Msg 5242, Level 22, State 1, Line 1

An inconsistency was detected during an internal operation in database ‘Invoices'(ID:11) on the page (1:35397). Please contact technical support. Reference number 4.

SQL server introduced two errors types of errors 5243 or 5242. When the users receive this kind of error it means that the SQL server database is corrupt. This type of error is clearly visible in the error log. If this type of error occurs, then you then the user has to figure out what has happened to the I/O system. This error describes record type is wrong for the current type of page.

Methods to Fix Microsoft SQL Server Error 5243

  1. Run the DBCC CHECK DB command – The user can try to run DBCC CHECKDB T-SQL command to restore the database. This command helps to check the physical and logical integrity of all the database objects in the specified database. For complete information about DBCC CHECKDB. If the database is too big then the user can use the REPAIR REBUILD option.
  2. By Restoring The Corrupt Page – In case if the page is corrupt and if the user knows the corrupt page then it is possible to restore the correct page.

In Microsoft SQL Server Error 5243 the user can view the corrupted page. Also, the user can detect the corrupt page by using a select in the MSDB database to the suspect_pages table.

Follow this T-SQL command in order to restore the page

Restore Database < Your Database Name>

Page = File:Page

File <device_name >


Resolve Microsoft SQL Server Error 5243 By Using Expert Solution

In case if the user cannot be able to restore the data then the user can use SQL Database Repair tool. With the help of this software, the user can easily fix Microsoft SQL Server Error 5243. This software is a simple, easy to use and smartly developed software. It can be used to restore SQL server database in case of database corruption. This utility can easily repair the corrupted MDF and NDF file data. By using this software the user can scan and recover triggers, stored procedure, functions, tables. Moreover, this tool is compatible with SQL server 2017 and its below versions.


Final Words

In this article, we have discussed how to fix Microsoft SQL server error 5243. The user can try to restore the database by using manual methods. But these methods require strong technical skills and knowledge. So to avoid data loss situation the user can take the help of MDF Recovery Software to resolve this problem.

Posted in SQL, T-SQL | Tagged | Leave a comment

Restore SQL Server 2014 Database from Backup – Step By Step Guide

Microsoft SQL server database contains the most crucial data of organizations. But while using MS SQL server many users often face problems like abrupt termination, database connection errors, transient errors, malicious software attacks. In order to save their important SQL server data, many organizations are prepared for the worst case scenarios to avoid data loss situations. In some situations, various users want to know how to restore SQL Server 2014 database from backup.

Well backing up your Microsoft SQL server data not only helps the user to restore the data but it also helps the user in such situations like disaster recovery. The general practice of creating the backup is very crucial when SQL data is the key factor for the business. Before proceeding to the solution part to restore SQL server 2014 database from backup. Let us first understand the importance of SQL server backups.

Importance of SQL Server Data Backups

Here are the points which help the user to understand the Importance of creating backups.

  1. It helps to protect from power failures – As we know that computer systems are prone to damage due to power failures. So due to sudden power failures, the SQL data might get corrupt and it will lead to a data loss situation. Therefore with the help of backups, the user can easily restore the data.
  2. Safety from Virus and other Malware attacks – Its nothing new that files and folder turn into an inaccessible state because of Virus and malware attacks. And by multiple trials of accessing the affected files and folders. The viruses spread throughout the system. So in this way backups helps to restore databases.
  3. Recover data in case of Operating system failure – So in case of an operating system crash or failure, all the files and the data in the hard drive will be gone. Backing up the data is the only thing that remains in court to get things back.
  4. Other Reasons – Data backup is also important in case if the general user’s errors like deleting a table by mistake. In that case, the user can easily restore the data from the backup.

Backing up the files and folders is not only essential things but also a good practice. That assures that your important data won’t go anywhere no matter how worse the situation is.

Restore SQL Server 2014 Database From Backup – By Using Manual Technique

#Technique 1: By Using SQL Server Management Studio

  1. First, the user has to connect to the appropriate instance of the Microsoft SQL Server database engine.
  2. After connecting, then go to the Object Explorer, and then click on the server name to Expand the server tree.
  3. Now click on databases, and then choose either a system database or user database.
  4. Perform the right click on the database and then go to Task and after a click on restore.
  5. Choose the type of restoration operation. Such as database file, filegroups or transaction log.
  6. Now on General page, the user has to click on from Device.
  7. After that click on browse button to open the specify backup box dialogue box.
  8. Now the user has to select the backup device in the backup media text box and after that click on Add button to open select backup device dialogue box.
  9. Choose the device that you want to use for the restore operation. After selecting the restore option choose backup file and then click on OK.

Automated Solution to Restore SQL Server 2014 Database From Backup

The manual solution is not a reliable solution to perform this task. Because it requires strong technical knowledge. So to avoid in any data loss situation the user can take the help of SQL Backup Restore Tool. This software gives two export options SQL server database or SQL server compatible scripts to export SQL data. The user can easily recover and preview database Tables, views, stored procedure, Triggers, columns, functions, etc. This utility supports .bak file of SQL server 2017 and below versions.

Note: If the user wants to repair the corrupted MDF (primary database file) and NDF (secondary database file) then the user can try SQL database recovery software to resolve all the SQL database corruption issues.

Final Words

Many time SQL users want to know how to restore SQL server 2014 database from backup. So in this article, we have discussed the step by step procedure to perform this task. We have also given the importance of SQL server data backups. The above discussed manual solution to perform this task has various limitations. To avoid any data loss situations the user can take the help of Automated solution to resolve this issue.

Posted in SQL, SQL Server Backup | Tagged | Leave a comment

Know How to Repair SQL Database Marked as Suspect Mode in SQL Server

Repair SQL Database Marked as Suspect Mode

MS SQL server is a widely used relational database management system developed by Microsoft. It is a software product which primarily helps to store and retrieve the data of other applications. SQL database can have a specific state at a given time and it runs in various different modes which are online, offline, restoring, recovering, recovery pending, suspect, emergency. But sometimes when the user connects to the SQL server. The user will find the database is marked as a suspect state. The user will not be able to connect to the database. So in this article, we will discuss how to repair SQL database marked as suspect. Also, the forthcoming article will also discuss the various reasons for this error.

“I am currently using SQL server 2014 addition on my Windows 8 machine. Recently when I tried to connect to the SQL database I got an error like this database cannot be opened it has been marked suspect by recovery error. I don’t know what to do in this situation, Kindly guide me how to tackle with MS SQL database marked as suspect error problem.”

sql suspect error

Possible Reasons Behind MS SQL Database Marked as Suspect

When the SQL server starts up, then it attempts to obtain an exclusive lock on the server device file. In case of the device, the file is being used by another program or its found missing then SQL server starts displaying errors. Here are the possible reasons for this problem.

  1. Due to unexpected SQL server shutdown, power failure, or hardware failure.
  2. In the case of a database, files are being held by the operating system or third-party backup software.
  3. The system failed to open the device where the data or log files resides.
  4. In case if the SQL cannot complete a rollback or roll forward operation.
  5. SQL database cannot be opened due to inaccessible files or insufficient disk space.

Know How to Repair SQL Database Marked as Suspect – Manually

Here are the steps to fix MS SQL database marked as suspect mode problem.

  1. First Open SQL server management studio and then connect to your database.
  2. Now select the new query option.
  3. Then turn on the suspect Flag on the database and then set it to Emergency
  4. EXEC sp_resetstatus ‘database name’;


  5. Now perform a Consistency check on the Master database.
  6. DBCC CHECKDB (‘database name’)

  7. Now Bring the database into single user mode and after that roll back the previous transactions.

  9. Take the backup of the complete SQL database.
  10. Then attempt the database repair allowing some data loss.
  11. DBCC CHECKDB (‘database name’, REPAIR_ALLOW_DATA_LOSS)

  12. Now bring the database into multiuser mode.

  14. Now refresh the server and verify the connectivity of the database.

Note: By performing the above steps the MS SQL database marked as the suspect problem will be solved. In case if the problem is still there then the user can take the help of an automated solution.

Automated Solution to Resolve MS SQL Database Marked as Suspect Error

If you have tried the above procedure and you are unable to resolve this issue or your database still marked as Suspect then you can try SQL Database Recovery. This utility is an ultimate solution to recover corrupted SQL database. By using this software the user can restore database objects like Triggers, views, functions, stored procedure, etc. This software is capable to recover deleted records from database tables. It provides two scan options which are quick scan and advanced scan to repair corrupted SQL database. This software is compatible with SQL 2017 and its below versions.


Final Conclusion

In this article, we have discussed how to repair SQL database marked as suspect. Also, we have discussed the various causes of this problem. We have given the manual solution to solve MS SQL database marked as suspect error issue. If the user doesn’t want to face any data loss situation then, in that case, the user can take the help of an automated solution MS SQL recovery tool to recover your most crucial SQL database.

Posted in SQL | Tagged , | Leave a comment

Kernel Migrator for SQL Server – Expert Product Review

Ease of use, accuracy, integrity, efficiency, and security are some characteristics which a good software should have, and when we speak of software products that focus on business users, the scale of expectations increases. Therefore, there remains no room for software functionality mistakes and bluffs; otherwise, the risk of data loss or corruption can surface.

Kernel Migrator for SQL Server claims to migrate both corrupt and healthy SQL server database file(s) to distinct cloud platforms, which I’ve tested carefully, to find out how well it gets the job done.

I’ll walk you through my Kernel Migrator for SQL Server review dwelling the associated benefits and my experience, explaining how well the job of SQL Server database migration went.

Let’s have a look at specifications first:

Supported Versions of MS SQL Server: 2000 to 2017

Processor: Intel Pentium.

RAM: 256 Minimum.

Storage: 50MB

For more info, you can visit the Kernel website.

User Interface

Software should please you not only with its functionality but with looks as well; the User-Interface plays an important role in deciding the level of user acceptance & friendliness; otherwise, it’ll be like any other typical software product.

Kernel’s Migrator for SQL Server boasts of modern UI, and if I compare with what it has to offer, it doesn’t have multiple options which generally turns the look & feel into bulky.

Open, Migrate, and Help are the main options available in the menu bar. Each time you launch the tool, the Select SQL Database window pops on-screen automatically saving your time to reach to the Open option, but in case if you close the window you can reach to the option and launch it again whenever needed.

My Experience

Support is one of the important things which most of the users seek from a software product whether it is for personal use or business use. Kernel tool with its wide support for older software versions eliminates the accessibility gap.

I’ve SQL Server 2017 installed on my system, and I was not hoping that Migrator for SQL Server tool would be supporting this version, but unexpectedly it did, and that’s not all, the tool support all versions of SQL Server ranging from SQL Server 2000 to 2017 – which is the latest version of SQL Server available.

Kernel Migrator for SQL Server is accompanied with the built-in ability to detect the database version, but in case if someone wants to select the same manually, he/she can do so. For me, it did the job automatically.

The tool comes with the feature of Live Preview, which in all senses is quite nice. Once you load the file and the database file scan is complete, it lists all database objects in a tree structure – you can expand or collapse the objects to check what’s inside and click on the object to preview it.

For many, it can be confusing for why does this feature even matter? It matters as it helps you in confirming the data integrity & hierarchy.

For cross-check, I’d tried a demo database file, and you all can see the results below.

At first, I thought of Kernel’s Migrator for SQL Server as a simple tool that serves for the only purpose of database migration to Azure SQL & Amazon RDS with limited functionalities, but after I used it for a while, I was quite surprised to know that I can migrate either all or selected database objects.

From destination database selection to server authentication credentials input, everything is combined and given in unified Migration Options window; no extra efforts are required to first configure the tool with SQL Server and login credentials.


The issue of compatibility sometimes comes as a big question especially for those companies or organizations who are still using an older build of SQL Server for their database needs; there could be many reasons why these organizations practice such habit.

Kernel has done a good job in terms of compatibility; the tool is made wide compatible with a range of Microsoft SQL Server versions form SQL Server version 2000 to 2017.

Pricing & Availability

Kernel Migrator for SQL Server is made available in two distinct product license categories which are Corporate and Technician License. For both the license types, it offers lifetime support, software updates, and 100% money back guarantee.

The Corporate license costs around $149 whereas Technician license costs for $299 including other associated benefits. It’s not proper to suggest you invest your money right after learning what I’ve experienced, and so you can download the free version and check on your own. The trial version is bounded with some limitations but to prove the claims, it can generate Live preview of database objects.


  • Easy to use.
  • Minimal User-Interface.
  • Advanced Scan Mode.
  • Compatible with older SQL Server versions.

Live Preview.


  • Cannot migrate database using the Free Trial version.
  • Cannot select more than one database file at a time for migration.


SQL Migration tool is a lightweight tool that doesn’t require high-end hardware to run. It is easy to use too. In my Kernel Migrator for SQL Server review, I’ve shared my experience to let you all better understand how it performs the migration. The free trial version allows you to understand better about what you’ll be getting when you will make the purchase. You too can share your views over the same and ask questions in case of any queries you might have.

Posted in Uncategorized | Tagged | Leave a comment

SQL Power Doc to Discover, Diagnose and Document SQL Server

This article will provide an overview of SQL Power Doc, a powerful script to document SQL Server, including pre-requisites, purpose, installation, processing and output. Automation seems to be an increasing need in IT industry. At present, PowerShell leads the game in the Windows-based environments, since it is capable of handling highly complex workflows. PowerShell is the go-to choice when it comes to automating even requirements such as configuration management. I got an opportunity to assess SQL Power Doc recently. I have worked with PowerShell a lot (I have even written a book on it). I have built my share of automation and designing tools using PowerShell. When I took a look at SQL Power Doc, I felt the tool does have its share of capabilities to help DBAs in its own way. Let me detail some of my experience while exploring this tool. Let’s get started.

What is SQLPower Doc?

SQL Power Doc was written by Kendal VanDyke. It is a command line PowerShell manifest consisting of several PowerShell modules that will help you build a solid foundation to Discover, Document and Diagnose SQL Server instances and Windows Operating system details. Additionally, you’ll gain some insight into what directions you can go for future exploration. It basically invokes PowerShell cmdlets and give you the necessary details front and center.

Further reading

Using SQL Power Doc to Discover, Diagnose and Document SQL Server


Today, I’m making my 100th post on SQLShack

The journey with the SQLShack family has been a one from the beginning. It is certainly not easy to join an excellent group of elite authors. When I joined this family, I looked at it as a place to showcase my work online, but I never thought it was going to be an interesting one and have such an impact on my career. Never did I imagine that I would write a hundred articles. This is very much a milestone in my professional life, and all the credit go to our captain and the SQLShack family.

To view all my articles, click the following link


Posted in awards | Leave a comment

How to Change Suspect Mode to Normal Mode in SQL Server Database

If you are reading this, you are desperately looking for solutions to change suspect mode to normal mode. It also means that your database has gone into the suspect mode. In that case, you have already experienced one of the most irritating situations possible for a DBA.

Worry no more, as you will soon get out of this situation. In this post, we have compiled some of the prominent solutions to the query, “how to change suspect mode to normal mode in SQL”. We will also tell you in detail in which situations a database can turn into suspect mode and what can be done to get back the database into normal mode.

How to Change Suspect Mode to Normal Mode – Possible Scenarios

In this section, let us check some of the common scenarios responsible for suspect mode of SQL database.

Scenario 1: “Some inexperienced employees used SQL database yesterday and now the database is in suspect mode. I guess they may have misplaced some data. How to bring back a Database Online from Suspect mode?”

What happens in this case is some data or log files get deleted (or misplaced) from the database when the Server is in offline mode. As a result, the database fails to start and goes into the suspect mode.

Solution: If the suspect mode situation occurs due to this reason, the error message of SQL Error Log will contain the name and directory of the missing file. Place back the file and then run this command to put the database online in normal mode without any data loss.


Scenario 2: “Some applications have locked our database files and now the database cannot come online. How can we remove the suspect mode and bring in online in the normal mode?”

In this particular situation, SQL Server fails to put an exclusive lock on the log file or data when it tries to come online. It also fails to access a lock placed on data/ log file by some other tools like anti-virus applications (happens if SQL Server is shared with these tools).

Solution: If the above reason has put the database in suspect mode, you have to kill the file handler responsible for placing lock on the file. Take the help of Process Explorer to do this and the involvement of System Admins is recommended to perform this particular step. Later, run this command to put SQL database into normal mode again.


Scenario 3: “Due to power surge, SQL Server got shut down suddenly while it was in the middle of a transaction. Now the database is offline with suspect mode because of the corrupt transaction. Tell me how to bring the database back into normal mode.”

Here, a corrupt transaction that took place in the database has put the database into the suspect mode. This is often considered as the worst possible scenario as it contains high chance of data loss if there is no good backup. This is also the main reason for suspect mode in OLTP databases. If SQL Server is been closed abruptly or restarted while in the middle of a transaction and then it failed to complete the transaction (whether commit or rollback), this scenario occurs.

Solution: If you have a good backup of your database available, you can restore the database up to a suitable point. If you do not have this option available, you need to run the DBCC CHECKDB Repair_Allow_Data_Loss command. These are the steps you have to perform on SQL Server Management Studio.

Remember: Following the below steps can result in data loss from your database. So, we recommend you to try this only after you have tried every other solution.

  • Use SQL Server Management Studio to connect to SQL database and find out which database has gone into suspect mode. To do this, run the command:
  • USE master
  • Expand Management Node > SQL Server Error Logs and open the SQL error log. Check if the database is marked suspect in the error log.
  • Now, put the suspect database into Emergency mode, since it is not feasible to connect to a database that is in suspect mode. Run this command:
  • USE master

    Note: Before you enable Emergency Mode, remember that it cannot be rolled back. So, we suggest you to take data backup if possible before applying this command.

  • Now the database has gone into read-only state and only system admin privileges can access the database. You can run this command to verify the physical and logical integrity of all database objects:
  • This command will enable single user mode for the database. It will also check if rollback is available:
  • It is time to repair the database and you need to use the below DBCC CHECKDB command:

    Warning: Once you have run this command, there is no rollback available. Any lost data will be lost forever.

  • Put the database into MULTI_USER mode so that anyone can use the database. Run this command:

You should have your database back in online mode by now. You have to check how much data you have lost.

Here are some of the other reasons that can put SQL database into suspect mode:

  • SQL database MDF file corruption
  • Problems during rollback or transaction completion
  • Errors due to limited space on disc or system drive.
  • Transaction log corruption
  • When the Server cannot access the drive where the log files are stored
  • Corruption of hard disk

Change Suspect Mode to Normal Mode Without Any Data Loss

Manual approaches that users implement to turn the suspect database into normal mode involves loss of valuable data. Most of the SQL users cannot afford to lose their data and hence they look for some better solution. SQL Database Repair Software is such an application that can fix all the scenarios responsible for suspect mode and put in back to normal mode without losing any data. To run this application, you have to perform these steps:

  1. Add the database file with suspect mode in the software.
  2. Apply Advanced scan mode to fix any discrepancy.
  3. The tool will scan the database and repair it.
  4. The complete recovered database will be available for preview.Click Export the save the required database components in any location.


Since the suspect mode is a common issue faced by SQL users, these users keep asking in forums, “how to change suspect mode to normal mode in SQL?” Their search should end here as we have depicted some verified methods to put SQL database into normal mode. Use any of the solutions mentioned here to get rid of the suspect mode SQL database.

Posted in SQL | Leave a comment