July 15, 2008 at 1:12 am
Dear all,
What is the proper procedures of SP2 installation ?
My production server has been running for more than 2 years, I want to upgrade it from SP1 to SP2 + Hot Fix, what should I do ?
Do I need to remove all user connections first ?
I had some unhappy experiences in SP2 upgrade : the SP2 upgrade process failed but no solution found, what I could do was : re-installation, but failed still.
But now I think maybe the reason was : I hadn't close all user connections.
Do you have any experiences in SQL 2005 SP2 upgrade sharing with me ?
July 15, 2008 at 5:10 am
When I applied SP2 I had the opportunity and time-window to do it off working hours. In my case I had no active users. I know it is not required to change the database to single user, but I do not know how active users are handled. I should expect it is better to disconnect them, when possible.
I started with creating a full backup and applied SP2 without any problems.
July 15, 2008 at 5:25 am
Hi, before you apply the SP 2 hotfix, please remember that SQL Server service must be restarted.Also restart the computer before the change is applied.
Even I think its better to check that no users are connected. So the best way, is to do it off peak hours.;)
July 15, 2008 at 6:36 am
In additon to all your database backups, I'd backup all binaries as well. You never know, this could get you back to your current state in the event of upgrade failure.
-- You can't be late until you show up.
July 15, 2008 at 7:16 am
Adding to the above::
1. send out a outage notice. (IMP)
2. restart your SQL service/SSIS/SSAS, to get rid of any erreneous connections.
3. Log onto the remote server as a Administrator.
4. Start the patch process.
now if you patch fails(which i had similar situations).. Look into the your DATA Folder security and include the SQL Service account as a user in the folder security with READ/WRITE permissions. also Check your logon permissions (v.IMP).
The patch itself does many Security changes on the RUN.. to these folders and has a good Rollback Procedure for indivudual components.
Always check your @@version after the path...
Maninder
www.dbanation.com
July 16, 2008 at 2:55 am
If you want to apply SP2 and a CU without rebooting, you must:
1) Stop all SQL Server services
2) Stop the .Net Optimization service (stop both the x86 and x64 services on a 64-bit OS)
Do both of these before starting the SP2 apply and you should avoid any messages about a reboot being required. However, I recommend doing a reboot after the CU apply, just so you know for certain how the box will behave after it is rebooted.
I also recommend you install the latest BOL, as it includes details that are introduced in SP2 plus other documentation fixes and upgrades.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 16, 2008 at 3:15 am
Thanks for all of your replies.
Do you are these Service Pack 2 & Hot-Fix are the latest ones for SQL Server 2005 ?
Or there are still some updated version ?
Service Pack 2
32-bit
SQLServer2005SP2-KB921896-x86-ENU.exe
64-bit
SQLServer2005SP2-KB921896-x64-ENU.exe
Hot Fix
32-bit
SQLServer2005-KB934458-x86-ENU.exe
64-bit
SQLServer2005-KB934458-x64-ENU.exe
July 16, 2008 at 3:22 am
Guide lines to install any PATCH or SP is as follow.
Stop all SQL Server Services.
Install the SP or PATCHES.
Start the SQL Server Services.
Basit Ali Farooq
MCITP Database Administrator
Microsoft Certified Professional Developer (Web Applications)
Microsoft Certified Database Administrator
Microsoft Certified Systems Engineer
Microsoft Certified Systems Administrator
CIW Security Analyst
Cisco Certified Network Associate
July 16, 2008 at 8:01 am
onlo (7/15/2008)
Dear all,What is the proper procedures of SP2 installation ?
My production server has been running for more than 2 years, I want to upgrade it from SP1 to SP2 + Hot Fix, what should I do ?
Do I need to remove all user connections first ?
I had some unhappy experiences in SP2 upgrade : the SP2 upgrade process failed but no solution found, what I could do was : re-installation, but failed still.
But now I think maybe the reason was : I hadn't close all user connections.
Do you have any experiences in SQL 2005 SP2 upgrade sharing with me ?
Ensure your master db and resource db files are in the same location. That has bitten me in the past, and it sounds you may be having the same problem.
Also, ensure you are installing under a Local Admins account and the account is sa on the instance as well.
A good rollback procedure: copy your system-database (including resource) and the SQL installation files to the O/S drive and do a LUN copy (backup) of that drive. If the installation fails, simply restore the LUN copy and refresh the existing (corrupted) system-database and install files with the files from the LUN backup.
What is the error you are getting?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 16, 2008 at 9:31 am
Marios is right, I had forgotton this issue.
There is a documented process for moving master DB data and log files, and for moving the resource DB files. I had a SQL 2005 SP1 installation where these 4 files were all in their own folders, and SQL Server worked fine.
The SP2 upgrade will place a new copy of the resource database mdf and ldf files in the default location used when you installed SQL Server. It totally ignores anything in master DB about where the ersource DB is located. When SQL Server starts during the upgrade, it detects that the resource DB version no longer matches the upgraded master db version and SQL crashes. You are now stuffed.
You can try copying the resource DB mdf to the location given in master DB, but I did not get SQL running properly even with this. As it was a C&B environment I uninstalled SQL and re-installed a fresh copy.
I recommend that master DB and resource DB mdf and ldf should always remain in their default locations. The SP2 install does not honour the method given in BOL for moving these databases, and I do not trust any further CU or SP install to be free of this bug.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 16, 2008 at 10:07 am
EdVassie (7/16/2008)
I recommend that master DB and resource DB mdf and ldf should always remain in their default locations. The SP2 install does not honour the method given in BOL for moving these databases, and I do not trust any further CU or SP install to be free of this bug.
This is a perfect illustration of MS not practicing what they preach... 😉
I have come to realize (kinda late) that leaving these files in the default location will save me lots of these headaches later on.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply