SQL Server 2016 SP1 Released!

SQL Server 2016 SP1 was recently released!

You can read more about the changes in SP1 here:

https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/

https://blogs.technet.microsoft.com/dataplatforminsider/2016/11/16/sql-server-2016-service-pack-1-generally-available/

The biggest set of changes in SQL Server 2016 SP1 revolve around many of the features that were previously only available in SQL Server Enterprise Edition (such as Change Data Capture), now also being offered in SQL Server Standard and SQL Server Web Editions as well!!

You can download SQL Server 2016 SP1 from here: https://www.microsoft.com/en-us/download/details.aspx?id=54276

Performing database deployments using AliaSQL

Most continuous integration build processes have a need to perform database deployments over the course of development, which is why a tool such as AliaSQL arose to fulfill that need!

You can find out about AliaSQL from here: https://github.com/ClearMeasure/AliaSQL

AliaSQL is available for download directly as an executable or can be installed via a NuGet package.

Once you download the tool, you will probably want to learn how to use it, so you should check out the "Getting Started" guide here: https://github.com/ClearMeasure/AliaSQL/wiki/Getting-started

If you want to look at a demo project, you can find that here: https://github.com/ericdc1/AliaSQL-Demo/tree/master/Source/Database.Demo/scripts

While I was working with AliaSQL, however, I found a problem in the samples that were provided in the Getting Started Guide:

AliaSQL.exe Update .\sqlexpress Demo ./scripts

First of all, the example assumes that you have SQL Server Express installed.  Fortunately, the name of the database server can just as easily be substituted as follows:

AliaSQL.exe Update SQL2014Svr Demo ./scripts

But what about remote SQL Server instances?  What do you do then?  Thankfully, a solution for that is also available:

AliaSQL.exe Update SQL2014Svr Demo ./scripts mysqluser mysqlpassword

Lastly, I could never get this command to work!!  So what was wrong?  Well, even though the AliaSQL.exe file is placed in the root of the scripts directory, the path that is used in the command assumes that AliaSQL resides OUTSIDE of the scripts directory!!  This was fixed by using the following command instead:

AliaSQL.exe Update SQL2014Svr Demo . mysqluser mysqlpassword

Notice that I no longer reference the relative path to the ./scripts directory and simply assume that the AliaSQL.exe exists at the root of the scripts directory (as follows the pattern in the demo and the KickStarter project).

That was all that was needed for working with AliaSQL to do my database deployments!

For your reference, I have also provided a sample PowerShell script on doing database deployments using AliaSQL:

Microsoft SQL Server Compact 4.0 Package Failed while installing Visual Studio 2015

I was recently installing Visual Studio 2015 Update 3 on a server with SQL Server 2016 installed when I suddenly encountered the following error message:

I had never faced this error message on machines previously installed with SQL Server 2014, so it seems to be a specific conflict with SQL Server 2016.  For all intents and purposes, the failure of this installation package did not impact my development at all, but it is just something to watch out for in case your development is dependent on SQL Server Compact Edition 4.0.

New authentication methods in SQL Server 2016

I recently set up and configured a SQL Server 2016 instance on a virtual machine and when I logged into the system for the first time, I encountered a welcome change to the authentication screen!

As you can see from the above image, SQL Server 2016 has now added support for "Active Directory Password Authentication" as well as "Active Directory Integrated Authentication"!

If you can read this article, you can get some more details on these 2 new types of authentication mechanisms: https://msdn.microsoft.com/en-us/library/ms189721.aspx

If you read through the article, you can see that both of these authentication mechanisms are targeted towards authenticating against Azure Active Directory!

For more information on connecting to SQL Server using Azure Active Directory, you can check out this article: https://azure.microsoft.com/en-us/documentation/articles/sql-database-aad-authentication/

SQL Server 2016 RTM Released!

SQL Server 2016 RTM has been released to MSDN!

You can download it from MSDN Subscriptions now here: https://msdn.microsoft.com/subscriptions/securedownloads/?#searchTerm=&ProductFamilyId=650&Languages=en&PageSize=50&PageIndex=0

Migrate from Oracle to SQL Server--for FREE!

Microsoft is offering FREE licenses of SQL Server for customers wishing to migrate away from Oracle!

You can claim this limited-time offer here: https://www.microsoft.com/en-us/server-cloud/sql-license-migration.aspx

SQL Server on Linux!!

If you haven't already heard the news, Microsoft has recently announced that SQL Server will become available on the Linux platform!!

https://blogs.microsoft.com/blog/2016/03/07/announcing-sql-server-on-linux/

Unfortunately, you will have to wait until sometime next year (in 2017) to get this exciting release of SQL Server, but you can follow the progress of this product development here: https://www.microsoft.com/en-us/server-cloud/sql-server-on-linux.aspx

ADFS support coming to SQL Server 2016!

If you have used any version of SQL Server Analysis Services or Reporting Services in the past, you may already be well aware of the fact that neither of theses services currently support integration with ADFS (Active Directory Federation Services).

However, with the release of SQL Server 2016, ADFS support will finally be coming to SQL Server!

If you track this Connect feedback item (https://connect.microsoft.com/SQLServer/feedback/details/1340165/provide-adfs-support-for-ssas-and-ssrs), you will see the comments from Microsoft:

Thanks for your feedback. ADFS support, aka claim authentication, is a frequently requested feature. It'll come some time after SQL Server 2016 ships. 

So Microsoft is offering yet another reason for us to upgrade our existing installations of SQL Server 2012 and SQL Server 2014 to SQL Server 2016!

If you are like me, you will be eagerly anticipating this capability since it will provide true SSO support for integration with several Microsoft products such as SharePoint, Dynamics CRM and Project Server.  If Team Foundation Server 2015 also adds support for ADFS, that will sweeten the deal even further!