Moving to SQL 2005
-
Hi everyone,
I wanted to get your opinion on how to migrate our SQL 2K databases to 2005. Our situation is unique in that we have two SQL 2K servers that we want to move to a new server with SQL 2005 installed on it. What would be the best way of accomplishing this merge, keeping in mind the user permissions and DTSs, etc.? Thanks in advance.
-
Hint: do not use any wizard tool to upgrade or import/export from SQL2000 to SQL 2005.
Here are what I did:
1. Use this KB to copy the logins to new server: http://support.microsoft.com/kb/246133
2. Backup the databases at SQL 2000 server
3. Restore the databases at SQL 2005 server using the backups in step 2.
4. Change the compatibility to 9.0 using sp_dbcmptlevel for each database.
5. Remap logins at step 1 to usernames in each database using sp_change_users_login. This will retain the permmissions.
In each database, each restored user will have a default shema = its name in the database. for example user 'Steve" will have the default schema = 'Steve' and owns that schema. Its up to you to change its default schema back to DBO and delete the schema or just leave them as is.
For the DTS, you better rebuild the packages. Using the upgrade is kind of a time bomb waiting for you in the future.
hth,
-
hth,
Out of interest, does it matter if each of the accounts has it's own schema or should they all be reset back to DBO?
What effect do they play?
Coll
-
it doesn't matter what you do with the schemas but I changed all back to DBO. In case we let the developers go we don't need to worry about anything else beside deleting their login.
-
Thank you very much for your detailed response.
As far as DTS packages go, we have hundreds!
What kind of problems have you seen or read about as far as upgradings DTS packages is concerned? -
I don't remember the details but you can google for that. I did google and found lot of problems reported. We had some old DTS packages using ActiveX that SSIS couldn't run. Beside that what can assure that the upgraded packages will run when MS releases next SP or next version of SQL? So I asked the developers to redo everything.
-
I wish my situation was as simple as two servers... One thing you could do is put two instances of SQL on your server - multiple instances do not need to be licensed seperately anymore. This would help you with user conflicts and possibly simplify things for you.
Now - on to DTS. If you have your packages stored in Meta-Data-Services, I am sorry to report, you have to move all of them to local storage to even see them after upgrading. Meta-Data stored packages just disappear on you. After that, I can tell you from extensive experience that in any DTS package more complicated than a simple transform, the SSIS upgrade will give you an SSIS package with a DTSRun command in it that contains your original DTS package. Although this pretty much sucks from an "upgrade" perspective, it does mean that 90% of your packages will still function, they will just not really be SSIS packages. From there, you can install some backward compatibility tools found on the MS website and run and even edit the packages. If they are all working, I would recommend upgrading them over time as they need modifications.
-
There's a SQL 2000 DTS Runtime you can install on 2005 to run your packages.
The advice above looks pretty good to me as well.
-
Michael,
I am probably outdated. Can you show me a link for this info?
Thanks a lot!
-
We have restored our SQL 2000 DB to SQL 2005, ran a full regression with our custom application which accesses the 2005 DB and found no issues. Are there any repercussions of not running the sp_dbcmptlevel command and retaining the current compatibility level of 80?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply