Link SQL Server 2000 with SQL Server 2008

Question

I have my database running on a SQL 2000 server and my windows application developed on VS 2003.  I am porting the application to a web based version using VS 2010 which will not connect to my existing database.  My hosting service can move me to a SQL2008 server, but that breaks my application till I get the port done.  Question is, can I run a copy of the current database on a local machine with SQL 2008 and create a temporary application to do updates to the data and replicate/mirror/upload the changed data to my SQL 2000 production database as needed.  That gives me more time to develop and test my new application while providing a better implementation of inputing new data while I get everything else ported, tested and moved to a SQL 2008 database.  I am using a hosting service so do not have complete control of settings on production server, but can copy and upload data.

---

Aegis2010

Answer 1

There is such version SQL Server 2003... Yes you can create linked server between SS2005 and SS2008 to move the changes

http://www.sqlservercentral.com/articles/Video/66413/

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Why linked server between test db server onto production server?

I would suggest you to refresh db from Prod SQL 2005 onto your test db server 2008 on regular basis - take full back on 2005 copy across thru restore it on you test 2008 db server.

You can do this with T-SQL scripting and schduled it based on your requirement.

We refreshing our test environments from prod weekly basis using T-SQL scripting it just work fine.

---

http://uk.linkedin.com/in/ramjaddu

Answer 3

We use SQL Compare for structure's changes.. It depends on what  and how the OP wants to apply changes.... If it is small amount of data has changed so using linked server could be fine 

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 4

It was late when I posted original message.  SS is version 2000 and I developed application with VS2003.  What I am looking for is a way to create a copy of my production DB on my local SS2008 so I can create a new application to interface with it using VS2010.  Problem is that VS2010 does not support SS2000.  If I can develop for my local copy and then update the production manually/automatically, it buys me some time to port my application and server over to the latest versions.

---

Aegis2010

Answer 5

As said before why don't you take full db backup on SS2000 and restore it on SS2008 then VS2008 application should support

---

http://uk.linkedin.com/in/ramjaddu

Answer 6

RamJaddu,

My production DB is on SS2000 and used daily by all of my clients.  When I move it to SS2008, I will have 2 wks before I will loose my SS2000 version and my application will not work.  What I need is a short term solution that is SS2008 and I can keep the two DB synchronized.  That lets me create a new application on SS2008 while keeping existing application working and incrementally porting over different parts of the App.  I have read through the other threads on linking SS2000 with SS2008 but am not sure that is what I need.  Is a mirrored DB better or just make a local copy and daily manually upload and download changes?  Point is, I need both DB to stay with current data

---

Aegis2010

Answer 7

DB mirroring and log-shipping are out of scope as they should be created on same editions other wise for db mirroring when db faillover onto SQL2008 then you won't be faillover db backup onto SQL 2000 until the server upgraded to SQL 2008. Log-shipping STAND BY option does not support between different editions.

Replications also not advisable on production to test server as it creates lots of triggers and cause several performance issues -

here some suggestions -

As said only best option would be copy full sql2000 backup file across and restore it on SQL 2008 database. You can build your application on sql 2008 on test server which was restored from prod sql 2000.

If your db size is couple of GB then you can refresh full backup on daily / even once in couple of hours also you can try with SSIS packages for incremental feeds but that might create some load on your production server during refresh time.

hope this helps you 

---

http://uk.linkedin.com/in/ramjaddu