Replication - SQL 2005 to 2000

[SQLBI]

Hi,

My SQL Server 2000 DB is a subscriber to a dailiy replication from an off site SQL Server.

Until recently, both servers were 2000 and replication worked fine. The publisher has now upgraded to SQL 2005 and is unable to reinstate the replication, apparently due to a permissions problem.

The error being recieved at the SQL 2005 end is:

I've been asked to add the user of the remote site (which currently has db_owner rights on my server) to the sysadmin server role and i'm a little uneasy about this as it will obviously give a remote 3rd party complete access to my SQL Server.

Could this error message in fact be refering to the permissions of the SQL 2005 Server?

Has anyone come across this before and is the solution (as stated in SQL 2005 BOL here:

http://msdn2.microsoft.com/en-us/library/ms152528.aspx)

to add this useer to the sysadmin role? This article appears to suggest that once the replication is set up, i could remove the user from the sysadmin role and its db_owner privaleges should suffice.

Thanks in advance.



Cheers,
Leigh

The problem with common sense is that it isn't that common!

 

[Catadmin]

You can't replicate 2005 to a 2000 machine. You can do it the other way around, but 2000 doesn't recognize a lot of the data types, etc. that have been added to or changed in SQL Server 2005.

Sorry.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLBI]

Oh great!

Can an instance of 2000 exist on the 2005 box?

Cheers,
Leigh

The problem with common sense is that it isn't that common!

 

[Catadmin]

Well, yes & no.

If you actually restore to 2005 or detach and reattach to 2005, 2k5 will auto-upgrade the DB.

However, if the 2000 db exists on a separate SQL Server that is nothing but SQL 2000, then you can connect to the SQL Server in question via the SSMS interface without upgrading the database to 2k5.

Does that answer your question?


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLBI]

Right...

But i can't have an instance of 2000 and and instance of 2005 on the same server, like i can with 2000/7.0?

Sorry but as you've probably gathered i'm a little confused.


Cheers,
Leigh

The problem with common sense is that it isn't that common!

 

[Catadmin]

If they're actually on 2 different boxes, or if you actually have SQL 2000 and SQL 2005 installed side-by-side you can.

However, I'm not sure how to do a side-by-side install of SQL with 2000 and 2005. You'll probably want to search Microsoft's website for KB articles relating to that.

Sorry, when you said instances, I thought you were talking literal instances within the same Server installation.


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLBI]

Thanks catadmin.

What i meant was can install 2005 onto my 2000 server, but keep my installation of 2000 and all of my 2000 DBs in 2000 format?

Or, can 2005 not exist on the same server as 2000, in which case it will auto upgrade all of my 2000 DB's to 2005 if installed on the same physical server?

Cheers,
Leigh

The problem with common sense is that it isn't that common!

 

[Catadmin]

Like I said, I don't have too much information on that topic. You'll want to check Microsoft's website to see what they have.

However, when I installed the developer version on my PC, it wanted to upgrade my existing database and didn't install the 2005 version on my PC if I told it not to (It just installed the client tools without touching my database). Finally, after my PC broke and I had to get a new one, I did install SQL 2005 on my PC and went back to re-install the 2000 Client tools. However, this isn't a try side-by-side install scenario.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLBI]

Hi,

For clarification, 2005 can exitst as a Named Instance side by side with SQL 2000 SP3 and above.

http://msdn2.microsoft.com/en-us/library/ms143694.aspx

Cheers,
Leigh

The problem with common sense is that it isn't that common!