Seperate SQL 2K Publisher, SQL2K Distributor, SQL2K5 Subscriber
Feb 17, 2006
Hi,
We are about to alter our replication architecture from
SQL 2K publisher/SQL 2K5 distributor/subscriber
to
SQL 2K publisher, seperate SQL 2K distributor and SQL 2K5 subscriber.
The subscriptions will be pulled from the distributor and pushed to the subscriber. Basically, all the work will be done by the distributor (in the hope of improving performance).
Has anyone had any experience of a similar architecture? All SQL servers are running with the latest service packs.
View 9 Replies
ADVERTISEMENT
Mar 28, 2007
hi,
I'm reading the MS online docs about replication. there's something not so clear until so far.
I have one central server and 7 client servers.
Sometimes new data is entered at one client server. This should be replicated (when client comes online) to the Central Server (merge replication).
Also, the other way, once the central server gets new data (that was replicated from a client), it should be again replicated to all 6 other clients (from the central server).
now, who should i configure as the distributor, publisher and subscriber?
View 3 Replies
View Related
May 16, 2006
For transactional replication, are there any issues and is it even possible to have 32-bit SQL 2000 publisher and SQL 2005 64-bit distributor and subscriber? Thanks
View 1 Replies
View Related
Jul 30, 2006
We have a SQL 2000 Server (publisher & distributor) with around 15 databases being replicated (all using merge replication).
My question is the following:
Is it possible to perform Merge replication from a SQL 2000 (acting as the publisher & distributor) to a SQL 2005 server acting as the Subscriber)?
If anyone has a link to any instructions somewhere that would be great.
Thanks
View 3 Replies
View Related
Apr 13, 2008
Hi Everyone,
I plan to do transactional replication. The publisher/distributor is sql2005 and subscriber is sql 2000. Is that possible. Because When I setup subscriber, I keep getting "The directory service is currently unavailable" in finding sql server replications.
Thank you in advance
View 3 Replies
View Related
Jun 26, 2007
Hello,
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
View 3 Replies
View Related
Nov 28, 2005
Is it possible to convert a SQL2K datafile to SQL2K5? I have a 2K database that I need to easily convert to 2K5, I apprecaite any insight on this issue.
View 1 Replies
View Related
Sep 14, 2007
Hi
I'm trying to select data from a mirrored database situated on a SQL2k5 server, via a linked server on SQL2k.
I can select the data fine using SQLOLEDB but it's not aware of the mirroring so if the DB fails-over it errors.
To get around this, I installed SQLNCLI.msi to install the SQL2k5 provider and created a linked server like this:
exec sp_addlinkedserver @server=N'TEST2',@srvproduct=N'SQLNCLI',@provider=N'SQLNCLI',@provstr=N'Server=server1;failover partner=server2;'
exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TEST2',@useself=N'False',@locallogin=NULL,@rmtuser=N'user',@rmtpassword='password'
Executing the statement: select * from test2.database.dbo.table
gives the following error:
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column. Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column 'IsActive' (compile-time ordinal 2) of object '"database"."dbo"."table"' was reported to have changed. The exact nature of the change is unknown].
If i change the statement to
select * from openquery(test2,'select * from database.dbo.table')
It works. I don't really want to have to go and refactor all the SQL though!
I've seen similar posts related to Oracle and SQL2k5 64bit but they don't seem to be relevant to this situation.
Here's the version info:
2000 server (SP3):
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
2005 mirrored servers (SP2 3054):
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
I've also tried applying the following to the 2005 servers: sp2(3159) sp2(3161) but makes no difference.
Any ideas?
TIA
View 1 Replies
View Related
Apr 3, 2007
Does anyone know if there are any known issues with using a 64 bit distributor and 32 bit publisher together?
This is SQL 2005 and Push Merge replication.
Thanks in advance
Martin
View 1 Replies
View Related
Jun 26, 2007
I AM TRYING TO SET UP A DISTRIBUTOR ON SQL SERVER 2005.
THE LAST STEP WHEN I AM TRING TO CONNECT I GET THE ERROR BELOW:
“ORACLE SERVER INSTANCE 'FOOBAR' CANNOT BE ENABLED AS A PUBLISHER BECAUSE OF THE FOLLOWING ERROR:
ADDITIONAL INFORMATION:
UNABLE TO RUN SQL*PLUS. MAKE CERTAIN THAT A CURRENT VERSION OF THE ORACLE CLIENT CODE IS INSTALLED AT THE DISTRIBUTOR. FOR ADDITIONAL INFORMATION, SEE SQL SERVER ERROR 21617 IN TROUBLESHOOTING ORACLE PUBLISHERS IN SQL SERVER BOOKS ONLINE. (MICROSOFT SQL SERVER, ERROR: 21617)�
RESEARCHING THE ERROR IT STATES TO MAKE SURE SQLPLUS IS SET UP CORRECTLY IN THE PATH. THE PATH IS CORRECT. TESTING SQL PLUS IT WORKS; AND I CAN ALSO CONNECT TO THE ORACLE DB IN BUSINESS INTELLIGENCE.
ANY HELP WOULD BE APPRECIATED.
View 3 Replies
View Related
Aug 6, 2007
Hi - hoping some folks here can help me. I've got a SQL 2000, Service Pack 4 database that I need to publish, but I'm getting the following error on the exec sp_addpublication statement when I try to set up publication in Management Studio:
Msg 8526, Level 16, State 2, Procedure sp_addpublication, Line 802
Cannot go remote while the session is enlisted in a distributed transaction that has an active savepoint.
I also tried to set up the Publisher in Enterprise Studio, on the theory that the system sproc was not backwards compatible, and there I got this error:
Error 627: Cannot use SAVE TRANSACTION within a distributed transaction
There is one proc in the publisher's database that uses SAVE TRANSACTION, but it isn't currently being run and it isn't dependent on the table being published. Has anybody else encountered this? Any ideas about how to make this go away?
View 1 Replies
View Related
Jul 16, 2015
I am setting up  transaction replication in SQL Server 2012 with three individual servers for Distributor, Publisher, Subscriber, But I want to know how to set the folder for Snapshot agent like whether the folder should be a network shared folder or normal local folder.
View 4 Replies
View Related
Apr 3, 2007
I am investigating scenarios for our new replication strategy and we have 2 very powerfull machines. 1 however more powerful than the other.
The distributor will be pushing (merge) data to 40 replicas around the globe, the database is about 5GB in size and uses dynamic filtering to sort out who needs what data.
The publisher will only be working with the distributor it is not doing any other work.
I am guessing the distributor will have the majorty of the work to do in this case and should have the more powerful machine. Does anyone know if this is correct?
Thanks in Advance
Martin
View 3 Replies
View Related
Aug 28, 2007
I have Oracle 64-bit drivers installed. I can run SQLPLUS and it works. I can create linked servers to oracle and that works also. I am having this error when I try to add oracle as a publisher. OraOLEDB.Oracle is already set to 'Allow InProces'.
Msg 21626, Level 16, State 1, Procedure sp_MSrepl_testconnection, Line 110
Unable to connect to Oracle database server 'DTMART' using the Oracle OLEDB provider OraOLEDB.Oracle. For addition information, see SQL Server Error 21626 in Troubleshooting Oracle Publishers in SQL Server Books Online.
Msg 21747, Level 16, State 1, Procedure sp_MSrepl_adddistpublisher, Line 463
Cannot establish a connection to the Oracle Publisher 'DTMART'. Verify connection information and ensure that you can connect to the Publisher through a tool like SQL*PLUS.
Thanks..
View 1 Replies
View Related
May 30, 2001
Hi All,
Can push and pull subscriptions be configured on the publisher/distributer server?
We have server A running SQL Server 2000 configured as Publisher/Distributor and also a Push subscription (transactional replication) to Server B configured as Subscriber.
The idea was to have Server B as backup server if Server A goes down. Users will continue to update data in server B till server A is up.
Once server A is up, I want to synchronise the up-to-minuit date from Server B to Server A in the form of Transactional replication configured as "Pull Subscription" from Server A.
Is that possible?
Thanks
Sri
View 1 Replies
View Related
Oct 13, 2006
Hi all,
I am having a problem now. I cannot drop my publisher and publication because i was uninstalled the remote distibutor before i disable al lthe publication. Now the remote distibutor no longer exist.
Can another one help me? Urgent!!
View 1 Replies
View Related
Aug 29, 2007
hi,
Im I want to configure my sqlserver 2005 server as both subscriber and distributor for transactional replication purposes.
I want to configure the sql server 2000 as the publisher.
When I try to configure the publisher in the sql 2000 server, Im not being able to establish the connection with the distributor 2005 sql server in any of the 2 possible cases.
case1-Using windows authentication- message:need to use management studio or SMO
case2-Using sql server authentication-message:authentication failed
Question:
Is it possible at all to do this? or should I make the distributor to reside in a sql 2000 server?
thank you
View 1 Replies
View Related
Jan 23, 2007
I am setting up my 2005 Replication system...
publisher = 2005 sp1
Subscriber = 2005 sp1
I created a publication for a single table. Then I created the subscription to another 2005 server. Had to add it as a subscriber in the wizard. Told it to do the snapshot right away.
Everything seems fine right up to the point where it tries to connect to the subscriber... I get a cannot connect error. I have tried all kinds of security context and accounts for the sql agent to run under but nothing seems to work. I cannot even get a linked server to work. I have the subscriber setup to accept remote connections.
I am not sure where to look at next... I never had this issue in 2000.
View 14 Replies
View Related
Jul 23, 2005
Hello All,The setting is SQL server 7, on Windows NT.If the distributor can not access the log on the publisher database ( butsubscribers are not deleted yet), will the full backup of the publisherdatabase truncate the transaction log? My guess is, it will not truncate thelog.Can any one confirm please?Thanks,Mokles
View 2 Replies
View Related
Aug 22, 2007
Hi all
Can the Snapshot be copied from the distributor to the subscriber and then from the subscriber using the copied snapshot, create a subscription to the publisher?
Urgent help would be greatly appreciated
WARNING: Running on cold coffee!
View 3 Replies
View Related
Nov 2, 2006
Hi,
Server A houses the Production DB which services an OLTP system. Transactional replication is configured on this server which has two subscribers - on two separate servers (servers B and C). The Distribution database currently resides on Server A (which is also the Publisher).
Server B is on the same LAN as Server A (on the same rack). I'm considering moving the Distribution database from Server A to Server B to offload some of the processing overhead from Server A.
Are there any gotchas/performance problems associated with having the Distribution database (remote distributor) and the Subscriber on the same server (server B) in a transaction replication topology?
Please advise or point me to the appropriate documentation - I haven't found anything that addresses this specific question...thanks in advance,
Smitha
View 3 Replies
View Related
Oct 2, 2007
I am having a problem creating a publisher.
I have a database, MobileApp, on a server called Server03, which is a subscriber to a database Stock on Server01. I create the database as a subscriber on Server01, and it gets replicated to Server03 correctly.
I now want to use MobileApp on Server03 as a publisher to mobile devices. I click to add a new publication, and add all the tables. I get all the way to the end, but when I click on finish, I get an error for each table - 'an article with a different subscriber_upload_options value already exists for object.
Any ideas please?
Thanks
Pete
View 6 Replies
View Related
Aug 26, 2007
I have a clustering between Server A and Server B. There is a clustered SQL Server installed.
Server A is a primary node(oltp production server) and Server B is a secondary node.
i've set up transactional replication betw serer A and B. Server A is Publisher and Server B is subscriber. B also has a distribution database. I use serve B for all the reporting purposes.
Currently both servers have sp1.
Now i want to apply sp2 to the server B first since i use it for reporting purposes (just to make sure there are no issues and then after some days i want to apply sp2 on server A which is my prodction oltp server)
So when i apply sp2 on server B , my publisher will have SP1 and subscriber (& distributor) will have sp2. Will this cause any issues with replication?
Any help would be appriciated.
Thanks
View 4 Replies
View Related
Aug 25, 2004
What happens when Network Connectivity between Distributor & Subscriber
fails in Transactional Replication ??
Thanks,
John
View 2 Replies
View Related
Jan 29, 2008
I am not able to connect to Remote Distributor from Publisher using Administrative Link Password. I have configured the Distributor on 1 m/c and Publisher on another m/c. When i use the wizard to configure the publisher using remote distributor. I have also specified the same Admin Password link (distributor_admin) in the distributor m/c.
Â
I am getting the following message:
Â
TITLE: New Publication Wizard------------------------------
SQL Server could not connect to the Distributor using the specified password.
[URL]
------------------------------ADDITIONAL INFORMATION:
Connection to server [SANMENON] failed.OLE DB provider "SQLNCLI" for linked server "D956CF83-AE2E-4FC5-83DD-BE90D84A3950" returned message "Login timeout expired".OLE DB provider "SQLNCLI" for linked server "D956CF83-AE2E-4FC5-83DD-BE90D84A3950" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". (Microsoft SQL Server, Error: 21670)
[URL]
------------------------------BUTTONS:
OK------------------------------
View 12 Replies
View Related
Jul 31, 2015
We are going away from a 2003 Windows Server OS with SQL Server 2008 R2 to a 2012 Windows Server with 2012 SQL Server. Both the distributor and subscriber resides on 2003 Windows Server and the focus will be to migrate those databases to the 2012 SQL Server.
 We would also like to avoid sending down a new snapshot due to logic in the replication process (major headache to clean up data).  what's the best approach in moving the distributor and subscriber databases without having to run a snapshot? Â
