March 18, 2009 at 2:16 am
Hi,
we are doing in-place upgrade from sql server 2000 standard edition x86 to sql server 2005 developer edition x86.
I have couple of questions here.
1.We have the sql server 2000 installed as Mixed mode authentication.Now while installing sql server 2005, do we need to must choose Mixed mode authentication or we can choose the windows authentication too.
We are planning to go for windows authentication. Is this block the upgrade?
2.Sql server 2000 database engine and sql server agent are running under local system. Now we would like to change it to the domain sql server service account while installing sql server 2005.Can we do this?
Or Do we need to change the services to domain account 1st in sql server 2000 and do the upgrade?
or after the upgrade can we chage the services to sqlservice domain account?
which method should we follow?
thanks
March 18, 2009 at 10:00 am
Hi Mani,
Before upgrading did you check all the prerequistes for installing 2005
1. What is the current sp on sql 2000. (Because sql 2000 with sp3 or later can only be upgraded to sql 2005 developer edition.
2. Did you run the upgrade advisor before going for upgrade. (If you got any errors or warnings did you rectify them.)
3. My best recommendation is do a trial run in your test server before going on the production server.
SQL Server 2005 Upgrade Technical Reference Guide is the best one. Please read this carefully before going for upgrade. You can download it http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en
And coming to your questions
1. Authentication mode does not effect your upgradation. You can switch over to windows authentication before or after. But make sure you take care of users who are using sql authentication and their permissions to various objects. Otherwise they will not be able to access the db and you will be in trouble
2. You can change the service account of the sql server even after upgradation. But make sure that the service account has required permissions. (Personall i prefer it doing after the upgradation. Once you are sure that everything is working fine)
And finally make sure you take full backups of system databases and full and log backups of your user databases.
And happy upgradation. Please let me know how your upgradation went
🙂
[font="Verdana"]Thanks
Chandra Mohan[/font]
March 18, 2009 at 3:45 pm
Thank you,
I have couple of questions here.
1.In sql server 2000 all the logins were created using SQL Server Authentication, with a username and password.
If I choose windows authentication while installing and do the inplace upgrade to sql server 2005, the users will able to connect to the database as before the upgrade?
2.We have Remote servers in Security->Linked server->Remote Servers.So after the upgrade what shoud I do with these Remote Servers? We have NO linked servers.
thanks in advance
March 19, 2009 at 3:31 am
Be aware that an in-place upgrade to SQL Server 2005 can fail leaving SQL totally unusable. You need to include this scenario in your planning.
If this happens, your options are:
a) Restore the system disk to a save point or image taken immediately prior to the upgrade so you have a working system again. Then uninstall SQL 2000 and install SQL 2005.
b) Uninstall the dead SQL Server, then proceed with a clean install of SQL Server 2005.
You should detach all your user databases before starting the upgrade, and re-attach them when you know the upgrade is successful. If you have any issues during the upgrade, you really do not want the user databases to be affected.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply