Solved

Migration DB from SQL server 2005 RTM to SQL server 2008 R2 SP2 (probably with CU4)

Posted on 2013-01-26
58
993 Views
Last Modified: 2013-02-15
Dear all,

we are in the project of migrate SQL server 2005 to SQL server 2008 R2 standard editon and we will upgrade the SQL server 2008 R2 to SP2 and probably CU4.

we thinking about 2 ways to do the data migration:
1) SQL native backup and restore of all system and user database from SQL server 2005 to SQL server 2008 R2.
2) SQL mirror to mirror all DB from SQL server 2005 to SQL server2008 R2. During switch over, we just set mirror partner to off. then we change the SQL server 2008 R2 to be the master.

Questions:

1) Some SQL server 2005 we are using still RTM edition, can we do choice 1 and 2 ?
2) Some SQL server 2005 we are using still RTM edition ,any known issue when doing this for item 1 and 2?
3) When we backup the master DB from SQL server 2005 to SQL server 2008 R2, we still need to start SQL server 2008 R2 in single user mode ?
4) what is the full command to restore the master DB? after restore master DB, what else we have to do to make sure the SQL server migrated well ?

DBA100.
0
Comment
Question by:marrowyung
  • 30
  • 15
  • 11
  • +1
58 Comments
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 250 total points
ID: 38823070
this task much more complicated and requires DBA with experience and knowledge to do this , if you need to keep this company in the busness
-----------------------------------------------------
for start:
document all what you have on your sql2005 -- I mean All : DB; sql jobs; SSIS;DTS; Linked servers; logins users.. etc..
---
check what the sql server 2005 edition  you have

and check
Version and Edition Upgrades
http://msdn.microsoft.com/en-us/library/ms143393(v=sql.105).aspx

also read all topics from :
Upgrading to SQL Server 2008 R2
http://msdn.microsoft.com/en-us/library/bb677622(v=sql.105).aspx
---
it depends on your environment:
I would recommend:
1. SQL  backup and restore  user database from SQL server 2005 to SQL server 2008 R2.
--
you can not  restore system 2005 to 2008R

there are ways to copy logins\ etc---

--about your Qs:

1) Some SQL server 2005 we are using still RTM edition, can we do choice 1 and 2 ?
[EZ] you can - it not so important if you upgrade user DBs to sql2008r2
2) Some SQL server 2005 we are using still RTM edition ,any known issue when doing this for item 1 and 2?
[EZ] usually no issues - depends case by case  - it not so important if you upgrade user DBs to sql2008r2
3) When we backup the master DB from SQL server 2005 to SQL server 2008 R2, we still need to start SQL server 2008 R2 in single user mode ?
[EZ]  can not be done
4) what is the full command to restore the master DB? after restore master DB, what else we have to do to make sure the SQL server migrated well ?
[Ez] no need for upgrade
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38823632
EugeneZ,

"you can not  restore system 2005 to 2008R"

This already measn that backup restore is not going to work.  but why you say "Some SQL server 2005 we are using still RTM edition ,any known issue when doing this for item 1 and 2? "

how can we fix this if we are running SQL server 2008 R2 in a new machine, SQL server 2005 RTM and SQL server 2008 R2 standard will run on different machine.

"When we backup the master DB from SQL server 2005 to SQL server 2008 R2, we still need to start SQL server 2008 R2 in single user mode ?
[EZ]  can not be done"

so how can I transfer the master DB content to the new server?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38824133
"Upgrading to SQL Server 2008 R2
http://msdn.microsoft.com/en-us/library/bb677622(v=sql.105).aspx"

this one don't talk about upgrading from SQL server 2005 to SQL server 2008R2, it is mainly SQL server 2008 to SQL 2008 R2. am I right on this?

I checked from this "http://msdn.microsoft.com/en-us/library/ms143393(v=sql.105).aspx", that upgrading from SQL server 2005 SP2 to SQL server 2008 R2 is supported upgrade path, am I right?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38824161
if can't restore master DB to the SQL server 2008 R2, any script to transfer login + password to the new server?
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 250 total points
ID: 38824270
Yeah, cannot restore MASTER, so, need to script the user security stuff... You can restore user databases, but will need to tidy up security (the user login is a SID in the DB and that is likely to be different on the new one - so you must tidy up those SID's, not just the user login names).

There is a KB article : http://support.microsoft.com/kb/918992

So long as the newer version is a "equal or higher" edition you should be fine, you run into difficulty if going from 2005 Enterprise to say 2008 standard.

Would not work trying to mirror 2005 with 2008 as an alternative to migration. Do you already have a cluster / db mirroring in place ?

You can upgrade in place and is probably the best (but admittedly a bit scarey). I strongly suggest you read : http://technet.microsoft.com/en-us/magazine/gg454217.aspx and http://www.techrepublic.com/blog/10things/10-tips-for-upgrading-to-sql-server-2008-r2/2376

Basically...
1)  run the upgrade advisor
2)  script fixes etc (keep the scripts) and document everything
3)  do a test on a different machine to run through the processes (doesnt have to reflect 'live')
4)  double check all the associated / related / dependant processes / apps / procedures  and plan your course for migration for those other things
5)  plan your timing to minimise any impact on users and periodical processes (like end of month)
6)  if you do have a replication of your live site (e.g. a dev environment) then test that first
    - full backups and transaction log backups
    - full machine backup
    - might be a good time to test your recovery processes before you begin

Then once you have gone through the processes, and have documented everything, and have timings, then plan the exact upgrade for Live.

If you are really concerned, then you could install the 2008 version as a side by side instance and migrate that way. But you then have named instances and some processes (like connection strings and external scripts) might need to change - if for no other reason than to specify the named instance of the server.

At one company, we had a machine dedicated to QA and it had almost a replication of everything "live", so we used that a lot. One strategy used to be install as a new instance, do all the testing and finding out all the little problems with named instances et al, then do the upgrade in place on the QA machine and test all the live activities. If that passed, then it is normally an upgrade in place on live. Mind you, we had the luxury of time over weekends to perform the upgrades, and that time is something you need to consider.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38824458
RTM edition - Release to Manufacturing edition (usually mean for sale version without SP)

You need to check actual sql server edition : run in the SSMS of you sql server 2005 :
 Select @@Version
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38824460
additionally  to the posted link how to \trabsfer logins:

re-read my above post: and based on documented list:
script your linked servers (passwords used there you need type manually on the destination server)
All SSIS\DTS  pack: you can \copy msdb tables data or just import\export
just need to adjust each connection (unless you plan to use CNAME)

same for jobs, etc'

and read the upgrade linkes\Doc\Etc.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38825946
mark_wills,

"Yeah, cannot restore MASTER"

so the ONLY thing I can do is to use this http://support.microsoft.com/kb/918992 to script out username and password?

"So long as the newer version is a "equal or higher" edition you should be fine, you run into difficulty if going from 2005 Enterprise to say 2008 standard. "

What if we are migrating from SQL server 2005 RTM/SP2 enterprise to SQL server 2008 R2 standard?

Then it shouldn’t work and what should we do ?

What should we do if they are different machine?

"Would not work trying to mirror 2005 with 2008 as an alternative to migration. Do you already have a cluster / db mirroring in place ?"

So Mirror from SQL 2005 RTM/SP2 to SQL server 2008 R2 is not going to work ! Some SQL has cluster and some do not.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38826215
Correct, master needs to be scripted. Mind you, if using Windows Authentication, then you might need to check to see how much of a problem it might be.

Going from Enterprise to Standard is always a problem, might need to reconsider 2008 R8 enterprise otherwise you are going to need side-by-side upgrade (ie 2008 as a new instance) and export 2005 and import 2008. Some have said if you dont use any enterprise features then you can get a detach and attach happening, but, that has not been my experience. You can also use the copy database wizard : http://msdn.microsoft.com/en-au/library/ms188664(v=sql.105).aspx

For verification of upgrade compatability / possibilities, check your editions : http://msdn.microsoft.com/en-au/library/ms143393(v=sql.105).aspx

For cluster upgrade, then you must upgrade the passive nodes first, and then they no longer participate in any failover until the job is complete. Please read very carefully : http://msdn.microsoft.com/en-au/library/ms191295(v=sql.105).aspx

And then, on a lighter note, please see Aaron's experience in his upgrade : http://sqlblog.com/blogs/aaron_bertrand/archive/2010/01/05/my-experiences-upgrading-2005-2008.aspx
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38826234
What if we are migrating from SQL server 2005 RTM/SP2 enterprise to SQL server 2008 R2 standard?

you should be fine - if you do not do in place upgrade but just move (backup\restore; detach\attach) from 2005 -> to 2008 sql server
--

regarding logins-- you can do them manually if you wish - and or script..

--

Mirror - is not the best solution: however: it can be done if you need.

--
if you need to minimize downtime try to use log shipping or full backup-> restore- > and differential backup\restore
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38826299
EugeneZ,

"What if we are migrating from SQL server 2005 RTM/SP2 enterprise to SQL server 2008 R2 standard?

you should be fine - if you do not do in place upgrade but just move (backup\restore; detach\attach) from 2005 -> to 2008 sql server
"
from link from MS already said that we can't downgrade from enterprise to standard edition, please correct me if I am wrong.

we are discussing here that backup restore is not going to work, right?

"regarding logins-- you can do them manually if you wish - and or script.."

I don't want to type password manually and the script to fix this is only this http://support.microsoft.com/kb/918992  ?

"Mirror - is not the best solution: however: it can be done if you need."

Mark_wills presented that mirror is not going to work, or you have tried that before and it works ?

it is not the best and what is the best solution of it?

"if you need to minimize downtime try to use log shipping or full backup-> restore- > and differential backup\restore "

I think if the solution of mirror,  which one is the best as I can simple type alter database XXXx set partner off, then can I make the new DR server the primary one. simple enought and fast using prepared script ?
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38826312
you can restore user db from sql 2005 to sql2008

if you do upgrade in place - you can not for your case : ent to st
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38826317
mirror  needs backup restore as well..

as per above post - the most popular backup restore to the new box with combinations: or just full last backup or with differential

but it is case -by case and you need to decide what is the best for your company

it means: mirror can be for you the best if you do not have downtime window ...
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38826333
EugeneZ,

"you can restore user db from sql 2005 to sql2008 "

Excellent ! then everything is simple.

but we can't just do the backup and restore for MASTER DB, must use http://support.microsoft.com/kb/918992 , right?

"if you do upgrade in place - you can not for your case : ent to st "

yeah, I can't do inplace upgrade as the currenty verion we are using is the SQL server 2005 enterprise edition but we are going to use SQL server 2008 R2 standard.

"mirror  needs backup restore as well.."

yes, but much easlier from my point of view as the change keep sync. and not need to do one more backup , one more file transfer and one more file restore, right?

just run script to remove the mirror and change the DR to primary, right?

or we just do full backup and then trans. log backup ?

Then what you also means is SQL server 2005 (Whatever it is RTM or SP2) can mirror all user databases to SQL server 2008 R2, whatever it is in SP2 or SP2 + CU4 ?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38826482
Please see my earlier post...

You cannot backup and restore if there have been any use of enterprise features.

If you had enterprise, presumably for your cluster, then you might be surprised that you have used some of the features. For example, if you have table or index partitioning, then you definitely cannot restore. If you used enterprise because of high performance mirroring, then it isnt available in standard (so even if you can restore, you might lose out elsewhere).

For your cluster, you must perform specific steps. For a start, you will be doing a side-by-side install, again, please read that link carefully.

Also note that for failover clustering, 2008R2 standard only supports 2 nodes, enterprise supports up to 16, thats the same in 2005. So, guessing that there might be multi-nodes ?

Also, you must be careful with default TCP port numbers / IP adress conflicts with side-by-side installs in a cluster.

Mirroring is a slightly different beast from clustering and often used (incorrectly) interchangably clustering does use mirrors, but mirrors dont have to be clustered. For example both std and enterprise support mirroring (in both 2005 and 2008) but only enterprise (again both 2005 and 2008) support asynch mirroring. Bit like the clustering in terms of std v enterprise feature support. And definitely, mirrored partners MUST BE the same edition. Similarly, the high speed support that you may have used on 2005 enterprise is not available on 2008R2 standard.

If you used enterprise, then chances are you needed enterprise features and might not work on your new std version. You need to check this. You must check this and assess the impacts.

Honestly... Dont let anyone tell you "this will work" unless they KNOW categorically what your environment is, what features have been deployed, and all the associated "gotcha's".

Read the various links carefully. Run the upgrade advisors regardless of side-by-side or upgrade in place.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38826511
Oh, and again, another little problem with some of the other comments above...

You would NOT backup and restore your mirror, you would backup your primary DB with norecovery and restore that as if you were establishing your mirror again. Which is what you are doing with a new instance. If your primary DB had additional backup logs, then they too have to be applied to your mirror.

When talking system databases, it is not just logins and master, there other (user) objects in master, msdb, and model (ie the collection of system) databases.

Again, more reading for you... http://msdn.microsoft.com/en-us/library/ms189053(v=sql.105).aspx

My advice to you would be to carefully consider what and how you have used your existing enterprise edition, and consider the Enterprise version of 2008 R2

And, if going to 2008 R2, then why not 2012 ?
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38828904
yes, mark_wills is correct: in case you use enterprise  features - consider sql 2008 r2  enterprise --- if not -- standard will be good enough
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38829616
mark_wills,

I understand .

"If you used enterprise because of high performance mirroring, then it isnt available in standard (so even if you can restore, you might lose out elsewhere).
"
As SQL server 2008 R2 standard edition support to run on top of the Windows 2008 R2 enterprise edition as a 2 nodes cluster top of this Windows 2008 R2 enterprise cluster, so it will be ok. we will running as 2 x 2 nodes clusters group.


The application vendor including Microsoft confirm it works and vendor say standard edition it is ok, then I think it will be ok.

so if backup and restore is not ok, then what else we can try ? will have a test machine soon as the new hardware just arrive.

""For your cluster, you must perform specific steps. For a start, you will be doing a side-by-side install, again, please read that link carefully."

I read both link and I can't notice they mention anyting special about cluster, which one you referring to?

"Also, you must be careful with default TCP port numbers / IP adress conflicts with side-by-side installs in a cluster. "

excellent answer, the IP address will be handled by the network team.

"
Mirroring is a slightly different beast from clustering and often used (incorrectly) interchangably clustering does use mirrors, but mirrors dont have to be clustered."

no no. what i mean is I use mirror instead of backup as we can simple built up a script to stop the mirroing and birng the new server as the new primary. thne executing the script is much faster than backup and restore differential backup, agree ?

except that the mirror sync the data but lag behind. this will happen if too much DB is replication to the same DR server.

"And definitely, mirrored partners MUST BE the same edition. "

so if we setup mirror and replicate form SQL server 2005 RTM/SP2 enterprise edition to SQL server 2008 R2 standard edition, even as a 2 nodes on top of Windows 2008 R2 enterprise edition, it still DOESN'T work ?

"If you used enterprise, then chances are you needed enterprise features and might not work on your new std version. You need to check this. You must check this and assess the impacts."

application Vendor confirmed that standard edition is ok. So this mean ..... ?

"Honestly... Dont let anyone tell you "this will work" unless they KNOW categorically what your environment is, what features have been deployed, and all the associated "gotcha's"."

yes, but this is not some kind of thing that I tried before, I have to ask someone AND THEN verify it myself.

the point is, if backup and restore doesn't work, then rise up my hand now to the management as they/the PMs already assuming that it will works.  If we don't find that out, trial run ourselves, then hard to tell total down time.

one funny tthing is the litespeed backup software, Quest's support, confirmed that the native MS SQL backup can't backup and restore DB From SQL server 2005 enterprise edition to SQL server 2008 R2 and their software can't do it either. They said this is an known issue from Microsoft and this is because the DB architecture was changed.

now we found out we can, then I have to ask them why they say this !

