Microsoft® Data Migration Assistant v3.3

Data Migration Assistant (DMA) enables you to upgrade to a modern data platform by detecting compatibility issues that can impact database functionality on your new version of SQL Server. It recommends performance and reliability improvements for your target environment. It allows you to not only move your schema and data, but also uncontained objects from your source server to your target server.

Here’s the download link

What is new in V3.3?

  • DMA v3.3 enables migration of an on-premises SQL Server instance to SQL Server 2017, on both Windows and Linux.



  • Migration blocking issues:  DMA discovers the compatibility issues that block migrating on-prem SQL Server database(s)s to Azure SQL Database(s). It then provides recommendations to help customers remediate those issues.
  • Partially or unsupported features:  DMA detects partially or unsupported features that are currently in use at the source SQL Server. It then provides comprehensive set of recommendations, alternative approaches available in Azure and mitigating steps so that customers can plan ahead this effort into their migration projects.
  • Discovery of issues that can affect an upgrade to an on-premises SQL Server. These are described as compatibility issues categorized under these areas:
    • Breaking changes
    • Behavior changes
    • Deprecated features
  • Discover new features in the target SQL Server platform that the database can benefit from after an upgrade. These are described as feature recommendations and are categorized under these areas:
    • Performance
    • Security
    • Storage

Supported source and target versions

  • Source: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, and SQL Server 2016
  • Target: SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, and Azure SQL Database

Screen Shot 2018-01-05 at 11.39.44 AMScreen Shot 2018-01-05 at 11.40.18 AMScreen Shot 2018-01-05 at 11.42.28 AM


Happy learning!

Aman Kharbanda



SQL Operations Studio (Public Review) is now available

SQL Operations Studio is now available. Here’s the link to download and install it’s December (Latest release as of this writing) release:

Get SQL Operations Studio (preview) for Windows

This release of SQL Operations Studio (preview) includes a standard Windows installer experience, and a .zip:


  1. Download and run the SQL Operations Studio (preview) installer for Windows.
  2. Start the SQL Operations Studio (preview) app.

.zip file

  1. Download SQL Operations Studio (preview) .zip for Windows.
  2. Browse to the downloaded file and extract it.
  3. Run \sqlops-windows\sqlops.exe

Get SQL Operations Studio (preview) for macOS

  1. Download SQL Operations Studio (preview) for macOS.
  2. To expand the contents of the zip, double-click it.
  3. To make SQL Operations Studio (preview) available in the Launchpad, drag to the Applications folder.

Supported Operating Systems

SQL Operations Studio (preview) runs on Windows, macOS, and Linux, and is supported on the following platforms:


  • Windows 10 (64-bit)
  • Windows 8.1 (64-bit)
  • Windows 8 (64-bit)
  • Windows 7 (SP1) (64-bit) – Requires KB2533623
  • Windows Server 2016
  • Windows Server 2012 R2 (64-bit)
  • Windows Server 2012 (64-bit)
  • Windows Server 2008 R2 (64-bit)


  • macOS 10.13 High Sierra
  • macOS 10.12 Sierra


    • Red Hat Enterprise Linux 7.4
    • Red Hat Enterprise Linux 7.3
    • SUSE Linux Enterprise Server v12 SP2
    • Ubuntu 16.0

Allows us to connect well to the DB and it gives a nice feel of GUI integration.
I’ll pen down more in next writeup about this tool!

    • Screen Shot 2018-01-04 at 5.32.35 PM.png


Happy learning!

Aman Kharbanda


Database fails to generate a Checkpoint

This seems to be one of the many strange errors within SQL Server that you may encounter. You might’ve lately restored this DB from the server where Replication flag would have been enabled – That’s the closest of the scenarios which happened in my case. Although system DB’s were not restored as part of the exercise.

So, here’s what the error message will scare you with:
One or more recovery units belonging to database ‘mydatabase’ failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.

The log scan number (643555:241:0) passed to log scan in database ‘ mydatabase ‘ is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup

Couple of options to start with:

  1. Check within the sys.databases table and see if the log_reuse_wait_desc is marked with Replication. If so, then we must work to get rid of it.
    SELECT name, log_reuse_wait_desc FROM sys.databases where log_reuse_wait_desc = ‘Replication’
  2. You may execute this stored procedure to remove replication from this DB if you’re sure that it’s not a replication participating DB.
    EXEC sp_removedbreplication ‘mydatabasename’
  3. After executing #2, if DB entry is still showing up in sys.databases table within log_reuse_wait_desc as ‘Replication’ then try marking the transactions as replicated using this command:
    EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
  4. After executing #3, if you’re still hitting up this error, then advise would be to install Replication component using the setup.exe and put the DB into publication (Use any dummy table) and then remove replication using the same command as mentioned in #2.

Off course, prior going into this series of steps you’ll have to verify a certain things respective to your environment:

  • Ensure that database integrity is rightly checked and validated.
  • Error log is rightly checked and there are no more errors available there, apart of this one.
  • Server resources are available in plenty.
  • Clean restoration was performed earlier while bringing this DB online.

Hope this helps!

Happy leaning.

Aman Kharbanda

Backup/Restore IO Size

By default, SQL Server will use jumbo sized IOs (typically of 1MB) to perform backup and restore operations. These can adversely affect the IO latency for your array. Whenever possible, use MAXTRANSFERSIZE=65536 on your BACKUP or RESTORE statements. This could make your backups and restore take a tad longer, so test accordingly. If you are leveraging Transaction Log Shipping, you might have no way to tune this, as sqllogship.exe doesn’t offer a means to tune this parameter. If log shipping backups/restores are affecting your performance, considering rolling out scripts to perform log shipping manually.

If you are not achieving good throughput from your backup/restore operations after using the MAXTRANSFERSIZE=65536 parameter, considering bumping up the number of buffers available to the process by using the BUFFERCOUNT parameter. In our testing, a 1.5TB database backed up in 24 minutes, 38 seconds at default settings, and 25 minutes, 6 seconds with 64Kb IO block size and BUFFERCOUNT=2048.

Happy Learning.
Aman Kharbanda




SQL Server Performance Dashboard

Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature of SSMS. These reports will allow the DBA’s to quickly identify whether there is a current bottleneck on their system and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem.

Common performance problems that the dashboard reports may help to resolve include:

– CPU bottlenecks (and what queries are consuming the most CPU)
– IO bottlenecks (and what queries are performing the most IO)
– Index recommendations generated by the query optimizer (missing indexes)   
– Blocking
– Latch contention

The information captured in the reports is retrieved from the DMV’s. There is no additional tracing or data capture required, which means the information is always available and this is a very inexpensive means of monitoring the DB server.

Here are the detailed steps of Installation/Configuration of Performance Dashboard-

(a) Installing SQL Server 2012 Performance Dashboard Reports

 The following steps will install the Performance Dashboard Reports-

 1) Open the below link in your web browser and download SQL Server 2012 Performance Dashboard Reports:–pd     
2) Navigate to the location where you saved the SQLServer2012_PerformanceDashboard.MSI file and double-click it. You will receive a welcome screen. Click ‘Next’.
3) The next screen will present you with the license agreement. Read the agreement, select ‘I accept the terms in the license agreement’, and click ‘Next’.
4) The next screen will ask you the required registration information. Enter the details and click ‘Next’.
5) You will be asked to choose the program features for installation. Go with the default selection and click ‘Next’.
6) On the next screen, click ‘Install’ to begin the installation process. You can choose to click ‘Back’ button to make any other changes to your installation settings.
7) Click ‘Finish’ to exit the installation wizard. Now, you have successfully installed SQL Server 2012 Performance Dashboard Reports.

(b) Configuring SQL Server 2012 Performance Dashboard Reports

 After finishing the installation, it is necessary to configure the SQL Server to use the Performance Dashboard toolset you have just installed. The default installation directory for SQL Server 2012 Performance Dashboard Reports is ‘C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard\’. Navigate to this location, search for the ‘setup.sql’ file, double-click it to open the Performance Dashboard Reports in SQL Server Management Studio. Next, establish connection to the SQL Server Database Engine instance where you need to install and access the reports.

(c) Using SQL Server 2012 Performance Dashboard Reports

 After you have successfully installed Performance Dashboard Reports, you need to consider the following-

  • You need to ensure that all functions and procedures accessed by the queries in reports must be present in all instances of SQL Server for which you need to carry out performance analysis. You need to search for the ‘setup.sql’ file after navigating to the installation directory and run the script. Close the window after the execution is finished.

Go to Object Explorer in SQL Server Management Studio, right-click on the server instance, and select ‘Reports’ → ‘Custom Reports’. In the ‘Open File’ dialog box, select the ‘performance_dashboard_main’ report file and click ‘Open’. You will be displayed a warning dialog box stating that you are about to run a custom report. Click ‘Run’ in this dialog box to open the ‘performance_dashboard_main.rdl’ file.

Sample reports are pasted here for your further reference.

Happy Learning.

Aman Kharbanda