April 12, 2011 at 12:16 pm
Background: My workplace has an application that uses a SQL Server database. We are planning a version upgrade of the application down the road, but the new version requires SQL Server 2005. We are presently at SQL Server 2000, which makes upgrading SQL a prerequisite to the app's version upgrade. The vendor will support the app upgrade, but our maintenance agreement doesn't include SQL upgrades (they will give advice on app-specific questions, however).
I already verified some weeks ago in one test environment that our present version of the app works with SQL Server 2005. The vendor said it would, but we needed to make sure for ourselves. Last weekend I ran an in-place upgrade from SQL Server 2000 to 2005 (which is what the vendor recommends) in a second test environment, and it was successful. In the process, some questions emerged, and there are some things I'd like to make sure of before moving on to upgrading the production environment.
1) The installation program uses Windows authentication to perform the upgrade (or at least, the Database Services portion of it), which means that the SQL login that corresponds to the Windows login you're running the upgrade from has to have sufficient privileges to apply the upgrade. At first, it didn't work on my test environment, but then I went into Enterprise Manager (since I was still at 2000 at this point) and gave the Windows user's SQL login the same privileges as the sa login. After that it worked. The question is, what particular privileges are needed to get the upgrade to work? I didn't mind applying sa's privileges in a test environment, but doing that in production strikes me as being overkill and potentially troublesome, so I'd prefer to avoid that if possible.
2) When I finished applying the upgrade and service pack, the main database showed as "(In Recovery)" in SSMS. Since I'd already come in on Sunday to work on the test, at that point I called it a day and went home. When I came in Monday morning, the database was fine (and the app worked). So I was trying to figure out how long it took the database to finish recovering. In the Windows Event Viewer Application log, I found an Event ID 3408 from MSSQLSERVER with the description "Recovery is complete. This is an informational message only. No user action is required." Does the timestamp on the event correspond to when it finished recovering? It was only about 15 minutes after I left on Sunday, which is a waiting period I would certainly find acceptable for the production upgrade.
Follow-up questions to #2: Is there any way to forestall ending up with the database in recovery and/or speed up the process? Or is it just a matter of waiting, and it takes as long as it's going to take and we don't have any say about it?
3) I'm asking this question because one of my main concerns is preserving the users/logins/privileges through the upgrade process. If the users already show up under Security --> Logins in SSMS, will they be affected by changes to the database? If by any chance it becomes necessary to detach/reattach or restore the database from a pre-upgrade backup, will I lose the users?
Hope these questions aren't too boneheaded, but I'm pretty new to SQL Server 2005 so I'm proceeding VERY carefully. Thanks in advance for letting me pick the community's collective brain! 😎
April 12, 2011 at 12:26 pm
Geoffrey Wrigg (4/12/2011)
1) The question is, what particular privileges are needed to get the upgrade to work?
A system administrator or sa is required to perform the upgrade.
2) Does the timestamp on the event correspond to when it finished recovering? It was only about 15 minutes after I left on Sunday, which is a waiting period I would certainly find acceptable for the production upgrade.
Follow-up questions to #2: Is there any way to forestall ending up with the database in recovery and/or speed up the process? Or is it just a matter of waiting, and it takes as long as it's going to take and we don't have any say about it?
Yes timestamp correlates to completion time.
You have to let it finish.
3) I'm asking this question because one of my main concerns is preserving the users/logins/privileges through the upgrade process. If the users already show up under Security --> Logins in SSMS, will they be affected by changes to the database? If by any chance it becomes necessary to detach/reattach or restore the database from a pre-upgrade backup, will I lose the users?
The logins may be affected by it or orphaned (especially if restoring the database to a different server). You would simply need to check for and fix "orphaned" users.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 13, 2011 at 11:16 am
Much appreciated, Cirque, those answers really do help. They basically confirmed what I suspected already, but it's good to have some confirmation that I'm thinking on the proper wavelength.
Just a bit more detail on my question #3: If that kind of situation arose, I'd probably end up calling the app's tech support for assistance in getting the users sorted out. That pertains enough to the app itself that they would provide support for it.
April 18, 2011 at 9:12 am
We did the production server upgrade last night. The SQL portion was a bit bumpy, but thanks to our experience with the test upgrades (and Cirque's answers), it wasn't anything we weren't expecting. We did have one issue with the app itself, but we were able to fix it without much trouble.
Thanks again for the assist.
April 18, 2011 at 9:31 am
Geoffrey Wrigg (4/12/2011)
Follow-up questions to #2: Is there any way to forestall ending up with the database in recovery and/or speed up the process? Or is it just a matter of waiting, and it takes as long as it's going to take and we don't have any say about it?
Yes, there is. Run a Checkpoint before you detach the database. That will allow a clean shut down and minimal recovery time.
There's no way, once a DB is recovering, to speed up or stop the process though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply