Migrating from SQL2000 -> SQL2008 R2 2

[1DMF]

Hi,

We are having new servers installed next week to replace our old SBS2003.

We are getting SBS2011 which includes a seperate server for SQL2008 R2.

I've been thrown in the deepend with the migration of the SQL data and rerouting of all our systems and applications.

Having never migrated anything SQL wise before I really would appreciate any heads up and advice you guys could give me.

How do you go about moving SQL data from one version to another?

I take it you cannot restore the SQL2000 .BAK files into SQL2008?

Do I simply copy the MDF/LDF files to the new box and then create new instances on the new box?

Is there a tool in SQL 2008 for migrating / importing old SQL data?

Your advice is very much appreciated.

Regards,
1DMF



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[gmmastros]

Both methods work. You can backup/restore or detach/attach.

The bigger issue is migrating logins, linked servers, and verifying all the functionality works.

There are security implications regarding the log on account for SQL Server. This is particularly relevant if you are using SQL to move data between various computers. To do this, open services, scroll to the SQL Server service, right click -> Properties, click on the "Log On" tab. If your current server is using an active directory account, you should be able to configure the new server to use the same account.

Microsoft has published articles about migrating logins between SQL Servers. [google]Migrate SQL Server logins between SQL2000 and SQL 2008r2[/google]

If you are using linked servers, you will need to script them from the old server and recreate them on the new one.

There may be other gotchas that I'm not thinking about, but you should at least check these things.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Can't seem to attach the MDF/LDF files?

I get 'Access is denied'.

I've checked the file permissions and SYSTEM has full control

Any idea what access is denied? from what SQL or Windows

I'm logged into SQL server with the sa account and both servers have the same password for the sa account?

Thanks,
1DMF


"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[1DMF]

Got it working.

Needed to change ownership of the file and add my account to the permissions and it seemed to attach and work fine.

Let's hope the 'real' move goes as smoothly as the test!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[SQLBill]

You should find the Upgrade Wizard for SQL Server. There should be a version for SS2008r2. You would run it on the SQL Server 2000 box and it will tell you any code that does not comply with SS2008. That code would then need to be re-written and applied after the upgrade.

Also, if you are using DTS packages, you would have to convert them to SSIS packages.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[1DMF]

Thanks bill, but what code are you refering to?

Stored Procedures?

Views?

We don't use DTS, so that at least won't be an issue.

Can't find an upgrade wizard for SQL2008 R2 , I found an upgrade advisor :

http://msdn.microsoft.com/en-us/library/ms144256.aspx

Is that what you meant?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[1DMF]

Well I ran the advisor and i'm a little confused.

Firstly this is an 'upgrade' advisor, yet we are perfoming a 'migration', which isn't the same thing is it?

Secondly, it is moaning about Full-Text-Search, stating I have to fix this before as FTS has changed in 2008, but we don't have any FTS catalogs?

It is also stating that large backup/restore hitory tables might hang when it tries to alter the tables to add new columns, I'm assuming these are either sys tables or msdb tables, which I wasn't planning on migrating, should I be?

cheers
1DMF.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[SQLBill]

The upgrade advisor (and yes that is what I meant) looks at everything even if you aren't migrating it. You got pretty lucky with what it did find. Lots of times you'll find code that is using old joins (*=, =*) and SS2005/2008 don't like that. There's other code items that are acceptable in SS2000 but not in later versions, but it looks like you don't have any of that to deal with.

As for FTS, check to see if you have Full-Text enabled. I believe it will give the warning if Full-text is enabled even if it isn't being used.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLScholar]

Firstly this is an 'upgrade' advisor, yet we are perfoming a 'migration', which isn't the same thing is it?
[/qoute]

Well actually in some ways you are doing an upgrade. When you mount (restore... whatever) the database on 2008 then the database will be upgraded to a 2008 database.

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[djj55]

To add to what Dan (SQLScholar) said remember you CANNOT go from 2008 to 2000.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[1DMF]

Thanks guys,

Well to day is the day, so wish me luck

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[SQLScholar]

djj55

good point!

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[1DMF]

Seems like good news!

It's all moved over fine, all inhouse DB's and members extranet seems to be up and running without a hitch.

I even took this opportunity to change all ntext data types to nvarchar(Max)

Just need to reconfigure the backup jobs and it should be job done!



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[gmmastros]

I even took this opportunity to change all ntext data types to nvarchar(Max)

Did you check your code to see if anything will break? The old Text/nText data types had special functions that will not work with the varchar(max)/nvarchar(max) data type. I'm specifically referring to TextPtr, UpdateText, and WriteText.

I encourage you to run this code to see if it returns anything.

Select	schema_name(schema_id), 
        Name, 
        type_desc, 
        object_name(parent_object_id)
From	sys.objects
Where	type_desc in ('SQL_STORED_PROCEDURE', 'SQL_TRIGGER', 'SQL_TABLE_VALUED_FUNCTION', 'SQL_SCALAR_FUNCTION')
        And (
         object_definition(object_id) Like '%TextPtr%'
         Or object_definition(object_id) Like '%UPDATETEXT%'
         Or object_definition(object_id) Like '%WRITETEXT%'
         )

If you're lucky, the query above won't return anything. If you're not lucky, this will return a list of object (stored procedures, triggers, and functions) that contain code that handles the text data type. If anything is returned, you should fix the code so that it does NOT use TextPtr, UpdateText and/or WriteText.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Thanks George, no rows returned

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[djj55]

Hello, make sure you check for deprecated code. SQL email is different and there was one other one we needed to fix but I can not remember. Sorry. There might be more.



djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[1DMF]

Don't even know what SQL email is, let alone use it, so shouldn't be an issue.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta