I did not know that restoring the master from the old SQL server to the new SQL server would have solved my issues. I'm glad to know this for the next time however. I'll make sure I restore the master along with the other DBs.! Appreciate the tips from everyone.
________________________________ From: [email protected] [[email protected]] on behalf of Ken Schaefer [[email protected]] Sent: Tuesday, October 22, 2013 7:03 PM To: [email protected] Subject: RE: [NTSysADM] RE: sql server upgrade All this info is stored in Master, so if you are in a position to backup and restore Master, then that would also solve the problem. Note also that the issue you describe is not limited to SQL Server accounts – you have the same issue with Windows principals – they need to be re-granted permission to login to the new SQL Server, as well as re-assigned any server-wide roles – as these are stored in Master, unlike individual database permissions which are stored in the individual DB. Cheers Ken From: [email protected] [mailto:[email protected]] On Behalf Of J- P Sent: Wednesday, 23 October 2013 7:09 AM To: [email protected] Subject: RE: [NTSysADM] RE: sql server upgrade I think this maybe an option http://social.msdn.microsoft.com/Forums/sqlserver/en-US/355e56e3-3d24-472f-903b-87618de3a1a6/importexport-userlogin-credentials-and-passwords?forum=sqldatabaseengine this is a PERFECT answer. It worked flawlessly. Just to be clear what I did: 1. I backed up the database (full data backup) from the source. 2. I ran the script as indicated by microsoft to produce a script (script A): http://support.microsoft.com/kb/918992/ 3. I ran the following script and saved each row of the results as one script (script B): select 'exec master.dbo.sp_addsrvrolemember @loginame=''' + member.name + ''', @rolename=''' + roles.name + '''' from sys.server_role_members join sys.server_principals roles on role_principal_id = roles.principal_id join sys.server_principals member on member_principal_id = member.principal_id 4. I restored the full data backup to the destination. 5. I ran script A against the destination. 6. I ran script B against the destination. What this did was built the logins, and kept the password, Server Roles and User Mapping confiugrations for all the logins. Brilliant and flawless. Thanks Jonathan! Jean-Paul Natola ________________________________ From: [email protected] To: [email protected] Subject: [NTSysADM] RE: sql server upgrade Date: Tue, 22 Oct 2013 17:14:51 +0000 This just a bit of additional information regarding the SQL server migration as we discussed below. I –finally- did the migration last night using option #2 as discussed. Things went mostly well. The one thing I didn’t anticipate was that detaching the DBs from the old server and attaching the DBs on the new server wasn’t quite enough for the various applications to run, even when the new SQL server had the same server name and IP address. Reason being, the old SQL server had several user accounts/logins created WITHIN SQL during the time the applications(s) were initially setup and DBs were created. Those accounts/logins within SQL had specific security contexts and levels for the different databases on the old server. Attaching the DBs to the new server did not automatically create those SQL accounts/logins on the new SQL server, so many of the applications did not function until we called the app-vendors themselves and explained our migration process. The vendors were however pretty good in regards to knowing exactly how to manually create those SQL accounts/logins within the new SQL server and get their respective applications running again fairly quick. So, a word of caution to anyone migrating over DBs from one SQL server to another, check the accounts that were created within the SQL mgmt. console and plan for getting them recreated on the new SQL server, else your apps won’t function. JR Sent: Monday, August 12, 2013 2:52 PM To: [email protected] Subject: [NTSysADM] RE: sql server upgrade thanks damien and glen. i think i'll go ahead with option #2. i already have the MS tech article on how to rename a sql server so that isn't a concern. appreciate the insight. jesse ________________________________ From: [email protected]<mailto:[email protected]> [[email protected]] on behalf of Glen Johnson [[email protected]] Sent: Monday, August 12, 2013 2:43 PM To: [email protected]<mailto:[email protected]> Subject: [NTSysADM] RE: sql server upgrade +100 on option 2. I personally wouldn’t trust option one, way too many strange things happen later and you never know if they are related to the upgrade or not. Plus, I never do OS upgrades. Never, never, never. From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Jesse Rink Sent: Monday, August 12, 2013 3:34 PM To: [email protected]<mailto:[email protected]> Subject: [NTSysADM] sql server upgrade We have an older Windows 2003 R2 x64 VM server which contains SQL2005 x64 on it. I'm planning on getting this box more current... My first option is to: 1. Take a image backup of the VM with our PHD Virtual software 2. Increase the c: drive hard disk in vSphere and then use a partition tool to expand the c: partition (its too small to perform the 2008 R2 upgrade on it) 3. Upgrade 2003 R2 x64 to Windows 2008 R2. 4. Upgrade SQL 2005 to SQL 2008 R2. Anyone had bad experiences going this route? The second option is to: 1. Create a brand new 2008 R2 server with SQL 2008 R2. 2. Detach the 10 DBs from SQL-OLD, copy them over to the SQL-NEW server and re-attach. 3. Decomission the old SQL-OLD server 4. Rename SQL-NEW to SQL-OLD and assign it the same IP address that the original SQL-OLD had. 5. Setup my Maintenance Plans from scratch. I think I'll be good for all my client applications that have specific ODBC settings configured pointing to either the IP address of the SQL box or the computer name/FQDN. Anyone had bad experiences going this route? Leaning more towards option TWO at this time... I tend to like clean installs as opposed to upgrades (generally). JR
