Generic SQL Server installation/migration procedure

Support for Micosoft SQL Server in the context of Jiwa installations.

Generic SQL Server installation/migration procedure

Postby Hyperus » Fri May 09, 2008 4:16 pm

Having had to plan this for myself for this weekend, I put in a bit of research ready for my move from SQL2000 Enterprise to SQL2005 Standard (hence I cant just *upgrade*). Regardless - a new installation is better to ensure a completely clean start for SQL 2005 to operate with the best possible chance of success etc.

Note the references here to WSUS (Windows Software update Services) for users that have the WSUS Database integrated into their main SQL Server to avoid unnecessary SQL instances. If your WSUS Server is connecting to its OWN WMSDE Instance - ensure you DO NOT follow any steps that reference uninstalling or reinstalling WSUS.

. Full Sql Backups:- Perform a complete backup of ALL of your databases WITH data verification
. Make a note of ALL Attached Databases Including SUSDB from WSUS
. Make a note of the SQL instance name you are uninstalling and reinstalling
. Make a note of all Maintenance plans – you need to record all current actions – including specific notes about dates, times repetition
. Make a note of all SQL Server Agent Jobs - you need to record all current actions - including specific notes about dates, times repetition – ONLY include jobs not covered from Maintenance plans
. Export old SQL usernames using Method 2 script from http://support.microsoft.com/kb/246133 (sql2000 to sql2005 version)
. Export ALL WSUS MetaData to Cab and log files using WSUSUtil.exe Export
. Stop Exchange, IIS, WSUS AND SQL Services (Exchange Services share a common Search Component with MS SQL in some cases)
. Rename/Move WSUSContent Folder to preserve all WSUS Content Files
. uninstall WSUS IF REQUIRED*
. Move/rename SQL MDF and LDF files Away from old SQL Folder locations in case SQL2000 uninstall deletes them :)
. Uninstall SQL2000 and all associated SQL2000 Service packs
. CCleaner was used after uninstallations to clean up bad registry references (http://ccleaner.com/download/builds.aspx)
. Reboot Server to ensure a clean start ready for SQL2005 install
. Check all event logs (Application and System) for any serious errors before continuing
. Again Stop all Exchange Services and Exit any instances of MMC to ensure a clean SQL installation
. Install Latest Visual C++ Runtime Components (VCRedist2005) (http://www.microsoft.com/downloads/deta ... layLang=en)
. Install SQL2005 (Splitting programs to C: , Data to D:, and Logs to E:) – ENSURE the Instance is the same as the one removed
. Install SQL2005SP2 (http://www.microsoft.com/downloads/deta ... laylang=en)
. Install PostSP2 feature packs from here ONLY if required (http://www.microsoft.com/downloads/deta ... layLang=en)
. install these required critical PostSP2 Patches in THIS order:-
SQLServer2005-KB933508-x86-ENU.exe (http://www.microsoft.com/downloads/deta ... laylang=en)
SQLServer2005-KB934458-x86-ENU.exe (http://www.microsoft.com/downloads/deta ... laylang=en)
SQLServer2005-KB934459-x86-ENU.exe (http://www.microsoft.com/Downloads/deta ... laylang=en)
. Download and run CHECK for bad maint plans (sp2mpchk_setup.EXE)(http://www.microsoft.com/downloads/deta ... layLang=en) - sp2MPCheck should be run from a CMD prompt after running the expanded utility - this will assist with viewing errors.
. Visit http://update.microsoft.com/MicrosoftUpdate/ - install any required Post SQL2005 updates
. Reboot Again after all service packs are complete to get all services back to normal state
. Again Check all event logs (Application and System) for any serious errors before continuing
. Download and Update to latest SQL2005 Books online (*optional)(http://www.microsoft.com/downloads/deta ... layLang=en)
. Login to SQL 2005 Studio and execute the Create Users part of the User Export Script
. Move all SQL Databases back to their original location and re-attach them ALL
. Set Default DB properties – Default Data Locations (D: for DB and E: for trans logs) and also default protocols
. Set each database main option to correct state – EITHER Simple or FULL – depending on logging/recovery model
. if setting a DB for FULL, ensure you have re-created the transaction log backups/purges as required – and truncate on checkpoint if needed
. Reinstall/Reinstate/rekey all Maintenance plans
. Reinstall/Reinstate/rekey all SQL Server Agent Jobs
. Perform a DBCC checkdb as a precaution on each database – (or the lot at once using - exec master.dbo.sp_MSforeachdb 'Dbcc checkdb (?)' )
. Application Event Viewer holds the results of the DBCC CheckDB tests – check it carefully for errors again now
. Run the remaining part of the Sql User creation script that assigns default databases and additional server roles to the users created 2 steps up
. Install WSUS v3SP1 and import metadata exported from first wsus step to rebuild WSUS SQL Database on Main SQL instance – NOT WMSDE instance
. move/copy/permission WSUSContent Files to correct location and propagate permissions to all content files
. DRINK BEER :)

Usual disclaimers for the above - it is meant as a set of guidelines to assist with a safe/patched SQL2005 installation - some of the above patches may or may not apply to the options you have chosen to install.

/Roy Adams
Last edited by Hyperus on Mon May 12, 2008 9:12 am, edited 7 times in total.
Hyperus
Occasional Contributor
Occasional Contributor
 
Posts: 23
Joined: Mon Feb 18, 2008 1:12 pm
Location: Shailer Park, Queensland

Re: Generic SQL Server installation/migration procedure

Postby Scott.Pearce » Fri May 09, 2008 4:25 pm

I like the last step :D
Scott Pearce
Senior Analyst/Programmer
Jiwa Financials
User avatar
Scott.Pearce
Senpai
Senpai
 
Posts: 742
Joined: Tue Feb 12, 2008 11:27 am
Location: New South Wales, Australia
Topics Solved: 221

Re: Generic SQL Server installation/migration procedure

Postby Cutto » Mon Nov 10, 2008 4:12 pm

Hi All,

I borrowed this for last week-end and it worked a treat. The last step was extra nice.

Cheers
Peter
Cutto
I'm new here
I'm new here
 
Posts: 2
Joined: Mon Nov 10, 2008 3:07 pm
Location: South-East Melbourne


Return to Microsoft SQL Server

Who is online

Users browsing this forum: No registered users and 1 guest

cron