Download Free SQL Server 2016 Developer Edition

Microsoft announced the general availability of SQL Server 2016 today. This version has many path-breaking updates for performance, security and business intelligence. On top of that, the Developer Edition of SQL Server 2016 and 2014 are now completely free! There is no restriction on features, capabilities or how long you can use them! Read on to find out how to download free SQL Server 2016 Developer Edition and other goodies.

In these Developer Editions, the databases do not have to be smaller than X GB, the feature Y will not be disabled, the time is not restricted to Z days (i.e. 180 days) and you don’t have to pay any $ Dollars. These Developer Editions have all the features of the Enterprise Edition, but should not be used for Production.

Download Free SQL Server Developer Edition

To download the Developer Edition, all you need is a free membership to the Visual Studio Dev Essentials and then go to the following URL to download the installation file.
Download Free SQL Server 2014 and 2016 Developer Edition

Download Free SQL Server Tools

If you don’t plan on downloading the full install of SQL Server yet, you can still get the latest tools.

New 2016 Version of SQL Server Management Studio (SSMS)

SSMS is a free client tool for general SQL Server management and TSQL development. It is compatible with prior versions of SQL Server too.

New 2016 Version of SQL Server Data Tools (SSDT)

SSDT is a free development tool that is a subset of Visual Studio IDE. It is focused on database and business intelligence related development i.e. TSQL, SSIS, SSRS and SSAS.

Download Free eBook

While at your downloading spree, get the final version of the free MS Press eBook that gives an overview of the latest features in the SQL Server 2016 version.

Introducing Microsoft SQL Server 2016
Mission-Critical Applications, Deeper Insights, Hyperscale Cloud

Authors: Stacia Varga (t), Denny Cherry (t), Joseph D’Antoni (t)
Pages: 215
ISBN: 978-1-5093-0195-9

Although the title says that it is an introductory book, it is certainly not a beginner’s level text. In its nine chapters, it introduces the new features of SQL Server 2016. So it will be more useful if you have already worked with SQL Server for a while and are aware of features (or possibilities for enhancements) in the previous versions.

Free SQL Server eBook - Introducing SQL Server 2016

Gotcha – SSIS ImportExport Wizard Can Kill Your Diagrams

Some things are meant to be learnt the hard way. And that is how I learnt about today’s gotcha.

Overview of [sysdiagrams]

I have been working on an ERD (Entity Relationship Diagram) recently and used the Database Diagram feature in SSMS for this purpose. When you try to create a diagram for the first time in a database, a message box asks you if you would like to create diagramming objects.

Confirmation dialog
Image 1 (Click to enlarge)

On clicking Yes, a system table by the name of [sysdiagrams] is created in the same database that you are creating the diagram in. The diagrams are stored in this table. SSMS shows the diagram in the Database Diagrams node of the Object Explorer tree view.

Diagram and its table
Image 2 (Click to enlarge)

The Scenario

I was creating my diagram in a test environment. At some point I had to refresh all data from the production environment. The easiest way for me to do a full refresh is to use the Import and Export Wizard. The wizard can be launched either via SSMS context menu or in the Business Intelligence Developer Studio as an SSIS project. As usual in the case of quick data refreshes, I selected all tables using the top-left Source checkbox in the wizard, and chose the options to delete and reinsert all rows with identity values.

Select all tables and reinsert rows
Image 3 (Click to enlarge)

When the wizard ran successfully, my database diagram at the destination test system was missing!


Upon some research and trials I found that that if the diagramming capabilities in the source and destination servers are enabled, the wizard includes the [sysdiagrams] table automatically in the list of tables to refresh. As you can see, there are no other system tables in the wizard except the [sysdiagrams] table so it is easy to miss it in a long list.

sysdiagrams is included
Image 4 (Click to enlarge)

So in my case, all data in the destination [sysdiagrams] table was deleted. There were no diagrams at the source so nothing was imported for this table. This outcome would have been the same with the drop and recreate option too because the destination table would have been recreated.


One needs to be careful while using the Import and Export Wizard. Uncheck this table in the selection list to preserve the diagrams at destination.

How To Have a Colorful Status Bar in SSMS. And Why is that Useful!

Would it be helpful if the status bar of SSMS query windows for PROD environment were red, QA were green and DEV were blue? Yes. The query windows will be distinguished and reduce the chances of oops moments. If you frequently open simultaneous query windows for multiple environments/servers in SSMS, then you must learn about this SSMS feature. Don’t miss reading a word of caution at the end!

