Wither ASP.Net?

I ran into an interesting problem while installing MDS on a customer machine this week.  I installed SQL Server 2012 and Visual Studio 2012 on a Windows 7 box.  When I tried to configure MDS, I got a warning that said some Web Server software wasn’t installed.  After verifying that the list of things that MDS said it required was indeed installed, I decided I wasn’t going to be intimidated by a mere warning so I configured MDS anyway.  This worked fine but when I tried to bring up the client I got an error that said the URL was invalid.  I dug into the IIS configuration and found (with a little help) that the World Wide Web service was disabled.  I’ve seen this before – a security policy on the network disables IIS periodically to keep people from starting their own web sites on the corporate network.

When I enabled and started the World Wide Web service, I god an error that said a page handler wasn’t available.  This appeared to be an ASP.Net problem.  When I looked more into the IIS configuration I noticed the .Net 4 Application Pools weren’t there.  After a bunch of messing around with settings, I finally did a “Repair” install of Visual Studio 2012.  After this, the ASP.Net application pools were there and MDS ran fine.  My assumption is the problem was caused by the Web Server being disabled when the 4.5 .Net framework was installed the first time.  Not an earth shattering discovery but running into corporate security policies that disable web servers is pretty common so I hope this might save you an afternoon of tearing your hair out someday (yes, I’m officially blaming my shortage of hair on ASP.Net).

MDS Source Updates

I ran into an interesting requirement on a recent MDS engagement.  The customer wanted to give a large number of users read-only access to the MDS data.  While they didn’t want most of the users to be able to update the Master Data, they realized that the users would frequently find issues with the Master Data.  They wanted the users to be able to suggest changes and corrections to the Master Data so the data could be fixed in the source system.

We decided to use permissions to make the attributes of the key master entities read-only while creating an updatable text attribute that users could use to report issues.  This worked well but we needed to inform the source system owners when an issue was reported so they could fix it quickly.  Our first attempt was to just create a business rule that fired when the change-request attribute was updated and used the normal notification mechanism to send an email to the data steward when a change-request field was populated.  This worked OK but the data steward was forced to click on the link to go to the MDS database to find out what the change-request text said.  In this customer’s environment data stewards didn’t necessarily have access to MDS so this was inconvenient.  To simplify the data steward’s life, we wrote a customer workflow class that was called when the change-request attribute   was updated.  The custom workflow made a web service call into MDS to retrieve the contents of the change-request attribute, composed an email with the change-request and other pertinent information and made a DBMail call to send the email to the data steward.  We still have some work to do to configure distribution and make the mail more attractive but it looks like this is a viable solution.

File Format Versioning

File versioning is another thing that I assume most people understand but I still run into a lot of confusion about.  This came up in one of the 24 Hours of PASS sessions I watched,  The question was something like “does the format of the data in a SQL Server file change when you do an upgrade or does SQL Server understand both the new and old format”?

While there might be exceptions I’m not aware of, there is only one file format that a particular version of SQL Server understands.  This means that when you upgrade a SQL Server file – upgrade in place by installing a new version, restore to a new version, attach to a newer version – the format of the data in the file is changed to match the new SQL Server version.  This is enforced to avoid implementing code that behaves differently depending on the version that a page of data was created on.  If the data in the file wasn’t upgraded when the database engine is upgraded then SQL Server 2012 would have to be able to handle at least 5 different page formats – SQL 7, SQL 2000, SQL 2005, SQL 2008, and SQL 2012.  This would make the code very fragile and difficult to test.  You would also see bugs that would depend on which version of the database a page was originally written by.  You could also run into issues where performance was different in SQL 2012 for a database created in SQL 2000 and a database created in SQL 2012.  So for all these reasons, not changing the format of the data files when SQL Server is upgrade would be a very bad thing.

This leads to several behaviors we’re all familiar with:

·         You can restore a backup made on an older version to a newer version of SQL Server but you can’t restore a newer backup to an older version.  SQL Server knows how to convert a page from the older format to the newer format but not the opposite.  Note that the compatibility level of a database does NOT affect the format of the database pages.  All database pages in a SQL Server 2012 instance use the 2012 format.

·         Log shipping and Database Mirroring can transfer data from an older database version to a newer version but not the other way round.  Again, the page upgrade is a one way transformation.

·         You can attach a SQL 2008 database to a SQL 2012 instance but once you do, you can’t go back.

·         You can only upgrade a database by two versions – 2005 to 2012 works but 2000 to 2012 doesn’t.  This was a choice Microsoft made to limit the number of upgrade routines they have to develop, maintain, and test.  Isn’t this pretty arbitrary?  Sure, but think about whether you would want the SQL Dev and Test teams working on the upgrade from SQL 7 to SQL 2014 or working on new features for SQL 2014.

The one-way upgrade is the reason rollback planning is such an important part of upgrade planning.  If you attach your database files to an instance of a newer version and something happens, you can’t go back just by attaching the files to the old level or backing up the new level database and restoring it to the old level.  You also can’t use log shipping or mirroring from the new instance to keep the databases in the older instance current.