upgrade advisors  has been running and there are something I don't understand, I think I will ask the vendor if this can be ignore.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38829636
one funny tthing is the litespeed backup software, Quest's support, confirmed that the native MS SQL backup can't backup and restore DB From SQL server 2005 enterprise edition to SQL server 2008 R2 and their software can't do it either.
This is your opportunity to ask them for the MS documentation to back that up.  I will be very surprised if they respond.  In other words it is BS.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38829718
"This is your opportunity to ask them for the MS documentation to back that up.  I will be very surprised if they respond.  In other words it is BS. "

This is what I am going to do.

But one thing, can the system DB backup and restore in this way ? or the way to do with system DB except tempdB and user database is different ?

only user database can be backup and restore to the new SQL 2008 R2 server but not system DB ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38829731
mark_wills,

you are just excellent,

"You would NOT backup and restore your mirror, you would backup your primary DB with norecovery and restore that as if you were establishing your mirror again. Which is what you are doing with a new instance. If your primary DB had additional backup logs, then they too have to be applied to your mirror."

yes, I tried that many time before and that's why I suggest this as, duing the switch over time, it probably faster and the script might finish in 1- 2 sec. depends on how many DB we have.,

we will do a full bakcup and then transaction log backup, some articles said if the content do not changed, we can do just one full backup and let in non operation mode, but I found out that this is not very true. we have to do a transaction log backup and restore it to the new server anyway.

"And, if going to 2008 R2, then why not 2012 ?"

The applicatoin we are using said something stupid like "they think SQL server 2012 + Windos 2012 should be ok but they didn't try it out yet. However, SQL server 2008 R2 + Windows 2008 R2 cluster has been CERTIFIED", then this certified means a lot to us.

this vendor can say different thing from time to time and we prefer to stay with the word "Certified"

"My advice to you would be to carefully consider what and how you have used your existing enterprise edition, and consider the Enterprise version of 2008 R2 "

I will ask them about this and probalby export the upgarde advice report for them to read and see if they say "you can ignore it".
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38829737
Understand that you are gathering knowledge here, my point was that some of the things being said are very generic and quite sweeping statements, and you have to be careful.

If you cannot backup/restore then the option is to use the copy database wizard.

Pairs/Partners in a mirror, must be the same edition - full stop.

Read the prerequisites : http://msdn.microsoft.com/en-us/library/ms366349(v=sql.105).aspx

If using replication with publishers and subscribers, then there is more flexibility.

It is far quicker to backup and restore than allow machines to resynch over the network. So, would suggest going through the steps to re-establish the mirror on the new named instances. This was the link I was referring to : http://msdn.microsoft.com/en-us/library/ms189053(v=sql.105).aspx

It is far safer for Quest to say it is not supported, because if there is a flag set whithin the database (even if was "just a test") then it will not restore. But as acperkins said above, would be interesting to ask...

Very pleased to hear that new machine(s) will be available for testing first. That is you biggest asset - especially if you have never done it before.

You mentioned it is Win Server 2008 R2 - might want to check for some "gotcha's" if planning to set up 2005 on that machine as well : http://support.microsoft.com/kb/936302
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38829752
Sorry, was typing up my response to your previous (longer) post.

Absolutely hold them to the "certified" clause... That is worth its weight in gold in they have gone through the certification programs.

Yes, show them the report and ask them to comment - very good idea...

System databases Temdb gets created, Master is scripted, Model (only if you are using models), MSDB is the more interesting one... see : http://msdn.microsoft.com/en-au/library/ms188274(v=sql.90).aspx as for migration, I would be inclined to try the restore as a new db per the user databases. You will have to go through every schedule and user object in that DB and put in real MSDB, and my own strategy would be recreate the jobs from BIDS etc... and use it to review whats there.

>> you are just excellent,

Well, thank you kind sir. Not sure everyone will agree with that sentiment :)

I tend to be a bit pedantic because this type of activity can have consequences, and if we dont give you good suggestions, then we are not really helping you.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38829769
mark_wills,

"Well, thank you kind sir. Not sure everyone will agree with that sentiment"

They will, you are the one stand in the TED and you have your skill here to proof that.


"Understand that you are gathering knowledge here, my point was that some of the things being said are very generic and quite sweeping statements, and you have to be careful."

I think the time still your sleeping time and you still in your bed, you work very hard. you usually reply my ticket in our afternoon time, we are in lunch time now. haha.

"Understand that you are gathering knowledge here, my point was that some of the things being said are very generic and quite sweeping statements, and you have to be careful."

yes, you are right but I just confused by the statement before that backup and restore is not going to work. so I must amazing.

usually what I did before is :
1) backup and restore user and system DB to the new server, MS SQL use this method for a long long time and everytimes it just works. can't see why it doens't work this time.
2) only the master DB need to restore in single user mode. but for the side-by-side upgrade, I can still do in this way or the master DB is not going to work anyway? and I have to use the script you show me before to script out username and pasword ?
3) msdb and model DB should be ok to just backup and restore, I should be right ?

sorry this the first time I upgarde the SQL server 2005 RTM to the SQL server 2008 R2, a lot of thing needs to be clarify before going futher.

you know, what I will do today is read throught everyting you give me, and if lucky enought, setup the SQL server 2008 R2 +SP2 + CU4 + litespeed backup software, then try play around with the backup and restore.

Then we know how far we can go.. it should be very simple !

Without the Quest's reply I will not ask so much. but I think they means that we just can't restore system DB but user database can! I just reply them and see what they said.

I also download the upgrade technical guide from MS and they talk about backup and Restore DB.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38829788
so the mirror is not going to work anyway as SQL server 2005 RTM/SP2 can't mirror with SQL server 2008 R2, right? I just want to ask what is the different in the T-SQL script on creating mirror, and it seems that I just stop asking this.

in SQL server 2005, what we have done and test many time is:

1)create mirror end point on both primary and DR server by:

CREATE ENDPOINT [Mirroring]
      AUTHORIZATION [<domain>\username]
      STATE=STARTED
      AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
      FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)

2) do full DB backup and log backup, restore to the DR server.

3) create mirroring partner ship on primary server side:

ALter database <database name> set partner = 'TCP://DR server name:5022'

4) create mirroring partner ship on DR server side:

ALter database <database name> set partner = 'TCP://primary server name:5022'

it is just simple and straight forward.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38829789
The backup and restore may work. The difference this time is going from Enterprise to Standard. Thats the new / challenging part.

System databases...

Tempdb gets created, Master is scripted (dont even attempt a recovery), Model (only if you are using models).

MSDB is the more interesting one... see : http://msdn.microsoft.com/en-au/library/ms188274(v=sql.90).aspx

As for migrating MSDB, I would be inclined to try the restore as per the user databases in your test environment. You will have to go through every schedule and user object in that DB.

You could restore as a different name, then look at copying across to the real MSDB.

My own strategy has always been to recreate the jobs from BIDS etc... and use it to review whats there. If you have to go through every single item in MSDB, then you may as well create.

Mind you, not much support for migrating / backup restore for MSDB : http://www.sqlservercentral.com/Forums/Topic1196430-1550-1.aspx and then : http://support.microsoft.com/kb/264474 says you cannot do it.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38830049
mark_wills,

ok, so right now:

1) TempDB do not need to care about that as it will create automatically itself.
2) Master DB migrate by doing http://support.microsoft.com/kb/918992, am I right?
3) Model backup as per user database.
4) I can't see what is so special about the msdb, I htink we should just backup from SQL server 2005 and then restore it to SQL server 2008 R2 ?
5) restore all user database.

then the new SQL server should be restart and see if everything is ok. Then we are done ?

" You could restore as a different name, then look at copying across to the real MSDB."

on the new machine, why should i create a different name? or we then compare the content of it after restore and the old one in the existing server? the new server's msdb have very little thing.

"My own strategy has always been to recreate the jobs from BIDS etc... and use it to review whats there. If you have to go through every single item in MSDB, then you may as well create. "

Yes, worth to take a look, and I think I will let the vendor recreate the job if it is missing or NOT THEIR NEW VERSION. I will just double check the name of the item. SP, table for example.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38830194
Mark_wills,

It seems that, from this link http://www.sqlservercentral.com/Forums/Topic1196430-1550-1.aspx, for MSDB we only can do script out the job object.

but do you know if the script generate in this way, can SQL server 2008 R2 surprisingly can't understand the script at all as it is generate from previous version of SQL server?

should I right click on the msdb database and then select script generate Wizard and start scripting all ? in the script generated wizard, all option should be 'true'?
MSDB-script.jpg
MSDB-script-option.jpg
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38830510
one thing, it seems right now if we don't do full backup + the differential backup, log shipping is another choice, but can log shipping connect different version of SQL server?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 38830511
MSDB is kinda special - it has all the jobs and SQL Server Agent, Mail, Report Delivery and lots of stuff...

Yes, it can be backed up and restored as a different database, but really dont think it will work as msdb - it is one of the system databases, and it does check quite a few things. My experience has always been failure if trying to restore MSDB - it errors knowing it is a system database.

The reason to back up as a different database is an attempt to be able to copy data and avoid having to script, but copy and paste rows will only work if the structures havent changed.

Then there might be problems with those row GUID's that get created and retained in a few different tables. Seem to recall seeing a "bug" alert a few years back...

To be perfectly honest, I have never tried to script or copy the MSDB. Always taken the hard yards and re-created. Not a big problem for most of the jobs.

I guess the other concern is if your jobs are DTS jobs or you took the time to upgrade them to SSIS jobs - you might need to consider that.

There is support for DTS in 2008 and pretty much bypasses the upgrade process. You could run the upgrade process JUST for SSIS (it is seperate, so no problems with std v ent).

 would be worthwhile reading : http://msdn.microsoft.com/en-us/library/cc879336(v=sql.105).aspx for the SSIS part and a must read is : http://msdn.microsoft.com/en-us/library/cc280546(v=sql.105).aspx

But we are kind of guessing as to what you have in your MSDB. It really can be easier to simply recreate the maintenance plans and scheduled jobs and dbmail.

If you have SSRS and SSIS then you will need to upgrade them as well. Some SSRS functions will store information in MSDB (report delivery).

best to list out everything you have added to MSDB.

For user tables and other user objects, sure script away. For system related and wizard created objects, then you really need a LOT of them before throwing out the manaul method of recreating.

Not a lot of good news for system databases is it... Thats why upgrade in place is very attractive.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38830610
Wouldnt do log shipping. But yes, you can go from 2005 to 2008... (but not back again)

Until you actually restore the database it isnt upgraded and any Stand-By mode attempt to apply log shipping at that stage will error. I think it works if you apply the log while still in recovery mode, or certainly, have gone through the upgrade process.

More straight forward if you can use the backup and the transaction log backups. The steps you set out before for mirror seem fine...

Just one more thing about those pesky System databases...

When you create your new instance, the system databases will also be created and all that must happen is recreate the specific user (or DBA) generated objects.

