Replication Beetwen SQL 2005 && SQL 2000
Oct 30, 2007Dear All
Is it possible to make replication beetwen:
SQL 2005 as publisher & SQL 2000 as subscriber ? :shocked:
Best Regards
Piotr
ADVERTISEMENT
What Will Going From Latin1_General_Bin To Latin1_General_Bin2 In SQL 2005 Do To Replication From 2000 To 2005?
Aug 3, 2006If we changed the sort order from BIN to BIN2 but kept everything else the same will it have any effect on replication? So in SQL 2005 if I were to change my default collation from Latin1_General_Bin to Latin1_General_Bin2, would that cause replication to break? I suspect that it will not be an issue since it is just sort order that is changing and the code page stays the same.
BTW, this is transactional replication. Sorry, I left that out of my original post.
Thanks,
Grant
2000 And 2005 Replication
Jun 27, 2007Hi Friends
What are the main differences between Sql Server 2000 Replication and Sql server 2005 Replication?
Thanks
Vic
2000 To 2005 Upgrade With Replication
Aug 17, 2005Where can i find some reference material on how to upgrade to 2005 in a replicated environment please?
does anyone know what order i need to tackle the servers, is it likely to be the same as the Service Pack process?
cheers
Hybrid Replication (2000 And 2005)
May 4, 2006Ok here is the deal...We have our current dev box running SQL 2000 SP3We have two new desktop boxes that we are using as a test, they are both running SQL 2005 SP1The Dev Box = PUBLISHERDesktop #1 = DISTRIBUTORDesktop #2 = SUBSCRIBERAll three SQL Servers are using the same Domain Account to run SQL Agent and SQL Server processes.When I try to create a publication from the publisher, while running the sp_addpublication step through the UI (or thru SQL Query Analyzer) I get the following error message:Msg 15404, Level 16, State 10, Procedure sp_grant_publication_access, Line 136Could not obtain information about Windows NT group/user 'crumpte', error code 0xffff0002.So as a test I tried using our subscriber box as the publisher and had it point to the same distributor box to act as a distributor - and the publication was created fine.It seems to be an issue using SQL 2k as the Publisher and SQL 2005 as the distributor. From the research that I have done, I found this KB article:http://support.microsoft.com/default.aspx?kbid=830596Which essentially says to install the latest SP for SQL 2k, which would be SP4, in the past I know there were issues with AWE memory allocation.Q1. Has SP4 been hotfixed to resolve this AWE issue?Q2. Has anyone here successfully setup replication from SQL 2k -> SQL 2005 using SQL 2005 as the distributor?Thanks!
View 12 Replies View Related2000/2005 Mixed Replication
Jan 17, 2006Hi There
Currently in production we have a publisher, remote distributor, and subscriber all running 2000.
We
want to upgrade the subscriber and possibly the remote distributor to
2005 while leaving the publisher 200 for now, i need a good link or
article about the proper proceudre to accomplish this.
Thanx
Tx Replication Works In 2000 But Not In 2005?
Apr 28, 2006Hello,
I've got a simple transactional replication set up. I have a separate publisher, distributor, and subscriber with 76 articles (tables only) being pushed from the distributor.
I have this exact setup with the same tables and data working in the SQL 2000 environment. I am testing replication on our SQL 2005 test servers before moving to production, however when the distributor attempts to push out the initial snapshot I keep getting this error.
Error messages:
Incorrect syntax near ')'. (Source: MSSQLServer, Error number: 102)
Get help: http://help/102
Incorrect syntax near ')'. (Source: MSSQLServer, Error number: 102)
Get help: http://help/102
Incorrect syntax near the keyword 'end'. (Source: MSSQLServer, Error number: 156)
Get help: http://help/156
Anyone know anythign about it?
Thank you,
Aaron Lowe
Replication SQL 2000 SP3 To SQL 2005 SP2 Problem?
Oct 19, 2007Hi All,
I have replication from SQL 2000 SP3 to SQL 2005 SP2 and now i am expereancing one strange problem, there are situations that last row of some tables(Articles) doesn't come to subscriber.For exmple i insert 50 rows and 49 are coming to subscriber and always it is last row(considered by my Primary key).Before then my replication model was SQL2000 SP3 -> SQL2000 SP3 and i didn't have such problems.
Can someone give me a clue?
Looking For 2000 &&> 2005 Replication Options.
Aug 8, 2007I am looking for a way to replicate databases from an environment using SQL Server 2000 sp4 to machines using either 2000 sp4 or 2005 sp2. The size of the dbs (+3 gb) warrant a transactional model, but the developers opted to not create primary keys for any of their tables and PKs are required for transactional publications (http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1627873&SiteID=17&pageid=0#1614178). Creating PKs are, most likely, not an option given the time constraints of the project.
I'm also aware of the fact that merge replication does not work from SQL Server 2000 to SQL Server 2005 (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=499634&SiteID=1).
I'm looking for alternative solutions to this problem, regardless of whether it's replication or not. Log shipping was suggested at one point, but I want secondary confirmation on this. Any help is appreciated. Thanks.
UPDATE: Log shipping in SQL Server 2000 is supported in Enterprise and Developer edition. The source server is using 2000 Standard.
UPDATE 2: Log shipping in SQL Server Standard is *possible* but unsupported http://sql-server-performance.com/Community/forums/t/17798.aspx?PageIndex=1. It looks like the original poster was still having problems by the end of this thread.
Transactional Replication From 2005 To 2000 Issue
Nov 8, 2007Hi,
I setup transactional replication between 2005-> 2000 database for only one table. It works fine no problem. I checked replication monitor everything works well.
My Subscription was Push Subscription on Publisher.
This morning I restored main database at publication server, I saw all my publication configuration were gone.
I then went to create a publication and push subscription again..and did it, But when I went to replication monitor.
I checked that Snapshot are being created, but from distributor to Subscriber is saying below message?
"The concurrent snapshot for publication is not available because it has not been fully generated.....lONG CHPPPED off meassage"
Can any body tell me hwo to get over this issue?
Many Thanks
SKR
SQL 2000 -&&> SQL 2005 Merge Replication Compatible?
Apr 19, 2006Hi everyone,
I'm currently trying to setup a SQL 2k (SP3, build 922) merge replication publisher and a push subscription to a SQL 2005 (RTM release, no hotfixes) subscriber. The distribution database resides on a separate SQL 2k server (SP3, build 1007). I get the error below leading me to believe merge replication is not compatible between versions.
Replication-agentclassname: agent Agent_Name failed. Procedure or function sp_MSupdatesysmergearticles has too many arguments specified..
I say this b/c I've tried SQL 2005 32-bit and x64 subscribers and both give the same error. Anyone have any ideas if this is by design or just a bug that will be fixed later? Thanks.
LSC
SQL Server 2000 To 2005 Replication Upgrade
Apr 17, 2008Hi All,
Currently we use SQL 2K SP4 and snapshot replication with a Central Publisher with Remote Distributor toplogy.
I am looking to upgrade or migrate our SQL servers to SQL 2005 and was wondering what is the best way to do this for our replicated architecture?
Is the best way to run the SQL 2005 Upgrade on all 3 servers (publisher, distributor, subscriber) and should it automatically upgrade the servers including the replication components? Is there anything i should consider/watch out for when doing the upgrade and it involves replication (namely snapshot replication)?
Thanks in advance.
Merge Replication Differences Between SQL 2000 And SQL 2005
Jun 27, 2006We have developed a mobile system that uses merge replication for SQL Mobile to SQL 2005. Previously we have developed mutliple mobile systems using merge replication for SQL Ce to SQL 2000.
Based on the knowledge we had gathered over about 4 years, we applied the synchronisation parameters for the SQL 2005 solution as we would for the SQL 2000 solution.
We have found there are some differences. Not too surprising I suppose, only some of these have us a little baffled.
For instance, there was a little flag called keep_partition_changes in SQL 2000 that is supposedly superceded by the use_partition_groups flag. However, if you don't set up your filtering to conform to the standards required by the use_pre_computed_partitions flag if you want it set to true, then the use_partition_groups flag gets set to false - also the @partition_options falg gets set back to 0 (static or non-unique data) when we want it at 3 (Single Parition, One subscriber).
To top it all off, when you get the use_partition_groups flag working, there are restrictions on which columns you can update on the device. WTF? This seems ludicrous, to be unable to update data at the subscriber - particularly information that allows you to effectively "delete" data from your subscription.
Examples of the current behaviour are as follows,
On initialize for a subscriber, the subscriber will receive their own data as inserts, plus exact multiples of that data as updates. Say there are 100 rows in TableA, the subscriber gets 100 inserts, plus 6000 updates. TableB has 20 rows, the subscriber gets 20 inserts, 1200 updates.
Further to this, performance goes out the window when synchronising changes. Typically the data flow will be between 5 and 200 changes in both directions for a synchronisation. We are seeing sync times in the replication monitor of over 20 seconds per user. Surely the calculations do not take that long. The tables in the database are not very large.
This behaviour gets significantly worse as we load the system. The application has an auto sync function which is timed to operate evry 10 minutes. However, now that there is in excess of 50 or so users on the system, those synchronisation times blow out to multiple minutes and the server starts to thrash. We have looked at indexing and maintenance but to no avail.
Everything still points to the merge replication setup.
So, it seems obvious to me that we are mising some key information about how to set up merge replication in SQL 2005. We woudl be very gratefull if someone could point out the errors of our ways.
Sorry for the convoluted post. Hope someone can help us.
Cheers
Steve
RMO Vs Scriptiong On Hybrid 2000 2005 Replication
Jan 8, 2007I have created the plication and push subscription on SQL 2000 by running sql scripts. I can view it through management console already.
but on SQL 2000 server, i dont' know how to start the snapshot agent and how to synchronize explicitly. In 2005, there is a system sp
sp_startpublication_snapshot @publication = @publication
can do the job. can you tell me how to do the same in the sql 2000?
additionally, I tried to use RMO to implement the replication. but it seems some of the classes only support 2005. doesnt' it means RMO can't be used to program on hybrid replication environment?
plz reply. thanks
Transactional Replication : SQL 2000 To 2005. Add New Table
Mar 2, 2007I have a publication on 2000 machine, replicating by pull subscription to a 2005 machine. I added a table to the Articles in the publication. Reinitialized the subscription, and also restarted both the snapshot and log reader agent. I was kind of assuming the publication would automatically create the table on the subscriber, but after a very long wait it is still not there. Am I missing anything?
PS. Distination Object setting : "Action if name is in use" is set to drop and create new, not that that should make any difference since name is not in use anyway.
Thanks
SQL Replication: 2005 (Publisher) And A 2000 (Subscriber)???
Sep 25, 2006Hi,
I was wondering if it is possible to setup replication between a 2005 SQL Publisher and a 2000 SQL Subscriber? Is there any special setup steps I need?
Thanks so much,
David
Replication From Sql Server 2000 To Sql Express 2005
Sep 15, 2006
I want to replicate data from sql server 2000 to a sql server express 2005 box.
I can dts data across..but I want to be able to set up push replication from the Sql Server 2000 box, but the option to push the data to the Sql Express server in the GUI does not come up only Sql Server 2000 boxes are listed. Initially I will be using Snapshot replication but would like to eventually move on to transactional rep if only I can get it to push the data across.
Does anyone know how to acheive this thanks for any help Sammy.
Weird Conflicts - Merge Replication 2005 -&&> 2000
Aug 3, 2006Publisher is 2005 x64, subscribers SS2000 (SP3) and SS2005 x64. Pull agents, no filters on subscriptions. We are seeing many seemingly random conflicts on between SS2000 subscriber and publisher. It happens on several different tables.
One table is never editted, only inserts happening everywhere and deletes happening on the SS2000 subscriber. Deletes will sometimes generate conflict. Reason is '"he row was deleted at 'CTS11.CTS' but could not be deleted at 'cts4a.cts'. Unable to synchronize the row because the row was updated by a different process outside of replication." CTS11 is SS2000 subscriber, CTS4A is publisher.
Probably unrelated bug but when looking at conflicts on this same table in SS2005 conflict viewer, get error "ID is neither a DataColumn nor a DataRelation for table summary (System.dATA)" and then "Column ID does not belong to table summary (System.Data)". ID column is rowguid, only unusual thing about table is that it has varchar(8000) field plus some other fields.
Other tables generate conflicts with this reason "The row was updated at 'CTS11.CTS' but could not be updated at 'cts4a.cts'. The merge process was unable to synchronize the row." I enabled verbose logging in the merge agent but the log file didn't contain any further explanation.
This same topology and schema worked fine when all publishers and subscribers were SS2000.
Any insight into how to fix this would be appreciated.
Merge Replication: Problems Upgrading SQL-2000 To 2005
Dec 25, 2006Hello!
We have a module in our business-application, that automatically installs merge replication of the business-application's database, both at publisher and subscribers. It's intended for Sql-2000. Now we need it to be applicable also for Sql-2005, so the module requires some changes, because, as we noticed, sql-2005's replication technology differs from Sql-2000's one.
A few questions to experts, familiar with Merge Replication in SQL-2005:
1. Is it possible to create hybrid replication, with publisher running at SQL-2000 (MSDE) and subscribers running at SQL-2005 Express? Merge publication is not supported in SQL 2005 Express, but some users may require option to use the application at free-of-charge database platform.
2. How deep are changes in merge replication implementation at system level? Is it just modified a bit since SQL-2000, or changed entirely? This knowledge is needed, because the module uses some low-level features (executing system sp's, querying replication-specified tables, etc.). For example, when we tried to create subscription of existing publication in SQL 2005 using the module as is, we found out that sp_addmergepullsubscription_agent doesn't use @encrypted_password parameter anymore, and subscription creation process failed.
3. If anybody has experience using merge replication creation/deletion/detection scripts, generated by SQL 2000, in SQL 2005! Please, tell - what more problems may happen?
Thanks!
Excessive Network Usage With SQL 2000 To SQL 2005 Transactional Replication
Aug 3, 2007We have a SQL2000 database (Publisher) replicating inserts and updates across a 10Mb link to a SQL 2005 database (Subscriber). The Publisher has two tables we are interested in, 1 with 50 columns and 1 with 15. Both tables have 6 insert/update triggers that fire when a change is made to update columns on the publisher database.
We have set up a pull transactional replication from the Subscriber to occur against the Publisher every minute. We have limited the subscription/replication configuration to Publsih 6 columns from table 1 and 4 from table 2. Any change occuring on any other columns in the Publisher are of no interest. The SQL 2005 database has a trigger on table 1 and table 2 to insert values into a third table. There are around 7,000 insert/updates on table 1 and 28,000 on table 2 per day. All fields in the tables are text.
We are seeing "excessive" network traffic occuring of approximately 1MB per minute (approx 2GB per 24 hrs). We also see that the Distributor databases are getting very large -- upto around 30GB and growing until they get culled. We have reduced the culling intrval from 72 hrs to 24 hours to reduce the size.
Does anyone have any suggestions as to how this "excessive" network traffic can be minimised and how the distributor database size can be minimised. I think that maybe they are both related?
Thanks,
Geoff
WA POLICE
Transactional Replication From SQL 2000 Sp4 Publisher/distributor To SQL 2005 Sp2 Subscriber
Jun 26, 2007Hello,
Are there any requirements that dictate the SQL Server version for the distribution agent for a SQL 2000 publisher with a transactional push subscription to a SQL 2005 subscriber?
Thanks in advance,
John
How To Skip Snapshot In Transaction Replication (from SQL Server 2005 To 2000)
Jun 13, 2006We have two SQL Server 2005 production DB at remote sites. Due to network bandwidth issue, we need to replicate these DBs (publishers and distributers) to central corporate SQL 2000 DB (subscriber for backup and possible reporting (and in rare case as a failover server).
We would start out with backup from SQL 2000 db restored on remote SQL 2005 DBs. When we have DB issue on remote 2005 DB, we want to restore it from central corp. 2000 DB backup. Since two DBs are replicating to central DB, we DO NOT want combined db back up data on restored remote 2005 db. We can restore the db and delete unwanted data before we turn on replication from this restored server. So, this is not a problem.
The real problem is how to avoid snapshot replication (during initialization) when we create a transaction replication on this restored server to avoid over writing data on the central subcriber sql 2000 DB???
HELP!!
Transactional Replication From SQL 2005 To SQL 2000 Affects Performance Of Database.
Sep 21, 2007
Hello,
We previously having two servers A and B. Server A is used for updation of data and the data then replicated to server B. Server B is used for
Server A :
purpose : used for database updation/ modification
SQL Server version : SQL Server 2000 SP 2
Server Z :
purpose : used for Reporting
SQL Server version : SQL Server 2000 SP 2
We were doing Transactional replication from Server A to Server B.
Last month we have broght another server (Server B) with same hardware configuration but having SQL SERVER 2005 installed. This is to speed up our database update process. We have moved some of the database on this new server so that we can achieve our deadlines.
Server B :
purpose : used for database updation/ modification
SQL Server version : SQL Server 2005
I have set up the transactional replication from Server B to Server Z and replication works fine.
However, the issue is after it is started replicating from this new server (Server B) performance of all the queries reduced a lot.(making my life harder)
I didnt expected this as our reporting server is still SQL server 2000.
I have restored the backup of database which was replicated from server A (sql server 2000) and compared execution plan for one of our common query (which is used in most of the reports and which is now taking longer time to provide results)
I found that database which is replicated from Server B (Sql server 2005) is having primary keys. which was not present in the database which replicated from server A(Sql server 2000).
I have then removed the primary key and make the indexes same as previous copy of database(which was replicated from server A) But still the query takes long time.
Execution plan now shows "Table Spool" which was not present in previous copy of database.
Almost every query for this database is taking longer time now.
Can someone suggest me what is wrong and what should I need to fix.
Am I going on the right direction?
Setting Up Replication On SQL Server 2000 Using Distributor Running On SQL Server 2005
Jul 6, 2006Hi
I have a setup where I need to replicate the database which is actually subscribing from another database. The current setup is all in SQL Server 2000. I need to now setup a Distrbutor on a SQL server 2005 and publish the database using this distributor to another server on SQL server 2000.
Has anybody done this before. If yes what will I need to check. Can you please let me know :-
1) SQL Server 2000 which SP should be installed to support this enviroment.
2) SQL Server 2005 which SP should be installed to support this environment.
3) Any thing that I need to look out for.
Thanks for any inputs on this.
Regards
Configuring Merge Replication For Side-by-side SQL Server 2000 And 2005
Feb 6, 2007I am trying to migrate from my current system, where I do merge replication from Windows Mobile devices running SQL Server CE 2.0 to a central database running SQL Server 2000 sp3a. I want eventually to move to a system running SQL Server 2005 CE replicating to a SQL Server 2005 back-end. But the transition will need to be gradual, and I may have to support both systems for a while until I can convert all clients from the old system to the new. I also need to do thorough testing.
So ... I'm trying to set up a test environment giving me the maximum possible flexibility to do my testing. Ideally, I'd like to set up SQL Server 2000 and SQL Server 2005 on a side-by-side basis, in a manner that would potentially allow mobile devices running both SQL Server CE 2.0 and SQL Server 2005 CE to sync with either back-end server.
Can someone provide me with guidance as what is possible to set up here? I know that SQL Server 2000 and 2005 can be installed side-by-side on the same server. It also appears that you can set up SQL Server 2000 so that EITHER SQL Server CE 2.0 OR SQL Server 2005 CE can sync with SQL Server 2000 (see www.microsoft.com/sql/editions/sqlmobile/connectivity-tools.mspx), but I don't know if it's possible for BOTH SQL Server CE 2.0 AND SQL Server 2005 CE to sync to the same SQL Server 2000. As for SQL Server 2005 ... it appears to be possible to set up SQL Server 2005 so that BOTH SQL Server CE 2.0 devices AND SQL Server 2005 CE devices can sync to the same SQL Server 2005 (see web page cited above). However, I don't know if it's possible to set up a SQL Server 2005 server installation in this manner while at the same time having a side-by-side SQL Server 2000 installation supporting any level of mobile merge replication.
HELP!!!!
Transactional Replication From SQL Server 2005 To SQL Server 2000
Nov 8, 2006I am trying to test simple replication (only tables) of a database that resides on a SQL Server 2005 instance to a SQL Server 2000 instance. The Publisher and Distributer are set up on the SQL Server 2005 instance for Transactional replication. The subscriber is set up on in the 2000 instance. Replication Monitor shows the following error after applying a few scripts:
"Category: SQLSERVER Source SQLSERVER2000 Number: 170 Message: Line 6: Incorrect syntax near
'max'."
Here SQLSERVER2000 is the name of my 2000 instance, as should be obvious.
Beyond this point, replication fails. Any pointers as to where the problem could lie? Is this a known backward compatibility issue? I've checked all tables in the database and none contain any datatype that is new to 2005 (the database was actually created in and for SQL Server 2000.
Replication from 2000 to 2005 works fine, but the other way round is failing as described above. Any clues?
Replication From A 2005 Database To A 2000 Database
Jun 7, 2006Is it possible to setup replication for a particular database from an SQL2005 database server to a SQL2000 database server.
I've searched a few forums and the MS knowledge base but can't find a specific answer.
Hopefully someone can point this novice in the right direction.
Mike
Replication From SQL Server 2000 To SQL Server 2005
Apr 11, 2007I am trying to replicate data from SQL Server 2000 to SQL Server 2005.previously we did it from 2000 to 2000 using script.
but when i am tring to use that script with 2000 to 2005 its not working i have made the necessary changes in the script.Is there major changes to replicate from 2000 to 2005
Install Sql 2005 Instance With Reporting Service 2005 On Sql 2000 With RS 2000 Server
Aug 18, 2006Hi
We would like to install Sql 2005 Enterprise Edition (including database engine, reporting service, integration service and analysis service) as a sepearte instance on a server which already has Sql 2000 with reporting services and analysis services. We do not want to disturb the existing sql 2000 setup.
If we do that then what will happen to my earlier sql 2000 reporting service? Will it be upgraded to sql 2005 reporting service? I heard that reporting services are instance unaware application. Where will be the default reporting service database available?
Please help us.
Regards,
Sankar N
MSDE 2000 Replication To SQL Server 2000
Jun 27, 2006i went through the documentation but i was not clear on following, here is the scenario :-
a Central server is having SQL Server 2000
3 Remote Locations :- Each having 4-5 no of computers, connected on a Lan, and on one of the machines MSDE 2000 will be running.
My Questions are :-
1. Can remote locations, update data locally and send changes (say in every one hour) to central server. If yes, then how ??
2. Same way they can receive updates from Central Server on whatever was updated on Centra Server or on the 3 remote locations. If yes, then How ??
any help will be highly appreciable.
SQL 2005 Error: Replication-Replication Distribution Subsystem: Agent (null) Failed.
Jun 15, 2007I'm getting this, after upgrading from 2000 to 2005.Replication-Replication Distribution Subsystem: agent (null) failed.The subscription to publication '(null)' has expired or does notexist.The only suggestions I've seen are to dump all subscriptions. Sincewe have several dozen publications to several servers, is there adecent way to script it all out, if that's the only suggestion?Thanks in advance.
View 3 Replies View RelatedReplication In SQL 2000
Mar 21, 2001Hi,
I have no idea of SQL2000.Can anyonetell me steps /precuations involved in going for Replication in SQL2K.
Also,How do we do log shipping in SQL2K?
TIA
pd
SQL 7 To 2000 Replication
Apr 4, 2001I am trying to set up transactional replication (not immediate updating) via a push subscription between a SQL 7.0 Server and a SQL 2000 Server. The SQL 7 server is the Publisher / Distributor, and the SQL 2000 server is the subscriber. Replication will not work between the two, I have removed then setup replication to no avail. The SQL 7 server always reports that it can't connect to the SQL 2000 server. Same problems the other way if I try a pull subscription.
Anyone have any ideas?
thanks,
Kyle Freeman
zilchfrag@zilchfrag.com