The following is the default status bar for a query window in SSMS.

The status bar can be made Green (or any other color) like the example below.

All you need to do is to expand the Options on the connection dialog …

… and pick a color of your liking as shown below.

These color choices will persist even when you close and reopen the SSMS.

Although, setting up a different color for all individual servers is possible but having too many colors could be confusing! You might find it useful to set one specific color per environment. For example, Red for PROD servers, Green for QA servers and Yellow for TEST servers.

How to Reset it Back to Default Color:

Open the Connect to Database Engine dialog box and remove the check for the Use Custom Color checkbox. Click on Connect. All new query windows for the server you are trying to connect will revert back to the default color. Any open query windows for that server or others will not be affected. I’m not aware of any option to reset this for all connections at once.

A Word of Caution:

A new color selection is applied to the query window immediately but the effect of removing a color is visible only with new query windows, not the ones currently open. Whenever you change the connection of any open query window, always verify that the expected color is showing on the status bar.

Recover unsaved queries after SSMS crash or PC restart

You are using the query editor in SQL Server Management Studio (SSMS). You have spent an hour writing and tweaking a query. Then probably another 15 minutes to format it to your liking. But you haven’t saved it yet. If the SSMS crashes now before you could save your work, is all of your effort wasted? Maybe not. The in-built auto-recover features make it possible to recover unsaved queries after SSMS crash. I discuss all the manual or tool based recovery options in this article.

Auto-Recovery in SSMS

The SSMS developer team anticipated this situation so they have built in some auto recovery options in the tool. Usually, the next time you start SSMS after a crash, it tries to recover your unsaved queries. The recovered files show up in a dialog box as shown below. The dialog box has a list of files that SSMS was able to recover. You can select the files that you wish to salvage.

The file names are cryptic with no indication about their content. This kind of naming is ok if you were working with only one query window and know which query is being salvaged. But if you had multiple query windows open at the time of crash, the cryptic file names will be of no help. So you might want to recover all the files in this list and review them later. Next, make a note of the folder locations where the files will be recovered so that you can take a look at them. Now you can click the Recover Selected Files button.

The auto recovery dialog will help recover unsaved queries after SSMS crash
SSMS Query Recover Dialog

What If Auto-Recovery Does Not Work

Sometimes SSMS may not present the recovery dialog box. It might seem that all the work is lost. Well, most of the queries (if not all) can still be recovered because the recovery dialog shows where are the backup files located.

Windows XP

On Windows XP, SSMS saves auto recover copies of queries as you work, in the folder –

C:/Documents and Settings/<user name>/My Documents/SQL Server Management Studio/Backup Files/Solution1

Windows 7

On Windows 7, the queries can be recovered from –

C:/Users/<user name>/Documents/SQL Server Management Studio/Backup Files/Solution1

Recovery Folder Content

The names of the auto saved files are cryptic. So you will need to open all the files to find the one you are interested in.

Recover unsaved queries after SSMS crash in this folder
SSMS Query Auto Recover Location in Windows XP

Other Options

If auto recover does not work help for any reason, then here are a few more options.

SQL Server Query Cache

You can try recovering TSQL statement text using some of the DMVs and DMFs. The sys.dm_exec_requests along with sys.dm_exec_sql_text can show the statement text. But this is helpful only if you executed the query and the query is still in SQL Server’s memory. If the query text was just sitting in the editor and you never executed it before the SSMS crash then this method cannot help.

USE [your_database_name]
s.last_execution_time AS [ExecutionTime], 
t.text AS [Statement] 
sys.dm_exec_query_stats AS s
sys.dm_exec_sql_text(s.sql_handle) AS t
s.last_execution_time DESC

Third Party Tools

Some third party tools like SSMS Boost (free), Red Gate SQL Prompt (paid after trial), SSMS Tools Pack (paid after trial) etc. can help in a situation like this, but only if you had them installed and running at the time of the crash. They cannot help otherwise.

While talking about tools, you can even up vote this Connect item to add an undo closed tab option in SSMS.

Conclusion and Best Practices

  • Save your work frequently. CTRL+S is your friend. This advice is good not just for web designers but software developers too!
  • Use a version control system like Git, SVN etc.