So from Master, users added manually, or, groups added manually (and by manual I mean by script or interface as a deliberate and specific action over and above the built-in groups).
If only a couple, then you can simply add them in again, but then need to fix SID's.

From Models, unless you are using models (templates), then dont worry any more.

From MSDB, well we have spoken about that already.

From TEMPDB then that gets created every time you start SQL Server Service.

From RESOURCES basically dont touch and dont worry.

There is also a special case for replication, the Distributor database, but not sure if you use any replication. Hopefully not, but worse case is another manual job depending on just how many publications exist.

The upgrade advisor really is your best friend...
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38833545
going back to your question:

<Migration DB from SQL server 2005 RTM to SQL server 2008 R2 SP2 (probably with CU4)>

SQL server 2005 RTM  can not be used for mirror (bugs were fixed with later by sql2005 SPs)
--

As I understood - you do not have ":Zero down " environment

use backup restore of just user DB: exclude ALL system sql2005 DBs: MASTER: MODEL: MSDB: TEMPDB from backup restores
--

Copy logins; linked server; SSIS; Sqlagent operators;jobs (or script jobs or use copy data from msdb system tables (I recommend for you to use script options)
--
Setup DBmail on the new server2008 -- as you have on sql2005
--
recreate DB maint plans on the new server if you have in sql2005
etc

--use above posted links
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38834683
mark_wills,

when I script a job from SQL server 2005 and just run in SQL server 2008 R2, what I get is an error:

Msg 515, Level 16, State 2, Procedure sp_add_job, Line 137
Cannot insert the value NULL into column 'owner_sid', table 'msdb.dbo.sysjobs'; column does not allow nulls. INSERT fails.
The statement has been terminated.

I didn't use the script to script login to the master DB yet, is it why ?

"So from Master, users added manually, or, groups added manually (and by manual I mean by script or interface as a deliberate and specific action over and above the built-in groups).
If only a couple, then you can simply add them in again, but then need to fix SID's."

Here, I don't know what do you mean fix SID's, please show me how with example.

"From Models, unless you are using models (templates), then dont worry any more."

here, basically don't know if we are using models, never user it from my point of view, so we can ask the vendor and if they say no then we don't touch/migrate it?

EugeneZ,

"SQL server 2005 RTM  can not be used for mirror (bugs were fixed with later by sql2005 SPs)"

Thanks for telling me this as never setup log shipping by that version.

BTW, what is zero down environment?

I just get the server install and the restore of user database is working good. REALLY can't restore msdb and model.


by http://msdn.microsoft.com/en-us/library/ms188664.aspx, I can use copy db wizard to copy msdb and model and master prehaps ? doubt about that.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38834686
oh, god, the link said "The model, msdb, and master databases cannot be copied or moved by the Copy Database Wizard.
"

...................
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38834703
Mark_wills,

I don't have the RESOURCES database and please advice where is it? never see it before.

we don't use replicatoin.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38835847
Dont worry about resources database. *laughing* thought I mentioned not to worry - I only mentioned it because it is one of the system databses and lives side by side (physically) with master.

But if you want to know : http://msdn.microsoft.com/en-us/library/ms190940(v=sql.90).aspx

System databases are very special and cannot simply be copied / detached+attach / restored.

There is not too much joy out there scripting MSDB. Have you made a list of how many jobs plans etc ? Have you properly assessed the manual effort ?

When you add a user to SQL, it exists as a user name, but it also has a special identifier that databases use internally known as a SID. So, create a user = generate a new SID.

It is the SID that connects back to the login and is the Security ID for the user. So, while it *might* look OK because you added the user name, a restored DB from a different server using the same user name is most likely going to have a different SID.

They become what is known as "orhpaned users" see ; http://msdn.microsoft.com/en-us/library/ms175475(v=sql.105).aspx

The KB article with the script to migrate users should also mention orphaned users, and is just another step with a side-by-side install.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38836383
>>I only mentioned it because it is one of the system databses and lives side by side (physically) with master. <<
As an aside, in SQL Server 2008 that is no longer a requirement.  In fact to quote MSDN with SQL Server 2008:
the location of the Resource database is <drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\MSSQL\Binn\. The database cannot be moved.
In other words starting with SQL Server 2008 the master can be moved to a faster/bigger drive, but the resources database should remain in the same place.

You can see how the following paragraph in the link you posted has been removed from SQL Server 2008:
The Resource database depends on the location of the master database. If you move the master database, you must also move the Resource database to the same location. For more information, see Moving System Databases. Also, do not put the Resource database in either compressed or encrypted NTFS file system folders. Doing so will hinder performance and prevent upgrades.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38837668
<BTW, what is zero down environment? >

it is, for example: near 0hours downtime per SLA
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38838152
@acperkins, You are of course quite correct, and I humbly stand corrected :)

However (*defense mechanism kicking in), I was referring to the 2005 documentation (per the link) to indicate that the resources DB was not something new for 2008.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38838743
mark_wills,

"There is not too much joy out there scripting MSDB. Have you made a list of how many jobs plans etc ? Have you properly assessed the manual effort ?"

As we are running the side-by-side case, then we just read the SQL jobs, script that, and apply it to the new server, that's it.

"When you add a user to SQL, it exists as a user name, but it also has a special identifier that databases use internally known as a SID. So, create a user = generate a new SID.

It is the SID that connects back to the login and is the Security ID for the user. So, while it *might* look OK because you added the user name, a restored DB from a different server using the same user name is most likely going to have a different SID. "

so in this case, we restore back the master server (we cna't do this), the user account added back but SID is different and we can't fix it, right?

So we do this http://support.microsoft.com/kb/918992 and it will just create the new SID for the existing user in the server DB server and it will be fine?

"The KB article with the script to migrate users should also mention orphaned users, and is just another step with a side-by-side install. "

the KB you are talking about is http://support.microsoft.com/kb/918992 ?

"To be perfectly honest, I have never tried to script or copy the MSDB. Always taken the hard yards and re-created. Not a big problem for most of the jobs."

from your previous response, it seems that I can only script out the SQL job or recreate that manually one by one. And no other choice.

"Not a lot of good news for system databases is it... Thats why upgrade in place is very attractive. "

so inplace upgrade will upgrade them all without any problem you mean ?

acperkins,

"In other words starting with SQL Server 2008 the master can be moved to a faster/bigger drive, but the resources database should remain in the same place."

Excellent !

EugeneZ,
"All SSIS\DTS  pack: you can \copy msdb tables data or just import\export ""

"
Copy logins; linked server; SSIS; Sqlagent operators;jobs (or script jobs or use copy data from msdb system tables (I recommend for you to use script options)"

I knew for the MSDB, you can only script each job out, but what you mean copy data? table by table ?


I open 2 more ticket to givey you all more score as you all help me a lot, please help answering:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_28015162.html

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28014964.html
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38838866
Yes, the KB Article does include in the script the of the original user SID.

Yes, an upgrade in place can resolve of lot of these migrating type issues - however - you would need the Enterprise version for 2008 R2.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38842425
mark_wills,

Yes, understands what you mean.

but I can tell you that, the link doesn't work, it will gives error message.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38845964
one thing, we have some statement from the vendor that he just know we tried to use SQL server 2005 SP4 on Windows 2008 R2 cluster instead of SQL server 2008 R2 standard on top of Windows 2008 R2 enterprise as their application still not passed the baseline test ! and we have to relocate datacenter before the test is passed. so we have no choice to use SQL server 2005 SP4 on Windows 2008 R2 cluster.

but what he said is "If you Install SQL 2005 with cluster then you will face issues while upgrading it to SQl 2008"

What kind of issue it should be, it must be a in-place upgrade and what will be the problem ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38845965
one thing, we have some statement from the vendor that he just know we tried to use SQL server 2005 SP4 on Windows 2008 R2 cluster instead of SQL server 2008 R2 standard on top of Windows 2008 R2 enterprise as their application still not passed the baseline test ! and we have to relocate datacenter before the test is passed. so we have no choice to use SQL server 2005 SP4 on Windows 2008 R2 cluster.

but what he said is "If you Install SQL 2005 with cluster then you will face issues while upgrading it to SQl 2008"

What kind of issue it should be, it must be a in-place upgrade and what will be the problem ?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38846053
Well, upgrade in place will mean enterprise to enterprise.

If you currently have 2005 enterprise, then yes, you should apply SP4 for Windows 2008 R2 cluster.

I am not sure what issues your vendor is talking about. Worse case is the SQL Server cluster is brought down and then re-established.

Perhaps the vendor is referring to the validation test : http://support.microsoft.com/kb/943984

You will need to clarify the vendor's comment.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38846676
I would like to avoid your confusion by adding extra advanced step "how-to copy  sql jobs, etc)
You need to use the standard ways:
via SSMS GUI:
SSMS,_> SQL Server Agent -> Jobs
right click jobs -> click Tasks -> Generate Scripts

--I think we covered all in this Question and must move to another :

marrowyung: please reread all what was posted  here (again there is much more solutions posted here than you even need)
--
Good project plan/documentation/inventory  is your tool

It is very advanced DBA task that you try to accomplish and without clear reqs; plan, goal, all details we can not guide you toward  your target .

<for the extra "details" such as "their application still not passed the baseline test"
we do not know what is included in their test and what your vendor needs .

if you still
need EE help -- collect all new details question\ sort-order - and post as a new question. Thanks
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 38849877
EugeneZ,

"I would like to avoid your confusion by adding extra advanced step "how-to copy  sql jobs, etc)
You need to use the standard ways:
via SSMS GUI:
SSMS,_> SQL Server Agent -> Jobs
right click jobs -> click Tasks -> Generate Scripts "

I knew this. but I am not sure if right click on MSDB and select tasks ->generate scripts, and finally if the whole script can then apply to SQL server 2008 R2.

This is what I asked before. So just one script here instead of too much individual script for each SQL job.s


"<for the extra "details" such as "their application still not passed the baseline test"
we do not know what is included in their test and what your vendor needs ."

what I mean is the vendor's application has some kind of testing problem on THEIR new application, this might bring us back to use SQL server 2007 with SP4 on Windows 2008 R2 cluster platform instead of SQL server 2008 R2 standard platform.

decision keep changing just because of the vendor as they send an email to us and what i feel is they prefer SQL server 2008 and mention this :

"If you Install SQL 2005 with cluster then you will face issues while upgrading it to SQl 2008"

and this is nothing more for this ticket, this make us wondering which DB to use.

As you can see I keep closing existing ticket and create new one, as I think you guys help me a lot and should get more score from me.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38850176
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38850222
<I knew this. but I am not sure if right click on MSDB and select tasks ->generate scripts, and finally if the whole script can then apply to SQL server 2008 R2.>


I though it was clear: you do not need to script msdb database objects tables\views\etc..

but elements of sql server stored in msdb by using for example:

jobs:

SSMS,_> SQL Server Agent -> Jobs
right click jobs -> click Tasks -> Generate Scripts "

from sql agent you can script all jobs in 1  script

How to script all the SQL Jobs from SQL Server Management studio?

Step 1: Go to SQL Server Mgmt Studio.
Step 2: Expand SQL Server Agent >> Jobs
Step 3: Hit F7 (or) goto menu View >> Summary
Step 4: Now ALL jobs would be listed out there.
Step 5: Make use of Control key and choose all the JOBs you want to script. (OR) If you want to script everything press Ctrl + A
Step 6: Right click >> "Script Job as" >> "Create To" >> File

from http://vadivel.blogspot.com/2011/09/script-all-sql-jobs-from-sql-server.html
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38850384
EugeneZ,

Excellent guide.

finally it will be the master DB which is remaining the troublestep for us.

when I follow this http://support.microsoft.com/kb/918992 to carry old login and password from SQL server 2005 SP2 or SP4(I upgraded), and apply to SQL server 2008 R2,
what I get is a lot of errors:







      Msg 15025, Level 16, State 2, Line 6
The server principal 'BUILTIN\Administrators' already exists.
Msg 15025, Level 16, State 2, Line 9
The server principal 'NT AUTHORITY\SYSTEM' already exists.
Msg 15401, Level 16, State 1, Line 12
Windows NT user or group '<Server>\SQLServer2005MSSQLUser$SBATCH01$MSSQLSERVER' not found. Check the name again.
Msg 15401, Level 16, State 1, Line 15
Windows NT user or group '<Server>\SQLServer2005SQLAgentUser$SBATCH01$MSSQLSERVER' not found. Check the name again.
Msg 15401, Level 16, State 1, Line 18
Windows NT user or group '<Server>\SQLServer2005MSFTEUser$SBATCH01$MSSQLSERVER' not found. Check the name again.
Msg 15401, Level 16, State 1, Line 21
Windows NT user or group 'XXX\test' not found. Check the name again.
Msg 15433, Level 16, State 1, Line 24
Supplied parameter sid is in use.
Msg 15433, Level 16, State 1, Line 27
Supplied parameter sid is in use.
Msg 15433, Level 16, State 1, Line 30
Supplied parameter sid is in use.
Msg 15401, Level 16, State 1, Line 33
Windows NT user or group 'XXX\test1' not found. Check the name again.
Msg 15401, Level 16, State 1, Line 36
Windows NT user or group 'XXX\test2' not found. Check the name again.
Msg 15401, Level 16, State 1, Line 39
Windows NT user or group 'XXX\test3' not found. Check the name again.
Msg 15401, Level 16, State 1, Line 42
Windows NT user or group 'XXX\test4' not found. Check the name again.
Msg 15433, Level 16, State 1, Line 45
Supplied parameter sid is in use.

so that link doesn;t work. it can't even do the same thing from SQL server 2005 with SP4 to another machine with SQL server 2005 SP4.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38850396
I just try you method and what I found out is when I execute the script, it will create a lot of errors:

Msg 515, Level 16, State 2, Procedure sp_add_job, Line 137
Cannot insert the value NULL into column 'owner_sid', table 'msdb.dbo.sysjobs'; column does not allow nulls. INSERT fails.
The statement has been terminated.

this will show up for all job I try to move.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38850877
Well, it would appear that some of those login errors are legitimate - you havent copied across those users to windows authentication, so, it is reporting "what is"

It also looks like "special case" users were setup - maybe as the Service owners, and might also have to do with your above error (noticed the special case '<Server>\SQLServer2005SQLAgentUser$SBATCH01$MSSQLSERVER )

And that what the scripts should be doing, showing you some potential errors where you might have missed a few steps. Now it is entirely up to you what you do with those errors, but for the jobs above, I would be inclined to go check what user it was that owned those jobs.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38853928
mark_wills,

I just tried that out and yes, I will result that to "you havent copied across those users to windows authentication, so, it is reporting "what is"
"
a the test machien with SQL server 2008 R2 and SQLserver 2005 with SP4 I am using is a standlone server, it dont' connect to the AD and therefore do domain account can be added.

the standalone SQL server login can be login using old password by using this: http://support.microsoft.com/kb/918992.

as long as the server can connect to the AD to find domain user, then I think it should be ok.

"Server>\SQLServer2005SQLAgentUser$SBATCH01$MSSQLSERVER "

so this kind of SQL server 2005 account can be remove after migrate to SQL server 2008 (we are still in the process of SQL 205 SP4 or SQL Server 2008 R2 + SP2+ CU4, but SQL server 2008 R2 most likely as they belive that that later on the upgrade will be less pain).
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38854058
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38883714
it seems that  everything in SQL configuration manager has to create manually, like Alias, on the new server's configuration manager?

How about link server? how can we carry the password of that link server item to the new server?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38884135
Well, it depends on how you set up that linked server.

If all on the same domain, then easy enough to simply use your windows login.

If on a totally seperate machine, then you have a few additional challenges with security first at the network level (you need the two machine to be able to see each other), and then the SQL login.

When setting up the linked server you can provide either the local (external) alternate user and password, or, the remote user and password.

The biztalk document "how to" actually describes it pretty well for SQL Server ( and while it says biztalk, dont worry, it is pure SQL Server that they are talking about) : http://msdn.microsoft.com/en-au/library/aa560998(v=bts.10).aspx
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38892242
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38892318
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now