In theory and needs testing:
1. Work with business users to get downtime
2. Stop sql apply so no new changes are going to the Oracle publisher database
3. Remove subscription, publication and distributor from current replication
4. Oracle DBA to clear out replication related objects - fresh clean slate
5. Use log shipping to apply last t-log and restore db with Keep_Replication
6. Set up new distribution, publication (keep existing object unchanged), and subscriber (without initialization) Will this work?
View 3 Replies
View Related
Feb 9, 2000
Hello:
I am running an application on mssql 6.5, sp4.
We are working on using a database as a publisher and a remote database as a subscriber for one replication in Canada.
WE havw just been told that another application on a different server(publisher/ distribution) wants to replication to our database as a subscriber.
We also will need(as a publisher/distribution) to replication to a second subscriber database after we completely test the first replication to Canada.
Therefore my questions are:
1) Can a database on a local database -- our database -- on one server be a publisher and subscriber to ther different databases?
2)has any one been involved with replication on a database used both as a publisher and subscriber to different replications requirements?
What are the pitfalls aside from the scheduling conflicts?
3) In terms of database space requirements, how much disk space extra do we need? What guidelines should we use? For each table as an article, assuming we are doing the full table, do we add 100% more space or what?
4) Any other pitfalls we should be aware of?
Thanks.
David Spaisman
View 1 Replies
View Related
Sep 27, 2006
Quisiera saber si se puede y como llevar a cabo la replicación con SQL 7 (Publisher) y SQL 2005 (Subscriber). Desde ya muchas gracias.
View 4 Replies
View Related
Dec 7, 2006
I have replication setup between our main site and a remote one, and have recently noticed that the database at the remote site's .MDF file is about 3 times as large as the main site's. This doesn't seem to make sense since essentially all of our data is replicated between the two servers. Can anyone suggest why this might be happening and what is safe to do to shrink the remote file?
TIA
Ron L
View 8 Replies
View Related
Sep 14, 2007
I have been looking for a way to replicate data from a DB2 system running on an AIX machine. I found some information related to doing this with SQL2000, but not with 2005. Can this be done - preferably without writing our own provider.
Thanks in advance
View 5 Replies
View Related
Sep 14, 1999
Hi Nerds
There are 2 production servers (SQL Server 6.5). One is publisher and the other one is subscriber. Data is replicated from publisher to subscriber which is a transaction based replication. Each day at 8:00PM data comes into publisher and the updations,replication,backup,reporting are carried out till 11:00AM next day. The subscriber server is used as a reporting server and the publishing server is used as the data warehouse server.
I want to cleanup a table on the publisher. Since it is replicated on the subscriber, can I do delete operation on the subscriber, make the subscriber server a publisher and the publisher server a subscriber, do replication from publisher(new) to subscriber(new) and again change publisher to subscriber and subscriber to publisher without affecting the production line of the databases. I have to do this between 11:00AM to 8:00PM.
Pranav.
View 1 Replies
View Related
Sep 23, 2015
We've got x2 data centers in the US:
NYC & SAC
Sadly the hostnames for the boxes I am trying to replicate are similar:
- db1.nyc.mydomain.tld
- db1.sac.mydomain.tld
The servers can talk to each other over all necessary ports however when I generate a push subscription from the publisher, I am asked to add the SQL Server subscriber and it already shows DB1 listed since itself is DB1.nyc. How can I attach the remote subscriber when setting up the subscription from the publisher if the hostname conflicts in my unique scenario?I created a entry on the publishers host file to use db2sac for the SAC IP and entering that alias in as the remote subscriber but no dice.
View 4 Replies
View Related
