Side by side SQL versions, Entity Framework, and SQL Aliases

My laptop has both SQL Server 2008 and SQL Server 2012 installed for development purposes.  The SQL Server 2008 is at the root instance (local) and the 2012 version is installed has its own instance (local)\SQL2012.

I have been developing an application which has an app.config file with a SQL Server connection string (code first Entity Framework).  Since the solution I'm working on it in source control (TFS), it's shared; so I decided to make an alias for use in the connection string.  That way, others developing on this project can use their own local database without changing the configuration file (and then checking their connection string changes back in and irritating the rest of the dev group).  The alias that I created for my local laptop points to the SQL 2012 instance.

I opened up SQL Server Configuration Manager and created an alias to my 2012 instance (.\SQL2012).  However, when the update-database command is executed, the database was deployed to the root instance (SQL 2008), and not the 2012 instance that I wanted it deployed to (as identified in my SQL Alias).  I restarted the SQL Server service, but still got the same result when I called the update-database command again.

What I didn't realized is that I was using the SQL Server 2008 version of SQL Server Configuration Manager, not the 2012 version.  The 2008 version won't show the 2012 services but 2012 version will show all services.

To better explain, this screenshot is what I see when I run the 2008 version:


and this is the screenshot from the 2012 version:


Notice that 2012 version displays the SQL Server 2012 instance of the services.  Because I was using the 2008 version of the Configuration manager, I was unknowingly restarting the root server (2008) service, not the 2012 service.

After I restarted the 2012 instance, all worked just fine.

As far as Entity Framework is concerned, it seems to me that should expect an error that an instance can't be found when using the update-datebase command.  It would have been nice to see an error at a minimum (i.e. "Cannot find SQL instance, defaulting to root").

See https://msdn.microsoft.com/en-us/library/ms174212.aspx for exact locations of the specific version of SQL Server Configuration Manager.

BizTalk BAM XML Import/Export Gotcha

I've recently looked at some BizTalk code that another developer wrote, which has some BAM artifacts included in the solution.  Instead of saving the Excel xlsx file to the code repository, the developer saved the exported XML file instead.  See a portion of the original XML document below.  Notice that there is some PivotTable and RealTime Aggregation information within it.



I needed to make some updates to one of the BAM Activities, so I opened up a blank Excel document and selected "Import XML..."   The BAM Activities and Views load up just fine.



However, there was one big issue that I came across.  Sadly, the PivotTable and RealTimeAggregation information did not get loaded.  If I didn't take the time to review and compare my updates with the last version of the file (we use TFS as a code repository), I would have never known that I might have pushed an incomplete BAM process (compared to the previous version) into our database.  See the new output below.

As another test, I decided to do an import and then a direct export without any modification.  Sure enough, the RealTimeAggregation and PivotTableViews within the exported XML document were missing.

To correct this, I needed to do some reverse-engineering/trial and error with creating a new PivotTable within the Excel document and re-export the XML file out.  Then I needed to compare the old XML with the new XML to make sure I had the PivotTables and Aggregations the same.  At this point I decided to put the Excel document into source code as well... :)

Not sure if this is a bug, an oversight, or not the intent of the Import XML feature within Excel, but I would think that one could get 'mildly' burned if the source xlsx file isn't saved to your code repository.