Installation Issue with Windows 7, Intel RAID Storage Controller and Advanced Format Disk

I had to re-install Windows 7 on one of my machines. It’s important to note that I had to re-install it, means, I had Windows 7 already running on that box.

The installation started as expected, created the partition, copied the files, started to setup the system. And when it comes to start the services, the installation aborted, telling “Windows Setup could not configure Windows on this computer’s hardware”.

That was a little bit surprising, since Windows 7 was running on this machine before. Searching the Web, I found no explanation. Then I remembered that I already had some trouble when I changed the system’s hard disk to a new one having the so called “Advanced Format”. In my case, I switched to a Western Digital WD5000BPKT. After that, Windows Update did not worked any more. This was solved by installing a new driver for the Intel RAID storage controller.

The installation image I was using was a Windows 7 RTM. So I decided to create a Windows 7 SP 1 installation image, hoping the driver fix was already included in SP 1. What can I say… the installation did its job 🙂

Summary: To run into this problem, your machine has to have an Intel RAID storage controller and an Advanced Format disk, where you want to install Windows 7 RTM on it. My solution was to use Windows 7 SP 1 instead.

The Knowledge Base article 2455673, which I found later, describes the details and shows two other resolutions. But I do prefer the SP 1 way.

Disk Space Side Effects of SQL Server Data Tools

SQL Server Data Tools (SSDT) installs LocalDB, a new version of SQL Express. For details on LocalDB, please refer to Introducing LocalDB, an improved SQL Express.

As Kevin Cunnane explains in the SQL Server forum thread Importing database always defaults to localdb, creating a SQL Server database project by SSDT always creates a database on LocalDB, (localdb)\Projects since September 2012 update of SSDT. And of course, one cannot change this behavior – well, why should we, just because we do have a SQL Server installed on our dev box?

Kevin points out that the database is empty and only populated on debug/deploy. So another 4 MB are wasted on my profile drive. Why on my profile drive? Because the database files are located on %user%/AppData/Local/Microsoft/VisualStudio/SSDT. Of course, you can delete the database, also from within SQL Server Management Studio, as soon as you close the database project. But when you re-open the project, the database is re-created.

The system databases, error logs and event files are located at %user%/AppData/Local/Microsoft/Microsoft SQL Server Local DB/Instances/Projects (or V11.0, if you do not have September 2012 update or later installed). Here you can remove the event log and system health files from time to time, if LocalDB won’t do so. If you like to test it (I didn’t): the directory is set in the registry under HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\UserInstances. In case there is more than one entry below this key, have fun 😉 This seems to happen when you have SSDT installed before the September 2012 update, and then updated it.

Trying to change some settings of LocalDB using SQL Server Management Studio, like database default locations or error log recycling, led to an ‘Access denied’ exception on my machine.

But that’s not all. There is also a .dbmdl file created, having about 8 MB on my really small sample database project. This file can be deleted too, but will be re-created. Some guys on the web say that .dbmdl files are only used as a cache per user. This is why they should not be put under source control.

So in case you run out of disk space, check your profile for LocalDB database, log or health files and your projects for .dbmdl files.

Where Do SSDT SQL Scripts Connect To?

In some software projects I used empty Visual Studio solutions to create some kind of database projects. I added SQL script files to the solutions to create all database objects, like tables, functions, stored procedures, views, constraints, and so forth. A batch file was used to run all these scripts against the database.

One common part of these script files was a snippet of sample code to test the database object, e.g. a stored procedure or function, directly from inside Visual Studio. This snippet both documented the use and gave a quick way to test changes. Of course, this snippet was intended to be used only when connected to a local SQL instance to avoid damage in any other environment!

Playing around with SQL Server Data Tools (SSDT) in VS 2012, I was looking for the ability to connect a function script to my existing local database. Under the menu item SQL / Transact SQL Edit / Connection there is the ability to connect to a database. To my surprise, I was not asked to which SQL instance or database I would like to connect to. At least, clicking the connect menu item seemed to do nothing at all.

