Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Upgrade from SQL Server 2000 with Linked servers and Replication jobs Expand / Collapse
Author
Message
Posted Tuesday, March 22, 2011 11:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 20, 2014 2:22 PM
Points: 42, Visits: 535
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

Post #1082166
Posted Wednesday, October 24, 2012 4:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 2:29 PM
Points: 9, Visits: 67
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.
Post #1376711
Posted Thursday, October 25, 2012 9:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 20, 2014 2:22 PM
Points: 42, Visits: 535
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


Post #1377086
Posted Thursday, October 25, 2012 9:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 2:29 PM
Points: 9, Visits: 67
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.
Post #1377092
Posted Thursday, October 25, 2012 10:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 20, 2014 2:22 PM
Points: 42, Visits: 535
Hi,

Please refere this article:
http://sqlblog.de/blog/2009/07/linked-sql-server-encrypted-connection/

Thanks
Post #1377104
Posted Thursday, October 25, 2012 11:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 2:29 PM
Points: 9, Visits: 67
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
Post #1377160
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse