I have a central server (CS1) with database CDB and a branch server (BR1)
Inside the branch server, there are two database DB1 and DB2.
CDB contains data for DB1 and DB2
I made a subscription in the brancserver named BR1:DB1(this will replicate the data from CDB to DB1)
I also made a subscription for DB2 named BR1:DB2(this will replicate the data from CDB to DB2)
Unfortunately, upon starting the synchronization for BR1:DB1, the data replicated into DB1 also contains data for DB2. same happens with the BR1:DB2..
How do I filter the data that will be replicate per database..Example:only data for DB1 coming from CDB should be replicated into DB1 if that specific subscription(BR1:DB1) is synchronized.
We need to replicate multiple databases (publications) to one central subscriber. The schema of those articles are identical in all publications and also the primary keys in publications do not have any overlap.
Is this possible?If yes is there any specific thing that I should consider for it's implementation? Should each publication has it's own dedicated distributor or all of them can share one distributor?
I have database (DB) on the server SQL1. This database (DB) is published on SQL1 server for SQL2 server. So, SQL1 is publisher and SQL2 is subscriber for (DB). Can I publish database (DB) on the server SQL2 for the server SQL3? I would like to implement this scheme of Publication/Subscription:
<o:p></o:p> SQL1 -> SQL2 ->SQL3..
I know, that it is simpler just to publish DB for SQL2 and SQL3 from SQL1, but, because of network connectivity, I can't do that...
When creating the snapshot I didn't choose the option copy non-clustered indexes. I created some indexes manually on subscriber database. Replication failed later with some issue.
so reinitialized with new snapshot but all the indexes are gone from the subscriber database.
How to keep the indexes without dropping from subscriber database whenever we reinitialized with snapshot
I am doing replication through C#.net after creating the publication in server and subscription in client. But i want to know how can we view the transactions that are happening to the both databases through the C#.net code...If anybody knows please help me....
We have a database which is (a subset of tables are) replicated to another via transactional replication. Whilst most changes made at the published database reach the subscriber within a matter of seconds, we have a SQL Agent job which performs a calculation in the published database and then immediately exports data from the subscriber using log shipping. The result is that the calculated changes do not make it through to the exported transaction logs in time.
Is there a way to manually "refresh" the subscriber databases using T-SQL?
We have a database we are replicating to about 8 SQL Express subscribers from a SQL 2012 SP2 publisher. The size of the database grew too large for the 10GB license limit for SQL Express and now replication refuses to replicate any of our deletions on the publisher to reduce the size of the database. I've come up with a few options below.
1) Drop one of the larger table indices on the subscriber database to get below the size restriction. Permit the replication to replicate the deleted records and then rebuild the index. (I'm not sure how important an index is to this table. Is it merely performance related?)
2) "Upsize" SQL Express to SQL Standard on the affected boxes. Allow the deletes to replicate. Backup the database, downgrade to SQL Express and restore the database back to SQL a new SQL express instance. This would involve a lot of work on each box. I'd like to avoid it if possible.
I am using the "Pull Subscription Wizard" to set up merge replication. I go through all the steps of the wizard and then I hit "Finish" at the end. When it gets to the third step (Creating the Subscription), I get the old:
__________________________________________________ _________ mmc.exe has generated errors and will by closed by Windows. You will need to restart the program.
An error log is being created. __________________________________________________ _________
This happens whether I try to do this from my PC or from the actual server I am creating the subscription on.
Has anyone else had this issue? Is there a hotfix I need?
I hope anyone here can help me with this tough problem:
I have a replication enviroment with several subscribers. Publisher:MSSQL 2000, SP3 Subscribers: MSSQL200, SP3 and MSDE2000, SP3
Since a few weeks, one of the subscribers fails when synchronizing with the message "This process could not enumerate changes at the subscriber"
The erroroutput with verboselevel 3 doesn't help me much, I append it to the end of this posting. (unfortunately german)
I heard that this error can be caused by a "blank" in an image column or different Service Pack Versions - definitely not possible in this case! The only strange detail is that the SQL Server Versionnumber of this MSDE shown via EM is not the same as the number shown in controlpanel->software. By the way, is it ok that here are two lines for the MSDE? SP3 is definitely installed on all machines!
Any help would be greatly appreciated - i'm already losing hope... Best Regards, Gert
Microsoft SQL Server-Merge-Agent 7.00.623 Copyright (c) 1998 Microsoft Corporation
Percent Complete: 0 Verbindung mit Abonnent 'MyServerAT074' Connecting to Abonnent 'MyServerAT074.MyDB' MyServerAT074.MyDB: {call sp_MSgetversion } Percent Complete: 0 Verbindung mit Verteiler 'MyServer1s' Connecting to Verteiler 'MyServer1s.' MyServer1s.: {call sp_MSgetversion } MyServer1s.: {call sp_helpdistpublisher (N'MyServer1s') } MyServer1s.distribution: select datasource, srvid from master..sysservers where srvname = N'MyServer1s' MyServer1s.distribution: select datasource, srvid from master..sysservers where srvname = N'MyServerAT074' MyServer1s.distribution: {call sp_MShelp_merge_agentid (0, N'MyDB', N'MyDB', 9, N'MyDB')} Percent Complete: 0 Initialisiert MyServer1s.distribution: {call sp_MShelp_profile (19, 4, N'')} Percent Complete: 1 Verbindung mit Verleger 'MyServer1s' Connecting to Verleger 'MyServer1s.MyDB' Connecting to Verleger 'MyServer1s.MyDB' MyServer1s.MyDB: {call sp_MSgetversion } Percent Complete: 3 Ruft Publikationsinformationen ab Percent Complete: 4 Ruft Abonnementinformationen ab Connecting to Abonnent 'MyServerAT074.MyDB' Disconnecting from Abonnent 'MyServerAT074' Percent Complete: 4 Uploadet Datenänderungen zum Verleger Percent Complete: 5 Ruft die Liste der Löschungen von MyServer1s.MyDB ab Percent Complete: 5 Verarbeitet Artikel 'First'
...... SNIP .... Percent Complete: 5 Verarbeitet Artikel 'LastTable' Percent Complete: 0 Der Prozess konnte die Änderungen auf dem Abonnenten nicht aufzählen. Percent Complete: 0 Category:SQLSERVER Source: MyServerAT074 Number: 0 Message: Attributverletzung eingeschränkter Datentypen Attributverletzung eingeschränkter Datentypen Disconnecting from Abonnent 'MyServerAT074' Disconnecting from Verleger 'MyServer1s' Disconnecting from Verleger 'MyServer1s' Disconnecting from Verteiler 'MyServer1s'
I want to call a stored procedure to do the insert into my published arcticle/table. Is this possible? I know you can call a stored procedure to insert into the subscriber arctiles.
The problem is the published database has a generated primary key (max +1 )that is calculated via a stored procedure during all inserts. I would bring the stored procedure over to the subscriber but that would not help since the values in the target table will not be 'current' because the subscriber database is only updated nightly.
I am tring to use SQL Server Mobile 2005 to subscribe to a SQL Server 2005 publication from a pocketPC. In my code I have a SqlCeReplication object. One of the properties that must be set is: Subscriber, but I have no idea what that should be set to. When I was setting up the publication I don't remember specifying anything like that, and the class description of this property doesn't help: "Specifies the name of the Subscriber". Any help will be appreciated.
I have tried backing up the replication from Primary Server (Publisher) A and copying over to Subscriber B. When I sync. I am missing the data. I don't know what step I am missing or it is bug on SQL 2K SP3.
I don't know what i am doing wrong.
Can anybody give me a step by step detail how to backup the publisher database and copy to a subscriber and sync by saying the schema and data already available. As we have WAN problem all the times, and when I try the full snapshot of 20 gig it was taking more than 24 hrs and also if the connection fails my snapshot also fails
I installed 2 SQL2000 servers at a client site, the first acting as a publisher and distributor for transactional replication. The second is a push subscription to the first. The client wanted to change the database name, and some other stuff, so I removed the replication and set it up again for the new database name.
All's fine at the publisher / distributor but the subscriber is showing as still having active push subscription to the old (and new) databases
I am using merge replication with a push subscription type. I am wondering if the updates of the tables on the subscriber side are push to the publisher from the subscriber or pulled from the subscriber by the publisher when the syncronisation takes place. this makes a big diferrence for me and i can't find the answer to this question anywhere...
if anyone could answer it would be really appreciated
Hi, I am trying to setup bi-directional transactional replication between 2 SQL Servers, I add the subscription on both the servers using the code below.
I have defined 'AnyServer' on both the servers using cliconfg (Server1's AnyServer pointed towards Server2 and Server2's AnyServer pointed towards Server1, I need to do that because there is a restriction to run the same code on both the servers),
After inserting a record on server1 in the 'test' database, the changes successfully transfers to the server2, then server2 sends it back to server1 and server1 generates the error of
"Violation of PRIMARY KEY constraint 'PK_Table1'. Cannot insert duplicate key in object 'Table1'."
It seems as if loop detection is failing if I keep the same subscriber name on both sides.
It runs fine when I change the subscriber name in the subscription (@subscriber = 'Server2' for server1 and @subscriber = 'Server1' for server2).
My manager does not want to go through the process of moving 40 GB of data to the subscriber database over the network as would occur during the initialization phase of replication (exact method of replication has yet to be determined). The subscriber db is for Web Access and will be read-only. He wants to back up a db and use tapes to restore the db on the subscriber db server, and then activate replication. I have told him that you have to allow SQL Server to create the objects on the subscriber side and you have to allow SQL Server to control the data flow, that manually creating the objects on the subscriber side will not work. He says that I am incorrect.
Who is right? I haven't been able to find an answer from the Microsoft site and news groups.
I am trying to setup mirrored subcriber in Transactional Replication in SQL 2005. I am not able to do so as it don't work after failover.However I am able to setup mirrored publisher with distribution property "PublisherFailoverPartner" for snapshot and log reader agent profiles and it works even after failover.
However I need to know, do we have something called "Subscriberfailoverproperty" or any other method to set up mirrored subscriber in Transactional Replication in SQL 2005.
Using SQL CE 3.1 merge replication. Working fine, but want to make subscription read only. In other words, master/slave pattern. How is this accomplished?
Hi. I have setup an SQL Server 2005 Merge Replication. Now I need to display the status of the replication programatically from the subscriber side, I have checked the documentation which mentions MergeSubscription & MergeSubscriberMonitor but I couldn't know how to use them!! Any Help ? Thanks
I know that SQL Server Express 2005 can subscribet to SQL Server 2000 publications. However, it seems that SQL server 2000 cannot perform a push to SQL Express.
Can anyone refer me to any online help or documentation on how a proper replication can be done between SQL 2005 Exress and SQL 2000??
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.
Using Merge replication between SQL Server 2000 and SQL Server CE, is there any way that row deletes could occur on the subscriber without a reinitialize or explicit delete of row on publisher.
More specifically, if there is a row filter that returns a days worth of data with each days pull, for example, and the filter looked like select <columns> from Table where UpdateDate < GETDATE() and UpdateDate >= DATEADD(d,1,GETDATE()) would there be some implicit delete at subscriber each day because data sent changed?
My research indicates this does not happen, but I have a colleague who thinks differently.
I have setup merge replication between SQL Server 2005 and SQL Server Express. The setup uses web synchronization. (And the setup uses dynamic filter HOST_NAME)
On some of the client machines, replication fails with the following error (when initializing the subscription):
from subscriber merge agent verbose log: A dynamic snapshot will be applied from 'D:DOCUME~1JBAUMG~1.CORLOCALS~1TempDB101-EQA-SCL_BQDB_BQ_PUB_BQDB'
Validating dynamic snapshot 2007-04-1819:39:45.434 OLE DB Subscriber 'FRG-BAUMGARTENJSQLEXPRESS': sys.sp_MSregisterdynsnapseqno @snapshot_session_token=N'\XXX.XX.XX.XXSnapshotuncDB101-EQA-SCL_BQDB_BQ_PUB20070417180079dynsnap', @dynsnapseqno='5599F67E-A1A9-4573-A14F-9851F6FE4B51'
(Note: XXX.XX.XX.XX is a proper IP of DB, I have just masked it in the post)
The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file.When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
I enabled verbose logging for the snapshot agent for the subscriber, but there was no error there. It is obvious from the log info above that the error occurs only after downloading the snapshot to the client and during applying it to the subscriber by the merge agent.
After I got this error, I tried running the subscriber program again and it successfully replicated on the third attempt. But this behavior is not consistent and it fails for most of the time on some of the machines
I am setting up a system using SQL Server 2005 replicating to both SQL Express and MSDE clients. My question is this - if a client PC has been rebuilt - i.e. new hard disk etc, how can I automatically make that machine realise that it is already a subscriber of a SQL Server database and for it to automatically get a snapshot?
Hi Folks Is there an easy way around this ? One Way Transactional Rep Subscriber needs SCHEMABINDING on the majority of their Views (require View Indexes) which read from Replicated Tables. Main table has 4 Million Rows ReInitialize Subscription Errors with Cannot Drop Table because it is being referenced By Object ..... [schemaBound View] GW
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.
i have replication setup and working, there are 3 subscribers and now i want to add a 4th one. is it ok if i just add the subscriber and reinitialize replication?
Greetings All, I was hoping that a replication sage might be able toanswer a question for me.I want to have one subscriber subscribing to N publishers. Iessentially have a company that has a main headquarters and threesatellite offices. I want each of the satellite offices to push theirdata up to the master database. From what I have read it seems likethis should not be a problem. Some questions that come to mind are:1.)Does the master need to be read only or can it be configured to beupdateable as well?2.)Can the distribution agent on all the publishers be set tocontinuously distribute or should it be staggered so as not to cause aproblem when another distribution agent is running?3.)If the distribution is set to "delay distribution" will this causechanges on the subscriber to be pushed out to the publishers?In this database guid's are used as pk's so the issue of pk collisionsis not a problem.I hope that this question is not too vague. My experience thus farwith replication has been simple one way transactional and simplemerge replication.Regards, Louis Frolio
