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.
https://msdn.microsoft.com/library/mt238290.aspx

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.
https://msdn.microsoft.com/en-us/library/mt204009.aspx

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
https://info.microsoft.com/Introducing-SQL-Server-2016-eBook.html

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

Free Developer Editions of SQL Server 2014 and 2016

Microsoft is releasing its Developer Editions of SQL Server 2014 and 2016 for free!

Developer Edition is the same as the Enterprise Edition but it is licensed for development and testing purposes only.

The Developer license for the previous versions (2008 R2, 2012) used to go for about $50 and up. Another option was to download the fully functional edition valid for 180-days and later buy a licence.

More information about Microsoft’s new approach to developer licensing is at our Facebook post –

Find Top 25 Inefficient Query Plans by CPU, IO, Recompiles, Execution Count

Microsoft TechNet Gallery is a treasure trove of scripts that can save you a lot of coding time or sometimes introduce creative ways of solving a challenge.

I came across this collection of scripts that will show you the top 25 inefficient query plans (in XML format) sorted by CPU, IO, recompiles, execution counts etc.

Download: http://gallery.technet.microsoft.com/Find-inefficient-query-88f4611f

SSIS Components Naming Conventions

Read a New Version of This Blog Post – SSIS Naming Convention – Updated for SSIS 2016

I wrote earlier about my naming conventions for SSIS variables that let me quickly figure out the source of the variable value very easily. This post continues on to the prefix-based naming conventions I use (or plan to use) for the 120+ components in SSIS packages.

While looking around online for SSIS component naming conventions, most of the suggestions are based on Jamie Thompson’s (Blog | @JamieT) post SSIS: Suggested Best Practices and naming conventions. He has tried to limit the prefixes to 3 or 4 characters and included most of the frequently used components. Using the prefixes as described in his post has helped me a lot in the maintenance phase of a package when my memory has faded about the functionality and the workflow. Some more motivation to adopt a naming convention can be found in 31-days of SSIS series by Jason Strate (Blog | @StrateSQL).

My goal was to make an exhaustive list of components with their prefixes so that –

  • I do not need to put on my thinking hat every time I start using a component for the first time.
  • The naming convention can be future-proof and avoid confusion with new components being introduced in SSIS platform.
  • Remove the guesswork (especially for new developers and maintenance teams who may not be SSIS developers primarily) by avoiding prefixes that sound too similar to another.

I created an Excel sheet of all the components (more than 120) that I found listed in MSDN, i.e. containers, connection managers, sources, destinations, tasks, and transformations. My prefixes are more elaborate than Jamie’s for the reasons mentioned above, but that is just my preference. You can use the Excel as a template for you organization and modify the prefixes to you liking.

The following is an example of one section from the Excel.

Download: Get the full Excel workbook at the TechNet Galleries

Example –

SSIS Component Naming Conventions

Folks, Give Your Eyes a Break

I work long hours looking at computer monitor(s). On certain days, the eye-strain used to get too heavy on my eyes and I couldn’t even look at people straight in the eye!

I needed some kind of reminder to pause, look away from the monitors, and maybe take a water break.

There are many programs that show cute pop-up reminders. But they are on the same screen that I’m trying to look away from in the first place! Also most of them are easy to ignore. I didn’t want a system lock screen because logging in frequently becomes an irritant after some time.

I’ve started using the good old Windows Task Scheduler along with the NirCmd utility by NirSoft. It is a simple EXE file and does not need any installation. While it can do a lot of useful stuff but I was interested in just turning the monitor on/off. Especially good if you work on laptops that don’t have dedicated on/off switch for monitors.

  • NirCmd.exe concise documentation and download page (scroll to the bottom to see both 32-bit and 64-bit versions).
  • NirCmd.exe extensive documentation page.

I extracted the 64-bit zip in my Downloads folder and created a simple batch file with the following commands –

