Moving data from internal SQL2k to hosted MS SQL Server 2005 or 2008 1

[smatthews]

Has anyone had experience in moving data from a SQL2k server (internal) to a another SQL server that is hosted? A web server would run against this hosted SQL instance to provide authentication into a web site.

We would love to have fully sync'd initially and then do instant updates to it if something is changed on the internal SQL2k server.

Any insight or resources to get started would be helpful.

 

[hmckillop]

Effectively what you are looking to do is to replicate your DB changes from your local SQL2000 DB to a hosted SQL2005 server.

Initially you will probably backup your local DB and then restore to the hosted (transfer via FTP?) - you may also require your host to help you a bit as not sure of local file paths etc.

Replication - you will be restricted to the replication types due to SQL2005 and SQL2008 expanding the functionality and this is not offered with SQL2k
You are also best to ensure you have the latest Service Pack for SQL2000

Have a look at

http://msdn.microsoft.com/en-us/library/ms143241.aspx

it contains fairly much everything you need to know about the nuances between versions etc.



one last point, a client of mine also went down this route, I however have recommended a change to his architecture for his application - he updates his data on his local server via Access front end, I have said if he repoints to the remote server then there is no requirement for the replication as the hosted SQL server is the source for all the data

"I'm living so far beyond my income that we may almost be said to be living apart

 

[smatthews]

Good info...thanks!