But running the script worked, and the result window told me that the function was created successfully. I was wondering where. Searching for a database selection option in the toolbars, as I know it from the SQL script files in VS 2010, failed. There is no such possibility. Was the function created in the master database? But which SQL Server instance was used?

Well, Visual Studio showed me the answer to these questions all the time, but I did not noticed it.

When you edit a function or a stored procedure script file of a SQL Server Database project – not a simple SQL Query file! – you will find these information in the properties window on the right (or wherever you have placed it). The properties window shows the connection name, logon name and so forth, and whether the connection is open or closed. You cannot change any entry. These properties are not available for table script files of the project, which can’t be connected to any database at all from within Visual Studio.

But the properties window does not tell you the name of the database. Almost, it is the project’s name. But it doesn’t have to. The name can be found in the .sqlproj.user file, located in the project directory. Of course, you can edit this file to change the name.

Create a SQL Azure Database using SSDT

To get into Microsoft Azure application development, I was playing around with SQL Server Data Tools (SSDT), trying to create a database on SQL Azure.

I started creating a simple database, having two tables with a few columns. As done before with other tools, I was using the extended properties to document the database ‘inline’. The target platform was my local SQL 2012 instance. Deployment went fine, updating after some changes as well.

So the SQL Azure database server was created quickly, and I tried to deploy that database from my local machine to SQL Azure. It failed – of course (I wouldn’t have written a post just to tell you I succeeded 😉 ).

The reason surprised me, but a solution was found very quickly.

The reason why publishing failed is, that, beside others, SQL Azure does not support extended properties.

The solution is posted by Bill Gibson on the SQL Server Data Tools Team Blog in his post Migrating a Database to SQL Azure using SSDT.

I created a Schema Compare file as suggested by Bill, and put a second SQL Server project into my solution, following his instructions – and it worked fine 🙂

Now I have two database projects in my solution. The first one with full featured extended properties to document the database by itself. This one is used to build and update the local SQL 2012 version of the database. And the second one, based on Schema Compare, to be able to convert the SQL 2012 version into a SQL Azure compatible one and build and update the instance on SQL Azure. Of course, one have to take care not to update the scripts of the SQL Azure version, but keep in mind that SQL 2012 is the master.

This works smooth and easy in my simple test environment, because I do not use such things as user defined types (CLR) or XML indexes. Both are also not supported by SQL Azure. And migrating an existing database which uses these feature is not just letting Schema Compare doing the work. In such a case, one has to do some re-work, or probably re-think the decision to move to SQL Azure.

Btw, I think it’s worth to mention that when excluding the Default objects from schema comparison, this does not mean that column default values created by the CREATE or ALTER TABLE statement will be ignored. It seems that only those defaults created by CREATE DEFAULT are ignored. To me this is OK, since the SQL Server books say that CREATE DEFAULT will be removed in future versions of SQL Server anyway.

Windows Store App .NET Framework Subset

I’ve learned it when I created Silverlight apps – so why am I so surprised when playing around with Windows Store apps?

The .NET framework for Windows Store apps only ‘provides a subset of managed types‘. This subset officially is called ‘.NET for Windows Store apps’. For details, please refer to ‘.NET for Windows Store apps overview‘.

And, to make it no too easy, .NET for Windows Store apps is not just a subset. As Microsoft says, ‘in some cases, a type that you used in a .NET Framework desktop app doesn’t exist within the .NET for Windows Store apps. Instead, you can use a type from the Windows Runtime.‘ So – have fun searching the correct type and namespace…

Overall, I do really like the documentation of Microsoft. But again, .NET for Windows Store apps (who ever had the idea for this name!?) goes its own way. When looking for a class or type documentation on http://msdn.microsoft.com/en-us/library/default.aspx, there is a ‘Silverlight’ entry in the ‘Other Versions’ selection. Accordingly, I expected a ‘.NET for Windows Store apps’ choice. But there is none – maybe because of the long name? You can identify the .NET for Windows Store app support by this icon in the column in front of the member name: .Supported in .NET for Windows Store apps

Thanks to Microsoft helping me to stay young and flexible.