Windows Server 8 and SQL Server 2008 R2

Together with the very public release of Windows 8, Microsoft released Windows Server 8.
Earlier this year I attended TechEd North America, and asked the Microsoft people about a "Windows Server Next", but nobody could say anything. Actually most of the answers were like "I don't know anything about a new Windows Server version.". On the other hand there were a lot about SQL Server "Denali" and Visual Studio Next. The three products usually are released in a new major version rather close together.
But now the preview for Windows Server 8 is here. I got mine from my MSDN subscription. It is not general available like the Windows 8 desktop edition.

Installing Windows Server 8 on vmware was quite simple and without challenges. The installation was done on vmware workstation 8 as Windows 8 the other day.
I installed the full version. Windows Core I will take a look at another time. Especially when I have figured out how to install SQL Server "Denali" on Windows Server 8.

After the initial installation when the system started for the first time, I took a quick look at the used storage.

The storage amount used for the Windows installation is ok. This is nice when you consider consolidation by server virtualization.

Installing SQL Server 2008 R2 (Enterprise Edition x64) requires some .NET and Windows Installer.

This is handled by the installation. Just click OK. The screen shows nothing for a minute or two, but some activity can be spotted in the Task Manager.

In this case I installed Database Services with Fulltext Services, Reporting Services, Integration Services,  Business Intelligence Development Studio and all Management Tools.
After the installation, a little more store is used.

More precisely 5.9 GB are used by the SQL Server installation.


The new Start design I am working on getting used to.

In general the SQL Server 2008 behaves normally.

BTW - When I activated Windows Update, there were 13 critical updates for Windows Server Preview.

SQL Server on Windows 8

The next Windows has just been released as a preview, both as desktop and as server.
Downloading from MSDN took some time. It looked like I was not the only one downloading ;-)

When I tried to install the Windows 8 desktop as a virtual computer i vmware, the initial boot failed with a "HAL_INITIALIZATION_FAILED".
A little browsing using my favorite web search engine, I found several  indications that the problem is a new HAL (Hardware Abstraction Layer) in Windows 8. A HAL that vmware 7.n can not handle...
The next version of vmware (8.0) was released 2011-09-13, but the licensing is not open until the week of 2011-09-19.
Still it was no problem to acquire a trial license and install it (vmware workstation) on my desktop running Windows 7 Ultimate x64.

On the new vmware the Windows 8 installation went without any problems. :-)

The vmware tools also installed on the virtual Windows 8 without any issues.
One thing I learned the hard way is that installing the vmware tools locks the GUI on the virtual Windows 8.

I tried to install SQL Server "Denali", but it failed right in the beginning without any clues. No folders are created, and no entries are made in the registry.

The installation of SQL Server 2008 R2 (Developer Edition, x64) went as on Windows 7. Installing Service Pack 1 went as normal.
Also it looks like everything is running fine. :-)

The new Start menu in Windows 8 holds the SQL Server 2008 icons like this:

From a SQL Server perspective, I have not found a major difference between Windows 7 and Windows 8.

Servername in SQLCMD script

A variable can be used in both SQLCMD and T-SQL statements.
This is a very short example:
:Setvar _servername "SANDY.sqladmin.lan"
:CONNECT $(_servername)
SELECT
  N'$(_servername)' AS [_servername]
 ,SERVERPROPERTY('SERVERNAME') AS [servername];

12th Annual System Administrator Appreciation Day

Please remember "12th Annual System Administrator Appreciation Day".

SQL Server 2008 R2 SP1

Service Pack 1 for Microsoft SQL Server 2008 R2 was released 2011-07-11. The Service Pack is described in KB2463333, and the download is found here.
This is a short example on upgrading a test server running Windows Server 2008 R2 x64.

I downloaded the file "SQLServer2008R2SP1-KB2528583-x64-ENU.exe" and executed it as Administrator.
After some unpacking the first windows looks like when we are installing SQL Server.

Fig. 1. The window "SQL Server 2008 R2 update".

 After accepting the license terms, it is time to select what to update. The info to the right about language etc. is only shown when a item is selected in the field "Instances".

Fig. 2. The window "Select Features".

 Then the installation checks the files in use.

Fig. 3. The window "Check Files In Use".

 Like when installing SQL Server, a summary of the effective installation is shown before executing the installation. I like this opportunity to check before actually executing.

Fig. 4. The window "Ready to update".

Why the title is spelled with starting minuscles  where all other titles are spelled with starting capitals I do not know...
The completion message also gives the path for the summary log of the update.

Fig. 5. The window "Complete".

Please notice that the update does not require a computer restart, but the database instance is down during some of the update.
This is shown in the SQL Server Error Log with the start of the update in the elder SQL Server Error Log (e.g. "ERRORLOG.1").
2011-07-14 08:24:58.69 spid53      Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2011-07-14 08:24:58.70 spid53      FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
2011-07-14 08:25:07.12 spid13s     Service Broker manager has shut down.
2011-07-14 08:25:07.31 spid7s      SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.
2011-07-14 08:25:07.31 spid7s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
2011-07-14 08:25:07.36 Server      The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/SANDY.SQLADMIN.LAN ] for the SQL Server service. 
2011-07-14 08:25:07.36 Server      The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/SANDY.SQLADMIN.LAN:1433 ] for the SQL Server service. 

In this case the database instance was started about three minutes after the shutdown.
2011-07-14 08:28:47.26 Server      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 
Jun 17 2011 00:54:03 
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)


2011-07-14 08:28:47.28 Server      (c) Microsoft Corporation.
2011-07-14 08:28:47.28 Server      All rights reserved.
2011-07-14 08:28:47.28 Server      Server process ID is 2436.

Notice that the new version number is 10.50.2500. Before upgrade the version number was 10.50.1600.

When the database instance is started again, the SQL Server Error Log shows that a lot of changes is done after the database instance is online.
2011-07-14 08:28:50.83 spid7s      Database 'master' is upgrading script 'msdb_upgrade_discovery.sql' from level 171050560 to level 171051460.
2011-07-14 08:28:50.84 spid7s       
2011-07-14 08:28:50.84 spid7s      ----------------------------------------------------------------
2011-07-14 08:28:50.84 spid7s      msdb_upgrade_discovery starting
2011-07-14 08:28:50.88 spid7s      MSDB format is: SQL Server 2008
2011-07-14 08:28:50.92 spid7s      User 'sa' is changing database script level entry 4 to a value of 2.
2011-07-14 08:28:50.93 spid7s      User 'sa' is changing database script level entry 5 to a value of 2.
2011-07-14 08:28:50.93 spid7s      User 'sa' is changing database script level entry 6 to a value of 2.
2011-07-14 08:28:50.93 spid7s      User 'sa' is changing database script level entry 6 to a value of 0.
2011-07-14 08:28:50.94 spid7s      Running SQL Server 2005 SP2 to SQL Server 2008 upgrade script
2011-07-14 08:28:50.94 spid7s      ----------------------------------------------------------------
2011-07-14 08:28:50.94 spid7s       
2011-07-14 08:28:51.77 spid7s      Database 'master' is upgrading script 'sqlagent100_msdb_upgrade.sql' from level 0 to level 2.
2011-07-14 08:28:51.77 spid7s      ----------------------------------------
2011-07-14 08:28:51.77 spid7s      Starting execution of PREINSTMSDB100.SQL
2011-07-14 08:28:51.77 spid7s      ----------------------------------------
2011-07-14 08:28:52.00 spid7s      Setting database option COMPATIBILITY_LEVEL to 100 for database msdb.

... and so on

Most changes looks like they are done by executing T-SQL script files.

I tried afterwards to find the files, but I could not. Hopefully the files are deleted after the update. This is OK from an audit point of view.

In this case executing the last changes took about half a minute.

2011-07-14 08:29:18.99 spid7s      Recovery is complete. This is an informational message only. No user action is required.

In the same folder as the summary log (Fig. 5) a detail log is written in the file "Detail.txt". Where the summary log in the file "Summary_SANDY_20110714_081920.txt" is 5 KB, the detail log is 753 KB.
According to the summary log, the update took about ten minutes overall and about six minutes on the database instance.
Overall summary:
  Final result:                  Passed
  Exit code (Decimal):           0
  Exit message:                  Passed
  Start time:                    2011-07-14 08:19:31
  End time:                      2011-07-14 08:29:38
  Requested action:              Patch


Instance MSSQLSERVER overall summary:
  Final result:                  Passed
  Exit code (Decimal):           0
  Exit message:                  Passed
  Start time:                    2011-07-14 08:23:56
  End time:                      2011-07-14 08:29:33
  Requested action:              Patch
This fits with the start to end in the detail log.
2011-07-14 08:19:32 Slp: Log provider 'Microsoft.SqlServer.Chainer.Infrastructure.LogProviderFile' has been registered

...

2011-07-14 08:29:52 Slp: Setup result: 0

Short said the update took ten minutes overall where the update on the database instance took six minutes with three minutes downtime.

SQL Server Management Studio (SSMS) is upgraded by the same file. You do not have to do a seperate udgrade on SSMS using the file "SQLManagementStudio_x{64 | 86}_ENU.exe".