Upgrade from SQL Server 2000 with Linked servers and Replication jobs

  • Hello All,

    I am in the process of Upgrading from SQL Server 2000 Enterprise Edition (x86) to SQL Server 2008 Enterprise Edition. now I have Linked servers, DTS packages and Replication jobs running on the server

    My question is:

    what are the exact steps (or precautions that I need to take) while upgrading with linked servers, DTS packages (Actice x scripts task is there) and mapping the exact logins with SIDs and with Repliation jobs.

    Please suggest me a link or please suggest the list of steps that i have to follow.

    Thanks in advance

  • Did you get this worked out? Were there any special issues with regards to the Linked Servers? I'm doing the same thing, and have added a Linked Server, but it's giving me issues.

  • Hi,

    I dont have any special Issues, but it's good if you re-create Linked server after you upgrade your sql server and after you migrated Logins and SIDs to new server.

    With regards to DTS package migration, there are 3 methods

    1) Run the DTS package in SSIS using backward compatibility feature

    Pros: if you have any legacy application/legacy feature that needs to run ONLY DTS package

    Cons: you can't use the latest features and advances in the SSIS package, for ex: if there is an ODBC connection which connest and pulls millons records iinto sql server destination, then after you upgrade you can use OLEDB or native connection to pull the records which is much faster that legacy method

    2) Upgrade the DTS package using the DTS upgrade wizard which you find thru SSMS object explorer --> Management --> Legacy --> DTS --> right click on the individual DTS package and select the option MIGRATE. This method will automatically upgrades to newer version, if there are some ActiveX Scripts you have to re-create them seperately, you have no choice (to my knowledge, plz confirm this with soem one else too .... just to make sure)

    Pros: Easy to migrate, less effort

    Cons: If you have many legacy tasks like ActiceX Script, it will be tideous to re-create all the broken pieces

    3) Re-create the whole SSIS package, which is similar to DTS package with same source and destination and same transformations

    Pros: Very straight forward and 100% success if you properly work on the creation of new SSIS package

    Con: Time consuming, but it's worth to spend such time on this

    Here is the referenec giude on the DTS MIgration:

    http://www.infosys.com/microsoft/resource-center/Documents/DTS-SSIS-2008-migration.pdf

    Hope this might help you or atleast some intilative. Let me know if I can give more info, to my knowledge

    Thanks

  • Thank you. My issue is actually just with setting up a Linked Server. It appears the problem is that I'm using SQL Server 64 bit and trying to add a linked server that is SQL Server 2000 (32 bit). When I try to run a stored procedure on the remote server, i get this:

    OLE DB provider "SQLNCLI10" for linked server "SOMESERVER.COM" returned message "Client unable to establish connection".

    Msg 20, Level 16, State 1, Line 0

    Encryption not supported on SQL Server.

    I'm not sure why "Encryption is the issue", but when setup the same linked server on a 32 bit machine with SQL Server 32 bit, it works fine. :crazy:

  • Hi,

    Please refere this article:

    http://sqlblog.de/blog/2009/07/linked-sql-server-encrypted-connection/

    Thanks

  • Thank you I actually had found that article, and did try that, and it still didn't work. I did get it worked out using an odbc connection.

    Thanks!

    -Russ

Viewing 6 posts - 1 through 5 (of 5 total)

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