2011-07-29
12th Annual System Administrator Appreciation Day
Please remember "12th Annual System Administrator Appreciation Day".
2011-07-14
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.
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".
Then the installation checks the 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.
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.
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.
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.
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
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".
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". |
 |
| Fig. 2. The window "Select Features". |
 |
| Fig. 3. The window "Check Files In Use". |
 |
| Fig. 4. The window "Ready to update". |
The completion message also gives the path for the summary log of the update.
 |
| Fig. 5. The window "Complete". |
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.
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".
Subscribe to:
Posts (Atom)
Posts
