Concerns on a complete SQL Server 2008 R2 migration

Question

Post reply

Concerns on a complete SQL Server 2008 R2 migration

TMitera

SSC Rookie

Points: 36
More actions
December 14, 2011 at 2:19 pm

#253476

Hello,
Next week I will be moving a SQL instance. The original hardware is actually getting completely blown out and reloaded, so I'll be reliant upon backups (Two different ones, two different methods) and cannot "look back" on the old config. Thus, I am trying to make sure that I don't overlook anything important, and wanted to see if anyone had any input.
The source environment is SQL Server 10.50.1600 (basically unpatched 2008 R2). It has eight user databases, ranging from tiny (a few MB) to about 600 GB. There are many user accounts set up, and their mapping to databases varies. The original directory tree is completely different from what the destination one is going to be.
My intended process:
- Back up all databases
- Export usernames/hashed passwords using sp_help_revlogin
- Reload
- Install SQL 2008 R2, and leave it unpatched
- Restore MSDB (to get our maintenance plans back)
- Restore the eight user databases
- Import the usernames and verify mapping
- Patch SQL up to current
Am I missing any other important bit of information that I will later go "If only I had documented X, or backed Y up"? Any gotchas to look out for? I've done my share of database restores but never a whole instance, and with no ability to look off of the old system I'm wanting to make sure I account for everything I can.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

JeremyE SSCoach Points: 16199 More actions · Answer 1

A few other things you may want to script out or keep track of if they apply:

•sp_configure

•Alerts

•Operators

•Jobs

•SQL Agent config

•Database Mail config

•Trace Flags

•Linked Servers

•Replication publications or subscriptions

TMitera SSC Rookie Points: 36 More actions · Answer 2

•sp_configure
•Alerts
•Operators
•Jobs
•SQL Agent config
•Database Mail config
•Trace Flags
•Linked Servers
•Replication publications or subscriptions

Of the above, it looks like (based off of my test environment) a restore of the MSDB database covers the Alerts, Operators, Jobs, and SQL Agent config. It doesn't look like Database Mail is set up, or at the very least on the source server when I open that it brings up a setup wizard, and there are no replication items.

I do see some linked servers so I need to document those, is there a way to script that out? Also, where are Trace Flags found?

JeremyE SSCoach Points: 16199 More actions · Answer 3

Right click on the linked server and select “Script Linked Server as”

For trace flags, you can use DBCC TRACESTATUS

Lowell SSC Guru Points: 323518 More actions · Answer 4

you'll have to script the jobs, i think; as i remember it,you'll hit a coule of issues:

system databases cannot be restored between versions or service packs. (ie 2005-->2005SP2, or 2005-->2008,2008-->2008R2, etc.

Also, the scripts reference the servername itself, so if you were to restore the scripts on a different server, but the same version, the jobs will fail, or try to execute stuff on the old server instead of itself.

Someone else may crosscheck to see if my recollecitons are wrong.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQlLogic SSCrazy Eights Points: 9686 More actions · Answer 5

I also like to know how you script out sql agent job , operators,Maintenance plans , all users mapping .If some body has script or the way to do it please post .

Thanks

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

tmitera (12/14/2011)
•sp_configure
•Alerts
•Operators
•Jobs
•SQL Agent config
•Database Mail config
•Trace Flags
•Linked Servers
•Replication publications or subscriptions
Of the above, it looks like (based off of my test environment) a restore of the MSDB database covers the Alerts, Operators, Jobs, and SQL Agent config. It doesn't look like Database Mail is set up, or at the very least on the source server when I open that it brings up a setup wizard, and there are no replication items.
I do see some linked servers so I need to document those, is there a way to script that out? Also, where are Trace Flags found?

although you mentioned logins you didn't mention scripting them out. It is possible to generate a script that contains all the logins and the encrypted passwords and the internal SQL SID. This makes it so you never need to know SQL Login passwords and because the SIDs are the same the restored SQL databases user informatio simply matches up without issue. I'm including a script to generate the commands. You will have to edit the output to exclude any extra logins in your environment..

I agree with Lowell, if you are changing the server name or the version in ANY way you can't restore msdb without issues.. Theoretically if you were keeping the same name you COULD install it as R2 RTM (the version you had) and then after the msdb restore you could then apply the service pack.. But I think that might be a bit more risk than is warranted..

You will also want to capture any special permissions that are granted to logins at the server level, like VIEW ANY DEFINITION as an example..

The fact you will have nothing to reference requires additional vigilance and you must have all the information in hand.. So concentrate on the system databases and system config.. The database backups will help with the user databases.. And if you jandle the logins as I mentioned above it makes that a little easier..

CEWII

TMitera SSC Rookie Points: 36 More actions · Answer 7

Well, from my test environment (which is pretty barebones to be fair) the restoration of the MSDB took care of the jobs. I can't actually test them since the bigger databases aren't present in this environment, but I am (possibly erroneously) figuring that since the databases will have the same logical names at worst I might have to do some re-association.

Thankfully, I will be going from 2008 R2 to 2008 R2, so that should mitigate any of the issues that Lowell mentioned.

JeremyE (12/14/2011)
For trace flags, you can use DBCC TRACESTATUS

When I run that, I get the following as output:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Something tells me I'm doing something wrong. Either that, or there are no trace flags to be found.

Thanks for the help so far all, I didn't anticipate this quick or extensive of replies.

JeremyE SSCoach Points: 16199 More actions · Answer 8

If nothing was returned by DBCC TRACESTATUS then you don't have any trace flags enabled.

TMitera SSC Rookie Points: 36 More actions · Answer 9

Well, I did actually script out the logins, or at least I believe I have. I referenced the following MS KB article: http://support.microsoft.com/kb/246133

Basically, you create a stored procedure that scripts the user information out, including hashed passwords. When the output was ran in my test environment it worked as expected, and even after that when I restored some test databases both the database level permissions (as expected) as well as the server level mappings synced up.

With the scripts mentioned in the MSDB, are we talking about plans, or something else entirely? The caution against restoring that database is alarming especially since the server name will be changing, but it would outwardly appear that the restoration of the database to my test environment (also a different server name) seemed to be a success. That being said, I can't test everything in this environment so if there is significant risk involved with restoring it and a way to otherwise document what configuration it contains, I'm all for it.