April 15, 2010 at 7:40 am
Hi Friends,
New server Details as below as
1. OS windows server 2008 R2 Enterprise Edition
2. SQL SERVER 2008 (SP1) Enterprise Edition
Old server Details as below
1. OS windows 2003 server Enterprise Edition
2. SQL Server 2000 (SP4) Enterprise Edition
The following steps what I have done for upgrade From SQL SERVER 2000 to SQL Server 2008
1. create seprate sqladmin local windows user and Include Administrators permission. ( I am not using Administrator user)
2. Installed SQL SERVER 2008 with mixde mode authentication and applied SP1.
3. Create New database name as same as old database name(SQL2000) with compatible 100(SQL2008)
4. Restored database in SQL2008 version from SQL 2000 which last day backup file .BAK,
5. check DBCC checkdb command for checking if any database consistency error.
4. updated database statistics
6. Ran dbcc updateusages (dbname)
Finally I want to ask them s/w team and connect existing application which is already running in SQL SERVE 2000 box. (not tested)
1. Application need to be changed new IP address in connection string, Is it working existing user? or create new user. I am thinking old user will be working because I restored full backup.
Please suggestion me, if any additional work should follows database and application level.
Thank you very much for valuable comments.
April 15, 2010 at 7:51 am
Hi,
SQL2008 comes with "enforce password policy" on by default. if you have users with weak passwords either you should make them strong or disable this for each user using SSMS. Plus having object_level permission may be needed.
Regards,
MShenel
April 16, 2010 at 8:12 am
ananda.murugesan (4/15/2010)
Hi Friends,New server Details as below as
1. OS windows server 2008 R2 Enterprise Edition
2. SQL SERVER 2008 (SP1) Enterprise Edition
Old server Details as below
1. OS windows 2003 server Enterprise Edition
2. SQL Server 2000 (SP4) Enterprise Edition
The following steps what I have done for upgrade From SQL SERVER 2000 to SQL Server 2008
1. create seprate sqladmin local windows user and Include Administrators permission. ( I am not using Administrator user)
2. Installed SQL SERVER 2008 with mixde mode authentication and applied SP1.
3. Create New database name as same as old database name(SQL2000) with compatible 100(SQL2008)
4. Restored database in SQL2008 version from SQL 2000 which last day backup file .BAK,
5. check DBCC checkdb command for checking if any database consistency error.
4. updated database statistics
6. Ran dbcc updateusages (dbname)
Finally I want to ask them s/w team and connect existing application which is already running in SQL SERVE 2000 box. (not tested)
1. Application need to be changed new IP address in connection string, Is it working existing user? or create new user. I am thinking old user will be working because I restored full backup.
Please suggestion me, if any additional work should follows database and application level.
Thank you very much for valuable comments.
Hi,
first. Step 3 is not neseccary but you have to transfer the user befor you restore the db because of orphaned users. for transfer user search for the related scripts here in this forum
regards
April 16, 2010 at 8:49 am
Thanks for your reply..
In step 3.
I don't know transfer user before restored. I done just restored from SQL 2000 version database.
Apllication is does not connected, because existing login name not available which are in sql 2000 db, but all existing users appered in new sql 2008 db.
Please help me, how add login name in new sql 2008 db? which are already created in SQL 2000 db.
Thanks
April 16, 2010 at 8:55 am
in SQL Server 2000 you can get logins with their passwords using "sp_help_revlogin"
and execute in SQL2008.
Regards,
MShenel
April 16, 2010 at 9:37 am
I got the result with your valuable help.
The following two SPs download and exc the test server,
1. 'sp_hexadecimal'
2. sp_help_revlogin
output as blow, after that Exec in SQL 2008 box, it is working fine.
SET @pwd = CONVERT (varbinary(256), 0x01004F5FA44B912C01C37F5260851506D0C685A6FF4CD9398BE434FD116F41C4047BC8AF0F408A4958C662E910D0)
EXEC master..sp_addlogin 'SQL', @pwd, @sid = 0xA3C9BFD498DE9146857341F5E38FBE51, @encryptopt = 'skip_encryption'
Thanks
April 17, 2010 at 3:59 am
ananda.murugesan
but all existing users appered in new sql 2008 db.
these are the database users, they require linking to a server level login to allow server\database access
ananda.murugesan (4/16/2010)
I got the result with your valuable help.The following two SPs download and exc the test server,
1. 'sp_hexadecimal'
2. sp_help_revlogin
output as blow, after that Exec in SQL 2008 box, it is working fine.
SET @pwd = CONVERT (varbinary(256), 0x01004F5FA44B912C01C37F5260851506D0C685A6FF4CD9398BE434FD116F41C4047BC8AF0F408A4958C662E910D0)
EXEC master..sp_addlogin 'SQL', @pwd, @sid = 0xA3C9BFD498DE9146857341F5E38FBE51, @encryptopt = 'skip_encryption'
Thanks
although it probably does work, you have used the wrong script\method. What you have done is script the login for SQL Server 2000. There is a second method which creates a different procedure to export the login(s) for SQL Server 2005\2008. Read the MS kb article carefully and select the appropriate method.
see this link
scroll down the page and check method 2
Optionally you could create new users on the new server instead of importing and use the stored procedure
sp_change_users_login
to sync the new server logins with the database users
if you do export and import the users, import after you have restored the databases. If try the import before hand any set default database attempts will fail as the databases have not yet been restored 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 17, 2010 at 7:34 am
Perry Whittle, Thanks for your reply, I should take care further upgradation of login transfer.
Regards
Ananda
April 17, 2010 at 9:34 am
A couple of points on your process:
Item 1: SQL Server service accounts should not be setup as local administrators. Whether or not you use a domain user or local user is dependent upon whether or not SQL Server will need access to network resources.
Item 3: You do not have to create a database before restoring. Also, any database settings are restored with the database and need to be changed after the restore. You need to change the compatibility level and modify the page verify setting to CHECKSUM. You should also validate and change the db owner (I use sa for my databases).
As others have pointed out, you need to run the updated version of sp_help_revlogin before you restore the databases. When you do that, the users will automatically synch when the database is restored. Note: when doing this, you probably will be required to change the passwords for each user.
Have you validated the server settings. Have you set max memory to allow enough for the OS? Is this x64 or x86 hardware? If x64 - you definitely need to set the max memory or SQL Server could end up starving the OS. How much memory do you have available?
Did you set the lock pages in memory local user right?
If your service account running SQL Server is not an administrator (recommended), then you should also add that user to the Perform Volume Maintenance user right. This will enable instant file initialization and speed up data file growth. Don't rely on autogrowth though, you should manually grow the file to the right size and monitor.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 17, 2010 at 2:05 pm
Jeffrey Williams-493691 (4/17/2010)
As others have pointed out, you need to run the updated version of sp_help_revlogin before you restore the databases. When you do that, the users will automatically synch when the database is restored. Note: when doing this, you probably will be required to change the passwords for each user.
as i pointed out above apply the logins after you have restored the databases otherwise any attempts to set the default database in the create login script will fail as the database does not exist. The server logins and database users will still be in sync as the SIDs will match on the server login and the database user. Scripting the logins does not require you to change the password
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 17, 2010 at 4:29 pm
Perry Whittle (4/17/2010)
Jeffrey Williams-493691 (4/17/2010)
As others have pointed out, you need to run the updated version of sp_help_revlogin before you restore the databases. When you do that, the users will automatically synch when the database is restored. Note: when doing this, you probably will be required to change the passwords for each user.as i pointed out above apply the logins after you have restored the databases otherwise any attempts to set the default database in the create login script will fail as the database does not exist. The server logins and database users will still be in sync as the SIDs will match on the server login and the database user. Scripting the logins does not require you to change the password
Really? I was not aware that a login has a database. Once the login has been created on the destination system with the correct SID, the user in the database being restored will be synched correctly and will not be orphaned.
When moving logins from SQL Server 2000 to SQL Server 2005 - the passwords may be invalidated due to the policy settings in 2005/2008. If that happens, the logins password has to be reset because SQL Server has created a temporary randomly generated password.
Review sp_help_revlogin for further details.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 17, 2010 at 5:15 pm
Jeffrey Williams-493691 (4/17/2010)
Really? I was not aware that a login has a database. Once the login has been created on the destination system with the correct SID, the user in the database being restored will be synched correctly and will not be orphaned.
Yes the CREATE LOGIN WITH options have a DEFAULT_DATABASE =
Generally the revlogin procedure will export the logins default database too. OK if its set to master but if its a database that hasnt been created yet, just causes the script to error (but still creates the login). I just prefer to create the logins after the databases have been restored. An orphaned database user will automatically map into the database when the login with the correct SID is created.
Not had an issue thus far with the passwords but yes the policy feature can definitely have a bearing on this
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 17, 2010 at 5:24 pm
Perry Whittle (4/17/2010)
Jeffrey, yes its one of the WITH options DEFAULT_DATABASE =The revlogin procedure will export the logins default database. OK if its master but if its a database that hasnt been created yet just causes the script to error (but still creates the login). I just prefer to create the logins after the databases have been restored. Not had an issue thus far with the passwords but yes the policy feature can definitely have a bearing on this
Right - forgot about that one. And yes, you can do it after the fact - but I've had too many situations where someone thought it was a good idea to run a script provided by the application vendor to recreate the users. And then, nothing matches up and you have to go the route of trying to auto_fix every single one using sp_change_users_login.
Either way works - you just have to make sure the SID is the same. Just one more reason to prefer windows accounts instead.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 19, 2010 at 12:58 am
Mr.Perry Whittle said correct.
when I am using sp_help_revlogin sql 2000 then after exec this script in
SQL 2008 DB all Login were automatically maped with particulare database, which was already login were maped in SQL 2000 database.
I never reset password in SQL 2008, it is working old pw where sql 2000 database logins.
Jeffrey Williams said - If local windows user and having administrators permmission, then sql services, network services does not running,
I had installed local windows user but it is running all sql services. Please clarify me, I could not undersatand this points.
Only if login server with user Administrator, SQL 2008 connect only sa not in Administrator, because Installation done by local windows user.
In SQL 2008 db, Is it possible for connect thru query analyzer in sql 2000?
Thanks.
April 19, 2010 at 2:31 pm
ananda.murugesan (4/19/2010)
Mr.Perry Whittle said correct.when I am using sp_help_revlogin sql 2000 then after exec this script in
SQL 2008 DB all Login were automatically maped with particulare database, which was already login were maped in SQL 2000 database.
I never reset password in SQL 2008, it is working old pw where sql 2000 database logins.
Jeffrey Williams said - If local windows user and having administrators permmission, then sql services, network services does not running,
I had installed local windows user but it is running all sql services. Please clarify me, I could not undersatand this points.
Only if login server with user Administrator, SQL 2008 connect only sa not in Administrator, because Installation done by local windows user.
In SQL 2008 db, Is it possible for connect thru query analyzer in sql 2000?
Thanks.
What I stated was:
Item 1: SQL Server service accounts should not be setup as local administrators. Whether or not you use a domain user or local user is dependent upon whether or not SQL Server will need access to network resources.
It is not recommended that the account running SQL Server be added to the local administrators group. It is not required and grants that user greater rights to the system than are necessary.
If you use a local user account to run SQL Server, and SQL Server needs access to network resources - it will not work because a local user account can not be authenticated on the domain. So, if you need access to network resources you need to run SQL Server with a domain user that can be granted the necessary rights to access those resources. For example, if you want to backup your databases to a network share - it won't work.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply