Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Upgrade from SQL Server 2000 with Linked servers and Replication jobs


Upgrade from SQL Server 2000 with Linked servers and Replication jobs

Author
Message
mpalaparthi
mpalaparthi
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 566
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
russds
russds
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 94
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.
mpalaparthi
mpalaparthi
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 566
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
russds
russds
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 94
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
mpalaparthi
mpalaparthi
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 566
Hi,

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

Thanks
russds
russds
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 94
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search