CD "C:UsersAalam.RangiDownloadsnircmd-x64"
nircmd.exe speak text "Turning monitor Off in 5 seconds"
timeout 5
nircmd.exe monitor off
timeout 5
nircmd.exe speak text "Turning monitor on in 30 seconds"
timeout 30
nircmd.exe monitor on

Yes, it speaks too! Then I opened the Windows Task Scheduler and scheduled the batch file as a 30 minute job.

There you go! It turns off my monitor every 30 minutes for 30 seconds. I get up from my chair for a mini stretch, take a gulp of water, look at the farthest end of the room or maybe out of the window, while the monitor turns back on.

Now there could be some folks whose workplace enterprise policy won’t let them download an EXE file from Internet. You could use a screensaver instead. E.g. the following line in the batch file will trigger the Mystify screensaver. A reminder enough for the break. Also, if it is just going to be a single command then maybe you don’t even need a batch file. You can put this command in the scheduled task by itself.

@START /WAIT /MIN %windir%system32mystify.scr -s

The downside is that this job keeps running even after I’ve left my office. Might startle the janitor 🙂

Awesome collection of free Microsoft eBooks

Eric Ligman at Microsoft has posted a series of blogs posts with an awesome collection of free ebooks (in multiple formats) related to Microsoft technologies like SQL Server, Azure, SharePoint etc.

I’m posting the links to the original posts with full list of books here.

http://blogs.msdn.com/b/mssmallbiz/archive/2012/07/27/large-collection-of-free-microsoft-ebooks-for-you-including-sharepoint-visual-studio-windows-phone-windows-8-office-365-office-2010-sql-server-2012-azure-and-more.aspx

http://blogs.msdn.com/b/mssmallbiz/archive/2012/07/30/another-large-collection-of-free-microsoft-ebooks-and-resource-kits-for-you-including-sharepoint-2013-office-2013-office-365-duet-2-0-azure-cloud-windows-phone-lync-dynamics-crm-and-more.aspx

http://blogs.msdn.com/b/mssmallbiz/archive/2013/06/18/huge-collection-of-free-microsoft-ebooks-for-you-including-office-office-365-sharepoint-sql-server-system-center-visual-studio-web-development-windows-windows-azure-and-windows-server.aspx

 

A TechNet wiki page with links to more books. There could be overlaps with the previous links.

http://social.technet.microsoft.com/wiki/contents/articles/11608.e-book-gallery-for-microsoft-technologies.aspx

 

Another post with links of free Microsoft Office products training –

http://blogs.msdn.com/b/mssmallbiz/archive/2013/09/17/free-microsoft-office-training-resources-and-more-how-to-get-them.aspx

Parent-Child Table Dependency Script

Requirement:

You need to delete and then insert data into tables which have parent-child relationships defined via primary and foreign keys. The delete and insert commands have to be executed in proper sequence.

The sequence for DELETE is Child-before-Parent.

The sequence for INSERT is Parent-before-Child.

I created a quick script in response to a forum post. The script is not the best out there because –

  • It does not handle circular references in relationships
  • It takes 30 seconds or more if run on a database with hundreds of tables

I’m sharing it for my future reference and probably others with smaller number of tables would find it useful too.

Download script at –
http://gallery.technet.microsoft.com/Parent-Child-Table-45fa5268

Further reference:
For a faster (but not with all these output columns) script, look at Erland Sommarskog’s response in the forum thread.

Script output would look like the example below –

TableDependencyReport

How to Read MSDN Forums with an NNTP News Reader

If you indulge in Microsoft MSDN Forums (social.msdn.microsoft.com/Forums) frequently then you might have had a thought about using a News reader.

The MSDN Forums Help Wiki mentions an “NNTP bridge” by Microsoft to configure one’s News reader but that project seems to have been off the shelf since the release of the new MSDN forums. It seems there is no other information about the future availability of a new bridge which would be compatible with the current forums.

There is a CodePlex community project at Community Forums NNTP Bridge that provides the functionality to provide this bridge. Download the zip file (approx 1 MB). Extract and run the setup.exe to install. Its documentation is quite explanatory but I’d like to restate and add to it –

  • The bridge essentially acts as an NNTP server so it has to be running when you plan to use your News Reader. It can be turned off (closed) at other time.
  • You have to use the IP 127.0.0.1 as the NNTP server in your News Reader configuration while setting up the forums. This is not mentioned clearly in an otherwise good documentation. Screenshots below.
  • You have to turn on (enable) the NNTP option in your forum settings page before you can use the bridge.
  • You have to use the same Windows Live login (Hotmail, MSN, Live etc.) account credentials while starting the NNTP bridge server that you use for the forums.
  • Microsoft Outlook can not be used as a News reader but its cousins viz. Outlook Express, or more recently, Windows Live Mail (WLM) can be used as such. WLM is a part of the free Microsoft Essentials package. If you have other favorite news readers then give them a try too.

Other links:

Windows Live Mail Configuration:

WLMConfigurationGrayscale

Calculate TSQL Stored Procedure Complexity

I had a recent project where we planned to re-factor an existing application’s .Net and TSQL code. I wanted to find the complexity of the stored procedures and arrive at an estimate of the effort required to review all them. I had in mind that something like number of lines of code, number of dependencies and parameters in the stored procedure would give a good starting  point to create such an estimate.

I patched together a script that produced a report similar to the example below. I used that output and put in some more formulas to assign a complexity level (simple, medium or complex) and the approximate number of minutes required to review that procedure. I have not included those calculations here because they depend entirely on the estimator’s perception of complexity and the developer’s skill level.

The number of lines of code is a subjective metric here because it depends on the developer’s coding style and use of whitespace. Even then, it could be a useful reference point.

Complexity Report Screenshot

In case this seems useful then the script to create that report is available for download at Technet.

Download and rate the script at: http://gallery.technet.microsoft.com/Calculate-TSQL-Stored-831b683a

CHANGE HISTORY:
2012/Dec/01 – Version 1
2013/Feb/15 – Version 1.1 Added the CASE statement for complexity.
2014/Apr/29 – Version 1.2 Added Database and Schema name to the report.
Included objects that may not be refering to other objects.
Included views, functions and triggers.

Please feel free to share you feedback in the comments.

SSIS Package Development Effort Estimation

I blogged about a DTS to SSIS conversion effort estimation project that I had a chance to work upon previously. I had created an Excel workbook to help in that estimation.

That workbook was not just limited to the DTS to SSIS conversion estimates. It can be used for regular SSIS development project estimations too. I’ve updated the workbook a bit to reflect its generic nature.

The Excel workbook can help in estimating the effort required to create new SSIS packages. The new SSIS could either be a conversion of an existing DTS package or a fresh requirement. The estimation methodology would be similar in both cases.

Benefits of Using this Estimation Methodology

  • Standardize the estimates by using the same rules instead of guessing randomly, which could return widely varying figures depending on whom you talk to.
  • Standardizing also means that the estimation process for small, medium or large projects is the same. The importance of having a standardized process is directly proportional to the project size, to reduce the variance.
  • The estimates can be fine-tuned over time with real-world feedback so that the estimates get better with each new project.
  • Flexible to include the developer skill factor.

Two Steps to Fill the Estimation Workbook

  1. Put in the estimated number of minutes required for –
    a) creating each type of component in SSIS. This time could vary depending on the developer’s experience and skill. Sample values are included in the workbook.
    b) standard time additions for activities like analysis, error handling, logging and documentation. Sample values are included in the workbook.
  2. Update the workbook with the details like the type and number of all tasks, connections, transformations etc. that would be used in the SSIS package.
    a) If the basis is a DTS package then open the DTS and count the tasks, connections etc.
    b) If the basis is a fresh requirement then put the details according to those requirements. This will also help you to plan the package development.

A brief Help section is also included in the workbook.

Download

Download at: http://gallery.technet.microsoft.com/DTS-to-SSIS-Conversion-7153a957